印刻数据库

外观

PostgreSQL 表级复制 ​

PostgreSQL 表级复制允许在不同数据库实例之间复制特定表的数据，而不是整个数据库。这在需要将部分数据复制到其他环境或实现数据分片时非常有用。

表级复制实现方式 ​

1. 逻辑复制（PostgreSQL 10+） ​

逻辑复制是 PostgreSQL 10 引入的内置表级复制功能，基于发布-订阅模型。

配置步骤 ​

sql
-- 1. 在发布者（主库）上启用逻辑复制
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 5;
ALTER SYSTEM SET max_wal_senders = 10;
-- 重启数据库使配置生效

-- 2. 在发布者上创建发布
-- 发布单个表
CREATE PUBLICATION pub_table1 FOR TABLE users;
-- 发布多个表
CREATE PUBLICATION pub_tables FOR TABLE users, orders, products;
-- 发布所有表
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- 3. 在订阅者（备库）上创建订阅
CREATE SUBSCRIPTION sub_table1
  CONNECTION 'host=primary_host dbname=dbname user=replication password=password port=5432'
  PUBLICATION pub_table1;

-- 4. 验证订阅状态
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_subscription;

高级配置选项 ​

sql
-- 发布特定列
CREATE PUBLICATION pub_user_name FOR TABLE users (id, name);

-- 仅复制 INSERT 操作
CREATE PUBLICATION pub_insert_only FOR TABLE users WITH (publish = 'insert');

-- 复制 INSERT、UPDATE、DELETE 操作
CREATE PUBLICATION pub_all_ops FOR TABLE users WITH (publish = 'insert, update, delete');

-- 同步数据后创建订阅
CREATE SUBSCRIPTION sub_table1
  CONNECTION 'host=primary_host dbname=dbname user=replication password=password port=5432'
  PUBLICATION pub_table1
  WITH (copy_data = true);

2. 基于触发器的复制 ​

使用触发器实现表级复制，适用于较早版本的 PostgreSQL。

配置示例 ​

sql
-- 1. 在源表创建触发器函数
CREATE OR REPLACE FUNCTION replicate_table()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO target_schema.target_table VALUES (NEW.*);
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE target_schema.target_table SET (col1, col2) = (NEW.col1, NEW.col2) WHERE id = NEW.id;
  ELSIF TG_OP = 'DELETE' THEN
    DELETE FROM target_schema.target_table WHERE id = OLD.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. 为源表添加触发器
CREATE TRIGGER trg_replicate_table
AFTER INSERT OR UPDATE OR DELETE ON source_schema.source_table
FOR EACH ROW EXECUTE FUNCTION replicate_table();

3. 使用 pg_dump/pg_restore 进行表级备份恢复 ​

bash
# 导出单个表
pg_dump -h source_host -U postgres -d source_db -t source_table -f table_dump.sql

# 导出多个表
pg_dump -h source_host -U postgres -d source_db -t table1 -t table2 -f tables_dump.sql

# 导入到目标数据库
pg_restore -h target_host -U postgres -d target_db table_dump.sql

表级复制监控 ​

1. 逻辑复制监控 ​

sql
-- 查看订阅状态
SELECT * FROM pg_stat_subscription;

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

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

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

2. 性能监控 ​

sql
-- 监控复制延迟
SELECT 
  subscription_name,
  backend_start,
  state,
  sync_priority,
  sync_state,
  replay_lag,
  write_lag
FROM pg_stat_subscription;

-- 监控复制相关等待事件
SELECT 
  wait_event_type,
  wait_event,
  count(*)
FROM pg_stat_activity
WHERE application_name LIKE 'subscription%'
GROUP BY wait_event_type, wait_event;

最佳实践 ​

1. 生产环境配置建议 ​

  • 合理设置 WAL 级别：逻辑复制需要 wal_level = 'logical'
  • 配置足够的复制槽：每个订阅需要一个复制槽，建议设置 max_replication_slots = 2 * 订阅数量
  • 设置适当的 WAL 发送者：max_wal_senders = 10 或根据订阅数量调整
  • 定期监控复制状态：设置监控告警，当复制延迟超过阈值时及时通知
  • 使用专用复制用户：为逻辑复制创建专用用户，仅授予必要权限

2. 性能优化 ​

