pg14+pgpool-II- 4.2.4高可用
一、环境配置
三个节点安装数据库软件;三个节点安装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 地址。可以看到,访问请求可能会被分发到数据库节点中的任何一个。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 我与微信审核的“相爱相杀”看个人小程序副业
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~