Skip to main content
Septvean's Documents
Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage
  1. Documents
  2. /
  3. Database
  4. /
  5. PostgreSQL
  6. /
  7. PostgreSQL 备份恢复

PostgreSQL 备份恢复

PostgreSQL 的备份方案通常不是“选一个工具”，而是组合使用：

  • pg_dump：解决单库、单表恢复和跨版本迁移。
  • pg_basebackup：解决整个实例的快速恢复。
  • 基础备份 + WAL 归档：解决时间点恢复，即 PITR。
  • 定期恢复演练：确认备份真的可用。

PostgreSQL 官方将备份分为 SQL 转储、文件系统备份、连续 WAL 归档三类。PostgreSQL 18 备份文档

一、先理解 RPO 与 RTO

指标 含义 示例
RPO 最多允许丢失多少数据 RPO 5 分钟：灾难时最多丢 5 分钟数据
RTO 最长允许多久恢复服务 RTO 30 分钟：必须在 30 分钟内恢复
保留期 能恢复到多早以前 保留 30 天：可恢复最近 30 天状态

不同备份方式的能力：

方式 工具 备份粒度 PITR 跨大版本 适用场景
逻辑备份 pg_dump 库、Schema、表 否 较好 日常备份、迁移、单表恢复
全实例逻辑备份 pg_dumpall 整个实例 否 较好 角色、表空间、多个数据库
物理基础备份 pg_basebackup 整个实例 配合 WAL 否 大型生产库快速恢复
连续归档 基础备份 + WAL 整个实例 是 否 生产灾备、误操作恢复
冷备份 停库后复制文件 整个实例 否 否 简单离线场景

这里的“实例/数据库集群”指一个 PostgreSQL 数据目录，其中可以包含多个数据库。

二、逻辑备份：pg_dump

1. 推荐使用自定义格式 

pg_dump \
  -h 127.0.0.1 \
  -p 5432 \
  -U backup_user \
  -d ashare \
  -Fc \
  --verbose \
  -f /srv/pgbackup/ashare_20260914.dump

主要参数：

参数 作用
-d ashare 备份的数据库
-Fc custom 自定义格式
-f 输出文件
--verbose 输出详细过程
-h/-p/-U 主机、端口、用户

自定义格式支持压缩、选择性恢复和并行恢复，通常比普通 SQL 文件更实用。

pg_dump 获得的是备份开始时的一致性快照，通常不会阻塞普通查询和写入，但可能与需要排他锁的 ALTER TABLE 等操作发生冲突。pg_dump 官方说明

2. 大库并行备份

并行备份只能使用目录格式：

pg_dump \
  -h 127.0.0.1 \
  -U backup_user \
  -d ashare \
  -Fd \
  --jobs=4 \
  -f /srv/pgbackup/ashare_20260914.dir

--jobs=4 表示同时使用 4 个工作进程，并会建立 5 个数据库连接，即工作进程数加一个主连接。

并行度越高不一定越快，还要考虑磁盘吞吐、CPU、网络和生产库负载。

3. 备份指定 Schema 

pg_dump \
  -U backup_user \
  -d ashare \
  -Fc \
  --schema=market \
  -f market_schema.dump

4. 备份指定表 

pg_dump \
  -U backup_user \
  -d ashare \
  -Fc \
  --table='market.daily_quote' \
  -f daily_quote.dump

仅备份结构：

pg_dump -U backup_user -d ashare \
  --schema-only \
  -f ashare_schema.sql

仅备份数据：

pg_dump -U backup_user -d ashare \
  --data-only \
  --table='market.daily_quote' \
  -Fc \
  -f daily_quote_data.dump

单表备份可能不包含它所依赖的扩展、类型、函数、其他表和部分关联对象，因此不能保证直接恢复到一个全空数据库。

5. 备份角色和表空间

pg_dump 只备份一个数据库，不备份角色和表空间等实例级对象。需要额外执行：

pg_dumpall \
  -h 127.0.0.1 \
  -U postgres \
  --globals-only \
  -f globals_20260914.sql

globals 文件通常包含：

  • 用户与角色
  • 角色成员关系
  • 表空间定义
  • 部分实例级权限

