Pacemaker+Corosync搭建PostgreSQL集群
一、環境
$ cat /etc/redhat-release CentOS Linux release 7.0.1406 (Core) $ uname -a Linux zhaopin-5-90 3.10.0-123.el7.x86_64 #1 SMP Mon Jun 30 12:09:22 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux node1: 172.17.5.90 node2: 172.17.5.91 node3: 172.17.5.92 vip-master: 172.17.5.99 vip-slave: 172.17.5.98
二、配置Linux集群環境
1. 安裝Pacemaker和Corosync包
在所有節點執行：
$ sudo yum install -y pacemaker pcs psmisc policycoreutils-python postgresql-server
2. 禁用防火墻
在所有節點執行：
$ sudo setenforce 0 $ sudo sed -i.bak "s/SELINUX=enforcing/SELINUX=permissive/g" /etc/selinux/config $ sudo systemctl disable firewalld.service $ sudo systemctl stop firewalld.service $ sudo iptables --flush
3. 啟用pcs
在所有節點執行：
$ sudo systemctl start pcsd.service $ sudo systemctl enable pcsd.service ln -s '/usr/lib/systemd/system/pcsd.service' '/etc/systemd/system/multi-user.target.wants/pcsd.service' $ sudo passwd hacluster Changing password for user hacluster. New password: Retype new password: passwd: all authentication tokens updated successfully.
4. 集群認證
在任何一個節點上執行，這里選擇node1：
$ sudo pcs cluster auth 172.17.5.90 172.17.5.91 172.17.5.92 Username: hacluster Password: 172.17.5.90: Authorized 172.17.5.91: Authorized 172.17.5.92: Authorized
5. 同步配置
在node1上執行：
$ sudo pcs cluster setup --last_man_standing=1 --name pgcluster 172.17.5.90 172.17.5.91 172.17.5.92 Shutting down pacemaker/corosync services... Redirecting to /bin/systemctl stop pacemaker.service Redirecting to /bin/systemctl stop corosync.service Killing any remaining services... Removing all cluster configuration files... 172.17.5.90: Succeeded 172.17.5.91: Succeeded 172.17.5.92: Succeeded
6. 啟動集群
在node1上執行：
$ sudo pcs cluster start --all 172.17.5.90: Starting Cluster... 172.17.5.91: Starting Cluster... 172.17.5.92: Starting Cluster...
7. 檢驗
1）檢驗corosync
在node1上執行：
$ sudo pcs status corosyncMembership information
Nodeid Votes Name 1 1 172.17.5.90 (local) 2 1 172.17.5.91 3 1 172.17.5.92</pre>
2）檢驗pacemaker
$ sudo pcs status Cluster name: pgcluster WARNING: no stonith devices and stonith-enabled is not false WARNING: corosync and pacemaker node names do not match (IPs used in setup?) Last updated: Mon Oct 19 15:08:06 2015 Last change: Stack: unknown Current DC: NONE 0 nodes and 0 resources configured Full list of resources: PCSD Status: zhaopin-5-90 (172.17.5.90): Online zhaopin-5-91 (172.17.5.91): Online zhaopin-5-92 (172.17.5.92): Online Daemon Status: corosync: active/disabled pacemaker: active/disabled pcsd: active/disabled三、安裝和配置PostgreSQL
1. 創建目錄
在所有節點上執行：
$ sudo mkdir -p /data/postgresql/data $ sudo mkdir -p /data/postgresql/xlog_archive $ sudo chown -R postgres:postgres /data/postgresql/ $ sudo chmod 0700 /data/postgresql/data2. 初始化db
在node1上執行：
$ sudo su - postgres $ initdb -D /data/postgresql/data/ The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". fixing permissions on existing directory /data/postgresql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /data/postgresql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating collations ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok loading PL/pgSQL server-side language ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: postgres -D /data/postgresql/data or pg_ctl -D /data/postgresql/data -l logfile start3. 修改配置文件
在node1上執行：
$ vim /data/postgresql/data/postgresql.conf listen_addresses = '*' wal_level = hot_standby synchronous_commit = on archive_mode = on archive_command = 'cp %p /data/postgresql/xlog_archive/%f' max_wal_senders=5 wal_keep_segments = 32 hot_standby = on restart_after_crash = off replication_timeout = 5000 wal_receiver_status_interval = 2 max_standby_streaming_delay = -1 max_standby_archive_delay = -1 synchronous_commit = on restart_after_crash = off hot_standby_feedback = on $ vim /data/postgresql/data/pg_hba.conf local all all trust host all all 172.17.0.0/16 md5 host replication all 172.17.0.0/16 md54. 啟動
在node1上執行：
$ pg_ctl -D /data/postgresql/data/ start server starting [ 2015-10-16 08:51:31.451 UTC 53158 5620ba93.cfa6 1 0]LOG: redirecting log output to logging collector process [ 2015-10-16 08:51:31.451 UTC 53158 5620ba93.cfa6 2 0]HINT: Future log output will appear in directory "pg_log". $ psql -U postgres psql (9.2.13) Type "help" for help. postgres=# create role replicator with login replication password '8d5e9531-3817-460d-a851-659d2e51ca99'; CREATE ROLE postgres=# \q5. 制作slave
在node2和node3上執行：
$ sudo su - postgres $ pg_basebackup -h 172.17.5.90 -U postgres -D /data/postgresql/data/ -X stream -P could not change directory to "/home/wenhang.pan" 20127/20127 kB (100%), 1/1 tablespace node2: $ vim /data/postgresql/data/recovery.conf standby_mode = 'on' primary_conninfo = 'host=172.17.5.90 port=5432 user=replicator password=8d5e9531-3817-460d-a851-659d2e51ca99 application_name=node2' restore_command = '' recovery_target_timeline = 'latest' node3: $ vim /data/postgresql/data/recovery.conf standby_mode = 'on' primary_conninfo = 'host=172.17.5.90 port=5432 user=replicator password=8d5e9531-3817-460d-a851-659d2e51ca99 application_name=node3' restore_command = '' recovery_target_timeline = 'latest'6. 啟動slave
在node2和node3上執行：
$ pg_ctl -D /data/postgresql/data/ start pg_ctl: another server might be running; trying to start server anyway server starting -bash-4.2$ LOG: database system was interrupted while in recovery at log time 2015-10-16 08:19:07 GMT HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. LOG: entering standby mode LOG: redo starts at 0/3000020 LOG: consistent recovery state reached at 0/30000E0 LOG: database system is ready to accept read only connections LOG: streaming replication successfully connected to primary7. 查看集群狀態
在node1上執行：
$ psql -U postgres psql (9.2.13) Type "help" for help. postgres=# select * from pg_stat_replication ; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state -------+----------+------------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------ 10745 | 16384 | postgres | node2 | 172.17.5.91 | | 43013 | 2015-10-16 02:54:02.279384+00 | 1911 | streaming | 39/7B000060 | 39/7B000060 | 39/7B000060 | 39/7B000000 | 0 | async 50361 | 16384 | postgres | node3 | 172.17.5.92 | | 52073 | 2015-10-15 10:13:15.436745+00 | 1911 | streaming | 39/7B000060 | 39/7B000060 | 39/7B000060 | 39/7B000000 | 0 | async (2 rows) postgres=# \q 8. 停止PostgreSQL服務 在node1、node2和node3上執行： $ pg_ctl -D /data/postgresql/data/ -mi stop waiting for server to shut down.... done server stopped四、配置自動切換
1. 配置
在node1執行：
1）將配置步驟先寫到腳本
$ vim cluster_setup.sh將cib配置保存到文件pgsql_cfg
pcs cluster cib pgsql_cfg
在pacemaker級別忽略quorum
pcs -f pgsql_cfg property set no-quorum-policy="ignore"
禁用STONITH
pcs -f pgsql_cfg property set stonith-enabled="false"
設置資源粘性，防止節點在故障恢復后發生遷移
pcs -f pgsql_cfg resource defaults resource-stickiness="INFINITY"
設置多少次失敗后遷移
pcs -f pgsql_cfg resource defaults migration-threshold="3"
設置master節點虛ip
pcs -f pgsql_cfg resource create vip-master IPaddr2 ip="172.17.5.98" cidr_netmask="24" op start timeout="60s" interval="0s" on-fail="restart" op monitor timeout="60s" interval="10s" on-fail="restart" op stop timeout="60s" interval="0s" on-fail="block"
設置slave節點虛ip
pcs -f pgsql_cfg resource create vip-slave IPaddr2 ip="172.17.5.99" cidr_netmask="24" op start timeout="60s" interval="0s" on-fail="restart" op monitor timeout="60s" interval="10s" on-fail="restart" op stop timeout="60s" interval="0s" on-fail="block"
設置pgsql集群資源
pcs -f pgsql_cfg resource create pgsql pgsql pgctl="/opt/pgsql/bin/pg_ctl" psql="/opt/pgsql/bin/psql" pgdata="/data/postgresql/data/" config="/data/postgresql/data/postgresql.conf" rep_mode="sync" node_list="zhaopin-5-90 zhaopin-5-91 zhaopin-5-92" master_ip="172.17.5.98" repuser="replicator" primary_conninfo_opt="password=8d5e9531-3817-460d-a851-659d2e51ca99 keepalives_idle=60 keepalives_interval=5 keepalives_count=5" restore_command="cp /data/postgresql/xlog_archive/%f %p" restart_on_promote='true' op start timeout="60s" interval="0s" on-fail="restart" op monitor timeout="60s" interval="4s" on-fail="restart" op monitor timeout="60s" interval="3s" on-fail="restart" role="Master" op promote timeout="60s" interval="0s" on-fail="restart" op demote timeout="60s" interval="0s" on-fail="stop" op stop timeout="60s" interval="0s" on-fail="block" # 設置master/slave模式 pcs -f pgsql_cfg resource master pgsql-cluster pgsql master-max=1 master-node-max=1 clone-max=3 clone-node-max=1 notify=true
配置master ip組
pcs -f pgsql_cfg resource group add master-group vip-master
配置slave ip組
pcs -f pgsql_cfg resource group add slave-group vip-slave
配置master ip組綁定master節點
pcs -f pgsql_cfg constraint colocation add master-group with master pgsql-cluster INFINITY
配置啟動master節點
pcs -f pgsql_cfg constraint order promote pgsql-cluster then start master-group symmetrical=false score=INFINITY
配置停止master節點
pcs -f pgsql_cfg constraint order demote pgsql-cluster then stop master-group symmetrical=false score=0
配置slave ip組綁定slave節點
pcs -f pgsql_cfg constraint colocation add slave-group with slave pgsql-cluster INFINITY
配置啟動slave節點
$ sudo pcs -f pgsql_cfg constraint order promote pgsql-cluster then start slave-group symmetrical=false score=INFINITY
配置停止slave節點
pcs -f pgsql_cfg constraint order demote pgsql-cluster then stop slave-group symmetrical=false score=0
把配置文件push到cib
pcs cluster cib-push pgsql_cfg</pre>
2）執行操作文件
$ sudo sh cluster_setup.sh2. 查看狀態
1）查看cluster狀態
在node1上執行：
$ sudo pcs status Cluster name: pgcluster WARNING: corosync and pacemaker node names do not match (IPs used in setup?) Last updated: Mon Oct 19 15:10:52 2015 Last change: Mon Oct 19 15:10:12 2015 by root via crm_attribute on zhaopin-5-92 Stack: corosync Current DC: zhaopin-5-90 (version 1.1.13-a14efad) - partition with quorum 3 nodes and 5 resources configured Online: [ zhaopin-5-90 zhaopin-5-91 zhaopin-5-92 ] Full list of resources: Master/Slave Set: pgsql-cluster [pgsql] Masters: [ zhaopin-5-92 ] Slaves: [ zhaopin-5-90 zhaopin-5-91 ] Resource Group: master-group vip-master (ocf::heartbeat:IPaddr2): Started zhaopin-5-92 Resource Group: slave-group vip-slave (ocf::heartbeat:IPaddr2): Started zhaopin-5-90 PCSD Status: zhaopin-5-90 (172.17.5.90): Online zhaopin-5-91 (172.17.5.91): Online zhaopin-5-92 (172.17.5.92): Online Daemon Status: corosync: active/disabled pacemaker: active/disabled pcsd: active/disabled2）查看PostgreSQL集群狀態
在node3上執行：
$ psql -U postgres psql (9.2.13) Type "help" for help. postgres=# select * from pg_stat_replication ; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state -------+----------+------------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------ 11522 | 16384 | replicator | zhaopin-5-91 | 172.17.5.91 | | 41356 | 2015-10-19 07:10:01.898257+00 | 1915 | streaming | 81/D9000000 | 81/D9000000 | 81/D9000000 | 81/D9000000 | 2 | potential 11532 | 16384 | replicator | zhaopin-5-90 | 172.17.5.99 | | 41786 | 2015-10-19 07:10:01.945532+00 | 1915 | streaming | 81/D9000000 | 81/D9000000 | 81/D9000000 | 81/D9000000 | 1 | sync (2 rows)五、參考
從頭開始搭建集群：
PgSQL Replicated Cluster：
http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster 來自：