postgresql 一次插入多条记录_PostgreSQL 复制方案（管够）

流感无情，人间有情，总会有雨过天晴的时候。希望大家都能够平平安安，一切顺利。

一些读者在公众号后台留言，希望笔者能对 PostgreSQL 的主备进行比较全面的介绍。正好春节前，笔者计划再写一篇文章，那就开始吧。

为了规范相关术语，约束如下：

• 使用 Master 术语统一代表主数据库或主服务器

• 使用 Standby 术语统一代表备数据库或备用服务器

数据复制

在使用 PostgreSQL 数据库的大部分应用场景中，为了获得更好的性能和扩展性，除了 Master 节点外，通常会启动更多的 PostgreSQL 实例(Standby 节点)来处理额外的负载，并从 Master 节点复制数据。在需要高可用性的情况下，这也是将数据持续地复制到 Standby 的典型解决方案，以便在 Master 崩溃时可以接管数据并提供服务能力。

事务日志

在讲解如何设置数据复制之前，我们快速看一下 PostgreSQL 如何在较低级别处理数据更改。

当 PostgreSQL 处理更改数据库中数据的命令时，它将新数据写入磁盘以使其持久化。默认情况下，PostgreSQL 数据库在磁盘上有两个写入数据的存储路径(不同操作系统存储路径会有所差异，这里以 CentOS 7.5 为参考)：

• 数据文件默认位于 /var/lib/pgsql/11/data/base 目录下面，存储数据包括表、索引、临时表和其他对象。该目录的大小受限于节点磁盘大小的限制。

• 事务日志默认位于 /var/lib/pgsql/11/data/pg_wal 目录下面，数据文件中存储最新更改的日志。其大小受配置限制，默认情况下为 1 GB。

可能初学者会有疑问，为什么要在两个位置写入相同的数据？这似乎是多余的，但这其实是有原因的。

想象一下，假如有一个事务将 text 类型的值 test 的记录插入一个大表中，并且碰巧数据库服务在事务中间崩溃了，此时字母 te 已经写入磁盘，而其余部分丢失了。当再次启动数据库时，它将无法判断记录是否已损坏，因为它不知道该字段的值是单词 test 还是字母 te。

幸好，用户不用担心这种问题的发生，因为我们可以使用 checksum 机制来解决。那么数据库如何找到已经损坏的记录？如果每次意外重新启动后，验证整个数据库的 checksum 是非常昂贵的。

如果对传统数据库(比如 MySQL/MariaDB)熟悉的话，读者应该知道相关的解决方案。解决方案就是，在将数据写入数据文件之前，PostgreSQL 始终将其先写入事务日志。事务日志(write-ahead log，也称为预写日志)是 PostgreSQL 对数据文件所做的更改的列表。事务日志显示为一组 16 MB 大小的文件(称为 WAL 文件)，位于 PostgreSQL 数据库路径下的 pg_wal 子目录中。每个文件包含许多记录，这些记录表明了以哪种方式更改哪个数据文件。只有将事务日志保存在磁盘上时，数据库才会将数据写入数据文件。当事务日志已满时，PostgreSQL 将删除其最旧的段以重新使用磁盘空间，但是首先它得确保数据已经写入数据文件。

现在，假如数据库系统故障后，重新启动后将发生以下情况：

• 如果在写入事务日志期间系统崩溃，则 PostgreSQL 会确定日志记录处于不完整状态，因为 checksum 出现不匹配。PostgreSQL 会丢弃此次事务日志记录，并对事务的写入数据执行回滚。

• 如果在写入数据文件期间系统崩溃，但事务日志未损坏，则 PostgreSQL 将遍历事务日志，验证数据内容已写入数据文件，并在必要时更正数据文件。无需扫描所有数据文件，因为它从事务日志中知道应该更改哪个数据文件的哪一部分以及如何更改。

PostgreSQL 重放事务日志的过程称为恢复，这是数据库在意外重新启动后始终执行的操作。如果从初始化数据库服务器到当前时间都有完整的事务日志，则可以将数据库的状态恢复到过去的任何时间点。实际上，这是现实存在的情况。实际生产环境，笔者建议将 PostgreSQL 配置为在某处归档事务日志，而不是删除旧的 WAL 文件。然后，可以使用此归档文件在另一台计算机上执行数据库的基于时间点的恢复(point-in-time recovery)。

物理复制

PostgreSQL 的事务日志条目可以从一个数据库服务器(Master)获取，并应用于另一台数据库服务器(Standby)上的数据文件。在这种情况下，Standby 将具有 Master 数据库的完整的副本。传输事务日志条目并将其应用于另一台服务器的过程称为物理复制。之所以称其为物理复制，是因为事务日志恢复是在数据文件级别上起作用，并且 Standby 上的数据库副本将是 Master 的副本。

Standby 数据库可以配置为允许只读查询，在这种情况下，它称为 Hot Standby。

下面笔者介绍几种物理复制的方式。

日志传送复制

PostgreSQL 配置物理复制的一种方法是持续不断地将新的 WAL 文件从 Master 传送到 Standby，并将其应用于 Standby，以获取数据库的同步副本，这种情况称为日志传送复制。

要设置基于日志传送的复制，应采取以下措施：

