PostgreSQL发布订阅模式实现数据实时同步

• 1.1 创建复制用户
• 1.2 配置pg_hba.conf
• 1.3 创建数据库和模式
• 2.1 创建发布
• 2.2 配置postgresql.conf
• 2.3 配置pg_hba.conf
• 4.1. 详解pg_subscription表信息
• 4.2. 详解pg_stat_subscription表信息

要实现从两台源服务器（192.168.31.60和192.168.31.82）到目标服务器（192.168.31.118）的PostgreSQL数据增量备份，我们将使用PostgreSQL的逻辑复制功能。以下是详细的步骤和配置：

0. 服务器规划

名称	IP地址
60源服务器（发布端）	192.168.31.60
82源服务器（发布端）	192.168.31.82
118目标服务器（订阅端）	192.168.31.118

以上3台服务器配置如下：

root@node1921683160:/mnt/software# uname -a
Linux node1921683160 5.4.18-52-generic #41 SMP Wed Mar 2 15:24:07 CST 2022 aarch64 aarch64 aarch64 GNU/Linux

操作系统为银河麒麟V10操作系统。

1. 在目标服务器上配置

1.1 创建复制用户

首先，在目标服务器上创建一个用于复制的用户。登录到目标服务器的PostgreSQL，并执行以下命令：

CREATE ROLE postgresdba LOGIN PASSWORD 'your_password' SUPERUSER;

将your_password替换为一个强密码。

1.2 配置pg_hba.conf

编辑目标服务器的pg_hba.conf文件，允许复制用户从源服务器连接：

# 添加以下行
host    replication     postgresdba      192.168.31.60/32        md5
host    replication     postgresdba      192.168.31.82/32        md5

然后，重新加载PostgreSQL配置或重启服务。

1.3 创建数据库和模式

如果electric数据库和模式尚不存在，请在目标服务器上创建它们：

CREATE DATABASE electric;
\c electric
CREATE SCHEMA electric;

2. 在源服务器上配置

对于每一台源服务器（192.168.31.60和192.168.31.82），执行以下步骤：

2.1 创建发布

在PostgreSQL 10及以上版本中，逻辑复制使用“发布”和“订阅”模型。首先，在源服务器上创建一个发布：

-- 在每个源数据库上执行
CREATE PUBLICATION electric_pub FOR ALL TABLES IN electric.electric;

这里我们假设你想要复制electric模式下的所有表。 PostgreSQL不支持ALL TABLES语法，我们可以使用下面的脚本来进行创建：

DO 
$$
  
DECLARE  
    table_name text;  
    publication_query text := 'CREATE PUBLICATION electric_pub FOR TABLE ';  
BEGIN  
    FOR table_name IN (SELECT tablename FROM pg_tables WHERE schemaname = 'electric') LOOP  
        publication_query := publication_query || 'electric.' || table_name || ', ';  
    END LOOP;  
    -- Remove the last comma and space  
    publication_query := rtrim(publication_query, ', ');  
    EXECUTE publication_query;  
END 
$$
;

上面的脚本表示electric库中所有的表都要复制。

2.2 配置postgresql.conf

确保以下设置已配置以启用逻辑复制：

wal_level = logical               # 确保WAL日志级别为logical
max_replication_slots = 4         # 根据需要调整复制槽的数量
max_wal_senders = 4               # 根据需要调整WAL发送进程的数量
track_commit_timestamp = on

修改配置后，重新加载PostgreSQL配置或重启服务。

2.3 配置pg_hba.conf

允许复制连接到源数据库。编辑pg_hba.conf文件：

# 允许目标服务器进行复制连接
host    replication     postgresdba      192.168.31.118/32       md5

确保使用之前为postgresdba角色设置的密码，并重新加载配置。

postgresdba必须要拥有REPLICATION权限。你可以使用下面的SQL进行赋权。

ALTER ROLE postgresdba WITH REPLICATION;

3. 在目标服务器上设置订阅

在目标服务器上，为每一个源服务器创建一个订阅：

-- 订阅192.168.31.60的数据
CREATE SUBSCRIPTION electric_sub_60
CONNECTION 'host=192.168.31.60 port=5432 user=replicator password=your_password dbname=electric'
PUBLICATION electric_pub;

-- 订阅192.168.31.82的数据
CREATE SUBSCRIPTION electric_sub_82
CONNECTION 'host=192.168.31.82 port=5432 user=replicator password=your_password dbname=electric'
PUBLICATION electric_pub;

替换your_password为复制用户的密码。

如果报错ERROR: schema "electric" does not exist，则根据1.3进行schema创建。

4. 监控复制状态

在目标服务器上，可以使用以下查询来监控复制的状态：

SELECT * FROM pg_subscription;

或者查看更详细的复制状态：

SELECT * FROM pg_stat_subscription;

这将显示每个订阅的状态，包括是否正在接收数据、最后的接收时间等。

4.1. 详解pg_subscription表信息

pg_subscription 是 PostgreSQL 中的一个系统视图，用于显示关于数据库订阅的信息。订阅是 PostgreSQL 逻辑复制功能的一部分，允许一个数据库（订阅者）从另一个数据库（发布者）接收数据更改。

现在，我将详细解释你给出的 SELECT * FROM pg_subscription; 查询的结果：