如果分别使用 pg_dump 备份各数据库，必须同时保存 pg_dumpall --globals-only。需要注意，pg_dumpall 中每个数据库分别一致，但不同数据库之间的快照并不处于完全相同的时间点。SQL Dump 官方说明

三、逻辑恢复：pg_restore

1. 查看备份内容 

pg_restore --list /srv/pgbackup/ashare_20260914.dump

可以先导出目录清单：

pg_restore --list ashare_20260914.dump > restore.list

编辑 restore.list 后，只恢复选中的对象：

pg_restore \
  --dbname=ashare_restore \
  --use-list=restore.list \
  ashare_20260914.dump

2. 恢复到新数据库

如果需要保留原来的对象所有者，应先创建相关角色。恢复到新实例时，可以先恢复全局对象：

psql \
  -X \
  --set ON_ERROR_STOP=on \
  -U postgres \
  -d postgres \
  -f globals_20260914.sql

创建空数据库：

createdb \
  -U postgres \
  -T template0 \
  -O ashare_owner \
  ashare_restore

恢复：

pg_restore \
  -U postgres \
  -d ashare_restore \
  --jobs=4 \
  --exit-on-error \
  --verbose \
  ashare_20260914.dump

pg_restore 默认遇到错误后继续执行，只在最后显示错误数量，所以生产恢复建议使用 --exit-on-error。并行恢复只支持 custom 和 directory 格式，且不能与 --single-transaction 同时使用。pg_restore 官方说明

3. 不保留原所有者和权限

开发、测试环境经常使用：

pg_restore \
  -U postgres \
  -d ashare_restore \
  --no-owner \
  --no-acl \
  --exit-on-error \
  ashare_20260914.dump

这会产生两个变化：

  • 所有对象归执行恢复的用户所有。
  • 原来的 GRANT、REVOKE 不会恢复。

不能在生产环境中不加分析地使用，因为所有权变化可能影响权限管理和 SECURITY DEFINER 函数。

4. 覆盖原数据库 

pg_restore \
  -U postgres \
  -d postgres \
  --clean \
  --if-exists \
  --create \
  --exit-on-error \
  ashare_20260914.dump

注意：

  • --clean 会删除将要恢复的对象。
  • --create 会按照备份内记录的数据库名称创建数据库。
  • 两者组合可能直接删除并重建已有数据库。
  • 执行前应确认目标主机、端口和数据库，避免误删生产库。

5. 恢复指定表 

pg_restore \
  -U postgres \
  -d ashare_restore \
  --schema=market \
  --table=daily_quote \
  ashare_20260914.dump

这不会自动恢复该表依赖的全部对象。如果目标库已经有正确结构，可以只恢复数据：

pg_restore \
  -U postgres \
  -d ashare_restore \
  --data-only \
  --schema=market \
  --table=daily_quote \
  ashare_20260914.dump

6. 恢复普通 SQL 文件

备份：

pg_dump -U backup_user -d ashare > ashare.sql

恢复：

createdb -U postgres -T template0 ashare_restore

psql \
  -X \
  --set ON_ERROR_STOP=on \
  --single-transaction \
  -U postgres \
  -d ashare_restore \
  -f ashare.sql

大型数据库是否使用 --single-transaction 需要权衡：它能保证全部成功或全部回滚，但最后一个小错误也可能回滚数小时的恢复结果。

恢复完成后更新统计信息：

ANALYZE;

四、工具版本兼容性

建议备份端使用与服务器相同大版本的客户端工具：

pg_dump --version
psql --version
pg_restore --version

主要规则：

  • PostgreSQL 18 的 pg_dump 可以备份 PostgreSQL 18 或较旧服务器。
  • PostgreSQL 17 的 pg_dump 不能备份 PostgreSQL 18。
  • 逻辑备份通常可以恢复到更高大版本。
  • 不保证能够恢复到更低大版本。
  • 物理备份只能用于相同 PostgreSQL 大版本及兼容环境。

跨版本迁移前还要确认所有扩展在目标版本上可用。pg_dump 版本兼容说明

五、物理备份：pg_basebackup

pg_basebackup 在线复制整个 PostgreSQL 实例，包括所有数据库，但不能只备份某一个数据库或表。

