文档

搭建PostgreSQL主从架构

更新时间:

PostgreSQL是一个开源数据库,以其强大的功能、标准合规性、可靠性和扩展性受到广泛认可和采用。PostgreSQL遵循SQL标准,支持NoSQL数据类型(JSON/XML/hstore)递归查询、全文搜索、GIS地理信息处理等,能够应对各种复杂数据分析需求。广泛应用于互联网网站、企业级应用后端、数据仓库与分析平台、位置应用系统、复杂数据对象处理等应用场景。本文档介绍如何在CentOS 7.x操作系统的ECS实例上搭建PostgreSQL主从架构。

准备工作

本教程适用于熟悉云服务器ECS、Linux系统、PostgreSQL的阿里云用户。

搭建PostgreSQL主从复制架构,需要创建2台专有网络类型的ECS实例,一台ECS实例作为主节点,另一台ECS实例作为从节点。创建的ECS实例需要满足以下条件,更多参数说明,请参见自定义购买实例

  • 操作系统:PostgreSQL支持多种操作系统,本文以CentOS 7.x为例。

    Alibaba Cloud Linux 3/2不支持本文使用的YUM源安装方式,建议您使用源码安装。更多其他操作系统安装方式,请参见PostgreSQL官网

  • 实例规格:PostgreSQL主从架构并没有严格的规格限制,具体的规格选择应基于详细的容量规划、性能测试和业务增长预测。

  • 公网IP:建议您不为ECS实例分配公网IP,按需购买弹性公网IP绑定至ECS实例,后续您可以根据实际情况考虑升级配置或调优架构。详情请参见申请弹性公网IP

  • 安全组:已在安全组内添加入方向规则放行80、22、5432端口。具体操作,请参见添加安全组规则

步骤一:配置PostgreSQL主节点

  1. 远程连接PostgreSQL主节点。

  2. 安装PostgreSQL。

    1. 运行以下命令,添加PostgreSQL官方YUM仓库。

      sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    2. 安装PostgreSQL 15。

      sudo yum install -y postgresql15-server
      说明

      本教程以PostgreSQL 15版本为例。您可以根据业务需求选择合适的版本。

  3. 初始化PostgreSQL数据库。

    sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

    回显如下所示时,说明PostgreSQL数据库已初始化完成。

    image

  4. 依次运行以下命令,启动服务并设置服务开机自启动。

    sudo systemctl enable postgresql-15
    sudo systemctl start postgresql-15
  5. 在主节点上创建数据库账号replica(用于主从复制),并设置密码及登录权限和备份权限。

    1. 运行以下命令,登录postgres用户。

      sudo su - postgres

      回显结果出现-bash-4.2$时表示成功登录。

      image

    2. 输入以下命令,进入PostgreSQL交互终端。

      psql

      回显结果出现postgres=#时表示成功进入交互终端。

      image

    3. 运行以下命令,为用户postgres设置密码,增强安全性。

      ALTER USER postgres WITH PASSWORD 'YourPassWord';
    4. 输入以下SQL语句创建数据库账号,并设置密码、登录权限和备份权限。

      CREATE ROLE user login replication encrypted password 'YourPassWord';

      本示例中创建数据库账号replica,并将密码设置为replica

      CREATE ROLE replica login replication encrypted password 'replica';
    5. 查询账号是否创建成功。

      SELECT usename from pg_user;

      返回结果如下,表示已创建成功。

      image

    6. 查询权限是否创建成功。

      SELECT rolname from pg_roles;

      返回结果如下,表示已创建成功。

      image

    7. 输入以下命令,按Enter键退出SQL终端。

      \q
    8. 输入以下命令,按Enter键退出PostgreSQL。

      exit
  6. 设置replica用户白名单。

    1. 运行以下命令,打开pg_hba.conf文件。

      sudo vim /var/lib/pgsql/15/data/pg_hba.conf
    2. i键进入编辑模式,在IPv4 local connections段添加下面两行内容。

      host    all             all             <从节点的VPC IPv4网段>          md5     #允许VPC网段中md5密码认证连接
      host    replication     replica         <从节点的VPC IPv4网段>          md5     #允许用户从replication数据库进行数据同步

      image

    3. Esc键,输入:wq,按下Enter键,保存并退出pg_hba.conf文件。

  7. 配置postgresql.conf文件。

    1. 运行以下命令,打开postgresql.conf文件。

      sudo vim /var/lib/pgsql/15/data/postgresql.conf
    2. 分别找到以下参数,并将参数修改为以下内容:

      listen_addresses = '*'   #监听的IP地址
      max_connections = 100    #最大连接数,从库的max_connections必须要大于主库的
      wal_level = replica      #记录足够的信息以支持常见的备库用途,包括流复制和热备。
      synchronous_commit = on  #开启同步复制
      max_wal_senders = 32     #同步最大的进程数量
      wal_sender_timeout = 60s #流复制主机发送数据的超时时间
    3. Esc键,输入:wq,按下Enter键,保存并退出postgresql.conf文件。

  8. 运行以下命令,重启PostgreSQL服务。

    sudo systemctl restart postgresql-15

