03 - PostgreSQL 的备份与恢复

更新时间：2025 年 6 月

版本：PostgreSQL 17

简介

参考：PostgreSQL: Documentation: 16: Chapter 26. Backup and Restore

PostgreSQL 备份数据有三种不同的方法，每种方法都有着不同的实现工具

• SQL 转储备份
• 文件系统级别备份
• 连续归档（存档， archiving）备份

对比

注意事项

• 中小型数据库迁移、跨版本升级、按需恢复单表时可以选择 SQL 转储备份。其他情况都应该通过 pg_basebackup + WAL Archive 实现全量+增量的备份，或者直接使用第三方工具
• pg_basebackup 会记录起始与结束位置的 WAL LSN，确保备份点完整性和一致性，所以不用担心两者配合使用会出错
• pg_dump 和 pg_dumpall 不会生成文件系统级备份，所以不能用作连续归档解决方案的一部分，不能代替 tar、pg_basebackup 等其他文件备份命令

SQL 转储备份

参考：PostgreSQL: Documentation: 16: 26.1. SQL Dump

备份思想

使用 SQL 命令生成一个文件，恢复时，将以与转储时相同的状态重新创建数据库

PostgreSQL 提供实用程序 pg_dump、pg_dumpall 用于SQL 转储备份以及恢复，本文介绍其简单的使用方法

优缺点

优点

• 跨版本/跨平台兼容：导出的 SQL 脚本通常可在不同 PostgreSQL 版本或操作系统间恢复
• 灵活选择备份范围：支持备份单个数据库、特定表或全集群（含全局对象如角色）
• 无需停库：备份期间数据库可正常读写（使用默认的 pg_dump 会获取快照点的一致性备份）
• 备份压缩：支持 gzip 或自定义格式压缩（-Fc），节省存储
• 恢复粒度细：可选择性恢复部分表或数据

缺点

• 恢复速度慢：需重新执行 SQL 重建对象 + 插入数据，大数据量时可能需数小时
• 不保证时间点恢复 (PITR)：只能恢复到备份执行时刻的状态
• 备份过程影响性能：大库备份可能消耗大量 I/O 和 CPU
• 不备份物理文件：表空间、配置文件等需额外处理

pg_dump

pg_dump 仅适用于备份单个数据库

备份内容：包括表、视图、触发器、存储过程等单个数据库内部对象，不包括角色、表空间等全局对象

转储输出方式：文件（-f 选项指定）、标准输出（默认，可以使用 > 重定向）、管道（|）

支持格式：支持多种输出格式，如 SQL、custom、directory 等

命令格式

# -d 可以省略
$ pg_dump [OPTION]... [-d] [DBNAME]

常用参数

• 一般参数

  • -f, --file=FILENAME：指定转储后文件或路径

  • -F[c|d|t|p], --format=c|d|t|p：转储文件格式

    • custom：二进制格式，只能用 pg_restore 进行恢复
    • directory：每个文件对应一个数据库对象，只能用 pg_restore 进行恢复
    • tar：输出一个 .tar 归档文件，只能用pg_restore进行恢复
    • plain text（默认）：输出为纯 SQL 文本格式

  • -j, --jobs=NUM：指定转储时并行的工作任务数量

  • -Z, --compress=METHOD[:DETAIL]：指定压缩方法和压缩级别

  • --lock-wait-timeout=TIMEOUT：等待表锁的超时时间

• 连接参数

  • -h, --host=HOSTNAME：指定连接主机或 socket。默认：local socket

    • -p, --port=PORT：指定连接端口。默认：5432

    • -U, --username=NAME：指定连接的角色名。默认：当前操作系统用户名

    • -w, --no-password：指定连接时不使用密码

    • -W, --password：指定连接时使用密码（交互式）

    • -d, --dbname=DBNAME：指定要备份的数据库

    • --role=ROLENAME：在备份之前，使用 SET ROLE 更改为指定角色