备份连接用户需要 REPLICATION 权限，并且 pg_hba.conf 必须允许复制连接。

CREATE ROLE backup_repl
    LOGIN
    REPLICATION;

密码建议通过受限权限的 .pgpass、证书或密钥系统管理，不要直接写在命令行中。

执行完整物理备份：

pg_basebackup \
  -h 10.0.0.10 \
  -p 5432 \
  -U backup_repl \
  -D /srv/pgbackup/base/2026-09-14 \
  --format=plain \
  --wal-method=stream \
  --progress \
  --verbose \
  --manifest-checksums=SHA256

说明：

  • 目标目录必须不存在或为空。
  • --wal-method=stream 会使用第二个复制连接同步收集备份期间需要的 WAL。
  • 它只保证基础备份自身完整，不代表未来 WAL 已归档。
  • 如果存在额外表空间，应使用 --tablespace-mapping 正确映射。
  • 物理备份不会备份安装在数据目录之外的 PostgreSQL 程序、TimescaleDB 动态库以及外置配置文件。

详细限制参见 pg_basebackup 官方文档。

验证物理备份 

pg_verifybackup /srv/pgbackup/base/2026-09-14

它可以检查：

  • backup_manifest
  • 文件是否缺失或多余
  • 文件校验和
  • 普通格式备份所需 WAL 是否可以解析

但通过 pg_verifybackup 不等于一定能成功启动数据库。官方明确建议继续执行真实恢复测试。pg_verifybackup 官方文档

六、PostgreSQL 18 增量物理备份

增量备份需要启用 WAL 摘要：

wal_level = replica
summarize_wal = on
wal_summary_keep_time = '14d'

首先建立完整备份：

pg_basebackup \
  -h 10.0.0.10 \
  -U backup_repl \
  -D /srv/pgbackup/full-20260901 \
  -X stream \
  --manifest-checksums=SHA256

随后引用上一次备份的 manifest：

pg_basebackup \
  -h 10.0.0.10 \
  -U backup_repl \
  -D /srv/pgbackup/incr-20260908 \
  -X stream \
  --incremental=/srv/pgbackup/full-20260901/backup_manifest

增量备份不能直接作为数据目录启动。恢复前必须把完整备份和增量链合成为一个“合成完整备份”：

pg_combinebackup \
  -o /srv/pgrestore/combined \
  /srv/pgbackup/full-20260901 \
  /srv/pgbackup/incr-20260908 \
  /srv/pgbackup/incr-20260914

然后验证：

pg_verifybackup /srv/pgrestore/combined

必须按照从最旧到最新的顺序提供备份，并保留增量备份依赖的整个链。PostgreSQL 不会替你管理备份依赖关系。PostgreSQL 18 增量备份说明、pg_combinebackup 文档

七、WAL 归档与时间点恢复 PITR

PITR 的核心是：

某次基础备份 + 从该备份开始连续不断的 WAL = 任意时间点状态

例如，10:00 误执行了：

DROP TABLE market.daily_quote;

如果有基础备份和连续 WAL，就可以恢复到 09:59:59。

1. 开启 WAL 归档 

wal_level = replica
archive_mode = on
archive_timeout = '60s'

archive_command =
  'test ! -f /srv/wal_archive/%f && cp %p /srv/wal_archive/%f'

其中：

  • %p：源 WAL 文件路径。
  • %f：WAL 文件名。
  • 归档命令只有真正成功时才能返回 0。
  • 已存在的不同内容文件不能被覆盖。

上面的 cp 只适合作为实验示例。生产环境应使用经过验证的备份工具或对象存储方案，支持原子上传、校验、加密、重试和监控。归档失败可能导致 pg_wal 不断增长，最终磁盘耗尽。连续归档官方说明

监控归档状态：

SELECT archived_count,
       failed_count,
       last_archived_wal,
       last_archived_time,
       last_failed_wal,
       last_failed_time
FROM pg_stat_archiver;

2. 恢复到指定时间

恢复必须先在隔离实例中进行，不要直接覆盖正在运行的生产数据目录。

将基础备份恢复到新的数据目录后，在恢复实例配置中设置：

restore_command = 'cp /srv/wal_archive/%f "%p"'

