数据库教程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 环境，建议调整以下内核参数。这些参数会影响共享内存、网络连接、文件句柄和进程调度能力。

```bash

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 用户资源限制规划

```bash

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 目录规划

```bash

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` 中配置以下参数：

```ini

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` 中允许复制链路。

```ini

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

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

```

配置完成后重载配置：

```sql

SELECT pg_reload_conf();

```

## 4.7 主机名解析配置

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

```bash

cat >> /etc/hosts << 'EOF'

192.168.10.11 fgedu-net-cn1

192.168.10.12 fgedu-net-cn2

EOF

```

## 4.8 数据库初始化

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

```bash

su - fgedu

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

```

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

```bash

pg_ctl start -D /fgedudb/pgdata

```

## 4.9 创建业务数据库与用户

```sql

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 在发布端创建测试表

```sql

\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 插入初始数据

```sql

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 在发布端创建发布

```sql

CREATE PUBLICATION pub_t_order FOR TABLE t_order;

```

查看发布信息：

```sql

SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete

FROM pg_publication;

```

## 5.5 在订阅端创建目标表

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

```sql

\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 在订阅端创建订阅

```sql

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 查看订阅状态

```sql

SELECT subname, subenabled, subconninfo, subpublications

FROM pg_subscription;

```

## 5.8 查看复制槽状态

在发布端执行：

```sql

SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn

FROM pg_replication_slots;

```

## 5.9 验证数据同步

在发布端插入新数据：

```sql

INSERT INTO t_order (order_id, user_id, amount, status)

VALUES (999999, 8888, 1288.50, 1);

```

在订阅端查询：

```sql

SELECT * FROM t_order WHERE order_id = 999999;

```

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

## 5.10 增量更新测试

在发布端执行更新：

```sql

UPDATE t_order SET status = 2, update_time = CURRENT_TIMESTAMP

WHERE order_id = 999999;

```

订阅端验证：

```sql

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

```

## 5.11 删除操作测试

发布端：

```sql

DELETE FROM t_order WHERE order_id = 999999;

```

订阅端：

```sql

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

```

## 5.12 单表复制常见问题

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

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

```sql

ALTER TABLE t_order REPLICA IDENTITY USING INDEX t_order_pkey;

```

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

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

```sql

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 在发布端创建多张业务表

```sql

\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 插入测试数据

```sql

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 创建多表发布

```sql

CREATE PUBLICATION pub_business_module

FOR TABLE t_user, t_order, t_payment;

```

查看发布对象：

```sql

SELECT * FROM pg_publication_tables

WHERE pubname = 'pub_business_module';

```

## 6.5 在订阅端创建对应表

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

```sql

\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 创建多表订阅

```sql

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 验证多表同步

发布端：

```sql

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);

```

订阅端：

```sql

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 查看订阅状态

```sql

SELECT subname, subenabled, subconninfo, subpublications

FROM pg_subscription;

```

## 7.3 查看复制槽状态

```sql

SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn

FROM pg_replication_slots;

```

## 7.4 计算复制延迟

发布端执行：

```sql

SELECT now() - pg_stat_replication.write_lag AS write_lag,

       now() - pg_stat_replication.replay_lag AS replay_lag

FROM pg_stat_replication;

```

订阅端执行：

```sql

SELECT subname, received_lsn, latest_end_lsn,

       latest_end_time - received_time AS apply_lag

FROM pg_stat_subscription;

```

## 7.5 监控 WAL 积压

```sql

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 查看复制错误日志

```bash

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

```

常见错误包括：

- 表结构不一致；

- 主键冲突；

- 外键约束失败；

- 复制槽缺失；

- 连接认证失败；

- 网络中断导致订阅失效。

## 7.7 暂停与恢复订阅

```sql

ALTER SUBSCRIPTION sub_business_module DISABLE;

ALTER SUBSCRIPTION sub_business_module ENABLE;

```

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

## 7.8 删除订阅与复制槽

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

```sql

DROP SUBSCRIPTION IF EXISTS sub_business_module;

```

发布端删除复制槽：

```sql

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 版本与扩展版本兼容。本套风哥教程按统一项目环境进行安装。

```bash

yum install -y pglogical

```

或从源码包安装：

```bash

tar -zxf pglogical.tar.gz

cd pglogical

make

make install

```

## 8.3 修改共享库配置

```ini

shared_preload_libraries = 'pglogical'

```

修改后重启数据库：

```bash

pg_ctl restart -D /fgedudb/pgdata

```

## 8.4 创建扩展

```sql

CREATE EXTENSION pglogical;

```

验证安装：

```sql

SELECT extname FROM pg_extension WHERE extname = 'pglogical';

```

## 8.5 在发布端创建节点

```sql

SELECT pglogical.create_node(

    node_name := 'provider_node',

    dsn := 'host=fgedu-net-cn1 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'

);

```

## 8.6 在订阅端创建节点

```sql

SELECT pglogical.create_node(

    node_name := 'subscriber_node',

    dsn := 'host=fgedu-net-cn2 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'

);

```

## 8.7 创建复制集

```sql

SELECT pglogical.create_replication_set(

    set_name := 'rs_business',

    replicate_insert := true,

    replicate_update := true,

    replicate_delete := true,

    replicate_truncate := false

);

```

## 8.8 将表加入复制集

```sql

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 在订阅端创建订阅

```sql

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 状态

```sql

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`

### 步骤二：确认复制模式

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

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

```bash

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

```

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

```bash

psql -h fgedu-net-cn2 -U fgedu -d fgedudb \

  -f /fgedudb/backup/schema_business.sql

```

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

```bash

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

```

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

```bash

psql -h fgedu-net-cn2 -U fgedu -d fgedudb \

  -f /fgedudb/backup/data_business.sql

```

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

```sql

CREATE PUBLICATION pub_project_business

FOR TABLE t_user, t_order, t_payment, t_product, t_stock;

```

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

```sql

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` 初始化完成。

### 步骤九：验证数据一致性

```sql

SELECT count(*) FROM t_order;

SELECT count(*) FROM t_user;

SELECT count(*) FROM t_payment;

```

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

### 步骤十：验证增量同步

发布端：

```sql

INSERT INTO t_product (product_id, product_name, price, status)

VALUES (99999, 'fengge_product', 999.00, 1);

```

订阅端：

```sql

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创建发布

```sql

CREATE PUBLICATION pub_source1 FOR TABLE t_order, t_user;

```

## 10.4 在源端2创建发布

```sql

CREATE PUBLICATION pub_source2 FOR TABLE t_order, t_user;

```

## 10.5 在目标端创建订阅1

```sql

CREATE SUBSCRIPTION sub_source1

CONNECTION 'host=fgedu-net-cn1 port=5432 dbname=fgedudb user=fgedu password=Fgedu@2025'

PUBLICATION pub_source1;

```

## 10.6 在目标端创建订阅2

```sql

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 段隔离。

示例：

```sql

ALTER TABLE t_order ADD COLUMN source_id VARCHAR(32);

UPDATE t_order SET source_id = 'fgedu-net-cn1';

```

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

```sql

ALTER TABLE t_order DROP CONSTRAINT t_order_pkey;

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

```

## 10.8 验证汇聚结果

```sql

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

```

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

## 10.9 多对一项目注意事项

- 必须提前设计主键策略；

- 必须明确数据冲突规则；

- 必须监控每个源端的延迟；

- 必须避免业务直接写入目标端造成数据混乱；

- 必须保留来源标识，便于问题追溯。

---

# 11. 风哥针对本文总结

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

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

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

风哥针对本文总结如下：

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

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

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

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

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

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

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

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

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