PostgreSQL逻辑复制：如何同步初始存量数据至订阅端？

针对已存在数据的表T，在启用逻辑复制后同步存量数据，有三种常用方案：

方案一：利用订阅初始快照自动同步 #

这是最省心的默认方案，订阅会自动拉取发布端的存量数据，再消费后续增量：

1. 发布端确保publication包含表T（默认发布INSERT/UPDATE/DELETE事件）：

   CREATE PUBLICATION my_publication FOR TABLE T;
   

2. 订阅端创建订阅时，保留默认的copy_data = true（可省略该参数）：

   CREATE SUBSCRIPTION my_subscription
   CONNECTION 'host=发布端地址 dbname=发布端库名 user=复制用户'
   PUBLICATION my_publication;
   

   订阅创建后会先同步表T的所有存量数据，完成后自动切换到增量变更消费。

方案二：手动导入存量数据+对齐复制位点 #

适合数据量极大、不想用快照锁表的场景：

1. 发布端创建publication和复制槽后，记录当前WAL位点：

   SELECT pg_current_wal_lsn(); -- 保存返回的LSN值，比如`0/1234567`
   

2. 导出表T的存量数据（仅导数据，假设订阅端已有表结构）：

   pg_dump -h 发布端地址 -d 发布端库名 -t T --data-only --inserts -f t_data.sql
   

3. 在订阅端导入数据：

   psql -h 订阅端地址 -d 订阅端库名 -f t_data.sql
   

4. 创建或修改订阅，指定从记录的LSN开始消费增量：

   -- 若未创建订阅
   CREATE SUBSCRIPTION my_subscription
   CONNECTION 'host=发布端地址 dbname=发布端库名 user=复制用户'
   PUBLICATION my_publication
   WITH (copy_data = false, start_lsn = '0/1234567');
   
   -- 若已创建订阅
   ALTER SUBSCRIPTION my_subscription SET (start_lsn = '0/1234567');
   

方案三：将存量转为复制事件消费 #

如果需要让存量数据以复制事件的形式被订阅端接收（小数据量适用）：

1. 确保publication对表T开启INSERT发布（默认已开启）：

   ALTER PUBLICATION my_publication ADD TABLE T;
   

2. 通过逻辑槽导出存量数据对应的INSERT事件：

   SELECT data FROM pg_logical_slot_get_changes(
     '你的复制槽名', 
     NULL, 
     NULL, 
     'publication_names', 'my_publication'
   );
   

   查询结果中的data字段就是表T存量数据对应的INSERT语句，可将这些语句导出后在订阅端执行，或让订阅端从当前槽位点开始消费。

内容的提问来源于stack exchange，提问作者lordav