說明：本文檔基于CentOS 7.9 郭毕、postgresql 11.9 溜歪、keepalived-1.3.5 搭建撰寫。
一、服務(wù)器規(guī)劃
| 服務(wù)器規(guī)劃 | IP及端口規(guī)劃 | VIP |
|---|---|---|
| PgMaster | 192.168.11.3 | 192.168.11.5 |
| PgSlave | 192.168.11.4 |
二比规、準(zhǔn)備相應(yīng)的安裝包
- postgresql11-11.9-1PGDG.rhel7.x86_64.rpm
- postgresql11-libs-11.9-1PGDG.rhel7.x86_64.rpm
- postgresql11-contrib-11.9-1PGDG.rhel7.x86_64.rpm
- postgresql11-server-11.9-1PGDG.rhel7.x86_64.rpm
- ipset-libs-7.1-1.el7.x86_64.rpm
- net-snmp-5.7.2-49.el7.x86_64.rpm
- net-snmp-libs-5.7.2-49.el7.x86_64.rpm
- net-snmp-agent-libs-5.7.2-49.el7.x86_64.rpm
- keepalived-1.3.5-19.el7.x86_64.rpm
- perl-Data-Dumper-2.145-3.el7.x86_64.rpm
三、配置防火墻拦英、關(guān)閉selinux
-
使用root賬號(hào)按以下命令操作
所有服務(wù)器均需配置防火墻
firewall-cmd --permanent --add-port=5432/tcp firewall-cmd --reload所有服務(wù)器均需重啟防火墻蜒什，檢查開發(fā)端口信息
systemctl restart firewalld.service firewall-cmd --state firewall-cmd --list-ports- 關(guān)閉SELinux
永久關(guān)閉，修改/etc/selinux/config ?件疤估，將SELINUX=enforcing改為SELINUX=disabled灾常，重啟機(jī)器即可
- 關(guān)閉SELinux
四、搭建主從環(huán)境
- 上傳rpm包到服務(wù)器铃拇，安裝postgresql包
創(chuàng)建數(shù)據(jù)存儲(chǔ)路徑
mkdir -p /home/data
創(chuàng)建上傳文件路徑
mkdir -p /home/software
創(chuàng)建數(shù)據(jù)文件備份路徑
mkdir -p /mnt/data/backups/
進(jìn)入上傳路徑
cd /home/software/
執(zhí)行下面命令進(jìn)行安裝
rpm -ivh postgresql11-libs-11.9-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql11-11.9-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql11-server-11.9-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql11-contrib-11.9-1PGDG.rhel7.x86_64.rpm
- 指定數(shù)據(jù)存儲(chǔ)路徑
pg數(shù)據(jù)存儲(chǔ)路徑默認(rèn)是/var/lib/pgsql/11/data/钞瀑，如需自定義存儲(chǔ)路徑，請(qǐng)按以下操作：
創(chuàng)建自定義數(shù)據(jù)存儲(chǔ)路徑慷荔。
mkdir -p /home/data/postgresdb/11
chown -R postgres:postgres /home/data/postgresdb
創(chuàng)建歸檔路徑
mkdir -p /home/postgres/arch/
chown -R postgres:postgres /home/postgres/arch/
創(chuàng)建postgresql-11.service.d目錄
mkdir -p /etc/systemd/system/postgresql-11.service.d
創(chuàng)建override.conf雕什，添加以下內(nèi)容
vi /etc/systemd/system/postgresql-11.service.d/override.conf
[Service]
Environment=PGDATA=/home/data/postgresdb/11
其中/home/data/postgresdb/11,表示數(shù)據(jù)存儲(chǔ)路徑，修改完成重啟載入显晶。
systemctl daemon-reload
- 初始化數(shù)據(jù)庫
/usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl start postgresql-11
- 初始化postgres數(shù)據(jù)庫用戶密碼（僅主庫操作）
su - postgres
psql -U postgres
\password
創(chuàng)建同步賬號(hào)
create role repl login replication encrypted password '123456';
修改完 \q 退出
exit
退出postgres賬號(hào)贷岸。
- 修改postgresql配置（僅主庫操作）
進(jìn)入自定義的數(shù)據(jù)存儲(chǔ)路徑
cd /home/data/postgresdb/11/
- 修改自定義的數(shù)據(jù)存儲(chǔ)路徑下的postgresql.conf文件。
vim postgresql.conf
查找并編輯以下配置項(xiàng)
port = 5432
max_connections = 2000
listen_addresses = '*'
wal_level = replica
max_wal_senders = 20
wal_keep_segments = 2048
archive_mode = on
archive_timeout=3600 #每小時(shí)歸檔一次
archive_command = 'test ! -f /home/postgres/arch/%f && cp %p /home/postgres/arch/%f'
- 修改pg_hba.conf文件磷雇。
vi pg_hba.conf
編輯配置如下
local all all trust
host all all 0.0.0.0/0 md5
host all all ::1/128 md5
local replication all trust
host replication all 0.0.0.0/0 md5
host replication all ::1/128 md5
- 編輯主庫同步配置文件偿警，recovery.done，如果沒有則創(chuàng)建倦春，找到以下配置項(xiàng)并修改
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.11.4 port=5432 user=repl password=123456'
其中host為另一臺(tái)服務(wù)器地址户敬，port為另一臺(tái)服務(wù)器端口落剪，user為同步賬號(hào)睁本，password為同步賬號(hào)密碼。
授權(quán)
chown -R postgres:postgres /home/data/postgresdb/11/recovery.done
chmod 700 /home/data/postgresdb/11/
然后重啟主庫
systemctl restart postgresql-11
- 搭建從庫環(huán)境
1)首先按上面的第五步的1忠怖、2步驟安裝好從庫
2)切換到postgres
su - postgres
3)執(zhí)行同步操作
先刪除/home/data/postgresdb/11/下的內(nèi)容
rm -rf /home/data/postgresdb/11/*
執(zhí)行同步操作
pg_basebackup -h 192.168.11.3 -p 5432 -U repl -F p -P -D /home/data/postgresdb/11/
其中：ip地址192.168.11.3為主庫地址呢堰，/home/data/postgresdb/11/為從庫的數(shù)據(jù)存儲(chǔ)路徑
4)編輯從庫同步配置文件，將recovery.done改為recovery.conf凡泣，如果沒有則創(chuàng)建枉疼，找到以下配置項(xiàng)并修改
cd /home/data/postgresdb/11/
mv recovery.done recovery.conf
vi recovery.conf
修改host配置
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.11.3 port=5432 user=repl password=123456'
其中host為另一臺(tái)服務(wù)器地址皮假，port為另一臺(tái)服務(wù)器端口，user為同步賬號(hào)骂维，password為同步賬號(hào)密碼惹资。
授權(quán)
chown -R postgres:postgres /home/data/postgresdb/11/recovery.conf
chmod 700 /home/data/postgresdb/11/
5)使用root賬號(hào)，啟動(dòng)從庫
systemctl start postgresql-11
6)驗(yàn)證主從關(guān)系
切換到postgres賬號(hào)
su - postgres
登錄進(jìn)入psql客戶端
psql -U postgres
執(zhí)行下面sql航闺，查詢主從關(guān)系褪测，f表示主庫，t表示從庫潦刃。
select pg_is_in_recovery();
- 配置環(huán)境變量（主從都執(zhí)行）
編輯環(huán)境變量
vim /etc/profile
添加如下內(nèi)容：
export PATH=$PATH:/usr/pgsql-11/bin
執(zhí)行以下命令
source /etc/profile
五侮措、安裝keepalived
- 上傳rpm包到服務(wù)器，安裝相關(guān)包
進(jìn)入到software
cd /home/software/
執(zhí)行下面的命令安裝
rpm -ivh perl-Data-Dumper-2.145-3.el7.x86_64.rpm
rpm -ivh --force net-snmp-libs-5.7.2-49.el7.x86_64.rpm
rpm -ivh --force net-snmp-agent-libs-5.7.2-49.el7.x86_64.rpm
rpm -ivh --force ipset-libs-7.1-1.el7.x86_64.rpm
rpm -ivh libnl3-3.2.28-4.el7.x86_64.rpm --force
rpm -ivh keepalived-1.3.5-19.el7.x86_64.rpm
- keepalived配置
創(chuàng)建腳本存放路徑
mkdir -p /etc/keepalived/scripts/
創(chuàng)建腳本日志路徑
mkdir -p /etc/keepalived/log/
編輯配置文件,具體內(nèi)容參考入下
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id 192.168.11.3
script_user root
enable_script_security
vrrp_skip_check_adv_addr
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_script check_pg_alived {
script "/etc/keepalived/scripts/check_pg.sh"
interval 2
weight -5
fall 2
rise 1
}
vrrp_instance VI_1 {
state BACKUP
interface ens160
virtual_router_id 66
priority 99
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_pg_alived
}
virtual_ipaddress {
192.168.11.5
}
notify_master "/etc/keepalived/scripts/failover.sh"
notify_fault "/etc/keepalived/scripts/fault.sh"
}
注意：
1.備庫的priority設(shè)置要比主庫的小乖杠，同時(shí)大于主庫priority + weight 的值 分扎。
比如這里主庫的priority 為99，備庫可以設(shè)置為98胧洒、97畏吓、96、95這幾個(gè)值卫漫。
2.備庫不需要配置nopreempt庵佣。
3.編寫相關(guān)腳本
1)編寫檢查pg的腳本
vim /etc/keepalived/scripts/check_pg.sh
#!/bin/bash
export PGDATABASE=postgres
export PGPORT=5432
export PGUSER=postgres
export PGHOME=/usr/bin
export PATH=$PATH:$PGHOME
LOGFILE=/etc/keepalived/log/pg_status.log
SQL2='update sr_delay set sr_date = now() where id =1;'
SQL1='SELECT pg_is_in_recovery from pg_is_in_recovery();'
SQL3='SELECT 1;'
#db_role=`echo $SQL1 | $PGHOME/psql -d $PGDATABASE -U $PGUSER -At -w`
db_role=`echo $SQL1 | psql -p $PGPORT -d $PGDATABASE -U $PGUSER -At -w`
if [ $db_role == 't' ];then
echo -e `date +"%F %T"` "Attention1:the current database is standby DB!" >> $LOGFILE
exit 0
fi
#備庫不檢查存活,主庫更新狀態(tài)
echo $SQL3 | psql -p $PGPORT -d $PGDATABASE -U $PGUSER -At -w
if [ $? -eq 0 ] ;then
echo $SQL2 | psql -p $PGPORT -d $PGDATABASE -U $PGUSER -At -w
echo -e `date +"%F %T"` "Success: update the master sr_delay successed!" >> $LOGFILE
exit 0
else
echo -e `date +"%F %T"` "Error:Is the server is running?" >> $LOGFILE
exit 1
fi
2)編寫備轉(zhuǎn)為主時(shí)觸發(fā)的腳本
vim /etc/keepalived/scripts/failover.sh
#!/bin/bash
export PGPORT=5432
export PGUSER=postgres
export PG_OS_USER=postgres
export PGDBNAME=postgres
export LANG=zh_CN.UTF-8
export PGPATH=/usr/bin
export PATH=$PATH:$PGPATH
LOGFILE=/etc/keepalived/log/failover.log
# 主備數(shù)據(jù)庫同步時(shí)延，單位為秒
sr_allowed_delay_time=10000
PGDBPATH=/usr/local/postgresql/data
SQL1='select pg_is_in_recovery from pg_is_in_recovery();'
SQL2="select sr_date as delay_time from sr_delay where now()-sr_date < interval \' $sr_allowed_delay_time \';"
#SQL2="select sr_date as delay_time from sr_delay where now()-sr_date < interval '100';"
db_role=`echo $SQL1 | psql -At -p $PGPORT -U $PGUSER -d $PGDBNAME -w`
db_sr_delaytime=`echo $SQL2 | psql -p $PGPORT -d $PGDBNAME -U $PGUSER -At -w`
SWITCH_COMMAND='pg_ctl promote -D '$PGDBPATH
# 如果為備庫汛兜，且延遲大于指定時(shí)間則切換為主庫
if [ $db_role == f ];then
echo -e `date +"%F %T"` "Attention:The current postgreSQL DB is master database,cannot switched!" >> $LOGFILE
exit 0
fi
if [ $db_sr_delaytime -gt 0 ];then
echo -e `date +"%F %T"` "Attention:The current master database is health,the standby DB cannot switched!" >> $LOGFILE
exit 0
fi
if [ !$db_sr_delaytime ];then
echo -e `date +"%F %T"` "Attention:The current database is statndby,ready to switch master database!" >> $LOGFILE
su - $PG_OS_USER -c "$SWITCH_COMMAND"
elif [ $? eq 0 ];then
echo -e `date +"%F %T"` "success:The current standby database successed to switched the primary PG database !" >> $LOGFILE
exit 0
else
echo -e `date +"%F %T"` "Error: the standby database failed to switch the primary PG database !,pelease checked it!" >> $LOGFILE
exit 1
fi
3)編寫狀態(tài)轉(zhuǎn)換失敗時(shí)觸發(fā)的腳本
vim /etc/keepalived/scripts/fault.sh
#!/bin/bash
LOGGFILE=/etc/keepalived/log/pg_db_fault.log
PGPORT=5432
echo -e `date +"%F %T"` "Error:Because of the priamry DB happend some unknown problem,So turn off the PostgreSQL Database!" >> $LOGFILE
PGPID="`netstat -anp|grep $PGPORT |awk '{printf $7}'|cut -d/ -f1`"
kill -9 $PGPID
echo -e `date +"%F %T"` "Error:Because of the priamry DB happend some unknown problem,So turn off the PostgreSQL Database!" >> $LOGFILE
systemctl stop keepalived
4)授予可執(zhí)行權(quán)限
cd /etc/keepalived/scripts
chmod 755 ./*.sh
5)創(chuàng)建用戶檢查檢查心跳的數(shù)據(jù)庫腳本
在主庫上執(zhí)行巴粪，plsql進(jìn)入會(huì)話執(zhí)行以下兩條sql語句。
create table sr_delay(id serial ,sr_date timestamp default now());
insert into sr_delay values(1,now());
6)主備服務(wù)器分別修改配置
編輯配置
vim /etc/sysconfig/keepalived
把KEEPALIVED_OPTIONS="-D" 修改為KEEPALIVED_OPTIONS="-D -d -S 0"
vim /etc/rsyslog.conf
加入如下配置：
#keepalived -S 0
local0.* /var/log/keepalived.log
7)主備分別配置防火墻策略
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --reload
systemctl restart firewalld.service
8)啟動(dòng)keepalived服務(wù)
使用下面的命令依次啟動(dòng)主庫和從庫的keepalived服務(wù)
systemctl start keepalived.service
并用下面的命令檢查是否綁定了指定的vip粥谬。
ip a
檢查/etc/keepalived/log/pg_status.log肛根，該日志會(huì)定時(shí)向數(shù)據(jù)庫更新時(shí)間戳，通過內(nèi)容可知是否正常漏策。
六派哲、測(cè)試主備切換
1)關(guān)閉主庫服務(wù)，切換為postgres賬號(hào)掺喻，執(zhí)行如下命令：
systemctl stop postgresql-11
2)從庫上執(zhí)行ip a 命令芭届，顯示vip已經(jīng)飄過來。
3)檢查從庫是否變?yōu)橹鲙臁?br>
登錄進(jìn)入psql客戶端,執(zhí)行下面命令
psql
執(zhí)行下面sql感耙，查詢主從關(guān)系褂乍，f表示主庫，t表示從庫即硼。
select pg_is_in_recovery();
六逃片、恢復(fù)主庫（原主庫變?yōu)閺膸欤?/h2>
1)檢查recovery.conf配置文件
將data下recovery.done改為recovery.conf
2)啟動(dòng)從庫服務(wù)（原主庫）
systemctl restart postgresql-11
3)檢查從庫（原主庫）的keepalived狀態(tài)
ps -ef | grep keepalived
如果服務(wù)不存在，使用root賬號(hào)用systemctl start keepalived命令啟動(dòng)keepalived只酥。
七褥实、備份
方案采用每周全量備份加開啟日志歸檔操作呀狼，保證當(dāng)災(zāi)難發(fā)生時(shí)，比如主從數(shù)據(jù)庫都損壞時(shí)损离，保證根據(jù)全量數(shù)據(jù)加歸檔的日志文件能夠最大程度還原數(shù)據(jù)哥艇。
1)編寫全量備份的腳本
mkdir /etc/keepalived/scripts/tools/
vim /etc/keepalived/scripts/tools/backup_pg_all.sh
#!/bin/bash
export PGPORT=5432
export PGUSER=postgres
export PGHOME=/usr/bin
export PATH=$PATH:$PGHOME
export PASS='123456'
LOGFILE=/etc/keepalived/log/backup_pg.log
BACKPATH=/mnt/data/backups/
backtime=`date +"%F %T"`
temppath=`date +"%Y%m%d"`
echo ”備份時(shí)間為${backtime},備份數(shù)據(jù)庫開始” >> ${LOGFILE}
source=`pg_basebackup -Ft -Pv -Xf -z -Z5 -p ${PGPORT} -U ${PGUSER} -w -D ${BACKPATH}${temppath}` 2>> ${LOGFILE}
if [ "$?" == 0 ];then
echo ”備份數(shù)據(jù)庫成功！僻澎！” >> ${LOGFILE}
else
echo ”備份數(shù)據(jù)庫失斔隆！怎棱！” >> ${LOGFILE}
fi
如果端口號(hào)哩俭、密碼等信息有改動(dòng)，需要根據(jù)實(shí)際情況調(diào)整拳恋。
授予可執(zhí)行權(quán)限
chmod 755 /etc/keepalived/scripts/tools/backup_pg_all.sh
2)每周一凌晨1點(diǎn)執(zhí)行全量備份
執(zhí)行crontab -e凡资，進(jìn)行定時(shí)任務(wù)編輯，增加如下配置：
1 1 * * 1 /etc/keepalived/scripts/tools/backup_pg_all.sh
