如何设置PostgreSQL数据库复制

数据库复制是将数据从中央或主服务器复制到多个称为副本的服务器的过程。主服务器接受读取和写入操作，而副本服务器提供只读事务。主服务器和副本共同构成一个数据库集群。数据库复制的目标是确保数据的冗余、一致性、高可用性和可访问性，尤其是在高流量、关键任务应用程序中。

PostgreSQL 提供了两种复制方法：物理（即流式）复制和逻辑复制。两者都是不同用例的理想选择，用户可以根据最终目标选择其中之一。让我们看看这些复制方法中的每一种。

物理 PostgreSQL 复制

这是 PostgreSQL 中最常见的复制类型。物理复制维护集群整个数据的完整副本。它使用精确的块地址并采用逐字节复制。简单来说，主服务器上的整个数据集都被复制到充当备用节点的副本。

物理复制不会复制主数据库集群的特定对象，例如表中的单行数据。相反，它在磁盘块级别上工作，并将所有数据镜像到副本节点；包括每个数据库中的所有表。此复制要求所有副本都相同。

用例：

• 它主要用于灾难恢复设置和备份，因为所有副本都是相同的。
• 处理大量数据时推荐使用

优点

• 它很容易实现，因为所有的数据库集群都是相同的。
• 它确保了任何时候的数据一致性和高可用性，因为所有副本都持有相同的数据副本。
• 它非常适合对副本进行只读操作。
• 它非常高效，因为它不需要任何特殊处理。

缺点

• 这是带宽密集型的，因为要复制整个数据，而不仅仅是主集群的一小部分。
• 它不提供多主数据库复制。

逻辑 PostgreSQL 复制

逻辑复制最早是在 PostgreSQL 9.0 中引入的。它通过复制数据对象及其基于唯一标识符（如主键）的更改来工作。简单来说，逻辑复制以基于行的模型复制数据库对象，而不是将所有内容发送到副本节点的物理复制。

因此，与物理复制相反，逻辑复制提供了对数据复制的细粒度控制。

用例：

逻辑复制的典型用例包括：

• 在不同主要版本的 PostgreSQL 之间复制。
• 在不同平台上托管的 PostgreSQL 实例之间进行复制，例如从 Linux 到 Windows。
• 将数据库中的增量更改实时发送到副本。
• 向不同的用户组授予对复制数据的访问权限。

优点

• 它是备份增量数据的理想选择。
• 由于更好的性能和低数据丢失，最推荐用于高可用性集群。
• 带宽优化，因为只有提交的数据事务的行更改才会发送到副本，而不是整个数据块。
• 用于使用物理复制无法实现的多主复制。
• 支持跨各种操作系统平台的复制，例如 Linux 到 Windows，反之亦然。

缺点

• 它无法实时传输大量交易。
• 复制过程比物理复制更复杂。
• 副本节点上的资源利用率高。

在 Ubuntu 22.04 上设置物理 PostgreSQL 复制

为了演示复制过程，您需要一个主节点和一个将在其上进行复制的副本节点。下面是我们的实验室设置，我们将使用 Ubuntu 22.04 作为首选操作系统。

主节点：IP：194.195.208.82 操作系统：Ubuntu 22.04 服务器

副本节点：IP：139.144.169.79 操作系统：Ubuntu 22.04 服务器

第 1 步：安装 PostgreSQL 服务器

第一步是在主节点和副本节点上安装 PostgreSQL。请注意，您需要在两个节点上安装相同版本的 PostgreSQL 才能进行逻辑复制。

在撰写本指南时，最新版本的 PostgreSQL 是 PostgreSQL 14。

首先，通过 SSH 登录到您的服务器并刷新存储库。

sudo apt update

接下来，按如下方式安装 PostgreSQL 数据库服务器。

sudo apt install postgresql postgresql-contrib -y

默认情况下，PostgreSQL 守护程序会在安装后自动运行。您可以使用以下命令验证这一点：

sudo systemctl status postgresql

下面的输出确认 PostgreSQL 服务已启动并正在运行。

此外，考虑使服务在系统启动时启动，如下所示。

sudo systemctl enable postgresql

第二步：配置主节点

接下来，以用户身份登录主节点 (194.195.208.82)，postgres该用户是每次新安装 PostgreSQL 时创建的默认用户。

sudo -u postgres psql

您需要创建一个复制用户，用于从主节点启动复制过程。

因此，运行以下命令创建复制用户并分配复制权限。在此命令中，replica_user 是复制用户，而P@ssword321是用户的密码。请务必提供一个强密码，这与我们使用的纯粹用于演示目的的密码不同。

CREATE ROLE replica_user WITH REPLICATION LOGIN PASSWORD 'P@ssword321';

然后从 PostgreSQL 提示符注销

postgres-# \q

接下来，您需要对主配置文件进行一些调整。使用您喜欢的文本编辑器访问以下配置文件：

sudo vim /etc/postgresql/14/main/postgresql.conf

打开文件后，向下滚动并找到该listen_addresses指令。该指令指定 PostgreSQL 数据库服务器侦听连接的主机。

通过删除#符号取消注释该指令，并替换localhost为用单引号括起来的服务器 IP 地址，如下所示：