• 控制参数

  • -O, --no-owner：备份对象不带有 owner 信息，导入时，owner 信息取决于导入的用户
  • --inserts：备份的数据中，插入数据使用 insert 语句，默认是 COPY ... FROM
  • --column-inserts：类似于 --inserts ，并在插入语句显示指定插入的字段
  • --rows-per-insert=NROWS：与 --inserts 或 --column-inserts 配合使用，指定每个 insert 语句同时插入多少条记录，默认一条
  • -a, --data-only：只备份数据，不备份模式
  • -c, --clean：是否在创建数据库之前先删除数据库，需要与 -C 一起使用才有作用
  • -C, --create：转储的文件中是否包含创建数据库的命令，默认不包含。注意恢复时与 psql 的 --single-transaction 选项不兼容
  • -e, --extension=PATTERN：只备份匹配的扩展（extension）对象
  • -E, --encoding=ENCODING：指定备份数据的编码格式
  • -n, --schema=PATTERN：只备份匹配的模式（schema）
  • -N, --exclude-schema=PATTERN：不备份匹配的模式（schema）
  • -s, --schema-only：只备份模式，不备份数据
  • -S, --superuser=NAME：指定在纯文本（plain-text）格式备份中使用的超级用户（superuser）名称
  • -t, --table=PATTERN：只备份匹配的数据表（table），可以指定多个 -t
  • -T, --exclude-table=PATTERN：不备份匹配的数据表（table）
  • --exclude-table-and-children=PATTERN：不备份匹配的表和其所有子表（包括分区表）
  • --table-and-children=PATTERN：只备份匹配的表和其所有子表（包括分区表）

注意事项

pg_dump 生成的转储是相对于 template0 这个模板数据库的，意味着通过 template1 添加的任何修改（语言、存储过程等）也将被 pg_dump 转储。因此，在恢复时，如果使用自定义的 template1 ，则必须从 template0 创建空数据库

备份恢复

恢复时可以使用 psql 命令，也可以使用 pg_restore

psql（常用）

• 格式支持：仅 SQL 格式的备份文件（-Fp 或者 --format=plain）
• 版本兼容：支持跨版本恢复
• 功能：没有其他功能

pg_restore

• 格式支持：不支持 SQL 格式，支持目录格式（-Fd）、TAR 包格式（-Ft）等其他格式备份文件
• 版本兼容：不支持跨版本，生成备份的 PostgreSQL 数据库版本紧密相关
• 功能：
  • 支持恢复单个表、库、模式等
  • 支持并行恢复
  • 可以显示恢复进度

示例 - 备份

准备

# 创建备份存放目录
mkdir -p /data/backup/pg

# 授权
chown -R postgres:postgres /data/backup/pg

01-备份单个表

# 备份 pagila 数据库的 category 表
$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -W -d pagila -t category > /data/backup/pg/pagila.category.sql


# 如果要同时备份多个表可以指定多个 -t 选项
$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -W -d pagila -t category -t country > /data/backup/pg/pagila.multi-table.sql

02-备份整个库

# 备份整个库，输出到 /data/backup/pg/pagila.sql
$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -W -d pagila > /data/backup/pg/pagila.sql

03-备份整个库（包含数据库 DDL）

# 备份，包含数据库删除和重新创建命令
# 注意：创建数据库完成，切换数据库时需要输入密码
$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -W -d pagila -c -C > /data/backup/pg/pagila-cc.sql

04-备份并压缩

# 备份并压缩
$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -W -d pagila -c -C | gzip > /data/backup/pg/pagila-cc.gz

示例 - 恢复

注：如果有连接到目标的会话，可以使用命令先终止连接

$ psql -h 127.0.0.1 -p 5432 -U postgres -c \
    "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'pagila';"

01-恢复单个表

# 恢复前删除表
$ psql -h 127.0.0.1 -p 5432 -U postgres -d pagila -c "DROP TABLE IF EXISTS public.category CASCADE;"

$ psql -h 127.0.0.1 -p 5432 -U postgres -d pagila < /data/backup/pg/pagila.category.sql

02-恢复整个库（手动创建数据库）

# 创建数据库
$ psql -h 127.0.0.1 -p 5432 -U postgres -W -c "CREATE DATABASE pagila"

