pg14+pgpool-II- 4.2.4高可用

luffy-0824 / 2023-08-21 / 原文

一、环境配置

三个节点安装数据库软件；三个节点安装pgpool软件；仅主库节点初始化数据库；三个节点修改pgpool配置文件
（若未指出在主节点操作，其余操作均在三个节点进行）

IP
主机名
作用
port
类型
备注
192.168.128.130
node01
主库
5432
写
对外提供写
192.168.128.131
node02
备库
5432
读
对外提供读
192.168.128.132
node03
备库
5432
读
对外提供读
192.168.128.135
  
虚拟IP
    
vip
数据库版本：14.8
os版本：centos 7.9
pgpool-ll版本：4.2.4

1.1 软件准备

软件下载

https://www.postgresql.org/ftp/source/
https://pgpool.net/mediawiki/index.php/Downloads

PostgreSQL版本：postgresql-14.8.tar.gz
pgpool版本：pgpool-II-4.2.4.tar.gz

1.2 配置selinux

setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config

1.3 关闭防火墙

systemctl stop firewalld.service
systemctl disable firewalld

1.4 安装软件依赖包

yum -y install gcc*
yum -y install python*
yum -y install perl-ExtUtils-Embed*
yum -y install zlib-devel*
yum -y install readline*

1.5 配置hosts文件

echo "192.168.128.130 node01" >> /etc/hosts
echo "192.168.128.131 node02" >> /etc/hosts
echo "192.168.128.132 node03" >> /etc/hosts

二、安装PostgreSQL数据库

2.1 创建用户及目录

创建用户
useradd postgres
echo 'rdjc12#$' | passwd --stdin postgres

创建相关目录
mkdir -p /data/pg_archive 
mkdir -p /data/pg_data
mkdir -p /data/pg_log
mkdir -p /data/pgpool_log
chown -R postgres:postgres /data

2.2 安装数据库软件

tar -xf postgresql-14.8.tar.gz
cd postgresql-14.8
./configure --prefix=/usr/local/pgsql
gmake world
gmake install-world

2.3 修改环境变量

/root/.bashrc和/home/postgres/.bashrc配置
export PGPORT=5432
export PGDATA=/data/pg_data
export PGHOME=/usr/local/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGDATABASE=postgres

三、安装pgpool软件

tar -xf pgpool-II-4.2.4.tar.gz
cd pgpool-II-4.2.4/
./configure --prefix=/usr/local/pgpool
make && make install

四、修改配置文件

4.1 PostgreSQL数据库配置

4.1.1 初始化数据库

仅在主节点node01初始化数据库
[root@node01 ~]# su - postgres 
[postgres@node01 ~]$ initdb -D /data/pg_data -U postgres -W

4.1.2 配置postgresql.conf文件

listen_addresses = '*'
archive_mode = on
archive_command = 'test ! -f /data/pg_archive/%f && cp %p /data/pg_archive/%f '
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on

logging_collector = on
log_statement=ddl
log_destination=stderr
log_directory='/data/pg_log'
log_filename='postgres-%d.log'
log_truncate_on_rotation=on
log_rotation_age=1d
log_rotation_size=10MB
log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'
log_checkpoints=on
log_lock_waits=on
log_autovacuum_min_duration=0
log_temp_files=0
lc_messages='C'

4.1.3 创建用户

set password_encryption = md5;
CREATE user pgpool  password '123456';
CREATE user repl replication  password '123456';
alter user postgres password '123456';
GRANT pg_monitor TO pgpool;

4.1.4 配置pg_hba.conf文件

host all all 192.168.128.1/24 md5 
host replication all 192.168.128.1/24 md5 


pg_ctl reload

4.2 pgpool配置

4.2.1 复制配置文件

cd /usr/local/pgpool/etc
cp pool_hba.conf.sample pool_hba.conf
cp pcp.conf.sample pcp.conf
cp pgpool.conf.sample-stream pgpool.conf
cp follow_primary.sh.sample follow_primary.sh
cp failover.sh.sample failover.sh
chmod +x follow_primary.sh
chmod +x failover.sh

cp escalation.sh.sample escalation.sh
chmod +x escalation.sh
chown postgres:postgres /usr/local/pgpool/etc/{failover.sh,follow_primary.sh,escalation.sh}

