You need to enable JavaScript to run this app.
导航
搭建PostgreSQL主从架构
最近更新时间:2023.12.07 14:18:20首次发布时间:2021.11.24 17:52:44

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;
      回显如下。
      alt

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

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

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