如何使用 pg_dump 和 pg_restore 备份和恢复数据库

你需要一个可以真正恢复的 PostgreSQL 备份，而不仅仅是一个看起来像备份的文件。pg_dump 和 pg_restore 是逻辑备份的标准工具：它们将数据库对象和数据复制到可移植的转储文件中，然后稍后在相同服务器、测试服务器或新的 PostgreSQL 实例上重建它们。

本指南专注于单数据库逻辑备份。对于整个集群的灾难恢复、时间点恢复或非常大的安装，请结合物理备份和 WAL 归档使用。

理解 pg_dump

pg_dump 连接到一个数据库并导出其模式和数据。它不会转储角色、表空间或全局集群对象；当需要这些时，请使用 pg_dumpall --globals-only。

输出格式

pg_dump 支持多种输出格式：

• 纯文本 (-Fp，默认)： 写入 SQL，使用 psql 恢复。易于检查和编辑，但大型恢复可能较慢且不够灵活。
• 自定义归档 (-Fc)： 写入压缩归档，供 pg_restore 使用。支持选择性恢复和并行恢复。
• 目录格式 (-Fd)： 写入一个目录文件。支持并行转储和并行恢复，有助于大型数据库。
• Tar 格式 (-Ft)： 写入 tar 归档，供 pg_restore 使用。它是单个文件，但不支持像自定义归档那样在格式内部进行压缩。

常用 pg_dump 选项

以下是 pg_dump 最常用的一些选项：

• -h <主机名>：连接到主机。
• -p <端口>：连接到端口。
• -U <用户名>：以用户身份连接。
• -W：连接前强制提示输入密码。
• -f <文件名>：写入文件或目录，而不是标准输出。
• -F <格式>：选择 p、c、d 或 t。
• -Z <级别>：为支持压缩的格式设置压缩级别。
• --exclude-table=<模式>：排除匹配的表。
• -j <数字> 或 --jobs=<数字>：在目录格式转储时使用并行作业。
• --clean：在纯 SQL 输出中包含删除命令，然后再创建命令。
• --create：包含创建数据库本身的命令。

示例：基本纯文本备份

要创建名为 mydatabase 的数据库的纯文本备份，该数据库由用户 myuser 拥有，并保存为 mydatabase_backup.sql：

pg_dump -U myuser -f mydatabase_backup.sql mydatabase

如果需要指定主机和端口：

pg_dump -h localhost -p 5432 -U myuser -f mydatabase_backup.sql mydatabase

根据你的身份验证方法，这可能会提示输入 myuser 的密码。

示例：自定义格式备份（推荐）

自定义格式是许多生产备份的良好默认选择，因为它与 pg_restore 配合使用并支持选择性恢复。

pg_dump -U myuser -Fc -f mydatabase_backup.dump mydatabase

这将创建 mydatabase_backup.dump，格式适合 pg_restore。

示例：带并行作业的目录格式备份

对于大型数据库，并行转储可以减少备份时间。它需要目录格式：

pg_dump -U myuser -Fd -j 4 -f mydatabase_backup_dir mydatabase

此命令将创建一个名为 mydatabase_backup_dir 的目录，其中包含多个文件，并使用 4 个并行作业。

理解 pg_restore

pg_restore 恢复由 pg_dump 创建的自定义、目录或 tar 归档。纯 SQL 文件使用 psql 恢复，而不是 pg_restore。

常用 pg_restore 选项

• -h <主机名>：连接到主机。
• -p <端口>：连接到端口。
• -U <用户名>：以用户身份连接。
• -W：连接前强制提示输入密码。
• -d <数据库>：连接到该数据库进行恢复。
• -v：打印详细的恢复输出。
• -c 或 --clean：在重新创建数据库对象之前删除它们。
• --create：在恢复之前创建转储的数据库。使用 -d 连接到现有的维护数据库，通常是 postgres。
• -j <数字>：在支持的地方使用并行作业进行恢复。
• -F <格式>：如果自动检测不够，则指定归档格式。

从纯文本 SQL 备份恢复

如果你使用 pg_dump 创建了纯文本 SQL 备份（例如 mydatabase_backup.sql），则使用 psql 恢复：

psql -U myuser -d mydatabase -f mydatabase_backup.sql

如果备份文件包含来自 pg_dump --create 的 CREATE DATABASE 和 \connect 命令，则连接到维护数据库，例如 postgres：

psql -U myuser -f mydatabase_backup.sql postgres

示例：从自定义格式备份恢复

要将自定义格式备份 mydatabase_backup.dump 恢复到名为 restored_db 的数据库中：

pg_restore -U myuser -d restored_db mydatabase_backup.dump

如果归档是使用 pg_dump --create 创建的，你可以让 pg_restore 创建转储的数据库。-d 值必须是用于初始连接的现有数据库，而不是正在创建的新数据库：

pg_restore -U myuser --create -d postgres mydatabase_backup.dump

这将创建转储中命名的数据库并恢复其中。如果要恢复到不同名称的数据库，请先创建该数据库，然后在不使用 --create 的情况下恢复：

createdb -U myuser restored_db
pg_restore -U myuser -d restored_db mydatabase_backup.dump

示例：并行恢复

为了加快恢复速度，特别是对于大型数据集，请使用 -j 选项：

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup.dump

这将使用 4 个工作作业进行恢复。当转储包含多个独立对象需要加载和索引时，并行恢复最有用。

示例：从目录格式备份恢复

要恢复以目录格式创建的备份（例如 mydatabase_backup_dir）：

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup_dir

注意最后一个参数是目录路径。

备份最佳实践

• 调度备份： 使用 cron、systemd 定时器或你的编排平台。根据你可以承受丢失的数据量选择调度。
• 使用正确的格式： 自定义格式是一个强大的默认选择。当需要为大型数据库进行并行转储时，目录格式更好。
• 测试恢复： 按计划恢复到暂存数据库。从未测试过的备份只是猜测。
• 捕获全局对象： 在从头重建服务器时，使用 pg_dumpall --globals-only 单独转储角色和表空间。
• 异地存储副本： 将备份保存在主服务器之外，并使用加密和访问控制保护它们。
• 监控作业： 对失败的转储、异常小的备份文件和旧的备份时间戳发出警报。
• 规划保留策略： 决定保留多少小时、每日、每周和每月备份，然后自动清理。
• 小心使用 --clean： 它会删除目标数据库中的对象。仅在你打算替换现有对象时使用。

要点

使用 pg_dump -Fc 进行灵活的单文件备份，使用 pg_dump -Fd -j 进行并行转储，使用 psql 进行纯 SQL 恢复，使用 pg_restore 进行归档恢复。然后通过在紧急情况发生之前恢复到测试数据库来验证该过程。