# 恢复，使用 --single-transaction 和 --set ON_ERROR_STOP=on 保证错误时回滚
# 注意：使用了 --single-transaction 就不能在 SQL 文件中出现 CREATE DATABASE 语句，所以与 pg_dump -c -C 选项不兼容 
$ psql -h 127.0.0.1 -p 5432 -U postgres -W -d pagila --single-transaction --set ON_ERROR_STOP=on < /data/backup/pg/pagila.sql

03-恢复整个库（无需手动创建数据库，发生错误时回滚）

$ psql -h 127.0.0.1 -p 5432 -U postgres -W  < /data/backup/pg/pagila-cc.sql

04-恢复压缩文件

# 恢复压缩后的备份文件，发生错误时回滚（单事务）
$ gunzip -c /data/backup/pg/pagila-cc.gz | psql -h 127.0.0.1 -p 5432 -U postgres -W --set ON_ERROR_STOP=on 

pg_dumpall

备份内容：备份 PostgreSQL 实例中的所有数据库，包括角色和表空间等全局对象

输出格式：只支持纯 SQL 文本格式

命令格式

$ pg_dumpall [OPTION]...

常用参数

• 一般参数

  • -f, --file=FILENAME：指定转储后文件或路径
  • --lock-wait-timeout=TIMEOUT：等待表锁的超时时间

• 连接参数

  • -h, --host=HOSTNAME：指定连接主机或 socket。默认：local socket

  • -p, --port=PORT：指定连接端口。默认：5432

  • -U, --username=NAME：指定连接的角色名。默认：当前操作系统用户名

  • -w, --no-password：指定连接时不使用密码

  • -W, --password：指定连接时使用密码（交互式）

  • -d, --dbname=DBNAME：指定要备份的数据库

  • --role=ROLENAME：在备份之前，使用 SET ROLE 更改为指定角色

• 控制参数

  • -a, --data-only：只备份数据，不备份模式
  • -c, --clean：是否在创建数据库之前先删除数据库
  • -E, --encoding=ENCODING：指定备份数据的编码格式
  • -g, --globals-only：只备份全局对象，不备份数据库
  • -O, --no-owner：备份对象不带有 owner 信息，导入时，owner 信息取决于导入的用户
  • -r, --roles-only：只备份角色，不备份数据库和表空间信息
  • -s, --schema-only：只备份模式，不备份数据
  • -t, --tablespaces-only：只备份表空间，不备份数据库和角色信息
  • --column-inserts：类似于 --inserts ，并在插入语句显示指定插入的字段
  • --inserts ：备份的数据中，插入数据使用 insert 语句，默认是 COPY ... FROM
  • --rows-per-insert=NROWS：与 --inserts 或 --column-inserts 配合使用，指定每个 insert 语句同时插入多少条记录，默认一条
  • --exclude-database=PATTERN：不备份匹配的数据库

备份恢复

仅支持 psql 导入

示例 - 备份

准备

# 创建备份存放目录
mkdir -p /data/backup/pg

# 授权
chown -R postgres:postgres /data/backup/pg

备份全部对象

# 注意：备份过程中需要多次输入密码，因为需要切换连接不同数据库
$ pg_dumpall -h 127.0.0.1 -p 5432 -U postgres -W -c > /data/backup/pg/all-obj.sql

备份全部对象并压缩

# 备份并压缩
$ pg_dumpall -h 127.0.0.1 -p 5432 -U postgres -W -c | gzip > /data/backup/pg/all-obj.gz

只备份全局对象，不备份数据库

$ pg_dumpall -h 127.0.0.1 -p 5432 -U postgres -W -g > /data/backup/pg/global-obj.sql

示例 - 恢复

恢复前停止所有连接

psql -h 127.0.0.1 -p 5432 -U postgres -d postgres -c \
    "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state = 'active';"

恢复全部对象

$ psql -h 127.0.0.1 -p 5432 -U postgres -W < /data/backup/pg/all-obj.sql

恢复压缩对象

