PostgreSQL 物理复制

核心概念

物理复制是PostgreSQL的一种数据复制技术，通过复制数据库的物理文件（包括WAL日志）来实现主从服务器之间的数据同步。物理复制的主要特点包括：

二进制复制：直接复制数据库的物理文件，不需要解析SQL语句
块级复制：复制数据块，复制效率高
精确复制：从库与主库数据完全一致，包括系统表和索引
支持只读查询：从库可以处理只读查询，分担主库压力
自动故障恢复：支持自动故障切换（需要额外工具）

物理复制的关键组件：

WAL（Write-Ahead Log）：预写日志，记录所有数据库修改操作
WAL发送进程：主库上的wal_sender进程，负责发送WAL日志
WAL接收进程：从库上的wal_receiver进程，负责接收WAL日志
WAL应用进程：从库上的进程，负责将WAL日志应用到数据库

物理复制配置方法

1. 主库配置

1.1 修改配置文件

bash

# 修改postgresql.conf
cat >> /var/lib/postgresql/15/main/postgresql.conf << EOF
# 启用归档和流复制
wal_level = replica
archive_mode = on
archive_command = 'cp %p /pg_backups/wal/%f'

# 配置WAL发送进程
max_wal_senders = 10
wal_keep_size = 2GB
max_replication_slots = 10

# 配置检查点
checkpoint_timeout = 30min
max_wal_size = 4GB
EOF

# 修改pg_hba.conf，允许从库连接
cat >> /var/lib/postgresql/15/main/pg_hba.conf << EOF
# 允许从库的复制连接
host replication replication_user 0.0.0.0/0 md5
EOF

1.2 创建复制用户

sql

-- 登录主库
psql -h localhost -U postgres

-- 创建复制用户
CREATE USER replication_user REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 'replication_pass';

1.3 重启主库

bash

pg_ctl restart -D /var/lib/postgresql/15/main

2. 从库配置

2.1 准备从库环境

bash

# 停止从库PostgreSQL服务
pg_ctl stop -D /var/lib/postgresql/15/main

