跳到主要内容

PostgreSQL 复制与高可用

复制和高可用是生产环境数据库系统的核心需求。PostgreSQL 提供了强大的内置复制功能，包括物理复制（流复制）和逻辑复制，可以满足从读写分离到异地灾备的各种场景需求。本章将详细介绍 PostgreSQL 的复制机制和高可用方案。

复制概述​

什么是数据库复制？​

数据库复制是指将一个数据库服务器（主库/Primary）的数据同步到一个或多个其他服务器（从库/Standby）的过程。复制的核心目的是：

  1. 数据冗余：防止数据丢失
  2. 读写分离：分散读负载，提高系统吞吐量
  3. 高可用性：主库故障时快速切换到从库
  4. 数据分发：将数据分发到不同的业务系统

PostgreSQL 复制类型对比​

特性流复制（物理复制）逻辑复制
复制单位整个集群（所有数据库）选择性复制（表级别）
复制方式复制 WAL 日志复制数据变更
跨版本支持不支持（必须版本一致）支持
跨平台支持不支持支持
延迟低相对较高
适用场景高可用、读写分离数据同步、版本升级

流复制（Streaming Replication）​

流复制是 PostgreSQL 最常用的复制方式，它通过网络实时传输 WAL（Write-Ahead Log）日志，实现主从数据同步。

流复制的工作原理​

┌─────────────────────────────────────────────────────────────┐
│                         主库 (Primary)                       │
│  ┌─────────┐    ┌─────────┐    ┌─────────────────────┐     │
│  │ 事务提交 │ -> │ WAL 日志 │ -> │ WAL Sender 进程     │     │
│  └─────────┘    └─────────┘    └──────────┬──────────┘     │
└───────────────────────────────────────────┼─────────────────┘
                                            │ 网络传输 WAL
                                            ▼
┌─────────────────────────────────────────────────────────────┐
│                         从库 (Standby)                       │
│  ┌─────────────────┐    ┌─────────┐    ┌─────────────┐     │
│  │ WAL Receiver    │ -> │ WAL 日志 │ -> │ 重放 WAL    │     │
│  └─────────────────┘    └─────────┘    └─────────────┘     │
└─────────────────────────────────────────────────────────────┘

流复制的核心机制：

  1. 主库写入数据时产生 WAL 日志
  2. WAL Sender 进程将 WAL 日志发送给从库
  3. 从库 WAL Receiver 进程接收并写入本地 WAL
  4. 从库重放 WAL 日志，实现数据同步

主库配置​

在主库上编辑 postgresql.conf：

# 启用复制所需的 WAL 级别
wal_level = replica

# 最大 WAL 发送进程数（根据从库数量设置）
max_wal_senders = 10

# 保留的 WAL 文件大小（防止从库落后太多导致 WAL 被删除）
wal_keep_size = 1GB

# 最大复制槽数量
max_replication_slots = 10

# 监听所有地址
listen_addresses = '*'

# 启用归档（可选，用于 PITR）
archive_mode = on
archive_command = 'cp %p /archive/%f'

配置 pg_hba.conf 允许复制连接：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      192.168.1.0/24          md5

创建复制用户：

-- 创建专用的复制用户
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'your_password';

从库配置​

方式一：使用 pg_basebackup 创建从库

# 在从库服务器上执行基础备份
pg_basebackup -h primary_host -p 5432 -U replicator \
    -D /var/lib/postgresql/18/main \
    -Fp -Xs -P -R

# 参数说明：
# -h：主库地址
# -U：复制用户
# -D：数据目录
# -Fp：普通文件格式
# -Xs：流式传输 WAL
# -P：显示进度
# -R：自动创建 standby.signal 和配置 primary_conninfo

方式二：手动配置

# 1. 停止从库服务
systemctl stop postgresql

