PostgreSQL 高可用架构

一、高可用概述

1.1 高可用目标

1.2 复制架构对比

特性	流复制	逻辑复制
复制单位	整个实例	表/数据库
数据格式	WAL 物理日志	逻辑变更
从库可写	否	是
跨版本	需相同主版本	可以
跨平台	需相同平台	可以
DDL 复制	自动	不复制
性能影响	低	较高
复杂度	低	中等

二、流复制

2.1 流复制原理

2.2 主库配置

# 1. 创建复制用户
sudo -u postgres psql << EOF
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replica_password';
EOF

# 2. 修改 postgresql.conf
cat >> /var/lib/postgresql/data/postgresql.conf << EOF

# 复制配置
wal_level = replica                    # 必须为 replica 或 logical
max_wal_senders = 10                   # 最大 WAL 发送进程数
wal_keep_size = 1GB                    # 保留的 WAL 大小
max_replication_slots = 10             # 最大复制槽数
hot_standby = on                       # 从库可读

# 同步复制（可选）
# synchronous_commit = on
# synchronous_standby_names = 'standby1'
EOF

# 3. 修改 pg_hba.conf，允许复制连接
cat >> /var/lib/postgresql/data/pg_hba.conf << EOF

# 复制连接
host    replication     replicator      192.168.1.0/24          scram-sha-256
EOF

# 4. 重启主库
systemctl restart postgresql

2.3 从库配置

# 1. 停止从库 PostgreSQL（如果已运行）
systemctl stop postgresql

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

# 3. 使用 pg_basebackup 从主库复制数据
pg_basebackup -h 192.168.1.100 -p 5432 -U replicator -D /var/lib/postgresql/data \
    -Fp -Xs -P -R

# 参数说明：
# -h: 主库地址
# -p: 主库端口
# -U: 复制用户
# -D: 目标数据目录
# -Fp: 纯文本格式
# -Xs: 流复制模式传输 WAL
# -P: 显示进度
# -R: 自动创建 standby.signal 和配置复制参数

# 4. 检查生成的配置（postgresql.auto.conf）
cat /var/lib/postgresql/data/postgresql.auto.conf
# primary_conninfo = 'user=replicator password=replica_password host=192.168.1.100 port=5432'

# 5. 启动从库
systemctl start postgresql

2.4 复制监控

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

-- 主库：查看复制槽
SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots;

-- 从库：查看复制状态
SELECT
    pg_is_in_recovery() AS is_standby,
    pg_last_wal_receive_lsn() AS receive_lsn,
    pg_last_wal_replay_lsn() AS replay_lsn,
    pg_last_xact_replay_timestamp() AS last_replay_time,
    EXTRACT(EPOCH FROM (NOW() - pg_last_xact_replay_timestamp())) AS lag_seconds;

-- 检查复制延迟（秒）
SELECT 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 replication_lag_seconds;

2.5 同步复制

-- 配置同步复制（postgresql.conf）
-- synchronous_commit = on
-- synchronous_standby_names = 'FIRST 1 (standby1, standby2)'

-- 同步模式选项
-- FIRST n (standby1, standby2, ...): 等待前 n 个从库
-- ANY n (standby1, standby2, ...): 等待任意 n 个从库

-- 查看同步状态
SELECT
    application_name,
    sync_state,      -- async, potential, sync, quorum
    sync_priority
FROM pg_stat_replication;

-- 同步级别（synchronous_commit）
-- off: 不等待（最快，可能丢数据）
-- local: 等待本地 WAL 刷盘
-- remote_write: 等待从库写入 WAL
-- on (remote_flush): 等待从库刷盘（默认）
-- remote_apply: 等待从库应用

-- 事务级别设置同步模式
BEGIN;
SET LOCAL synchronous_commit = 'remote_apply';
-- 执行关键操作
COMMIT;

2.6 复制槽

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

-- 使用复制槽（从库 primary_conninfo）
-- primary_slot_name = 'standby1_slot'

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

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

-- 复制槽的作用：
-- 1. 确保 WAL 不被删除，直到从库接收
-- 2. 防止从库因 WAL 丢失而断开
-- 3. 支持从库长时间离线后恢复

三、逻辑复制

3.1 逻辑复制原理

3.2 配置逻辑复制

-- 发布者配置（postgresql.conf）
-- wal_level = logical
-- max_replication_slots = 10
-- max_wal_senders = 10

-- 1. 在发布者创建发布
-- 发布所有表
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- 发布特定表
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- 发布特定操作
CREATE PUBLICATION my_publication FOR TABLE users
    WITH (publish = 'insert, update');

-- 2. 在订阅者创建表结构（逻辑复制不复制 DDL）
-- 使用 pg_dump 导出表结构
pg_dump -h publisher_host -U postgres -s -t users -t orders mydb > schema.sql
psql -f schema.sql

-- 3. 在订阅者创建订阅
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=192.168.1.100 port=5432 user=replicator password=xxx dbname=mydb'
    PUBLICATION my_publication;

