数据库教程FGMT63‑PostgreSQL逻辑复制技术与项目实战

前言与内容大纲介绍

PostgreSQL 逻辑复制是数据库架构设计、数据迁移、高可用建设与业务系统改造中的关键能力之一。与传统物理复制不同，逻辑复制基于数据行变化和逻辑解码机制，可以实现更细粒度的数据订阅、跨版本数据同步、部分表迁移、双向或多源汇聚等场景。本套风哥教程面向具备 PostgreSQL 基础运维能力的读者，围绕逻辑复制的理论模型、内置配置、项目环境准备、单表与多表复制、监控运维、pglogical 扩展以及综合项目实战展开。

风哥教程本文的目标不是简单罗列命令，而是帮助读者建立从原理到生产落地的完整路径。文中所有实战环境均按 64G 内存、8CPU 的服务器规格进行规划，主机名统一使用 fgedu-net-cn1 和 fgedu-net-cn2，数据库名、实例名和用户名统一使用 fgedudb，路径由常规 /u01 调整为 /fgedudb，便于形成统一的实验与交付模板。

本套风哥教程内容结构如下：

1. 逻辑复制基础与理论模型
2. PostgreSQL 逻辑复制核心架构
3. 发布、订阅与复制槽机制
4. 项目环境准备与系统参数规划
5. 单表逻辑复制实战配置
6. 多表逻辑复制实战配置
7. 逻辑复制运维与监控
8. pglogical 扩展配置实战
9. 综合实战一：同步模式、表结构与表数据
10. 综合实战二：多对一数据汇聚
11. 风哥针对本文总结

---

1. 逻辑复制基础与理论模型

1.1 什么是逻辑复制

逻辑复制是指数据库通过解析 WAL 日志中的逻辑变更记录，将数据行级别的 INSERT、UPDATE、DELETE 等操作发送给一个或多个订阅端，订阅端再按照事务语义将这些变更应用到本地数据库。与物理复制复制整个磁盘页或块不同，逻辑复制复制的是“数据变化的逻辑内容”。

这意味着逻辑复制可以做到：

• 只复制指定表，而不是整个实例；
• 对复制内容进行过滤；
• 在订阅端进行不同的数据处理；
• 支持跨大版本迁移中的数据同步；
• 实现多源数据汇聚；
• 为数据迁移、灰度发布和业务改造提供在线同步能力。

1.2 逻辑复制的适用场景

1.2.1 数据迁移与版本升级

当业务需要将数据库从旧环境迁移到新环境，或进行大版本升级时，逻辑复制可以作为在线迁移工具的一部分。源端持续发布增量变更，目标端先同步历史数据，再追赶增量，最终在业务低峰期完成切换。

1.2.2 部分业务数据同步

并不是所有业务都需要全库复制。很多场景只需要同步核心业务表、配置表、字典表或审计表。逻辑复制通过发布对象列表控制复制范围，非常适合这类需求。

1.2.3 多源数据汇聚

在报表系统、数据中台或分库分表改造场景中，多个业务库可能需要将数据汇聚到一个目标库中进行统一分析。逻辑复制可以支持多源到单目标的数据汇聚架构。

1.2.4 读写分离与查询卸载

虽然逻辑复制不是传统意义上的流复制高可用方案，但在一些允许延迟的业务中，可以将分析查询、报表查询和后台统计任务导向订阅端，从而降低主库压力。

1.3 逻辑复制的限制与边界

逻辑复制虽然灵活，但也有明确边界。它不复制 DDL 定义，不复制大对象，不复制序列，不复制用户权限，也不保证源端和订阅端在结构上完全一致。因此，在项目设计阶段必须明确：哪些对象由逻辑复制负责，哪些对象需要额外通过 DDL 脚本、备份恢复或应用迁移完成。

1.4 逻辑复制与物理复制的区别

物理复制关注磁盘数据块的一致性，逻辑复制关注数据行变化的逻辑一致性。物理复制通常要求主备版本严格一致，复制粒度粗，但一致性强；逻辑复制粒度细、拓扑灵活，但需要更多结构对齐和应用层处理。

