PostgreSQL 逻辑复制配置

核心概念

PostgreSQL 逻辑复制是一种基于日志的复制机制，它允许将一个数据库中的数据更改复制到另一个数据库中。与物理复制不同，逻辑复制可以实现表级复制，支持跨版本复制，并且可以在不同架构的数据库之间复制数据。

发布者（Publisher）：源数据库，负责将数据更改发布到一个或多个订阅者
订阅者（Subscriber）：目标数据库，负责从发布者接收数据更改并应用到本地
发布（Publication）：发布者上的一个对象，定义了要复制的表和操作类型
订阅（Subscription）：订阅者上的一个对象，定义了要连接的发布者和发布
WAL：预写式日志，包含了数据库的所有更改
逻辑解码：将WAL日志转换为逻辑更改的过程

逻辑复制优势

表级复制：可以只复制特定的表，而不是整个数据库
跨版本复制：支持在不同PostgreSQL版本之间复制数据
跨架构复制：支持在不同架构的数据库之间复制数据
灵活的复制策略：可以选择复制INSERT、UPDATE、DELETE或TRUNCATE操作
支持筛选：可以通过WHERE子句筛选要复制的数据

逻辑复制配置

1. 环境要求

PostgreSQL 10+版本
启用WAL逻辑解码
发布者和订阅者之间的网络连通性
相同的表结构（或兼容的表结构）

2. 发布者配置

2.1 启用逻辑复制

sql

-- 检查wal_level参数
SHOW wal_level;

-- 如果wal_level不是logical，需要修改
ALTER SYSTEM SET wal_level = logical;

-- 检查max_replication_slots参数
SHOW max_replication_slots;

-- 调整max_replication_slots参数
ALTER SYSTEM SET max_replication_slots = 10;

-- 检查max_wal_senders参数
SHOW max_wal_senders;

-- 调整max_wal_senders参数
ALTER SYSTEM SET max_wal_senders = 10;

-- 重启数据库使配置生效
-- sudo systemctl restart postgresql

2.2 创建发布

sql

-- 创建发布，包含所有表的INSERT、UPDATE、DELETE操作
CREATE PUBLICATION my_publication;

-- 创建发布，包含特定表
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- 创建发布，包含特定操作类型
CREATE PUBLICATION my_publication FOR TABLE users, orders WITH (publish = 'insert, update');

-- 创建发布，包含所有表
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- 查看发布
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

2.3 授权复制用户

sql

-- 创建复制用户
CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'strong_password';

-- 授权用户访问要复制的表
GRANT SELECT ON users, orders TO repl_user;

-- 如果是ALL TABLES发布，需要授权所有表
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_user;

-- 授权未来创建的表
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO repl_user;

3. 订阅者配置

3.1 启用逻辑复制

sql

-- 检查max_replication_slots参数
SHOW max_replication_slots;

-- 调整max_replication_slots参数
ALTER SYSTEM SET max_replication_slots = 10;

-- 检查max_logical_replication_workers参数
SHOW max_logical_replication_workers;

-- 调整max_logical_replication_workers参数
ALTER SYSTEM SET max_logical_replication_workers = 4;

-- 检查max_worker_processes参数
SHOW max_worker_processes;

-- 确保max_worker_processes足够大
ALTER SYSTEM SET max_worker_processes = 16;

-- 重启数据库使配置生效
-- sudo systemctl restart postgresql

3.2 创建订阅

sql

-- 创建目标表（如果不存在）
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10, 2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订阅
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION my_publication;

-- 查看订阅
SELECT * FROM pg_subscription;

3.3 配置订阅选项

sql

-- 创建订阅时指定复制槽名称
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION my_publication
  WITH (slot_name = 'my_slot');

-- 禁用自动同步（适用于已存在数据的表）
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION my_publication
  WITH (copy_data = false);

-- 启用双向复制
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION my_publication
  WITH (two_phase = true);

表级复制配置

1. 单个表复制

sql

-- 在发布者上创建表级发布
CREATE PUBLICATION users_publication FOR TABLE users;

