PostgreSQL/备份与恢复
创建备份是每个数据库管理员的必备任务。如果硬件崩溃或出现任何形式的数据损坏，DBA 必须确保数据库能够以最小的数据丢失恢复。PostgreSQL 提供多种策略来支持 DBA 实现此目标。
原则上，备份技术可分为两类：冷备份和热备份。冷备份是在没有数据库文件打开的情况下进行的备份。对于 PostgreSQL 来说，这意味着在进行备份的整个时间段内，实例必须停止。热备份是在正常工作时间进行的备份。客户端可以并行执行读写操作，以创建此形式的备份。
PostgreSQL 支持不同类型的备份
- 冷备份称为 文件系统级备份。
- 热备份有两种类型
- SQL 导出 生成 SQL 命令，例如：
INSERT，可以重新创建数据库。 - 持续归档和时间点恢复 (PITR) 使用特殊备份与之后所有数据更改的组合。
- SQL 导出 生成 SQL 命令，例如：
冷备份是在 PostgreSQL 实例未运行时进行的备份。它包括集群中所有数据库的所有文件。
只有一种方法可以创建一致的，因此有用的冷备份：PostgreSQL 实例必须停止，例如通过发出 pg_ctl stop 命令。这将断开所有客户端与集群中所有数据库的连接，关闭实例并关闭所有文件。之后，可以使用常用的操作系统复制工具（cp、tar、dd、rsync 等）将所有文件复制到安全位置，例如不同服务器上的磁盘。特别是要复制以下文件：
- 集群所在的目录节点下的所有文件。环境变量 $PGDATA 指向此目录，解析为类似于
.../postgres/14/data的内容。在命令行上使用echo $PGDATA或在 psql 中使用show data_directory;来查找目录。 - 所有配置文件。它们可能位于 $PGDATA 中，但也可能位于其他地方。主要的配置文件是：postgresql.conf、pg_hba.conf 和 pg_ident.conf。可以通过在 psql 实用程序中运行以下命令来找到它们的位置
show config_file;
show hba_file;
show ident_file;
- 所有表空间文件。这些文件位于文件系统的其他位置。可以通过查看
$PGDATA/pg_tblspc目录中的符号链接来找到它们的位置
cd $PGDATA/pg_tblspc
ls -lt
注意 您可以尝试只备份集群的某些部分，例如代表一个表的巨大文件，或一个表空间——或者相反：除巨大文件之外的所有内容。即使实例在生成这种部分副本时已关闭，此类副本也是无用的。恢复冷备份需要集群的所有数据文件和元信息文件才能重新创建集群。
注意 强烈建议在测试系统上验证每个备份/恢复策略以验证其可靠性，然后再在生产服务器上实施它们。特别是，必须测试恢复步骤！
► 优点
- 冷备份易于生成和恢复。
► 缺点
- 集群中任何数据库的持续 7x24 运行模式是不可能的。
- 无法备份集群的较小部分，例如单个数据库或表。
- 无法进行部分恢复。恢复必须包含所有集群文件。
- 崩溃后，最近一次冷备份后发生的任何数据更改都会丢失。只有备份中的数据将被恢复。
► 如何恢复
- 停止实例。
- 备份崩溃的集群的原始文件。它们可能对取证行动有用。
- 删除崩溃的集群的所有原始文件。
- 将冷备份的文件复制到它们原来的位置。
- 启动实例。它应该以正常方式启动，没有任何特殊消息。
与冷备份相比，热备份是在实例运行期间进行的，应用程序可能在备份进行时更改数据。热备份有时被称为 在线备份。PostgreSQL 支持两种截然不同的热备份：第一种是纯粹的基于 SQL 的版本，第二种是特定于产品的版本。它们将在接下来的两章中解释。
逻辑备份是热备份的两种形式之一。它包含集群、单个数据库或数据库某些部分中的数据和/或元数据。它们是由 pg_dump 或 pg_dumpall 实用程序创建的。
实例必须运行才能使这些实用程序正常工作。即使它们与其他客户端并行运行（可能需要更长的时间），它们也会创建数据在开始时间时的精确副本。例如，如果应用程序在此期间更改了一些数据，备份将获取旧值，而所有其他应用程序都将使用新值进行操作。这是因为 PostgreSQL 的 MVCC（多版本并发控制）实现允许同时存在一个行的多个版本。
pg_dump 在数据库级别工作，可以备份整个数据库，也可以备份其某些部分，例如单个表。它能够转储数据、模式定义或两者。参数 --data-only 和 --schema-only 选择目标部分。
pg_dump 支持两种输出格式：纯文本（可读的纯文本格式）和自定义（二进制格式）。格式类型由参数 --format 选择。纯文本格式包含 SQL 命令，例如 CREATE 和 INSERT。以这种格式创建的文件可以被 psql 用于恢复备份的数据。自定义格式有时被称为 存档格式。要恢复以这种格式创建的文件，必须使用 pg_restore。
以下图表显示了 pg_dump、psql 和 pg_restore 的协作。
一些示例
$ # dump complete database 'finance' in plain-text format to a file
$ pg_dump --dbname=finance --username=boss --format=plain --file=finance.sql
$
$ # restore database content (to a different or an empty database)
$ psql --dbname=finance_x --username=boss <finance.sql
$
$
$
$ # dump table 'person' of database 'finance' in binary format to a file
$ pg_dump --dbname=finance --username=boss --table=person \
--format=custom --file=finance_person.archive
$
$ restore table 'person' from binary file
$ pg_restore --dbname=finance_x --username=boss \
--format=custom <finance_person.archive
$
pg_dumpall 实用程序在集群级别运行，并内部调用 pg_dump 来转储集群中的每个数据库。此外，它还转储集群级别对象（“全局对象”），例如用户/角色及其权限。如果它在没有详细参数的情况下启动，它将转储集群的完整内容：所有数据库的所有数据和元数据，以及所有集群级别对象。参数 --globals-only 可用于将其行为限制为仅转储集群对象。pg_dumpall 的输出为纯文本格式。
► 优点
- 可以实现连续 7x24 运行模式。
- 可以备份或还原集群或数据库的小部分。
- 当您使用文本格式时，您可以从一个 PostgreSQL 版本切换到另一个版本，或者从一个硬件平台切换到另一个平台。
► 缺点
- 文本格式占用大量空间，但压缩效果很好。
► 如何恢复
如上图所示，恢复过程取决于转储的格式。文本文件采用标准 SQL 语法。要从这些文件重建数据，您必须使用 psql。具有自定义格式的文件具有特定于 PostgreSQL 的二进制结构，只能由 pg_restore 实用程序使用。
这是第二种形式的热备份。此类备份包括两部分。第一部分是所谓的基本备份，它包含集群所有文件的副本（类似于文件系统级备份）。第二部分包含从备份命令开始以来的所有数据更改。此类数据更改会随着进一步的在线活动（在备份生成期间和之后）而持续发生，存储在 WAL 文件中，并且必须像第一部分一样持续保存（“归档”）。
为了了解此类备份的目的和技术，了解 PostgreSQL 的崩溃恢复策略会有所帮助。在任何时候，并且独立于任何备份/恢复操作，PostgreSQL 都会维护预写日志 (WAL) 文件，主要用于崩溃安全性目的。此类WAL 文件包含日志记录，这些记录反映了对数据和模式所做的所有更改。在将更改传输到数据文件之前，日志记录会存储在（顺序写入的）WAL 文件中。如果系统崩溃，这些日志记录将用于在实例重新启动期间将集群恢复到一致状态。恢复过程会在 WAL 文件中搜索最后一个检查点的时间戳，并按时间顺序将所有后续日志记录重播到集群。通过此操作，集群将恢复到一致状态，并将包含所有更改，直到上次提交。
从备份恢复时，总体策略类似于崩溃恢复策略：删除崩溃集群的文件，从基本备份中恢复它们，通知恢复过程（这是实例的组成部分）如何通过操作系统命令访问已归档的 WAL 文件，然后重新启动实例。实例的恢复部分将所有日志记录从已归档的WAL 文件重播到（已恢复的）数据库文件，并将集群传输到一致状态。此后，集群将包含所有更改，直到崩溃之前的上次提交。
要实施此备份策略，必须执行三个操作
- 在postgres.conf 中定义所有必要的参数。
- 使用
pg_basebackup实用程序生成基本备份。 - 归档所有出现的 WAL 文件。
如果需要恢复，您必须删除集群中的所有文件，通过将备份复制到其原始位置来重新创建集群的旧状态，创建一个特殊文件（recovery.signal 或recovery.conf，见下文：步骤 3），其中包含一些恢复信息（尤其是已归档 WAL 文件的位置），然后重新启动实例。实例将根据其在postgres.conf 和recovery.conf 中的参数，将集群重新创建到一致状态，包括所有数据更改，直到上次提交。
► 优点
- 可以实现连续 7x24 运行模式。
- 以最小的数据丢失进行恢复。
- 生成的 WAL 文件可用于其他功能，例如复制。
► 缺点
- 基本备份仅适用于集群级别，不适用于任何更细粒度的级别，如数据库或表。
- 如果您的数据库非常繁忙，并且客户端更改了很多数据，可能会出现很多 WAL 文件。
步骤 1
您必须在postgres.conf 中定义一些参数，以便 WAL 文件包含足够的数据，WAL 文件的归档被激活，并且定义了一个复制命令来将 WAL 文件传输到一个安全位置。
# collect enough information in WAL files wal_level = 'replica' # activate ARCHIVE mode so that WAL files will be archived by the instance archive_mode = on # supply a system command to transfer WAL files to a failsafe location (cp, scp, rsync, ...) # %p represents the pathname including filename. %f represents the filename only. archive_command = 'scp %p dba@archive_server:/postgres/wal_archive/%f'
定义参数后，您必须重新启动集群：pg_ctl restart。集群将在其子目录pg_wal（在 Postgres 9.x 及更早版本中为pg_xlog）中持续生成 WAL 文件，以配合数据库中的数据更改。当它已填充一个 WAL 文件并且必须切换到下一个 WAL 文件时，它会将旧文件复制到定义的归档位置。
步骤 2
您必须使用 bg_basebackup 实用程序创建所谓的基本备份。
$ # take a copy (base backup) of the files of the cluster with the pg_basebackup utility
$ pg_basebackup --pgdata=/safe_drive/backup/
$
步骤 3
就是这样。所有其他活动都由实例执行，尤其是将完全填充的 WAL 文件持续复制到归档位置。
要执行恢复，原始的基本备份会被复制回来，并且实例被配置为在启动时执行恢复。
- 停止实例 - 如果它仍在运行。
- 创建崩溃集群的副本 - 如果您的磁盘空间足够。也许您在以后的阶段会需要它。
- 删除崩溃集群的所有文件。
- 从基本备份中重新创建集群文件。
- 在 $PGDATA 中创建一个特殊文件
- 12 版之前的 PostgreSQL：在 $PGDATA 中创建一个名为recovery.conf 的文件。它必须包含类似于以下命令的命令：restore_command = 'scp dba@archive_server:/postgres/wal_archive/%f %p'。此复制命令与postgres.conf 中的命令相反，该命令将 WAL 文件保存到归档位置。
- 12 版及之后的 PostgreSQL：在 $PGDATA 中创建一个名为recovery.signal 的空文件。在postgres.conf 中添加类似于以下命令的命令：restore_command = 'scp dba@archive_server:/postgres/wal_archive/%f %p'。此复制命令与postgres.conf 中的命令相反，该命令将 WAL 文件保存到归档位置。
- 启动实例。在启动期间，实例会复制并处理在归档位置中找到的所有 WAL 文件。
recovery.signal 或recovery.conf 的存在表明实例需要执行恢复。恢复成功后，此文件将被重命名。
如果您想恢复到崩溃发生之前（但在备份创建之后）的某个时间点，则可以通过指定该时间点来实现。在这种情况下，恢复过程将在处理所有已归档的 WAL 文件之前停止。此功能是时间点恢复一词的来源。
总而言之，用于恢复的两个关键命令（在recovery.conf 或postgres.conf 中）可能如下所示
restore_command = 'scp dba@archive_server:/postgres/wal_archive/%f %p' recovery_target_time = '2021-01-31 06:00:00 CET'
有一个开源项目Barman，它简化了备份和恢复步骤。如果您必须管理许多服务器和实例，并且配置和记住有关服务器环境的所有详细信息变得很复杂，Barman 会存储配置详细信息并自动执行进程。