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）、限制复制用户权限、使用强密码。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！