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

介绍

当设置应用程序进行生产时,有时有助于将数据库的多个副本放置在位。保持数据库副本同步的过程称为 replication. Replication 可为大量同时读取操作提供高可用性水平扩展,同时减少读取延迟,并允许地理分布的数据库服务器之间的对等复制。

PostgreSQL是一个开源对象关系数据库系统,高度可扩展和符合 ACID(Atomicity, Consistency, Isolation, Durability)和SQL标准。 PostgreSQL 10.0 版本引入了对 logical replication 的支持,除了 physical replication 之外。在一个逻辑复制方案中,高级写作操作从 master 数据库服务器中流到一个或多个 replica database 服务器中。在一个物理复制方案中,二进制写作操作被流从大师到复制,产生原始内容的对比精确副本。在您想要针对特定数据子集,如卸载报告、补丁或升级的情况下,逻辑复制可以提供速度和灵活性

在本教程中,您将在两个 Ubuntu 18.04 服务器上配置 PostgreSQL 10 的逻辑复制,其中一个服务器作为主,另一个作为复制。

前提条件

要遵循本教程,您将需要:

步骤 1 — 配置 PostgreSQL 用于逻辑复制

您需要修改几种配置设置,以便在服务器之间实现逻辑复制。 首先,您将配置 Postgres 以便在私人网络接口上聆听,而不是在公共网络上,因为在公共网络上暴露数据是一种安全风险。

在 db-master中,打开 /etc/postgresql/10/main/postgresql.conf,即主服务器配置文件:

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

找下面的线条:

1[label /etc/postgresql/10/main/postgresql.conf]
2...
3#listen_addresses = 'localhost'         # what IP address(es) to listen on;
4...

删除#并添加db_master_private_ip_address以启用私人网络上的连接:

<$>[注] 注: 在此步骤和以下步骤中,请确保您使用您的服务器的 私人 IP 地址,而不是他们的公共 IP。

1[label /etc/postgresql/10/main/postgresql.conf]
2...
3listen_addresses = 'localhost, db_master_private_ip_address'
4...

这使得 db-master听取在私人网络上的接入连接,除了循环接口。

接下来,找到下面的线条:

1[label /etc/postgresql/10/main/postgresql.conf]
2...
3#wal_level = replica                    # minimal, replica, or logical
4...

删除它,并更改它以将 PostgreSQL Write Ahead Log (WAL) 级别设置为逻辑性。

1[label /etc/postgresql/10/main/postgresql.conf]
2...
3wal_level = logical
4...

此日志上的条目将被复制服务器消耗,允许从主机复制高级写作操作。

保存文件并关闭它。

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

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

在最后一行之后,让我们添加一行,允许来自 db-replica的入口网络连接。

1[label /etc/postgresql/10/main/pg_hba.conf]
2...
3# TYPE DATABASE USER ADDRESS METHOD
4...
5host all all db_replica_private_ip_address/32 md5