# 2. 清空数据目录
rm -rf /var/lib/postgresql/18/main/*

# 3. 从主库复制数据
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/18/main -P -R

# 4. 创建 standby.signal 文件
touch /var/lib/postgresql/18/main/standby.signal

# 5. 配置连接信息（如果 -R 没有自动配置）

编辑 postgresql.auto.conf：

# 连接主库的信息
primary_conninfo = 'host=primary_host port=5432 user=replicator password=your_password'

# 可选：使用复制槽
primary_slot_name = 'standby1_slot'

启动和验证​

# 启动从库
systemctl start postgresql

在主库验证复制状态：

-- 查看复制连接
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_state
FROM pg_stat_replication;

-- 输出示例：
-- pid | usename    | application_name | client_addr  | state   | sent_lsn  | replay_lsn | sync_state
-- ----+------------+------------------+--------------+---------+-----------+------------+------------
-- 123 | replicator | walreceiver      | 192.168.1.11 | streaming | 0/5000148 | 0/5000148 | async

在从库验证状态：

-- 查看从库状态
SELECT pg_is_in_recovery();  -- 应该返回 true

-- 查看接收和重放位置
SELECT 
    pg_last_wal_receive_lsn() AS receive_lsn,
    pg_last_wal_replay_lsn() AS replay_lsn,
    pg_last_xact_replay_timestamp() AS replay_time;

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

复制槽（Replication Slots）​

复制槽确保主库保留足够的 WAL 日志，防止从库落后太多导致无法继续复制：

-- 创建复制槽
SELECT pg_create_physical_replication_slot('standby1_slot');

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

-- 删除复制槽
SELECT pg_drop_replication_slot('standby1_slot');

注意事项：

  • 复制槽会无限保留 WAL，如果从库长时间断开，可能导致主库磁盘被 WAL 填满
  • 建议设置 max_slot_wal_keep_size 限制 WAL 保留量
# 限制复制槽最多保留的 WAL 大小
max_slot_wal_keep_size = 10GB

同步复制​

异步复制（默认）存在数据丢失风险：主库崩溃时，已提交但未同步到从库的事务会丢失。同步复制确保事务提交前数据已写入至少一个从库。

主库配置：

# synchronous_standby_names 指定同步从库
# 格式：
# FIRST num_sync (standby_name, ...)：优先级模式，选择前 num_sync 个
# ANY num_sync (standby_name, ...)：仲裁模式，任意 num_sync 个确认即可
# standby_name：从库的 application_name

synchronous_standby_names = 'FIRST 1 (standby1, standby2)'
# 表示：优先使用 standby1，如果不可用则使用 standby2

从库配置：

# 在 primary_conninfo 中设置 application_name
primary_conninfo = 'host=primary_host port=5432 user=replicator application_name=standby1'

同步复制的权衡：

  • 优点：数据零丢失
  • 缺点：写入延迟增加（需要等待从库确认）
-- 查看同步状态
SELECT 
    application_name,
    sync_state,
    state
FROM pg_stat_replication;

-- sync_state 说明：
-- async：异步复制
-- sync：同步复制
-- potential：潜在的同步从库
-- quorum：仲裁复制

级联复制​

级联复制允许从库作为其他从库的主库，减轻主库负担：

主库 (Primary)
  │
  ├── 从库1 (Standby) ──┬── 从库1-1 (Cascade Standby)
  │                      └── 从库1-2 (Cascade Standby)
  │
  └── 从库2 (Standby)

配置级联从库：

# 在级联从库上配置
primary_conninfo = 'host=standby1 port=5432 user=replicator'

# 在中间从库（作为级联主库）上配置
hot_standby = on           # 允许只读查询
wal_level = replica        # 允许作为发送端
max_wal_senders = 5        # 允许发送 WAL

逻辑复制（Logical Replication）​

逻辑复制基于数据变更的逻辑表示，而不是物理 WAL 日志。它提供了更灵活的复制控制，可以选择性地复制特定的表或数据。

逻辑复制的工作原理​

┌─────────────────────────────────────────────────────────────┐
│                      发布端 (Publisher)                      │
│  ┌─────────┐    ┌─────────────────┐    ┌────────────────┐  │
│  │ 数据变更 │ -> │ Logical Decoding │ -> │ Publication    │  │
│  └─────────┘    └─────────────────┘    └────────────────┘  │
└─────────────────────────────────────────────────────────────┘
                                            │
                                            ▼
┌─────────────────────────────────────────────────────────────┐
│                      订阅端 (Subscriber)                     │
│  ┌────────────────┐    ┌─────────────────┐    ┌─────────┐  │
│  │ Subscription   │ -> │ Apply Changes   │ -> │ 目标表   │  │
│  └────────────────┘    └─────────────────┘    └─────────┘  │
└─────────────────────────────────────────────────────────────┘

配置逻辑复制​

发布端（主库）配置：

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
-- 创建发布（Publication）
-- 发布所有表
CREATE PUBLICATION all_tables FOR ALL TABLES;

-- 发布指定表
CREATE PUBLICATION user_data FOR TABLE users, orders;

-- 发布指定表的指定操作
CREATE PUBLICATION user_inserts FOR TABLE users WITH (publish = 'insert');

-- 添加表到现有发布
ALTER PUBLICATION user_data ADD TABLE products;

-- 移除表
ALTER PUBLICATION user_data DROP TABLE products;

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

订阅端（从库）配置：

-- 创建订阅（Subscription）
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=primary_host port=5432 dbname=mydb user=replicator password=password'
    PUBLICATION user_data;

-- 创建订阅时指定参数
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=primary_host port=5432 dbname=mydb user=replicator'
    PUBLICATION user_data
    WITH (
        copy_data = true,        -- 是否复制现有数据
        create_slot = true,      -- 是否创建复制槽
        synchronous_commit = on  -- 同步提交设置
    );

-- 启用/禁用订阅
ALTER SUBSCRIPTION my_subscription ENABLE;
ALTER SUBSCRIPTION my_subscription DISABLE;

-- 刷新发布的表
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;

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

逻辑复制冲突处理​

当订阅端的数据与发布端冲突时，需要处理冲突：

-- 查看复制冲突
SELECT * FROM pg_stat_subscription_conflicts;

-- 常见冲突类型：
-- insert_conflict：插入时主键冲突
-- update_missing：更新时行不存在
-- update_conflict：更新时数据版本冲突
-- delete_missing：删除时行不存在

-- 处理冲突的方式：
-- 1. 手动解决冲突
DELETE FROM target_table WHERE id = conflicting_id;

-- 2. 跳过当前事务
ALTER SUBSCRIPTION my_subscription SKIP (lsn = '0/12345678');

-- 3. 禁用订阅，修复数据后重新启用
ALTER SUBSCRIPTION my_subscription DISABLE;
-- 修复数据...
ALTER SUBSCRIPTION my_subscription ENABLE;

逻辑复制的限制​

-- 1. 表必须有主键或唯一索引（REPLICA IDENTITY）
ALTER TABLE my_table REPLICA IDENTITY DEFAULT;  -- 使用主键
ALTER TABLE my_table REPLICA IDENTITY USING INDEX idx_unique;  -- 使用唯一索引
ALTER TABLE my_table REPLICA IDENTITY FULL;  -- 使用所有列（性能较差）

-- 2. 不复制的内容
-- - DDL 操作（CREATE/ALTER/DROP）
-- - 序列值（需要手动同步）
-- - 大对象（Large Objects）
-- - 视图、物化视图

-- 3. TRUNCATE 需要特殊处理
-- 默认不复制 TRUNCATE，需要设置：
ALTER PUBLICATION my_pub SET (publish = 'insert, update, delete, truncate');

故障转移（Failover）​

故障转移是指主库故障时，将从库提升为新主库的过程。

手动故障转移​

# 1. 确认主库已停止（如果还能连接，先停止）
# 在主库上
pg_ctl stop -m fast

# 2. 在从库上执行提升
pg_ctl promote

# 或者使用 SQL 命令
psql -c "SELECT pg_promote();"

# 3. 验证新主库状态
psql -c "SELECT pg_is_in_recovery();"  -- 应该返回 false

应用连接切换​

故障转移后，应用需要连接到新的主库。常见方案：

方案一：使用虚拟 IP（VIP）

# 主库故障时，将 VIP 迁移到新主库
# 旧主库
ip addr del 192.168.1.100/24 dev eth0

# 新主库
ip addr add 192.168.1.100/24 dev eth0

方案二：使用 DNS 切换

# 更新 DNS 记录指向新主库
nsupdate << EOF
update delete db-master.example.com A
update add db-master.example.com 60 A 192.168.1.101
send
EOF

方案三：应用层配置多节点

# Python 示例
import psycopg2
from psycopg2 import OperationalError

hosts = ['primary:5432', 'standby1:5432', 'standby2:5432']

def get_connection():
    for host in hosts:
        try:
            conn = psycopg2.connect(
                host=host.split(':')[0],
                port=host.split(':')[1],
                database='mydb',
                user='app',
                password='password'
            )
            # 检查是否为主库
            cur = conn.cursor()
            cur.execute("SELECT pg_is_in_recovery()")
            if not cur.fetchone()[0]:
                return conn
        except OperationalError:
            continue
    raise Exception("No primary server available")

时间线（Timeline）​

PostgreSQL 使用时间线来区分不同的恢复历史：

-- 查看当前时间线
SELECT pg_control_checkpoint();

-- 时间线切换场景：
# 时间线 1: 初始主库
# 主库故障 -> 从库提升 -> 时间线 2
# 新主库故障 -> 另一个从库提升 -> 时间线 3

-- 从库需要跟踪主库的时间线变化
-- 配置 recovery_target_timeline = 'latest'

高可用方案​

Patroni​

Patroni 是最流行的 PostgreSQL 高可用解决方案，基于分布式配置存储（etcd、Consul 或 ZooKeeper）实现自动故障转移。

架构：

┌─────────────────────────────────────────────────────┐
│                   etcd 集群                          │
│     (存储集群状态和配置)                              │
└─────────────────────────────────────────────────────┘
          ▲                    ▲                    ▲
          │                    │                    │
    ┌─────┴─────┐        ┌─────┴─────┐        ┌─────┴─────┐
    │  Patroni  │        │  Patroni  │        │  Patroni  │
    │  + PG     │        │  + PG     │        │  + PG     │
    │  (Leader) │        │ (Replica) │        │ (Replica) │
    └───────────┘        └───────────┘        └───────────┘

安装和配置：

# 安装 Patroni
pip install patroni[etcd]

# 创建配置文件 /etc/patroni.yml
scope: postgres-cluster
namespace: /db/
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.10:8008

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: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        max_connections: 200
        shared_buffers: 1GB
        wal_level: replica
        max_wal_senders: 10
        max_replication_slots: 10
        hot_standby: on

  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.10:5432
  data_dir: /var/lib/postgresql/18/main
  authentication:
    replication:
      username: replicator
      password: password
    superuser:
      username: postgres
      password: password

# 启动 Patroni
patroni /etc/patroni.yml

常用操作：

# 查看集群状态
patronictl -c /etc/patroni.yml list

# 手动切换主库
patronictl -c /etc/patroni.yml switchover

# 重新初始化节点
patronictl -c /etc/patroni.yml reinit postgres-cluster node2

# 查看集群配置
patronictl -c /etc/patroni.yml show-config

pg_auto_failover​

pg_auto_failover 是一个简单易用的高可用扩展：

# 安装
apt-get install pg-auto-failover

# 创建监控节点
pg_autoctl create monitor --pgdata /var/lib/postgresql/monitor \
    --hostname 192.168.1.10 --auth trust --ssl-self-signed

# 创建主节点
pg_autoctl create postgres --pgdata /var/lib/postgresql/data \
    --monitor postgres://[email protected]:5432/pg_auto_failover \
    --hostname 192.168.1.11 --auth trust --ssl-self-signed

# 创建从节点
pg_autoctl create postgres --pgdata /var/lib/postgresql/data \
    --monitor postgres://[email protected]:5432/pg_auto_failover \
    --hostname 192.168.1.12 --auth trust --ssl-self-signed

# 查看状态
pg_autoctl show state

Repmgr​

repmgr 是另一个流行的高可用工具：

# 安装
apt-get install repmgr

# 主库配置 /etc/repmgr.conf
node_id=1
node_name=primary
conninfo='host=192.168.1.10 user=repmgr dbname=repmgr'
data_directory='/var/lib/postgresql/18/main'

# 注册主库
repmgr primary register

# 克隆创建从库
repmgr standby clone -h 192.168.1.10 -U repmgr -d repmgr

# 注册从库
repmgr standby register

# 查看集群状态
repmgr cluster show

# 手动切换
repmgr standby switchover

读写分离​

读写分离是将读操作分散到从库，写操作发送到主库，提高系统整体吞吐量。

应用层实现​

# Python 示例
class DatabaseRouter:
    def __init__(self):
        self.primary = psycopg2.connect(
            host='primary-host', database='mydb', user='app'
        )
        self.replicas = [
            psycopg2.connect(host=f'standby{i}-host', database='mydb', user='app')
            for i in range(1, 4)
        ]
        self.replica_index = 0
    
    def get_write_connection(self):
        return self.primary
    
    def get_read_connection(self):
        # 简单轮询
        conn = self.replicas[self.replica_index]
        self.replica_index = (self.replica_index + 1) % len(self.replicas)
        return conn

router = DatabaseRouter()

# 写操作使用主库
write_conn = router.get_write_connection()
write_conn.cursor().execute("INSERT INTO users ...")

# 读操作使用从库
read_conn = router.get_read_connection()
read_conn.cursor().execute("SELECT * FROM users ...")

使用 PgBouncer 实现读写分离​

PgBouncer 本身不支持读写分离，但可以通过配置多个数据库连接池实现：

# pgbouncer.ini
[databases]
; 写连接池 -> 主库
primary = host=primary-host port=5432 dbname=mydb

; 读连接池 -> 从库（多个从库可以用 Pgpool-II）
replica = host=replica-host port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = session

应用连接不同的池：

  • 写操作连接 primary 池
  • 读操作连接 replica 池

监控复制​

复制状态监控​

-- 主库：查看复制状态
SELECT 
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- 从库：查看接收和应用状态
SELECT 
    pg_last_wal_receive_lsn() AS received_lsn,
    pg_last_wal_replay_lsn() AS replayed_lsn,
    pg_is_in_recovery() AS is_standby,
    pg_last_xact_replay_timestamp() AS last_replay_time;

-- 计算复制延迟
SELECT 
    now() - pg_last_xact_replay_timestamp() AS time_delay,
    CASE 
        WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() 
        THEN 0 
        ELSE EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) 
    END AS seconds_delay;

复制延迟告警​

-- 创建监控视图
CREATE VIEW replication_monitor AS
SELECT 
    client_addr,
    application_name,
    state,
    sync_state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
    CASE 
        WHEN pg_wal_lsn_diff(sent_lsn, replay_lsn) > 1024 * 1024 * 100 
        THEN 'CRITICAL'  -- 超过 100MB
        WHEN pg_wal_lsn_diff(sent_lsn, replay_lsn) > 1024 * 1024 * 10 
        THEN 'WARNING'   -- 超过 10MB
        ELSE 'OK'
    END AS status
FROM pg_stat_replication;

-- 查询延迟严重的从库
SELECT * FROM replication_monitor WHERE status != 'OK';

复制槽监控​

-- 查看复制槽状态
SELECT 
    slot_name,
    slot_type,
    active,
    restart_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS wal_retained_bytes
FROM pg_replication_slots;

-- 找出不活跃的复制槽（可能导致 WAL 堆积）
SELECT * FROM pg_replication_slots WHERE NOT active;

-- 清理不需要的复制槽
SELECT pg_drop_replication_slot('old_slot_name');

小结​

本章我们学习了 PostgreSQL 复制与高可用：

  1. 复制类型：流复制（物理）和逻辑复制的对比
  2. 流复制配置：主库和从库的配置方法
  3. 同步复制：确保数据零丢失
  4. 级联复制：减轻主库负担
  5. 逻辑复制：选择性复制数据
  6. 故障转移：手动和自动切换
  7. 高可用方案：Patroni、pg_auto_failover、repmgr
  8. 读写分离：分散读负载
  9. 监控复制：监控复制状态和延迟

练习​

  1. 搭建一个一主两从的流复制环境
  2. 配置同步复制，测试数据一致性
  3. 使用逻辑复制同步指定的表
  4. 部署 Patroni 实现自动故障转移
  5. 编写监控脚本，当复制延迟超过阈值时告警

参考资源​