2016年5月22日 星期日

在 CentOS7/RHEL7 上架設多部 PostgreSQL

設定目標:
  • 架設多部 PostgreSQL Server,並且設定 Replication 進行同步資料與備援!
事前說明與準備:
  • PostgreSQL 基本安裝,請參考這一篇的設定!
  • 本篇將架設 9.4 版多部 PostgreSQL Server!一部 Pgpool-II,一部 Master Server 以及一部 Standby Server!
  • 示意圖如下:
    pgpool server: 192.168.100.241 pgs1 server: 192.168.100.231 pgs2 server: 192.168.100.232
  • 本篇將利用 repmgr 與 pgpool-II 來進行實作!

(一)安裝前設定流程:
  1. 先製作 ssh 金鑰對,方便連線登入到各個 Server:
    [root@pgpool ~]#ssh-keygen -t rsa
    [root@pgs1 ~]# ssh-keygen -t rsa
    [root@pgs2 ~]# ssh-keygen -t rsa
    
  2. 將製作好的 ssh 公鑰,互相送到各個 Server:
    (pgpool Server 上的設定)
    [root@pgpool ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@pgs1
    [root@pgpool ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@pgs2
    
    (pgs1 Server 上的設定)
    [root@pgs1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@pgpool
    [root@pgs1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@pgs2
    
    (pgs2 Server 上的設定)
    [root@pgs2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@pgpool
    [root@pgs2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@pgs1
    
  3. 在各個 Server 上安裝 PostgreSQL 的 repo 檔案:
    [root@pgpool ~]#yum -y install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
    [root@pgs1 ~]# yum -y install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
    [root@pgs2 ~]# yum -y install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
    
    ※上述網址為 PostgreSQL 官方提供的網址!

  4. 提前先新增 postgres 使用者與群組,以利後續的操作:
    (製作 pgpool Server上的 postgres 使用者與群組)
    [root@pgpool ~]# mkdir /var/lib/pgsql
    [root@pgpool ~]# groupadd -r -g 26 postgres
    [root@pgpool ~]# useradd -r -u 26 -M -d /var/lib/pgsql -n -g postgres postgres
    [root@pgpool ~]# passwd postgres
    [root@pgpool ~]# chown postgres:postgres -R /var/lib/pgsql/
    
    (製作 pgs1 Server上的 postgres 使用者與群組)
    [root@pgs1 ~]# mkdir /var/lib/pgsql
    [root@pgs1 ~]# groupadd -r -g 26 postgres
    [root@pgs1 ~]# useradd -r -u 26 -M -d /var/lib/pgsql -n -g postgres postgres
    [root@pgs1 ~]# passwd postgres
    [root@pgs1 ~]# chown postgres:postgres -R /var/lib/pgsql/
    
    (製作 pgs2 Server上的 postgres 使用者與群組)
    [root@pgs2 ~]# mkdir /var/lib/pgsql
    [root@pgs2 ~]# groupadd -r -g 26 postgres
    [root@pgs2 ~]# useradd -r -u 26 -M -d /var/lib/pgsql -n -g postgres postgres
    [root@pgs2 ~]# passwd postgres
    [root@pgs2 ~]# chown postgres:postgres -R /var/lib/pgsql/
    
    
  5. 仿照 root 製作 ssh 公鑰對方式,互相送到各個 Server,以利將來登入設定:
    (製作 pgpool 上的 postgres 憑證,並送至其他 Server)
    [root@pgpool ~]# su - postgres
    -bash-4.2$ ssh-keygen -t rsa
    -bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@pgs1
    -bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@pgs2
    
    (製作 pgs1 上的 postgres 憑證,並送至其他 Server)
    [root@pgs1 ~]# su - postgres
    -bash-4.2$ ssh-keygen -t rsa
    -bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@pgpool
    -bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@pgs2
    
    (製作 pgs2 上的 postgres 憑證,並送至其他 Server)
    [root@pgs2 ~]# su - postgres
    -bash-4.2$ ssh-keygen -t rsa
    -bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@pgs1
    -bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@pgpool
    
  6. 注意 SELinux 的修正:
    [root@pgpool ~]# /sbin/restorecon -vR /var/lib/pgsql/
    [root@pgs1 ~]# /sbin/restorecon -vR /var/lib/pgsql/
    [root@pgs2 ~]# /sbin/restorecon -vR /var/lib/pgsql/
    

(二)在 Master 上安裝設定 PostgreSQL Server:
※Master Server 為本例的 pgs1 Server!
  1. 利用 YUM 安裝必要套件:
    #yum -y install postgresql94 postgresql94-server postgresql94-contrib rsync
    
  2. 安裝 repmgr 套件:
    #yum -y install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/repmgr94-3.1.2-1.rhel7.x86_64.rpm
    
    ※ repmgr 套件是用來監控 PostgreSQL Server 對其他 PostgreSQL Server 的 Replication 動作!
  3. 初始化 PostgreSQL Database:
    #/usr/pgsql-9.4/bin/postgresql94-setup initdb
    
  4. 編輯 PostgreSQL 連線設定檔:
    #vim /var/lib/pgsql/9.4/data/pg_hba.conf
    (只修改需要修改的部份)
    local   all             all                             trust
    host    all             all         127.0.0.1/32        md5
    host    all             all         ::1/128             md5
    host    repmgr          repmgr      192.168.100.231/32  trust
    host    replication     repmgr      192.168.100.231/32  trust
    host    repmgr          repmgr      192.168.100.232/32  trust
    host    replication     repmgr      192.168.100.232/32  trust
    host    all             pgpool      192.168.100.241/32  trust
    host    all             all         192.168.100.241/32  md5
    
  5. 編輯 PostgreSQL 設定檔:
    #vim /var/lib/pgsql/9.4/data/postgresql.conf
    (只修改需要用到的部份)
    listen_addresses = '*'
    max_connections = 200
    shared_buffers = 512MB
    effective_cache_size = 1536MB
    work_mem = 2621kB
    maintenance_work_mem = 128MB
    default_statistics_target = 100
    shared_preload_libraries = 'repmgr_funcs'
    wal_level = hot_standby
    wal_buffers = 16MB
    checkpoint_segments = 32
    checkpoint_completion_target = 0.7
    archive_mode = on
    archive_command = 'cd .'
    max_wal_senders = 1
    wal_keep_segments = 5000
    wal_sender_timeout = 1s
    hot_standby = on
    log_destination = 'stderr'
    logging_collector = on
    log_directory = 'pg_log' 
    log_filename = 'postgresql-%a.log' 
    log_truncate_on_rotation = on
    log_rotation_age = 1d
    log_rotation_size = 0
    log_min_duration_statement = 0
    log_checkpoints = on
    log_connections = on
    log_disconnections = on
    log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
    log_lock_waits = on
    log_statement = 'all'
    log_temp_files = 0
    datestyle = 'iso, mdy'
    timezone = 'Europe/Brussels'
    lc_messages = 'en_US.UTF-8'
    lc_monetary = 'en_US.UTF-8'
    lc_numeric = 'en_US.UTF-8'
    lc_time = 'en_US.UTF-8'
    default_text_search_config = 'pg_catalog.english'
    
  6. 建立 repmgr 專用目錄:
    #su - postgres
    $ mkdir -p /var/lib/pgsql/repmgr/
    
  7. 編修 repmgr 設定檔:
    #vim /var/lib/pgsql/repmgr/repmgr.conf
    cluster=db_cluster
    node=1
    node_name=pgs1
    conninfo='host=pgs1 user=repmgr dbname=repmgr'
    pg_bindir=/usr/pgsql-9.4/bin/
    master_response_timeout=5
    reconnect_attempts=2
    reconnect_interval=2
    failover=manual
    promote_command='/usr/pgsql-9.4/bin/repmgr standby promote -f /var/lib/pgsql/repmgr/repmgr.conf'
    follow_command='/usr/pgsql-9.4/bin/repmgr standby follow -f /var/lib/pgsql/repmgr/repmgr.conf'
    
  8. 開啟防火牆:
    #firewall-cmd --permanent --add-service=postgresql
    #firewall-cmd --reload
    
  9. 啟動 PostgreSQL Server:
    #systemctl enable postgresql-9.4
    #systemctl start postgresql-9.4
    
  10. 建立 Replication 和 rempgr 的使用者,以及 rempgr 資料庫:
    #su - postgres
    $ psql
    postgres=# CREATE ROLE pgpool SUPERUSER CREATEDB CREATEROLE INHERIT REPLICATION LOGIN ENCRYPTED PASSWORD 'secret';
    postgres=# CREATE USER repmgr SUPERUSER LOGIN ENCRYPTED PASSWORD 'secret';
    postgres=# CREATE DATABASE repmgr OWNER repmgr;
    postgres=# \q
    
  11. 將 pgs1 Server 註冊成 rempgr 的 master node:
    #su - postgres
    $ /usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf master register
    

(三)在 Standby 上安裝設定 PostgreSQL Server:
※Standby Server 為本例的 pgs2 Server!
  1. 在 Standby Server 上安裝 PostgreSQL Server:
    #yum -y install postgresql94 postgresql94-server postgresql94-contrib rsync
    
  2. 安裝 repmgr 套件:
    #yum -y install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/repmgr94-3.1.2-1.rhel7.x86_64.rpm
    
  3. 所有設定檔,可由同步 Master Server 取得:
    #su - postgres
    $/usr/pgsql-9.4/bin/repmgr -D /var/lib/pgsql/9.4/data -d repmgr -p 5432 -U repmgr -R postgres standby clone pgs1
    
  4. 建立 repmgr 專用目錄:
    #su - postgres
    $ mkdir -p /var/lib/pgsql/repmgr/
    
  5. 編修 repmgr 設定檔:
    #vim /var/lib/pgsql/repmgr/repmgr.conf
    cluster=db_cluster
    node=2
    node_name=pgs2
    conninfo='host=pgs2 user=repmgr dbname=repmgr'
    pg_bindir=/usr/pgsql-9.4/bin/
    master_response_timeout=5
    reconnect_attempts=2
    reconnect_interval=2
    failover=manual
    promote_command='/usr/pgsql-9.4/bin/repmgr standby promote -f /var/lib/pgsql/repmgr/repmgr.conf'
    follow_command='/usr/pgsql-9.4/bin/repmgr standby follow -f /var/lib/pgsql/repmgr/repmgr.conf'
    
  6. 開啟防火牆:
    #firewall-cmd --permanent --add-service=postgresql
    #firewall-cmd --reload
    
  7. 啟動 PostgreSQL Server:
    #systemctl enable postgresql-9.4
    #systemctl start postgresql-9.4
    
  8. 將 pgs2 Server 註冊成 rempgr 的 standby node:
    #su - postgres
    $ /usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby register
    

(四)測試 PostgreSQL Server 的 Replication 功能:
  1. 在 Standby Server 上查詢 rempgr 的情況:
    #su - postgres
    $/usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show
    
    (顯示狀況如下:)
    Role      | Name | Upstream | Connection String
    ----------+------|----------|------------------------------------
    * master  | pgs1 |          | host=pgs1 user=repmgr dbname=repmgr
      standby | pgs2 | pgs1     | host=pgs2 user=repmgr dbname=repmgr
    
  2. 在 Standby Server 上查詢 PostgreSQL Database 的情況:
    $psql -U postgres -c "\list"
    
    (顯示狀況如下:)
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    -----------+----------+----------+-------------+-------------+-----------------------
     postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     repmgr    | repmgr   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
    (4 rows)
    
  3. 在 Master Server 上建立一個測試用資料庫:
    #su - postgres
    $psql -U postgres -c "CREATE DATABASE test"
    
  4. 在 Standby Server 上,再次查詢 PostgreSQL Database 的情況:
    $psql -U postgres -c "\list"
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    -----------+----------+----------+-------------+-------------+-----------------------
     postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     repmgr    | repmgr   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
    (5 rows)
    
    ※可以發現有新資料庫已經被複製過來了~~~
  5. 在 Standby Server 上,新增測試 database:
    $psql -U postgres -c "CREATE DATABASE test2"
    (顯示以下訊息:)
    ERROR:  cannot execute CREATE DATABASE in a read-only transaction
    

(五)設定PgPool Server 功能:
  1. 在 PgPool Server上安裝需要套件:
    #yum install postgresql94 pgpool-II-94
    
  2. 從範例檔複製設定檔:
    #cp /etc/pgpool-II-94/pgpool.conf.sample-stream /etc/pgpool-II-94/pgpool.conf
    
  3. 編輯設定檔:
    #vim /etc/pgpool-II-94/pgpool.conf
    (只要新增或是編修下列設定參數即可:)
    listen_addresses = '*'
    port = 5432
    backend_hostname0 = 'pgs1'
    backend_port0 = 5432
    backend_weight0 = 1
    backend_data_directory0 = '/var/lib/pgsql/9.4/data'
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    backend_hostname1 = 'pgs2'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/var/lib/pgsql/9.4/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
    enable_pool_hba = on
    pid_file_name = '/var/run/pgpool-II-94/pgpool.pid'
    sr_check_user = 'pgpool'
    sr_check_password = 'secret'
    health_check_period = 10
    health_check_user = 'pgpool'
    health_check_password = 'secret'
    failover_command = '/etc/pgpool-II-94/failover.sh %d %H'  
    recovery_user = 'pgpool'
    recovery_password = 'secret'
    recovery_1st_stage_command = 'basebackup.sh'
    
  4. 編輯 failover.sh shell scripts 檔案:
    #vim /etc/pgpool-II-94/failover.sh
    #!/bin/sh
    failed_node=$1
    new_master=$2
    (
    date
    echo "Failed node: $failed_node"
    set -x
    /usr/bin/ssh -T -l postgres $new_master "/usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-"
    exit 0;
    ) 2>&1 | tee -a /tmp/pgpool_failover.log
    
  5. 修改 failover.sh 檔案權限:
    #chmod 755 /etc/pgpool-II-94/failover.sh
    
  6. 編輯 pool_hba.conf 檔案:
    #vim /etc/pgpool-II-94/pool_hba.conf
    # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
    local   all         all                               trust
    host    all         all         127.0.0.1/32          trust
    host    all         all         ::1/128               trust
    host    all         all         0.0.0.0/0             md5
    
  7. 編輯 pool_passwd 檔案:
    #touch /etc/pgpool-II-94/pool_passwd
    #chown postgres:postgres /etc/pgpool-II-94/pool_passwd
    #su - postgres
    $pg_md5 -m -u pgpool secret
    
    ※可設定所有需要連結 pgpool 資料庫的使用者密帳!
  8. 利用 PCP 來連結管理 pgpool 功能:
    #echo "pgpool:$(pg_md5 secret)"| tee /etc/pgpool-II-94/pcp.conf
    
  9. 開啟防火牆:
    #firewall-cmd --permanent --add-service=postgresql
    #firewall-cmd --reload
    
  10. 啟動 PgPool Server:
    #systemctl enable pgpool-II-94
    #systemctl start pgpool-II-94
    

  11. (六)失效測試:

參考文獻:

  1. http://blog.pulipuli.info/2015/06/postgresqlpgpool2-ha-structure-of.html
  2. http://www.pgpool.net/docs/latest/pgpool-en.html
  3. http://www.pgpool.net/docs/latest/pgpool-zh_cn.html
  4. http://jensd.be/591/linux/setup-a-redundant-postgresql-database-with-repmgr-and-pgpool
  5. https://www.keyup.eu/en/blog/89-replication-and-load-balancing-with-postgresql-and-pgpool2
  6. http://yum.postgresql.org/repopackages.php
  7. http://dz.sdut.edu.cn/blog/subaochen/2013/08/%E5%85%B3%E4%BA%8Epgpool-ii%E7%9A%84online-recovery/
  8. http://www.ahlinux.com/postgresql/9419.html
  9. http://linux.xvx.cz/2014/10/loadbalancing-of-postgresql-databases.html
  10. https://github.com/2ndQuadrant/repmgr/blob/master/README.md