本地与Azure云PostgreSQL实例同步及协同运行方案咨询

一、在Azure VM上部署PostgreSQL实例 #

• 创建Azure VM：选择适配的操作系统（如Ubuntu Server、CentOS），配置匹配业务需求的CPU、内存及存储资源，确保VM的网络安全组（NSG）开放PostgreSQL默认端口5432，并授权本地IP访问该VM。
• 安装PostgreSQL（以Ubuntu为例）：

  sudo apt update
  sudo apt install postgresql postgresql-contrib
  

• 配置远程访问：
  1. 修改/etc/postgresql/<版本号>/main/postgresql.conf，设置listen_addresses = '*'以允许远程连接；
  2. 修改/etc/postgresql/<版本号>/main/pg_hba.conf，添加本地IP段的访问规则，例如host all all <本地IP/掩码> md5；
  3. 重启服务：sudo systemctl restart postgresql。
• 创建同步所需资源：登录PostgreSQL，创建与本地实例同名的数据库，以及具备复制权限的用户：

  CREATE DATABASE <你的数据库名称>;
  CREATE USER repl_user WITH REPLICATION LOGIN PASSWORD '<自定义密码>';
  

二、选择同步方案（两种主流实现方式） #

方式1：PostgreSQL内置流复制（主从单向同步） #

• 确定角色：建议将本地实例设为主库，Azure VM实例设为从库（可根据业务需求反向配置）。
• 主库（本地）配置：
  1. 修改postgresql.conf开启归档与复制功能：

     wal_level = replica
     max_wal_senders = 5
     wal_keep_size = 16MB
     archive_mode = on
     archive_command = 'cp %p /var/lib/postgresql/<版本号>/main/archive/%f'
     

  2. 修改pg_hba.conf，添加Azure VM公网IP的复制权限：

     host replication repl_user <Azure VM公网IP>/32 md5
     

  3. 重启本地PostgreSQL服务，执行基础备份并传输到Azure VM：

     pg_basebackup -h <本地IP> -U repl_user -D /var/lib/postgresql/<版本号>/main -P -X stream
     

     传输完成后，修改Azure VM上备份目录的权限：sudo chown -R postgres:postgres /var/lib/postgresql/<版本号>/main
• 从库（Azure VM）配置：
  1. 在数据目录创建recovery.conf（PostgreSQL 12+版本直接在postgresql.conf中配置primary_conninfo）：

     standby_mode = 'on'
     primary_conninfo = 'host=<本地公网IP> port=5432 user=repl_user password=<自定义密码>'
     trigger_file = '/tmp/promote_to_master'
     

  2. 重启Azure VM上的PostgreSQL服务，从库将自动连接主库启动同步。

方式2：逻辑复制（灵活同步指定表/支持双向协同） #

• 主库（本地）配置：
  1. 修改postgresql.conf：

     wal_level = logical
     max_replication_slots = 5
     max_wal_senders = 5
     

  2. 重启服务后创建发布：

     -- 同步所有表，或指定具体表：FOR TABLE table1, table2
     CREATE PUBLICATION my_publication FOR ALL TABLES;
     

• 从库（Azure VM）配置：
  1. 提前同步表结构：通过pg_dump -s <数据库名称>导出本地库结构，在Azure VM的PostgreSQL中执行。
  2. 创建订阅：

     CREATE SUBSCRIPTION my_subscription CONNECTION 'host=<本地公网IP> port=5432 dbname=<数据库名称> user=repl_user password=<自定义密码>' PUBLICATION my_publication;
     

  3. 查看同步状态：SELECT * FROM pg_stat_subscription;
• 双向协同配置：在两个实例上分别创建发布和订阅，需注意处理数据冲突（可通过触发器、业务逻辑或conflict_resolution参数配置）。

三、协同运行与监控 #

• 流复制场景：从库默认只读，可用于分担读请求；若主库故障，在从库执行touch /tmp/promote_to_master即可将其提升为主库，之后可调整本地实例为新主库的从库。
• 逻辑复制场景：支持多节点写入，适合分布式业务场景，需定期检查同步状态避免数据不一致。
• 监控手段：使用pg_stat_replication（流复制）或pg_stat_subscription（逻辑复制）查看同步状态；Azure侧可启用VM监控指标，本地可通过pgAdmin等工具监控实例状态。

内容的提问来源于stack exchange，提问作者Vlad Efanov