$ gunzip -c /data/backup/pg/all-obj.gz | psql -h 127.0.0.1 -p 5432 -U postgres -W 

文件系统级别备份

参考：PostgreSQL: Documentation: 16: 26.2. File System Level Backup

备份思想：直接复制 PostgreSQL 用于数据存储的文件，备份这部分文件

方法：

• 冷备份：关闭数据库后复制文件（简单但需停服）
• 热备份：运行时复制文件，但需配合以下机制保证一致性：
  • 使用 pg_basebackup 工具（官方推荐）
  • 手动执行 CHECKPOINT + pg_start_backup() → 复制文件 → pg_stop_backup()

优点：

• 备份速度快：文件级复制（尤其 pg_basebackup 并行流复制）
• 恢复速度快：直接替换文件即可恢复
• 支持 PITR：若同时开启 WAL 归档，可实现精确到秒级的时间点恢复
• 全量备份：包含所有数据库、表空间、配置文件

缺点：

• 备份文件大：占用与原数据目录相近的磁盘空间
• 复杂度高：热备份需严格协调 WAL 归档（否则备份无效）
• 平台绑定：备份文件通常不能跨操作系统或大版本恢复
• 需独占资源：pg_basebackup 可能影响主库性能（建议从副本备份）

其他文件系统级别备份

• 一致性快照
• rsync
• 第三方工具，例如：Percona Distribution for PostgreSQL

连续归档备份和 PITR

参考：PostgreSQL: Documentation: 16: 26.3. Continuous Archiving and Point-in-Time Recovery (PITR)

WAL 日志

PostgreSQL 始终会在数据目录的 pg_wal/ 子目录中维护一个预写日志 （WAL）。预写日志（WAL）记录了对数据库数据文件所做的每次更改（类似于 MySQL 的 binlog），该日志主要是为了崩溃恢复而设计：

• 当 PostgreSQL 实例崩溃时，可以通过 replaying 自上次 checkpoint 以来创建的日志记录，以达到数据一致性的目的

正是因为 WAL 日志的存在，使用 WAL 连续存档来备份数据库成为了可能

备份思想

备份思想

将文件系统级备份与 WAL 文件的备份相结合。需要恢复时，先还原文件系统备份，然后从备份的 WAL 文件中 replaying 以使系统进入想要达到的一致状态

优缺点

优点

• 零数据丢失：结合 WAL 归档，支持恢复到任意时间点（如误删数据前
• 增量式备份：仅需首次全量备份，后续持续归档 WAL（占用空间小）
• 支持指定时间点恢复：指定时间点恢复时，无需将 WAL 条目一直 replaying 到最后，在指定时间点停止 replaying 即可
• 高可用：可以实现暖备用（warm standby）系统，连续将一系列 WAL 文件发送到另一台加载了相同基本备份文件的实例，在该实例 replaying 即可
• 备份不锁库：基础备份和 WAL 归档对主库影响极小

缺点

• 配置复杂：需设置 archive_mode、archive_command 并管理 WAL 存储
• 恢复流程复杂：需手动配置 recovery.signal 和 PITR 目标时间。
• 存储成本：长期保留 WAL 文件占用额外空间（需定期清理）。
• 依赖基础备份：损坏的基础备份会导致整个备份链失效
• 可操作性差：只支持备份全部数据库，无法只备份部分数据库或表

设置 WAL 存档

运行中的 PostgreSQL 实例会产生无限的 WAL 记录序列。WAL 记录序列会被分割成 WAL 段文件，每个段文件通常为 16MB（可以在 initdb 时进行修改）。每个段文件都有一个序列号作为文件名，该序列号可以反应文件在 WAL 序列中的位置

• 当不启用 WAL 数据存档时，PostgreSQL 通常只创建几个段文件，然后通过将不再需要的段文件重命名为更高的段号来 “回收” 它们
• 当启用 WAL 数据存档时，需要捕获每个段文件的内容，并在回收段文件之前将该数据保存在某个地方，以达到备份的目的

注： 归档的 WAL 的过期清理只能手动清理，可以通过判断 WAL 文件的修改时间（mtime）进行清理

