ubuntu中pgadmin如何设置主从复制

Ubuntu中pgAdmin设置PostgreSQL主从复制的步骤

1. 环境准备

• 两台Ubuntu服务器（主库Primary、从库Standby），安装相同版本的PostgreSQL（如15.x）。
• 确保两台服务器网络互通，主库IP（如192.168.1.10）、从库IP（如192.168.1.11）可达。
• 在Ubuntu上安装PostgreSQL（若未安装，可通过sudo apt install postgresql postgresql-contrib完成）。

2. 配置主库（Primary）

2.1 修改PostgreSQL配置文件

• 编辑postgresql.conf（路径：/etc/postgresql/<版本>/main/postgresql.conf），调整以下参数：

  wal_level = replica                # 启用WAL日志（复制基础）
  max_wal_senders = 5                # 允许的最大复制连接数
  wal_keep_size = 128MB              # 保留WAL日志大小（避免备库断开后丢失日志）
  hot_standby = on                   # 允许从库进入只读模式（后续逻辑复制可能需要）
  listen_addresses = '*'             # 监听所有IP地址（允许从库远程连接）
  

• 编辑pg_hba.conf（路径：/etc/postgresql/<版本>/main/pg_hba.conf），添加从库连接权限：

  host replication replicator 192.168.1.11/32 md5  # 允许从库IP通过复制用户连接
  

• 重启PostgreSQL使配置生效：

  sudo systemctl restart postgresql
  

2.2 创建复制用户

• 使用psql工具创建复制专用角色（需具备REPLICATION权限）：

  sudo -u postgres psql
  CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'your_strong_password';  # 替换为强密码
  \q
  

3. 配置从库（Standby）

3.1 清空从库数据目录

• 停止从库PostgreSQL服务，并清空数据目录（默认路径：/var/lib/postgresql/<版本>/main/）：

  sudo systemctl stop postgresql
  sudo rm -rf /var/lib/postgresql/<版本>/main/*
  

3.2 使用pg_basebackup拉取主库数据

• 从主库拉取完整数据到从库数据目录（-R参数会自动生成standby.signal和postgresql.auto.conf，简化配置）：

  sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/<版本>/main -U replicator -P --wal-method=stream
  

  输入步骤2.2中设置的复制用户密码，等待拉取完成。

3.3 启动从库

• 启动从库PostgreSQL服务，此时从库会自动连接主库并开始同步：

  sudo systemctl start postgresql
  

4. 验证复制状态

4.1 在主库查看复制状态

• 登录主库psql，执行以下命令查看从库连接信息：

  SELECT application_name, client_addr, state, sync_state 
  FROM pg_stat_replication;
  

  • state = streaming：表示从库正在实时同步主库WAL日志。
  • sync_state = sync/async：表示同步模式（同步/异步）。

4.2 在从库验证数据同步

• 在主库创建测试表并插入数据：

  CREATE TABLE test_replication (id SERIAL PRIMARY KEY, name VARCHAR(100));
  INSERT INTO test_replication (name) VALUES ('Test Data');
  

• 在从库查询该表，确认数据是否同步：

  SELECT * FROM test_replication;
  

  若查询到数据，则复制成功。

5. 可选：逻辑复制（按需同步特定表）

若需按表同步数据（而非全库），可使用逻辑复制：

5.1 主库配置

• 修改postgresql.conf，启用逻辑复制：

  wal_level = logical                  # 逻辑复制需要更高WAL级别
  max_replication_slots = 5            # 允许的最大复制槽数量
  

• 重启PostgreSQL：

  sudo systemctl restart postgresql
  

5.2 创建发布（主库）

• 在主库中创建发布（指定同步的表，ALL TABLES表示所有表）：

  CREATE PUBLICATION pub_all FOR ALL TABLES;
  

5.3 创建订阅（从库）

• 在从库中创建订阅（连接主库发布，同步数据）：

  CREATE SUBSCRIPTION sub_all 
  CONNECTION 'host=192.168.1.10 port=5432 dbname=postgres user=replicator password=your_strong_password' 
  PUBLICATION pub_all;
  

  注意：从库需提前创建表结构（与主库一致），逻辑复制仅同步数据变更。

6. pgAdmin中的可视化操作

• 连接主从库：在pgAdmin中分别添加主库（192.168.1.10）和从库（192.168.1.11）的连接。
• 查看复制状态：右键主库→Dashboard→Replication标签，可查看从库连接状态、同步延迟等信息。
• 管理复制用户：在主库的Login/Group Roles节点下，可创建/修改复制用户（如replicator）。
• 逻辑复制配置：通过pgAdmin的Query Tool执行上述逻辑复制的SQL语句（创建发布、订阅）。

注意事项

• 主从库PostgreSQL版本需完全一致（如均为15.x），避免兼容性问题。
• 生产环境中建议配置WAL归档（archive_mode = on），防止备库长时间断开导致数据丢失。
• 异步复制性能高但可能存在少量数据丢失（主库提交后不等待备库确认）；同步复制（synchronous_standby_names = '*'）可保证强一致性，但会增加延迟，需根据业务需求选择。