Postgres安裝配置

1.下載下傳

2 建立使用者

# groupadd -g 210 postgres

# useradd -u 210 -g 210 -d /usr/local/postgres postgres

# passwd postgres //登陸資料庫需要用到這個使用者名和密碼

# chmod 755 /usr/local/postgres/

3下載下傳解壓縮 ，編譯

# cd /usr/local/src/tarbag

# su - postgres

$ tar xvfz postgresql-8.4.1.tar.gz -c ../software/

$ cd ../software/postgresql-8.4.1

$ ./configure --prefix=/usr/local/postgres //預設裝到家目錄中

$ make

$ make install

4 修改配置檔案

4-1 .bash_profile

$ cd /usr/local/postgres

$ vi .bash_profile

在檔案最後追加

export PATH="$PATH":/usr/local/postgres/bin

export POSTGRES_HOME=/usr/local/postgres

export PGLIB=$POSTGRES_HOME/lib

export PGDATA=$POSTGRES_HOME/data

export MANPATH="$MANPATH":$POSTGRES_HOME/man

export LD_LIBRARY_PATH="$LD_LIBRARY_PATH":"$PGLIB"

重新讀入，讓設定成功

$ source ~/.bash_profile

驗證一下

$ echo $POSTGRES_HOME //如果現實postgres的家目錄，說明環境變量沒問題

4-2 postgresql.conf

# mkdir /usr/local/postgres/data

# chown postgres /usr/local/postgres/data

# su - postgres

$ initdb -D /usr/local/postgres/data

$ cd /usr/local/postgres/data

$ vi postgresql.conf

Data目錄的權限一定要為0700，否則資料庫無法啟動

修改内容

listen_addresses = 'localhost,127.0.0.1,192.168.50.201'

port = 5432

password_encryption = on

//鄧贇添加内容

listen_addresses = 'localhost,127.0.0.1,168.50.201''可以寫成

listen_addresses = '0.0.0.0' 監聽所有的網絡

4-3 pg_hba.conf

$ vi pg_hba.conf

找到最下面這一行 ，這樣區域網路的人才能通路

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host all all 192.168.50.0/16 trust

5 加入服務

找到原來編譯的檔案夾

# cd postgresql-8.4.1

# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgres

# chmod 755 /etc/init.d/postgres

# /etc/init.d/postgres start //這樣就可以啟動了

6 開機啟動并且使用service指令啟動停止PostgreSQL（可選）

#chkconfig --add postgres

#chkconfig postgres on

#service postgres [start|stop] //可以使用這條指令了

//鄧贇添加

1、建立使用者時可以不設定密碼，直接su - 使用者。

2、如果期限建立的使用者名不叫postgres，那麼要把啟動腳本裡面的PGUSER=postgres，改成PGUSER=pgsql，否則也無法啟動。

然後可以用service postgres start、stop控制

6 其他

除此之外，當然要确認iptables設定，然後用postgres使用者進入bin，creatdb什麼的就任意胡為了。。。。。。

以下是安裝過程中出現的問題。并且以下配置的使用者名為pgsql。

Success. You can now start the database server using:

postgres -D /usr/local/pgsql/data

or

pg_ctl -D /usr/local/pgsql/data -l logfile start

日志：

[[email protected] data]# cat serverlog

su: user postgres does not exist

LOG: could not translate host name "localhost", service "5432" to address: Name or service not known

WARNING: could not create listen socket for "localhost"

LOG: could not bind IPv4 socket: Cannot assign requested address

HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.

WARNING: could not create listen socket for "192.168.50.202"

LOG: could not bind socket for statistics collector: Cannot assign requested address

LOG: disabling statistics collector for lack of working socket

WARNING: autovacuum not started because of misconfiguration

HINT: Enable the "track_counts" option.

LOG: database system was shut down at 2009-12-01 16:01:01 CST

LOG: database system is ready to accept connections

LOG: received fast shutdown request

LOG: aborting any active transactions

LOG: shutting down

LOG: database system is shut down

LOG: could not bind IPv4 socket: Cannot assign requested address

HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.

WARNING: could not create listen socket for "192.168.50.202"

FATAL: could not create any TCP/IP sockets

FATAL: data directory "/usr/local/pgsql/data" has group or world access

DETAIL: Permissions should be u=rwx (0700).

FATAL: data directory "/usr/local/pgsql/data" has group or world access

DETAIL: Permissions should be u=rwx (0700).

[[email protected] data]# echo >serverlog

[[email protected] data]# cat serverlog

FATAL: data directory "/usr/local/pgsql/data" has group or world access

DETAIL: Permissions should be u=rwx (0700).

LOG: could not bind socket for statistics collector: Cannot assign requested address

LOG: disabling statistics collector for lack of working socket

WARNING: autovacuum not started because of misconfiguration

HINT: Enable the "track_counts" option.

LOG: database system was shut down at 2009-12-01 16:11:55 CST

LOG: database system is ready to accept connections

[[email protected] data]# netstat -lnt

Active Internet connections (only servers)