PostgreSQL 17: 使用 pg_createsubscriber 将物理副本转换为逻辑副本

由 John Doe 五月 27, 2025

你需要创建逻辑副本，却担心耗时过长吗？现在，PostgreSQL 可以将物理副本转换为逻辑副本。

在山坡漫步的大象

特性提交日志

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

该命令必须在目标服务器上运行，且应能够连接到源服务器（发布者）和目标服务器（订阅者）。指定数据库中的所有表都会包含在逻辑复制设置中。系统会为每个数据库创建一对发布和订阅对象。

与常规逻辑复制的设置相比，pg_createsubscriber 的主要优势在于初始数据复制阶段，另外，它也能缩短追赶阶段。

要成功运行该命令，必须满足一些先决条件，这些条件基本属于逻辑复制的要求。具体流程包括：

使用 FOR ALL TABLES 为每个指定数据库创建发布对象和复制槽。
将恢复参数写入目标数据目录并启动目标服务器。
指定最后一个复制槽的 LSN（复制起始点），恢复将基于该位置进行。
等待目标服务器提升为主库状态。
在目标服务器上为每个指定数据库创建一个订阅（使用之前步骤中创建的发布对象和复制槽）。
为每个订阅将复制进度设置为复制起始点。
在目标服务器上为每个指定数据库启用订阅。
最后，修改目标服务器的系统标识符。

讨论：https://www.postgresql.org/message-id/flat/5ac50071-f2ed-4ace-a8fd-b892cffd33eb@www.fastmail.com

示例

在源服务器和目标服务器之间设置逻辑复制时，所有数据都需要从源端初始复制到目标端。根据设置中表的大小，此过程可能需要相当长的时间，并且该过程耗时越长，源端需要保留的预写日志（WAL）就越多，以便副本在完成初始数据复制后能够追赶进度。使用pg_createsubscriber时，您不再需要初始数据复制，因为物理副本设置时已经完成了这一步。您可以将物理副本作为起点，并将其转换为逻辑副本。其缺点是，源端和目标端的 PostgreSQL 主版本需要相同（这很明显，因为物理复制无法跨 PostgreSQL 主版本进行）。

为了了解其工作原理，让我们从头开始创建一个全新的 PostgreSQL 实例，并为物理复制和逻辑复制做准备：

$ initdb -D /var/pgsql/source
$ echo "wal_level=logical" >> /var/pgsql/source/postgresql.auto.conf
$ echo "max_replication_slots=10" >> /var/pgsql/source/postgresql.auto.conf
$ echo "hot_standby=on" >> /var/pgsql/source/postgresql.auto.conf
$ echo "port=8888"  >> /var/pgsql/source/postgresql.auto.conf
$ pg_ctl -D /var/pgsql/source start

一旦我们有了它，我们就需要一个跟随这个主节点的物理副本：

$ pg_basebackup -D /var/pgsql/target --write-recovery-conf -p 8888

$ tail -1 /var/pgsql/target/postgresql.auto.conf
port=8889
primary_conninfo = 'user=postgres passfile=''/home/postgres/.pgpass'' channel_binding=prefer port=8888 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

$ pg_ctl -D /var/pgsql/target start
waiting for server to start.... done
server started

为了确认稍后的逻辑复制确实有效，让我们使用 pgbench 给主服务器填充数据：

$ pgbench -i -s 10 -p 8888
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
vacuuming...                                                                                
creating primary keys...
done in 1.41 s (drop tables 0.00 s, create tables 0.03 s, client-side generate 1.03 s, vacuum 0.08 s, primary keys 0.28 s).

并检查物理副本是否获得了数据：

$ psql -c "select count(*) from pgbench_accounts" -p 8889
  count
---------
 1000000
(1 row)

现在是时候使用 pg_createsubscriber 将物理副本转换为逻辑副本了。第一步是停止副本：

$ pg_ctl -D /var/pgsql/target/ stop

下一步，你可以使用 pg_createsubscriber 进行试运行，或者直接运行。试运行示例如下：

$ pg_createsubscriber --database=postgres \
                      --pgdata=/var/pgsql/target \
                      --dry-run \
                      --subscriber-port=8889 \
                      --publisher-server='user=postgres passfile=''/home/postgres/.pgpass'' channel_binding=prefer port=8888 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable' \
                      --subscriber-username=postgres \
                      --publication=pub1 \
                      --subscription=sub1

$ echo $?
0

请注意命令运行的退出代码，0 表示成功。

不使用“dryrun”选项执行相同操作：

$ pg_createsubscriber --database=postgres --pgdata=/var/pgsql/target --subscriber-port=8889 --publisher-server="user=postgres passfile='/home/postgres/.pgpass' channel_binding=prefer port=8888 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable" --subscriber-username=postgres --publication=pub1 --subscription=sub1

此时运行日志中会有很多输出，如果你用心查看这些日志，你会看到将物理副本转换为逻辑副本的过程。让我们启动它，并检查一下逻辑副本的状态：

$ pg_ctl -D /var/pgsql/target start
waiting for server to start.... done
server started

正如预期的那样，我们在源服务器上获得了发布，在目标服务器上获得了订阅：

$ psql -p 8888 -c "select * from pg_publication"
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 16438 | pub1    |       10 | t            | t         | t         | t         | t           | f
(1 row)

$ psql -p 8889 -c "select * from pg_subscription"
  oid  | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpasswordrequired | subrunasowner | subfailover |                                                                                           >
-------+---------+------------+---------+----------+------------+-----------+-----------+------------------+-----------------+---------------------+---------------+-------------+------------------------------------------------------------------------------------------->
 24576 |       5 | 0/0        | sub1    |       10 | t          | f         | f         | d                | f               | t                   | f             | f           | user=postgres passfile=/home/postgres/.pgpass channel_binding=prefer port=8888 sslmode=pre>
(1 row)

通过在源服务器中添加一行数据，并检查目标服务器中的同一行，可以轻松验证正在进行的逻辑复制：

$ psql -p 8888 -c "insert into pgbench_accounts values (-1,-1,-1,'aaa')"
INSERT 0 1

$ psql -p 8889 -c "select * from pgbench_accounts where aid=-1"
 aid | bid | abalance |                                        filler
-----+-----+----------+--------------------------------------------------------------------------------------
  -1 |  -1 |       -1 | aaa
(1 row)