postgresql13.6流复制主从同步
安装好PG之后，如果给线上业务使用，至少得弄个主从，否则主库挂了，就可能导致业务长时间受影响。下面来看看怎么配置PG主从。
环境描述：
- PG版本：13.6
- 主库IP：172.16.103.225
- 从库IP：172.16.103.226
- 数据目录：/postgresql/pgdata
搭建主从（流复制）步骤：
- 在主库创建同步用户，并授权， （最好提前做好主备切换的权限配置，提前规划好pg_hba.conf， 以便后面主备切换）
- 在从库（下面也称standby）上安装好PG软件，安装跟主库一样（保持目录一致性），删除掉pgdata（数据目录）和archive（归档目录）下的文件，注意这里是操作从库的，不要干了主库！！
- 在从库上使用
pg_basebackup命令去搭建同步 - 启动从库
具体操作命令
- 主库上创建用户，授权
postgres=# CREATE ROLE replica login replication encrypted password 'repl_6534';
CREATE ROLE
- 配置
pg_hba.conf， 允许从库访问
$ vim pg_hba.conf
host replication all 172.16.103.225/32 md5
host replication all 172.16.103.226/32 md5
postgres=# select pg_reload_conf(); # 重载一下配置文件
- 从库上执行pg_basebackup去搭建同步
$ pg_basebackup -h 172.16.103.225 -U replica -p 5432 -F p -X s -v -P -R -D /postgresql/pgdata
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
WARNING: skipping special file "./.s.PGSQL.5432"
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/1B000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_28111"
WARNING: skipping special file "./.s.PGSQL.5432"pgdata/backup_label )
32338/32338 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/1B000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
- 启动从库
$ pg_ctl start -l /postgresql/pg_log/pg.log
waiting for server to start.... done
server started
查看数据库同步信息
1. 操作系统上进程
主库上会多一个walsender的进程
$ ps -ef | grep postgres | grep send
pgsql 7854 18709 0 11:19 ? 00:00:00 postgres: walsender replica 172.16.103.226(12836) streaming 0/1C0003E0
从库上会有一个walreceiver的进程
$ ps -ef | grep postgres
pgsql 30471 1 0 11:19 ? 00:00:00 /postgresql/pg13/bin/postgres
pgsql 30472 30471 0 11:19 ? 00:00:00 postgres: startup recovering 00000001000000000000001C
pgsql 30473 30471 0 11:19 ? 00:00:00 postgres: checkpointer
pgsql 30474 30471 0 11:19 ? 00:00:00 postgres: background writer
pgsql 30475 30471 0 11:19 ? 00:00:00 postgres: stats collector
pgsql 30476 30471 0 11:19 ? 00:00:00 postgres: walreceiver
2. 数据库表中的信息
postgres=# SELECT pg_is_in_recovery();
主库上返回f, 从库上返回t。
在主库上，查询表pg_stat_replication 可以看到从库同步的状态
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
pid | 7854
usesysid | 16436
usename | replica
application_name | walreceiver
client_addr | 172.16.103.226
client_hostname |
client_port | 12836
backend_start | 2023-12-22 11:19:53.781303+08
backend_xmin |
state | streaming
sent_lsn | 0/1C0003E0
write_lsn | 0/1C0003E0
flush_lsn | 0/1C0003E0
replay_lsn | 0/1C0003E0
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2023-12-22 11:24:24.29878+08
从库上可以查看 pg_stat_wal_receiver表查看同步状态
postgres=# select * from pg_stat_wal_receiver;
pid | 30476
status | streaming
receive_start_lsn | 0/1C000000
receive_start_tli | 1
written_lsn | 0/1C0003E0
flushed_lsn | 0/1C000000
received_tli | 1
last_msg_send_time | 2023-12-22 11:32:25.206129+08
last_msg_receipt_time | 2023-12-22 11:32:25.206617+08
latest_end_lsn | 0/1C0003E0
latest_end_time | 2023-12-22 11:19:53.783906+08
slot_name |
sender_host | 172.16.103.225
sender_port | 5432
conninfo | user=replica password=******** channel_binding=disable dbname=replication host=172.16.103.225 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any
需要注意的是，并不是说只有主库的pg_stat_replication有信息，从库一定没有，假如是一个级联的环境的话，那从库（它是某个从库的主库）也是有输出的。
pg_stat_replication表的几个字段的解释如下：
sent_lsn: 发送到standby的最后一个wal位置（还未write）。write_lsn: 写到standby操作系统上的最后一个wal位置（还未flush）。flush_lsn: 刷新到standby磁盘上的最后一个wal位置（到达disk）。replay_lsn: standby上重放的最后的事务日志位置（此时用户可见）。
select pg_current_wal_lsn();
是当前的wal_lsn， 也就是可以说主备延迟的字节数可以用pg_current_wal_lsn() - replay_lsn 得到。
主备切换步骤
- 关闭主库
- 从库上执行
pg_ctl promote提升为主库 - 应用配置重连新主库（原备库）【也可以提前规划好VIP， 把VIP切换到新主库】
- 原主库上创建
standby.signal文件， 配置postgresql.auto.conf文件， 以同步新主库 - 启动原主库，原主库变成了新从库
注意：异步情况下，如果主库宕机，切换从库的话，可能会有数据丢失。
主备切换具体操作命令
- 模拟主库宕机，直接停掉, 主库执行
$ pg_ctl stop -m immediate
waiting for server to shut down.... done
server stopped
此时，从库上执行select * from pg_stat_wal_receiver; 的话，已经没有输出。 日志会有同步报错信息。
- 从库上执行
$ pg_ctl promote
waiting for server to promote.... done
server promoted
查看数据库日志的话，可以看到以下信息
2023-12-22 11:45:44.835 CST [30472] LOG: received promote request
2023-12-22 11:45:44.835 CST [30472] LOG: redo done at 0/1C0003A8
2023-12-22 11:45:44.837 CST [30472] LOG: selected new timeline ID: 2
2023-12-22 11:45:44.883 CST [30472] LOG: archive recovery complete
2023-12-22 11:45:44.889 CST [30471] LOG: database system is ready to accept connections
原从库上的standby.signal文件也已经没了。
3. 原主库上创建standby.signal文件，把它变成新主库的从库
$ cd /postgresql/pgdata # 这个是我DB的数据目录，上面所说的postgresql.conf/postgresql.auto.conf/pg_hba.conf 均在该目录下
$ touch standby.signal
- 参考原来的从库的postgresql.auto.conf文件，配置老主库从新从库
$ vim postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replica password=repl_6534 channel_binding=disable host=172.16.103.226 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
- 启动原主库（新从库）
$ pg_ctl start
$ psql
psql (13.6)
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
pid | 5776
status | streaming
receive_start_lsn | 0/1C000000
receive_start_tli | 2
written_lsn | 0/1C0026C8
flushed_lsn | 0/1C000000
received_tli | 2
last_msg_send_time | 2023-12-22 14:21:05.444004+08
last_msg_receipt_time | 2023-12-22 14:21:05.444451+08
latest_end_lsn | 0/1C0026C8
latest_end_time | 2023-12-22 14:20:35.378922+08
slot_name |
sender_host | 172.16.103.226
sender_port | 5432
conninfo | user=replica password=******** channel_binding=disable dbname=replication host=172.16.103.226 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any
postgres=#
