PostgreSQL+Pgpool實現(xiàn)HA主備切換的操作

更新時間：2020年12月30日 10:33:11 作者：繁星為伴日月同行

這篇文章主要介紹了PostgreSQL+Pgpool實現(xiàn)HA主備切換操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

PostgreSQL流復(fù)制實現(xiàn)HA主備切換

環(huán)境說明和主機規(guī)劃

操作系統(tǒng)	主機名	主機	角色	端口
CentOS 7	master	10.0.0.11	PG-Master	54321
CentOS 7	slave	10.0.0.12	PG-Slave	54321
CentOS 7	pool	10.0.0.13	pgpool	54321

基礎(chǔ)環(huán)境配置（所有主機操作）

配置HOSTS

echo -e "10.0.0.11 master\n10.0.0.12 slave\n10.0.0.13 pool" >> /etc/hosts # 執(zhí)行一次即可

配置統(tǒng)一的時間（若已配置，請忽略）

yum install -y ntpdate && ntpdate ntp1.aliyun.com
echo -e "# sync time from ntp1.aliyun.com\n5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com > /dev/null 2>&1
" >> /var/spool/cron/root # 寫入定時任務(wù)，執(zhí)行一次即可

創(chuàng)建postgres用戶

useradd postgres && echo "your_password" | passwd --stdin postgres

配置免密鑰登陸

su - postgres
ssh-keygen -t rsa -f /home/postgres/.ssh/id_rsa -P "" 
cd ~/.ssh/
ssh-copy-id postgres@master # 三臺主機執(zhí)行
scp authorized_keys postgres@slave:~/.ssh # 只在master主機執(zhí)行
scp authorized_keys postgres@pool:~/.ssh # 只在master主機執(zhí)行

安裝Postgresql數(shù)據(jù)庫（PG9.6）

yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

yum install -y postgresql96-server postgresql96-contrib postgresql96 postgresql96-libs

創(chuàng)建統(tǒng)一的目錄結(jié)構(gòu)

mkdir /data1/pg_{data,bin,logs} -p

chown -R postgres.postgres /data1/

修改系統(tǒng)變量

vi /etc/profile #增加以下內(nèi)容
export PGHOME=/usr/pgsql-9.6/
export PGDATA=/data1/pg_data
export PGPORT=54321
export PATH=$PATH:$PGHOME/bin
# 生效
source /etc/profile

PostgreSQL流復(fù)制結(jié)構(gòu)（master和slave主機操作）

master主機操作

初始化系統(tǒng)

/usr/pgsql-9.6/bin/postgresql96-setup initdb

vi /usr/lib/systemd/system/postgresql-9.6.service

修改postgresql-9.6.service

內(nèi)容如下:

# Include the default config:
.include /usr/lib/systemd/system/postgresql-9.6.service

[Service]
Environment=PGDATA=/data1/pg_data

重啟PG服務(wù)

systemctl daemon-reload
su - postgres -c '/usr/pgsql-9.6/bin/initdb -D /data1/pg_data'
systemctl restart postgresql-9.6
systemctl enable postgresql-9.6.service

修改系統(tǒng)配置（以下用postgres用戶操作）

cp /data1/pg_data/pg_hba.conf{,.bak} 
cat >/data1/pg_data/pg_hba.conf<<EOF
local all    all            trust
host all    all      10.0.0.11/32   trust
host all    all      10.0.0.12/32   trust
host all    all      0.0.0.0/0    md5
host all    all      ::1/128     trust
host replication  stream_replication  0.0.0.0/0    md5
EOF
#host replication  stream_replication  0.0.0.0/0    md5 為流復(fù)制用戶

64G

cp /data1/pg_data/postgresql.conf{,.bak}
cat >/data1/pg_data/postgresql.conf<<EOF
listen_addresses = '*'
port = 54321
max_connections = 256
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 64MB
maintenance_work_mem = 2GB
min_wal_size = 2GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
wal_level = hot_standby
wal_log_hints = on
max_wal_senders = 1
hot_standby = on
logging_collector = on
log_directory = 'pg_log'
EOF
#操作完記得重啟 pg_ctl restart

128G

listen_addresses = '*'
port = 54321
max_connections = 256
shared_buffers = 32GB
effective_cache_size = 96GB
work_mem = 128MB
maintenance_work_mem = 2GB
min_wal_size = 2GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
wal_level = hot_standby
wal_log_hints = on
max_wal_senders = 1
hot_standby = on
logging_collector = on
log_directory = 'pg_log'

在主庫中創(chuàng)建流復(fù)制用戶(stream_replication)和PGPool用戶(srcheck)

CREATE USER stream_replication replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 'your_password';

CREATE USER srcheck replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 'your_password';

修改主庫pg_hba.conf文件（已操作見cat >/data1/pg_data/pg_hba.conf<<EOF）

host replication stream_replication 0.0.0.0/0 md5

slave主機操作

初始化系統(tǒng)

