PostgreSQL 高可用与备份：流复制、Patroni 自动故障转移与 PITR 恢复

wal_level = replica              # 启用 WAL 的详细日志（minimal/replica/logical）
max_wal_senders = 10             # 同时发送 WAL 的最大连接数
wal_keep_size = 1GB              # 保留 WAL 的最小量（备库 lag 过大时用）
max_replication_slots = 5        # 逻辑/物理复制槽数上限
hot_standby = on                 # 备库允许只读查询
synchronous_commit = remote_apply # remote_apply = 主库等待备库应用完成
synchronous_standby_names = 'FIRST 1 (s1, s2)'  # 指定同步对象

# 1. 从基础备份恢复
pg_basebackup -h primary_host -p 5432 -U repluser -D /var/lib/postgresql/data \
  -Ft -z -P --wal-method=stream

# 2. 创建 standby.signal 文件（让 PostgreSQL 以备模式启动）
touch /var/lib/postgresql/data/standby.signal

# 3. 配置主库连接
# postgresql.auto.conf
primary_conninfo = 'host=primary_host port=5432 user=repluser password=secret application_name=s1'

# 4. 启动备库
pg_ctl -D /var/lib/postgresql/data start

-- 主库：创建发布（Publish）
CREATE PUBLICATION app_publication FOR TABLE users, posts, orders;
-- 或全表：CREATE PUBLICATION all_tables FOR ALL TABLES;

-- 备库：创建订阅（Subscribe）
CREATE SUBSCRIPTION app_subscription
  CONNECTION 'host=primary_host port=5432 user=repluser password=secret dbname=myapp'
  PUBLICATION app_publication
  WITH (copy_data = true, create_slot = true);

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

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

# patroni.yml
scope: mycluster
namespace: /db/
name: node-1

restapi:
  listen: 0.0.0.0:8008
  connect_address: node-1:8008

etcd:
  hosts: etcd-1:2379,etcd-2:2379,etcd-3:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576  # 1MB WAL lag 限制
    postgresql:
      parameters:
        wal_level: replica
        hot_standby: on
        max_connections: 200

postgresql:
  listen: 0.0.0.0:5432
  connect_address: node-1:5432
  data_dir: /var/lib/postgresql/data
  authentication:
    replication:
      username: repluser
      password: secret
    superuser:
      username: postgres
      password: secret

#!/bin/bash
# backup.sh
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR=/backups/postgres
DB_NAME=myapp
RETENTION_DAYS=30

# pg_dump 自定义格式（可并行恢复）
pg_dump -Fc -Z 9 -v -d ${DB_NAME} -f ${BACKUP_DIR}/${DB_NAME}_${DATE}.dump

# 全局对象（角色/表空间）
pg_dumpall -r -f ${BACKUP_DIR}/roles_${DATE}.sql

# 清理旧备份（保留 30 天）
find ${BACKUP_DIR} -name "*.dump" -mtime +${RETENTION_DAYS} -delete

# 配置归档（postgresql.conf）
archive_mode = on
archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'
wal_level = replica

# 基础备份
pg_basebackup -D /backups/base_$(date +%Y%m%d) \
  -Ft -z -P -h primary -p 5432 -U repluser --wal-method=stream

# 备份完后生成 backup_manifest（PostgreSQL 14+）
# 用 pg_verifybackup /backups/base_20250801 验证一致性

# 1. 解压基础备份
tar xzf /backups/base_20250801.tar.zst -C /var/lib/postgresql/recovery

# 2. 创建恢复信号
touch /var/lib/postgresql/recovery/recovery.signal

# 3. 配置恢复参数（postgresql.conf 或命令行）
# restore_command：PostgreSQL 请求 WAL 文件时调用
cat > /var/lib/postgresql/recovery/postgresql.auto.conf <<EOF
restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2025-08-01 14:30:00'
recovery_target_action = 'promote'
EOF

# 4. 启动
cd /var/lib/postgresql/recovery && pg_ctl start
# PostgreSQL 会自动回放 WAL 到 14:30:00，完成后变为 Primary