-- 在订阅者上创建对应表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 在订阅者上创建订阅
CREATE SUBSCRIPTION users_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION users_publication;

2. 多个表复制

sql

-- 在发布者上创建多表发布
CREATE PUBLICATION multi_tables_publication FOR TABLE users, orders, products;

-- 在订阅者上创建对应表
-- ... 创建users、orders、products表 ...

-- 在订阅者上创建订阅
CREATE SUBSCRIPTION multi_tables_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION multi_tables_publication;

3. 所有表复制

sql

-- 在发布者上创建所有表发布
CREATE PUBLICATION all_tables_publication FOR ALL TABLES;

-- 在订阅者上确保有相同的表结构
-- 可以使用pg_dump导出表结构
-- pg_dump -h publisher_host -U postgres -d mydb -s -f schema.sql
-- psql -h subscriber_host -U postgres -d mydb -f schema.sql

-- 在订阅者上创建订阅
CREATE SUBSCRIPTION all_tables_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION all_tables_publication;

冲突处理

1. 冲突类型

主键冲突：订阅者上已存在相同主键的记录
唯一约束冲突：订阅者上已存在相同唯一约束的记录
外键冲突：引用的外键不存在

2. 冲突处理配置

sql

-- 创建订阅时设置冲突处理策略
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION my_publication
  WITH (conflict_resolution = 'last_update_wins');

-- 或使用first_update_wins策略
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION my_publication
  WITH (conflict_resolution = 'first_update_wins');

3. 手动处理冲突

sql

-- 查看冲突
SELECT * FROM pg_stat_subscription;

-- 查看冲突详细信息
SELECT * FROM pg_subscription_rel WHERE srsubstate = 'e';

-- 手动解决冲突
-- 1. 查看冲突数据
SELECT * FROM users WHERE id = conflict_id;

-- 2. 删除冲突记录
DELETE FROM users WHERE id = conflict_id;

-- 3. 重新启动订阅
ALTER SUBSCRIPTION my_subscription ENABLE;

逻辑复制监控

1. 发布者监控

sql

-- 查看复制槽状态
SELECT * FROM pg_replication_slots;

-- 查看WAL发送者状态
SELECT * FROM pg_stat_replication;

-- 查看发布状态
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

2. 订阅者监控

sql

-- 查看订阅状态
SELECT * FROM pg_subscription;

-- 查看订阅关系状态
SELECT * FROM pg_subscription_rel;

-- 查看订阅统计信息
SELECT * FROM pg_stat_subscription;

-- 查看逻辑复制工作进程
SELECT * FROM pg_stat_activity WHERE application_name LIKE '%subscription%';

3. 监控命令

sql

-- 在发布者上查看发布信息
SELECT pubname, pubowner, puballtables, pubinsert, pubupdate, pubdelete, pubtruncate
FROM pg_publication;

-- 在发布者上查看发布的表
SELECT pubname, schemaname, tablename
FROM pg_publication_tables
ORDER BY pubname, schemaname, tablename;

-- 在订阅者上查看订阅信息
SELECT subname, subpublications, subenabled, subconninfo
FROM pg_subscription;

-- 在订阅者上查看订阅的关系
SELECT srsubid, srrelid::regclass, srsubstate, srsublsn
FROM pg_subscription_rel
ORDER BY srsubid, srrelid;

逻辑复制维护

1. 暂停和恢复订阅

sql

-- 暂停订阅
ALTER SUBSCRIPTION my_subscription DISABLE;

-- 恢复订阅
ALTER SUBSCRIPTION my_subscription ENABLE;

2. 添加和移除表

sql

-- 在发布者上添加表到发布
ALTER PUBLICATION my_publication ADD TABLE new_table;

-- 在发布者上移除表从发布
ALTER PUBLICATION my_publication DROP TABLE old_table;

3. 刷新订阅

sql

-- 刷新订阅，重新同步所有表
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;

-- 刷新订阅，只同步特定表
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION WITH (copy_data = false);

4. 删除订阅和发布

sql