• 在 Master 上，执行以下操作：

  • 确保 WAL 文件具有足够的信息来进行复制 在 postgresql.conf 文件中，将 wal_level 配置参数设置为 replica 或 logical。

  • 通过将 archive_mode 参数设置为 on，启用 WAL 文件的归档功能。

  • 使用 archive_command 配置参数指定的事务日志归档的安全位置。

    PostgreSQL 会使用 archive_command 中的命令归档每个 WAL 文件。例如，此命令可以压缩文件并将其复制到网络存储上。如果命令为空，但是启用了 WAL 文件的归档，则这些文件将累积存储在 pg_wal 目录中。

• 在 Standby 上，执行以下操作：

  • 首先恢复 Standby 获取的 Master 数据库的备份副本。最简单的方法是使用 pg_basebackup 工具。在 Standby 上执行如下命令：

    $ pg_pasebackup -D /var/lib/pgsql/11/data -h master -U postgres

  • 其次在数据目录中创建一个 recovery.conf 文件。此文件用于配置服务器应如何执行恢复以及它是否应作为 Standby 工作。要将服务器设置为 Standby，文件中至少应包含以下两行：

    standby_mode = on

    restore_command = 'cp /wal_archive_location/%f %p'

    注：restore_command 参数的值将取决于 WAL 归档文件的位置。这是一个 Linux 操作系统的命令，它将 WAL 文件从 WAL 归档位置复制到数据目录中事务日志的位置。

Master 和 Standby 配置完成后，就会启动两个数据库服务，Master 会将所有 WAL 文件复制到存档位置，而 Standby 将从那里获取它们，并进行重放以恢复数据。但是 Standby 上无法进行任何事务操作，可以将其配置为只读访问模式，提高查询性能。

如果 Master 数据库崩溃并且有必要切换到 Standby，则应将 Standby 升级为新的 Master 数据库，同时意味着它应停止恢复模式并允许读写事务。为此，只需删除 Standby 数据库的 recovery.conf 文件并重新启动数据库，那么 Standby 就成为了新的 Master。而这时我们应该将原先的旧 Master 重新投入使用后，使其成为 Standby 以保持集群冗余。在这种情况下，应重复上述步骤，将旧的 Master 切换为新 Master 的 Standby 数据库，切勿再将旧的 Master 作为 Master 启动，否则可能导致副本之间的差异以及最终数据丢失。

通过日志传送实现复制的好处如下：

• 设置相对容易

• 无需连接 Standby 和 Master

• Master 不依赖于 Standby

• Standby 的数量可以大于一个。实际上，Standby 还可以用作其他 Standby 的 Master，这称为级联复制。

另一方面，基于日志传送的复制还存在一些问题：

• 必须为 WAL 归档文件提供一个网络存储位置，Master 和 Standby 都可以访问该位置，这意味着需要第三实体的介入。

• Standby 只有在存档后才能重放 WAL 文件。仅当文件完成时才会发生这种情况，即文件大小达到 16 MB。这意味着在 Master 上执行的最新事务可能不会反映在 Standby 数据库上，尤其是在 Master 上的事务很小且不经常发生的情况下。在这种情况下，Standby 数据库可能没有最近的事务，并且其状态落后于 Master 数据库。在 Master 数据库发生故障的情况下，某些数据可能会丢失。

流复制

除了日志传送复制，还有另一种可以在日志传送之上或根本不需要日志传送的物理复制方案，即流复制。流复制需要 Standby 和 Master 之间的连接，Master 会将所有事务日志条目直接发送到 Standby。这样，Standby 将具有所有最近的更改，而无需等待 WAL 文件被归档。

要配置流复制，除了前面提到的步骤外，还应在 Master 上执行以下操作：

1. 在数据库中创建用于复制的数据库角色，如下 SQL 命令：

postgres=# CREATE USER streamer REPLICATION PASSWORD 'secret';

2. 在 pg_hba.conf 文件中配置允许该用户连接到称为 replication 名称的虚拟数据库，如下所示：

host replication streamer xx.xxx.146.102/32 md5

在 Standby 上执行：

1. 在 Standby 上，将 primary_conninfo 参数添加到 recovery.conf 文件中，建立 Standby 到 Master 的连接，如下所示：

primary_conninfo = 'host=xxx.xx.146.101 port=5432 user=streamer password=secret'

如果启用并配置了 WAL 归档文件，则首先执行日志传送方案，直到归档文件中的所有 WAL 文件都应用于 Standby 数据库为止。当没有更多文件时，Standby 将连接到 Master 数据库，并开始直接从 Master 接收新的 WAL 条目。如果该连接中断或 Standby 重新启动，则该过程将再次开始。

其实可以完全不用 WAL 存档来设置流复制。默认情况下，Master 将最后 64 个 WAL 文件存储在 pg_wal 文件夹中。它可以在必要时将它们发送到 Standby 数据库。每个文件的大小为 16 MB。这意味着，只要数据文件中的更改量小于 1 GB，流复制就可以在不使用 WAL 存档的情况下在 Standby 数据库中重放这些更改。

Replication slot 功能

另外，PostgreSQL 提供了一种使 Master 识别到 Standby 处理了哪些 WAL 文件以及哪些未处理的方式。如果 Standby 数据库运行缓慢(或只是断开了连接)，则 Master 数据库将不会删除尚未处理的文件，即使它们的数量大于 64，也可以通过在 Master 数据库上创建 replication slot 来完成。然后 Standby 数据库将使用 replication slot，而 Master 数据库将跟踪该 replication slot 处理了哪些 WAL 文件。

<