cp recovery_1st_stage.sample recovery_1st_stage
chmod +x recovery_1st_stage
mv recovery_1st_stage /data/pg_data/
cp pgpool_remote_start.sample pgpool_remote_start
chmod +x pgpool_remote_start
mv pgpool_remote_start /data/pg_data/
chown postgres:postgres /data/pg_data -R

4.2.2 配置环境变量

echo "export PATH=$PATH:/usr/local/pgpool/bin/">> /etc/profile; . /etc/profile

4.2.3 配置failover.sh文件

vim /usr/local/pgpool/etc/failover.sh 
仅修改为： PGHOME=/usr/local/pgsql

4.2.4 配置recovery_1st_stage文件

vim  /data/pg_data/recovery_1st_stage
#修改为：
PGHOME=/usr/local/pgsql
ARCHIVEDIR=/data/pg_archive/
REPLUSER=repl

#添加-R【在53行左右的pg_basebackup的后面加上-R参数】
${PGHOME}/bin/pg_basebackup -h $PRIMARY_NODE_HOST -U $REPLUSER -p $PRIMARY_NODE_PORT -D $DEST_NODE_PGDATA -X stream -R

#第56行左右的.pgpass文件路径由/var/lib/pgsql/.pgpass更改为 ~/.pgpass
primary_conninfo = 'host=${PRIMARY_NODE_HOST} port=${PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${DEST_NODE_HOST} passfile=''~/.pgpass'''

4.2.5 配置pgpool_remote_start文件

vim /data/pg_data/pgpool_remote_start 
修改为： PGHOME=/usr/local/pgsql/

4.2.6 配置follow_primary.sh文件