recovery_target_time = '2026-09-14 09:59:59+08'
recovery_target_timeline = 'latest'
recovery_target_action = 'pause'

在恢复数据目录创建：

touch /srv/pgrestore/data/recovery.signal

启动该实例后，PostgreSQL 会读取 WAL 并停在目标时间。hot_standby=on 时可以检查：

SELECT pg_is_in_recovery();
SELECT pg_is_wal_replay_paused();
SELECT pg_last_xact_replay_timestamp();

SELECT count(*)
FROM market.daily_quote;

确认时间点正确后：

SELECT pg_wal_replay_resume();

PostgreSQL 18 使用 recovery.signal，不再使用旧版本的 recovery.conf。默认的 recovery_target_action 是 pause，适合先验证数据再结束恢复。恢复目标参数

3. PITR 不能直接恢复单表

PITR 恢复的是整个实例，不是某一个数据库或表。误删单表时通常这样处理：

  1. 在独立服务器恢复整个实例到误操作之前。
  2. 检查目标表数据。
  3. 从恢复实例导出该表。
  4. 导入生产库的临时表。
  5. 审核后再替换或合并数据。

同时必须单独备份：

  • postgresql.conf
  • pg_hba.conf
  • pg_ident.conf
  • TLS 证书和密钥
  • 扩展安装包及版本清单
  • 操作系统服务配置

这些配置文件的修改不会通过 WAL 恢复。

八、TimescaleDB 备份恢复

你的环境包含 TimescaleDB 时，完整逻辑恢复需要额外步骤。

1. 备份整个数据库 

pg_dump \
  -U backup_user \
  -d ashare \
  -Fc \
  -f ashare_timescale.dump

压缩 hypertable 不需要事先解压。

2. 创建目标数据库并加载扩展 

CREATE DATABASE ashare_restore;

连接目标数据库后：

CREATE EXTENSION IF NOT EXISTS timescaledb;

SELECT timescaledb_pre_restore();

3. 恢复 

pg_restore \
  -U postgres \
  -d ashare_restore \
  --exit-on-error \
  --verbose \
  ashare_timescale.dump

恢复结束后：

SELECT timescaledb_post_restore();

ANALYZE;

重要事项：

  • TimescaleDB 官方明确要求完整数据库恢复时不要使用 pg_restore -j，并行恢复可能无法正确恢复 TimescaleDB 内部目录。
  • 调用 timescaledb_pre_restore() 后，即使恢复失败，也应在处理完问题后调用 timescaledb_post_restore()。
  • 目标服务器必须提前安装兼容的 TimescaleDB 扩展程序。
  • 恢复前记录 PostgreSQL 与 TimescaleDB 版本。
  • 普通 pg_dump -t hypertable 不足以完整表达 hypertable 与 chunk 的关系。单 hypertable 迁移应采用“导出表结构 + CSV 数据 + 重新创建 hypertable”的流程。

参见 TimescaleDB 逻辑备份与恢复文档。

九、股票系统推荐方案

对于 A 股数据系统，可以按数据价值分类：

数据 特点 建议
market.daily_quote 通常可从数据源重新下载 每日逻辑备份或增量物理备份
证券基础资料 更新不频繁，但恢复依赖较多 每日逻辑备份
复权因子、指标结果 一般可以重新计算 降低备份频率
用户、订单、持仓、策略参数 不可重新生成 基础备份 + 连续 WAL
角色、权限、表空间 实例级元数据 pg_dumpall --globals-only
PostgreSQL/TimescaleDB 配置 WAL 不覆盖 独立配置备份

一个可落地的生产策略：

  • WAL 持续归档到独立存储。
  • 每周一次完整物理备份。
  • 每天一次 PostgreSQL 18 增量物理备份。
  • 每天一次关键数据库 custom 格式逻辑备份。
  • 角色或权限变更后重新执行 pg_dumpall --globals-only。
  • 至少保留两条完整可恢复的物理备份链。
  • 每月在隔离环境执行一次完整恢复和 PITR 演练。
  • 备份使用异地、加密、不可变存储。
  • 记录实际恢复时间，确认符合 RTO。

最后牢记：备份成功的标准不是生成了文件，而是已经从该文件成功恢复并验证过业务数据。