如何在Ubuntu18.04上使用PostgreSQL10设置逻辑复制

介绍

在为生产设置应用程序时，拥有多个数据库副本通常很有用。保持数据库副本同步的过程称为复制。复制可以为大量同时读取操作提供高可用性水平扩展，同时减少读取延迟。它还允许在地理上分布的数据库服务器之间进行对等复制。

PostgreSQL 是一个开源的对象关系数据库系统，具有高度可扩展性，符合 ACID（原子性、一致性、隔离性、持久性）和 SQL 标准。除了 物理复制 之外，PostgreSQL 10.0 版还引入了对 逻辑复制 的支持。在逻辑复制方案中，高级写操作从主数据库服务器流式传输到一个或多个副本数据库服务器。在物理复制方案中，二进制写入操作改为从主服务器流式传输到副本，从而生成原始内容的逐字节精确副本。如果您希望针对特定的数据子集（例如卸载报告、修补或升级），逻辑复制可以提供速度和灵活性。

在本教程中，您将在两台 Ubuntu 18.04 服务器上使用 PostgreSQL 10 配置逻辑复制，其中一台服务器充当主服务器，另一台服务器充当副本。在本教程结束时，您将能够使用逻辑复制将数据从主服务器复制到副本。

先决条件

要遵循本教程，您将需要：

两台 Ubuntu 18.04 服务器，我们将其命名为 db-master 和 db-replica，每台服务器都设置了普通用户帐户和 sudo 权限。要设置这些，请按照这个初始服务器设置教程。
在您的服务器上启用私有网络。专用网络允许您的服务器之间进行通信，而不会带来与将数据库公开到公共互联网相关的安全风险。
按照如何在 Ubuntu 18.04 上安装和使用 PostgreSQL 的第 1 步，在两台服务器上安装 PostgreSQL 10。

第 1 步 — 为逻辑复制配置 PostgreSQL

您需要修改几个配置设置以启用服务器之间的逻辑复制。首先，您将配置 Postgres 以侦听专用网络接口而不是公共接口，因为通过公共网络公开数据存在安全风险。然后，您将配置适当的设置以允许复制到 db-replica。

在db-master上，打开/etc/postgresql/10/main/postgresql.conf，主服务器配置文件：

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

找到以下行：

/etc/postgresql/10/main/postgresql.conf

...
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
...

通过删除 # 取消注释，并添加您的 db_master_private_ip_address 以启用专用网络上的连接：

注意： 在此步骤和后续步骤中，请确保使用服务器的私有 IP 地址，而不是它们的公共 IP。将数据库服务器暴露给公共互联网是一个相当大的安全风险。

/etc/postgresql/10/main/postgresql.conf

...
listen_addresses = 'localhost, db_master_private_ip_address'
...

这使得 db-master 除了环回接口外，还侦听专用网络上的传入连接。

接下来，找到以下行：

/etc/postgresql/10/main/postgresql.conf

...
#wal_level = replica                    # minimal, replica, or logical
...

取消注释，并将其更改为将 PostgreSQL Write Ahead Log (WAL) 级别设置为 logical。这增加了日志中的条目量，添加了提取差异或更改特定数据集的必要信息：

/etc/postgresql/10/main/postgresql.conf

...
wal_level = logical
...

此日志上的条目将由副本服务器使用，从而允许从主服务器复制高级写操作。

保存文件并关闭它。

接下来，让我们编辑 /etc/postgresql/10/main/pg_hba.conf，该文件控制允许的主机、身份验证和对数据库的访问：

sudo nano /etc/postgresql/10/main/pg_hba.conf

在最后一行之后，让我们添加一行以允许来自 db-replica 的传入网络连接。我们将使用 db-replica 的私有 IP 地址，并指定允许来自所有用户和数据库的连接：

/etc/postgresql/10/main/pg_hba.conf

...
# TYPE      DATABASE        USER            ADDRESS                               METHOD
...
host         all            all             db_replica_private_ip_address/32      md5

现在将允许来自 db-replica 的传入网络连接，并通过密码哈希 (md5) 进行身份验证。

