PostgreSQL流复制相当于MySQL的主从复制,可以实现数据同步和数据备份
实现异步流复制
两个主机节点
pg-1 192.168.139.161 Master
pg-2 192.168.139.162 Standby
Master 节点配置
#创建复制的用户并授权
root@pg-1:~# psql
psql (15.12)
Type "help" for help.
postgres=# create role repluser with replication login password '123456';
#修改pg_hba.conf进行授权
root@pg-1:~# vim /data/pgsql/pg_hba.conf
...
host replication repluser 0.0.0.0/0 md5
...
#修改配置(可选):
root@pg-1:~# vim /data/pgsql/postgresql.conf
...
archive_mode = on #建议打开归档模式,防止长时间无法同步,WAL被覆盖造成数据丢失
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
# 需要创建 /archive 目录并且该目录有 postgres 权限
wal_level = replica #设置wal的级别
max_wal_senders = 5 #这个设置可以最多有几个流复制连接，一般有几个从节点就设置几个
wal_sender_timeout = 60s #设置流复制主机发送数据的超时时间
max_connections = 200 # 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on #对主库无影响,用于将来可能会成为从库,这台机器不仅仅是用于数据归档，也用于数据查询,在从库上配置此项后为只读
max_standby_archive_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 10s #多久向主报告一次从的状态，当从每次数据复制都会向主报告状态，只是设置最长的间隔时间
hot_standby_feedback = on #如果有错误的数据复制，是否向主进行反馈
wal_log_hints = on #对非关键更新进行整页写入
...
Standby 节点配置
#清空数据和归档
root@pg-2:~# systemctl stop postgresql.service
root@pg-2:~# rm -rf /data/pgsql/*
root@pg-2:~# rm -rf /archive/* # 需要有该目录 存放归档日志，该目录有 postgres 权限
root@pg-2:~# rm -rf /backup/*
#备份主库数据到备库
root@pg-2:~# mkdir /backup/pg-backup-`date +%F` && pg_basebackup -D /backup/pg-backup-`date +%F` -Ft -Pv -U postgres -h 192.168.139.161 -p 5432 -R
#还原备份的数据，实现初始的主从数据同步
root@pg-2:~# tar xf /backup/pg-backup-2025-03-01/base.tar -C /data/pgsql/
root@pg-2:~# tar xf /backup/pg-backup-2025-03-01/pg_wal.tar -C /archive/
root@pg-2:~# vim /data/pgsql/postgresql.conf
...
primary_conninfo = 'host=192.168.139.161 port=5432 user=repluser password=123456'
restore_command = 'cp /archive/%f %p' # 这一项可用不用配置
...
# 启动服务（方法一）
root@pg-2:~# systemctl restart postgresql.service
# 方法二
root@pg-2:~# su - postgres
postgres@pg-2:~$ pg_ctl -D /pgsql/data start
在主库查看状态
root@pg-1:~# pg_controldata
pg_control version number: 1300
Catalog version number: 202209061
Database system identifier: 7476130572374821290
Database cluster state: in production # 生产状态
pg_control last modified: Sat 01 Mar 2025 02:04:44 PM UTC
...
postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
pid | state | client_addr | sync_priority | sync_state
------+-----------+-----------------+---------------+------------
2088 | streaming | 192.168.139.162 | 1 | async
#下面只在主节点査看同步模式,注意:如果无从节点连接,将无任何显示信息
postgres=# \x
Expanded display is on.
postgres=# SELECT pg_current_wal_insert_lsn(),* from pg_stat_replication;
-[ RECORD 1 ]-------------+------------------------------
pg_current_wal_insert_lsn | 0/A0009B8
pid | 2088
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 192.168.139.162
client_hostname |
client_port | 57286
backend_start | 2025-03-01 14:07:52.143458+00
backend_xmin | 744
state | streaming
sent_lsn | 0/A0009B8
write_lsn | 0/A0009B8
flush_lsn | 0/A0009B8
replay_lsn | 0/A0009B8
write_lag | 00:00:00.000607
flush_lag | 00:00:00.001007
replay_lag | 00:00:00.001008
sync_priority | 1
sync_state | sync
reply_time | 2025-03-01 14:12:26.058416+00
#服务器查看数据库是否为备库，f表主库 t表示为备库
postgres=# select * from pg_is_in_recovery();
-[ RECORD 1 ]-----+--
pg_is_in_recovery | f
postgres=# select application_name,client_addr,sync_state from pg_stat_replication;
-[ RECORD 1 ]----+----------------
application_name | walreceiver
client_addr | 192.168.139.162
sync_state | async
root@pg-1:~# ps aux | grep walsender
postgres 2088 0.0 0.1 166552 7208 ? Ss 14:07 0:00 postgres: walsender postgres 192.168.139.162(57286) streaming 0/A0009B8
在从库查看状态
#从节点可以读
root@pg-2:~# psql
postgres=# \c testdb1
testdb1=# select * from t1 ;
id
----
1
(1 row)
#从节点不支持写
testdb1=# insert into t1 values (2);
ERROR: cannot execute INSERT in a read-only transaction
#服务器查看数据库是否为备库，f表主库 t表示为备库
postgres=# select * from pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# \x
postgres=# SELECT * FROM pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 5481
status | streaming
receive_start_lsn | 0/A000000
receive_start_tli | 1
written_lsn | 0/A3EB3F0
flushed_lsn | 0/A3EB3F0
received_tli | 1
last_msg_send_time | 2025-03-01 14:19:47.479319+00
last_msg_receipt_time | 2025-03-01 14:19:47.461775+00
latest_end_lsn | 0/A3EB3F0
latest_end_time | 2025-03-01 14:17:17.294425+00
slot_name |
sender_host | 192.168.139.161
sender_port | 5432
conninfo | user=postgres password=******** channel_binding=disable dbname=replication host=192.168.139.161 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any
root@pg-2:~# pg_controldata
pg_control version number: 1300
Catalog version number: 202209061
Database system identifier: 7476130572374821290
Database cluster state: in archive recovery # 备库
root@pg-2:~# ps aux | grep postgres
postgres 5469 0.0 0.4 164840 18152 ? Ss 14:07 0:00 /apps/pgsql/bin/postmaster -D /data/pgsql
postgres 5470 0.0 0.3 165076 12940 ? Ss 14:07 0:00 postgres: checkpointer
postgres 5471 0.0 0.0 164840 2432 ? Ss 14:07 0:00 postgres: background writer
postgres 5472 0.0 0.3 165712 13776 ? Ss 14:07 0:00 postgres: startup recovering 00000001000000000000000A
postgres 5481 0.0 0.1 165596 6000 ? Ss 14:07 0:00 postgres: walreceiver streaming 0/A3EB3F0 #接收WAL日志进程
切换主从(主库坏掉)
将从库切换为主库
root@pg-2:~# su - postgres
postgres@pg-2:~$ pg_ctl promote
waiting for server to promote.... done
server promoted
postgres@pg-2:~$ pg_controldata
pg_control version number: 1300
Catalog version number: 202209061
Database system identifier: 7476130572374821290
Database cluster state: in production # 变为生产模式
...
# 重启服务
root@pg-2:~# systemctl restart postgresql.service 或者 postgres@pg-2:~$ pg_ctl -D /pgsql/data restart # 用哪个方式启动就用哪一个方式c
# 恢复到正常模式
testdb1=# select pg_wal_replay_resume();
testdb1=# delete from t1 where id = 1 ;
原主库切换为从库
#在原主库修复故障后,在主库服务器重复上面standby节点的 standby节点配置
root@pg-1:~# rm -rf /data/pgsql/*
root@pg-1:~# rm -rf /archive/*
root@pg-1:~# rm -rf /backup/*
root@pg-1:~# mkdir /backup/^C
root@pg-1:~# mkdir /backup/pg-backup-`date +%F` && pg_basebackup -D /backup/pg-backup-`date +%F` -Ft -Pv -U postgres -h 192.168.139.162 -p 5432 -R
#还原备份的数据，实现初始的主从数据同步
root@pg-1:~# tar xf /backup/pg-backup-2025-03-01/base.tar -C /data/pgsql/
root@pg-1:~# tar xf /backup/pg-backup-2025-03-01/pg_wal.tar -C /archive/
root@pg-1:~# vim /data/pgsql/postgresql.conf
...
primary_conninfo = 'host=192.168.139.162 port=5432 user=repluser password=123456'
restore_command = 'cp /archive/%f %p' # 这一项可用不用配置
...
#在原主库服务器创建standby.signal文件
root@pg-1:~# touch /data/pgsql/standby.signal
#在原主库服务器启动服务
root@pg-1:~# systemctl start postgresql.service
#在原主库服务器查看状态
postgres=# select * from pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
在新主库创建数据,验证是否能同步至原主库
注意 最好用异步复制；同步复制如果一个节点宕机 会插入数据会夯住
实现同步的流复制
配置同步复制
主节点需要特殊的如下配置，从节点同 Standby 节点配置
`主节点`
root@pg-1:~# vim /data/pgsql/postgresql.conf
...
synchronous_standby_names = '*' #开启此项，表示同步方式，需要同时打开synchronous_commit = on,此为默认值，默认是异步方式(异步方式性能好)
synchronous_commit = on #开启同步模式（最好不开）
...
其他操作和异步相同
验证同步状态
postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
pid | state | client_addr | sync_priority | sync_state
------+-----------+-----------------+---------------+------------
1442 | streaming | 192.168.139.162 | 1 | sync
#从节点停止服务
postgres@slave:~$ pg_ctl stop
#主节点只支持读，但写入时会处于卡顿状态，直至从节点恢复正常同步
testdb1=# delete from tl where id=2;
文章评论