PostgreSQL 备份与恢复

数据是最重要的资产，备份与恢复是 DBA 的核心技能。本文档涵盖逻辑备份、物理备份、WAL 归档、PITR、数据迁移及主从复制与故障切换。

1. 备份方式介绍

三种备份方式对比

特性	逻辑备份	物理备份（冷/热）	物理备份 + WAL 归档
工具	`pg_dump` / `pg_dumpall`	`pg_basebackup` / 文件系统复制	`pg_basebackup` + WAL 归档
备份内容	SQL 语句或自定义格式	数据目录的完整拷贝	基础备份 + 持续归档的 WAL
备份粒度	库、表、Schema 级别	整个集群	整个集群
恢复速度	慢（需重放 SQL）	快（直接替换数据目录）	快 + 可恢复到任意时间点
跨版本	支持（可跨大版本）	不支持	不支持
跨平台	支持	同平台/架构	同平台/架构
备份期间锁表	否（MVCC 快照）	否（pg_basebackup）	否
支持 PITR	否	否	是
适用场景	小中型库、迁移、部分表	大型库、快速全量恢复	生产环境、灾难恢复

备份策略建议

小型数据库（< 10GB）：
  └── pg_dump 每日全量逻辑备份

中型数据库（10GB ~ 500GB）：
  ├── pg_basebackup 每周全量物理备份
  └── WAL 归档持续增量

大型数据库（> 500GB）：
  ├── pg_basebackup 每周全量
  ├── WAL 归档持续增量
  ├── PG 17+ 增量备份
  └── 流复制从库作为热备

所有环境：
  └── 定期恢复演练！备份没经过验证等于没有备份

2. 逻辑备份 — pg_dump

基本用法

bash

# 备份单个数据库（SQL 文本格式）
pg_dump -h localhost -p 5432 -U postgres mydb > mydb.sql
pg_dump -h localhost -p 5432 -U postgres -d mydb -f mydb.sql

# 备份为自定义格式（推荐，支持并行恢复、压缩、选择性恢复）
pg_dump -Fc -d mydb -f mydb.dump

# 备份为目录格式（支持并行备份）
pg_dump -Fd -j 4 -d mydb -f mydb_dir/

# 备份为 tar 格式
pg_dump -Ft -d mydb -f mydb.tar

输出格式对比

格式	参数	压缩	并行备份	并行恢复	选择性恢复
SQL 文本	`-Fp`（默认）	否（可 gzip）	否	否	手动编辑
自定义	`-Fc`	自动	否	是	是
目录	`-Fd`	自动	是	是	是
Tar	`-Ft`	否	否	否	是

常用选项

bash

# 只备份 Schema（不含数据）
pg_dump -s -d mydb -f schema_only.sql

# 只备份数据（不含 Schema）
pg_dump -a -d mydb -f data_only.sql

# 备份指定表
pg_dump -t users -t orders -d mydb -f tables.dump

# 备份指定 Schema
pg_dump -n public -n app -d mydb -f schemas.dump

# 排除指定表
pg_dump -T temp_logs -T cache_* -d mydb -f mydb.dump

# 包含大对象（默认包含，-Fp 格式需显式指定）
pg_dump -b -d mydb -f mydb.dump

# 使用 INSERT 而非 COPY（兼容性好，速度慢）
pg_dump --inserts -d mydb -f mydb.sql
pg_dump --column-inserts -d mydb -f mydb.sql  # 含列名

# 压缩级别（自定义/目录格式）
pg_dump -Fc -Z 9 -d mydb -f mydb.dump         # 最大压缩
pg_dump -Fc -Z 0 -d mydb -f mydb.dump         # 不压缩

# SQL 文本格式配合 gzip
pg_dump -d mydb | gzip > mydb.sql.gz

备份所有数据库 — pg_dumpall

bash

# 备份整个集群（所有数据库 + 全局对象）
pg_dumpall -h localhost -U postgres > all_databases.sql

# 只备份全局对象（角色、表空间等）
pg_dumpall --globals-only > globals.sql

# 只备份角色
pg_dumpall --roles-only > roles.sql

# 只备份表空间定义
pg_dumpall --tablespaces-only > tablespaces.sql

逻辑恢复 — pg_restore / psql

bash

# SQL 文本格式恢复（使用 psql）
psql -d mydb -f mydb.sql
psql -d mydb < mydb.sql
gunzip -c mydb.sql.gz | psql -d mydb            # gzip 压缩的

# 自定义/目录/tar 格式恢复（使用 pg_restore）
pg_restore -d mydb mydb.dump
pg_restore -d mydb mydb_dir/
pg_restore -d mydb mydb.tar

# 并行恢复（大幅加速，仅自定义/目录格式）
pg_restore -d mydb -j 4 mydb.dump

# 恢复到新数据库
createdb newdb
pg_restore -d newdb mydb.dump

# 创建数据库并恢复（-C 选项会包含 CREATE DATABASE）
pg_restore -C -d postgres mydb.dump

# 只恢复指定表
pg_restore -t users -t orders -d mydb mydb.dump

# 只恢复 Schema 结构
pg_restore -s -d mydb mydb.dump

