| 操作系統(tǒng) | 主機(jī)名 | 主機(jī) | 角色 | 端口 |
|---|---|---|---|---|
| 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)境配置（所有主機(jī)操作）
配置HOSTS
echo -e "10.0.0.11 master\n10.0.0.12 slave\n10.0.0.13 pool" >> /etc/hosts # 執(zhí)行一次即可
配置統(tǒng)一的時(shí)間（若已配置，請忽略）
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 # 寫入定時(shí)任務(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 # 三臺主機(jī)執(zhí)行 scp authorized_keys postgres@slave:~/.ssh # 只在master主機(jī)執(zhí)行 scp authorized_keys postgres@pool:~/.ssh # 只在master主機(jī)執(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
master主機(jī)操作
初始化系統(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.confEOF
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.confEOF
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.confEOF）
host replication stream_replication 0.0.0.0/0 md5
slave主機(jī)操作
初始化系統(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
驗(yàn)證
主節(jié)點(diǎn)執(zhí)行
create table test (id int4, create_time timestamp(0) without time zone); insert into test values (1, now()); select * from test;
備節(jié)點(diǎn)執(zhí)行
select * from test;
其他查詢
進(jìn)入測試數(shù)據(jù)庫test，主庫上執(zhí)行如下命令返回f，備庫上返回t。 select pg_is_in_recovery();
執(zhí)行如下命令查看快照，它返回主庫記錄點(diǎn)、備庫記錄點(diǎn)；主庫每增加一條寫入，記錄點(diǎn)的值就會加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主機(jī)操作）
安裝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 #開啟自動(dòng)啟動(dòng)
添加Pgpool-II運(yùn)行用戶
useradd postgres # 環(huán)境準(zhǔn)備時(shí)已操作 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é)點(diǎn)登陸后執(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 ## 啟動(dòng) 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)，請?jiān)趍aster和slave主機(jī)分別執(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
模擬master主機(jī)宕機(jī)
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機(jī)了
修改master，啟動(dòng)
當(dāng)master主機(jī)宕機(jī)后，此時(shí)slave主機(jī)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'
同步時(shí)間線
#如果報(bào)時(shí)間線沖突落后，先停掉pg服務(wù)，然后執(zhí)行同步時(shí)間線，否知直接看狀態(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! # 重新啟動(dòng)數(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)啟動(dòng)了，但是還是down，需要手動(dòng)將master節(jié)點(diǎn)添加進(jìn)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)在兩個(gè)節(jié)點(diǎn)都是up了。
主從兩節(jié)點(diǎn)pgpool健康檢查腳本(pgpool_check.sh)
說明：此腳本是基于PGpool只安裝到master和slave兩個(gè)主機(jī)上的情況下使用，在master主機(jī)有了pgpool進(jìn)程后，可在slave主機(jī)執(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
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
標(biāo)簽：晉城 蚌埠 來賓 珠海 錦州 烏海 株洲 衡陽
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《PostgreSQL+Pgpool實(shí)現(xiàn)HA主備切換的操作》，本文關(guān)鍵詞 PostgreSQL+Pgpool,實(shí)現(xiàn),主備,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。