PostgreSQL数据库基于发布订阅的数据同步方案实现

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

1. 在目标服务器上配置

1.1 创建复制用户

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

CREATE ROLE replicator LOGIN PASSWORD 'your_password' SUPERUSER;

将your_password替换为一个强密码。

1.2 配置pg_hba.conf

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

# 添加以下行
host    replication     replicator      192.168.31.60/32        md5
host    replication     replicator      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模式下的所有表。

2.2 配置postgresql.conf

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

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

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

2.3 配置pg_hba.conf

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

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

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

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为复制用户的密码。

4. 监控复制状态

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

SELECT * FROM pg_subscription;

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

SELECT * FROM pg_stat_subscription;

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

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语句，生产环境中请谨慎使用

然后重新加载配置。
请注意，详细的日志记录可能会对性能产生影响，因此在生产环境中应谨慎配置。
完成上述步骤后，你的目标服务器应该开始从源服务器接收增量数据更新。确保定期检查复制状态和日志文件以确保一切正常。