源码编译Postgesql 9.6.3 并启用远程连接

1、下载安装包postgresql 9.6.3


地址：http://ftp.stu.edu.tw/pub/postgresql/latest/


rpm下载地址：


https://yum.postgresql.org/9.6/redhat/rhel-6.4-x86_64/


2、软件下载


在root 用户下 cd /opt/soft/ 
wget https://ftp.postgresql.org/pub/source/v9.6.3/postgresql-9.6.3.tar.bz2




3、解压


[root@pgdatat]# tar -zxvf postgresql-9.6.3.tar.gz
3.1 安装依赖包


[root@pg96 soft]# yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel  python-devel gcc-c++ openssl-devel cmake


[root@pgdata]# yum -y install gcc* readline-devel


3.2 增加用户设置密码：


[root@pg96 ~]# adduser postgres


adduser：用户“postgres”已存在 
[root@pg96 ~]# passwd postgres 
更改用户 postgres 的密码 。 
新的 密码： 
无效的密码： 它基于字典单词 
重新输入新的 密码： 
passwd： 所有的身份验证令牌已经成功更新。


[root@pgdata~]# id postgres
uid=26(postgres) gid=26(postgres) 组=26(postgres)
[root@pgdata ~]#




4、配置编译选项


[root@pgdatapostgresql-9.6.3]# ./configure --prefix=/opt/pgsql9.6.3 --with-pgport=5432 --with-perl  --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt  --with-wal-blocksize=8 --with-blocksize=8 --enable-debug --enable-cassert && gmake world


5、编译


[root@pgdata]# gmake
[root@pgdata# gmake install


6、修改postgres用户的环境变量
修改postgres用户的环境变量：


 vim ~/.bash_profile


PATH=$PATH:$HOME/bin:/opt/pgsql9.6.3/bin


export PATH




source ~/.bash_profile


7、初始化数据库
新建数据目录：
[root ]#  mkdir -p  /home/postgres/pgsql/data
更改权限：
[root]# chown postgres:postgres /home/postgres/pgsql/data


#初始化数据库
[postgres@pgdata]$/opt/pgsql9.6.3/bin/initdb -D /home/postgres/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.


The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".


Data page checksums are disabled.


fixing permissions on existing directory /home/postgres/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.


Success. You can now start the database server using:


    /opt/pgsql9.6.3/bin/pg_ctl -D /home/postgres/pgsql/data -l logfile start


[postgres@pgdata  ~]$ 




8、启动测试：


 /opt/pgsql9.6.3/bin/pg_ctl -D /home/postgres/pgsql/data -l logfile start
[postgres@pgdata ~]$psql
psql (9.6.3)
Type "help" for help.


postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)


postgres=# \q
[postgres@pg96 bin]$pg_ctl stop
LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
waiting for server to shut down....LOG:  database system is shut down
 done
server stopped




9、修改Postgresql用户的密码


步骤一：登录PostgreSQL



sudo -u postgres psql
步骤二：修改登录PostgreSQL密码


ALTER USER postgres WITH PASSWORD 'postgres';
注：


密码postgres要用引号引起来
命令最后有分号
步骤三：退出PostgreSQL客户端


\q


10、设置远程连接

1.postgresql.conf

vi /home/postgres/pgsql/data/postgresql.conf
修改成监听所有ip地址的连接请求，如下：
listen_addresses = '*'

2.pg_hba.conf

vi /home/postgres/pgsql/data/pg_hba.conf
在末尾的地方添加一行，如下：
host    all         all         0.0.0.0/0      md5