介绍
在为生产设置应用程序时，准备好多个数据库副本通常很有用。 保持数据库副本同步的过程称为复制 。 复制可以为大量同时读取操作提供高可用性水平扩展，同时减少读取延迟。 它还允许在地理上分布的数据库服务器之间进行对等复制。
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 Write Ahead Log （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文档 。
使用\connect meta-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;
您将看到以下输出确认订阅：
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和CREATE SUBSCRIPTION命令的手册条目。