# 只恢复数据
pg_restore -a -d mydb mydb.dump

# 先清除再恢复（--clean 添加 DROP 语句）
pg_restore --clean --if-exists -d mydb mydb.dump

# 查看备份内容（不实际恢复）
pg_restore -l mydb.dump                           # 列出目录
pg_restore -l mydb.dump > toc.list                # 导出为列表
# 编辑 toc.list 注释掉不需要的项
pg_restore -L toc.list -d mydb mydb.dump          # 按列表恢复

错误处理

bash

# 遇到错误继续恢复（默认遇错停止）
pg_restore --no-owner --no-privileges -d mydb mydb.dump 2>restore_errors.log

# 单事务模式（全部成功或全部失败）
pg_restore -1 -d mydb mydb.dump       # --single-transaction
psql -1 -d mydb -f mydb.sql

# 忽略所有者和权限（跨环境迁移时常用）
pg_restore --no-owner --no-privileges --role=target_user -d mydb mydb.dump

自动化备份脚本

bash

#!/bin/bash
# pg_backup.sh — 每日逻辑备份脚本

BACKUP_DIR="/data/backups/pg"
DB_NAME="mydb"
DB_USER="postgres"
DB_HOST="localhost"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p "$BACKUP_DIR"

# 执行备份
pg_dump -Fc -Z 6 \
    -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME" \
    -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "[$(date)] 备份成功: ${DB_NAME}_${DATE}.dump"

    # 验证备份文件
    pg_restore -l "$BACKUP_DIR/${DB_NAME}_${DATE}.dump" > /dev/null 2>&1
    if [ $? -eq 0 ]; then
        echo "[$(date)] 备份验证通过"
    else
        echo "[$(date)] 警告: 备份验证失败！" >&2
    fi
else
    echo "[$(date)] 备份失败！" >&2
    exit 1
fi

# 清理过期备份
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] 已清理 ${RETENTION_DAYS} 天前的备份"

3. WAL 日志归档

WAL 基础

WAL（Write-Ahead Logging，预写式日志）是 PostgreSQL 保证数据持久性的核心机制。

数据写入流程：

  事务提交
    │
    ▼
  写入 WAL 缓冲区 → 刷新到 WAL 文件（磁盘）
    │                         │
    ▼                         ▼
  返回客户端「提交成功」    后台写入数据文件
                           (checkpoint)

WAL 文件位置：$PGDATA/pg_wal/
文件命名：000000010000000000000001（24位十六进制）
默认大小：16MB / 文件

sql

-- 查看当前 WAL 位置
SELECT pg_current_wal_lsn();           -- 当前 WAL 写入位置
SELECT pg_walfile_name(pg_current_wal_lsn());  -- 对应的 WAL 文件名
SELECT pg_current_wal_insert_lsn();    -- 当前插入位置

-- 查看 WAL 相关配置
SHOW wal_level;                        -- minimal / replica / logical
SHOW max_wal_size;                     -- WAL 最大总量（触发 checkpoint）
SHOW min_wal_size;                     -- WAL 最小保留量
SHOW wal_keep_size;                    -- 为复制保留的 WAL 量（PG 13+）

配置 WAL 归档

归档就是将写满的 WAL 文件自动拷贝到安全的存储位置。

ini

# postgresql.conf

# 1. WAL 级别必须为 replica 或 logical
wal_level = replica

# 2. 开启归档
archive_mode = on

# 3. 归档命令：%p = WAL 文件路径，%f = WAL 文件名
archive_command = 'cp %p /data/wal_archive/%f'

# 更健壮的归档命令（防止覆盖 + 校验）
archive_command = 'test ! -f /data/wal_archive/%f && cp %p /data/wal_archive/%f'

# 使用 rsync 归档到远程服务器
archive_command = 'rsync -a %p backup_server:/data/wal_archive/%f'

# 归档超时（强制切换 WAL，保证归档间隔不超过此时间）
archive_timeout = 300    # 5分钟（0=禁用）

bash

# 创建归档目录
mkdir -p /data/wal_archive
chown postgres:postgres /data/wal_archive

# 修改配置后重启（archive_mode 需要重启）
pg_ctl restart -D $PGDATA

# 或仅修改 archive_command 时可以 reload
pg_ctl reload -D $PGDATA

验证归档

bash

# 手动触发 WAL 切换
psql -c "SELECT pg_switch_wal();"

# 检查归档目录
ls -la /data/wal_archive/

# 查看归档状态
psql -c "SELECT * FROM pg_stat_archiver;"

sql

-- 查看归档统计
SELECT archived_count,         -- 已归档数量
       last_archived_wal,      -- 最后归档的 WAL 文件
       last_archived_time,     -- 最后归档时间
       failed_count,           -- 归档失败次数
       last_failed_wal,        -- 最后失败的 WAL 文件
       last_failed_time        -- 最后失败时间
FROM pg_stat_archiver;

PG 15+ archive_library

ini

# PG 15+ 支持使用归档模块替代 archive_command
# 性能更好，无需 fork 进程
archive_library = 'basic_archive'
basic_archive.archive_directory = '/data/wal_archive'