1. oid: 这是订阅的内部对象ID，是一个唯一的标识符，用于在数据库内部跟踪该订阅。在你的例子中，这个值是 23290。
2. subdbid: 表示订阅所在的数据库的 OID。这里的 16385 是该数据库的内部标识符。
3. subskiplsn: 这是一个逻辑序列号（LSN），表示订阅开始时跳过的日志位置。0/0 通常表示从头开始接收更改。
4. subname: 订阅的名称。在你的例子中，它是 electric_sub。
5. subowner: 表示订阅所有者的用户ID。这里的 10 是该用户的内部ID。
6. subenabled: 表示订阅是否启用。t 表示订阅是启用的。
7. subbinary: 表示是否以二进制格式传输数据。f 表示数据不是以二进制格式传输的。
8. substream: 表示是否使用了子流复制。f 表示没有使用。
9. subtwophasestate: 表示两阶段提交的状态。d 可能表示“disabled”或某种默认状态（具体取决于 PostgreSQL 的版本和文档）。
10. subdisableonerr: 表示在出现错误时是否禁用订阅。f 表示在错误发生时不会禁用订阅。
11. subpasswordrequired: 表示连接发布者时是否需要密码。t 表示需要密码。
12. subrunasowner: 表示是否以订阅所有者的身份运行复制进程。f 表示不是以订阅所有者的身份运行。
13. subconninfo: 这是连接到发布者所需的连接字符串，包含如主机名、端口、用户名、密码和数据库名等信息。在你的例子中，它连接到 192.168.31.60 上的 electric 数据库，使用用户名 postgresdba 和密码 Rsc@123456。
14. subslotname: 复制槽的名称，用于逻辑复制。在你的例子中，它是 electric_sub。
15. subsynccommit: 表示提交事务时是否等待复制完成。off 表示不等待。
16. subpublications: 表示此订阅关联的发布名称。在你的例子中，它订阅了名为 electric_pub 的发布。
17. suborigin: 表示订阅的来源。any 通常表示可以接受来自任何来源的更新。

这个查询结果为你提供了关于 PostgreSQL 数据库中当前订阅的详细信息，这有助于你监控和管理逻辑复制的设置和状态。

4.2. 详解pg_stat_subscription表信息

pg_stat_subscription 是 PostgreSQL 提供的一个视图，用于查看订阅的统计信息。以下是对 pg_stat_subscription 查询结果中各列的详细解释，基于你提供的查询结果：

1. subid: 订阅的ID，是一个内部标识符，用于唯一标识数据库中的每个订阅。在你的查询结果中，subid 是 23290。
2. subname: 订阅的名称。这是你为订阅指定的名称，在你的例子中，订阅的名称是 electric_sub。
3. pid: 是 PostgreSQL 后台工作进程的进程ID，该进程负责处理这个订阅的复制。在你的结果中，pid 是 1119690。这意味着有一个 PostgreSQL 进程正在运行，并处理与订阅 electric_sub 相关的复制任务。
4. leader_pid: 如果订阅使用了逻辑复制的流复制功能，并且当前数据库是流复制组中的非领导者，则此列将显示领导者的进程ID。在你的情况下，这一列是空的，表明当前数据库可能是单个数据库或流复制组的领导者。
5. relid: 如果订阅当前正在复制特定表的数据，则此列将显示该表的 OID。在你的结果中，这一列是空的，可能表明订阅是针对整个数据库的，而不是特定的表。
6. received_lsn: 表示订阅者最近接收到的日志序列号（LSN）。LSN 用于标识在 PostgreSQL WAL（Write-Ahead Logging）中的特定位置。在你的结果中，received_lsn 是 C/5D844460，这表明订阅者已经接收到了这个 LSN 的数据。
7. last_msg_send_time: 表示发布者最近一次向订阅者发送消息的时间。在你的结果中，这个消息发送时间是 2024-06-06 14:53:32.863262+08。
8. last_msg_receipt_time: 表示订阅者最近一次确认接收到消息的时间。在你的结果中，这个消息接收时间是 2024-06-06 14:53:32.867661+08。
9. latest_end_lsn: 表示订阅者当前已经处理或应用的 WAL 数据的最新 LSN。在你的结果中，latest_end_lsn 也是 C/5D844460，与 received_lsn 相同，这可能表明订阅者正在实时处理接收到的数据，没有延迟。
10. latest_end_time: 表示订阅者处理或应用最新 WAL 数据的时间。在你的结果中，这个时间是 2024-06-06 14:53:32.863262+08，与 last_msg_send_time 相同，进一步表明数据的实时处理。

综上所述，你的查询结果表明订阅 electric_sub 正在正常运行，数据正在从发布者实时复制到订阅者，且没有明显的延迟。

5. 查看日志

要查看PostgreSQL的日志，你可以检查PostgreSQL的日志文件，通常位于PostgreSQL数据目录下的pg_log文件夹中。你也可以通过配置logging_collector、log_directory、log_filename和log_statement等参数来自定义日志记录。

例如，要启用所有SQL语句的日志记录，你可以在postgresql.conf中设置：

logging_collector = on
log_directory = '/var/log/postgresql/'
log_filename = 'postgresql.log'
log_statement = 'all'  # 记录所有SQL语句，生产环境中请谨慎使用

然后重新加载配置。