启用 WAL 存档

• wal_level 配置为 replica 或者 logical
• archive_mode 配置为 on

存档方式

• archive_command：使用操作系统指令来存档，配置简单，性能较差
• archive_library：使用 C 语言编写的共享库进行存档，配置复杂，性能较高

存档超时

• archive_timeout：备份超时时间，一般设置为一分钟

archive_command（推荐）

指定如何归档已完成的 WAL（Write-Ahead Logging）文件段的本地 shell 命令。该命令在 archive_mode 设置为 on 或 always 时生效

占位符

• %p：会被替换为需要归档的文件的路径
• %f：会被替换为需要归档的文件的文件名
• %%：需要在命令中使用 % 字符时，使用 %%

返回状态

• 当 archive 命令执行成功时，返回 0 退出状态。PostgreSQL 将认为该文件已成功存档，可以将其删除或回收
• 当 archive 命令执行失败时，返回非 0 退出状态。PostgreSQL 将认为该文件未成功存档，会进行定期重试

示例

wal_level = replica
archive_mode = on

# 仅适用于类 Unix 系统
# 在备份之前先使用 test 判断目的路径是否已经存在归档文件
archive_command = '/usr/bin/test ! -f /data/backup/pg/archive/%f && /usr/bin/cp %p /data/backup/pg/archive/%f'

archive_timeout = 60

注：archive_command 可使用 rsync、scp 推送远程安全存储

archive_library（不推荐）

需要自定义编写 C 语言的共享库，较少使用

制作基础备份

基础备份方式

• pg_basebackup 命令：操作简单，但是灵活性较差
• Low Level API：操作复杂，灵活性更好

pg_basebackup（推荐）

命令格式

$ pg_basebackup [OPTION]...

常用参数

• 连接参数

  • -h, --host=HOSTNAME：指定连接主机或 socket。默认：local socket

  • -p, --port=PORT：指定连接端口。默认：5432

  • -U, --username=NAME：指定连接的角色名。默认：当前操作系统用户名

  • -w, --no-password：指定连接时不使用密码

  • -W, --password：指定连接时使用密码（交互式）

  • -d, --dbname=DBNAME：指定要备份的数据库

• 一般参数

  • -c, --checkpoint=fast|spread：设置 checkpoint 模式为 fast （快速）或者 spread（均匀分布，默认）

  • -C, --create-slot：指定在开始备份之前创建指定命名的复制槽。如果插槽已存在，返回错误

  • -l, --label=LABEL：设置备份标签，如果未指定，这使用默认值 pg_basebackup base backup

  • -n, --no-clean：默认情况下，当中止并出现错误，会删除之前已创建的任何目录。该选项可以禁用删除，方便调试

  • -N, --no-sync： pg_basebackup 不等待所有文件安全写入磁盘，可能会出现备份损坏。生产不推荐使用

  • -P, --progress：启用备份的大致进度报告

  • -S, --slot=SLOTNAME：（只能与 -X stream 使用）使用指定的复制槽进行 WAL 流处理

    注意：如果基础备份打算用作使用复制槽的流复制备用备份，则备用备份应使用与 primary_slot_name 相同的复制槽名称

