PostgreSQL 本地Slave搭建步骤

  • A+
所属分类:PostgreSQL
高性能企业级服务器首台5折

PostgreSQL 被业界誉为最先进的开源数据库,目前阿里云数据库 PostgreSQL 版具有 NoSQL 兼容,高效查询,插件化管理,安全稳定的特性。本文档介绍使用阿里云 ECS 搭建 PostgreSQL 主从架构的操作步骤。

适用对象

适用于熟悉 ECS,熟悉 Linux 系统,熟悉 PostgreSQL 的阿里云用户。

前提条件

已在安全组中添加规则放行 5432 端口。

基本流程

使用阿里云 ECS 搭建 PostgreSQL 主从架构的操作步骤如下:

  1. 选购 ECS 实例。
  2. 主节点安装配置。
  3. 从节点安装配置。
  4. 检测验证。

步骤 1:选购 ECS 实例

搭建主从复制架构,需要选购2台专有网络类型的云服务器ECS实例,建议不分配公网IP,可按需购买弹性公网IP绑定至对应ECS实例,进行配置操作。后续使用您可以根据实际情况考虑配置升级或者架构调优变更。

步骤 2:安装 PostgreSQL

阿里云服务器上安装 PostgreSQL 有 2 种方式:

  • 镜像部署
  • 手动部署(源码编译安装/YUM安装)

本文档基于yum部署的方式,安装postgresql;您也可以在云市场基础环境中搜索筛选,使用镜像部署,更加快捷方便。

本文环境软件明细:

1
CentOS 7.2 |PostgreSQL (9.5.6)

步骤 3:PostgreSQL 主节点配置

  1. 主节点上执行以下命令安装 PostgreSQL。
    1. 1
      <span class="com"><span class="hljs-comment"># yum update -y </span></span>
    2. 1
       <span class="com"><span class="hljs-comment"># yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm -y</span></span>
    3. 1
       <span class="com"><span class="hljs-comment"># yum install postgresql95-server postgresql95-contrib -y</span></span>
    4. 1
       <span class="com"><span class="hljs-comment"># /usr/pgsql-9.5/bin/postgresql95-setup initdb</span></span>
    5. 1
       <span class="com"><span class="hljs-comment"># systemctl enable postgresql-9.5.service</span></span>
    6. 1
       <span class="com"><span class="hljs-comment"># systemctl start postgresql-9.5.service</span></span>
  2. 主节点上创建进行主从复制的数据库账号,并设置密码及登录和备份权限。
    1. 1
      <span class="com"><span class="hljs-comment"># su - postgres</span></span>
    2. 1
       <span class="com"><span class="hljs-comment"># psql</span></span>
    3. 1
      <span class="pln"> postgres</span><span class="pun">=<span class="hljs-comment">#</span></span><span class="pln"><span class="hljs-comment"> CREATE ROLE replica login replication encrypted password </span></span><span class="str"><span class="hljs-comment">'replica'</span></span><span class="pun"><span class="hljs-comment">;</span></span>
    4. 1
      <span class="pln"> CREATE ROLE</span>
    5. 1
      <span class="pln"> postgres</span><span class="pun">=<span class="hljs-comment">#</span></span><span class="pln"><span class="hljs-comment"> SELECT usename from pg_user </span></span><span class="pun"><span class="hljs-comment">;</span></span>
    6. 1
      <span class="pln"> usename  </span>
    7. 1
       <span class="pun">----------</span>
    8. 1
      <span class="pln"> postgres</span>
    9. 1
      <span class="pln"> replica</span>
    10. 1
       <span class="pun">(</span><span class="lit">2</span><span class="pln"> rows</span><span class="pun">)</span>
    11. 1
       
    12. 1
      <span class="pln"> postgres</span><span class="pun">=<span class="hljs-comment">#</span></span><span class="pln"><span class="hljs-comment"> SELECT rolname from pg_roles </span></span><span class="pun"><span class="hljs-comment">;</span></span>
    13. 1
      <span class="pln"> rolname  </span>
    14. 1
       <span class="pun">----------</span>
    15. 1
      <span class="pln"> postgres</span>
    16. 1
      <span class="pln"> replica</span>
    17. 1
       <span class="pun">(</span><span class="lit">2</span><span class="pln"> rows</span><span class="pun">)</span>
  3. 修改pg_hba.conf,设置replica用户白名单。
    1. 1
      <span class="com"><span class="hljs-comment"># vim /var/lib/pgsql/9.5/data/pg_hba.conf</span></span>

    在IPv4 local connections段添加下面两行内容

    host all all 192.168.1.0/24 md5 允许VPC网段中md5密码认证连接 host replication replica 192.168.1.0/24 md5 允许用户从replication数据库进行数据同步

  4. 修改postgresql.conf。
    1. 1
      <span class="com"><span class="hljs-comment"># vim /var/lib/pgsql/9.5/data/postgresql.conf</span></span>

    设置以下参数

    • wal_level = hot_standby 启用热备模式
    • synchronous_commit = on 开启同步复制
    • max_wal_senders = 32 同步最大的进程数量
    • wal_sender_timeout = 60s 流复制主机发送数据的超时时间
    • max_connections = 100 最大连接数,从库的max_connections必须要大于主库的
  5. 重启服务
    1. 1
      <span class="com"><span class="hljs-comment"># systemctl restart postgresql-9.5.service</span></span>

