如何在Ubuntu 18.04上使用PostgreSQL 10设置逻辑复制

介绍

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

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

找到以下行：

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

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

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

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

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

接下来，找到以下行：

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

取消注释，并将其更改为将PostgreSQL 预写日志（WAL）级别设置为logical。这会增加日志中的条目量，添加必要的信息以提取差异或更改特定数据集：

...
wal_level = logical
...

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

保存文件并关闭它。

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

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

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

...
# 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提示：

sudo -u postgres psql

sudo -u postgres psql

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

CREATE DATABASE example;

CREATE DATABASE example;

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

使用\connectmeta-command连接到您在每个主机上创建的数据库：

\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;

该public模式是在每个数据库中的默认架构成表自动放置。

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

第3步 - 设置发布

发布是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使用订阅来连接到现有的发布。一个发布可以在不同的副本服务器上有许多订阅，副本服务器也可以拥有自己的订阅者发布。要从您在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;

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

NOTICE:  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;

你现在应该看到：

 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数据库角色缺少必要的权限来访问example数据库DB-主 ;
• 该sammy数据库角色缺少REPLICATION期权DB-主 ;
• 该sammy数据库角色缺少必要的权限来访问widgets表DB-主 ;
• 该表未添加到db-master上的发布中。

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

DROP SUBSCRIPTION my_subscription;

结论

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

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

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

想要了解更多关于使用PostgreSQL 10设置逻辑复制的相关教程，请前往腾讯云+社区学习更多知识。

参考文献：《How To Set Up Logical Replication with PostgreSQL 10 on Ubuntu 18.04》