4. 物理备份 — pg_basebackup

基本用法

bash

# 基础物理备份（最常用）
pg_basebackup -h localhost -U replicator -D /data/backup/base \
    -Fp -Xs -P -v

# 参数说明：
# -D  备份目标目录（必须为空或不存在）
# -Fp 输出为 plain 格式（原始数据目录结构）
# -Ft 输出为 tar 格式
# -Xs 使用流复制传输 WAL（推荐）
# -Xf 使用 fetch 方式获取 WAL
# -P  显示进度
# -v  详细输出
# -z  gzip 压缩（tar 格式）
# -Z 6 压缩级别
# -c fast  快速 checkpoint（减少备份启动等待）

准备工作

sql

-- 1. 创建复制用户
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';

-- 2. 授予必要权限（PG 15+）
GRANT pg_read_all_data TO replicator;

ini

# 3. 配置 pg_hba.conf 允许复制连接
# TYPE  DATABASE        USER            ADDRESS         METHOD
host    replication     replicator      10.0.0.0/8      scram-sha-256
host    replication     replicator      127.0.0.1/32    scram-sha-256

ini

# 4. 确认 postgresql.conf 配置
wal_level = replica                # 必须
max_wal_senders = 10               # 最大 WAL 发送进程数

备份格式

bash

# Plain 格式：直接得到完整的数据目录（可直接启动）
pg_basebackup -D /data/backup/base -Fp -Xs -P

# Tar 格式：得到 tar 包，需要解压后才能使用
pg_basebackup -D /data/backup/ -Ft -Xs -P -z
# 输出：base.tar.gz  pg_wal.tar.gz

# Tar 格式解压恢复
mkdir -p /data/pgdata_restore
tar xzf base.tar.gz -C /data/pgdata_restore/
tar xzf pg_wal.tar.gz -C /data/pgdata_restore/pg_wal/

PG 15+ 备份压缩增强

bash

# 服务端压缩（减少网络传输）
pg_basebackup -D /data/backup/ -Ft --compress=server-gzip:6

# 客户端压缩
pg_basebackup -D /data/backup/ -Ft --compress=client-lz4

# zstd 压缩（PG 15+ 推荐，压缩率高速度快）
pg_basebackup -D /data/backup/ -Ft --compress=server-zstd:3

PG 17 增量备份

bash

# PG 17 新特性：增量备份，只备份上次以来变化的数据

# 前提：开启 WAL 摘要
# postgresql.conf: summarize_wal = on

# 首次全量备份
pg_basebackup -D /data/backup/full --checkpoint=fast -Xs

# 增量备份（基于上次备份的 manifest）
pg_basebackup -D /data/backup/incr1 \
    --incremental=/data/backup/full/backup_manifest

# 第二次增量备份（基于上次增量）
pg_basebackup -D /data/backup/incr2 \
    --incremental=/data/backup/incr1/backup_manifest

# 合并增量备份为可用的完整备份
pg_combinebackup /data/backup/full /data/backup/incr1 /data/backup/incr2 \
    -o /data/backup/combined

5. 物理备份恢复

Plain 格式恢复

bash

# 1. 停止 PostgreSQL
pg_ctl stop -D $PGDATA

# 2. 备份（可选）并清空当前数据目录
mv $PGDATA ${PGDATA}.old
# 或者
rm -rf $PGDATA

# 3. 拷贝备份数据
cp -r /data/backup/base $PGDATA

# 4. 清理不需要的文件
rm -f $PGDATA/postmaster.pid
rm -f $PGDATA/postmaster.opts

# 5. 确认权限
chown -R postgres:postgres $PGDATA
chmod 700 $PGDATA

# 6. 启动 PostgreSQL
pg_ctl start -D $PGDATA

Tar 格式恢复

bash

# 1. 停止 PostgreSQL
pg_ctl stop -D $PGDATA