• 控制参数

  • -D, --pgdata=DIRECTORY（必须）：指定输出的目录，如果目录不存在，pg_basebackup 将自动创建目录

  • -F, --format=p|t：选择输出的格式

    • p (plain)：默认格式，输出写入为纯文件，其布局与源服务器的数据目录和表空间相同

    • t (tar)：将输出作为 tar 文件写入目标目录中，注：wal 日志会单独打包和其他文件分开

  • -r, --max-rate=RATE：（仅当收集方式为 -Xfetch 时有效）设置 pg_basebackup 备份数据的最大传输速率，默认单位 KB

  • -R, --write-recovery-conf：简化了使用备份结果设置备用服务器的过程。创建一个 standby.signal 文件，并将连接设置追加到 postgresql.auto.conf 文件

  • -t, --target=TARGET[:DETAIL]：（必须指定收集方式 -Xfetch 或 -Xnone）指定备份应该放置在那个位置

    • client（默认）：将备份存储在运行 pg_basebackup 命令的主机
    • server:/some/path：将备份存储在备份实例主机的指定目录，注意需要对指定目录有写入权限
    • blackhole：仅测试，不会存储备份

  • -T, --tablespace-mapping=OLDDIR=NEWDIR：在备份期间将目录中 OLDDIR 的表空间重新定位到 NEWDIR

  • --waldir=WALDIR：（仅当输出纯文件格式 -Fp 时有效）设置要将 WAL（预写日志）文件写入到的目录，默认为指定目录的 pg_wal 子目录

  • -X, --wal-method=none|fetch|stream：在备份中包含所需的 WAL（预写日志）文件的方式

    • none：不包含预写日志
    • fetch：预写日志文件在备份结束时收集。注意：wal_keep_size 参数需要设置得足够高，以便在备份结束之前不会删除所需的日志数据
    • stream（默认，推荐）：在进行备份时流式传输预写日志数据

  • -z, --gzip：（当使用 tar 输出格式时有效）使用 gzip 压缩，使用默认的压缩级别

示例

创建基础备份

# 创建基础备份，并保存到 /data/backup/pg/data
$ pg_basebackup -h 127.0.0.1 -p 5432 -U postgres -W -D /data/backup/pg/unpack

创建基础备份并压缩

# 创建基础备份并压缩，输出报告
$ pg_basebackup -h 127.0.0.1 -p 5432 -U postgres -W -D /data/backup/pg/pack -Ft -z -P

远程进行基础备份

# 远程运行基础备份，并且在设置复制的连接信息
# 注意需要在 pg_hba.conf 中添加 replication 权限

# 压缩
$ pg_basebackup -h 192.168.111.196 -p 5432 -U postgres -W -D /data/backup/replica/pack -R -Ft -z -P

# 不压缩
$ pg_basebackup -h 192.168.111.196 -p 5432 -U postgres -W -D /data/backup/replica/unpack -R -P

Low Level API（不推荐）

Low Level API 方式必须严格遵守以下步骤执行，否则会出现数据错误

确认

确认 WAL 存档已启用且正常工作

$ grep '^archive' /data/pgsql/16/postgresql.conf

开始备份模式

执行以下命令（必须是超级管理员或已授予 EXECUTE 权限的角色）

postgres=# SELECT pg_backup_start(label => 'my_back', fast => false);
 pg_backup_start 
-----------------
 0/18000028
(1 row)

label 可以任意指定，如果同时有多个备份进程，label 不能相同

文件系统备份

使用文件系统备份工具（如 tar 或 cpio）对数据文件进行备份

$ tar -cvf data.tar.gz -C /data/pgsql/16/ .

可以省略目录 pg_dynshmem/ 、 pg_notify/ pg_serial/ pg_snapshots/ pg_stat_tmp/ 和 pg_subtrans/ 下的内容，但不能省略目录本身

终止备份模式

postgres=# SELECT * FROM pg_backup_stop(wait_for_archive => true);
NOTICE:  all required WAL segments have been archived
    lsn     |                           labelfile                            | spcmapfile 
------------+----------------------------------------------------------------+------------
 0/18000138 | START WAL LOCATION: 0/18000028 (file 000000010000000000000018)+| 
            | CHECKPOINT LOCATION: 0/18000060                               +| 
            | BACKUP METHOD: streamed                                       +| 
            | BACKUP FROM: primary                                          +| 
            | START TIME: 2023-10-18 15:07:36 CST                           +| 
            | LABEL: my_back                                                +| 
            | START TIMELINE: 1                                             +| 
            |                                                                | 
(1 row)

pg_backup_stop 将返回三个字段。其中第二个字段应写入备份根目录中命名 backup_label 的文件。第三个字段应写入名为 tablespace_map 的文件，除非该字段为空

PITR（基于时间点恢复）

PostgreSQL 通过一些恢复参数可以达到 PITR 的目的

相关参数

在 postgresql.conf 中设置

recovery_target_time = '2023-10-21 07:59:59+08'
recovery_target_timeline = latest
recovery_target_action = shutdown