步骤 4:PostgreSQL从节点配置

  1. 安装postgres。
    1. 1
       <span class="com"><span class="hljs-comment"># yum update -y</span></span>
    2. 1
          <span class="com"><span class="hljs-comment"># yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-        x86_64/pgdg-centos95-9.5-2.noarch.rpm -y</span></span>
    3. 1
          <span class="com"><span class="hljs-comment"># yum install postgresql95-server postgresql95-contrib -y</span></span>
  2. 使用pg_basebackup基础备份的工具制定备份目录。
    1. 1
        <span class="com"><span class="hljs-comment"># pg_basebackup -D /var/lib/pgsql/9.5/data -h 主节点IP -p 5432 -U replica -X stream -P</span></span>
    2. 1
           <span class="typ">Password</span><span class="pun">:</span>
    3. 1
           <span class="lit">30075</span><span class="pun">/</span><span class="lit">30075</span><span class="pln"> kB </span><span class="pun">(</span><span class="lit">100</span><span class="pun">%),</span> <span class="lit">1</span><span class="pun">/</span><span class="lit">1</span><span class="pln"> tablespace</span>
  3. 添加并修改recovery.conf。
    1. 1
       <span class="com"><span class="hljs-comment"># cp /usr/pgsql-9.5/share/recovery.conf.sample /var/lib/pgsql/9.5/data/recovery.conf</span></span>
    2. 1
       <span class="com"><span class="hljs-comment"># vim /var/lib/pgsql/9.5/data/recovery.conf</span></span>

    设置以下参数。

    • standby_mode = on # 声明此节点为从库
    • primary_conninfo = ‘host=主节点IP port=5432 user=replica password=replica’ # 对应主库的连接信息
    • recovery_target_timeline = ‘latest’ # 流复制同步到最新的数据
  4. 修改postgresql.conf。
    1. 1
       <span class="com"><span class="hljs-comment"># vim /var/lib/pgsql/9.5/data/postgresql.conf</span></span>

    设置以下参数。

    1. 1
       <span class="pun">-</span><span class="pln"><span class="ruby"> max_connections </span></span><span class="pun"><span class="ruby">=</span></span> <span class="lit"><span class="ruby"><span class="hljs-number">1000</span></span></span>             <span class="com"><span class="ruby"><span class="hljs-comment"># 最大连接数,从节点需设置比主节点大</span></span></span>
    2. 1
       <span class="pun">-</span><span class="pln"> hot_standby </span><span class="pun">=</span><span class="pln"> <span class="hljs-keyword">on</span>                   </span><span class="com"><span class="hljs-comment"># 开启热备</span></span>
    3. 1
       <span class="pun">-</span><span class="pln"><span class="ruby"> max_standby_streaming_delay </span></span><span class="pun"><span class="ruby">=</span></span> <span class="lit"><span class="ruby"><span class="hljs-number">30</span>s</span></span>  <span class="com"><span class="ruby"><span class="hljs-comment"># 数据流备份的最大延迟时间</span></span></span>
    4. 1
       <span class="pun">-</span><span class="pln"><span class="ruby"> wal_receiver_status_interval </span></span><span class="pun"><span class="ruby">=</span></span> <span class="lit"><span class="ruby"><span class="hljs-number">1</span>s</span></span>  <span class="com"><span class="ruby"><span class="hljs-comment"># 从节点向主节点报告自身状态的最长间隔时间</span></span></span>
    5. 1
       <span class="pun">-</span><span class="pln"> hot_standby_feedback </span><span class="pun">=</span><span class="pln"> <span class="hljs-keyword">on</span>          </span><span class="com"><span class="hljs-comment"># 如果有错误的数据复制向主进行反馈</span></span>
  5. 修改数据目录属组属主。
    1. 1
      <span class="com"><span class="hljs-comment"># chown -R postgres.postgres /var/lib/pgsql/9.5/data</span></span>
  6. 启动服务,设置开机自启。
    1. 1
       <span class="com"><span class="hljs-comment"># systemctl start postgresql-9.5.service</span></span>
    2. 1
       <span class="com"><span class="hljs-comment"># systemctl enable postgresql-9.5.service</span></span>

