环境说明和主机规划：
操作系统 主机名 主机 角色 端口
CentOS 7.3 master 10.0.40.191 PG-Master 54321
CentOS 7.3 slave 10.0.40.192 PG-Slave 54321
CentOS 7.3 pool 10.0.40.193 pgpool 54321
基础环境配置（所有主机操作）
配置HOSTS
echo -e "10.0.40.191 master\n10.0.40.192 slave\n10.0.40.193 pool" >> /etc/hosts #
执行一次即可
[root@pgpool-tdb01 ~]# echo -e "10.0.40.191 master\n10.0.40.192
slave\n10.0.40.193 pool" >> /etc/hosts
[root@pgpool-tdb01 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.40.191 master
10.0.40.192 slave
10.0.40.193 pool
配置统一的时间（若已配置，请忽略）
也可以配置本地自己的ntp服务器。
yum install -y ntpdate && ntpdate ntp1.aliyun.com
echo -e "# sync time from ntp1.aliyun.com\n5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com >
/dev/null 2>&1
" >> /var/spool/cron/root # 写入定时任务，执行一次即可
[root@pgpool-tdb01 ~]# yum install -y ntpdate && ntpdate ntp1.aliyun.com
Loaded plugins: fastestmirror, langpacks
......
Dependency Updated:
openssl.x86_64 1:1.0.2k-16.el7 openssl-libs.x86_64 1:1.0.2k-
16.el7
Complete!
17 Feb 17:15:16 ntpdate[32460]: step time server 120.25.115.20 offset 1.439184 sec
[root@pgpool-tdb01 ~]# echo -e "# sync time from ntp1.aliyun.com\n5 * * * *
/usr/sbin/ntpdate ntp1.aliyun.com > /dev/null 2>&1" >> /var/spool/cron/root
[root@pgpool-tdb01 ~]# cd /var/spool/cron
[root@pgpool-tdb01 cron]# ll
total 4
-rw-r--r--. 1 root root 94 Feb 17 17:29 root
[root@pgpool-tdb01 cron]# cat root
# sync time from ntp1.aliyun.com
5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com > /dev/null 2>&1
[root@pgpool-tdb01 cron]# crontab -l
# sync time from ntp1.aliyun.com
5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com > /dev/null 2>&1
创建postgres用户:
useradd postgres && echo "your_password" | passwd --stdin postgres
useradd postgres && echo "XXXX" | passwd --stdin postgres
[root@pgpool-tdb02 ~]# useradd postgres && echo "XXXX" | passwd --stdin
postgres
Changing password for user postgres.
passwd: all authentication tokens updated successfully.
配置免密钥登陆:
su - postgres
ssh-keygen -t rsa -f /home/postgres/.ssh/id_rsa -P ""
cd ~/.ssh/
ssh-copy-id postgres@master # 三台主机执行
scp authorized_keys postgres@slave:~/.ssh # 只在master主机执行
scp authorized_keys postgres@pool:~/.ssh # 只在master主机执行
[root@pgpool-tdb01 ~]# su - postgres
[postgres@pgpool-tdb01 ~]$ ssh-keygen -t rsa -f /home/postgres/.ssh/id_rsa -P ""
Generating public/private rsa key pair.
Created directory '/home/postgres/.ssh'.
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
d7:39:7d:00:55:47:0f:4f:bf:ae:85:31:9c:f6:e8:9f postgres@pgpool-tdb01
The key's randomart image is:
+--[ RSA 2048]----+
| ..oo=|
| . ++|
| . +|
| ..o...|
| S . +*...|
| . ..B. |
| o + |
| . o .|
| o.E |
+-----------------+
[postgres@pgpool-tdb01 ~]$ cd ~/.ssh/
[postgres@pgpool-tdb01 .ssh]$ ssh-copy-id postgres@master
The authenticity of host 'master (10.0.40.191)' can't be established.
ECDSA key fingerprint is 1a:f7:cc:f3:f8:4e:af:40:cf:fe:26:f6:12:7e:5f:46.
Are you sure you want to continue connecting (yes/no)? y
Please type 'yes' or 'no': yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any
that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now
it is to install the new keys
postgres@master's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'postgres@master'"
and check to make sure that only the key(s) you wanted were added.
[postgres@pgpool-tdb01 .ssh]$ scp authorized_keys postgres@slave:~/.ssh
The authenticity of host 'slave (10.0.40.192)' can't be established.
ECDSA key fingerprint is 37:ec:17:87:c6:8e:ee:6a:ba:47:a5:22:38:65:6b:21.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'slave,10.0.40.192' (ECDSA) to the list of known hosts.
postgres@slave's password:
authorized_keys 100%
1207 1.2KB/s 00:00
[postgres@pgpool-tdb01 .ssh]$ scp authorized_keys postgres@pool:~/.ssh
The authenticity of host 'pool (10.0.40.193)' can't be established.
ECDSA key fingerprint is 95:2e:fd:0c:2f:4e:54:4d:1f:5e:eb:b3:95:61:aa:5d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'pool,10.0.40.193' (ECDSA) to the list of known hosts.
postgres@pool's password:
authorized_keys
安装Postgresql数据库（PG10.7）
yum安装：
[root@pgpool-tdb01 ~]# yum install
https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-
x86_64/pgdg-centos10-10-2.noarch.rpm -y
[root@pgpool-tdb01 ~]# yum install postgresql10-contrib postgresql10-server
postgresql10 postgresql10-libs -y
创建统一的目录结构：
[root@pgpool-tdb01 ~]# mkdir /data1/pg_{data,bin,logs} -p
[root@pgpool-tdb01 ~]# ll /data1/
total 0
drwxr-xr-x. 2 root root 6 Feb 17 19:00 pg_bin
drwxr-xr-x. 2 root root 6 Feb 17 19:00 pg_data
drwxr-xr-x. 2 root root 6 Feb 17 19:00 pg_logs
[root@pgpool-tdb01 ~]# chown -R postgres.postgres /data1/
修改系统变量：
vi /etc/profile #增加以下内容
export PGHOME=/usr/pgsql-10/
export PGDATA=/data1/pg_data
export PGPORT=54321
export PATH=$PATH:$PGHOME/bin
# 生效
source /etc/profile
PostgreSQL流复制结构（master和slave主机操
作）
master主机操作
初始化系统
[root@pgpool-tdb01 ~]# /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK
删除pg_data后重新初始化：
[postgres@wmsexpress-db01 ~]$ pg_ctl -D /data1/pg_data/ initdb
修改postgresql-10.service
vi /usr/lib/systemd/system/postgresql-10.service
# Include the default config:
#.include /usr/lib/systemd/system/postgresql-10.service
[Service]
Environment=PGDATA=/data1/pg_data
重启PG服务：
systemctl daemon-reload
su - postgres -c '/usr/pgsql-10/bin/initdb -D /data1/pg_data'
systemctl restart postgresql-10
systemctl enable postgresql-10.service
systemctl status postgresql-10
/usr/pgsql-10/bin/pg_ctl -D /data1/pg_data -l logfile start
每次改postgresql.conf，需要执行下面的stop命令，再重启pg服务。
/usr/pgsql-10/bin/pg_ctl -D /data1/pg_data -l logfile stop
修改系统配置（以下用postgres用户操作）：
也可以用root账号操作，注意目录权限。
[root@pgpool-tdb01 pg_data]# cp /data1/pg_data/pg_hba.conf{,.bak}
[root@pgpool-tdb01 pg_data]# cat >/data1/pg_data/pg_hba.conf<<EOF
> local all all trust
> host all all 10.0.40.191/32 trust
> host all all 10.0.40.192/32 trust
> host all all 0.0.0.0/0 md5
> host all all ::1/128 trust
> host replication stream_replication 0.0.0.0/0 md5
> EOF
cp /data1/pg_data/postgresql.conf{,.bak}
cat >/data1/pg_data/postgresql.conf<<EOF
listen_addresses = '*'
port = 54321
max_connections = 3000
shared_buffers = 1GB
effective_cache_size = 2GB
work_mem = 64MB
maintenance_work_mem = 128MB
min_wal_size = 128MB
max_wal_size = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
wal_level = hot_standby
wal_log_hints = on
max_wal_senders = 10
hot_standby = on
logging_collector = on
log_directory = 'pg_log'
log_timezone = 'PRC'
timezone = 'PRC'
EOF
#操作完记得重启 pg_ctl restart
[root@pgpool-tdb01 ~]# cat >/data1/pg_data/postgresql.conf<<EOF
> listen_addresses = '*'
> port = 54321
> max_connections = 200
> shared_buffers = 512MB
> effective_cache_size = 2GB
> work_mem = 64MB
> maintenance_work_mem = 256MB
> min_wal_size = 256MB
> max_wal_size = 512MB
> checkpoint_completion_target = 0.9
> wal_buffers = 16MB
> default_statistics_target = 100
> wal_level = hot_standby
> wal_log_hints = on
> max_wal_senders = 1
> hot_standby = on
> logging_collector = on
> log_directory = 'pg_log'
> EOF
[root@pgpool-tdb01 ~]# su - postgres
Last login: Sun Feb 17 20:02:31 CST 2019 on pts/0
[postgres@pgpool-tdb01 ~]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-02-17 12:25:10.486 GMT [3174] LOG: listening on
IPv4 address "0.0.0.0", port 54321
2019-02-17 12:25:10.486 GMT [3174] LOG: listening on IPv6 address "::", port
54321
2019-02-17 12:25:10.489 GMT [3174] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.54321"
2019-02-17 12:25:10.492 GMT [3174] LOG: listening on Unix socket
"/tmp/.s.PGSQL.54321"
2019-02-17 12:25:10.520 GMT [3174] LOG: redirecting log output to logging
collector process
2019-02-17 12:25:10.520 GMT [3174] HINT: Future log output will appear in
directory "pg_log".
done
server started
在主库中创建流复制用户(stream_replication)和
PGPool用户(srcheck)：
[postgres@pgpool-tdb01 ~]$ psql
psql (10.7)
Type "help" for help.
postgres=# psql
postgres-# \q
[postgres@pgpool-tdb01 ~]$ psql
psql (10.7)
Type "help" for help.
postgres=# CREATE USER stream_replication replication LOGIN CONNECTION
LIMIT 5 ENCRYPTED PASSWORD 'XXXX';
CREATE ROLE
postgres=# CREATE USER srcheck replication LOGIN CONNECTION LIMIT 5
ENCRYPTED PASSWORD 'XXXX';
CREATE ROLE
修改主库pg_hba.conf文件（已操作见cat
>/data1/pg_data/pg_hba.conf<<EOF）：
host replication stream_replication 0.0.0.0/0 md5
slave主机操作
初始化系统