如何在Ubuntu20.04上使用PostgreSQL12设置连续存档和执行时间点恢复

作为 Write for DOnations 计划的一部分，作者选择了 Diversity in Tech Fund 来接受捐赠。

介绍

PostgreSQL是一个广泛使用的关系型数据库，支持ACID事务。首字母缩略词 ACID 代表原子性、一致性、隔离性和持久性。这是 PostgreSQL 支持的数据库事务的四个关键属性，以确保数据库中数据的持久性和有效性。

PostgreSQL 用来维护 ACID 属性的一种方法是 Write-Ahead Logging (WAL)。 PostgreSQL 在将更改写入数据库集群的数据文件之前，首先将数据库上的任何事务记录到 WAL 日志文件中。

通过连续归档，WAL 文件被复制到辅助存储，这有几个好处。例如，辅助数据库集群可以将存档的 WAL 文件用于复制目的，但您也可以使用这些文件执行时间点恢复 (PITR)。也就是说，如果发生事故，您可以使用这些文件将数据库集群回滚到所需的点。

在本教程中，您将在 Ubuntu 20.04 上使用 PostgreSQL 12 集群设置连续归档并在集群上执行 PITR。

先决条件

要完成本教程，您需要以下内容：

运行 Ubuntu 20.04 的单机。初始服务器设置指南将引导您设置服务器，包括具有适当权限的非root用户。
PostgreSQL 12 在机器上运行。如何在 Ubuntu 20.04 上安装和使用 PostgreSQL 教程的 Step 1 涵盖了在 Ubuntu 20.04 上安装 PostgreSQL 12。

第 1 步 — 在数据库集群上配置连续归档

在这第一步中，您需要配置 PostgreSQL 12 集群以将集群的 WAL 文件存档在与集群数据目录不同的目录中。为此，您必须首先在某处创建一个新目录来归档 WAL 文件。

创建一个新目录，如下所示：

mkdir database_archive

您现在需要授予默认 PostgreSQL 用户 postgres 写入此目录的权限。您可以通过使用 chown 命令更改目录的所有权来实现此目的：

sudo chown postgres:postgres database_archive

现在您已经为集群设置了一个目录来将 WAL 文件存档到其中，您必须在 postgresql.conf 配置文件中启用存档，默认情况下您可以在 /etc/postgresql/12/main/ 目录中找到该文件。

使用文本编辑器打开配置文件：

sudo nano /etc/postgresql/12/main/postgresql.conf

打开文件后，您将通过从行首删除 # 来取消注释带有 archive_mode 变量的行。此外，将 archive_mode 的值更改为 on，如下所示：

/etc/postgresql/12/main/postgresql.conf

. . .
archive_mode = on
. . .

您还将指定集群用于归档文件的命令。 PostgreSQL 提供了一个适用于本教程的归档命令，您可以在官方 PostgreSQL 文档中阅读。取消注释 archive_command 变量并添加以下命令：

/etc/postgresql/12/main/postgresql.conf

. . .
archive_command = 'test ! -f /path/to/database_archive/%f && cp %p /path/to/database_archive/%f'
. . .

这里的存档命令首先检查存档中是否已经存在 WAL 文件，如果不存在，则将 WAL 文件复制到存档中。

将 /path/to/database_archive 替换为您之前创建的 database_archive 目录的路径。例如，如果您在主目录中创建了这个：~/database_archive。

最后，您需要配置 wal_level 变量。 wal_level 指示 PostgreSQL 写入日志的信息量。对于连续归档，这需要至少设置为 replica：

/etc/postgresql/12/main/postgresql.conf

. . .
#wal_level = replica
. . .

这已经是 PostgreSQL 12 中的默认值，因此您不需要更改它，但如果您曾经更改此变量，请记住这一点。

您现在可以保存并退出您的文件。

要实现对数据库集群配置文件的更改，您需要重新启动集群，如下所示：

sudo systemctl restart postgresql@12-main

如果 PostgreSQL 成功重启，集群将在每个 WAL 文件满后归档。默认情况下，每个 WAL 文件为 16MB。

在需要立即归档事务的情况下，可以通过在集群上运行以下命令来强制数据库集群更改并归档当前的 WAL 文件：

sudo -u postgres psql -c "SELECT pg_switch_wal();"

随着数据库集群成功地将 WAL 文件复制到存档，您现在可以对数据库集群的数据文件执行物理备份。

第 2 步 — 执行 PostgreSQL 集群的物理备份

如果发生最坏的情况，定期备份数据库以帮助减少数据丢失非常重要。 PostgreSQL 允许您对数据库集群进行逻辑和物理备份。但是，对于 PITR，您需要对数据库集群进行物理备份。也就是说，您需要在 PostgreSQL 的数据目录中制作所有数据库文件的副本。默认情况下，PostgreSQL 12 数据目录为 /var/lib/postgresql/12/main/。

注意：你也可以通过在集群上运行以下命令来找到数据目录的位置：

sudo -u postgres psql -c "SHOW data_directory;"

在上一步中，您创建了目录 database_archive 来存储所有存档的 WAL 文件。在此步骤中，您需要创建另一个目录，称为 database_backup，以存储您将进行的物理备份。

再次，制作目录：