更多参数请参考 WAL 参数：PostgreSQL: Documentation: 16: 20.5. Write Ahead Log

recovery_target_action

recovery_target_action 是 PostgreSQL 在恢复到指定恢复点（例如时间点、事务 ID 或恢复标记）后，控制数据库接下来的行为的关键参数。该参数用于 PITR（时间点恢复）、WAL 重放恢复、standby promotion（从库提升） 等场景

示例 - 连续存档备份

准备

创建目录

注：此处直接在本地备份，生产环境推荐使用 rsync 远程备份，或者 nfs 挂载共享目录

BACKUP_BASE_DIR=/data/backup/pg

# 归档目录
ARCHIVE_DIR=${BACKUP_BASE_DIR}/archive

# 基础备份目录
BASEBACKUP_DIR=${BACKUP_BASE_DIR}/base


mkdir -p ${ARCHIVE_DIR} ${BASEBACKUP_DIR}

授权

chown -R postgres:postgres ${BASE_DIR} ${ARCHIVE_DIR} ${BASEBACKUP_DIR}

设置归档

修改配置

$ vim ${PGDATA}/postgresql.conf


wal_level = replica
max_wal_senders = 3
wal_keep_size = 128MB

archive_mode = on
archive_timeout = 60

# 仅适用于类 Unix 系统
# 在备份之前先使用 test 判断目的路径是否已经存在归档文件
archive_command = '/usr/bin/test ! -f /data/backup/pg/archive/%f && /usr/bin/cp %p /data/backup/pg/archive/%f'

重启 PostgreSQL

部分 wal、archive 参数需要重启才能生效

systemctl restart postgresql-${PG_MAJOR_VERSION}

设置基础备份

执行 pg_basebackup

sudo -i -u postgres  \
    pg_basebackup -h 127.0.0.1 -p 5432 -U postgres -W -D /data/backup/pg/base/$(date +'%Y%m%d-%H%M') -Ft -z -P

示例 - 连续存档恢复

恢复前可以模拟数据变更

停止服务器

$ systemctl stop postgresql-${PG_MAJOR_VERSION}

迁移当前数据目录和表空间

$ mv /data/pgsql/${PG_MAJOR_VERSION} /data/pgsql/${PG_MAJOR_VERSION}-bak

还原数据目录和表空间

# 基础备份时间
BASEBACKUP_TIME="20250610-1408"

# 创建目录
$ mkdir ${PGDATA}

# 还原数据
$ tar -zxvf ${BASEBACKUP_DIR}/${BASEBACKUP_TIME}/base.tar.gz \
    -C ${PGDATA}

# 还原 WAL 
$ tar -zxvf ${BASEBACKUP_DIR}/${BASEBACKUP_TIME}/pg_wal.tar.gz \
    -C ${PGDATA}/pg_wal

设置恢复配置

创建 recovery.signal

# 在数据目录中创建 recovery.signal，作为 PostgreSQL 12+ 识别的恢复标志
$ echo "" > ${PGDATA}/recovery.signal

设置恢复配置

# 设置 restore_command 
$ vim ${PGDATA}/postgresql.conf
restore_command = '/usr/bin/cp /data/backup/pg/archive/%f %p'

recovery_target_time = '2025-06-10 14:30:00+08'

recovery_target_timeline = latest

# 此处使用 promote，达到目标后自动结束恢复，删除 recovery.signal
recovery_target_action = promote

授权

# 权限设置
chown -R postgres:postgres ${PGDATA}

chmod 750 ${PGDATA}

启动服务器（触发恢复）

$ systemctl start postgresql-${PG_MAJOR_VERSION}

查看日志

journalctl -u postgresql-${PG_MAJOR_VERSION}