现在将允许从 db-replica接入网络连接,通过密码哈希((md5))(https://en.wikipedia.org/wiki/MD5)进行身份验证。

保存文件并关闭它。

接下来,让我们将我们的防火墙规则设置为允许从 db-replica到port `5432在 db-master上的流量:

1sudo ufw allow from db_replica_private_ip_address to any port 5432

最后,重新启动 PostgreSQL 服务器,以便更改生效:

1sudo systemctl restart postgresql

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

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

要测试复制设置的功能,让我们创建一个数据库、表和用户角色. 您将创建一个示例数据库与示例表,然后您可以使用它来测试服务器之间的逻辑复制。

首先,打开 psql 提示作为 postgres 用户,在 db-master 和 db-replica 上使用以下命令:

1sudo -u postgres psql

1[environment second]
2sudo -u postgres psql

在两个主机上创建一个名为示例的新数据库:

1CREATE DATABASE example;

1[environment second]
2CREATE DATABASE example;

<$>[注] **注: **在这些命令中,必须有最后的 ;。在交互式会话中,PostgreSQL 不会执行 SQL 命令,直到你用半字符号终止它们。Meta 命令(以 backslash 开始的命令,如 q和 c)直接控制 psql 客户端本身,因此不受此规则的约束。 有关 meta-命令和 psql 客户端的更多信息,请参阅 PostgreSQL 文档。 <$>

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

1\c example

1[environment second]
2\c example

创建一个名为widgets的新表,在两个主机上使用任意字段:

1CREATE TABLE widgets
2(
3    id SERIAL,
4    name TEXT,
5    price DECIMAL,
6    CONSTRAINT widgets_pkey PRIMARY KEY (id)
7);

1[environment second]
2CREATE TABLE widgets
3(
4    id SERIAL,
5    name TEXT,
6    price DECIMAL,
7    CONSTRAINT widgets_pkey PRIMARY KEY (id)
8);

db-replica上的表格不一定与其 db-master对称相同,但是,它必须包含在 db-master的表中存在的每个单一列。

在 db-master上,让我们创建一个新的用户角色,使用复制选项和登录密码。复制属性必须分配给用于复制的任何角色。我们将我们的用户称为sammy,但您可以用自己的用户名代替它。

1CREATE ROLE sammy WITH REPLICATION LOGIN PASSWORD 'my_password';

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

仍然在 db-master上,向您刚刚创建的用户角色授予示例数据库上的完整权限:

1GRANT ALL PRIVILEGES ON DATABASE example TO sammy;

接下来,向您的用户授予数据库中包含的所有表的权限:

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

public 方案是每个数据库中自动放置表的默认方案。

有了这些特权设置,您现在可以继续为复制提供示例数据库中的表。

步骤三:创建出版物

Publications 是 PostgreSQL 使用的机制,使表可用于复制. 数据库服务器将内部跟踪与特定出版物相关联的任何复制服务器的连接和复制状态. 在 db-master 上,您将创建一个出版物, my_publication,该出版物将作为将发送给您的 subscribers 的数据的主副本 - 在我们的情况下, db-replica。

在 db-master 上,创建一个名为my_publication的出版物:

1CREATE PUBLICATION my_publication;

添加您之前创建的widgets表:

1ALTER PUBLICATION my_publication ADD TABLE widgets;

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

步骤4:创建订阅

Subscriptions 被 PostgreSQL 用来连接到现有出版物。 一个出版物可以在不同的复制服务器上拥有多个订阅,而复制服务器也可以与订阅者拥有自己的出版物。

在 db-replica上,让我们创建一个名为my_subscription的订阅。CREATE SUBSCRIPTION命令将命名订阅,而CONNECTION参数将定义与出版商的连接字符串。这个字符串将包含主服务器的连接细节和登录凭证,包括您之前定义的用户名和密码,以及示例数据库的名称。再一次,请记住使用 db-master的私人IP地址,并用自己的密码替换my_password:

1[environment second]
2CREATE SUBSCRIPTION my_subscription CONNECTION 'host=db_master_private_ip_address port=5432 password=my_password user=sammy dbname=example' PUBLICATION my_publication;

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

1[secondary_label Output]
2[environment second]
3NOTICE:  created replication slot "my_subscription" on publisher
4CREATE SUBSCRIPTION

在创建订阅时,PostgreSQL 会自动将任何现有数据从主机同步到复制件中。在我们的情况下,没有数据要同步,因为widgets表是空的,但在添加新订阅到现有数据库时,这是一个有用的功能。

有了订阅,让我们通过在widgets表中添加一些演示数据来测试设置。

步骤5:测试和故障排除

为了测试我们的主人和复制之间的复制,让我们在widgets表中添加一些数据,并验证它是否正确复制。

在 db-master上,在widgets表中插入以下数据:

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

在 db-replica上,运行以下查询以获取本表中的所有条目:

1[environment second]
2SELECT * FROM widgets;

你现在应该看到:

1[secondary_label Output]
2[environment second]
3 id |    name    | price 
4----+------------+-------
5  1 | Hammer     |  4.50
6  2 | Coffee Mug |  6.20
7  3 | Cupholder  |  3.80
8(3 rows)

成功!从 db-master 成功复制到 db-replica. 从现在开始,所有INSERT、UPDATE和DELETE查询将单向复制到服务器上。

要注意在复制服务器上写查询的一点是,它们不会被复制回主服务器。 PostgreSQL 目前对在服务器之间数据差异时解决冲突的支持有限。如果存在冲突,复制将停止,而 PostgreSQL 将等到数据库管理员手动解决问题。

您现在可以在两个服务器上退出psql提示:

1\q

1[environment second]
2\q

现在你已经完成了测试设置,你可以自己添加和复制数据。

故障解析

如果复制似乎不起作用,一个很好的第一步是检查在 db-replica上的PostgreSQL日志,查找可能出现的错误:

1[environment second]
2tail /var/log/postgresql/postgresql-10-main.log

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

*私人网络在两台服务器上都未启用,或者服务器位于不同的网络上;

  • db-master未配置为在正确的私人网络 IP 地址上收听连接;
  • UFW 上的 Write Ahead Log 级别配置错误(必须设置为逻辑);
  • db-master未配置为接受正确的 db-replica私人 IP 地址的入口连接;
  • UFW 等防火墙在端口上阻止入口的 PostgreSQL 连接;
  • 公共 **db-master 和 db-replica 之间存在不匹配的表名或字段;
  • 数据库中的`宽

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

1[environment second]
2DROP SUBSCRIPTION my_subscription;

结论

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

您现在拥有必要的知识,可以通过添加额外的复制服务器来实验水平阅读扩展、高可用性和PostgreSQL数据库的地理分布。

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

Published At
Categories with 技术
Tagged with
comments powered by Disqus