步骤 5:检测验证

  1. 主节点中可查看到sender进程。
    1. 1
        <span class="com"><span class="hljs-comment"># ps aux |grep sender</span></span>
    2. 1
      <span class="pln">     postgres  </span><span class="lit">2916</span>  <span class="lit">0.0</span>  <span class="lit">0.3</span> <span class="lit">340388</span>  <span class="lit">3220</span> <span class="pun">?</span>        <span class="typ">Ss</span>   <span class="lit">15</span><span class="pun">:</span><span class="lit">38</span>   <span class="lit">0</span><span class="pun">:</span><span class="lit">00</span><span class="pln"> postgres</span><span class="pun">:</span><span class="pln"> wal sender     process replica </span><span class="lit">192.168</span><span class="pun">.</span><span class="lit">1.222</span><span class="pun">(</span><span class="lit">49640</span><span class="pun">)</span><span class="pln"> streaming </span><span class="lit">0</span><span class="pun">/</span><span class="pln">F01C1A8</span>
  2. 从节点中可查看到receiver进程。
    1. 1
      <span class="com"><span class="hljs-comment"># ps aux |grep receiver</span></span>
    2. 1
      <span class="pln"> postgres </span><span class="lit">23284</span>  <span class="lit">0.0</span>  <span class="lit">0.3</span> <span class="lit">387100</span>  <span class="lit">3444</span> <span class="pun">?</span>        <span class="typ">Ss</span>   <span class="lit">16</span><span class="pun">:</span><span class="lit">04</span>   <span class="lit">0</span><span class="pun">:</span><span class="lit">00</span><span class="pln"> postgres</span><span class="pun">:</span><span class="pln"> wal receiver process   streaming </span><span class="lit">0</span><span class="pun">/</span><span class="pln">F01C1A8</span>
  3. 主库中可查看到从库状态。
    1. 1
      <span class="pln"> replication</span><span class="pun">=<span class="hljs-comment">#</span></span><span class="pln"><span class="hljs-comment"> select </span></span><span class="pun"><span class="hljs-comment">*</span></span><span class="pln"><span class="hljs-comment"> from pg_stat_replication</span></span><span class="pun"><span class="hljs-comment">;</span></span>
    2. 1
      <span class="pln"> pid  </span><span class="pun">|</span><span class="pln"> usesysid </span><span class="pun">|</span><span class="pln"> usename </span><span class="pun">|</span><span class="pln"> application_name </span><span class="pun">|</span><span class="pln">  client_addr  </span><span class="pun">|</span><span class="pln"> client_hostname </span><span class="pun">|</span><span class="pln"> client_port </span><span class="pun">|</span><span class="pln">         backend_start         </span><span class="pun">|</span><span class="pln"> backend_xmin </span><span class="pun">|</span><span class="pln">   state   </span><span class="pun">|</span><span class="pln"> sent_location </span><span class="pun">|</span><span class="pln"> write_locati</span>
    3. 1
      <span class="pln"> on </span><span class="pun">|</span><span class="pln"> flush_location </span><span class="pun">|</span><span class="pln"> replay_location </span><span class="pun">|</span><span class="pln"> sync_priority </span><span class="pun">|</span><span class="pln"> sync_state </span>
    4. 1
       <span class="pun">------+----------+---------+------------------+---------------+-----------------+-------------</span>     <span class="pun">+-------------------------------+--------------+-----------+---------------+-------------</span>
    5. 1
       <span class="pun">---+----------------+-----------------+---------------+------------</span>
    6. 1
       <span class="lit">2916</span> <span class="pun">|</span>    <span class="lit">16393</span> <span class="pun">|</span><span class="pln"> replica </span><span class="pun">|</span><span class="pln"> walreceiver      </span><span class="pun">|</span> <span class="lit">192.168</span><span class="pun">.</span><span class="lit">1.222</span> <span class="pun">|</span>                 <span class="pun">|</span>       <span class="lit">49640</span> <span class="pun">|</span>    <span class="lit">2017</span><span class="pun">-</span><span class="lit">05</span><span class="pun">-</span><span class="lit">02</span> <span class="lit">15</span><span class="pun">:</span><span class="lit">38</span><span class="pun">:</span><span class="lit">06.188988</span><span class="pun">+</span><span class="lit">08</span> <span class="pun">|</span>         <span class="lit">1836</span> <span class="pun">|</span><span class="pln"> streaming </span><span class="pun">|</span> <span class="lit">0</span><span class="pun">/</span><span class="pln">F01C0C8     </span><span class="pun">|</span> <span class="lit">0</span><span class="pun">/</span><span class="pln">F01C0C8   </span>
    7. 1
       <span class="pun">|</span> <span class="lit">0</span><span class="pun">/</span><span class="pln">F01C0C8      </span><span class="pun">|</span> <span class="lit">0</span><span class="pun">/</span><span class="pln">F01C0C8       </span><span class="pun">|</span>             <span class="lit">0</span> <span class="pun">|</span><span class="pln"> async</span>
    8. 1
       <span class="pun">(</span><span class="lit">1</span><span class="pln"> rows</span><span class="pun">)</span>

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: