PostgreSQL 逻辑复制

PostgreSQL 逻辑复制配置流程（标准版）

逻辑复制的核心角色只有两个：

• 发布端（Publisher / 源库）

• 订阅端（Subscriber / 目标库）

• 一、前置条件（必写）
  • 1️⃣ 版本要求
  • 2️⃣ 源表要求
• 二、发布端（Publisher）配置步骤
  • Step 1：修改 postgresql.conf
  • Step 2：创建逻辑复制用户
  • Step 3：授权复制用户访问权限
  • Step 4：创建发布（Publication）
    • 方式一：发布指定表（最常见）
    • 方式二：发布所有表（可写）
  • Step 5：检查发布是否成功
• 三、订阅端（Subscriber）配置步骤
  • Step 6：创建目标表（结构必须一致）
  • Step 7：创建订阅（Subscription）
  • Step 8：检查订阅状态
• 四、验证复制效果（必写）
  • 在发布端插入数据
  • 在订阅端查看数据
• 五、逻辑复制支持与限制（总结）
  • ✅ 支持
  • ❌ 不支持
• 六、“一句话流程总结”（强烈建议背）
• 七、重点（写在论述题里很稳）

---

一、前置条件（必写）

1️⃣ 版本要求

• PostgreSQL ≥ 10

2️⃣ 源表要求

• 被复制的表：
  • 必须有主键
  • 或设置 REPLICA IDENTITY FULL

ALTER TABLE test SET REPLICA IDENTITY FULL;

---

二、发布端（Publisher）配置步骤

Step 1：修改 postgresql.conf

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

修改后 必须重启数据库

pg_ctl restart

---

Step 2：创建逻辑复制用户

CREATE ROLE repuser
  LOGIN
  REPLICATION
  PASSWORD 'repuser';

---

Step 3：授权复制用户访问权限

GRANT CONNECT ON DATABASE testdb TO repuser;
GRANT USAGE ON SCHEMA public TO repuser;
GRANT SELECT ON TABLE test TO repuser;

---

Step 4：创建发布（Publication）

方式一：发布指定表（最常见）

CREATE PUBLICATION pub_test
FOR TABLE test;

方式二：发布所有表（可写）

CREATE PUBLICATION pub_all
FOR ALL TABLES;

---

Step 5：检查发布是否成功

SELECT * FROM pg_publication;

---

三、订阅端（Subscriber）配置步骤

Step 6：创建目标表（结构必须一致）

CREATE TABLE test (
  id INT PRIMARY KEY,
  info TEXT
);

✔ 表名、字段名、字段类型必须一致
✔ 字段顺序可以不同

---

Step 7：创建订阅（Subscription）

CREATE SUBSCRIPTION sub_test
CONNECTION 'host=192.168.1.10 port=5432 dbname=testdb user=repuser password=repuser'
PUBLICATION pub_test;

这里的 host 是 发布端 IP

---

Step 8：检查订阅状态

SELECT * FROM pg_subscription;

---

四、验证复制效果（必写）

在发布端插入数据

INSERT INTO test VALUES (1, 'hello logical replication');

在订阅端查看数据

SELECT * FROM test;

👉 能查到数据，说明 逻辑复制生效

---

五、逻辑复制支持与限制（总结）

✅ 支持

• INSERT
• UPDATE
• DELETE

❌ 不支持

• TRUNCATE
• DDL（ALTER TABLE、DROP TABLE 等）

表结构变更后：

ALTER SUBSCRIPTION sub_test REFRESH PUBLICATION;

---

六、“一句话流程总结”（强烈建议背）

wal_level=logical → 创建复制用户 → 创建 publication → 订阅端建表 → 创建 subscription

---

七、重点（写在论述题里很稳）

• 一个数据库可以有：
  • 多个 publication
  • 多个 subscription
• 逻辑复制是：
  • 表级复制
  • 异构架构友好
  • 不依赖物理 WAL 文件位置

---