PostgreSQL逻辑复制与物理流复制实战：读写分离架构配置详解

流复制基础：物理复制的WAL传输机制

PostgreSQL复制分为物理流复制和逻辑复制两类。物理流复制传输WAL（Write-Ahead Logging）记录，备库重放WAL实现字节级一致，适用于读写分离和高可用集群场景。数据库运维中，正确配置复制架构是保证数据一致性和查询吞吐量的关键。

流复制的核心配置在postgresql.conf和pg_hba.conf中：

# 主库 postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1024   # 保留WAL大小(MB)
hot_standby = on

# pg_hba.conf 添加复制权限
host replication replicator 192.168.1.0/24 md5

主库创建复制用户：

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_pass';

备库使用pg_basebackup初始化数据目录：

pg_basebackup -h 192.168.1.10 -U replicator -D /var/lib/postgresql/data \
    -Fp -Xs -P -R

# -R 参数自动生成standby.signal和primary_conninfo配置

pg_basebackup完成后，备库data目录下自动生成standby.signal文件和postgresql.auto.conf配置：

# postgresql.auto.conf（备库自动生成）
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=secure_pass'
primary_slot_name = 'standby_slot'

主库创建复制槽（Replication Slot）防止备库断连时WAL被清理：

SELECT pg_create_physical_replication_slot('standby_slot');

读写分离配置与应用层路由

流复制搭建完成后，应用层需要将读请求路由到备库、写请求发送到主库。Pgpool-II和HAProxy是常用的中间件方案，也可在应用层通过连接池配置实现。

使用Pgpool-II配置读写分离：

# pgpool.conf
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# 读写分离
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

# 健康检查
health_check_period = 10
health_check_user = 'pgpool'

Pgpool-II自动识别SELECT查询转发到备库，非SELECT语句发送到主库。事务内的SELECT语句默认发送到主库以保证一致性，可通过white_function_list配置允许在备库执行的函数。

逻辑复制：发布订阅模型与选择性同步

与物理复制不同，逻辑复制基于逻辑解码（Logical Decoding）解析WAL日志，将数据变更以逻辑格式传输。逻辑复制支持选择性复制表、跨版本复制和异构数据库同步。

发布端配置：

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_logical_replication_workers = 4

# 创建发布
CREATE PUBLICATION my_pub FOR TABLE 
    users, orders, order_items
    WITH (publish = 'insert, update, delete');

# 对单表发布
CREATE PUBLICATION user_pub FOR TABLE users;

订阅端配置：

# 创建订阅
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=192.168.1.10 port=5432 user=replicator password=secure_pass dbname=source_db'
    PUBLICATION my_pub
    WITH (
        copy_data = true,          -- 初始数据同步
        create_slot = true,        -- 自动创建复制槽
        enabled = true,
        slot_name = 'logical_sub_slot'
    );

逻辑复制仅同步INSERT、UPDATE、DELETE操作，不支持DDL变更复制。表结构需要在订阅端预先创建，且字段类型和名称需与发布端一致。

复制冲突处理与监控

备库上的长查询可能因WAL重放产生复制冲突。主库的WAL重放操作（如行锁、DDL）会中断备库正在执行的查询。通过postgresql.conf中的max_standby_streaming_delay参数控制备库延迟WAL重放的时间窗口：

# 备库postgresql.conf
max_standby_streaming_delay = 30s
max_standby_archive_delay = 30s

# 监控复制状态
SELECT 
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- 备库查看复制延迟
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

复制延迟监控是数据库高可用架构运维的核心指标。当延迟超过阈值时触发告警，运维人员需排查主库写入压力、网络带宽和备库重放性能。流复制配合自动故障切换工具如Patroni或repmgr，可实现主库故障时的自动切换，保障服务连续性。逻辑复制在异构数据库迁移和数据分发场景中提供更灵活的选择，是PostgreSQL数据同步方案的重要补充。数据库高可用架构的设计需要综合考虑复制延迟、故障切换速度和数据一致性保证，PostgreSQL的流复制与逻辑复制为不同场景提供了成熟的技术方案。