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
張貼者： 於
標籤： ,