Ubuntu PostgreSQL数据同步策略

Ubuntu环境下PostgreSQL数据同步策略

一、基础架构：主从复制（Master-Slave Replication）

主从复制是PostgreSQL最核心的同步策略，通过WAL（Write-Ahead Logging）日志实现主库数据向备库的传输，分为异步复制（默认）和同步复制两种模式，适用于高可用、读写分离等场景。

1. 主库配置（192.168.1.10）

安装PostgreSQL：通过sudo apt-get install postgresql-15 postgresql-contrib安装最新稳定版（以15为例）。

修改postgresql.conf：调整以下关键参数，启用复制功能：

listen_addresses = '*'  # 监听所有IP地址
wal_level = replica     # 设置WAL级别为replica（支持复制）
max_wal_senders = 10    # 允许最多10个复制连接
wal_keep_size = 1024    # 保留WAL日志大小（MB），避免备库断开后丢失日志
archive_mode = on       # 开启归档模式（可选，用于长期保存WAL）
archive_command = 'test !-f /pg_archive/%f && cp %p /pg_archive/%f'  # 归档命令（可选）

修改pg_hba.conf：允许备库IP连接主库进行复制：
```
host replication repl_user 192.168.1.20/32 md5
```
重启PostgreSQL使配置生效：sudo systemctl restart postgresql-15。

创建复制用户：在主库中执行SQL，创建专用于复制的用户（密码需强度高）：

CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'YourStrongPassword123!' CONNECTION LIMIT 1;

2. 备库配置（192.168.1.20）

停止PostgreSQL服务：sudo systemctl stop postgresql-15。
清空数据目录：删除备库原有数据（首次配置需执行）：
```
rm -rf /var/lib/pgsql/15/data/*
```
拉取主库数据：使用pg_basebackup工具从主库同步数据（推荐-R参数自动生成standby配置）：
```
sudo -u postgres pg_basebackup -h 192.168.1.10 -U repl_user -D /var/lib/pgsql/15/data/ -Fp -Xs -P -R
```
若未使用-R，需手动创建standby.signal文件（touch /var/lib/pgsql/15/data/standby.signal）并配置postgresql.auto.conf：
```
primary_conninfo = 'host=192.168.1.10 port=5432 user=repl_user password=YourStrongPassword123!'
```
启动备库：sudo systemctl start postgresql-15。

3. 验证复制状态

主库查看复制情况：执行SQL查询pg_stat_replication视图，确认备库是否连接并同步：
```
SELECT application_name, client_addr, state, sync_state 
FROM pg_stat_replication;
```
正常结果应显示state=streaming（正在流式传输）、sync_state=async（异步同步）。
备库验证恢复状态：执行SQL确认备库处于恢复模式（只读）：
```
SELECT pg_is_in_recovery();  -- 返回t表示备库正常
```

二、高可用增强：自动故障转移与集群管理

为避免主库宕机导致服务中断，需引入自动故障转移和集群管理工具，提升系统可靠性。

1. 工具选择

Patroni：开源的PostgreSQL高可用管理工具，支持自动故障转移、配置管理，可与etcd、Consul等协调服务集成。
Pgpool-II：提供连接池、负载均衡、故障转移功能，适合读多写少的场景。
repmgr：专注于复制和故障转移的工具，简化备库提升为主库的操作。

2. Patroni配置示例（主库192.168.1.10）

安装Patroni：sudo apt-get install patroni。

创建配置文件：/etc/patroni.yml（以etcd为协调服务）：

scope: postgres_cluster
namespace: /db/
name: postgres-primary

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.10:8008

etcd:
  hosts:
    - 192.168.1.10:2379
    - 192.168.1.20:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        max_wal_senders: 10
        wal_keep_size: 1024
        hot_standby: on
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8
  pg_hba:
    - host replication repl_user 192.168.1.20/32 md5
    - host all all 0.0.0.0/0 md5

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.10:5432
  data_dir: /var/lib/pgsql/15/data
  bin_dir: /usr/pgsql-15/bin
  authentication:
    replication:
      username: repl_user
      password: YourStrongPassword123!
    superuser:
      username: postgres
      password: YourSuperUserPassword123!
  parameters:
    unix_socket_directories: '/var/run/postgresql'

tags:
  nofailover: false
  noloadbalance: false

启动Patroni：sudo patroni /etc/patroni.yml。

3. 故障转移流程

主库宕机：Patroni检测到主库无响应，自动触发故障转移，选择一个健康备库提升为新主库。
客户端切换：应用通过Patroni的REST API获取新主库地址，实现无缝切换。

三、逻辑复制（Logical Replication）

逻辑复制允许按表级别同步数据，支持不同PostgreSQL版本之间的同步，适用于数据分发、跨数据库集成等场景。

1. 配置步骤

主库配置：修改postgresql.conf启用逻辑复制：

wal_level = logical  # 设置WAL级别为logical
max_replication_slots = 10  # 允许的最大复制槽数量
max_wal_senders = 10  # 允许的最大复制连接数

修改pg_hba.conf允许备库连接：

host replication repl_user 192.168.1.20/32 md5

重启PostgreSQL：sudo systemctl restart postgresql-15。

创建发布（Publication）：在主库中为需要同步的表创建发布：
```
CREATE PUBLICATION my_pub FOR TABLE my_table;
```

备库配置：在备库中创建订阅（Subscription），连接到主库的发布：

CREATE SUBSCRIPTION my_sub
CONNECTION 'host=192.168.1.10 port=5432 user=repl_user password=YourStrongPassword123!'
PUBLICATION my_pub;

2. 注意事项

表结构一致性：备库需提前创建与主库相同的表结构（不含数据）。
冲突处理：逻辑复制不自动处理冲突，需应用层或通过触发器解决。

四、第三方工具：pgsync（零停机迁移与同步）

pgsync是一款现代化的PostgreSQL数据同步工具，支持零停机迁移、并行传输、模式自动适配，适用于开发和生产环境同步。

1. 安装pgsync

在Ubuntu上通过gem安装：

sudo apt-get install ruby-dev libpq-dev build-essential -y
sudo gem install pgsync

2. 配置.pgsync.yml

在项目根目录创建.pgsync.yml文件，定义源和目标数据库：

from: postgres://postgres:YourSourcePassword@192.168.1.10:5432/source_db?sslmode=require
to: postgres://postgres:YourTargetPassword@192.168.1.20:5432/target_db?sslmode=require
exclude:
  - sensitive_table1
  - sensitive_table2
schemas:
  - public
data_rules:
  email: unique_email  # 自定义数据规则（如去重）

3. 使用命令

同步模式：pgsync --schema-only（仅同步表结构）。
同步数据：pgsync（同步结构和数据）。
同步特定表：pgsync table1,table2。

五、生产环境最佳实践

异步复制优先：适用于大多数场景，性能高、延迟低；关键业务可使用同步复制（需权衡延迟）。
监控复制状态：通过pg_stat_replication视图定期检查复制延迟，设置告警（如Zabbix、Prometheus）。
定期备份：结合WAL归档和pg_basebackup，定期备份主库数据，防止数据丢失。
自动化故障转移：使用Patroni、Pgpool-II等工具，减少人工干预时间。
安全配置：启用SSL加密（sslmode=require）、限制复制用户权限、使用强密码。