PostgreSQL17新特性之物理备份转逻辑副本的pg_createsubscriber工具

概述

pg_createsubscriber从物理备用服务器创建新的逻辑副本。

成功运行后，目标服务器的状态类似于新的逻辑复制设置。逻辑复制设置和 pg_createsubscriber 之间的主要区别在于初始数据拷贝。它只执行同步阶段，确保每个表都处于同步状态。

该pg_createsubscriber面向大型数据库系统，因为在逻辑复制设置中，大部分时间都花在执行初始数据复制上。此外，长时间同步数据的副作用通常是要应用大量更改（在初始数据复制期间发生的数据变更），这会增加逻辑副本变为可用的时间。对于较小的数据库，建议使用逻辑复制进行初始数据同步。

使用pg_createsubscriber工具的前提条件

1）源服务器和目标服务器必须具有与pg_createsubscriber相同的主要版本

2）目标数据目录必须具有与源数据目录相同的系统标识符

3）目标数据目录的给定数据库用户必须具有创建订阅和使用 pg_replication_origin_advance（）的权限

4）连接源服务器的用户有superuser权限，"create publication XXX for all tables"需要superuser

注意事项

1）如果目标服务器转换失败，则数据目录可能不处于可恢复的状态。在这种情况下，建议创建新的备库

2）转换期间源服务器避免执行DDL命令，DDL命令不会被逻辑复制同步到转换完成的目标服务器

3）转换完成时会使用pg_resetwal更改系统标识符，这样可以避免目标服务器使用源服务器中的 WAL 文件的情况

4）如果复制使用的是primary_slot_name，则在逻辑复制设置后，将从源服务器中删除这个复制槽

5）目标服务器是同步副本，则在运行pg_createsubscriber时，主服务器上的事务提交可能会等待复制

pg_createsubscriber是如何工作的

通过转换日志来看pg_createsubscriber工具是如何完成转换的

##检查目标服务器目录，并对比源和目标服务器是否是同一个系统标识
pg_createsubscriber -d test_db -D /app/pg17_beta1/data -v -P "host=mydb1b port=5417 user=rep_user"
pg_createsubscriber: validating connection string on publisher
pg_createsubscriber: validating connection string on subscriber
pg_createsubscriber: checking if directory "/app/pg17_beta1/data" is a cluster data directory
pg_createsubscriber: getting system identifier from publisher
pg_createsubscriber: system identifier is 7373460011704668633 on publisher
pg_createsubscriber: getting system identifier from subscriber
pg_createsubscriber: system identifier is 7373460011704668633 on subscriber
##启动目标服务器，检查是否满足转换的前提条件
pg_createsubscriber: starting the standby with command-line options
pg_createsubscriber: server was started
pg_createsubscriber: checking settings on subscriber
pg_createsubscriber: checking settings on publisher
pg_createsubscriber: primary has replication slot "s_5417"
pg_createsubscriber: stopping the subscriber
pg_createsubscriber: server was stopped
##在源服务器创建发布和复制槽
pg_createsubscriber: creating publication "pg_createsubscriber_24593_9dc5a30f" on database "test_db"
pg_createsubscriber: creating the replication slot "pg_createsubscriber_24593_9dc5a30f" on database "test_db"
pg_createsubscriber: create replication slot "pg_createsubscriber_24593_9dc5a30f" on publisher
##将恢复参数写入目标数据目录，然后重新启动目标服务器
##postgresql.auto.conf中新增的参数
recovery_target = ''
recovery_target_timeline = 'latest'
recovery_target_inclusive = true
recovery_target_action = promote
recovery_target_name = ''
recovery_target_time = ''
recovery_target_xid = ''
recovery_target_lsn = '0/C45DA28'

pg_createsubscriber: starting the subscriber
pg_createsubscriber: server was started
pg_createsubscriber: waiting for the target server to reach the consistent state
pg_createsubscriber: target server reached the consistent state
pg_createsubscriber: hint: If pg_createsubscriber fails after this point, you must recreate the physical replica before continuing.
##为目标服务器上的每个指定数据库创建订阅
pg_createsubscriber: dropping publication "pg_createsubscriber_24593_9dc5a30f" on database "test_db"
pg_createsubscriber: creating subscription "pg_createsubscriber_24593_9dc5a30f" on database "test_db"
pg_createsubscriber: setting the replication progress (node name "pg_32778" ; LSN 0/C45DA28) on database "test_db"
##为目标服务器上的每个指定数据库启用订阅
pg_createsubscriber: enabling subscription "pg_createsubscriber_24593_9dc5a30f" on database "test_db"
##如果备用服务器正在使用primary_slot_name，删除这个复制槽
pg_createsubscriber: dropping the replication slot "s_5417" on database "test_db"
pg_createsubscriber: stopping the subscriber
pg_createsubscriber: server was stopped
##使用pg_resetwal修改系统标识符
pg_createsubscriber: modifying system identifier of subscriber
pg_createsubscriber: system identifier is 7387944225062119497 on subscriber
pg_createsubscriber: running pg_resetwal on the subscriber
pg_createsubscriber: subscriber successfully changed the system identifier
##转换完成
pg_createsubscriber: Done!
[postgres@mydb1a ~]$ pg_ctl -D /app/pg17_beta1/data start
waiting for server to start....2024-07-05 08:38:08.296 CST [1473272] LOG:  starting PostgreSQL 17beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
2024-07-05 08:38:08.297 CST [1473272] LOG:  listening on IPv4 address "0.0.0.0", port 5417
2024-07-05 08:38:08.297 CST [1473272] LOG:  listening on IPv6 address "::", port 5417
2024-07-05 08:38:08.298 CST [1473272] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5417"
2024-07-05 08:38:08.303 CST [1473275] LOG:  database system was shut down at 2024-07-05 08:36:49 CST
2024-07-05 08:38:08.304 CST [1473275] LOG:  recovered replication state of node 1 to 0/C45DA28
2024-07-05 08:38:08.306 CST [1473272] LOG:  database system is ready to accept connections
2024-07-05 08:38:08.323 CST [1473279] LOG:  logical replication apply worker for subscription "pg_createsubscriber_24593_9dc5a30f" has started
 done
server started
[postgres@mydb1a ~]$ psql -p 5417
psql (17beta1)
Type "help" for help.
##目标服务器由物理备库转换为独立的主库
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)
postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".
test_db=# select * from test;
 id | name
----+------
  1 | a
(1 row)
##源服务器插入数据后，可同步到转换完成后的目标服务器中
test_db=# select * from test;
 id | name
----+------
  1 | a
  1 | b
(2 rows)

参考：

PostgreSQL：文档：17：pg_createsubscriber