# 输出如下日志
Jun 10 15:25:47 db-01.postgresql.local systemd[1]: Starting PostgreSQL 17 database server...
Jun 10 15:25:47 db-01.postgresql.local postgres[42564]: 2025-06-10 15:25:47.556 CST [42564] LOG:  starting PostgreSQL 17.5 on x86_64-pc-linux-gnu, co
mpiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
Jun 10 15:25:47 db-01.postgresql.local postgres[42564]: 2025-06-10 15:25:47.557 CST [42564] LOG:  listening on IPv4 address "0.0.0.0", port 5432
Jun 10 15:25:47 db-01.postgresql.local postgres[42564]: 2025-06-10 15:25:47.557 CST [42564] LOG:  listening on IPv6 address "::", port 5432
Jun 10 15:25:47 db-01.postgresql.local postgres[42564]: 2025-06-10 15:25:47.559 CST [42564] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.567 CST [42567] LOG:  database system was interrupted; last known up at 2
025-06-10 14:08:34 CST
Jun 10 15:25:47 db-01.postgresql.local postgres[42568]: /usr/bin/cp: cannot stat '/data/backup/pg/archive/00000002.history': No such file or director
y
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.613 CST [42567] LOG:  starting backup recovery with redo LSN 0/4000028, c
heckpoint LSN 0/4000080, on timeline ID 1
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.618 CST [42567] LOG:  restored log file "000000010000000000000004" from a
rchive
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.624 CST [42567] LOG:  starting point-in-time recovery to 2025-06-10 14:30
:00+08
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.626 CST [42567] LOG:  redo starts at 0/4000028
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.633 CST [42567] LOG:  restored log file "000000010000000000000005" from a
rchive
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.639 CST [42567] LOG:  restored log file "000000010000000000000006" from a
rchive
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.641 CST [42567] LOG:  completed backup recovery with redo LSN 0/4000028 a
nd end LSN 0/4000120
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.641 CST [42567] LOG:  consistent recovery state reached at 0/4000120
Jun 10 15:25:47 db-01.postgresql.local postgres[42564]: 2025-06-10 15:25:47.641 CST [42564] LOG:  database system is ready to accept read-only connec
tions
Jun 10 15:25:47 db-01.postgresql.local systemd[1]: Started PostgreSQL 17 database server.
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.649 CST [42567] LOG:  recovery stopping before commit of transaction 1048
, time 2025-06-10 14:47:45.283186+08
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.649 CST [42567] LOG:  redo done at 0/6045048 system usage: CPU: user: 0.0
0 s, system: 0.00 s, elapsed: 0.02 s
Jun 10 15:25:47 db-01.postgresql.local postgres[42572]: /usr/bin/cp: cannot stat '/data/backup/pg/archive/00000002.history': No such file or director
y
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.657 CST [42567] LOG:  selected new timeline ID: 2
Jun 10 15:25:47 db-01.postgresql.local postgres[42573]: /usr/bin/cp: cannot stat '/data/backup/pg/archive/00000001.history': No such file or director
y
Jun 10 15:25:47 db-01.postgresql.local postgres[42567]: 2025-06-10 15:25:47.771 CST [42567] LOG:  archive recovery complete
Jun 10 15:25:47 db-01.postgresql.local postgres[42565]: 2025-06-10 15:25:47.772 CST [42565] LOG:  checkpoint starting: end-of-recovery immediate wait
Jun 10 15:25:47 db-01.postgresql.local postgres[42565]: 2025-06-10 15:25:47.778 CST [42565] LOG:  checkpoint complete: wrote 41 buffers (0.3%); 0 WAL
 file(s) added, 0 removed, 0 recycled; write=0.004 s, sync=0.002 s, total=0.007 s; sync files=16, longest=0.001 s, average=0.001 s; distance=33044 kB
, estimate=33044 kB; lsn=0/6045048, redo lsn=0/6045048
Jun 10 15:25:47 db-01.postgresql.local postgres[42564]: 2025-06-10 15:25:47.782 CST [42564] LOG:  database system is ready to accept connections

删除标识

如果 recovery_target_action 为 pause 或者 shutdown，则需要手动删除标识

rm -rf  ${PGDATA}/recovery.signal

重启服务

如果 recovery_target_action 为 pause 或者 shutdown，则需要手动重启服务

systemctl restart postgresql-${PG_MAJOR_VERSION}