PostgreSQL逻辑复制实战：高可用集群与数据同步方案

小编 • • 数据库

PostgreSQL逻辑复制与物理复制的区别

PostgreSQL高可用方案中数据复制分为两种模式：物理复制和逻辑复制。物理复制基于WAL（Write-Ahead Logging）字节流同步，从库与主库完全一致，只能全库复制，从库不可写。逻辑复制基于逻辑解码（Logical Decoding）将WAL解析为逻辑变更事件，支持选择性复制（按表、按行过滤），目标库可读写，可跨大版本复制。

逻辑复制适用于以下场景：部分表同步到分析库、跨版本升级迁移、多活数据中心双向同步、数据分发到异构系统。物理复制适用于简单的主从高可用场景，延迟更低、配置更简单。

逻辑复制的配置流程

逻辑复制需要在发布端（Publisher）和订阅端（Subscriber）分别配置。发布端创建发布（Publication），订阅端创建订阅（Subscription）。

发布端配置：

-- postgresql.conf 关键参数
wal_level = logical          -- 必须设置为logical
max_replication_slots = 10   -- 复制槽数量
max_wal_senders = 10         -- WAL发送进程数
max_worker_processes = 16    -- 工作进程数

-- 修改后需要重启PostgreSQL
-- pg_ctl restart -D /var/lib/postgresql/data

-- 创建复制用户
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

-- 在pg_hba.conf中添加复制访问规则
-- host    all    replicator    192.168.1.0/24    md5

-- 创建发布（Publication）
-- 发布所有表
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- 发布指定表
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items, customers;

-- 发布时带选项
CREATE PUBLICATION pub_orders FOR TABLE orders
  WITH (publish = 'insert, update, delete');  -- 只同步增删改

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

-- 添加表到已有发布
ALTER PUBLICATION pub_orders ADD TABLE products;

-- 从发布中移除表
ALTER PUBLICATION pub_orders DROP TABLE order_items;

订阅端配置：

-- 创建订阅（Subscription）
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=192.168.1.10 port=5432 dbname=prod user=replicator password=secure_password'
  PUBLICATION pub_orders;

-- 仅创建订阅结构，不立即同步数据（用于控制同步时机）
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=192.168.1.10 port=5432 dbname=prod user=replicator password=secure_password'
  PUBLICATION pub_orders
  WITH (copy_data = false);  -- 不复制初始数据，只同步增量

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

-- 关键状态字段：
-- received_lsn: 已接收的LSN位置
-- last_msg_send_time: 最后消息发送时间
-- last_msg_receipt_time: 最后消息接收时间
-- latest_end_lsn: 最新已应用LSN

-- 停止/启动订阅
ALTER SUBSCRIPTION sub_orders DISABLE;
ALTER SUBSCRIPTION sub_orders ENABLE;

-- 删除订阅
DROP SUBSCRIPTION sub_orders;
-- 注意：删除订阅会自动删除对应的复制槽

表结构与复制标识

逻辑复制要求订阅端的表结构必须与发布端兼容。表必须有主键或REPLICA IDENTITY，否则UPDATE和DELETE操作无法同步。

-- 订阅端创建匹配的表结构
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    order_no    VARCHAR(32) NOT NULL,
    user_id     BIGINT NOT NULL,
    total       DECIMAL(10,2) DEFAULT 0,
    status      SMALLINT DEFAULT 0,
    created_at  TIMESTAMP DEFAULT NOW(),
    updated_at  TIMESTAMP DEFAULT NOW()
);

-- 没有主键的表设置复制标识
-- 使用UNIQUE索引作为复制标识
ALTER TABLE order_items REPLICA IDENTITY USING INDEX idx_order_items_no;

-- 或使用FULL模式（将整行作为标识，效率较低）
ALTER TABLE order_items REPLICA IDENTITY FULL;

-- 查看表的复制标识设置
SELECT relname, relreplident FROM pg_class WHERE relname = 'orders';
-- relreplident取值：
-- 'd' = default（使用主键）
-- 'i' = index（使用UNIQUE索引）
-- 'f' = full（整行标识）
-- 'n' = nothing（不允许UPDATE/DELETE复制）

订阅端表可以有比发布端更多的列（新增列需要有默认值），但不能缺少发布端存在的列。列的顺序不必一致，但名称和数据类型必须匹配。

复制冲突诊断与解决

逻辑复制最常见的冲突是主键冲突（订阅端已存在相同主键的数据）和外键约束冲突。诊断方法：

-- 查看订阅worker状态和错误
SELECT subname, pid, relid, received_lsn, last_msg_send_time,
       latest_end_lsn, latest_end_time
FROM pg_stat_subscription
WHERE subname = 'sub_orders';

-- 查看复制槽状态
SELECT slot_name, plugin, slot_type, database,
       active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

-- 查看复制冲突日志
-- PostgreSQL日志文件中搜索关键词：
-- grep "CONFLICT" /var/log/postgresql/postgresql-*.log
-- grep "duplicate key" /var/log/postgresql/postgresql-*.log

-- 解决主键冲突：跳过冲突事务
-- 方法1：设置跳过冲突的LSN
-- 先找到冲突事务的LSN
SELECT * FROM pg_stat_subscription WHERE subname = 'sub_orders';
-- 假设冲突LSN为 0/1A0000A0

-- 跳过该LSN
ALTER SUBSCRIPTION sub_orders DISABLE;
-- 在pg_replication_slots中推进confirmed_flush_lsn
SELECT pg_replication_slot_advance('sub_orders', '0/1A0000B0');
ALTER SUBSCRIPTION sub_orders ENABLE;

