# 搭建PostgreSQL主从架构
#### 简介
PostgreSQL是一个开源对象关系型数据库管理系统，并侧重于可扩展性和标准的符合性。PostgreSQL面向企业复杂SQL处理的OLTP在线事务处理场景，支持NoSQL数据类型（JSON/XML/hstore），支持GIS地理信息处理，在可靠性、数据完整性方面有良好声誉，适用于互联网网站、位置应用系统、复杂数据对象处理等应用场景。
本文指导您使用华为云弹性云服务器搭建PostgreSQL主从架构。
#### 前提条件
- 已创建两台弹性云服务器，分别作为主节点和从节点。创建过程请参考[自定义购买ECS](https://support.huaweicloud.com/usermanual-ecs/ecs_03_7002.html)。
  本示例使用的操作系统为Ubuntu 20.04 64位、Debian 12.0.0 64位。
  主节点IP示例：192.168.1.10
  从节点IP示例：192.168.1.11
  
- 弹性云服务器所在安全组添加了如下表所示的安全组规则，具体步骤参见[为安全组添加安全组规则](https://support.huaweicloud.com/usermanual-vpc/zh-cn_topic_0030969470.html)。
  表1安全组规则 
  | 方向  | 优先级 | 策略 | 类型      | 协议端口        | 源地址        |
  |:---|:---|:---|:---|:---|:---|
  | 入方向 | 1    | 允许    | IPv4 | TCP: 22   | 0.0.0.0/0 |
  | 入方向  | 1     | 允许     | IPv4   | TCP: 5432 | 0.0.0.0/0 |
     
  
 
#### 步骤一：配置PostgreSQL主节点
请根据当前的操作系统，查看并执行对应的操作步骤。
- [Ubuntu]
  1. 执行以下命令，添加华为云加速的PostgreSQL官方源。
     ```
     echo "deb [signed-by=/etc/apt/trusted.gpg.d/postgresql.gpg] https://mirrors.huaweicloud.com/postgresql/repos/apt focal-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
     ```
     
  
  2. 执行以下命令，导入PostgreSQL官方签名密钥。
     ```
     wget -qO - https://mirrors.huaweicloud.com/postgresql/repos/apt/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
     ```
     
  
  3. 执行以下命令，更新源并安装PostgreSQL 14。
     ```
     sudo apt update
     sudo apt install -y postgresql-14 postgresql-client-14 postgresql-contrib-14
     ```
     
  
  4. 执行以下命令，启动PostgreSQL并设置开机自启。
     ```
     sudo systemctl enable --now postgresql@14-main
     ```
     
  
  5. 执行以下命令，验证PostgreSQL的安装版本。
     ```
     psql --version
     ```
     回显如下类似信息，表示安装成功。
     ![](https://support.huaweicloud.com/bestpractice-ecs/zh-cn_image_0000002579014730.png)
     
  
  6. 依次执行以下命令，编辑/etc/postgresql/14/main/postgresql.conf文件。
     ```
     # 允许所有地址访问
     sudo sed -i "s/^#listen_addresses =.*/listen_addresses = '*'/" /etc/postgresql/14/main/postgresql.conf
     # 开启WAL复制模式
     sudo sed -i "s/^#wal_level =.*/wal_level = replica/" /etc/postgresql/14/main/postgresql.conf
     # 最大WAL发送进程
     sudo sed -i "s/^#max_wal_senders =.*/max_wal_senders = 5/" /etc/postgresql/14/main/postgresql.conf
     # 开启复制槽
     sudo sed -i "s/^#max_replication_slots =.*/max_replication_slots = 5/" /etc/postgresql/14/main/postgresql.conf
     # WAL日志保留大小
     sudo sed -i "s/^#wal_keep_size =.*/wal_keep_size = 128MB/" /etc/postgresql/14/main/postgresql.conf
     # 开启WAL归档
     sudo sed -i "s/^#archive_mode =.*/archive_mode = on/" /etc/postgresql/14/main/postgresql.conf
     # 归档路径配置
     sudo sed -i "s|^#archive_command = ''|archive_command = 'cp %p /var/lib/postgresql/14/main/wal_archive/%f'|" /etc/postgresql/14/main/postgresql.conf
     # 创建WAL归档目录并授权
     sudo mkdir -p /var/lib/postgresql/14/main/wal_archive
     sudo chown postgres:postgres /var/lib/postgresql/14/main/wal_archive
     sudo chmod 700 /var/lib/postgresql/14/main/wal_archive
     ```
     
  
  7. 执行以下命令，修改/var/lib/pgsql/11/data/pg_hba.conf配置文件。
     ```
     # 末尾添加从节点IP复制权限，采用scram-sha-256加密（Ubuntu20.04默认认证方式）
     echo "host    replication     replica_user    192.168.1.11/32    scram-sha-256" | sudo tee -a /etc/postgresql/14/main/pg_hba.conf
     ```
     
  
  8. 执行以下命令，创建账号并授权。
     ```
     # 创建复制用户
     sudo -u postgres psql -c "CREATE ROLE replica_user WITH REPLICATION LOGIN PASSWORD '密码';"
     # 创建物理复制槽（防止WAL日志被清理，断线自动追数）
     sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('pg_slot01');"
     ```
     
  
  9. 执行以下命令，重启服务**。**
     ```
     sudo systemctl restart postgresql@14-main
     sudo systemctl status postgresql@14-main
     ```
     
   
- [Debian]
  1. 执行以下命令，更新源并安装PostgreSQL 15。
     ```
     apt update
     apt install -y postgresql postgresql-contrib
     ```
     
  
  2. 执行以下命令，启动PostgreSQL并设置开机自启。
     ```
     systemctl enable postgresql
     systemctl start postgresql
     ```
     
  
  3. 执行以下命令，验证PostgreSQL的安装版本。
     ```
     psql --version
     ```
     回显如下类似信息，表示安装成功。
     ![](https://support.huaweicloud.com/bestpractice-ecs/zh-cn_image_0000002610236889.png)
     
  
  4. 依次执行以下命令，编辑/etc/postgresql/15/main/postgresql.conf文件。
     ```
     # 主节点配置
     sed -i "s/^#listen_addresses =.*/listen_addresses = '*'/" /etc/postgresql/15/main/postgresql.conf
     sed -i "s/^wal_level =.*/wal_level = replica/" /etc/postgresql/15/main/postgresql.conf
     sed -i "s/^max_wal_senders =.*/max_wal_senders = 10/" /etc/postgresql/15/main/postgresql.conf
     sed -i "s/^max_replication_slots =.*/max_replication_slots = 10/" /etc/postgresql/15/main/postgresql.conf
     ```
     
  
  5. 执行以下命令，修改/etc/postgresql/15/main/pg_hba.conf配置文件。
     ```
     # 允许从库复制
     echo "host    replication     replica_user    0.0.0.0/0    trust" >> /etc/postgresql/15/main/pg_hba.conf
     ```
     
  
  6. 执行以下命令，创建账号并授权。
     ```
     sudo -u postgres psql -c "CREATE ROLE replica_user WITH REPLICATION LOGIN;"
     ```
     
  
  7. 执行以下命令，重启服务**。**
     ```
     systemctl restart postgresql
     ```
     
   
#### 步骤二：配置PostgreSQL从节点
请根据当前的操作系统，查看并执行对应的操作步骤。
- [Ubuntu]
  1. 执行以下命令，添加华为云加速的PostgreSQL官方源。
     ```
     echo "deb [signed-by=/etc/apt/trusted.gpg.d/postgresql.gpg] https://mirrors.huaweicloud.com/postgresql/repos/apt focal-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
     ```
     
  
  2. 执行以下命令，导入PostgreSQL官方签名密钥。
     ```
     wget -qO - https://mirrors.huaweicloud.com/postgresql/repos/apt/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
     ```
     
  
  3. 执行以下命令，更新源并安装PostgreSQL 14。
     ```
     sudo apt update
     sudo apt install -y postgresql-14 postgresql-client-14 postgresql-contrib-14
     ```
     
  
  4. 执行以下命令，启动PostgreSQL并设置开机自启。
     ```
     sudo systemctl enable --now postgresql@14-main
     ```
     
  
  5. 执行以下命令，验证PostgreSQL的安装版本。
     ```
     psql --version
     ```
     回显如下类似信息，表示安装成功。
     ![](https://support.huaweicloud.com/bestpractice-ecs/zh-cn_image_0000002579921234.png)
     
  
  6. 执行以下命令，停止从库服务，清空数据目录。
     ```
     sudo systemctl stop postgresql@14-main
     sudo rm -rf /var/lib/postgresql/14/main/*
     ```
     
  
  7. 执行以下命令，配置免密认证 .pgpass。
     ```
     echo "192.168.1.10:5432:replication:replica_user:密码" | sudo tee /var/lib/postgresql/.pgpass
     sudo chown postgres:postgres /var/lib/postgresql/.pgpass
     sudo chmod 600 /var/lib/postgresql/.pgpass
     ```
     
  
  8. 执行以下命令，全量克隆主库数据。
     ```
     sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/14/main/ \
     -U replica_user -P -v --wal-method=stream
     ```
     
  
  9. 执行以下命令，配置备库参数和备库标识文件。
     ```
     # 开启热备只读
     sudo sed -i "s/^#hot_standby =.*/hot_standby = on/" /etc/postgresql/14/main/postgresql.conf
     # 配置主库连接信息+绑定复制槽
     echo "primary_conninfo = 'host=192.168.1.10 port=5432 user=replica_user password=Pg@Huawei@123'" | sudo tee -a /etc/postgresql/14/main/postgresql.conf
     echo "primary_slot_name = 'pg_slot01'" | sudo tee -a /etc/postgresql/14/main/postgresql.conf
     # 创建备库标识文件 standby.signal（PG12+新版，替代旧版recovery.conf）
     sudo touch /var/lib/postgresql/14/main/standby.signal
     sudo chown postgres:postgres /var/lib/postgresql/14/main/standby.signal
     ```
     
  
  10. 执行以下命令，启动从库服务。
      ```
      sudo systemctl enable --now postgresql@14-main
      sudo systemctl status postgresql@14-main
      ```
      
   
- [Debian]
  1. 执行以下命令，更新源并安装PostgreSQL 15。
     ```
     apt update
     apt install -y postgresql postgresql-contrib
     ```
     
  
  2. 执行以下命令，启动PostgreSQL并设置开机自启。
     ```
     systemctl enable postgresql
     systemctl start postgresql
     ```
     
  
  3. 执行以下命令，验证PostgreSQL的安装版本。
     ```
     psql --version
     ```
     回显如下类似信息，表示安装成功。
     ![](https://support.huaweicloud.com/bestpractice-ecs/zh-cn_image_0000002579751024.png)
     
  
  4. 执行以下命令，停止从库服务，清空数据目录。
     ```
     systemctl stop postgresql
     rm -rf /var/lib/postgresql/15/main/*
     ```
     
  
  5. 执行以下命令，全量克隆主库数据。
     ```
     mkdir -p /var/lib/postgresql/15/main
     chown postgres:postgres /var/lib/postgresql/15/main
     chmod 700 /var/lib/postgresql/15/main
     cd /tmp && sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/15/main -U replica_user -P -X stream
     ```
     
  
  6. 执行以下命令，配置备库参数和备库标识文件。
     ```
     # 备库配置
     echo "primary_conninfo = 'host=192.168.1.10 port=5432 user=replica_user'" >> /etc/postgresql/15/main/postgresql.conf
     echo "hot_standby = on" >> /etc/postgresql/15/main/postgresql.conf
     # 备库标识
     touch /var/lib/postgresql/15/main/standby.signal
     chown postgres:postgres /var/lib/postgresql/15/main/standby.signal
     ```
     
  
  7. 执行以下命令，启动从库服务。
     ```
     systemctl start postgresql
     ```
     
   
#### 步骤三：验证部署完成
请根据当前的操作系统，查看并执行对应的操作步骤。
- [Ubuntu]
  1. 执行以下命令，在主节点查看复制状态。
     ```
     sudo -u postgres psql -c "SELECT pid,usename,application_name,state,sync_state FROM pg_stat_replication;"
     ```
     
  
  2. 执行以下命令，在从节点查看WAL接收状态。
     ```
     sudo -u postgres psql -c "SELECT * FROM pg_stat_wal_receiver;"
     sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
     ```
     
  
  3. 数据同步测试。
     1. 在主节点上创建一个测试表并插入数据。
        ```
        sudo -u postgres psql -c "CREATE TABLE test_huawei(id INT PRIMARY KEY, info TEXT);"
        sudo -u postgres psql -c "INSERT INTO test_huawei VALUES(1,'华为云Ubuntu20.04 PG主从');"
        ```
        
     
     2. 在从节点上查询该表，验证数据是否已同步。
        ```
        sudo -u postgres psql -c "SELECT * FROM test_huawei;"
        ```
        
     
     
     如果能查询到已插入的数据，则表示主从复制已成功建立。
     
   
- [Debian]
  1. 执行以下命令，主节点查看复制状态。
     ```
     sudo -u postgres psql -c "SELECT pid, usename, state, sync_state FROM pg_stat_replication;"
     ```
     
  
  2. 执行以下命令，从节点查看WAL接收状态
     ```
     sudo -u postgres psql -c "SELECT * FROM pg_stat_wal_receiver;"
     sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
     ```
     
  
  3. 数据同步测试。
     1. 在主节点上创建一个测试表并插入数据。
        ```
        sudo -u postgres psql -c "CREATE TABLE test_sync(id int);" 
        sudo -u postgres psql -c "INSERT INTO test_sync VALUES(1);"
        ```
        
     
     2. 在从节点上查询该表，验证数据是否已同步。
        ```
        sudo -u postgres psql -c "SELECT * FROM test_sync;"
        ```
        
     
     
     如果能查询到已插入的数据，则表示主从复制已成功建立。