-- 删除订阅
DROP SUBSCRIPTION my_subscription;

-- 删除发布
DROP PUBLICATION my_publication;

-- 删除复制槽（如果需要）
SELECT pg_drop_replication_slot('my_slot');

逻辑复制最佳实践

1. 性能优化

合理设置复制槽数量：根据实际需求设置max_replication_slots参数
调整WAL保留时间：设置wal_keep_size参数，确保订阅者有足够的时间接收WAL日志
使用合适的复制方式：对于大表，考虑使用pg_dump先初始化数据，再启用逻辑复制
避免频繁的大事务：大事务会导致WAL日志膨胀，影响复制性能
使用多个订阅者：对于大规模数据复制，考虑使用多个订阅者分担负载

2. 安全性考虑

使用专用复制用户：创建专门的复制用户，授予最小必要权限
加密复制连接：使用SSL/TLS加密发布者和订阅者之间的连接
限制网络访问：通过防火墙限制只有订阅者可以访问发布者的5432端口
定期更换密码：定期更换复制用户的密码

3. 监控和告警

监控复制延迟：定期检查复制延迟，及时发现问题
监控复制槽状态：确保复制槽正常工作，避免WAL日志堆积
监控订阅状态：确保订阅正常运行，及时处理冲突
配置告警：当复制延迟超过阈值时发送告警

常见问题及解决方案

1. 复制延迟过高

可能原因：

网络带宽不足
发布者负载过高
订阅者负载过高
大事务导致WAL日志堆积

解决方案：

sql

-- 检查复制延迟
SELECT * FROM pg_stat_subscription;

-- 调整WAL保留时间
ALTER SYSTEM SET wal_keep_size = '1GB';

-- 增加复制工作进程
ALTER SYSTEM SET max_logical_replication_workers = 8;

-- 优化发布者和订阅者的性能
-- ... 性能优化措施 ...

2. 订阅失败

可能原因：

网络连接问题
认证失败
表结构不匹配
权限不足

解决方案：

sql

-- 查看订阅状态
SELECT * FROM pg_subscription_rel WHERE srsubstate = 'e';

-- 检查连接信息
SELECT subconninfo FROM pg_subscription WHERE subname = 'my_subscription';

-- 重新创建订阅
DROP SUBSCRIPTION IF EXISTS my_subscription;
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=repl_user password=strong_password'
  PUBLICATION my_publication;

3. 冲突频繁发生

可能原因：

双向复制导致的冲突
订阅者上有手动修改
主键或唯一约束冲突

解决方案：

sql

-- 设置冲突处理策略
ALTER SUBSCRIPTION my_subscription SET (conflict_resolution = 'last_update_wins');

-- 避免在订阅者上手动修改数据
-- 或使用双向复制时确保数据修改不会冲突

-- 手动解决冲突
DELETE FROM conflicting_table WHERE id = conflict_id;
ALTER SUBSCRIPTION my_subscription ENABLE;

常见问题（FAQ）

Q1：逻辑复制和物理复制有什么区别？

A1：逻辑复制和物理复制的主要区别：

复制粒度：逻辑复制是表级复制，物理复制是实例级复制
跨版本支持：逻辑复制支持跨版本复制，物理复制要求版本兼容
复制内容：逻辑复制只复制数据更改，物理复制复制整个数据库集群
灵活性：逻辑复制可以选择复制特定表和操作类型，物理复制不能
性能：物理复制性能更高，逻辑复制性能相对较低

Q2：逻辑复制支持哪些PostgreSQL版本？

A2：逻辑复制从PostgreSQL 10版本开始支持，建议使用PostgreSQL 11+版本，因为11版本对逻辑复制进行了很多改进和优化。

Q3：逻辑复制可以复制DDL语句吗？

A3：默认情况下，逻辑复制不复制DDL语句（如CREATE TABLE、ALTER TABLE等）。如果需要复制DDL语句，可以使用以下方法：

使用pg_logical_ddl_extension扩展
在发布者和订阅者上手动执行相同的DDL语句
使用第三方工具（如Londiste、Slony-I等）