在项目选型时，如果目标是高可用和强一致灾备，物理复制更合适；如果目标是数据迁移、部分同步、多源汇聚或跨版本迁移，逻辑复制更合适。

---

2. PostgreSQL 逻辑复制核心架构

2.1 逻辑复制的主要组件

PostgreSQL 逻辑复制由几个核心组件协同完成：

• 发布端：产生数据变更并对外提供变更流；
• 订阅端：拉取并应用变更；
• 发布对象：被选中复制的表或表组；
• 复制槽：记录发布端已经发送到订阅端的位置；
• 逻辑解码插件：将 WAL 中的二进制日志解析为可读的逻辑变更格式；
• 复制协议：发布端与订阅端之间的数据传输通道。

2.2 发布端工作机制

发布端的核心任务是捕获数据变化并将其发送给订阅端。当用户在发布端执行 INSERT、UPDATE、DELETE 时，变化会先写入 WAL。逻辑复制服务随后通过逻辑解码机制，将这些变化转换为逻辑变更消息，并根据发布对象进行过滤。

发布端不需要提前知道所有订阅端的存在，订阅端通过复制槽来维护自己的消费位置。发布端只需要保证，在订阅端尚未消费之前，相关 WAL 不会被过早回收。

2.3 订阅端工作机制

订阅端通过后台进程连接发布端，从指定复制槽开始拉取增量变更。拉取到的变更会先进入本地临时存储，然后由应用进程按照事务顺序应用到订阅端表中。

订阅端应用变更时，会使用发布端发送的主键或 replica identity 来定位目标行。如果订阅端表缺少有效 replica identity，UPDATE 和 DELETE 操作可能无法正确应用。

2.4 复制槽的作用

复制槽是逻辑复制中非常重要的概念。它用于记录订阅端已经消费到的 WAL 位置，即使订阅端临时断开，发布端也知道从哪里继续发送。

复制槽的存在会阻止 PostgreSQL 回收尚未被消费的 WAL。因此，如果订阅端长期断开，发布端的 WAL 可能会持续堆积，最终导致磁盘空间增长。生产环境中必须对复制槽状态和 WAL 积压进行监控。

2.5 事务顺序与一致性

逻辑复制通常按照发布端事务提交顺序进行应用。这意味着订阅端可以获得与发布端一致的事务视图。但需要注意，逻辑复制并不保证“实时强一致”，它存在复制延迟，尤其在网络抖动、发布端高负载或订阅端应用压力较大时，延迟可能明显增加。

---

3. 发布、订阅与复制槽机制

3.1 发布的概念

发布是发布端上定义的一个复制出口。一个发布可以包含一个或多个表，也可以定义为复制所有表。发布本身不决定谁来消费，它只决定哪些数据可以被复制。

在生产项目中，建议按业务模块创建发布，而不是把所有表都放在一个DF布中。这样可以降低故障影响范围，也便于后续按业务线进行维护。

3.2 订阅的概念

订阅是订阅端上定义的一个复制入口。订阅会指定发布端连接信息、发布名称、复制槽名称以及目标表映射关系。订阅创建后，PostgreSQL 会自动建立复制连接并开始拉取增量。

3.3 复制槽与订阅生命周期

复制槽与订阅之间存在紧密关系。订阅在创建时通常会指定复制槽，复制槽记录订阅端的接收位置。如果订阅被删除，复制槽是否保留取决于配置，生产环境中需要谨慎处理，避免出现孤立复制槽。

3.4 数据变更过滤

逻辑复制支持基于发布对象的过滤。例如，只发布某些表，或在表级别指定列。这样可以避免把不需要的测试表、日志表或临时表同步到目标端。

3.5 复制方向与拓扑

逻辑复制常见拓扑包括：

• 一对一单向复制；
• 一对多分发；
• 多对一汇聚；
• 双向复制。

其中，双向复制最复杂，必须处理冲突、循环复制、序列同步和外键依赖问题。没有成熟的冲突处理方案，不建议在核心业务中盲目使用双向逻辑复制。

