Postgresql高可用之repmgr+keepalived+流復(fù)制架構(gòu)搭建

IT那活兒發(fā)布于2023-01-11 13:20 / 1739人閱讀

點(diǎn)擊上方藍(lán)字關(guān)注我們

repmgr是一個(gè)對(duì)postgresql流復(fù)制進(jìn)行管理以及自動(dòng)故障轉(zhuǎn)移的開源軟件，大大的簡(jiǎn)化了PG流復(fù)制架構(gòu)的管理。但是repmgr不具備提供VIP功能，只能借助keepalived實(shí)現(xiàn)VIP，并確保VIP運(yùn)行在primary節(jié)點(diǎn)上。其不具備連接池功能，所以只是一個(gè)輕量級(jí)開源軟件。下面詳細(xì)介紹該架構(gòu)的詳細(xì)搭建過(guò)程。

一、repmgr軟件的安裝

先安裝repmgr依賴的軟件包，官方推薦使用yum安裝如下組件和rpm包

yumcheck-update

yumgroupinstall "Development Tools"

yuminstall yum-utils openjade docbook-dtds docbook-style-dsssldocbook-style-xsl

yuminstall flex libselinux-devel libxml2-devel libxslt-developenssl-devel pam-devel readline-devel

下載repmgr并安裝，當(dāng)前最新的版本為5.2.1.

./configure&& make install

注意，確保pg_config在安裝用戶的環(huán)境變量PATH中，repmgr會(huì)默認(rèn)安裝到postgres的軟件目錄下。

二、PG數(shù)據(jù)庫(kù)及repmgr配置

首先完成流復(fù)制相關(guān)的參數(shù)修改和用戶創(chuàng)建，并修改pg_hba.conf文件

createuser repluser with usperuser password ****;

createdatabase repmgr with owner=repluser;

--修改主備節(jié)點(diǎn)的pg_hba.conf文件

local replication repluser trust

host replication repluser 127.0.0.1/32 trust

host replication repluser 10.26.60.0/24 scram-sha-256

local repmgr repluser trust

host repmgr repluser 127.0.0.1/32 trust

host repmgr repluser 10.26.60.0/24 scram-sha-256

--修改.pgpass

spcl-pg250:5432:repmgr:repluser:Repl#2021

spcl-pg249:5432:repmgr:repluser:Repl#2021

--修改/etc/repmgr.conf

node_id=1

node_name=host01

conninfo=host=host01user=repluser dbname=repmgr connect_timeout=2

data_directory=/pgdata’

--注冊(cè)主節(jié)點(diǎn)

repmgr-f /etc/repmgr.conf primary register

--驗(yàn)證集群狀態(tài)

repmgr-f /etc/repmgr.conf cluster show

--創(chuàng)建從庫(kù)的/etc/repmgr.conf文件

node_id=2

node_name=host02

conninfo=host=host02user=repluser dbname=repmgr connect_timeout=2

data_directory=/pgdata

--進(jìn)行從庫(kù)搭建前的檢測(cè)

[postgres@host02pgdata]$ repmgr -h host01 -U repluser -d repmgr -f/pgdata/repmgr.conf standby clone --dry-run

NOTICE:destination directory "/pgdata" provided

INFO:connecting to source node

DETAIL:connection string is: host=host01 user=repluser dbname=repmgr

DETAIL:current installation size is 31 MB

INFO:"repmgr" extension is installed in database "repmgr"

INFO:replication slot usage not requested; no replication slot will beset up for this standby

INFO:parameter "max_wal_senders" set to 32

NOTICE:checking for available walsenders on the source node (2 required)

INFO:sufficient walsenders available on the source node

DETAIL:2 required, 31 available

NOTICE:checking replication connections can be made to the source server (2required)

INFO:required number of replication connections could be made to thesource server

DETAIL:2 replication connections required

WARNING:data checksums are not enabled and "wal_log_hints" is "off"

DETAIL:pg_rewind requires "wal_log_hints" to be enabled

NOTICE:standby will attach to upstream node 1

HINT:consider using the -c/--fast-checkpoint option

INFO:all prerequisites for "standby clone" are met

--執(zhí)行以下命令完成從庫(kù)搭建

repmgr-h host01 -U repluser -d repmgr -f /etc/repmgr.conf standby clone

注意：如果數(shù)據(jù)量很大，則在搭建從庫(kù)時(shí)需要加上--fast-checkpoint參數(shù)，否則調(diào)用pg_basebackup備份的過(guò)程會(huì)非常慢。

---如果不是使用repmgr完成的流復(fù)制搭建，則配置連接串時(shí)，需要指定application_name

repmgr-f /etc/repmgr.conf standby register

repmgr-f /etc/repmgr.conf cluster show

至此，repmgr就搭建及配置完成。

三、利用repmgr進(jìn)行主從切換

--直接進(jìn)行主從切換

repmgrstandby switchover -f /etc/repmgr.conf --siblings-follow --dry-run --切換檢查

repmgr-f /etc/repmgr.conf standby switchover

--主庫(kù)down掉后，從庫(kù)手動(dòng)切換成主庫(kù)

repmgr-f /etc/repmgr.conf standby promote ---備節(jié)點(diǎn)

repmgr-f /etc/repmgr.conf standby follow --如果有多個(gè)備節(jié)點(diǎn)，在其他備節(jié)點(diǎn)執(zhí)行

四、配置自動(dòng)failover

--修改/etc/repmgr.conf

shared_preload_libraries = repmgr

monitor_interval_secs=2

connection_check_type=connection

reconnect_attempts==6

reconnect_interval=4

failover=automatic

promote_command=/usr/local/postgresql/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file

follow_command=/usr/local/postgresql/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n

log_level=INFO

log_facility=STDERR

log_file=/data/pgdata/log/repmgr.log

