跨两台服务器的PostgreSQL数据库数据共享实现方法咨询

阿华AIGC实验室

2026-5-20

Got it, let's walk through the best ways to sync your PostgreSQL data from Server-1 to Server-2 over the internet—since both have static IPs, we have solid, reliable options to choose from depending on your sync latency needs:

方案1：原生流复制（Streaming Replication）- 实时/近实时同步

This is PostgreSQL's built-in solution for full database replication, perfect if you need Server-2 to mirror Server-1's data almost instantly.

步骤：

确保版本一致：Server-1和Server-2必须运行完全相同版本的PostgreSQL，版本不兼容会直接导致复制失败。

配置Server-1（主库）：

编辑postgresql.conf（路径随系统不同，比如/var/lib/postgresql/14/main/postgresql.conf）：

wal_level = replica          # 启用复制所需的WAL日志级别
max_wal_senders = 5          # 允许的复制连接数
wal_keep_size = 1GB          # 保留足够的WAL日志，确保从库能追上主库进度
listen_addresses = '*'       # 允许外部IP连接

编辑pg_hba.conf，允许Server-2的静态IP通过复制角色连接：

host    replication     replicator     [Server-2静态IP]/32     scram-sha-256

创建专用的复制用户：

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '你的强密码';

重启Server-1的PostgreSQL服务，让配置生效。

配置Server-2（从库）：

先停止PostgreSQL服务：sudo systemctl stop postgresql
清空现有数据目录（如果有旧数据，建议先备份）：
```
sudo rm -rf /var/lib/postgresql/14/main/*
```

使用pg_basebackup从Server-1拉取初始数据：

sudo -u postgres pg_basebackup -h [Server-1静态IP] -U replicator -D /var/lib/postgresql/14/main -P -X stream

创建standby.signal文件，标记这是一台备用服务器：
```
sudo touch /var/lib/postgresql/14/main/standby.signal
```

编辑Server-2的postgresql.conf：

primary_conninfo = 'host=[Server-1静态IP] port=5432 user=replicator password=你的强密码 application_name=server2_standby'
hot_standby = on             # 允许从库接受只读查询

启动Server-2的PostgreSQL服务：sudo systemctl start postgresql

验证复制状态：
在Server-1上执行以下查询，确认Server-2已连接：
```
SELECT usename, application_name, state FROM pg_stat_replication;
```
结果中能看到replicator用户和server2_standby应用名称，说明复制正常运行。

方案2：逻辑复制（Logical Replication）- 同步特定表/自定义数据

如果你只需要同步Modbus采集的特定表（而非整个数据库），或者Server-2需要在同步数据之外写入自有数据，逻辑复制是更灵活的选择。

步骤：

配置Server-1（发布端）：

编辑postgresql.conf：

wal_level = logical          # 逻辑复制需要的WAL级别
max_replication_slots = 5    # 用于跟踪复制进度的槽位数量
max_wal_senders = 5
listen_addresses = '*'

重启PostgreSQL，然后为目标表（比如device_data）创建发布规则：
```
CREATE PUBLICATION modbus_data_pub FOR TABLE device_data;
```

更新pg_hba.conf，允许Server-2的IP连接到目标数据库：

host    你的数据库名       你的数据库用户       [Server-2静态IP]/32     scram-sha-256

配置Server-2（订阅端）：
- 确保Server-2上已存在目标表device_data（可以手动创建，或用CREATE TABLE ... LIKE同步Server-1的表结构）。
- 创建订阅，拉取Server-1的同步数据：
```
CREATE SUBSCRIPTION modbus_data_sub 
CONNECTION 'host=[Server-1静态IP] port=5432 dbname=你的数据库名 user=你的数据库用户 password=你的数据库密码' 
PUBLICATION modbus_data_pub;
```
验证同步状态：
在Server-2上执行以下查询，查看订阅状态：
```
SELECT subname, substate FROM pg_stat_subscription;
```
状态为streaming表示同步正常。

方案3：定时批量同步 - 非实时（分钟/小时级）

如果不需要即时同步，用pg_dump/pg_restore配合定时任务是简单低耗的选择。

步骤：

创建同步脚本（比如sync_modbus_data.sh）：

#!/bin/bash
# 配置变量
PG_USER="你的数据库用户"
PG_DB="你的数据库名"
SERVER1_IP="Server-1静态IP"
SERVER2_IP="Server-2静态IP"
BACKUP_PATH="/tmp/pg_modbus_dump.dump"

# 从Server-1导出数据
pg_dump -h $SERVER1_IP -U $PG_USER -Fc $PG_DB > $BACKUP_PATH

# 导入到Server-2（-c参数会先清理现有数据）
pg_restore -h $SERVER2_IP -U $PG_USER -d $PG_DB -c $BACKUP_PATH

# 清理临时备份文件
rm $BACKUP_PATH

给脚本添加执行权限：
```
chmod +x sync_modbus_data.sh
```
配置定时任务：
编辑crontab，设置每5分钟执行一次脚本（可根据需求调整频率）：
```
crontab -e
```
添加以下内容：
```
*/5 * * * * /脚本所在路径/sync_modbus_data.sh >> /var/log/pg_sync.log 2>&1
```

互联网环境下的核心安全注意事项

绝对不要直接把PostgreSQL暴露在公网：优先用SSH隧道或VPN加密通信。比如在Server-2上运行以下命令，把本地5432端口映射到Server-1的5432端口：
```
ssh -L 5432:localhost:5432 Server-1的用户名@Server-1静态IP -N
```
之后Server-2连接localhost:5432就等同于连接Server-1的PostgreSQL，无需开放公网端口。
严格限制防火墙规则：如果必须开放5432端口，只允许Server-2的静态IP访问。在Server-1上执行：
```
sudo ufw allow from Server-2静态IP to any port 5432
```
使用强认证方式：pg_hba.conf中务必使用scram-sha-256认证（不要用MD5或信任模式），且密码要足够复杂。
监控同步状态：用Prometheus+Grafana等工具设置告警，或定期检查PostgreSQL内置的状态视图（pg_stat_replication、pg_stat_subscription），避免同步中断无人知晓。