Ubuntu 安裝 PostgreSQL 8.04

安裝 postgresql

sudo apt-get install postgresql postgresql-client postgresql-contrib

如需要 GUI 管理工具可以安裝 pgadmin3

sudo apt-get install pgadmin3

更改資料庫 postgres 預設密碼 方法一

sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD 'newpassword';
template1=# \q

更改資料庫 postgres 預設密碼 方法二

sudo su postgres
postgres@local:/$ psql
psql (8.4.8)
Type "help" for help.

postgres=# select * from pg_shadow;
密碼欄位應該是空值
postgres=# alter user postgres with password 'newpassword';
postgres=# \q
離開psql
postgres@local:/$ exit
離開 postgres 帳號

更改系統帳號 postgres 密碼

sudo passwd -d postgres
sudo su postgres -c passwd

輸入新密碼

經過上面步驟之後就可以透過 command-line 和 pgAdmin 使用新的密碼連入資料庫伺服器。但在使用pgAdmin之前還應該將 PostgresSQL admin pack 設定好，以啟用 pgAdmin 更好的 logging 和 monitoring 工具，使用下面命令設定:

sudo su postgres -c psql < /usr/share/postgresql/8.4/contrib/adminpack.sql

修改設定，讓資料庫能接受遠端連線(Access from Remotely)(非必需)

sudo vi /etc/postgresql/8.4/main/postgresql.conf

#listen_addresses = 'localhost'

to

listen_addresses = '*'

#password_encryption = on

to

password_encryption = on

儲存並關閉檔案


設定誰可以連線

Now for the final step, we must define who can access the server. This is all done using the pg_hba.conf file. (The following advice can also be given to you - plus you don’t even need to figure out IP addresses and subnet masks - from the latest versions of pgAdmin (1.6.x). But it doesn’t hurt to know how things work).

sudo vi /etc/postgresql/8.4/main/pg_hba.conf

Comment out, or delete the current contents of the file, then add this text to the bottom of the file:

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database
# super user can access the database using some other method.
# Noninteractive
# access to all databases is required during automatic maintenance
# (autovacuum, daily cronjob, replication, and similar tasks).
#
# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

# Connections for all PCs on the subnet
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host    all         all         [ip address]          [subnet mask]  md5

and in the last line, add in your subnet mask (i.e. 255.255.255.0) and the IP address of the machine that you would like to access your server (i.e. 125.232.82.46). However, if you would like to enable access to a range of IP addresses, just substitute the last number for a zero and all machines within that range will be allowed access (i.e. 125.232.192.0 would allow all machines with an IP address 125.232.192.x to use the database server).

That’s it, now all you have to do is restart the server:

to

sudo /etc/init.d/postgresql restart

And all should be working.


原文:
http://hocuspokus.net/2008/05/install-postgresql-on-ubuntu-804/