PostgreSQL 主从流复制搭建【一、架构选择】┌─────────────┬─────────────────────┬─────────────────────────────┐ │ 模式 │ 特点 │ 适用场景 │ ├─────────────┼─────────────────────┼─────────────────────────────┤ │ 异步流复制 │ 主库不等备库确认 │ 大多数场景性能优先 │ │ 同步流复制 │ 主库等备库确认才返回 │ 数据零丢失性能有损耗 │ │ 级联复制 │ 备库再向下游复制 │ 多机房、减轻主库压力 │ │ 逻辑复制 │ 表级复制跨版本 │ 部分表复制、升级迁移 │ └─────────────┴─────────────────────┴─────────────────────────────┘ 【二、主库配置primary】-- 2.1 创建复制用户CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD repl_pass;GRANT CONNECT ON DATABASE postgres TO repl;-- 2.2 修改 postgresql.confcat /pg/data/postgresql.conf EOF# 流复制核心参数listen_addresses * # 监听所有地址wal_level replica # wal级别minimal/replica/logicalmax_wal_senders 10 # 最大复制连接数wal_keep_size 1GB # 保留WAL量PG13替代wal_keep_segmentsmax_replication_slots 10 # 最大复制槽数hot_standby on # 备库允许只读查询# 可选同步复制# synchronous_commit remote_apply # 同步级别off/local/remote_write/remote_apply/on# synchronous_standby_names slave1 # 指定同步备库名称EOF##主库创建复制槽-- 1. 创建一个名为 test_physical_slot 的物理复制槽SELECT pg_create_physical_replication_slot(test_physical_slot);-- 2. 验证复制槽是否创建成功 (此时 active 字段应该为 f)SELECT slot_name, slot_type, active FROM pg_replication_slots;-- 2.3 修改 pg_hba.conf允许备库连接cat /pg/data/pg_hba.conf EOF# 流复制授权host replication repl 192.168.1.0/24 scram-sha-256host all repl 192.168.1.0/24 scram-sha-256EOF-- 2.4 重启主库生效pg_ctl restart -D /pg/data【三、备库搭建standby】-- 3.1 方式一pg_basebackup推荐在线热备--备库节点执行pg_basebackup -h 10.10.170.252 -p 5432 -U repl -D /pg/data_slave -Fp -Xs -P -v -R参数说明-h 主库IP -p 端口 -U 复制用户-D 备库目录 -Fp 平面格式 -Xs 流式WAL-P 显示进度 -v 详细输出 -R 自动生成 standby.signal-- 3.2 方式二手动复制停机维护时使用--主库执行# 主库停写后备份pg_ctl stop -D /pg/datarsync -avz --exclude postmaster.pid /pg/data/ root备库ip:/pg/data_slave/pg_ctl start -D /pg/data# 备库创建 standby.signaltouch /pg/data_slave/standby.signal【四、备库配置文件】-- 4.1 自动生成pg_basebackup -R 已生成cat /pg/data_slave/postgresql.auto.conf# 内容示例primary_conninfo host192.168.1.10 port5432 userrepl passwordrepl_passprimary_slot_name test_physical_slot # 如果使用复制槽-- 4.2 备库 postgresql.conf 调优cat /pg/data_slave/postgresql.conf EOFhot_standby on # 允许只读查询hot_standby_feedback on # 向主库反馈查询进度减少冲突max_standby_archive_delay 30s # 备库应用WAL延迟max_standby_streaming_delay 30sEOF-- 4.3 启动备库# 第一步设置正确权限必须 0700最安全chmod 0700 /pg/data_slave# 第二步确认属主是 postgres必须chown postgres:postgres /pg/data_slave# 第三步启动备库pg_ctl start -D /pg/data_slave【五、验证复制状态】-- 5.1 主库查看复制连接SELECT * FROM pg_stat_replication;-- 关键字段-- pid, usename, application_name, client_addr, state,-- sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state-- 5.2 主库查看复制槽SELECT * FROM pg_replication_slots;-- 5.3 备库查看接收状态SELECT * FROM pg_stat_wal_receiver;-- 关键字段-- pid, status, receive_start_lsn, received_lsn,-- latest_end_lsn, last_msg_send_time, conninfo-- 5.4 查看延迟主库执行SELECTclient_addr,application_name,pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag,sync_stateFROM pg_stat_replication;-- 5.5 查看主备 LSN 对比-- 主库SELECT pg_current_wal_lsn();-- 备库SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp();【六、常用管理操作】-- 6.1 主库创建/删除复制槽SELECT pg_create_physical_replication_slot(slot_slave1, true);SELECT pg_drop_replication_slot(slot_slave1);-- 6.2 备库提升为主库故障切换pg_ctl promote -D /pg/data_slave-- 或 SQL 方式SELECT pg_promote();-- 6.3 主库切换 WAL 文件SELECT pg_switch_wal();-- 6.4 查看 WAL 文件列表SELECT * FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 10;-- 6.5 查看归档状态SELECT * FROM pg_stat_archiver;【七、同步复制配置可选】-- 7.1 主库 postgresql.confsynchronous_commit remote_applysynchronous_standby_names FIRST 1 (slave1, slave2) -- 1个同步其他异步# 或synchronous_standby_names ANY 1 (slave1, slave2) -- 任意1个确认即可-- 7.2 备库 application_name 设置# 在备库 primary_conninfo 中指定primary_conninfo host... application_nameslave1-- 7.3 查看同步状态SELECT application_name, sync_state, sync_priorityFROM pg_stat_replication;【八、级联复制备库再向下游复制】-- 8.1 二级备库配置# 二级备库指向一级备库pg_basebackup -h 192.168.1.11 -p 5432 -U repl -D /pg/data_slave2 \-Fp -Xs -P -v -R-- 8.2 一级备库开启级联cat /pg/data_slave/postgresql.conf EOFmax_wal_senders 10hot_standby onEOF【九、常见问题】┌─────────────────────────────┬─────────────────────────────────────────────┐│ 问题 │ 解决 │├─────────────────────────────┼─────────────────────────────────────────────┤│ 备库启动后一直是 startup │ 检查 primary_conninfo 网络连通性 ││ 复制延迟越来越大 │ 检查网络带宽、备库IO性能、hot_standby_feedback││ 主库WAL积压 │ 增大 wal_keep_size 或配置归档 ││ 备库查询被中断 │ 调整 max_standby_streaming_delay ││ 密码认证失败 │ 检查 pg_hba.conf 和 scram-sha-256 配置 ││ 复制槽导致主库WAL不清理 │ 及时删除不用的复制槽 ││ 切换后老主库变备库 │ 使用 repmgr/pg_auto_failover 等工具 │└─────────────────────────────┴─────────────────────────────────────────────┘【十、一键巡检脚本】-- 主库巡检SELECTclient_addr,application_name,state,sync_state,pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag,reply_timeFROM pg_stat_replication;-- 备库巡检SELECTstatus,pg_last_wal_receive_lsn() AS receive_lsn,pg_last_wal_replay_lsn() AS replay_lsn,pg_last_xact_replay_timestamp() AS replay_time,now() - pg_last_xact_replay_timestamp() AS delay;