-- 方法2：直接删除订阅端冲突数据
DELETE FROM orders WHERE id = 12345;  -- 删除冲突行
-- 订阅worker会自动重试并应用该事务

预防冲突的最佳实践：订阅端表不插入发布端可能复制过来的数据，或者使用不同的主键序列范围避免主键碰撞。双向同步场景需要特别处理，避免循环复制。

Patroni高可用集群部署

Patroni是PostgreSQL高可用管理工具，基于etcd/ZooKeeper/Consul做leader选举和自动故障转移。配合HAProxy或PgBouncer实现读写分离。

# patroni.yml 配置示例
name: pg-node-1

scope: pg-cluster

etcd:
  hosts: 192.168.1.20:2379,192.168.1.21:2379,192.168.1.22:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 20
    maximum_lag_on_failover: 1048576  -- 1MB
    synchronous: true  -- 同步复制模式
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 8
        max_wal_senders: 5
        max_connections: 100

  initdb:
    - encoding: UTF8
    - data-checksums
    - locale: C.UTF-8

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.10:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/15/bin
  pgpass: /var/lib/postgresql/.pgpass

  authentication:
    replication:
      username: replicator
      password: secure_password
    superuser:
      username: postgres
      password: admin_password

  parameters:
    wal_level: replica
    hot_standby: "on"
    max_connections: 200
    max_wal_senders: 10
    max_replication_slots: 10
    wal_keep_size: 1GB

  create_replica_methods:
    - basebackup

  basebackup:
    checkpoint: fast

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    replicate: true

Patroni的关键机制：leader节点运行PostgreSQL主库，replica节点运行只读从库。当leader宕机时，Patroni通过etcd选举新leader，原replica提升为主。maximum_lag_on_failover限制从库延迟超过阈值时不参与选举，防止数据丢失。

# 启动Patroni
patroni /etc/patroni/patroni.yml &

# 查看集群状态
patronictl -c /etc/patroni/patroni.yml list
# 输出示例：
# + Cluster: pg-cluster ----+---------+----+-----------+
# | Member    | Host         | Role    | State  | TL | Lag |
# | pg-node-1 | 192.168.1.10 | Leader  | running|  1 |   - |
# | pg-node-2 | 192.168.1.11 | Replica | running|  1 |  0s |
# | pg-node-3 | 192.168.1.12 | Replica | running|  1 |  0s |
# +-----------+--------------+---------+--------+----+-----+

# 手动switchover（计划内主从切换）
patronictl switchover /etc/patroni/patroni.yml

# 手动failover（强制切换，旧主可能丢数据）
patronictl failover /etc/patroni/patroni.yml

# 重新初始化故障节点
patronictl reinit /etc/patroni/patroni.yml pg-node-3

HAProxy读写分离配置

HAProxy通过Patroni的REST API判断节点角色，将写请求路由到主库，读请求路由到从库：

# haproxy.cfg
frontend pg_front
    bind *:5432
    mode tcp
    option tcplog

    # 写请求路由到主库
    acl is_write nbsrv(pg_write) ge 1
    use_backend pg_write if is_write

    # 默认读请求路由到从库
    default_backend pg_read

backend pg_write
    mode tcp
    option tcp-check
    # 通过Patroni API检查是否为leader
    tcp-check send GET\ /primary HTTP/1.0


    tcp-check expect string 200 OK
    server pg-1 192.168.1.10:5432 check port 8008
    server pg-2 192.168.1.11:5432 check port 8008
    server pg-3 192.168.1.12:5432 check port 8008

backend pg_read
    mode tcp
    balance roundrobin
    option tcp-check
    # 检查是否为replica且可读
    tcp-check send GET\ /replica HTTP/1.0


    tcp-check expect string 200 OK
    server pg-1 192.168.1.10:5432 check port 8008
    server pg-2 192.168.1.11:5432 check port 8008
    server pg-3 192.168.1.12:5432 check port 8008

Patroni在8008端口提供REST API，/primary返回200表示该节点是主库，/replica返回200表示是从库。HAProxy根据这个判断动态路由请求，故障转移时自动感知新主库。

监控与运维要点

PostgreSQL高可用集群的监控指标包括：复制延迟（replication lag）、连接数、WAL积压量、复制槽状态、Patroni集群健康状态。使用postgres_exporter采集指标，关键告警规则：

# 复制延迟告警
- alert: PGReplicationLagHigh
  expr: pg_replication_lag_seconds > 30
  for: 5m
  labels:
    severity: warning

# WAL积压告警
- alert: PGWALBacklog
  expr: pg_stat_replication_pg_wal_lsn_diff > 1073741824  # 1GB
  for: 10m
  labels:
    severity: warning

# Patroni集群无leader告警
- alert: PatroniNoLeader
  expr: patroni_cluster_has_leader == 0
  for: 2m
  labels:
    severity: critical

定期维护包括：检查复制槽是否有积压WAL（可能导致磁盘满）、VACUUM和ANALYZE保持统计信息准确、监控长期运行事务（阻塞VACUUM导致表膨胀）、定期验证备份可恢复性。逻辑复制环境还需监控冲突日志，及时发现并处理数据不一致问题。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/postgresql-luo-ji-fu-zhi-shi-zhan-gao-ke-yong-ji-qun-yu-shu/

赞 (0)
小编小编
0
上一篇 4小时前
下一篇 4小时前

相关推荐