Q4：逻辑复制的延迟是多少？

A4：逻辑复制的延迟取决于多种因素，包括：

网络带宽和延迟
发布者和订阅者的负载
事务大小和频率
复制工作进程数量

在正常情况下，逻辑复制的延迟应该在毫秒级别，对于大事务可能会有更高的延迟。

Q5：如何迁移现有数据到逻辑复制环境？

A5：迁移现有数据到逻辑复制环境的方法：

在发布者上创建表和数据
使用pg_dump导出数据
在订阅者上创建表结构
使用pg_restore导入数据
在发布者上创建发布
在订阅者上创建订阅，设置copy_data = false

这样可以确保现有数据被正确迁移，并且后续的数据更改会通过逻辑复制自动同步。

Q6：逻辑复制可以用于灾备吗？

A6：逻辑复制可以用于灾备，但需要考虑以下因素：

逻辑复制的延迟可能比物理复制高
逻辑复制不复制DDL语句，需要额外处理
逻辑复制可能存在冲突，需要冲突处理机制
逻辑复制不复制系统表，需要额外处理

PostgreSQL 逻辑复制配置 ​

核心概念 ​

逻辑复制优势 ​

逻辑复制配置 ​

1. 环境要求 ​

2. 发布者配置 ​

2.1 启用逻辑复制 ​

2.2 创建发布 ​

2.3 授权复制用户 ​

3. 订阅者配置 ​

3.1 启用逻辑复制 ​

3.2 创建订阅 ​

3.3 配置订阅选项 ​

表级复制配置 ​

1. 单个表复制 ​

2. 多个表复制 ​

3. 所有表复制 ​

冲突处理 ​

1. 冲突类型 ​

2. 冲突处理配置 ​

3. 手动处理冲突 ​

逻辑复制监控 ​

1. 发布者监控 ​

2. 订阅者监控 ​

3. 监控命令 ​

逻辑复制维护 ​

1. 暂停和恢复订阅 ​

2. 添加和移除表 ​

3. 刷新订阅 ​

4. 删除订阅和发布 ​

逻辑复制最佳实践 ​

1. 性能优化 ​

2. 安全性考虑 ​

3. 监控和告警 ​

常见问题及解决方案 ​

1. 复制延迟过高 ​

2. 订阅失败 ​

3. 冲突频繁发生 ​

常见问题（FAQ） ​

Q1：逻辑复制和物理复制有什么区别？ ​

Q2：逻辑复制支持哪些PostgreSQL版本？ ​

Q3：逻辑复制可以复制DDL语句吗？ ​

Q4：逻辑复制的延迟是多少？ ​

Q5：如何迁移现有数据到逻辑复制环境？ ​

Q6：逻辑复制可以用于灾备吗？ ​

PostgreSQL 逻辑复制配置

核心概念

逻辑复制优势

逻辑复制配置

1. 环境要求

2. 发布者配置

2.1 启用逻辑复制

2.2 创建发布

2.3 授权复制用户

3. 订阅者配置

3.1 启用逻辑复制

3.2 创建订阅

3.3 配置订阅选项

表级复制配置

1. 单个表复制

2. 多个表复制

3. 所有表复制

冲突处理

1. 冲突类型

2. 冲突处理配置

3. 手动处理冲突

逻辑复制监控

1. 发布者监控

2. 订阅者监控

3. 监控命令

逻辑复制维护

1. 暂停和恢复订阅

2. 添加和移除表

3. 刷新订阅

4. 删除订阅和发布

逻辑复制最佳实践

1. 性能优化

2. 安全性考虑

3. 监控和告警

常见问题及解决方案

1. 复制延迟过高

2. 订阅失败

3. 冲突频繁发生

常见问题（FAQ）

Q1：逻辑复制和物理复制有什么区别？

Q2：逻辑复制支持哪些PostgreSQL版本？

Q3：逻辑复制可以复制DDL语句吗？

Q4：逻辑复制的延迟是多少？

Q5：如何迁移现有数据到逻辑复制环境？

Q6：逻辑复制可以用于灾备吗？