---

4. 项目环境准备与系统参数规划

4.1 环境规格说明

本套风哥教程中的所有实战均按 64G 内存、8CPU 服务器规格设计。实际配置时，应根据业务负载、并发连接数、WAL 生成速率和复制延迟目标进行微调。

环境规划如下：

项目	规划值
主机名	fgedu-net-cn1、fgedu-net-cn2
数据库名	fgedudb
实例名	fgedudb
用户名	fgedu
数据目录	/fgedudb
架构	发布端 fgedu-net-cn1，订阅端 fgedu-net-cn2

4.2 系统内核参数规划

针对 64G 内存、8CPU 环境，建议调整以下内核参数。这些参数会影响共享内存、网络连接、文件句柄和进程调度能力。

cat >> /etc/sysctl.conf << 'EOF'
fs.file-max = 655360
kernel.shmall = 4294967296
kernel.shmmax = 34359738368
kernel.shmmni = 4096
kernel.sem = 250 32000 32 128
net.core.somaxconn = 65535
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
vm.dirty_ratio = 15
vm.dirty_background_ratio = 5
EOF

sysctl -p

说明：shmmax 设置为 34GB 左右，是为了满足 PostgreSQL 共享内存段的分配需求；somaxconn 用于提升高并发连接能力；dirty_ratio 系列参数用于控制写回磁盘的节奏。

4.3 用户资源限制规划

cat >> /etc/security/limits.conf << 'EOF'
fgedu soft nofile 65536
fgedu hard nofile 65536
fgedu soft nproc 65536
fgedu hard nproc 65536
fgedu soft memlock unlimited
fgedu hard memlock unlimited
EOF

4.4 目录规划

mkdir -p /fgedudb/pgdata
mkdir -p /fgedudb/pg_wal
mkdir -p /fgedudb/pg_log
mkdir -p /fgedudb/backup
chown -R fgedu:fgedu /fgedudb
chmod 700 /fgedudb/pgdata

4.5 数据库参数规划

针对 64G 内存、8CPU 环境，建议在 postgresql.conf 中配置以下参数：

max_connections = 400
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 4GB
work_mem = 64MB
wal_buffers = 512MB
min_wal_size = 4GB
max_wal_size = 16GB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 512
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
max_worker_processes = 16
wal_level = logical
archive_mode = on
archive_command = 'cp %p /fgedudb/backup/archive/%f'
max_replication_slots = 10
max_logical_replication_workers = 8
max_sync_workers_per_subscription = 4

参数说明：

• wal_level = logical：开启逻辑复制所需的 WAL 级别；
• shared_buffers = 16GB：约为内存的 25%；
• effective_cache_size = 48GB：约为内存的 75%；
• max_worker_processes = 16：支持更多后台复制进程；
• max_logical_replication_workers：控制逻辑复制工作进程数量；
• max_sync_workers_per_subscription：控制每个订阅的同步并行度。

4.6 pg_hba.conf 配置

发布端和订阅端之间需要建立复制连接，因此必须在 pg_hba.conf 中允许复制链路。

host    fgedudb         fgedu           fgedu-net-cn2/32        scram-sha-256
host    replication     fgedu           fgedu-net-cn2/32        scram-sha-256

配置完成后重载配置：

SELECT pg_reload_conf();

4.7 主机名解析配置

为避免依赖 DNS，可在两台主机的 /etc/hosts 中配置主机名解析。

cat >> /etc/hosts << 'EOF'
192.168.10.11 fgedu-net-cn1
192.168.10.12 fgedu-net-cn2
EOF

4.8 数据库初始化

在 fgedu-net-cn1 上初始化数据库：

su - fgedu
initdb -D /fgedudb/pgdata -U fgedu -W

在 fgedu-net-cn2 上执行相同操作。初始化完成后，修改 postgresql.conf 和 pg_hba.conf，然后启动数据库。

pg_ctl start -D /fgedudb/pgdata

4.9 创建业务数据库与用户