log_status_interval=300

repmgrd_service_start_command = repmgrd --daemonize=true

repmgrd_service_stop_command = kill `cat /data/pgdata/repmgrd.pid`

repmgrd_pid_file= /pgdata/repmgrd.pid

--啟動(dòng)守護(hù)進(jìn)程

repmgrdaemon start

--自動(dòng)failover之后需手工將原主節(jié)點(diǎn)恢復(fù)成備節(jié)點(diǎn)，然后執(zhí)行repmgr-f /etc/repmgr.conf standby register --force ，否則下次不會(huì)自動(dòng)failover

五、keepalived配置

keepalived在此架構(gòu)中，只作為提供VIP的工具，不進(jìn)行故障轉(zhuǎn)移操作，所以配置相對(duì)簡(jiǎn)單，其配置如下：

/etc/keepalived/keepalived.conf

global_defs {

router_id pg_ha

# enable_script_security

}

vrrp_script checkpg {

script "/etc/keepalived/scripts/checkpg.sh"

interval 15

fall 3

rise 1

}

vrrp_instance VI_pgusdp {

state BACKUP

interface ens160

virtual_router_id 152

priority 80

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass 234235

}

track_script {

checkpg

}

notify_master "/etc/keepalived/scripts/master.sh"

notify_backup "/etc/keepalived/scripts/slave.sh"

virtual_ipaddress {

10.**.**.**/24

}

/etc/keepalived/scripts/checkpg.sh

#!/bin/bash

export PGDATABASE=postgres

export PGPORT=5432

export PGUSER=postgres

export PGBIN=/usr/local/postgresql/bin

export PGDATA=/data/pgdata

LOGFILE=/etc/keepalived/log/keepalived.log

nc -w 3 localhost 5432

a=`echo $?`

if [ $a -eq 1 ] ;then

exit 1

else

SQL1=SELECT pg_is_in_recovery from pg_is_in_recovery();

db_role=`echo $SQL1 | ${PGBIN}/psql -d $PGDATABASE -U $PGUSER -h $PGDATA -At -w`

if [ $db_role == t ];then

exit 1

/etc/keepalived/scripts/master.sh

LOGFILE=/etc/keepalived/log/keepalived.log

export PGDATABASE=postgres

export PGPORT=5432

export PGUSER=postgres

export PGBIN=/usr/local/postgresql/bin

export PGDATA=/data/pgdata

LOGFILE=/etc/keepalived/log/keepalived.log

SQL1=SELECT pg_is_in_recovery from pg_is_in_recovery();

db_role=`echo $SQL1 | ${PGBIN}/psql -d $PGDATABASE -U $PGUSER -h $PGDATA -At -w`

if [ $db_role == t ];then

echo -e `date +"%F %T"` "the current database is standby DB! " >> $LOGFILE

exit 1

else

echo -e `date +"%F %T"` "the current database is master DB!" >> $LOGFILE

/etc/keepalived/scripts/slave.sh

LOGFILE=/etc/keepalived/log/keepalived.log

export PGDATABASE=postgres

export PGPORT=5432

export PGUSER=postgres

export PGBIN=/usr/local/postgresql/bin

export PGDATA=/data/pgdata

LOGFILE=/etc/keepalived/log/keepalived.log

SQL1=SELECT pg_is_in_recovery from pg_is_in_recovery();

db_role=`echo $SQL1 | ${PGBIN}/psql -d $PGDATABASE -U $PGUSER -h $PGDATA -At -w`

if [ $db_role == t ];then

echo -e `date +"%F %T"` "the current database is standby DB! " >> $LOGFILE

else

echo -e `date +"%F %T"` "the current database is master DB!" >> $LOGFILE

END

GPU云服務(wù)器云服務(wù)器高可用系統(tǒng)架構(gòu)設(shè)計(jì) mysql高可用架構(gòu) 分布式架構(gòu)-高可用 webrtc媒體服務(wù)器高可用架構(gòu)

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://m.hztianpu.com/yun/129981.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1459·2023-01-11 13:20
RAC雙節(jié)點(diǎn)crash回復(fù)一例

閱讀 1814·2023-01-11 13:20
ORA-600處理一例

閱讀 1263·2023-01-11 13:20
雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

閱讀 2006·2023-01-11 13:20
RAC集群節(jié)點(diǎn)1重啟分析一例

閱讀 4226·2023-01-11 13:20
CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

閱讀 2882·2023-01-11 13:20
oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

閱讀 1489·2023-01-11 13:20
分布式緩存組件故障分析及監(jiān)控優(yōu)化

閱讀 3809·2023-01-11 13:20

成人无码视频,亚洲精品久久久久av无码,午夜精品久久久久久毛片,亚洲中文字幕日韩无码

資訊專欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

Postgresql高可用之repmgr+keepalived+流復(fù)制架構(gòu)搭建

相關(guān)文章

**新書推薦 |《PostgreSQL實(shí)戰(zhàn)》出版(提供樣章下載)**

PostgreSQL 中文資料匯總

人工智能幫助千萬(wàn)用戶完成「隱形征信」計(jì)算

基于騰訊云CVM自建高可用Redis實(shí)踐

發(fā)表評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

TA的文章

消息中間件故障分析一例

RAC雙節(jié)點(diǎn)crash回復(fù)一例

ORA-600處理一例

雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

RAC集群節(jié)點(diǎn)1重啟分析一例

CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

分布式緩存組件故障分析及監(jiān)控優(yōu)化

最新活動(dòng)

資訊專欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

Postgresql高可用之repmgr+keepalived+流復(fù)制架構(gòu)搭建

相關(guān)文章

發(fā)表評(píng)論

0條評(píng)論

男|高級(jí)講師

TA的文章

最新活動(dòng)

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！