步骤二:配置PostgreSQL从节点

  1. 远程连接PostgreSQL从节点。

    具体操作,请参见通过密码或密钥认证登录Linux实例

  2. 安装PostgreSQL。

    1. 运行以下命令,添加PostgreSQL官方YUM仓库。

      sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    2. 安装PostgreSQL 15。

      sudo yum install -y postgresql15-server
      说明

      本教程以PostgreSQL 15版本为例。您可以根据业务需求选择合适的版本。

  3. 运行以下命令,使用pg_basebackup基础备份工具从主库创建从库的基础备份。

    pg_basebackup -D /var/lib/pgsql/15/data -h <主节点公网IP> -p 5432 -U replica -X stream -P

    出现Password时,输入主库的密码。出现如下所示时,表示已同步成功。

    image

  4. 配置从库的postgresql.conf文件。

    1. 运行以下命令,打开postgresql.conf文件。

      sudo vim /var/lib/pgsql/15/data/postgresql.conf
    2. 分别找到以下参数,并将参数修改为以下内容。

      primary_conninfo = 'host=<主节点IP> port=5432 user=replica password=replica' #对应主库的连接信息
      recovery_target_timeline = 'latest' #流复制同步到最新的数据
      max_connections = 1000             # 最大连接数,从节点需设置比主节点大
      hot_standby = on                   # 开启热备
      max_standby_streaming_delay = 30s  # 数据流备份的最大延迟时间
      wal_receiver_status_interval = 1s  # 从节点向主节点报告自身状态的最长间隔时间
      hot_standby_feedback = on          # 如果有错误的数据复制向主进行反馈

      image

    3. Esc键,输入:wq,按下Enter键,保存并退出postgresql.conf文件。

  5. 运行以下命令,修改数据目录的属组和属主。

    sudo chown -R postgres.postgres /var/lib/pgsql/15/data
  6. 依次运行以下命令,启动服务并设置服务开机自启动。

    sudo systemctl enable postgresql-15
    sudo systemctl start postgresql-15

步骤三:检测验证

检测验证需要主从节点之间存在数据交互,例如,从节点备份目录时,进行检测能够得到预期的结果。

sudo pg_basebackup -D /var/lib/pgsql/15/data -h <主节点IP> -p 5432 -U replica -X stream -P
  1. 在主节点中运行以下命令,查看sender进程。

    ps aux |grep sender

    返回结果如下,表示可以成功查看到sender进程。

    postgres  2916  0.0  0.3 340388  3220 ?        Ss   15:38   0:00 postgres: walsender  replica 192.168.**.**(49640) streaming 0/F01C1A8
  2. 在从节点中运行以下命令,查看receiver进程。

    ps aux |grep receiver

    返回结果类似如下,表示可以成功查看到receiver进程。

    postgres 23284  0.0  0.3 387100  3444 ?        Ss   16:04   0:00 postgres: walreceiver   streaming 0/F01C1A8
  3. 在主节点中进入PostgreSQL交互终端,输入以下SQL语句,在主库中查看从库状态。

    1. 运行以下命令,登录postgres用户。

      sudo su - postgres

      回显结果出现-bash-4.2$时表示成功登录。

    2. 输入以下命令,进入PostgreSQL交互终端。

      psql

      回显结果出现postgres=#时表示成功进入交互终端。

    3. 运行以下命令,在主库中查看从库状态。

      select * from pg_stat_replication;

      返回结果类似如下,表示可成功查看到从库状态。

      pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_locati
      on | flush_location | replay_location | sync_priority | sync_state 
      ------+----------+---------+------------------+---------------+-----------------+------------- +-------------------------------+--------------+-----------+---------------+-------------
      ---+----------------+-----------------+---------------+------------
      2916 | 16393 | replica | walreceiver | 192.168.**.** | | 49640 | 2017-05-02 15:38:06.188988+08 | 1836 | streaming | 0/F01C0C8 | 0/F01C0C8 
      | 0/F01C0C8 | 0/F01C0C8 | 0 | async
      (1 rows)
    4. 输入以下命令,并按Enter退出SQL终端。

      \q
    5. 输入以下命令,并按Enter退出PostgreSQL。

      exit