保存文件并关闭它。

接下来，让我们设置防火墙规则以允许从 db-replica 到 db-master 上的端口 5432 的流量：

sudo ufw allow from db_replica_private_ip_address to any port 5432

最后，重新启动 PostgreSQL 服务器以使更改生效：

sudo systemctl restart postgresql

将配置设置为允许逻辑复制后，您现在可以继续创建数据库、用户角色和表。

第 2 步 — 设置数据库、用户角色和表

为了测试您的复制设置的功能，让我们创建一个数据库、表和用户角色。您将创建一个带有示例表的 example 数据库，然后您可以使用它来测试服务器之间的逻辑复制。您还将创建一个专用用户并为他们分配数据库和表的权限。

首先，在 db-master 和 db-replica 上使用以下命令以 postgres 用户身份打开 psql prompt：

sudo -u postgres psql

sudo -u postgres psql

在两台主机上创建一个名为 example 的新数据库：

CREATE DATABASE example;

CREATE DATABASE example;

注：这些命令中最后的;是必需的。在交互式会话中，PostgreSQL 不会执行 SQL 命令，直到您用分号终止它们。元命令（以反斜杠开头的命令，如 \q 和 \c）直接控制 psql 客户端本身，因此不受此规则的约束。有关元命令和 psql 客户端的更多信息，请参阅 PostgreSQL 文档。

使用 \connect 元命令，连接到您刚刚在每个主机上创建的数据库：

\c example

\c example

在两个主机上创建一个名为 widgets 的新表，其中包含任意字段：

CREATE TABLE widgets
(
    id SERIAL,
    name TEXT,
    price DECIMAL,
    CONSTRAINT widgets_pkey PRIMARY KEY (id)
);

CREATE TABLE widgets
(
    id SERIAL,
    name TEXT,
    price DECIMAL,
    CONSTRAINT widgets_pkey PRIMARY KEY (id)
);

db-replica 上的表不需要与其对应的 db-master 相同。但是，它必须包含 db-master 表上的每一列。附加列不得具有 NOT NULL 或其他约束。如果他们这样做，复制将失败。

在 db-master 上，让我们使用 REPLICATION 选项和登录密码创建一个新用户角色。 REPLICATION 属性必须分配给用于复制的任何角色。我们将调用我们的用户 sammy，但您可以将其替换为您自己的用户名。确保也将 my_password 替换为您自己的安全密码：

CREATE ROLE sammy WITH REPLICATION LOGIN PASSWORD 'my_password';

记下您的密码，因为稍后您将在 db-replica 上使用它来设置复制。

仍然在 db-master 上，将 example 数据库的完全权限授予您刚刚创建的用户角色：

GRANT ALL PRIVILEGES ON DATABASE example TO sammy;

接下来，将数据库中包含的所有表的权限授予您的用户：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sammy;

公共模式是每个数据库中的默认模式，表会自动放入其中。

设置了这些权限后，您现在可以继续使 example 数据库中的表可用于复制。

第 3 步 — 设置出版物

Publications 是 PostgreSQL 用来使表可用于复制的机制。数据库服务器将在内部跟踪与给定发布关联的任何副本服务器的连接和复制状态。在 db-master 上，您将创建一个发布 my_publication，它将作为将发送给您的 订阅者 的数据的主副本 — 在我们的案例，db-replica。

在 db-master 上，创建一个名为 my_publication 的发布：

CREATE PUBLICATION my_publication;

将您之前创建的 widgets 表添加到其中：

ALTER PUBLICATION my_publication ADD TABLE widgets;

有了您的出版物，您现在可以添加一个订阅者来从中提取数据。

第 4 步 — 创建订阅

PostgreSQL 使用 Subscriptions 连接到现有发布。一个发布可以在不同的副本服务器上拥有多个订阅，并且复制服务器也可以拥有自己的带有订阅者的发布。要访问您在 db-master 上创建的表中的数据，您需要创建对在上一步中创建的发布 my_publication 的订阅。