vim /usr/local/pgpool/etc/follow_primary.sh
#修改为：
PGHOME=/usr/local/pgsql/
ARCHIVEDIR=/data/pg_archive
REPLUSER=repl
PCP_USER=pgpool
PGPOOL_PATH=/usr/local/pgpool/bin/
PCP_PORT=9898
REPL_SLOT_NAME=${NODE_HOST//[-.]/_}
#超级用户
PGUSER_SUPER=postgres
#超级用户pgdb密码
PGUSER_SUPER_PWD='123456'
#dbname
PGUSER_SUPER_DBNAME=postgres
#复制用户
PGUSER_REPLI=repl
#复制用户密码
PGUSER_REPLI_PWD='123456'

#添加-R【在129行左右的pg_basebackup的后面加上-R参数】
${PGHOME}/bin/pg_basebackup -h $PRIMARY_NODE_HOST -U $REPLUSER -p $PRIMARY_NODE_PORT -D $DEST_NODE_PGDATA -X stream -R

#第101、132行左右的.pgpass文件路径由/var/lib/pgsql/.pgpass更改为 ~/.pgpass
primary_conninfo = 'host=${NEW_PRIMARY_NODE_HOST} port=${NEW_PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${NODE_HOST} passfile=''~/.pgpass'''

4.2.7 配置escalation.sh

vim /usr/local/pgpool/etc/escalation.sh
#修改 $VIP/24为$VIP/20 掩码;并且修改vip；以及DEVICE配置为机器自己的网卡
PGPOOLS=(node01 node02 node03)

VIP=192.168.128.135
DEVICE=ens33

for pgpool in "${PGPOOLS[@]}"; do
    [ "$HOSTNAME" = "$pgpool" ] && continue

    ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$pgpool -i ~/.ssh/id_rsa_pgpool "
        /usr/bin/sudo /sbin/ip addr del $VIP/20 dev $DEVICE
    "
done
exit 0

4.2.8 配置pool_hba.conf

vi /usr/local/pgpool/etc/pool_hba.conf 
host all pgpool 0.0.0.0/0 md5 
host all postgres 0.0.0.0/0 md5

4.2.9 配置密码

pg_md5 -p -m -u postgres pool_passwd
password: (输入密码123456)
pg_md5 -p -m -u pgpool pool_passwd
password: (输入密码123456)

cat /usr/local/pgpool/etc/pool_passwd
postgres:md5a3556571e93b0d20722ba62be61e8c2d
pgpool:md5a258db5c0f4f595eb0667066f0f4bb60

4.2.10 配置.pgpass文件

su - postgres
vim ~/.pgpass
node01:5432:replication:repl:123456
node02:5432:replication:repl:123456
node03:5432:replication:repl:123456
node01:5432:postgres:postgres:123456
node02:5432:postgres:postgres:123456
node03:5432:postgres:postgres:123456

 chmod 0600 ~/.pgpass

4.2.11 配置pcp.conf文件

echo 'pgpool:'`pg_md5 123456` >>/usr/local/pgpool/etc/pcp.conf
echo 'postgres:'`pg_md5 123456` >>/usr/local/pgpool/etc/pcp.conf

4.2.12 配置.pcppass文件

su - postgres
vim ~/.pcppass
localhost:9898:pgpool:123456
192.168.128.135:9898:pgpool:123456
node01:9898:pgpool:123456
node02:9898:pgpool:123456
node03:9898:pgpool:123456

chmod 600 ~/.pcppass

4.2.13 配置pgpool_node_id文件

从 Pgpool-II 4.2 中，现在所有主机的所有配置参数都相同。如果启用了监视器功能，以消除对哪个主机是哪个主机的干扰，则需要pgpool_node_id文件。
需要创建一个pgpool_node_id文件，并指定pgpool（看门狗）节点编号（例如0，1，2…）来识别pgpool（看门狗）主机。
node01：
[root@node01 ~]# echo "0" >> /usr/local/pgpool/etc/pgpool_node_id

node02：
[root@node02 ~]# echo "1" >> /usr/local/pgpool/etc/pgpool_node_id

node03：
[root@node03 ~]# echo "2" >> /usr/local/pgpool/etc/pgpool_node_id

4.2.14 安装插件pgpool_recovery

使用pgpool源码包进行编译

cd pgpool-II-4.2.4/src/sql/pgpool-recovery/
 make && make install

 4.2.15 配置pgpool.conf

# [CONNECTIONS]
listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
pcp_socket_dir = '/tmp'
## - Backend Connection Settings -
backend_hostname0 = 'node01'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data/pg_data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'node01'

backend_hostname1 = 'node02'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/pg_data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'node02'

backend_hostname2 = 'node03'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/data/pg_data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'node03'

## - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
# [LOGS]
logging_collector = on
log_directory = '/data/pgpool_log'
log_filename = 'pgpool-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 100MB
# [FILE LOCATIONS]
pid_file_name = '/tmp/pgpool.pid'
## 此目录用来存放 pgpool_status 文件，此文件保存集群状态（刷新有问题时会造成show pool_status不正确）
logdir = '/tmp'
# [Streaming REPLICATION MODE]
sr_check_user = 'pgpool'
sr_check_password = '123456'
follow_primary_command = '/usr/local/pgpool/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

# [HEALTH CHECK GLOBAL PARAMETERS]
health_check_period = 5
health_check_timeout = 20
health_check_user = 'pgpool'
## 为健康检查时查找 pool_passwd
health_check_password = '123456'
health_check_max_retries = 3
# [FAILOVER AND FAILBACK]
failover_command = '/usr/local/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
# [ONLINE RECOVERY]
recovery_user = 'postgres'
recovery_password = '123456'
recovery_1st_stage_command = 'recovery_1st_stage'
# [WATCHDOG]
use_watchdog = on
hostname0 = 'node01'
wd_port0 = 9000
pgpool_port0 = 9999

hostname1 = 'node02'
wd_port1 = 9000
pgpool_port1 = 9999

hostname2 = 'node03'
wd_port2 = 9000
pgpool_port2 = 9999

wd_ipc_socket_dir = '/tmp'

## - Virtual IP control Setting -
delegate_IP = '192.168.128.135'
## - Behaivor on escalation Setting -
wd_escalation_command = '/usr/local/pgpool/etc/escalation.sh'

## - Lifecheck Setting -
wd_lifecheck_method = 'heartbeat'
### -- heartbeat mode --
heartbeat_hostname0 = 'node01'
heartbeat_port0 = 9694
heartbeat_device0 = ''

heartbeat_hostname1 = 'node02'
heartbeat_port1 = 9694
heartbeat_device1 = ''

heartbeat_hostname2 = 'node03'
heartbeat_port2 = 9694
heartbeat_device2 = ''

4.3 配置互信

（分别在root和postgres用户下面进行配置）
root:
mkdir ~/.ssh
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@node01
ssh-copy-id -i id_rsa_pgpool.pub postgres@node02
ssh-copy-id -i id_rsa_pgpool.pub postgres@node03

postgres:
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@node01
ssh-copy-id -i id_rsa_pgpool.pub postgres@node02
ssh-copy-id -i id_rsa_pgpool.pub postgres@node03


chmod 600 ~/.ssh/* 
chmod 644 ~/.ssh/*.pub 
chmod 700 ~/.ssh
测试看是否免密登录：
ssh postgres@node01  -i ~/.ssh/id_rsa_pgpool    date
ssh postgres@node02  -i ~/.ssh/id_rsa_pgpool    date
ssh postgres@node03  -i ~/.ssh/id_rsa_pgpool    date

4.4 配置postgresql用户的sudo权限

vim /etc/sudoers 
postgres ALL=(ALL) NOPASSWD:ALL

4.5 postgresql数据库创建插件pgpool_recovery

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# CREATE EXTENSION pgpool_recovery;
CREATE EXTENSION

五、启动pgpool

5.1 启动pgpool

启动pgpool(先后分别在node01、node02、node03上启动，使用postgres用户)
su - postgres 
pgpool -D -n &
参数：
-d：表示使用debug方式启动

5.2 关闭pgpool

pgpool -m fast stop

5.3 恢复备库

node02：
[postgres@node02 ~]$ pcp_recovery_node -h 192.168.128.135 -p 9898 -U pgpool -n 1 -v -w
pcp_recovery_node -- Command Successful

node03：
[postgres@node03 ~]$ pcp_recovery_node -h 192.168.128.135 -p 9898 -U pgpool -n 2 -v -w
pcp_recovery_node -- Command Successful
查看流复制状态
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 71318
usesysid         | 16385
usename          | repl
application_name | walreceiver
client_addr      | 192.168.128.131
client_hostname  |
client_port      | 33642
backend_start    | 2023-08-17 17:13:24.111627+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/9000148
write_lsn        | 0/9000148
flush_lsn        | 0/9000148
replay_lsn       | 0/9000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-08-17 17:26:09.844183+08
-[ RECORD 2 ]----+------------------------------
pid              | 71412
usesysid         | 16385
usename          | repl
application_name | walreceiver
client_addr      | 192.168.128.132
client_hostname  |
client_port      | 58724
backend_start    | 2023-08-17 17:13:48.973076+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/9000148
write_lsn        | 0/9000148
flush_lsn        | 0/9000148
replay_lsn       | 0/9000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-08-17 17:26:10.239344+08

5.4 查看集群状态

[postgres@node01 ~]$ psql -h 192.168.128.135 -p 9999 -U postgres postgres -c "show pool_nodes"
Password for user postgres:
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | node01   | 5432 | up     | 0.333333  | primary | 0          | false             | 0                 |                   |                        | 2023-08-17 17:18:58
 1       | node02   | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 |                   |                        | 2023-08-17 17:18:58
 2       | node03   | 5432 | up     | 0.333333  | standby | 0          | true              | 0                 |                   |                        | 2023-08-17 17:18:58
(3 rows)

5.5 pgpool与集群相关操作

1、查看集群配置信息
pcp_pool_status -h 192.168.128.135 -p 9898 -U pgpool -v -w
2、查看集群节点详情
-h 表示集群IP，-p 表示PCP管道端口（默认是9898），-U 表示 PCP管道用户，-v表示查看详细内容
pcp_watchdog_info -h 192.168.128.135 -p 9898 -U pgpool -v -w
3、查看节点数量
pcp_node_count -h 192.168.128.135 -p 9898 -U pgpool -w
4、查看指定节点信息
pcp_node_info -h 192.168.128.135 -p 9898 -U pgpool -n 0 -v -w
5、增加一个集群节点
#-n 表示节点序号（从0开始）
pcp_attach_node -h 192.168.128.135 -p 9898 -U pgpool -n 0 -v -w
6、脱离一个集群节点
pcp_detach_node -h 192.168.128.135 -p 9898 -U pgpool -n 0 -v -w
7、提升一个备用节点为活动节点
pcp_promote_node -h 192.168.128.135 -p 9898 -U pgpool -n 0 -v -w
8、恢复一个离线节点为集群节点
pcp_recovery_node -h 192.168.128.135 -p 9898 -U pgpool -n 0 -v -w
PostgresSQL集群
1、连接集群
psql -h 192.168.128.135 -p 9999
2、查看集群状态
psql -h 192.168.128.135 -p 9999 -U postgres postgres -c "show pool_nodes"
3、查看相关参数配置
show pool_status

5.6 pgpool启动顺序

启动(postgres用户)：
1、先启动数据库服务
2、启动pgpool服务
3、先主后从
数据库：
node01：pg_ctl start
node02：pg_ctl start
node03：pg_ctl start

pgpool：
node01：pgpool -D -n &
node02：pgpool -D -n &
node03：pgpool -D -n &
关闭：
1、先关闭pgpool服务
2、关闭数据库服务
3、先从后主
pgpool：
node03：pgpool -m fast stop
node02：pgpool -m fast stop
node01：pgpool -m fast stop

数据库：
node03：pg_ctl stop
node02：pg_ctl stop
node01：pg_ctl stop

六、高可用测试

6.1 关闭node01主数据库

!!!关闭主数据库
集群状态：
[postgres@node01 ~]$ psql -h 192.168.128.135 -p 9999 -U postgres postgres -c "show pool_nodes"
Password for user postgres:
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | node01   | 5432 | up     | 0.333333  | primary | 0          | false             | 0                 |                   |                        | 2023-08-18 17:26:33
 1       | node02   | 5432 | up     | 0.333333  | standby | 0          | true              | 0                 |                   |                        | 2023-08-18 17:26:33
 2       | node03   | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 |                   |                        | 2023-08-18 17:26:33
(3 rows)
流复制状态：
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 27171
usesysid         | 16385
usename          | repl
application_name | walreceiver
client_addr      | 192.168.128.131
client_hostname  |
client_port      | 32984
backend_start    | 2023-08-18 17:25:05.838877+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/5000148
write_lsn        | 0/5000148
flush_lsn        | 0/5000148
replay_lsn       | 0/5000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-08-18 17:41:09.176991+08
-[ RECORD 2 ]----+------------------------------
pid              | 27331
usesysid         | 16385
usename          | repl
application_name | walreceiver
client_addr      | 192.168.128.132
client_hostname  |
client_port      | 41416
backend_start    | 2023-08-18 17:26:10.21129+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/5000148
write_lsn        | 0/5000148
flush_lsn        | 0/5000148
replay_lsn       | 0/5000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-08-18 17:41:08.970829+08
当前主库为node01。
关闭主数据库
[postgres@node01 ~]$ pg_ctl stop
等待一段时间
查看集群状态
[postgres@node01 ~]$ psql -h 192.168.128.135 -p 9999 -U postgres postgres -c "show pool_nodes"
Password for user postgres:
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | node01   | 5432 | down   | 0.333333  | standby | 0          | false             | 0                 |                   |                        | 2023-08-18 17:42:41
 1       | node02   | 5432 | up     | 0.333333  | primary | 0          | true              | 0                 |                   |                        | 2023-08-18 17:42:50
 2       | node03   | 5432 | down   | 0.333333  | standby | 0          | false             | 0                 |                   |                        | 2023-08-18 17:42:41
(3 rows)
查看流复制状态
postgres=# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn |
write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+-----------+------------+-
----------+-----------+------------+---------------+------------+------------
(0 rows)
无流复制 
恢复node03
pcp_recovery_node -h 192.168.128.135 -p 9898 -U pgpool -n 2 -v -w
恢复node01
pcp_recovery_node -h 192.168.128.135 -p 9898 -U pgpool -n 0 -v -w
查看集群状态
[postgres@node01 ~]$ psql -h 192.168.128.135 -p 9999 -U postgres postgres -c "show pool_nodes"
Password for user postgres:
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | node01   | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 |                   |                        | 2023-08-18 17:44:53
 1       | node02   | 5432 | up     | 0.333333  | primary | 0          | false             | 0                 |                   |                        | 2023-08-18 17:42:50
 2       | node03   | 5432 | up     | 0.333333  | standby | 0          | true              | 33554272          |                   |                        | 2023-08-18 17:44:34
(3 rows)
查看流复制状态
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 6238
usesysid         | 16385
usename          | repl
application_name | walreceiver
client_addr      | 192.168.128.130
client_hostname  |
client_port      | 46510
backend_start    | 2023-08-18 17:44:47.619679+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/A000000
write_lsn        | 0/A000000
flush_lsn        | 0/A000000
replay_lsn       | 0/A000000
write_lag        | 00:00:00.088287
flush_lag        | 00:00:00.088287
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-08-18 17:48:02.109842+08
-[ RECORD 2 ]----+------------------------------
pid              | 65099
usesysid         | 16385
usename          | repl
application_name | walreceiver
client_addr      | 192.168.128.132
client_hostname  |
client_port      | 45624
backend_start    | 2023-08-18 17:48:03.586592+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/A000000
write_lsn        | 0/A000000
flush_lsn        | 0/A000000
replay_lsn       | 0/A000000
write_lag        | 00:00:00.101131
flush_lag        | 00:00:00.101131
replay_lag       | 00:00:00.101131
sync_priority    | 0
sync_state       | async
reply_time       | 2023-08-18 17:48:03.475388+08

七、负载均衡测试

7.1 使用sql语句进行测试

[postgres@node01 ~]$ psql -h 192.168.128.135 -p 9999 -U postgres -c "select inet_server_addr()"
 inet_server_addr
------------------
 192.168.128.130
(1 row)

[postgres@node01 ~]$ psql -h 192.168.128.135 -p 9999 -U postgres -c "select inet_server_addr()"
 inet_server_addr
------------------
 192.168.128.131
(1 row)

[postgres@node01 ~]$ psql -h 192.168.128.135 -p 9999 -U postgres -c "select inet_server_addr()"
 inet_server_addr
------------------
 192.168.128.132
(1 row)
这条命令是通过pgpool 代理端口访问数据库，获取数据库IP 地址。可以看到，访问请求可能会被分发到数据库节点中的任何一个。

 

 参考连接：https://www.modb.pro/db/176886

pg14+pgpool-II- 4.2.4高可用更多相关文章

Pycharm 常用快捷键

Spyder 快捷键大全

【游记】CCPC 济南 2024 游记

python bytecode解析

ansible进阶与自动化

RK3568开发板Openwrt文件系统构建

动态网络架构

机器学习入门

沨鸾的Shell小技巧

为你的手机内核开启docker支持

在Linux下优雅的调试C语言

浅析Dockerhub API：如何优雅地从dockerhub偷rootfs镜像

C# 通过注册表、API两种方式获取显示器的分辨率尺寸

2024.10.31 文件管理方案

python练习题

Kinsta-博客中文翻译-八-

JAX---Flax-深度学习教程-一-

Java-中文官方教程-2022-版-二-

JavaTutorialNetwork-中文系列教程-一-

JavaBeginnersTutorial-中文系列教程-一-

随机推荐

验证码处理在自动化测试中的应用

一些学科笑话

NOIP2024模拟赛20 & 11.1 小记

20241101 数据结构与算法期中机试收获

Java，启动！

什么是IT技术

即将到来！

2024/11/1日 日志 关于JavaScript简介&引入方式 以及基础语法的学习

舍得-时间-工作是人的一生最重要的事情-自己要有私房钱-人的一生最重要的事情是书写自己的人生

2.TiUP 部署 DM 集群

原型模式的C++实现

python bytecode解析

09-XSS键盘监听、cookie窃取&文件上传绕过

ubuntu 24.04 部署 mysql 8.4.3 LTS

国标GB28181公网平台LiteGBS国标GB28181视频平台建筑工地无线视频联网监控系统方案

imes完工下线

android 13 更改手机信号调整

BFS(Breath First Search 广度优先搜索)

Visual Studio Code（VSCode）中设置中文界面

影响黄金价格大幅波动的因素主要有哪些？

热门话题

Ethernaut Level 11: Elevator Attack and Blockchain Interaction

快速部署开源spug运维平台的Docker安装指南

驱动调试之printk的原理与使用

计算机思维模型及其应用

华为云发布代码大模型PanGu-Coder2，实现高效代码生成

Linux多硬盘数据存储和分区操作

构建高可用架构: 分层冗余与自动故障转移

LoRA：高效调参的大语言模型适应方法

《分布式系统的基本原理及互联网分层架构的本质》

Hadoop写流程解析

Java架构师的系统架构设计方法论中的规范要点

使用observeDOM解决BetterScroll插件在移动端无法滑动的问题

互联网一致性架构设计实践

高并发系统架构与水平扩展

混合应用的崛起：跨平台开发取代原生应用

  穗舟网 （www.seizhou.com）

本站除标明"本站原创"外所有文章版权归创作人所有，本站不承担任何法律责任和连带责任，如有冒犯请直接联系，我们将立即予以纠正并致歉。

Powered by WordPress  ·  v1.0.0-alpha