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

日志：

[root@java-pmsql-201 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).

[root@java-pmsql-201 data]# echo >serverlog

[root@java-pmsql-201 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

[root@java-pmsql-201 data]# netstat -lnt

Active Internet connections (only servers)