mkdir database_backup

现在通过更改所有权确保 postgres 用户有权写入目录：

sudo chown postgres:postgres database_backup

现在您有了备份目录，您需要对数据库集群的数据文件执行物理备份。幸运的是，PostgreSQL 有内置的 pg_basebackup 命令为您执行所有操作。以 postgres 用户身份运行命令：

sudo -u postgres pg_basebackup -D /path/to/database_backup

将 /path/to/ 替换为您的目录的路径。

通过数据库集群的物理备份，您现在可以在集群上执行时间点恢复。

第 3 步 — 在数据库集群上执行时间点恢复

现在您至少有一个数据库的物理备份并且您正在归档 WAL 文件，如果您需要将数据库回滚到以前的状态，您现在可以执行 PITR。

首先，如果数据库仍在运行，您需要将其关闭。您可以通过运行 systemctl stop 命令来执行此操作：

sudo systemctl stop postgresql@12-main

一旦数据库不再运行，您需要删除 PostgreSQL 数据目录中的所有文件。但首先，您需要将 pg_wal 目录移动到其他位置，因为这可能包含对恢复很重要的未归档 WAL 文件。使用mv命令移动pg_wal目录如下：

sudo mv /var/lib/postgresql/12/main/pg_wal ~/

现在，您可以完全删除 /var/lib/postgresql/12/main 目录并重新创建它：

sudo rm -rf /var/lib/postgresql/12/main

其次是：

sudo mkdir /var/lib/postgresql/12/main

现在，您需要将上一步中创建的物理备份中的所有文件复制到新的空数据目录中。您可以使用 cp 执行此操作：

sudo cp -a /path/to/database_backup/. /var/lib/postgresql/12/main/

您还需要确保数据目录具有 postgres 用户作为所有者和适当的权限。运行以下命令更改所有者：

sudo chown postgres:postgres /var/lib/postgresql/12/main

并更新权限：

sudo chmod 700 /var/lib/postgresql/12/main

从物理备份中复制的 pg_wal 目录中的 WAL 文件已过时且无用。您需要将它们替换为您在清空 PostgreSQL 数据目录之前复制的 pg_wal 目录中的 WAL 文件，因为在停止服务器之前某些文件可能尚未存档。

删除/var/lib/postgresql/12/main目录下的pg_wal文件如下：

sudo rm -rf /var/lib/postgresql/12/main/pg_wal

现在从清除数据目录之前保存的 pg_wal 目录中复制文件：

sudo cp -a ~/pg_wal /var/lib/postgresql/12/main/pg_wal

正确恢复数据目录后，您需要配置恢复设置以确保数据库服务器正确恢复归档的 WAL 文件。恢复设置可在 /etc/postgresql/12/main/ 目录下的 postgresql.conf 配置文件中找到。

打开配置文件：

sudo nano /etc/postgresql/12/main/postgresql.conf

打开文件后，找到 restore_command 变量并从行首删除 # 字符。就像您在第一步中对 archive_command 所做的那样，您需要指定 PostgreSQL 应该如何恢复 WAL 文件。由于 archive 命令只是将文件复制到存档，因此 restore 命令会将文件复制回来。 restore_command 变量将类似于以下内容：

/etc/postgresql/12/main/postgresql.conf

. . .
restore_command = 'cp /path/to/database_archive/%f %p'
. . .

请记住将 /path/to/database_archive/ 替换为存档目录的路径。

接下来，您可以选择指定恢复目标。这是数据库集群在离开恢复模式之前尝试恢复到的点。恢复目标可以是时间戳、事务 ID、日志序列号、使用 pg_create_restore_point() 命令创建的还原点的名称，或者数据库达到一致状态时的名称。如果未指定恢复目标，则数据库集群将通读归档中 WAL 文件的整个日志。

有关 recovery_target 变量的完整选项列表，请参阅官方 PostgreSQL 文档。

注意： 恢复目标必须是您正在使用的物理备份之后的某个时间点。如果您需要返回到较早的点，那么您需要使用数据库的较早备份。

设置 restore_command 和 recovery_target 后，保存并退出文件。

在重新启动数据库集群之前，您需要通知 PostgreSQL 它应该以恢复模式启动。您可以通过在集群的数据目录中创建一个名为 recovery.signal 的空文件来实现此目的。要在目录中创建一个空文件，请使用 touch 命令：

sudo touch /var/lib/postgresql/12/main/recovery.signal

现在您可以通过运行以下命令重新启动数据库集群：

sudo systemctl start postgresql@12-main

如果数据库成功启动，它将进入恢复模式。一旦数据库集群达到恢复目标，它将删除 recovery.signal 文件。

现在您已成功将数据库集群恢复到所需状态，您可以开始正常的数据库操作。如果您想恢复到不同的时间点，您可以重复此步骤。

结论

在本教程中，您设置了一个 PostgreSQL 12 数据库集群来归档 WAL 文件，然后使用归档的 WAL 文件执行时间点恢复。如果发生事故，您现在可以将数据库集群回滚到理想状态。

要了解有关连续存档和时间点恢复的更多信息，您可以阅读文档。

有关 PostgreSQL 的更多教程，请查看我们的 PostgreSQL 主题页面。