备份、恢复与高可用

9.1 pg_dump 逻辑备份

逻辑备份将数据库导出为 SQL 脚本或自定义格式文件，可以跨版本迁移，可以选择性恢复特定表。

SHELL# 备份单个数据库（plain 文本格式，适合小数据库）
pg_dump -h localhost -U myuser -d mydb > mydb_backup.sql

# custom 格式：压缩、支持并行恢复、支持选择性恢复（推荐）
pg_dump -h localhost -U myuser -d mydb \
  --format=custom \
  --compress=9 \
  --file=mydb_backup.dump

# directory 格式：每张表一个文件，支持并行备份（大数据库推荐）
pg_dump -h localhost -U myuser -d mydb \
  --format=directory \
  --jobs=4 \         # 4 个并行任务
  --file=mydb_backup_dir/

# 备份所有数据库（含角色、表空间定义）
pg_dumpall -h localhost -U postgres > all_databases.sql

# 只备份全局对象（角色、表空间，不含数据）
pg_dumpall -h localhost -U postgres --globals-only > globals.sql

# 仅备份表结构（不含数据）
pg_dump -h localhost -U myuser -d mydb --schema-only > schema.sql

# 仅备份数据（不含结构）
pg_dump -h localhost -U myuser -d mydb --data-only > data.sql

SHELL# pg_restore：从 custom/directory 格式恢复

# 恢复到新数据库
createdb -h localhost -U myuser mydb_restored
pg_restore -h localhost -U myuser -d mydb_restored mydb_backup.dump

# 并行恢复（大幅提升速度）
pg_restore -h localhost -U myuser -d mydb_restored \
  --jobs=4 \
  mydb_backup.dump

# 选择性恢复：只恢复 orders 表
pg_restore -h localhost -U myuser -d mydb_restored \
  --table=orders \
  mydb_backup.dump

# 只恢复结构（不含数据）
pg_restore --schema-only -d mydb_restored mydb_backup.dump

# 恢复 plain SQL 格式
psql -h localhost -U myuser -d mydb_restored < mydb_backup.sql

9.2 PITR（时间点恢复）

PITR（Point-in-Time Recovery）允许将数据库恢复到任意历史时间点，是生产级灾备的标准方案。依赖于 WAL（Write-Ahead Log）归档。

postgresql.conf# 1. 在主库开启 WAL 归档（postgresql.conf）
wal_level = replica           # 至少 replica 才能归档
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
# %p = WAL 文件路径, %f = 文件名
# 生产环境推荐：归档到 S3 或 NFS
# archive_command = 'aws s3 cp %p s3://my-pg-wal-bucket/%f'

max_wal_senders = 3           # 流复制从库数量
wal_keep_size = 1GB           # 本地保留的 WAL 量

SHELL# 2. 取基础备份（base backup）
pg_basebackup \
  -h localhost \
  -U replica_user \
  -D /backup/basebackup_$(date +%Y%m%d) \
  --format=tar \
  --compress=9 \
  --checkpoint=fast \
  --progress \
  --wal-method=stream   # 同时备份备份过程中产生的 WAL

# 3. 恢复到指定时间点
# 将基础备份解压到新的 PGDATA 目录
tar xzf base.tar.gz -C /var/lib/postgresql/17/restore/

recovery.conf (PG12前) / postgresql.conf (PG12+)# PG 12+ 在 postgresql.conf 中配置恢复
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2024-03-15 14:30:00+08'
recovery_target_action = 'promote'  # 恢复完成后提升为可读写

9.3 流复制

流复制（Streaming Replication）将主库的 WAL 实时发送给从库，从库应用 WAL 实现数据同步，实现读写分离和高可用。

postgresql.conf (主库)# 主库配置
wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB

# 主库防火墙需开放 5432 端口给从库

pg_hba.conf (主库)# 允许从库连接复制
host  replication  replica_user  192.168.1.0/24  scram-sha-256

SQL (主库)-- 创建复制专用用户
CREATE USER replica_user WITH
  REPLICATION
  LOGIN
  ENCRYPTED PASSWORD 'replica_password';

SHELL (从库)# 从主库创建基础备份（在从库服务器上执行）
pg_basebackup \
  -h 192.168.1.100 \
  -U replica_user \
  -D /var/lib/postgresql/17/standby/ \
  -P \
  --wal-method=stream