-- 带选项的订阅
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=192.168.1.100 port=5432 user=replicator password=xxx dbname=mydb'
    PUBLICATION my_publication
    WITH (
        copy_data = true,           -- 初始同步数据（默认 true）
        create_slot = true,         -- 自动创建复制槽（默认 true）
        enabled = true,             -- 立即启用（默认 true）
        synchronous_commit = 'off'  -- 同步提交级别
    );

3.3 逻辑复制管理

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

-- 修改发布
ALTER PUBLICATION my_publication ADD TABLE new_table;
ALTER PUBLICATION my_publication DROP TABLE old_table;
ALTER PUBLICATION my_publication SET (publish = 'insert, update, delete');

-- 删除发布
DROP PUBLICATION my_publication;

-- 查看订阅
SELECT * FROM pg_subscription;

-- 查看订阅状态
SELECT
    subname,
    pid,
    relid::regclass,
    received_lsn,
    last_msg_send_time,
    last_msg_receipt_time,
    latest_end_lsn,
    latest_end_time
FROM pg_stat_subscription;

-- 管理订阅
ALTER SUBSCRIPTION my_subscription DISABLE;
ALTER SUBSCRIPTION my_subscription ENABLE;
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;

-- 删除订阅
DROP SUBSCRIPTION my_subscription;

-- 手动同步表
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION WITH (copy_data = true);

3.4 逻辑复制注意事项

-- 1. 必须有主键或复制标识
-- 没有主键的表需要设置复制标识
ALTER TABLE my_table REPLICA IDENTITY FULL;
-- 或使用唯一索引
ALTER TABLE my_table REPLICA IDENTITY USING INDEX my_unique_index;

-- 复制标识选项
-- DEFAULT: 使用主键
-- NOTHING: 不记录旧值（不能 UPDATE/DELETE）
-- FULL: 记录所有列旧值（性能差）
-- USING INDEX: 使用指定的唯一索引

-- 2. DDL 不会复制
-- 需要在两端手动执行 DDL

-- 3. 序列不会复制
-- 需要手动同步或使用 UUID

-- 4. 大对象不支持

-- 5. 冲突处理
-- 主键冲突会导致复制停止
-- 需要手动解决后重启订阅

-- 查看复制错误
SELECT * FROM pg_stat_subscription_stats;

四、读写分离

4.1 读写分离架构

4.2 PgBouncer 配置

; /etc/pgbouncer/pgbouncer.ini

[databases]
; 主库（读写）
mydb_rw = host=192.168.1.100 port=5432 dbname=mydb

; 从库（只读）- 多个从库轮询
mydb_ro = host=192.168.1.101,192.168.1.102 port=5432 dbname=mydb

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; 连接池模式
; session: 会话级（保持连接到会话结束）
; transaction: 事务级（事务结束释放连接）
; statement: 语句级（每条语句后释放）
pool_mode = transaction

; 连接池大小
default_pool_size = 20
min_pool_size = 5
max_client_conn = 1000

; 超时设置
server_connect_timeout = 3
server_login_retry = 3
query_timeout = 300
client_idle_timeout = 0

; 日志
logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 1
log_disconnections = 1

# userlist.txt（密码使用 scram-sha-256 格式）
# "username" "password"
"app_user" "SCRAM-SHA-256$..."

4.3 应用层读写分离

# Python 示例：基于 SQLAlchemy 的读写分离
from sqlalchemy import create_engine, event
from sqlalchemy.orm import sessionmaker
import random

# 创建主库和从库连接
master_engine = create_engine('postgresql://user:pass@master:5432/mydb')
slave_engines = [
    create_engine('postgresql://user:pass@slave1:5432/mydb'),
    create_engine('postgresql://user:pass@slave2:5432/mydb'),
]

# 创建会话
MasterSession = sessionmaker(bind=master_engine)
SlaveSession = sessionmaker(bind=random.choice(slave_engines))

class RoutingSession:
    def __init__(self):
        self.master = MasterSession()
        self.slave = SlaveSession()
        self._use_master = False

    def using_master(self):
        self._use_master = True
        return self

    @property
    def session(self):
        return self.master if self._use_master else self.slave

    def query(self, *args, **kwargs):
        return self.session.query(*args, **kwargs)

    def add(self, instance):
        return self.master.add(instance)

    def commit(self):
        return self.master.commit()

# 使用示例
db = RoutingSession()

# 读操作（自动路由到从库）
users = db.query(User).filter(User.status == 1).all()

# 写操作（自动路由到主库）
db.add(User(name='Alice'))
db.commit()

# 强制使用主库读（保证读到最新数据）
user = db.using_master().query(User).filter(User.id == 1).first()

// Java 示例：Spring Boot 读写分离
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource routingDataSource() {
        RoutingDataSource routingDataSource = new RoutingDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource());
        targetDataSources.put("slave", slaveDataSource());
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(masterDataSource());
        return routingDataSource;
    }
}

// 自定义路由数据源
public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

// 注解标记只读
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnly {
}