# 清理从库数据目录
rm -rf /var/lib/postgresql/15/main/*

2.2 从主库创建基础备份

bash

# 使用pg_basebackup创建基础备份
pg_basebackup -h 主库IP -U replication_user -D /var/lib/postgresql/15/main -F p -X stream -R

# 参数说明：
# -h：主库IP地址
# -U：复制用户
# -D：目标数据目录
# -F p：plain格式，直接写入数据目录
# -X stream：流式复制WAL日志
# -R：自动生成recovery.signal和primary_conninfo

2.3 配置从库

bash

# 修改从库postgresql.conf（可选，根据需要调整）
cat >> /var/lib/postgresql/15/main/postgresql.conf << EOF
# 从库配置
hot_standby = on
max_standby_streaming_delay = 30s
hot_standby_feedback = on
EOF

2.4 启动从库

bash

pg_ctl start -D /var/lib/postgresql/15/main

3. 验证复制状态

3.1 在主库上验证

sql

-- 查看WAL发送进程状态
SELECT * FROM pg_stat_replication;

-- 查看复制槽状态（如果使用了复制槽）
SELECT * FROM pg_replication_slots;

3.2 在从库上验证

sql

-- 检查是否处于恢复模式
SELECT pg_is_in_recovery();

-- 查看WAL接收进程状态
SELECT * FROM pg_stat_wal_receiver;

-- 查看复制延迟
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

物理复制高级配置

1. 使用复制槽

复制槽可以防止主库过早删除从库尚未接收的WAL日志：

sql

-- 在主库上创建复制槽
SELECT * FROM pg_create_physical_replication_slot('slot_name');

-- 修改从库的primary_conninfo，添加复制槽参数
# 在recovery.signal或postgresql.auto.conf中添加
primary_slot_name = 'slot_name'

2. 级联复制

级联复制允许从库从另一个从库复制数据，减少主库的复制压力：

主库 → 从库1 → 从库2
  ↓
应用连接

配置步骤：

bash

# 在从库1上启用WAL转发
# 修改从库1的postgresql.conf
cat >> /var/lib/postgresql/15/main/postgresql.conf << EOF
# 允许级联复制
max_wal_senders = 5
wal_keep_size = 1GB
EOF

# 在从库2上配置复制源为从库1
pg_basebackup -h 从库1_IP -U replication_user -D /var/lib/postgresql/15/main -F p -X stream -R

3. 同步复制

同步复制确保WAL日志至少写入一个从库后才返回成功：

sql

-- 在主库上配置同步复制
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 ("standby1", "standby2")';
SELECT pg_reload_conf();

-- 查看同步状态
SELECT * FROM pg_stat_replication WHERE sync_state = 'sync';

物理复制监控与管理

1. 复制状态监控

bash

# 1. 监控复制延迟
psql -h 从库IP -U postgres -c "SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;"

# 2. 监控WAL发送和接收
psql -h 主库IP -U postgres -c "SELECT * FROM pg_stat_replication;"
psql -h 从库IP -U postgres -c "SELECT * FROM pg_stat_wal_receiver;"

# 3. 监控复制槽
psql -h 主库IP -U postgres -c "SELECT * FROM pg_replication_slots;"

# 4. 使用pg_stat_replication视图监控详细状态
psql -h 主库IP -U postgres -c "SELECT application_name, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state FROM pg_stat_replication;"

2. 复制性能优化

bash

# 1. 优化主库配置
cat >> /var/lib/postgresql/15/main/postgresql.conf << EOF
# WAL优化
wal_compression = on
checkpoint_timeout = 30min
max_wal_size = 4GB
# 并行WAL发送
max_wal_senders = 10
EOF

# 2. 优化从库配置
cat >> /var/lib/postgresql/15/main/postgresql.conf << EOF
# 并行恢复
max_worker_processes = 8
max_parallel_workers_per_gather = 4
# 优化从库查询性能
hot_standby = on
hot_standby_feedback = on
EOF

# 3. 优化网络
# 使用专用网络连接
# 增加网络带宽
# 优化网络路由

3. 常见问题处理

3.1 复制延迟过高

bash

# 1. 检查网络状态
ping 主库IP
netstat -s

# 2. 检查主库负载
psql -h 主库IP -U postgres -c "SELECT * FROM pg_stat_activity;"
top

# 3. 检查从库负载
psql -h 从库IP -U postgres -c "SELECT * FROM pg_stat_activity;"
top

# 4. 优化配置
# 增加从库的max_worker_processes
# 增加主库的wal_compression

3.2 从库连接断开

bash

# 1. 检查从库日志
cat /var/log/postgresql/postgresql-15-main.log

# 2. 检查主库pg_hba.conf配置
cat /var/lib/postgresql/15/main/pg_hba.conf

# 3. 检查主库max_wal_senders设置
psql -h 主库IP -U postgres -c "SHOW max_wal_senders;"

# 4. 重启从库
pg_ctl restart -D /var/lib/postgresql/15/main

3.3 WAL日志丢失

bash

# 1. 检查主库wal_keep_size设置
psql -h 主库IP -U postgres -c "SHOW wal_keep_size;"

# 2. 检查是否使用了复制槽
psql -h 主库IP -U postgres -c "SELECT * FROM pg_replication_slots;"

# 3. 重新创建从库
# 停止从库
pg_ctl stop -D /var/lib/postgresql/15/main
# 清理数据目录
rm -rf /var/lib/postgresql/15/main/*
# 重新创建基础备份
pg_basebackup -h 主库IP -U replication_user -D /var/lib/postgresql/15/main -F p -X stream -R
# 启动从库
pg_ctl start -D /var/lib/postgresql/15/main

最佳实践

1. 架构设计最佳实践

使用多个从库：配置2-3个从库，提高系统可用性
配置复制槽：防止WAL日志丢失
实现级联复制：对于远距离从库，使用级联复制减少主库压力
考虑地理分布：将从库部署在不同地理位置，提高灾备能力
配置同步复制：对于关键业务，使用同步复制确保数据安全

2. 配置最佳实践

bash

# 主库推荐配置
cat >> /var/lib/postgresql/15/main/postgresql.conf << EOF
# WAL配置
wal_level = replica
archive_mode = on
archive_command = 'cp %p /pg_backups/wal/%f'
max_wal_senders = 10
wal_keep_size = 2GB
max_replication_slots = 10

# 检查点配置
checkpoint_timeout = 30min
max_wal_size = 4GB
checkpoint_completion_target = 0.9

# WAL压缩
wal_compression = on
EOF

# 从库推荐配置
cat >> /var/lib/postgresql/15/main/postgresql.conf << EOF
# 热备配置
hot_standby = on
max_standby_streaming_delay = 60s
hot_standby_feedback = on

# 并行恢复
max_worker_processes = 8
max_parallel_workers_per_gather = 4

# 监控配置
log_min_duration_statement = 1000
log_statement = 'ddl'
EOF

3. 管理最佳实践

定期检查复制状态：使用监控工具定期检查复制延迟和状态
备份从库：定期备份从库，防止主库和从库同时故障
测试故障切换：定期测试故障切换流程，确保能够快速恢复
文档化配置：详细记录复制配置和管理流程
培训团队：确保运维团队熟悉复制管理和故障处理

常见问题（FAQ）

Q1：物理复制和逻辑复制有什么区别？

A1：

特性	物理复制	逻辑复制
复制方式	二进制物理复制	SQL语句复制
复制粒度	块级	行级
从库类型	只读	可读写
版本兼容性	相同主从版本	支持跨小版本
复制效率	高	相对较低
从库查询	支持只读查询	支持只读查询
数据一致性	完全一致	最终一致
适用场景	高可用、灾备	数据迁移、多活

Q2：如何提高物理复制的性能？

A2：

优化网络：使用专用网络连接主从库，增加带宽
启用WAL压缩：减少网络传输的数据量
增加从库资源：提高从库的CPU和内存配置
优化主库配置：增加wal_keep_size，减少检查点频率
使用级联复制：对于多个从库，使用级联复制减少主库压力

Q3：如何实现物理复制的自动故障切换？

A3：

可以使用以下工具实现自动故障切换：

Patroni：基于ZooKeeper、etcd或Consul的自动故障切换工具
pgpool-II：支持连接池和自动故障切换
repmgr：PostgreSQL复制管理工具，支持自动故障切换
Stolon：基于etcd的PostgreSQL集群管理器

Q4：如何监控物理复制的延迟？

A4：

sql

-- 方法1：在从库上查询复制延迟
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

-- 方法2：在主库上查询WAL发送和接收差距
SELECT 
    application_name,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    (sent_lsn - replay_lsn) AS replay_lag
FROM pg_stat_replication;

-- 方法3：使用Prometheus + Grafana监控
-- 安装pg_exporter
-- 配置Prometheus抓取复制指标
-- 使用Grafana可视化复制延迟

Q5：如何处理物理复制中的冲突？

A5：

物理复制通常不会出现数据冲突，因为从库是只读的。如果出现冲突，可能是以下原因：

从库意外变为可写：检查从库是否被误操作
WAL日志损坏：检查WAL日志完整性，重新创建从库
硬件故障：检查存储和网络硬件，修复故障后重新创建从库

Q6：如何在不停机的情况下添加新的从库？

A6：

bash

# 1. 确保主库配置了合适的wal_level和max_wal_senders
# 2. 创建复制用户（如果尚未创建）
# 3. 使用pg_basebackup从主库创建基础备份
pg_basebackup -h 主库IP -U replication_user -D /var/lib/postgresql/15/main -F p -X stream -R
# 4. 配置从库
# 5. 启动从库
pg_ctl start -D /var/lib/postgresql/15/main
# 6. 验证复制状态

Q7：如何升级物理复制集群？

A7：

物理复制集群的升级通常采用以下步骤：

升级从库：先升级所有从库
切换主从角色：将其中一个从库提升为主库
升级原主库：将原主库作为从库加入集群
可选：切换回原主库：如果需要，将原主库重新提升为主库

Q8：如何配置从库的只读模式？

A8：

从库默认处于只读模式，无需额外配置。可以通过以下命令验证：

sql

-- 在从库上执行
CREATE TABLE test (id int);
-- 应该返回错误：ERROR:  cannot execute CREATE TABLE in a read-only transaction

如果需要允许从库写入，可以修改配置：

bash

# 修改postgresql.conf
cat >> /var/lib/postgresql/15/main/postgresql.conf << EOF
# 允许从库写入（不推荐用于生产环境）
# hot_standby = off
EOF

物理复制的应用场景

高可用性：构建主从架构，实现自动故障切换
读写分离：从库处理只读查询，分担主库压力
灾备系统：将从库部署在不同地理位置，实现灾备
数据迁移：使用物理复制进行数据库迁移
备份系统：从从库创建备份，减少对主库的影响

物理复制是PostgreSQL构建高可用性和灾备系统的基础，掌握物理复制的配置和管理对于PostgreSQL数据库管理员来说是必不可少的技能。

PostgreSQL 物理复制 ​

核心概念 ​

物理复制配置方法 ​

1. 主库配置 ​

1.1 修改配置文件 ​

1.2 创建复制用户 ​

1.3 重启主库 ​

2. 从库配置 ​

2.1 准备从库环境 ​

2.2 从主库创建基础备份 ​

2.3 配置从库 ​

2.4 启动从库 ​

3. 验证复制状态 ​

3.1 在主库上验证 ​

3.2 在从库上验证 ​

物理复制高级配置 ​

1. 使用复制槽 ​

2. 级联复制 ​

配置步骤： ​

3. 同步复制 ​

物理复制监控与管理 ​

1. 复制状态监控 ​

2. 复制性能优化 ​

3. 常见问题处理 ​

3.1 复制延迟过高 ​

3.2 从库连接断开 ​

3.3 WAL日志丢失 ​

最佳实践 ​

1. 架构设计最佳实践 ​

2. 配置最佳实践 ​

3. 管理最佳实践 ​

常见问题（FAQ） ​

Q1：物理复制和逻辑复制有什么区别？ ​

Q2：如何提高物理复制的性能？ ​

Q3：如何实现物理复制的自动故障切换？ ​

Q4：如何监控物理复制的延迟？ ​

Q5：如何处理物理复制中的冲突？ ​

Q6：如何在不停机的情况下添加新的从库？ ​

Q7：如何升级物理复制集群？ ​

Q8：如何配置从库的只读模式？ ​

物理复制的应用场景 ​

PostgreSQL 物理复制

核心概念

物理复制配置方法

1. 主库配置

1.1 修改配置文件

1.2 创建复制用户

1.3 重启主库

2. 从库配置

2.1 准备从库环境

2.2 从主库创建基础备份

2.3 配置从库

2.4 启动从库

3. 验证复制状态

3.1 在主库上验证

3.2 在从库上验证

物理复制高级配置

1. 使用复制槽

2. 级联复制

配置步骤：

3. 同步复制

物理复制监控与管理

1. 复制状态监控

2. 复制性能优化

3. 常见问题处理

3.1 复制延迟过高

3.2 从库连接断开

3.3 WAL日志丢失

最佳实践

1. 架构设计最佳实践

2. 配置最佳实践

3. 管理最佳实践

常见问题（FAQ）

Q1：物理复制和逻辑复制有什么区别？

Q2：如何提高物理复制的性能？

Q3：如何实现物理复制的自动故障切换？

Q4：如何监控物理复制的延迟？

Q5：如何处理物理复制中的冲突？

Q6：如何在不停机的情况下添加新的从库？

Q7：如何升级物理复制集群？

Q8：如何配置从库的只读模式？

物理复制的应用场景