# 创建 standby.signal 文件（PG 12+ 替代 recovery.conf）
touch /var/lib/postgresql/17/standby/standby.signal

postgresql.conf (从库)# 从库连接主库配置
primary_conninfo = 'host=192.168.1.100 port=5432 user=replica_user password=replica_password application_name=standby1'
hot_standby = on          # 允许从库接受只读查询
hot_standby_feedback = on # 从库将长事务信息反馈给主库，避免主库 VACUUM 导致冲突

SQL (主库监控)-- 查看复制状态
SELECT
  client_addr,
  application_name,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag_bytes,
  write_lag,
  flush_lag,
  replay_lag,
  sync_state
FROM pg_stat_replication;

-- state 含义：
-- streaming: 正常流复制中
-- startup:   从库正在追赶 WAL
-- backup:    正在创建 basebackup

9.4 Patroni：自动故障转移

Patroni 是目前最成熟的 PostgreSQL HA（高可用）解决方案，可以在主库宕机时自动将从库提升为主库，并更新连接信息。

patroni.ymlscope: postgres-cluster
namespace: /db/
name: pg-node1

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

etcd3:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 30
    maximum_lag_on_failover: 1048576  # 最大允许 1MB 落后
    postgresql:
      use_pg_rewind: true
      parameters:
        max_connections: 200
        shared_buffers: 4GB
        effective_cache_size: 12GB
        wal_level: replica

  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.101:5432
  data_dir: /var/lib/postgresql/17/main
  authentication:
    replication:
      username: replicator
      password: reppassword
    superuser:
      username: postgres
      password: postgrespassword

SHELL# Patronictl 常用命令
patronictl -c /etc/patroni.yml list          # 查看集群状态
patronictl -c /etc/patroni.yml failover postgres-cluster   # 手动故障转移
patronictl -c /etc/patroni.yml switchover postgres-cluster # 计划内主从切换
patronictl -c /etc/patroni.yml restart postgres-cluster pg-node1  # 重启节点
patronictl -c /etc/patroni.yml pause         # 暂停自动故障转移
patronictl -c /etc/patroni.yml resume        # 恢复自动故障转移

9.5 PgBouncer 连接池

PostgreSQL 的每个连接都是一个独立的 OS 进程，大量短连接（如 Web 请求）会导致进程创建开销极高。PgBouncer 作为连接池代理，维护少量"真实"DB 连接，服务大量客户端连接。

模式	事务数据库连接	适用场景	限制
Session 模式	客户端连接期间一直持有	长连接应用	节省最少，接近无池化
Transaction 模式（推荐）	每个事务分配一个DB连接，事务结束即归还	无状态 Web 应用	不能用 SET、PREPARE、LISTEN
Statement 模式	每条语句独立分配连接	极少场景	不能用事务

pgbouncer.ini[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

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

# 连接池大小配置
max_client_conn = 10000   # 最大客户端连接数
default_pool_size = 25    # 每个数据库+用户组合的连接池大小
min_pool_size = 5         # 最小保持的空闲连接数
reserve_pool_size = 5     # 紧急预留连接数
reserve_pool_timeout = 5  # 超过此时间从预留池借用

# 超时设置
server_idle_timeout = 600    # 空闲后端连接超时
client_idle_timeout = 0      # 客户端空闲超时（0=不限制）
query_timeout = 0            # 查询超时（0=不限制，建议设置）
client_login_timeout = 60

# 日志
log_connections = 0   # 生产环境关闭，否则日志暴增
log_disconnections = 0
logfile = /var/log/pgbouncer/pgbouncer.log

9.6 监控关键指标

SQL-- 1. 连接数监控
SELECT
  COUNT(*) AS total_connections,
  COUNT(*) FILTER (WHERE state = 'active')  AS active,
  COUNT(*) FILTER (WHERE state = 'idle')    AS idle,
  COUNT(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,
  MAX(now() - query_start) FILTER (WHERE state = 'active') AS longest_query
FROM pg_stat_activity
WHERE datname = current_database();

-- 2. 缓存命中率（应保持 > 99%）
SELECT
  sum(heap_blks_read)  AS heap_read,
  sum(heap_blks_hit)   AS heap_hit,
  ROUND(sum(heap_blks_hit) * 100.0 /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- 3. 表大小排名
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
  pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;