// AOP 切换数据源
@Aspect
@Component
public class DataSourceAspect {
    @Before("@annotation(ReadOnly)")
    public void setReadDataSource() {
        DataSourceContextHolder.setDataSourceType("slave");
    }

    @After("@annotation(ReadOnly)")
    public void clearDataSource() {
        DataSourceContextHolder.clear();
    }
}

五、高可用方案

5.1 Patroni 方案

5.2 Patroni 配置

# /etc/patroni/patroni.yml
scope: postgres-cluster
namespace: /db/
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.101:8008

etcd:
  hosts:
    - 192.168.1.10:2379
    - 192.168.1.11:2379
    - 192.168.1.12:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576 # 1MB
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: on
        max_connections: 200
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 1GB

  initdb:
    - encoding: UTF8
    - data-checksums

  pg_hba:
    - host replication replicator 192.168.1.0/24 scram-sha-256
    - host all all 192.168.1.0/24 scram-sha-256

  users:
    admin:
      password: admin_password
      options:
        - createrole
        - createdb
    replicator:
      password: replica_password
      options:
        - replication

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.101:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/15/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replica_password
    superuser:
      username: postgres
      password: postgres_password
  parameters:
    unix_socket_directories: "/var/run/postgresql"

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

5.3 Patroni 管理

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

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

# 故障转移（强制）
patronictl -c /etc/patroni/patroni.yml failover

# 重新初始化从库
patronictl -c /etc/patroni/patroni.yml reinit node2

# 暂停自动故障转移
patronictl -c /etc/patroni/patroni.yml pause

# 恢复自动故障转移
patronictl -c /etc/patroni/patroni.yml resume

# 修改配置
patronictl -c /etc/patroni/patroni.yml edit-config

# 重启节点
patronictl -c /etc/patroni/patroni.yml restart node1

# 重新加载配置
patronictl -c /etc/patroni/patroni.yml reload node1

5.4 HAProxy 配置

# /etc/haproxy/haproxy.cfg
global
    maxconn 1000
    log /dev/log local0

defaults
    mode tcp
    log global
    retries 2
    timeout connect 3s
    timeout server 30s
    timeout client 30s

frontend postgres_frontend
    bind *:5432
    default_backend postgres_master

backend postgres_master
    option httpchk GET /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server node1 192.168.1.101:5432 check port 8008
    server node2 192.168.1.102:5432 check port 8008
    server node3 192.168.1.103:5432 check port 8008

frontend postgres_replica_frontend
    bind *:5433
    default_backend postgres_replicas

backend postgres_replicas
    option httpchk GET /replica
    http-check expect status 200
    balance roundrobin
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server node1 192.168.1.101:5432 check port 8008
    server node2 192.168.1.102:5432 check port 8008
    server node3 192.168.1.103:5432 check port 8008

listen stats
    bind *:7000
    stats enable
    stats uri /

六、故障恢复

6.1 主从切换

# 计划内切换（Switchover）
# 1. 使用 Patroni
patronictl -c /etc/patroni/patroni.yml switchover

# 2. 手动切换
# 在主库
pg_ctl promote -D /var/lib/postgresql/data

# 或通过 SQL
SELECT pg_promote();

6.2 时间点恢复（PITR）

# 1. 配置 WAL 归档（主库 postgresql.conf）
archive_mode = on
archive_command = 'cp %p /archive/%f'

# 2. 执行基础备份
pg_basebackup -h localhost -D /backup/base -Ft -z -P

# 3. 恢复到指定时间点
# 解压基础备份
tar -xzf /backup/base/base.tar.gz -C /var/lib/postgresql/data

# 创建恢复配置
cat > /var/lib/postgresql/data/postgresql.auto.conf << EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2024-01-15 10:30:00'
recovery_target_action = 'promote'
EOF

# 创建恢复信号文件
touch /var/lib/postgresql/data/recovery.signal

# 启动数据库
pg_ctl start -D /var/lib/postgresql/data

6.3 从库重建

# 方法 1：pg_basebackup
pg_basebackup -h master_host -D /var/lib/postgresql/data -U replicator -Fp -Xs -P -R

# 方法 2：pg_rewind（快速重建，需要 wal_log_hints = on）
pg_rewind --target-pgdata=/var/lib/postgresql/data \
          --source-server='host=master_host user=replicator'

# 方法 3：使用 Patroni
patronictl -c /etc/patroni/patroni.yml reinit standby_node

七、总结

高可用方案选择

方案	适用场景	复杂度	RTO	RPO
流复制 + 手动切换	小规模、成本敏感	低	分钟级	接近 0
Patroni + etcd	中大规模生产环境	中	秒级	接近 0
PgBouncer	读写分离、连接池	低	-	-
逻辑复制	跨版本迁移、部分同步	中	-	可能丢失

最佳实践

1. 同步复制：关键业务使用同步复制，确保 0 数据丢失
2. 复制槽：始终使用复制槽，防止 WAL 被删除
3. 监控：监控复制延迟、WAL 积压
4. 定期演练：定期进行故障转移演练
5. 备份：即使有复制，也要定期备份
6. 网络：主从库使用专用网络，减少延迟