postgresql9.2.4 离线安装

1、创建用户

[root@vkeep ~]# groupadd postgres
[root@vkeep ~]# useradd -g postgres postgres -m -s /bin/bash
[root@vkeep ~]# echo "Database@123" | passwd --stdin postgres

2、安装依赖包

[root@vkeep ~]# yum install gcc gcc-c++ zlib-devel readline readline-devel perl-ExtUtils-Embed pam-devel openssl openssl-devel cmake libxslt-devel libxml2-devel openldap-devel python-devel tcl tcl-devel bison flex xmlto -y

3、创建数据库目录

[root@vkeep ~]# mkdir -p /data/postgres/{soft,postgresql,pgdata}
[root@vkeep ~]# chown -R postgres: /data

4、创建环境变量

[root@vkeep ~]# su - postgres
[postgres@vkeep ~]$ echo 'export PGPORT=5432
export PG_HOME=/data/postgres/postgresql
export PATH=$PG_HOME/bin:$PATH
export PGDATA=$PG_HOME/pgdata
export LD_LIBRARY_PATH=$PG_HOME/lib
export LANG=en_US.utf8
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
' >> ~/.bash_profile && source  ~/.bash_profile

5、上传源码包，并解压

# 解压源码包
[postgres@vkeep ~]$ cd /data/postgres/soft/
[postgres@vkeep soft]$ tar -zxf postgresql-9.2.4.tar.gz

6、编译源码包

[postgres@vkeep soft]$ cd postgresql-9.2.4/
[postgres@vkeep postgresql-9.2.4]$ ./configure --prefix=/data/postgres/postgresql --with-pgport=5432 --with-openssl --with-perl \
--with-tcl --with-python --with-pam --without-ldap --with-libxml --with-libxslt \
--enable-thread-safety --with-wal-blocksize=16 --with-blocksize=8

注：
1、--with-blocksize 数据库需要经常做插入的操作，数据量增长非常快，尽量把此参数设大一点；经常做小数据查询、更新且内存不是非常大的时候可以设小一点，默认8K即可
2、生产环境不要加 --enable-dtrace --enable-debug ，影响数据库性能

7、安装数据库

[postgres@vkeep postgresql-9.2.4]$ make && make install

8、初始化数据库

[postgres@vkeep postgresql-9.2.4]$ # 创建目录
[postgres@vkeep postgresql-9.2.4]$ mkdir $PG_HOME/pgdata
[postgres@vkeep postgresql-9.2.4]$ # 初始化数据库集簇
[postgres@vkeep postgresql-9.2.4]$ initdb -D $PGDATA -W 

9、启动数据库

[postgres@vkeep postgresql-9.2.4]$ pg_ctl -D $PGDATA start

10、创建数据库用户和数据库

[postgres@vkeep pgdata]$ psql -d postgres -p 5432
postgres=# create database testdb;
postgres=# create user keep with Superuser password  'keep';

11、配置远程连接

# 配置连接

[postgres@vkeep pgdata]$ cd $PGDATA
[postgres@vkeep pgdata]$ echo "listen_addresses = '*'
port=5432
unix_socket_directories='/data/postgres/postgresql/pgdata'
" >> postgresql.conf
[postgres@vkeep pgdata]$ echo 'host all all 0.0.0.0/0 md5' >> pg_hba.conf

# 重启数据库

[postgres@vkeep pgdata]$ pg_ctl stop
[postgres@vkeep pgdata]$ pg_ctl -D $PGDATA start

# 验证远程连接

[postgres@vkeep pgdata]$ psql -d testdb -U keep -p 5432 -h 192.168.140.96