/usr/pgsql-9.6/bin/postgresql96-setup initdb

vi /usr/lib/systemd/system/postgresql-9.6.service

修改postgresql-9.6.service

內(nèi)容如下:

# Include the default config:
.include /usr/lib/systemd/system/postgresql-9.6.service

[Service]
Environment=PGDATA=/data1/pg_data

重啟PG服務(wù)

systemctl daemon-reload

基礎(chǔ)備份復(fù)制到備庫服務(wù)器

rm -rf /data1/pg_data # 如果沒有重要數(shù)據(jù)可操作，主要為同步主庫路徑

su - postgres -c 'pg_basebackup -D $PGDATA --format=p -h master -p 54321 -U stream_replication -W'

修改備庫配置信息

cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf

vi $PGDATA/recovery.conf

增加以下內(nèi)容

standby_mode='on'
primary_conninfo = 'host=master port=54321 user=stream_replication password=your_password'
restore_command = ''
recovery_target_timeline = 'latest'
# 重啟PG服務(wù)
systemctl restart postgresql-9.6
systemctl enable postgresql-9.6.service

驗證

主節(jié)點執(zhí)行

create table test (id int4, create_time timestamp(0) without time zone);
insert into test values (1, now());
select * from test;

備節(jié)點執(zhí)行

select * from test;

其他查詢

進入測試數(shù)據(jù)庫test，主庫上執(zhí)行如下命令返回f，備庫上返回t。 select pg_is_in_recovery();

執(zhí)行如下命令查看快照，它返回主庫記錄點、備庫記錄點；主庫每增加一條寫入，記錄點的值就會加1。

select txid_current_snapshot();

執(zhí)行如下命令可以查看主備同步狀態(tài)。

select * from pg_stat_replication;

字段state顯示的同步狀態(tài)有：startup（連接中）、catchup（同步中）、streaming（同步）；字段sync_state顯示的模式有：async（異步）、sync（同步）、potential（雖然現(xiàn)在是異步模式，但是有可能升級到同步模式）。

主備切換

假設(shè)主庫崩潰了，備庫如何從只讀狀態(tài)切換為讀寫狀態(tài)呢？只要把備庫的postgresql.conf中hot_standby修改為off，并且刪除recovery.conf，然后重啟庫就可以提供服務(wù)了。

PGPool2（pool主機操作）

安裝PGPool2

yum install -y http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-II-release-3.6-1.noarch.rpm
yum -y install pgpool-II-pg96 pgpool-II-pg96-debuginfo pgpool-II-pg96-devel pgpool-II-pg96-extensions
systemctl enable pgpool.service #開啟自動啟動

添加Pgpool-II運行用戶

useradd postgres # 環(huán)境準(zhǔn)備時已操作
chown -R postgres.postgres /etc/pgpool-II
chown -R postgres.postgres /var/run/pgpool/

配置pool_hba.conf

cp /etc/pgpool-II/pool_hba.conf{,.bak}

vi /etc/pgpool-II/pool_hba.conf

增加內(nèi)容

host all all 0.0.0.0/0 md5

配置pcp.conf

主節(jié)點登陸后執(zhí)行：

postgres=# select rolname,rolpassword from pg_authid;
  rolname  |    rolpassword    
--------------------+-------------------------------------
 pg_signal_backend | 
 srcheck   | md5662c10f61b27a9ab38ce69157186b25f
 postgres   | md5d3612d57ee8d4c147cf27b11e3a0974d
 stream_replication | md59279ef6b904bc483e4f85e6d44cfc0ed
(4 rows)

vi /etc/pgpool-II/pool_passwd

增加SQL執(zhí)行結(jié)果的內(nèi)容，形式為$rolname:$rolpassword例如：

srcheck:md5662c10f61b27a9ab38ce69157186b25f

或者：

pg_md5 -u postgres your_password

vi /etc/pgpool-II/pcp.conf ## 加入 postgres:上一命令的輸出

配置pgpool.conf

cp /etc/pgpool-II/pgpool.conf{,.bak}

vi /etc/pgpool-II/pgpool.conf

內(nèi)容如下:

# CONNECTIONS

listen_addresses = '*'
port = 54321
socket_dir = '/var/run/pgpool'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/pgpool'

# - Backend Connection Settings -

backend_hostname0 = 'master'
backend_port0 = 54321
backend_weight0 = 1
backend_data_directory0 = '/data1/pg_data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'slave'
backend_port1 = 54321
backend_weight1 = 1
backend_data_directory1 = '/data1/pg_data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# - Authentication -

enable_pool_hba = on
pool_passwd = 'pool_passwd'

# FILE LOCATIONS

pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/data1/pg_logs'

replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

sr_check_period = 5
sr_check_user = 'srcheck'
sr_check_password = '123456'
sr_check_database = 'postgres'

# HEALTH CHECK 健康檢查

health_check_period = 10
health_check_timeout = 20
health_check_user = 'srcheck'
health_check_password = '123456'
health_check_database = 'postgres'