接下来，找到该wal_level指令。该设置指定要写入预写日志 (WAL) 文件的信息量。

取消注释该行并将其设置logical为如图所示。

接下来，找到该wal_log_hints指令。默认情况下，它设置为off。

当设置on为该值时，允许 PostgreSQL 服务器在页面的第一次修改期间将每个磁盘页面的全部内容写入 WAL 文件。

取消注释并将其设置为on.

这就是此配置文件中所需更改的全部内容。保存更改并退出。

接下来，访问/etc/postgresql/14/main/pg_hba.conf配置文件。

sudo vim /etc/postgresql/14/main/pg_hba.conf

将此行附加到配置文件的末尾。这允许副本 (139.144.169.79) 使用replica_user.

host  replication   replica_user  139.144.169.79/24   md5

保存更改并关闭文件。重启 PostgreSQL 服务。

sudo systemctl restart postgresql

第三步：配置副本节点

在副本节点可以开始从主节点复制数据之前，您需要创建主节点数据目录到副本数据目录的副本。为此，首先，停止副本节点上的 PostgreSQL 服务。

sudo systemctl stop postgresql

接下来，删除副本数据目录中的所有文件，以便从头开始并为主节点数据目录腾出空间。

sudo rm -rv /var/lib/postgresql/14/main/

现在pg_basebackup 如图所示运行该实用程序，将数据从主节点复制到副本节点。

sudo pg_basebackup -h 194.195.208.82 -U replica_user -X stream -C -S replica_1 -v -R -W -D /var/lib/postgresql/14/main/

让我们看一下命令中使用的选项：

• -h：此选项指定主机，在本例中为主节点的 IP 地址。
• -U：该选项指定复制用户。这是在主节点上配置的用户，副本节点将使用该用户连接到它。在我们的例子中，复制用户被称为replica_user.
• -X：该选项与流值一起指示pg_basebackup实用程序流式传输并将 WAL 文件包含在备份中。
• -C：该选项允许您在备份开始之前创建一个复制槽。该选项与-S指定插槽名称的选项一起使用。在这种情况下，我们的复制槽称为replica_1。
• -v：这会打印出详细的输出，指示从主节点到副本的备份过程的进度。
• -R：该选项创建两个文件；一个名为的空恢复配置文件standby.signal和一个名为的主节点连接设置文件postgresql.auto.conf.该standby.signal文件包含有关主节点的连接信息，该postgresql.auto.conf 文件通知您的副本集群它应该作为备用服务器运行。
• -W：这会提示您为replica_user 复制用户提供密码。
• -D：最后，该-D选项允许您包含要导出备份文件的目录。在这个例子中，我们将数据放在/var/lib/postgresql/14/main/副本节点的目录下。

这是运行复制命令的输出。

之后，在副本上执行以下命令以将数据目录的所有权授予 postgres 用户。

sudo chown postgres -R /var/lib/postgresql/14/main/

现在，启动 PostgreSQL 服务器。副本现在将以热备用模式运行。

sudo systemctl start postgresql

第 4 步：测试复制设置

至此，主节点的数据目录已经成功备份到副本上，副本开始双机热备。

剩下的部分是测试复制是否按预期工作。如果副本在热备模式下成功运行，那么它应该连接到主服务器上的主数据库集群。

要验证副本是否连接到主节点并且主节点正在流式传输，请登录到主服务器并切换到postgres 用户。

sudo -u postgres psql

接下来，查询pg_stat_replication包含有关复制的重要信息的表。在此命令中，我们正在检索有关副本的 IP 地址和主服务器状态的信息。

SELECT client_addr, state
FROM pg_stat_replication;

您应该得到以下输出确认您的设置正在运行。

为了证实这一点，我们将创建一个测试数据库并添加一个包含几条记录的表，然后验证数据库是否已在副本上复制。

我们将创建一个名为students.

CREATE DATABASE students_db;

接下来，切换到数据库。

\c students_db;

输出

You are now connected to database "students_db" as user "postgres".

进入数据库后，创建一个student_details使用以下模式调用的表。

CREATE TABLE student_details (first_name VARCHAR(15), last_name VARCHAR(15) , email VARCHAR(40) );

然后将以下记录插入表中。

INSERT INTO  student_details (first_name, last_name, email)
VALUES  ('Arthur', 'Spencer', 'arthurspencer@gmail.com');

您可以查询表以确认插入的记录，如图所示。

SELECT *
FROM student_details;

现在转到副本节点并切换到postgres 用户。

sudo -u postgres psql

在 shell 上，验证刚才在主节点中创建的数据库是否存在。

postgres=# \l

现在，切换到数据库。

\c students_db;

查询表中存储的所有记录。

SELECT *
FROM student_details;

您应该得到与您在主节点中创建的记录完全相同的记录，如下所示。

这就证明复制成功了！您可以继续在主服务器上添加更多记录，然后将这些记录复制或复制到副本节点。

结论

在本教程中，我们了解了 PostgreSQL 提供的两种复制方法及其优缺点。我们更进一步，使用 PostgreSQL 逻辑模型创建了一个复制集群，并演示了从主节点到副本节点的数据复制。前往官方文档以更全面地了解 PostgreSQL 中的复制。