CREATE DATABASE fgedudb OWNER fgedu;
CREATE USER fgedu WITH PASSWORD 'Fgedu@2025';
ALTER USER fgedu WITH SUPERUSER;
GRANT ALL PRIVILEGES ON DATABASE fgedudb TO fgedu;

说明：项目实战阶段为便于配置，可临时授予 fgedu 较高权限；生产环境应按最小权限原则逐步收敛。

---

5. 单表逻辑复制实战配置

5.1 实战目标

在 fgedu-net-cn1 上创建一张业务表 t_order，并将其通过逻辑复制同步到 fgedu-net-cn2。这是逻辑复制最基础、最常用的项目模板。

5.2 在发布端创建测试表

\c fgedudb fgedu

CREATE TABLE t_order (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount NUMERIC(12,2) NOT NULL DEFAULT 0,
    status SMALLINT NOT NULL DEFAULT 0,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_t_order_user_id ON t_order(user_id);
CREATE INDEX idx_t_order_create_time ON t_order(create_time);

5.3 插入初始数据

INSERT INTO t_order (order_id, user_id, amount, status)
SELECT i, i%1000, ROUND((i*13.7)::numeric,2), i%5
FROM generate_series(1,100000) i;

ANALYZE t_order;

5.4 在发布端创建发布

CREATE PUBLICATION pub_t_order FOR TABLE t_order;

查看发布信息：

SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete
FROM pg_publication;

5.5 在订阅端创建目标表

订阅端表结构应与发布端保持一致。为确保结构一致，建议先通过备份或 DDL 脚本创建。

\c fgedudb fgedu

CREATE TABLE t_order (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount NUMERIC(12,2) NOT NULL DEFAULT 0,
    status SMALLINT NOT NULL DEFAULT 0,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

5.6 在订阅端创建订阅

CREATE SUBSCRIPTION sub_t_order
CONNECTION 'host=fgedu-net-cn1 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'
PUBLICATION pub_t_order;

创建订阅时，PostgreSQL 会自动创建复制槽并开始同步数据。如果目标表中已有数据，可能需要使用 copy_data = false，然后手动完成初始数据同步。

5.7 查看订阅状态

SELECT subname, subenabled, subconninfo, subpublications
FROM pg_subscription;

5.8 查看复制槽状态

在发布端执行：

SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

5.9 验证数据同步

在发布端插入新数据：

INSERT INTO t_order (order_id, user_id, amount, status)
VALUES (999999, 8888, 1288.50, 1);

在订阅端查询：

SELECT * FROM t_order WHERE order_id = 999999;

如果订阅端能够查到该记录，说明单表逻辑复制链路已经建立成功。

5.10 增量更新测试

在发布端执行更新：

UPDATE t_order SET status = 2, update_time = CURRENT_TIMESTAMP
WHERE order_id = 999999;

订阅端验证：

SELECT order_id, status, update_time FROM t_order WHERE order_id = 999999;

5.11 删除操作测试

发布端：

DELETE FROM t_order WHERE order_id = 999999;

订阅端：

SELECT count(*) FROM t_order WHERE order_id = 999999;

5.12 单表复制常见问题

5.12.1 表没有主键导致复制失败

如果表没有主键或 replica identity，UPDATE 和 DELETE 可能无法正确应用。建议业务表都设计主键。

ALTER TABLE t_order REPLICA IDENTITY USING INDEX t_order_pkey;

5.12.2 初始数据已经存在导致冲突

如果订阅端已有数据，创建订阅时可能出现主键冲突。此时应先清空订阅端数据，或使用 copy_data = false。

CREATE SUBSCRIPTION sub_t_order
CONNECTION 'host=fgedu-net-cn1 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'
PUBLICATION pub_t_order
WITH (copy_data = false);

5.12.3 复制延迟较高

延迟较高通常与发布端 WAL 生成速度、订阅端应用速度、网络带宽和磁盘 IO 有关。应结合复制延迟指标和系统负载进行排查。

---

6. 多表逻辑复制实战配置

6.1 实战目标

在项目中，很多场景需要同步一组业务表，而不是单张表。本套风哥教程本文演示如何将订单表、用户表和支付表组成一个业务模块，统一发布到订阅端。

6.2 在发布端创建多张业务表

\c fgedudb fgedu

CREATE TABLE t_user (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(128) NOT NULL,
    mobile VARCHAR(32),
    status SMALLINT NOT NULL DEFAULT 1,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE t_payment (
    pay_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    pay_amount NUMERIC(12,2) NOT NULL,
    pay_status SMALLINT NOT NULL DEFAULT 0,
    pay_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE t_order (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount NUMERIC(12,2) NOT NULL DEFAULT 0,
    status SMALLINT NOT NULL DEFAULT 0,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

6.3 插入测试数据

INSERT INTO t_user (user_id, username, mobile, status)
SELECT i, 'user_'||i, '138'||lpad(i::text,8,'0'), 1
FROM generate_series(1,50000) i;

INSERT INTO t_order (order_id, user_id, amount, status)
SELECT i, i%50000+1, ROUND((i*7.3)::numeric,2), i%4
FROM generate_series(1,200000) i;

INSERT INTO t_payment (pay_id, order_id, pay_amount, pay_status, pay_time)
SELECT i, i, ROUND((i*6.1)::numeric,2), i%3, CURRENT_TIMESTAMP
FROM generate_series(1,200000) i;

ANALYZE t_user;
ANALYZE t_order;
ANALYZE t_payment;

6.4 创建多表发布

CREATE PUBLICATION pub_business_module
FOR TABLE t_user, t_order, t_payment;

查看发布对象：

SELECT * FROM pg_publication_tables
WHERE pubname = 'pub_business_module';

6.5 在订阅端创建对应表

为保证结构一致，建议使用同一套 DDL 在订阅端执行。

\c fgedudb fgedu

CREATE TABLE t_user (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(128) NOT NULL,
    mobile VARCHAR(32),
    status SMALLINT NOT NULL DEFAULT 1,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE t_payment (
    pay_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    pay_amount NUMERIC(12,2) NOT NULL,
    pay_status SMALLINT NOT NULL DEFAULT 0,
    pay_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE t_order (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount NUMERIC(12,2) NOT NULL DEFAULT 0,
    status SMALLINT NOT NULL DEFAULT 0,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

6.6 创建多表订阅

CREATE SUBSCRIPTION sub_business_module
CONNECTION 'host=fgedu-net-cn1 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'
PUBLICATION pub_business_module;

6.7 验证多表同步

发布端：

INSERT INTO t_user (user_id, username, mobile, status) VALUES (9999999, 'fengge_test', '13900000000', 1);
INSERT INTO t_order (order_id, user_id, amount, status) VALUES (9999999, 9999999, 888.00, 0);
INSERT INTO t_payment (pay_id, order_id, pay_amount, pay_status, pay_time) VALUES (9999999, 9999999, 888.00, 1, CURRENT_TIMESTAMP);

订阅端：

SELECT * FROM t_user WHERE user_id = 9999999;
SELECT * FROM t_order WHERE order_id = 9999999;
SELECT * FROM t_payment WHERE pay_id = 9999999;

6.8 多表复制中的依赖问题

如果表之间存在外键关系，订阅端应用变更时可能因为父表数据尚未到达而失败。因此，多表复制应尽量保证相关表同时纳入发布，并考虑在订阅端关闭外键约束检查，或通过应用层控制数据写入顺序。

---

7. 逻辑复制运维与监控

7.1 监控的重要性

逻辑复制创建完成并不代表项目结束。生产环境中必须持续监控复制延迟、复制槽状态、WAL 积压、订阅状态和错误日志。很多故障并不是复制链路突然中断，而是延迟缓慢增大，最终影响业务切换和数据一致性目标。

7.2 查看订阅状态

SELECT subname, subenabled, subconninfo, subpublications
FROM pg_subscription;

7.3 查看复制槽状态

SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

7.4 计算复制延迟

发布端执行：

SELECT now() - pg_stat_replication.write_lag AS write_lag,
       now() - pg_stat_replication.replay_lag AS replay_lag
FROM pg_stat_replication;

订阅端执行：

SELECT subname, received_lsn, latest_end_lsn,
       latest_end_time - received_time AS apply_lag
FROM pg_stat_subscription;

7.5 监控 WAL 积压

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retention_size
FROM pg_replication_slots;

如果 retention_size 持续增长，说明订阅端消费速度慢于发布端产生速度，需要排查订阅端负载、网络和磁盘 IO。

7.6 查看复制错误日志

tail -f /fgedudb/pg_log/postgresql-*.log

常见错误包括：

• 表结构不一致；
• 主键冲突；
• 外键约束失败；
• 复制槽缺失；
• 连接认证失败；
• 网络中断导致订阅失效。

7.7 暂停与恢复订阅

ALTER SUBSCRIPTION sub_business_module DISABLE;
ALTER SUBSCRIPTION sub_business_module ENABLE;

暂停订阅后，发布端仍会保留复制槽位置，因此必须关注 WAL 保留空间。

7.8 删除订阅与复制槽

如果项目下线，应先删除订阅，再根据业务需要决定是否删除复制槽。

DROP SUBSCRIPTION IF EXISTS sub_business_module;

发布端删除复制槽：

SELECT pg_drop_replication_slot('sub_business_module');

7.9 生产运维建议

生产环境建议建立以下巡检项：

1. 复制延迟是否超过业务容忍阈值；
2. 复制槽是否活跃；
3. WAL 保留空间是否异常增长；
4. 订阅端错误日志是否有冲突；
5. 发布端与订阅端表结构是否一致；
6. 网络带宽和磁盘 IO 是否成为瓶颈。

---

8. pglogical 扩展配置实战

8.1 pglogical 简介

pglogical 是 PostgreSQL 生态中常用的逻辑复制扩展，适用于复杂数据同步、数据迁移和多源汇聚场景。它提供了更灵活的节点管理、复制集、冲突处理和双向复制能力。

8.2 安装 pglogical

安装时需要注意 PostgreSQL 版本与扩展版本兼容。本套风哥教程按统一项目环境进行安装。

yum install -y pglogical

或从源码包安装：

tar -zxf pglogical.tar.gz
cd pglogical
make
make install

8.3 修改共享库配置

shared_preload_libraries = 'pglogical'

修改后重启数据库：

pg_ctl restart -D /fgedudb/pgdata

8.4 创建扩展

CREATE EXTENSION pglogical;

验证安装：

SELECT extname FROM pg_extension WHERE extname = 'pglogical';

8.5 在发布端创建节点

SELECT pglogical.create_node(
    node_name := 'provider_node',
    dsn := 'host=fgedu-net-cn1 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'
);

8.6 在订阅端创建节点

SELECT pglogical.create_node(
    node_name := 'subscriber_node',
    dsn := 'host=fgedu-net-cn2 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'
);

8.7 创建复制集

SELECT pglogical.create_replication_set(
    set_name := 'rs_business',
    replicate_insert := true,
    replicate_update := true,
    replicate_delete := true,
    replicate_truncate := false
);

8.8 将表加入复制集

SELECT pglogical.replication_set_add_table('rs_business', 't_order');
SELECT pglogical.replication_set_add_table('rs_business', 't_user');
SELECT pglogical.replication_set_add_table('rs_business', 't_payment');

8.9 在订阅端创建订阅

SELECT pglogical.create_subscription(
    subscription_name := 'sub_pglogical_business',
    provider_dsn := 'host=fgedu-net-cn1 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025',
    replication_sets := ARRAY['rs_business']
);

8.10 查看 pglogical 状态

SELECT * FROM pglogical.node;
SELECT * FROM pglogical.replication_set;
SELECT * FROM pglogical.subscription;

8.11 pglogical 常见问题

8.11.1 扩展未正确加载

如果创建扩展时报错，通常是 shared_preload_libraries 未配置或数据库未重启。

8.11.2 表结构不一致

pglogical 同样要求发布端和订阅端表结构对齐，否则会出现列类型不匹配错误。

8.11.3 冲突处理

在双向复制或多源汇聚中，冲突处理非常关键。可以通过冲突检测策略、时间戳策略或应用层去重机制降低冲突风险。

---

9. 综合实战一：同步模式、表结构与表数据

9.1 实战背景

很多项目不是简单创建一条复制链路，而是需要同时完成“结构同步规划、数据初始化和增量同步”三个环节。本套风哥教程本文通过一个完整项目案例，演示如何将业务模块从 fgedu-net-cn1 迁移到 fgedu-net-cn2。

9.2 项目实施步骤

步骤一：确认业务范围

需要同步的业务表如下：

• t_user
• t_order
• t_payment
• t_product
• t_stock

步骤二：确认复制模式

本项目采用“初始结构同步 + 全量数据导入 + 增量逻辑复制”的模式。

步骤三：导出发布端表结构

pg_dump -h fgedu-net-cn1 -U fgedu -d fgedudb \
  -s -t t_user -t t_order -t t_payment -t t_product -t t_stock \
  -f /fgedudb/backup/schema_business.sql

步骤四：导入订阅端表结构

psql -h fgedu-net-cn2 -U fgedu -d fgedudb \
  -f /fgedudb/backup/schema_business.sql

步骤五：导出发布端业务数据

pg_dump -h fgedu-net-cn1 -U fgedu -d fgedudb \
  -a -t t_user -t t_order -t t_payment -t t_product -t t_stock \
  -f /fgedudb/backup/data_business.sql

步骤六：导入订阅端业务数据

psql -h fgedu-net-cn2 -U fgedu -d fgedudb \
  -f /fgedudb/backup/data_business.sql

步骤七：在发布端创建发布

CREATE PUBLICATION pub_project_business
FOR TABLE t_user, t_order, t_payment, t_product, t_stock;

步骤八：在订阅端创建订阅

CREATE SUBSCRIPTION sub_project_business
CONNECTION 'host=fgedu-net-cn1 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'
PUBLICATION pub_project_business
WITH (copy_data = false);

这里使用 copy_data = false，因为数据已经通过 pg_dump 初始化完成。

步骤九：验证数据一致性

SELECT count(*) FROM t_order;
SELECT count(*) FROM t_user;
SELECT count(*) FROM t_payment;

分别在发布端和订阅端执行，确认记录数一致。

步骤十：验证增量同步

发布端：

INSERT INTO t_product (product_id, product_name, price, status)
VALUES (99999, 'fengge_product', 999.00, 1);

订阅端：

SELECT * FROM t_product WHERE product_id = 99999;

9.3 项目切换建议

当复制延迟稳定且业务验证通过后，可以在低峰期完成应用切换。切换前应确认：

• 发布端与订阅端数据一致；
• 复制延迟在可接受范围内；
• 应用连接字符串已准备好；
• 回滚方案已明确；
• 业务方已确认切换窗口。

---

10. 综合实战二：多对一数据汇聚

10.1 实战背景

多对一汇聚是逻辑复制的典型高级场景。多个业务源将各自的业务数据同步到同一个目标库，用于报表分析、统一查询或数据中台建设。

10.2 环境规划

假设存在两个源端和一个目标端：

角色	主机名	说明
源端1	fgedu-net-cn1	业务库 A
源端2	fgedu-net-cn2	业务库 B
目标端	新增目标主机	汇聚库

为便于演示，本套风哥教程本文继续使用 fgedu-net-cn1 和 fgedu-net-cn2 作为两个源端，目标端逻辑库仍使用 fgedudb。

10.3 在源端1创建发布

CREATE PUBLICATION pub_source1 FOR TABLE t_order, t_user;

10.4 在源端2创建发布

CREATE PUBLICATION pub_source2 FOR TABLE t_order, t_user;

10.5 在目标端创建订阅1

CREATE SUBSCRIPTION sub_source1
CONNECTION 'host=fgedu-net-cn1 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'
PUBLICATION pub_source1;

10.6 在目标端创建订阅2

CREATE SUBSCRIPTION sub_source2
CONNECTION 'host=fgedu-net-cn2 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'
PUBLICATION pub_source2;

10.7 处理主键冲突

多对一汇聚最大的问题是主键冲突。如果两个源端使用相同主键范围，目标端无法判断哪条记录为准。

常用解决方案：

1. 在业务层设计全局唯一 ID；
2. 在表中增加来源标识列；
3. 使用复合主键；
4. 应用层预先做 ID 段隔离。

示例：

ALTER TABLE t_order ADD COLUMN source_id VARCHAR(32);
UPDATE t_order SET source_id = 'fgedu-net-cn1';

目标端将主键改为 (order_id, source_id)：

ALTER TABLE t_order DROP CONSTRAINT t_order_pkey;
ALTER TABLE t_order ADD PRIMARY KEY (order_id, source_id);

10.8 验证汇聚结果

SELECT source_id, count(*) FROM t_order GROUP BY source_id;

如果两个源端数据都已进入目标端，说明多对一汇聚链路已建立。

10.9 多对一项目注意事项

• 必须提前设计主键策略；
• 必须明确数据冲突规则；
• 必须监控每个源端的延迟；
• 必须避免业务直接写入目标端造成数据混乱；
• 必须保留来源标识，便于问题追溯。

---

11. 风哥针对本文总结

PostgreSQL 逻辑复制是一项理论性与实践性都很强的数据库技术。本套风哥教程从逻辑复制的基础概念、核心架构、发布订阅模型、复制槽机制开始，逐步进入项目环境准备、单表复制、多表复制、运维监控、pglogical 扩展以及综合项目实战。

风哥认为，真正掌握逻辑复制，不能只停留在创建发布和订阅的命令层面，而要理解 WAL 解析、复制槽位置、事务顺序、冲突处理、监控告警和切换流程。生产环境中的逻辑复制项目，通常不是“能不能复制”的问题，而是“能不能稳定复制、能不能处理冲突、能不能在业务窗口内完成切换”的问题。

从实战角度看，单表复制是基础，多表复制是常态，多对一汇聚是进阶。每一层都需要更强的结构治理、数据校验和运维能力。本套风哥教程中涉及的主机名、路径、数据库名、用户名和参数配置，都是为了帮助读者形成可复用的项目模板。只要理解这些模板背后的设计思想，后续就可以根据实际业务规模进行调整。

风哥针对本文总结如下：

第一，逻辑复制适合部分表同步、数据迁移、多源汇聚和跨版本数据同步，但不适合替代所有高可用场景。

第二，发布端、订阅端和复制槽是逻辑复制的铁三角。发布端负责产生变更，订阅端负责应用变更，复制槽负责记录消费位置。

第三，环境参数必须按服务器规格规划。本套风哥教程基于 64G 内存、8CPU 环境，对共享内存、工作内存、WAL 日志和复制进程数进行了合理配置。

第四，单表复制是项目起点，多表复制是项目主体。真实业务通常是多张表协同复制，必须关注外键依赖、数据到达顺序和结构一致性。

第五，监控是生产项目的生命线。复制延迟、复制槽状态、WAL 积压、错误日志和冲突处理，决定了逻辑复制链路能否长期稳定运行。

第六，pglogical 扩展提供更复杂的复制能力，但也引入更高的配置复杂度。是否使用 pglogical，应根据项目复杂度、冲突策略和团队维护能力决定。

第七，综合项目必须按步骤实施。结构导出、数据初始化、增量配置、数据校验、业务验证和切换计划，缺一不可。

第八，多对一汇聚的核心不是复制命令，而是主键策略和冲突规则。没有清晰的数据归属策略，多源汇聚一定会出现故障。

最后，风哥强调：数据库架构设计的目标不是追求复杂技术，而是用最合适的技术解决业务问题。逻辑复制的价值，在于它能把数据变化以更灵活的方式组织起来，为业务迁移、系统改造和数据平台建设提供支撑。