搭建PostgreSQL主从架构

PostgreSQL是一个功能非常强大、开源的对象关系型数据库管理系统，本文介绍如何在Linux实例上搭建并使用PostgreSQL。

PostgreSQL可靠性高，支持NoSQL数据类型（JSON/XML/hstore）、GIS地理信息处理，适用于位置应用系统、在线事务处理等场景。

软件版本

• 操作系统：本文以CentOS 7.6为例，您也可以按需替换为CentOS 7.9、veLinux 1.0(CentOS兼容版)等CentOS 7系镜像。
• PostgreSQL：本文以为PostgreSQL 11为例。

前提条件

• 您已购买Linux实例。具体操作请参见购买云服务器。

• 您已为Linux实例绑定公网IP，使其具备访问公网的能力。如未绑定，请参见绑定公网IP。

• 实例关联安全组在入方向已放通TCP 5432端口。

第一步：搭建主节点

1. 远程连接云服务器并登录，具体操作请参考登录Linux实例小节。

2. 执行以下命令，安装PostgreSQL。

   yum update -y
   yum -y install https://mirrors.ivolces.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
   

3. 执行以下命令，安装客户端包和服务器包。

   yum -y install postgresql11
   yum -y install postgresql11-server
   

4. 执行以下命令，初始化PostgreSQL。
   /usr/pgsql-11/bin/postgresql-11-setup initdb

5. 执行以下命令，启动PostgreSQL服务。
   systemctl start postgresql-11

6. 执行以下命令，设置PostgreSQL服务开机自启动。
   systemctl enable postgresql-11

7. 执行以下命令，登录安装服务后自动生成的默认账号postgres。
   su - postgres

8. 执行以下命令，进入PostgreSQL交互终端。
   psql

9. 执行以下命令，为默认账号postgres设置密码，增强默认账号的安全性。
   ALTER USER postgres WITH PASSWORD '密码';

10. 执行以下命令，创建新数据库账号，设置密码并授权。
    create role 账户名 login replication encrypted password '密码';

11. 输入\q，按Enter键退出交互终端。

12. 输入exit，退出PostgreSQL。

13. 修改pg_hba.conf配置文件。

    1. 执行以下命令，打开文件。
       vim /var/lib/pgsql/11/data/pg_hba.conf
    2. 按“i”键进入编辑模式，在IPv4 local connections中添加以下内容。

       host all all ${从节点VPC IPv4网段} md5    #允许VPC网段中md5密码认证连接
       host replication  ${账户名}   ${从节点VPC IPv4网段} md5  #允许用户从replication数据库进行数据同步
       

    3. 按“Esc”，输入“:wq”，按Enter键，保存文件并返回。

14. 修改postgresql.conf配置文件。

    1. 执行以下命令，打开文件。
       vim /var/lib/pgsql/11/data/postgresql.conf
    2. 按“i”键进入编辑模式，找到以下参数并修改。注意需去掉参数前的“#”。

       wal_level = hot_standby  # 启用热备模式
       synchronous_commit = on  # 开启同步复制
       listen_addresses = '*'   #监听的IP地址，配置为*
       max_wal_senders = 32     # 同步最大的进程数量
       wal_sender_timeout = 60s # 流复制主机发送数据的超时时间
       max_connections = 500    # 最大连接数，从库的max_connections必须要大于主库
       

    3. 按“Esc”，输入“:wq”，按Enter键，保存文件并返回。

15. 执行以下命令，重启PostgreSQL服务。
    systemctl restart postgresql-11

第二步：搭建从主节点

1. 执行以下命令，安装PostgreSQL。

   yum update -y
   yum -y install https://mirrors.ivolces.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
   

2. 执行以下命令，安装客户端包和服务器包。

   yum -y install postgresql11
   yum -y install postgresql11-server
   

3. 执行以下命令，使用pg_basebackup基础备份工具制定备份目录，密码为数据库账户名对应的密码。
   pg_basebackup -h ${主节点私网IP地址} -U ${数据库账户名} -D /var/lib/pgsql/11/data -X stream -P

4. 执行以下命令，拷贝主节点配置相关文件到备份目录的recovery.conf文件。
   cp /usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.conf

5. 修改recovery.conf配置文件。

   1. 执行以下命令，打开文件。
      vim /var/lib/pgsql/11/data/recovery.conf
   2. 按“i”键进入编辑模式，找到以下参数并修改。注意需去掉参数前的“#”。

      standby_mode = on     #声明此节点为从库
      primary_conninfo = ‘host=主节点私网IP地址 port=5432 user=数据库账号名 password=数据库密码’ #对应主库的连接信息
      recovery_target_timeline = ‘latest’ #流复制同步到最新的数据
      

   3. 按“Esc”，输入“:wq”，按Enter键，保存文件并返回。

6. 修改postgresql.conf配置文件。

   1. 执行以下命令，打开文件。
      vim /var/lib/pgsql/11/data/postgresql.conf

   2. 按“i”键进入编辑模式，找到以下参数并修改。注意需去掉参数前的“#”。

      listen_addresses= '*'              # 监听的内网 IP 地址，配置为*
      max_connections = 600              # 最大连接数，从库的 max_connections 必须要大于主库
      hot_standby = on                   # 开启热备
      max_standby_streaming_delay = 30s  # 数据流备份的最大延迟时间
      wal_receiver_status_interval = 1s # 从节点向主节点报告自身状态的最长间隔时间
      hot_standby_feedback = on          # 如果有错误的数据复制向主进行反馈
      

   3. 按“Esc”，输入“:wq”，按Enter键，保存文件并返回。

7. 执行以下命令，修改数据目录的属组和属主。
   chown -R postgres.postgres /var/lib/pgsql/11/data

8. 执行以下命令，启动PostgreSQL服务。
   systemctl start postgresql-11

9. 执行以下命令，设置PostgreSQL服务开机自启动。
   systemctl enable postgresql-11

第三步：验证搭建完成

1. 登录主节点，执行以下命令，可以查看sender进程。
   ps aux |grep sender
   回显例如如下，表示主节点搭建成功。

   postgres 17281  0.0  0.0 416252  3644 ?        Ss   15:31   0:00 postgres: walsender wxt 192.168.0.127(38924) streaming 0/3000140
   root     17291  0.0  0.0 112812   980 pts/0    S+   15:31   0:00 grep --color=auto sender
   

2. 登录从节点，执行以下命令，可以查看receiver进程。
   ps aux |grep receiver
   回显例如如下，表示从节点搭建成功。

   postgres 16930  0.0  0.0 422320  3668 ?        Ss   15:31   0:00 postgres: walreceiver   streaming 0/3000140
   root     16942  0.0  0.0 112812   976 pts/0    S+   15:31   0:00 grep --color=auto receiver
   

3. 登录主节点，执行以下命令，从主库查看从库状态。

   1. 执行以下命令，登录安装服务后自动生成的默认账号postgres。
      su - postgres

   2. 执行以下命令，进入PostgreSQL交互终端。
      psql

   3. 执行以下命令，查看从库状态。
      select * from pg_stat_replication;
      回显如下。
      

4. 在主库新建一个库，验证从库可以自动同步。

   1. 执行以下命令，新建主库。
      create database testdb;

   2. 登录从节点，进入PostgreSQL交互终端，执行以下命令，查看从库。
      \l
      回显如下，表示从库同步成功。