在 db-replica 上，让我们创建一个名为 my_subscription 的订阅。 CREATE SUBSCRIPTION 命令将命名订阅，而 CONNECTION 参数将定义到发布者的连接字符串。此字符串将包括主服务器的连接详细信息和登录凭据，包括您之前定义的用户名和密码，以及 example 数据库的名称。再次提醒，请记住使用 db-master 的私有 IP 地址，并将 my_password 替换为您自己的密码：

CREATE SUBSCRIPTION my_subscription CONNECTION 'host=db_master_private_ip_address port=5432 password=my_password user=sammy dbname=example' PUBLICATION my_publication;

您将看到以下确认订阅的输出：

OutputNOTICE:  created replication slot "my_subscription" on publisher
CREATE SUBSCRIPTION

创建订阅后，PostgreSQL 会自动将任何预先存在的数据从主服务器同步到副本。在我们的例子中，由于 widgets 表是空的，因此没有要同步的数据，但是在向现有数据库添加新订阅时，这是一个有用的功能。

订阅到位后，让我们通过将一些演示数据添加到 widgets 表来测试设置。

第 5 步 — 测试和故障排除

为了测试我们的主副本和副本之间的复制，让我们将一些数据添加到 widgets 表并验证它是否正确复制。

在 db-master 上，在 widgets 表中插入以下数据：

INSERT INTO widgets (name, price) VALUES ('Hammer', 4.50), ('Coffee Mug', 6.20), ('Cupholder', 3.80);

在 db-replica 上，运行以下查询以获取此表上的所有条目：

SELECT * FROM widgets;

您现在应该看到：

Output id |    name    | price 
----+------------+-------
  1 | Hammer     |  4.50
  2 | Coffee Mug |  6.20
  3 | Cupholder  |  3.80
(3 rows)

成功！条目已成功从 db-master 复制到 db-replica。从现在开始，所有 INSERT、UPDATE 和 DELETE 查询将在服务器之间单向复制。

关于副本服务器上的写入查询需要注意的一件事是它们不会复制回主服务器。当服务器之间的数据出现分歧时，PostgreSQL 目前对解决冲突的支持有限。如果存在冲突，复制将停止，PostgreSQL 将等待数据库管理员手动修复问题。出于这个原因，大多数应用程序会将所有写入操作定向到主服务器，并在可用的副本服务器之间分配读取。

您现在可以在两台服务器上退出 psql 提示：

\q

\q

现在您已经完成了对设置的测试，您可以自行添加和复制数据。

故障排除

如果复制似乎不起作用，那么好的第一步是检查 db-replica 上的 PostgreSQL 日志是否存在任何可能的错误：

tail /var/log/postgresql/postgresql-10-main.log

以下是一些可能阻止复制工作的常见问题：

两台服务器都没有启用私有网络，或者服务器在不同的网络上；
db-master 未配置为侦听正确私网 IP 上的连接；
db-master 上的 Write Ahead Log 级别配置错误（必须设置为 logical）；
db-master 未配置为接受来自正确 db-replica 私有 IP 地址的传入连接；
像 UFW 这样的防火墙正在阻止端口 5432 上的传入 PostgreSQL 连接；
db-master 和 db-replica 之间存在不匹配的表名或字段；
sammy 数据库角色缺少访问 db-master 上的 example 数据库所需的权限；
sammy 数据库角色缺少 db-master 上的 REPLICATION 选项；
sammy 数据库角色缺少访问 db-master 上的 widgets 表所需的权限；
该表未添加到 db-master 上的发布中。

解决现有问题后，应自动进行复制。如果没有，请在重新创建之前使用以下命令删除现有订阅：

DROP SUBSCRIPTION my_subscription;

结论

在本教程中，您已在两台 Ubuntu 18.04 服务器上成功安装了 PostgreSQL 10，并在它们之间配置了逻辑复制。

通过添加额外的副本服务器，您现在拥有了试验水平读取扩展、高可用性和 PostgreSQL 数据库的地理分布所需的知识。

要了解有关 PostgreSQL 10 中逻辑复制的更多信息，您可以阅读 PostgreSQL 官方文档中关于主题的章节，以及 CREATE PUBLICATION 和上的手册条目创建订阅命令。