sql
-- 优化订阅者端的应用速度
ALTER SUBSCRIPTION sub_table1 SET (parallel_apply = 4);

-- 优化发布者端的 WAL 生成
ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE large_table SET (autovacuum_analyze_scale_factor = 0.025);

3. 安全性考虑 ​

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

-- 仅授予必要权限
GRANT SELECT ON TABLE users, orders TO repl_user;

常见问题处理 ​

1. 逻辑复制延迟过高 ​

问题：订阅者复制延迟不断增加

解决方法：

  • 检查网络连接是否稳定
  • 增加订阅者端的 parallel_apply 设置
  • 优化发布者端的 WAL 生成速率
  • 检查订阅者是否有长事务阻塞
sql
-- 查看订阅者端的长事务
SELECT * FROM pg_stat_activity WHERE state = 'active' AND xact_start < NOW() - INTERVAL '5 minutes';

2. 订阅初始化失败 ​

问题：创建订阅时提示 "could not connect to the publisher"

解决方法：

  • 检查连接字符串是否正确
  • 确认发布者的 listen_addresses 配置允许远程连接
  • 检查防火墙是否开放了 5432 端口
  • 确认复制用户的权限是否正确

3. 表结构不匹配 ​

问题：订阅者表结构与发布者不匹配

解决方法：

  • 在创建订阅前确保表结构完全一致
  • 使用 pg_dump -s 导出表结构并在订阅者端创建
  • 对于已有数据的表，考虑使用 pg_dump -t table -a 先同步数据

表级复制与其他复制方式比较 ​

复制方式版本要求灵活性性能复杂度适用场景
逻辑复制PostgreSQL 10+高中低表级复制、跨版本复制
基于触发器的复制所有版本高低高复杂过滤逻辑、特殊字段处理
pg_dump/pg_restore所有版本中高低一次性表级迁移
物理复制所有版本低高低完整数据库复制、高可用

常见问题（FAQ） ​

Q1：逻辑复制支持跨版本复制吗？ ​

A1：支持，逻辑复制可以在不同主版本的 PostgreSQL 之间进行复制，例如从 PostgreSQL 10 复制到 PostgreSQL 14。但建议发布者版本不高于订阅者版本。

Q2：如何暂停和恢复逻辑复制？ ​

A2：可以使用以下命令暂停和恢复逻辑复制：

sql
-- 暂停订阅
ALTER SUBSCRIPTION sub_table1 DISABLE;

-- 恢复订阅
ALTER SUBSCRIPTION sub_table1 ENABLE;

Q3：逻辑复制会复制 DDL 操作吗？ ​

A3：默认情况下，逻辑复制不会复制 DDL 操作。如果需要复制 DDL，需要手动在订阅者端执行相同的 DDL 语句，或者使用第三方工具如 pglogical。

Q4：如何删除订阅和发布？ ​

A4：使用以下命令删除订阅和发布：

sql
-- 删除订阅
DROP SUBSCRIPTION sub_table1;

-- 删除发布
DROP PUBLICATION pub_table1;

Q5：逻辑复制支持复制大对象吗？ ​

A5：逻辑复制不直接支持大对象（LO）的复制。对于大对象，可以考虑使用 lo_export 和 lo_import 手动复制，或者使用物理复制。

Q6：如何监控逻辑复制的性能？ ​

A6：可以通过以下方式监控：

  • 查询 pg_stat_subscription 视图查看复制延迟
  • 使用 Prometheus + Grafana 监控复制指标
  • 监控 PostgreSQL 日志中的复制相关信息
  • 使用 pg_stat_replication 视图查看 WAL 发送者状态

Q7：逻辑复制中的复制槽有什么作用？ ​

A7：复制槽确保发布者保留订阅者所需的所有 WAL 日志，防止 WAL 被过早回收导致复制失败。每个订阅会创建一个复制槽。

Q8：如何处理逻辑复制中的冲突？ ​

A8：逻辑复制中的冲突通常是由于订阅者端已有数据与发布者复制的数据冲突导致的。可以通过以下方式处理：

  • 在订阅者端使用 ON CONFLICT 子句处理冲突
  • 确保订阅者端没有写入操作（推荐只读）
  • 使用 initial_sync 选项重新初始化订阅
sql
-- 重新初始化订阅
ALTER SUBSCRIPTION sub_table1 REFRESH PUBLICATION;