PostgreSQL+Pgpool實現HA讀寫分離
環境說明和主機規劃：
作業系統 主機名 主機 角色 埠
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
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主機操作
初始化系統
[root@pgpool-tdb02 ~]# /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK
修改postgresql-10.service
[root@pgpool-tdb02 ~]# 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
基礎備份複製到備庫伺服器
rm -rf /data1/pg_data # 如果沒有重要資料可操作，主要為同步主庫路徑
[root@pgpool-tdb02 ~]# su - postgres -c 'pg_basebackup -D $PGDATA --
format=p -h master -p 54321 -U stream_replication -W'
關閉所有主機防火牆：(也可以在防火牆上設定埠限制)
如果你的系統上沒有安裝,使用命令安裝
安裝firewalld 防火牆yum install firewalld
開啟服務
關閉防火牆systemctl stop firewalld.service
開機自動啟動systemctl enable firewalld.service
關閉開機制動啟動systemctl disable firewalld.service
賦權：
[root@pgpool-tdb01 pg_data]# chown postgres.postgres pg_hba.conf.bak
[root@pgpool-tdb01 pg_data]# chown postgres.postgres postgresql.conf.bak
error：
pg_basebackup: could not connect to server: could not connect to server: No route
to host
Is the server running on host "master" (10.0.40.191) and accepting
TCP/IP connections on port 54321?
pg_basebackup: could not connect to server: FATAL: number of requested standby
connections exceeds max_wal_senders (currently 1)
pg_basebackup: could not get write-ahead log end position from server: ERROR:
could not open file "./pg_hba.conf.bak": Permission denied
以上報錯
需要關閉防火牆或在防火牆上設定埠限制。
[root@pgpool-tdb02 ~]# su - postgres -c 'pg_basebackup -D $PGDATA --
format=p -h master -p 54321 -U stream_replication -W'
Password:
[root@pgpool-tdb02 ~]# ll /data1/
total 4
drwxr-xr-x. 2 postgres postgres 6 Feb 17 19:03 pg_bin
drwx------. 21 postgres postgres 4096 Feb 17 21:18 pg_data
drwxr-xr-x. 2 postgres postgres 6 Feb 17 19:03 pg_logs
修改備庫配置資訊：
cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf
vi $PGDATA/recovery.conf
[postgres@pgpool-tdb02 ~]$ vi $PGDATA/recovery.conf
增加以下內容
standby_mode='on'
primary_conninfo = 'host=master port=54321 user=stream_replication
password=1qaz.com'
restore_command = ''
recovery_target_timeline = 'latest'
重啟PG服務:
systemctl restart postgresql-10
systemctl enable postgresql-10.service
驗證
主節點執行
create table test (id int4, create_time timestamp(0) without time zone);
insert into test values (3, now());
select * from test;
備節點執行
select * from test;
其他查詢
進入測試資料庫test，主庫上執行如下命令返回f，備庫上返回t。
select pg_is_in_recovery();
執行如下命令檢視快照，它返回主庫記錄點、備庫記錄點；主庫每增加一條寫入，記錄點的
值就會加1。
select txid_current_snapshot();
執行如下命令可以檢視主備同步狀態。
select * from pg_stat_replication;
欄位state顯示的同步狀態有：startup（連線中）、catchup（同步中）、streaming（同
步）；欄位sync_state顯示的模式有：async（非同步）、sync（同步）、potential（雖然現
在是非同步模式，但是有可能升級到同步模式）。
主：
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
備：
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
主備切換
假設主庫崩潰了，備庫如何從只讀狀態切換為讀寫狀態呢？只要把備庫的
postgresql.conf中hot_standby修改為off，並且刪除recovery.conf，然後重啟
庫就可以提供服務了。
PGPool2（pool主機操作）
安裝PGPool2
yum install -y
x86_64/pgpool-II-release-3.6-1.noarch.rpm
yum -y install pgpool-II-pg96 pgpool-II-pg96-debuginfo pgpool-II-pg96-devel
pgpool-II-pg96-extensions
systemctl enable pgpool.service #開啟自動啟動
下面的可以都安裝：
yum install -y x86_64/pgpool-
II-pg10-3.6.14-1pgdg.rhel7.x86_64.rpm
yum install -y x86_64/pgpool-
II-pg10-debuginfo-3.6.14-1pgdg.rhel7.x86_64.rpm
yum install -y x86_64/pgpool-
II-pg10-devel-3.6.14-1pgdg.rhel7.x86_64.rpm
yum install -y x86_64/pgpool-
II-pg10-extensions-3.6.14-1pgdg.rhel7.x86_64.rpm
yum install -y x86_64/pgpool-
II-release-3.6-1.noarch.rpm
yum -y install pgpool-II-pg10 pgpool-II-pg10-debuginfo pgpool-II-pg10-devel
pgpool-II-pg10-extensions
systemctl enable pgpool.service #開啟自動啟動
新增Pgpool-II執行使用者
useradd postgres # 環境準備時已操作
chown -R postgres.postgres /etc/pgpool-II
chown -R postgres.postgres /var/run/pgpool/
配置pool_hba.conf
cp /etc/pgpool-II/pool_hba.conf{,.bak}
vi /etc/pgpool-II/pool_hba.conf
增加內容
host all all 0.0.0.0/0 md5
配置pcp.conf
主節點登陸後執行：
postgres=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
--------------------+-------------------------------------
pg_signal_backend |
srcheck | md5662c10f61b27a9ab38ce69157186b25f
postgres | md5d3612d57ee8d4c147cf27b11e3a0974d
stream_replication | md59279ef6b904bc483e4f85e6d44cfc0ed
(4 rows)
[root@pgpool-tdb01 ~]# su - postgres
Last login: Mon Feb 18 00:05:49 CST 2019 on pts/0
[postgres@pgpool-tdb01 ~]$ psql
psql (10.7)
Type "help" for help.
主節點上執行：
要postgres使用者有MD5值,給postgres使用者加密碼。
alter user postgres with password '123';
postgres=# create user devuser with password 'XXXX';
CREATE ROLE
postgres=# create database wmsexpressdb;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE wmsexpressdb to devuser;
GRANT
postgres=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
----------------------+-------------------------------------
pg_monitor |
pg_read_all_settings |
pg_read_all_stats |
pg_stat_scan_tables |
pg_signal_backend |
stream_replication | md5b90626724a074c98507b58e9937298cb
srcheck | md544f2f6847b7c760bfb8331b345d2591f
postgres | md5289451de0ccee765f70bb6146e4d6c20
devuser | md5e75b559c51552b755247ecae024a6f24
(9 rows)
加入MD5認證：
[root@wmsexpress-pgpool01 pgpool-II]# vi pool_passwd
stream_replication:md5b90626724a074c98507b58e9937298cb
srcheck:md544f2f6847b7c760bfb8331b345d2591f
postgres:md5289451de0ccee765f70bb6146e4d6c20
devuser:md5e75b559c51552b755247ecae024a6f24
重啟pgpool 讓設定生效：
[root@wmsexpress-pgpool01 pgpool-II]# psql -p 54321 -h 10.0.4.37 -U devuser -d
postgres
psql: FATAL: md5 authentication failed
DETAIL: pool_passwd file does not contain an entry for "devuser"
[root@wmsexpress-pgpool01 pgpool-II]# pgpool stop
2019-03-04 11:56:08: pid 16620: LOG: stop request sent to pgpool. waiting for
termination...
.done.
[root@wmsexpress-pgpool01 pgpool-II]# pgpool -C -D
[root@wmsexpress-pgpool01 pgpool-II]# psql -p 54321 -h 10.0.4.37 -U devuser -d
postgres
Password for user devuser:
psql (10.7)
Type "help" for help.
postgres=>
postgres=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
----------------------+-------------------------------------
pg_monitor |
pg_read_all_settings |
pg_read_all_stats |
pg_stat_scan_tables |
pg_signal_backend |
stream_replication | md5cbdf308c01aeea690cefb040a85dd4ee
srcheck | md5ed70f2fcca0c9060ac658734adbae8bb
postgres | md510220e27448e3d1bf2531c9d99ad7b9e
(8 rows)
[root@pgpool-t01 pgpool-II]# vi /etc/pgpool-II/pool_passwd
增加SQL執行結果的內容，形式為$rolname:$rolpassword例如：
srcheck:md5662c10f61b27a9ab38ce69157186b25f #建議用這個方式
1
或者：
pg_md5 -u postgres your_password
vi /etc/pgpool-II/pcp.conf ## 加入 postgres:上一命令的輸出
增加devuser，並設定superuser許可權，加入MD5認證。
配置pgpool.conf
cp /etc/pgpool-II/pgpool.conf{,.bak}
vi /etc/pgpool-II/pgpool.conf
# CONNECTIONS
listen_addresses = '*'
port = 54321
socket_dir = '/var/run/pgpool'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/pgpool'
# - Backend Connection Settings -
backend_hostname0 = 'master'
backend_port0 = 54321
backend_weight0 = 1
backend_data_directory0 = '/data1/pg_data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'slave'
backend_port1 = 54321
backend_weight1 = 1
backend_data_directory1 = '/data1/pg_data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
# FILE LOCATIONS
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/data1/pg_logs'
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 5
sr_check_user = 'srcheck'
sr_check_password = 'XXXX'
sr_check_database = 'postgres'
# HEALTH CHECK 健康檢查
health_check_period = 10
health_check_timeout = 20
health_check_user = 'srcheck'
health_check_password = 'XXXX'
health_check_database = 'postgres'
# FAILOVER AND FAILBACK
failover_command = '/data1/pg_bin/failover_stream.sh %H'
failover_stream.sh指令碼
vim /data1/pg_bin/failover_stream.sh
[root@pgpool-t01 pg_bin]# chown postgres.postgres failover_stream.sh
chmod 777 /data1/pg_bin/failover_stream.sh
chmod u+s /sbin/ifconfig
chmod u+s /usr/sbin
pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 & ## 啟動
pgpool -m fast stop ## 關閉
[root@pgpool-t01 ~]# pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 &
[1] 3404
[root@pgpool-t01 ~]# pgpool -m fast stop
2019-02-18 00:49:29: pid 3439: LOG: stop request sent to pgpool. waiting for
termination...
.done.
[1]+ Done pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1
failover_stream.sh內容：
#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
new_master=$1
trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA"
# Prompte standby database.
/usr/bin/ssh -T $new_master $trigger_command
exit 0;
登陸設定
當執行 pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 & 後可檢視叢集狀態：
[root@pgpool-t01 ~]# psql -p 54321 -h 10.0.40.193 -U srcheck -d
postgres
Password for user srcheck:
psql (10.7)
Type "help" for help.
postgres=> show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt |
load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+---
----------------+-------------------
0 | master | 54321 | up | 0.500000 | primary | 0 | true
| 0
1 | slave | 54321 | up | 0.500000 | standby | 0 | false
| 0
(2 rows)
如果未發現叢集狀態，請在master和slave主機分別執行以下操作：
[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 9898 -n 0
[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 9898 -n 1
#詳情查詢命令pcp_attach_node
檢視pgpool的服務狀態：
[root@pgpool-t01 pgpool-II]# systemctl status pgpool.service
[root@pgpool-t01 pgpool-II]# systemctl status pgpool
[root@pgpool-t01 pgpool-II]# systemctl start pgpool.service
[root@pgpool-t01 pgpool-II]# systemctl start pgpool
[root@pgpool-t01 pgpool-II]# systemctl stop pgpool.service
測試SQL：
insert into test values (20,now());
select * from test;
psql -p 54321 -h 10.0.40.193 -U srcheck -d postgres
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
所有應用連線到PGPOOL節點，寫入會話會自動分配到PG主節點，只讀會話會以會話級平
均分配到PG主從兩個節點上。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22996654/viewspace-2664343/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Amoeba實現讀寫分離
- 使用Consul和ProxySQL實現MySQL HA讀寫分離高可用性MySql
- ProxySQL實現MySQL讀寫分離MySql
- Amoeba 實現 MySQL 讀寫分離MySql
- CQRS如何實現讀寫分離
- Amoeba+Mysql 實現讀寫分離MySql
- 【Mongo】Mongo讀寫分離的實現Go
- springboot實現讀寫分離Spring Boot
- mysql-proxy實現讀寫分離MySql
- 位元組面試：什麼是讀寫分離？讀寫分離的底層如何實現？面試
- docker+atlas+mysql實現讀寫分離DockerMySql
- Docker實現Mariadb分庫分表、讀寫分離Docker
- 搭建MySQL主從實現Django讀寫分離MySqlDjango
- MHA+ProxySQL實現讀寫分離高可用SQL
- ProxySQL實現Mysql讀寫分離 - 部署手冊MySql
- MySQL 中介軟體Atlas 實現讀寫分離MySql
- Spring實現資料庫讀寫分離Spring資料庫
- StoneDB 讀寫分離實踐方案
- Mycat讀寫分離配置實踐
- redis客戶端實現高可用讀寫分離Redis客戶端
- Spring Aop實現資料庫讀寫分離Spring資料庫
- Mycat實現mysql的負載均衡讀寫分離MySql負載
- Spring配置RoutingDataSource實現mysql讀寫分離SpringMySql
- mysql proxy 安裝及配置實現讀寫分離MySql
- 基於Sharding-Jdbc 實現的讀寫分離實現JDBC
- 搭建基於springmvc，ibatis的工程實現讀寫分離，配置分離SpringMVCBAT
- 關於Dapper實現讀寫分離的個人思考APP
- Mycat中介軟體實現Percona Cluster讀寫分離
- Mycat中介軟體實現Mysql主從讀寫分離MySql
- 資料庫讀寫分離，主從同步實現方法資料庫主從同步
- SpringBoot 專案優雅實現讀寫分離Spring Boot
- mycat結合MySQL的雙主實現讀寫分離MySql
- MySQL怎麼實現主從同步和Django實現MySQL讀寫分離MySql主從同步Django
- Redis的讀寫分離Redis
- Laravel讀寫分離原理Laravel
- MySQL Amoeba讀寫分離MySql
- Amoeba for mysql讀寫分離MySql
- MySQL讀寫分離AtlasMySql