# FAILOVER AND FAILBACK

failover_command = '/data1/pg_bin/failover_stream.sh %H'

failover_stream.sh腳本

vim /data1/pg_bin/failover_stream.sh
chmod 777 /data1/pg_bin/failover_stream.sh
chmod u+s /sbin/ifconfig 
chmod u+s /usr/sbin
pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 & ## 啟動
pgpool -m fast stop ## 關(guān)閉

failover_stream.sh內(nèi)容：

#! /bin/sh 
# Failover command for streaming replication. 
# Arguments: $1: new master hostname. 

new_master=$1 
trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA" 

# Prompte standby database. 
/usr/bin/ssh -T $new_master $trigger_command 

exit 0;

登陸設(shè)置

當(dāng)執(zhí)行pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 &后可查看集群狀態(tài)：

[postgres@pool pgpool-II]$ psql -p 54321 -h 10.0.0.13 -U srcheck -d postgres

postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay 
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
 0  | master | 54321 | up  | 0.500000 | primary | 0   | false    | 0
 1  | slave | 54321 | up  | 0.500000 | standby | 0   | true    | 0
(2 rows)

如果未發(fā)現(xiàn)集群狀態(tài)，請在master和slave主機分別執(zhí)行以下操作：

[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 9898 -n 0
[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 9898 -n 1
#詳情查詢命令pcp_attach_node

HA切換

模擬master主機宕機

Master端：

[postgres@master ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

當(dāng)前集群狀態(tài)

[postgres@pool ~]$ psql -p 54321 -h 10.0.0.13 -U srcheck -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0  | master | 5432 | down| 0.500000 | standby | 0    | false | 0
 1  | slave  | 5432 | up  | 0.500000 | primary | 0    | true | 0
(2 rows)

發(fā)現(xiàn)master已經(jīng)是standby了，且down機了

修改master，啟動

當(dāng)master主機宕機后，此時slave主機PG數(shù)據(jù)庫成為主庫，修改master成為slave的從庫即可

[postgres@master ~]$ vim recovery.conf
standby_mode='on'
primary_conninfo = 'host=slave port=54321 user=stream_replication password=your_password'
restore_command = ''
recovery_target_timeline = 'latest'

同步時間線

#如果報時間線沖突落后，先停掉pg服務(wù)，然后執(zhí)行同步時間線，否知直接看狀態(tài)
[postgres@master ~]$ pg_rewind --target-pgdata=/data1/pg_data --source-server='host=slave port=54321 user=postgres dbname=postgres'
servers diverged at WAL position 0/5000098 on timeline 1
rewinding from last common checkpoint at 0/5000028 on timeline 1
Done!
# 重新啟動數(shù)據(jù)庫
[postgres@master ~]$ pg_ctl start

再次查看當(dāng)前狀態(tài)

[postgres@pool ~]$ psql -p 54321 -h 10.0.0.13 -U srcheck -d postgres
postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0  | master | 5432 | down| 0.500000 | standby | 0    | false | 0
 1  | slave  | 5432 | up  | 0.500000 | primary | 0    | true | 0
(2 rows)

#注意雖然master已經(jīng)啟動了，但是還是down，需要手動將master節(jié)點添加進pgpool，master的node_id是0，所以-n 0
[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 54321 -n 0
#提示輸入密碼，輸入pcp管理密碼
#查看當(dāng)前狀態(tài)
[postgres@pool ~]$ psql -p 54321 -h 10.0.0.13 -U srcheck -d postgres
postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0  | master | 5432 | up | 0.500000 | standby | 0    | false | 0
 1  | slave  | 5432 | up  | 0.500000 | primary | 0    | true | 0
(2 rows)

現(xiàn)在兩個節(jié)點都是up了。

主從兩節(jié)點pgpool健康檢查腳本(pgpool_check.sh)

說明：此腳本是基于PGpool只安裝到master和slave兩個主機上的情況下使用，在master主機有了pgpool進程后，可在slave主機執(zhí)行sh pgpool_check.sh & 即可

#! /bin/bash
# Check Master host pgpool-process

while true
do
 pgcount=$(nmap 10.0.0.11|egrep '9898|9999'|wc -l)

 if [ $pgcount -eq 2 ] ; then
  echo 'Master host pgpool is GOOD！??！' > /dev/null 2>&1
 else
  echo -e "Master host pgpool is \033[31m BAD!!! \033[0m"
  echo -e "Master host pgpool is \033[31m BAD!!! \033[0m"
  echo -e "Master host pgpool is \033[31m BAD!!! \033[0m"
  echo -e "SYSTEM WILL DO THE SHELL : \033[34m su - postgres -c 'pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 &' \033[0m"
  su - postgres -c 'pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 &'
  pgport=$(netstat -lntup|egrep '9898|9999'|wc -l)
  [ $pgport -gt 0 ] && echo -e "Slave host pgpool is \033[32m RUNNING!!! \033[0m"
  exit 0
 fi
done

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章: