外观
PostgreSQL 源端和目标端数据库配置
核心概念
逻辑复制
逻辑复制是PostgreSQL 10+引入的一种复制技术，它基于WAL日志的逻辑内容进行复制，而不是物理块。与物理复制相比，逻辑复制具有以下特点：
- 粒度更细：可以复制特定的数据库、模式或表
- 跨版本复制：支持不同PostgreSQL版本之间的复制
- 从库可写：从库可以写入数据，而不影响复制
- 选择性复制：可以只复制特定的数据行或列
- 支持异构系统：可以与其他数据库系统进行数据同步
核心组件
- 发布者（Publisher）：源数据库，负责生成和发送逻辑WAL日志
- 订阅者（Subscriber）：目标数据库，负责接收和应用逻辑WAL日志
- 发布（Publication）：源端数据库上定义的一组要复制的对象（数据库、模式、表）
- 订阅（Subscription）：目标端数据库上定义的对发布的订阅关系
- 复制槽（Replication Slot）：用于确保源端数据库不会删除目标端数据库尚未处理的WAL日志
- 逻辑解码：将物理WAL日志转换为逻辑格式的过程
逻辑复制的工作原理
- 源端数据库将数据库修改记录到WAL日志
- 源端数据库的逻辑解码进程将WAL日志转换为逻辑变更
- 源端数据库通过复制槽将逻辑变更发送给目标端数据库
- 目标端数据库接收逻辑变更并应用到本地数据库
- 目标端数据库确认已处理的WAL位置，源端数据库更新复制槽状态
源端数据库配置
启用逻辑复制
bash
# 修改源端数据库的postgresql.conf文件
nano /etc/postgresql/15/main/postgresql.conf添加或修改以下配置：
txt
# 启用逻辑复制
wal_level = logical # 必须设置为logical
max_replication_slots = 10 # 最大复制槽数量
max_wal_senders = 10 # 最大WAL发送进程数
max_worker_processes = 16 # 最大工作进程数，影响逻辑解码性能
# 可选：启用WAL压缩
wal_compression = on重启源端数据库服务
bash
pg_ctl restart -D /var/lib/postgresql/15/main创建发布
创建发布用户
sql
-- 登录源端数据库数据库
psql -h 源端数据库IP -U postgres
-- 创建发布用户
CREATE USER publisher_user WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'publisher_pass';
-- 授予用户对要复制表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO publisher_user;
GRANT USAGE ON SCHEMA public TO publisher_user;创建发布
sql
-- 创建发布，复制public模式下的所有表
CREATE PUBLICATION my_publication
FOR ALL TABLES IN SCHEMA public;
-- 或只复制特定表
CREATE PUBLICATION my_publication
FOR TABLE users, orders, products;
-- 或复制所有表，并自动添加新表
CREATE PUBLICATION my_publication
FOR ALL TABLES;
-- 查看创建的发布
\dP
SELECT * FROM pg_publication;向发布中添加或移除表
sql
-- 向发布中添加表
ALTER PUBLICATION my_publication
ADD TABLE customers;
-- 从发布中移除表
ALTER PUBLICATION my_publication
DROP TABLE products;配置pg_hba.conf
bash
# 修改pg_hba.conf文件
nano /etc/postgresql/15/main/pg_hba.conf添加目标端数据库的连接权限：
txt
# 允许目标端数据库连接到源端数据库
host replication publisher_user 目标端数据库IP/32 md5重新加载配置
bash
pg_ctl reload -D /var/lib/postgresql/15/main目标端数据库配置
准备目标端数据库
sql
-- 登录目标端数据库
psql -h 目标端数据库IP -U postgres
-- 创建与源端数据库相同的表结构
-- 注意：表结构必须与源端数据库完全匹配
CREATE TABLE users (
id serial PRIMARY KEY,
name varchar(100) NOT NULL,
email varchar(100) UNIQUE NOT NULL,
created_at timestamp DEFAULT now()
);
-- 创建其他需要复制的表创建订阅用户
sql
-- 创建订阅用户
CREATE USER subscriber_user WITH ENCRYPTED PASSWORD 'subscriber_pass';
-- 授予用户对目标表的权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO subscriber_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO subscriber_user;创建订阅
sql
-- 创建订阅
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=源端数据库IP port=5432 dbname=postgres user=publisher_user password=publisher_pass'
PUBLICATION my_publication;
-- 查看创建的订阅
\dS
SELECT * FROM pg_subscription;验证数据同步
sql
-- 在源端数据库上插入测试数据
INSERT INTO users (name, email) VALUES ('测试用户1', 'test1@example.com');
INSERT INTO users (name, email) VALUES ('测试用户2', 'test2@example.com');
-- 在目标端数据库上验证数据是否同步
SELECT * FROM users;订阅的高级配置
启用/禁用订阅
sql
-- 禁用订阅
ALTER SUBSCRIPTION my_subscription DISABLE;
-- 启用订阅
ALTER SUBSCRIPTION my_subscription ENABLE;刷新订阅
当源端数据库添加了新表到发布中时，需要刷新订阅：
sql
-- 刷新订阅，获取新添加的表
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;删除订阅
sql
-- 删除订阅
DROP SUBSCRIPTION my_subscription;高级配置与管理
复制槽管理
sql
-- 查看复制槽状态
SELECT * FROM pg_replication_slots;
-- 手动创建逻辑复制槽
SELECT * FROM pg_create_logical_replication_slot('my_logical_slot', 'pgoutput');
-- 删除复制槽
SELECT pg_drop_replication_slot('my_logical_slot');冲突处理
逻辑复制中可能出现数据冲突，需要配置冲突处理策略：
sql
-- 设置冲突处理策略
-- error: 遇到冲突时报错（默认）
-- skip: 跳过冲突的变更
-- last_update_wins: 最后更新的记录获胜
-- 在目标端数据库上配置
ALTER SUBSCRIPTION my_subscription SET (
conflict_resolution = 'last_update_wins'
);性能优化
源端数据库优化
sql
-- 增加WAL发送进程数
ALTER SYSTEM SET max_wal_senders = 20;
-- 增加复制槽数量
ALTER SYSTEM SET max_replication_slots = 20;
-- 优化逻辑解码性能
ALTER SYSTEM SET max_worker_processes = 32;
ALTER SYSTEM SET wal_level = logical;目标端数据库优化
sql
-- 增加应用进程数
ALTER SYSTEM SET max_parallel_workers_per_gather = 8;
-- 调整同步提交级别
ALTER SYSTEM SET synchronous_commit = 'remote_write';监控逻辑复制
监控源端数据库
sql
-- 查看发布状态
SELECT * FROM pg_stat_publication;
-- 查看发布表状态
SELECT * FROM pg_stat_publication_tables;
-- 查看复制槽状态
SELECT * FROM pg_replication_slots WHERE slot_type = 'logical';监控目标端数据库
sql
-- 查看订阅状态
SELECT * FROM pg_stat_subscription;
-- 查看订阅表状态
SELECT * FROM pg_stat_subscription_tables;最佳实践
配置最佳实践
- 合理设置wal_level：确保设置为logical，启用逻辑复制
- 足够的复制槽：根据目标端数据库数量设置合理的max_replication_slots
- 适当的WAL发送进程：根据目标端数据库数量设置max_wal_senders
- 优化逻辑解码：增加max_worker_processes提高逻辑解码性能
- 使用复制槽：确保源端数据库不会删除目标端数据库尚未处理的WAL日志
- 配置冲突处理策略：根据业务需求选择合适的冲突处理策略
安全最佳实践
- 使用专用用户：为逻辑复制创建专用的源端数据库和目标端数据库用户
- 最小权限原则：只授予复制所需的最小权限
- 使用强密码：为复制用户设置强密码
- 限制网络访问：在pg_hba.conf中限制目标端数据库的IP访问范围
- 启用SSL：在生产环境中启用SSL加密复制连接
运维最佳实践
- 定期监控：监控源端数据库和目标端数据库的复制状态
- 定期备份：定期备份源端数据库和目标端数据库
- 测试复制：定期测试复制的完整性和性能
- 文档化配置：详细记录逻辑复制的配置和管理流程
- 版本兼容性：确保源端数据库和目标端数据库版本兼容（建议使用相同版本）
- 避免大事务：大事务会导致复制延迟，尽量避免
性能最佳实践
- 合理分区表：对大表进行分区，提高复制性能
- 优化WAL生成：调整checkpoint配置，减少WAL生成量
- 启用WAL压缩：减少网络传输量
- 使用并行复制：优化目标端数据库的并行应用配置
- 合理设置同步提交级别：根据业务需求选择合适的同步提交级别
常见问题（FAQ）
Q1：逻辑复制和物理复制有什么区别？
A1：主要区别如下：
| 特性 | 物理复制 | 逻辑复制 |
|---|---|---|
| 复制粒度 | 整个数据库 | 数据库、模式、表、行、列 |
| 从库状态 | 只读 | 可写 |
| 跨版本复制 | 有限支持 | 良好支持 |
| 异构系统支持 | 不支持 | 支持 |
| 配置复杂度 | 简单 | 复杂 |
| 性能 | 高 | 相对较低 |
Q2：如何选择发布和订阅的表？
A2：可以根据以下原则选择：
- 只复制需要同步的表，减少复制开销
- 考虑表的大小和更新频率，避免复制过大或更新频繁的表
- 确保发布和订阅的表结构完全一致
- 对于大表，考虑使用分区表提高复制性能
Q3：逻辑复制出现冲突怎么办？
A3：可以采取以下措施：
- 检查冲突原因，修复数据不一致
- 配置合适的冲突处理策略
- 重新初始化订阅
- 确保目标端数据库上的应用不会修改复制的表
Q4：如何重新初始化订阅？
A4：可以按照以下步骤重新初始化：
- 删除现有订阅
- 清理目标端数据库上的复制数据
- 重新创建订阅
- 验证数据同步
Q5：逻辑复制支持DDL复制吗？
A5：PostgreSQL 14+开始支持DDL复制，但需要使用pgoutput插件并配置合适的参数：
sql
-- 创建发布时包含DDL
CREATE PUBLICATION my_publication
FOR ALL TABLES
WITH (publish_via_partition_root = true, publish_ddl = 'table');Q6：如何监控逻辑复制的延迟？
A6：可以使用以下方法：
sql
-- 在目标端数据库上查看复制延迟
SELECT
subscription_name,
now() - last_msg_receipt_time AS last_msg_delay,
last_sync_time,
now() - last_sync_time AS sync_delay
FROM pg_stat_subscription;Q7：逻辑复制可以用于数据迁移吗？
A7：是的，逻辑复制可以用于数据迁移，特别是跨版本迁移：
- 在源数据库上创建发布
- 在目标数据库上创建订阅
- 等待数据同步完成
- 切换应用到目标数据库
- 停止并删除复制关系
Q8：如何处理大表的初始同步？
A8：对于大表，可以采取以下方法：
- 使用pg_dump/pg_restore进行初始数据加载
- 然后创建订阅，从当前WAL位置开始复制
- 或者使用pg_basebackup创建基础备份，然后配置逻辑复制
逻辑复制的应用场景
- 数据仓库同步：将生产数据库的数据同步到数据仓库
- 跨版本升级：在不同PostgreSQL版本之间迁移数据
- 读写分离：将特定表的读写分离到不同数据库
- 多活架构：构建多活数据库架构
- 数据集成：与其他数据库系统进行数据集成
- 微服务数据同步：在微服务架构中同步数据
- 数据备份：作为数据备份的补充
- 测试环境同步：将生产数据同步到测试环境
通过合理配置和管理PostgreSQL源端数据库与目标端数据库，可以构建灵活、高效的数据复制架构，满足不同业务场景的需求。