# 2. 清空数据目录
rm -rf $PGDATA/*

# 3. 解压基础备份
tar xzf /data/backup/base.tar.gz -C $PGDATA/

# 4. 解压 WAL 文件
tar xzf /data/backup/pg_wal.tar.gz -C $PGDATA/pg_wal/

# 5. 权限与启动
chown -R postgres:postgres $PGDATA
chmod 700 $PGDATA
pg_ctl start -D $PGDATA

归档恢复（基础备份 + 归档 WAL）

bash

# 1. 停止 PostgreSQL
pg_ctl stop -D $PGDATA

# 2. 清空并恢复基础备份
rm -rf $PGDATA/*
cp -r /data/backup/base/* $PGDATA/
# 或 tar 格式解压

# 3. 配置恢复参数（PG 12+）
cat >> $PGDATA/postgresql.conf << 'EOF'
# 恢复配置
restore_command = 'cp /data/wal_archive/%f %p'
recovery_target_action = 'promote'
EOF

# 4. 创建恢复信号文件
touch $PGDATA/recovery.signal

# 5. 清理并启动
rm -f $PGDATA/postmaster.pid
chown -R postgres:postgres $PGDATA
chmod 700 $PGDATA
pg_ctl start -D $PGDATA

# PostgreSQL 启动后：
# - 从 pg_wal/ 和归档目录回放 WAL
# - 回放完毕后自动 promote 为读写模式
# - recovery.signal 文件被自动删除

PG 12 以前使用 recovery.conf 文件配置恢复参数。PG 12+ 改为在 postgresql.conf 中配置 + recovery.signal / standby.signal 信号文件。

6. PITR（Point-In-Time Recovery）

PITR 可以将数据库恢复到过去任意一个时间点，是误操作后的救命稻草。

PITR 前提

需要具备：
  1. 一个误操作之前的基础备份（pg_basebackup）
  2. 从基础备份到误操作时间点之间的所有 WAL 归档
  3. 明确的恢复目标（时间点、事务 ID 或 LSN）

恢复目标类型

ini

# 恢复到指定时间点
recovery_target_time = '2024-06-15 14:30:00+08'

# 恢复到指定事务 ID
recovery_target_xid = '12345678'

# 恢复到指定 WAL 位置
recovery_target_lsn = '0/1A2B3C4D'

# 恢复到命名还原点
recovery_target_name = 'before_migration'

# 恢复到最近一致性点（恢复所有可用 WAL）
recovery_target = 'immediate'

# 是否包含目标事务本身
recovery_target_inclusive = true    # 默认 true，包含该事务
recovery_target_inclusive = false   # 不包含，恢复到该事务之前

创建还原点

sql

-- 在执行重要操作前创建命名还原点
SELECT pg_create_restore_point('before_migration');

-- 执行迁移操作...
ALTER TABLE users ADD COLUMN new_col TEXT;
UPDATE users SET new_col = 'default';

-- 如果需要回滚，可以恢复到 'before_migration'

PITR 完整流程

bash

# 场景：2024-06-15 14:30 有人误删了 users 表
# 需要恢复到 14:29:59

# === 步骤一：停止数据库 ===
pg_ctl stop -D $PGDATA -m fast

# === 步骤二：保留现场（可选，用于事后分析） ===
mv $PGDATA ${PGDATA}_damaged

# === 步骤三：恢复基础备份 ===
# 使用误操作之前最近的基础备份
cp -r /data/backup/base_20240615_000000 $PGDATA
# 或 tar 格式
# mkdir -p $PGDATA
# tar xzf /data/backup/base.tar.gz -C $PGDATA/

# === 步骤四：配置 PITR ===
cat >> $PGDATA/postgresql.conf << 'EOF'
# PITR 恢复配置
restore_command = 'cp /data/wal_archive/%f %p'
recovery_target_time = '2024-06-15 14:29:59+08'
recovery_target_inclusive = false
recovery_target_action = 'pause'
EOF

# === 步骤五：创建恢复信号文件 ===
touch $PGDATA/recovery.signal

# === 步骤六：清理并启动 ===
rm -f $PGDATA/postmaster.pid
chown -R postgres:postgres $PGDATA
chmod 700 $PGDATA
pg_ctl start -D $PGDATA -l /tmp/pg_recovery.log

# === 步骤七：验证恢复结果 ===
# recovery_target_action = 'pause' 让数据库在到达目标点后暂停
# 此时可以查询验证数据是否正确
psql -c "SELECT count(*) FROM users;"
psql -c "SELECT * FROM users ORDER BY id DESC LIMIT 5;"

# === 步骤八：确认恢复，提升为正常模式 ===
psql -c "SELECT pg_wal_replay_resume();"    # 如果 paused，先恢复回放
# 然后 promote
psql -c "SELECT pg_promote();"              # PG 12+
# 或
pg_ctl promote -D $PGDATA

# === 步骤九：清理恢复配置 ===
# 注释掉或删除 postgresql.conf 中的恢复参数
# recovery.signal 已被自动删除

recovery_target_action 选项

值	说明
`pause`	到达目标点后暂停（可验证后手动 promote）
`promote`	到达目标点后自动提升为可写（默认值）
`shutdown`	到达目标点后关闭数据库

PITR 恢复后的时间线

PITR 恢复后会创建新的时间线（Timeline），避免与原 WAL 冲突。

原始时间线:  TL1 ───────────────────────────────►
                              │
                              │ PITR 恢复到此点
                              │
新时间线:                      └── TL2 ──────────►

WAL 文件名变化：
  恢复前：000000010000000000000005  (TL1)
  恢复后：000000020000000000000005  (TL2)

ini

# 选择恢复到哪个时间线
recovery_target_timeline = 'latest'    # 默认，恢复到最新时间线
recovery_target_timeline = 2           # 恢复到指定时间线

7. 数据迁移 — pgloader

pgloader 是一个强大的数据迁移工具，支持从 MySQL、SQLite、CSV 等源迁移到 PostgreSQL。

安装

bash

# Ubuntu/Debian
apt-get install pgloader

# CentOS/RHEL
yum install pgloader

# macOS
brew install pgloader

# Docker
docker run --rm -it dimitri/pgloader pgloader --version

MySQL 到 PostgreSQL

bash

# 一条命令完成迁移
pgloader mysql://root:password@mysql-host/mydb \
         postgresql://postgres:password@pg-host/mydb

lisp

;; 完整配置文件：mysql_to_pg.load
LOAD DATABASE
    FROM mysql://root:password@mysql-host:3306/mydb
    INTO postgresql://postgres:password@pg-host:5432/mydb

WITH include drop,
     create tables,
     create indexes,
     reset sequences,
     workers = 4,
     concurrency = 2,
     batch rows = 10000

SET maintenance_work_mem to '512MB',
    work_mem to '128MB'

-- 类型映射
CAST type int with extra auto_increment to serial,
     type bigint with extra auto_increment to bigserial,
     type tinyint to smallint,
     type mediumint to integer,
     type tinyint when (= 1 precision) to boolean,
     type datetime to timestamptz,
     type varchar to text

-- 排除某些表
EXCLUDING TABLE NAMES MATCHING 'temp_', 'cache_'

-- 表名映射
ALTER TABLE NAMES MATCHING 'tbl_users' RENAME TO 'users'

BEFORE LOAD DO
  $$ CREATE SCHEMA IF NOT EXISTS app; $$;

bash

# 使用配置文件执行
pgloader mysql_to_pg.load

SQLite 到 PostgreSQL

bash

pgloader sqlite:///path/to/mydb.sqlite3 \
         postgresql://postgres@localhost/mydb

CSV 到 PostgreSQL

lisp

;; csv_import.load
LOAD CSV
    FROM '/data/users.csv'
    INTO postgresql://postgres@localhost/mydb?tablename=users

WITH truncate,
     skip header = 1,
     fields optionally enclosed by '"',
     fields terminated by ','

SET work_mem to '64MB',
    standard_conforming_strings to 'on'

BEFORE LOAD DO
  $$ DROP TABLE IF EXISTS users; $$,
  $$ CREATE TABLE users (
       id serial primary key,
       name text,
       email text,
       age integer,
       created_at timestamptz
     );
  $$;

PostgreSQL COPY（原生高速导入）

sql

-- 从 CSV 导入（服务端文件）
COPY users (name, email, age)
FROM '/data/users.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');

-- 从 CSV 导入（客户端文件，使用 \copy）
\copy users (name, email, age) FROM '/local/users.csv' CSV HEADER

-- 导出为 CSV
COPY users TO '/data/users_export.csv' WITH (FORMAT csv, HEADER true);
\copy users TO '/local/users_export.csv' CSV HEADER

-- 管道导入
cat users.csv | psql -d mydb -c "COPY users FROM STDIN CSV HEADER;"

-- PG 17: HEADER MATCH（验证列名与表匹配）
COPY users FROM '/data/users.csv' WITH (FORMAT csv, HEADER MATCH);

其他迁移方式

bash

# pg_dump + pg_restore 跨版本迁移
# 从 PG 14 迁移到 PG 17
pg_dump -h old-server -U postgres -Fc -d mydb -f mydb.dump
pg_restore -h new-server -U postgres -d mydb mydb.dump

# 使用 pg_dump 管道（无需中间文件）
pg_dump -h old-server -U postgres -d mydb | \
    psql -h new-server -U postgres -d mydb

# pg_upgrade 就地大版本升级
pg_upgrade \
    --old-datadir=/var/lib/pgsql/14/data \
    --new-datadir=/var/lib/pgsql/17/data \
    --old-bindir=/usr/pgsql-14/bin \
    --new-bindir=/usr/pgsql-17/bin \
    --link    # 使用硬链接（快速，但不保留旧数据）

8. 主从异步流复制

流复制（Streaming Replication）让从库实时接收主库的 WAL 流，实现数据热备和读写分离。

架构

异步流复制架构：

  主库 (Primary)                   从库 (Standby)
  ┌──────────────┐                ┌──────────────┐
  │ 客户端写入    │    WAL Stream  │ 只读查询      │
  │ ──────────►  │ ──────────────► │ ◄──────────  │
  │ WAL 生成     │                │ WAL 回放      │
  └──────────────┘                └──────────────┘

  特点：
  - 主库不等待从库确认，提交即返回（异步）
  - 从库可能有少量延迟
  - 从库可以接受只读查询（Hot Standby）

主库配置

ini

# postgresql.conf（主库）

wal_level = replica                    # 必须
max_wal_senders = 10                   # WAL 发送进程数，建议 > 从库数
wal_keep_size = 1024                   # 保留 WAL 大小（MB），防止从库断开太久追不上
                                       # PG 13 之前使用 wal_keep_segments

# 可选：归档（双重保险）
archive_mode = on
archive_command = 'cp %p /data/wal_archive/%f'

# 同步复制相关（异步模式不需要）
# synchronous_standby_names = ''       # 空=异步

sql

-- 创建复制用户
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'rep_password';

ini

# pg_hba.conf（主库）
# TYPE  DATABASE        USER            ADDRESS              METHOD
host    replication     replicator      10.0.0.0/8           scram-sha-256
host    replication     replicator      192.168.1.0/24       scram-sha-256

bash

# 重新加载配置
pg_ctl reload -D $PGDATA

从库搭建

bash

# === 方法一：pg_basebackup（推荐） ===

# 1. 停止从库（如果已有实例）
pg_ctl stop -D $PGDATA_STANDBY

# 2. 清空从库数据目录
rm -rf $PGDATA_STANDBY/*

# 3. 从主库拉取基础备份
pg_basebackup -h primary-host -U replicator -D $PGDATA_STANDBY \
    -Fp -Xs -P -R -v

# 关键参数 -R：自动生成 standby.signal 和配置复制连接信息
# 会在 postgresql.auto.conf 中写入：
# primary_conninfo = 'host=primary-host user=replicator password=rep_password'

# 4. 检查生成的文件
ls $PGDATA_STANDBY/standby.signal          # 存在此文件表示从库模式
cat $PGDATA_STANDBY/postgresql.auto.conf   # 包含复制连接信息

# 5. 可选：自定义从库配置
cat >> $PGDATA_STANDBY/postgresql.conf << 'EOF'
# 从库特有配置
hot_standby = on                   # 允许只读查询
hot_standby_feedback = on          # 反馈给主库，防止 VACUUM 清理从库需要的行
max_standby_streaming_delay = 30s  # 查询与回放冲突时等待时间
max_standby_archive_delay = 30s
primary_conninfo = 'host=primary-host port=5432 user=replicator password=rep_password application_name=standby1'
EOF

# 6. 设置权限并启动
chown -R postgres:postgres $PGDATA_STANDBY
chmod 700 $PGDATA_STANDBY
pg_ctl start -D $PGDATA_STANDBY

验证复制

sql

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

-- 在从库查看接收状态
SELECT status, receive_start_lsn, received_lsn, last_msg_send_time,
       last_msg_receipt_time, latest_end_lsn
FROM pg_stat_wal_receiver;

-- 判断当前是主库还是从库
SELECT pg_is_in_recovery();  -- true=从库, false=主库

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

-- PG 10+ 查看详细延迟
SELECT write_lag, flush_lag, replay_lag FROM pg_stat_replication;

bash

# 测试：在主库写入数据
psql -h primary-host -c "INSERT INTO test VALUES (1, 'hello');"

# 在从库查询
psql -h standby-host -c "SELECT * FROM test;"
# 应该能看到数据（可能有毫秒级延迟）

# 在从库尝试写入（应该报错）
psql -h standby-host -c "INSERT INTO test VALUES (2, 'world');"
# ERROR: cannot execute INSERT in a read-only transaction

同步复制（可选）

ini

# 主库 postgresql.conf
# 指定同步从库名称（对应从库 primary_conninfo 中的 application_name）
synchronous_standby_names = 'standby1'

# 多同步从库
synchronous_standby_names = 'FIRST 1 (standby1, standby2)'  # 任一确认即可
synchronous_standby_names = 'ANY 1 (standby1, standby2)'    # 任一确认即可
synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)' # 任意两个确认

# 同步级别
synchronous_commit = on              # 默认，等 WAL 刷盘
synchronous_commit = remote_write    # 等从库写入 OS 缓存
synchronous_commit = remote_apply    # 等从库回放完成（最强，延迟最大）

9. 主从切换（手动方式）

计划内切换（Switchover）

计划内切换是在维护窗口中进行的有序切换，不丢数据。

bash

# === 步骤一：确认复制无延迟 ===
# 在主库检查
psql -h primary -c "
    SELECT client_addr, state,
           pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
    FROM pg_stat_replication;"
# 确保 lag_bytes 接近 0

# === 步骤二：停止主库接收新连接 ===
# 修改 pg_hba.conf 拒绝新连接，或者在应用层切换

# === 步骤三：确保所有事务完成 ===
psql -h primary -c "
    SELECT count(*) FROM pg_stat_activity
    WHERE state = 'idle in transaction';"

# === 步骤四：在主库执行 checkpoint ===
psql -h primary -c "CHECKPOINT;"

# === 步骤五：关闭主库 ===
pg_ctl stop -D $PGDATA_PRIMARY -m fast

# === 步骤六：确认从库已回放所有 WAL ===
# 从库会自动回放剩余 WAL
psql -h standby -c "SELECT pg_last_wal_replay_lsn();"

# === 步骤七：提升从库为新主库 ===
pg_ctl promote -D $PGDATA_STANDBY
# 或
psql -h standby -c "SELECT pg_promote();"  # PG 12+

# 验证
psql -h standby -c "SELECT pg_is_in_recovery();"  -- 应返回 false

# === 步骤八：将旧主库转为新从库 ===
# 方法一：pg_rewind（快速，仅同步差异数据）
pg_rewind --target-pgdata=$PGDATA_PRIMARY \
          --source-server="host=new-primary port=5432 user=postgres" \
          --progress

# 配置旧主库为从库
cat >> $PGDATA_PRIMARY/postgresql.auto.conf << EOF
primary_conninfo = 'host=new-primary port=5432 user=replicator password=rep_password'
EOF
touch $PGDATA_PRIMARY/standby.signal
pg_ctl start -D $PGDATA_PRIMARY

# 方法二：重新 pg_basebackup（更可靠但更慢）
rm -rf $PGDATA_PRIMARY/*
pg_basebackup -h new-primary -U replicator -D $PGDATA_PRIMARY -Fp -Xs -P -R
pg_ctl start -D $PGDATA_PRIMARY

pg_rewind 详解

bash

# pg_rewind 条件：
# 1. 主库开启了 wal_log_hints = on 或使用了 data checksums
# 2. 旧主库是正常关闭的（或可以找到一致性点）

# 检查 data checksums
pg_controldata $PGDATA | grep checksum

# 创建集群时启用 checksums
initdb --data-checksums -D $PGDATA

# 已有集群启用（需停机）
pg_checksums --enable -D $PGDATA    # PG 12+

# pg_rewind 原理：
# 1. 找到两个集群的分叉点
# 2. 从源集群拷贝分叉后被修改的文件
# 3. 速度快，因为只传输差异数据

10. 主从故障切换（推荐方式）

使用 Patroni 自动故障切换

Patroni 是最流行的 PostgreSQL 高可用方案，基于 DCS（分布式一致性存储）。

Patroni 架构：

  ┌─────────┐     ┌─────────┐     ┌─────────┐
  │ Patroni │     │ Patroni │     │ Patroni │
  │ + PG    │     │ + PG    │     │ + PG    │
  │ Primary │     │ Standby │     │ Standby │
  └────┬────┘     └────┬────┘     └────┬────┘
       │               │               │
       └───────────┬───┘───────────────┘
                   │
            ┌──────┴──────┐
            │  etcd/ZK/   │      ← 分布式一致性存储
            │  Consul     │
            └─────────────┘
                   │
            ┌──────┴──────┐
            │  HAProxy/   │      ← 负载均衡（可选）
            │  pgbouncer  │
            └─────────────┘

Patroni 配置

yaml

# patroni.yml
scope: pg-cluster
namespace: /pg/
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.0.1:8008

etcd3:
  hosts:
    - 10.0.0.10:2379
    - 10.0.0.11:2379
    - 10.0.0.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_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: on
        archive_mode: on
        archive_command: 'cp %p /data/wal_archive/%f'

  initdb:
    - encoding: UTF8
    - data-checksums

  pg_hba:
    - host replication replicator 10.0.0.0/8 scram-sha-256
    - host all all 10.0.0.0/8 scram-sha-256

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

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.0.1:5432
  data_dir: /data/pgdata
  pgpass: /tmp/pgpass0
  authentication:
    replication:
      username: replicator
      password: rep_password
    superuser:
      username: postgres
      password: pg_password
  parameters:
    max_connections: 200
    shared_buffers: 4GB
    work_mem: 64MB

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

Patroni 操作

bash

# 启动 Patroni
patroni /etc/patroni/patroni.yml

# 查看集群状态
patronictl -c /etc/patroni/patroni.yml list
# +--------+----------+---------+---------+----+-----------+
# | Member | Host     | Role    | State   | TL | Lag in MB |
# +--------+----------+---------+---------+----+-----------+
# | node1  | 10.0.0.1 | Leader  | running |  1 |           |
# | node2  | 10.0.0.2 | Replica | running |  1 |         0 |
# | node3  | 10.0.0.3 | Replica | running |  1 |         0 |
# +--------+----------+---------+---------+----+-----------+

# 计划内切换（switchover）
patronictl -c /etc/patroni/patroni.yml switchover
# 交互式选择新主库

# 指定切换
patronictl switchover --master node1 --candidate node2 --force

# 故障切换（failover）
patronictl failover --candidate node2 --force

# 重新初始化节点
patronictl reinit pg-cluster node3

# 编辑动态配置
patronictl edit-config

# 暂停/恢复自动管理
patronictl pause
patronictl resume

# 重启 PostgreSQL（通过 Patroni）
patronictl restart pg-cluster node1

使用 pg_auto_failover

pg_auto_failover 是 Citus 开发的轻量级自动故障切换方案，不依赖外部 DCS。

bash

# 安装
apt-get install postgresql-17-auto-failover

# === Monitor 节点 ===
pg_autoctl create monitor \
    --pgdata /data/monitor \
    --pgport 5000 \
    --auth scram-sha-256 \
    --ssl-mode require

# === 主节点 ===
pg_autoctl create postgres \
    --pgdata /data/pgdata \
    --pgport 5432 \
    --pgctl /usr/lib/postgresql/17/bin/pg_ctl \
    --monitor 'postgres://autoctl_node@monitor-host:5000/pg_auto_failover?sslmode=require' \
    --auth scram-sha-256 \
    --ssl-mode require

# === 从节点 ===
pg_autoctl create postgres \
    --pgdata /data/pgdata \
    --pgport 5432 \
    --pgctl /usr/lib/postgresql/17/bin/pg_ctl \
    --monitor 'postgres://autoctl_node@monitor-host:5000/pg_auto_failover?sslmode=require' \
    --auth scram-sha-256 \
    --ssl-mode require

# 查看状态
pg_autoctl show state

# 手动故障切换
pg_autoctl perform switchover
pg_autoctl perform failover

HAProxy 读写分离

cfg

# haproxy.cfg
global
    maxconn 1000

defaults
    mode tcp
    timeout connect 5s
    timeout client 30s
    timeout server 30s

# 写请求 → 主库
frontend pg_write
    bind *:5432
    default_backend pg_primary

backend pg_primary
    option httpchk GET /primary
    http-check expect status 200
    server node1 10.0.0.1:5432 check port 8008 inter 3s fall 3 rise 2
    server node2 10.0.0.2:5432 check port 8008 inter 3s fall 3 rise 2
    server node3 10.0.0.3:5432 check port 8008 inter 3s fall 3 rise 2

# 读请求 → 从库（负载均衡）
frontend pg_read
    bind *:5433
    default_backend pg_standbys

backend pg_standbys
    option httpchk GET /replica
    http-check expect status 200
    balance roundrobin
    server node1 10.0.0.1:5432 check port 8008 inter 3s fall 3 rise 2
    server node2 10.0.0.2:5432 check port 8008 inter 3s fall 3 rise 2
    server node3 10.0.0.3:5432 check port 8008 inter 3s fall 3 rise 2

bash

# 应用连接方式
# 写操作
psql "host=haproxy-host port=5432 dbname=mydb"

# 读操作（负载均衡到从库）
psql "host=haproxy-host port=5433 dbname=mydb"

小结
逻辑备份灵活，支持跨版本迁移，适合小中型库；自定义格式（-Fc）是最佳选择
WAL 归档是 PITR 和物理恢复的基础，生产环境必须开启
物理备份速度快，pg_basebackup 是标准工具；PG 17 支持增量备份
PITR是误操作恢复的最后防线，务必定期演练
pgloader是跨数据库迁移利器，MySQL → PG 一键完成
流复制实现读写分离和热备，搭建简单
故障切换推荐使用 Patroni 自动化管理，避免手动操作的风险
备份策略核心原则：多种方式互补 + 定期恢复验证 + 异地存储

PostgreSQL 备份与恢复 ​

1. 备份方式介绍 ​

三种备份方式对比 ​

备份策略建议 ​

2. 逻辑备份 — pg_dump ​

基本用法 ​

输出格式对比 ​

常用选项 ​

备份所有数据库 — pg_dumpall ​

逻辑恢复 — pg_restore / psql ​

错误处理 ​

自动化备份脚本 ​

3. WAL 日志归档 ​

WAL 基础 ​

配置 WAL 归档 ​

验证归档 ​

PG 15+ archive_library ​

4. 物理备份 — pg_basebackup ​

基本用法 ​

准备工作 ​

备份格式 ​

PG 15+ 备份压缩增强 ​

PG 17 增量备份 ​

5. 物理备份恢复 ​

Plain 格式恢复 ​

Tar 格式恢复 ​

归档恢复（基础备份 + 归档 WAL） ​

6. PITR（Point-In-Time Recovery） ​

PITR 前提 ​

恢复目标类型 ​

创建还原点 ​

PITR 完整流程 ​

recovery_target_action 选项 ​

PITR 恢复后的时间线 ​

7. 数据迁移 — pgloader ​

安装 ​

MySQL 到 PostgreSQL ​

SQLite 到 PostgreSQL ​

CSV 到 PostgreSQL ​

PostgreSQL COPY（原生高速导入） ​

其他迁移方式 ​

8. 主从异步流复制 ​

架构 ​

主库配置 ​

从库搭建 ​

验证复制 ​

同步复制（可选） ​

9. 主从切换（手动方式） ​

计划内切换（Switchover） ​

pg_rewind 详解 ​

10. 主从故障切换（推荐方式） ​

使用 Patroni 自动故障切换 ​

Patroni 配置 ​

Patroni 操作 ​

使用 pg_auto_failover ​

HAProxy 读写分离 ​

PostgreSQL 备份与恢复

1. 备份方式介绍

三种备份方式对比

备份策略建议

2. 逻辑备份 — pg_dump

基本用法

输出格式对比

常用选项

备份所有数据库 — pg_dumpall

逻辑恢复 — pg_restore / psql

错误处理

自动化备份脚本

3. WAL 日志归档

WAL 基础

配置 WAL 归档

验证归档

PG 15+ archive_library

4. 物理备份 — pg_basebackup

基本用法

准备工作

备份格式

PG 15+ 备份压缩增强

PG 17 增量备份

5. 物理备份恢复

Plain 格式恢复

Tar 格式恢复

归档恢复（基础备份 + 归档 WAL）

6. PITR（Point-In-Time Recovery）

PITR 前提

恢复目标类型

创建还原点

PITR 完整流程

recovery_target_action 选项

PITR 恢复后的时间线

7. 数据迁移 — pgloader

安装

MySQL 到 PostgreSQL

SQLite 到 PostgreSQL

CSV 到 PostgreSQL

PostgreSQL COPY（原生高速导入）

其他迁移方式

8. 主从异步流复制

架构

主库配置

从库搭建

验证复制

同步复制（可选）

9. 主从切换（手动方式）

计划内切换（Switchover）

pg_rewind 详解

10. 主从故障切换（推荐方式）

使用 Patroni 自动故障切换

Patroni 配置

Patroni 操作

使用 pg_auto_failover

HAProxy 读写分离