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

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

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

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

一、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會默認(rèn)安裝到postgres的軟件目錄下。

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

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

createuser repluser with usperuser password ****;

createdatabase repmgr with owner=repluser;

--修改主備節(jié)點的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’

--注冊主節(jié)點

repmgr-f /etc/repmgr.conf primary register

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

repmgr-f /etc/repmgr.conf cluster show

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

node_id=2

node_name=host02

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

data_directory=/pgdata

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

[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í)行以下命令完成從庫搭建

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

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

---如果不是使用repmgr完成的流復(fù)制搭建，則配置連接串時，需要指定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

--主庫down掉后，從庫手動切換成主庫

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

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

四、配置自動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

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

repmgrdaemon start

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

五、keepalived配置

keepalived在此架構(gòu)中，只作為提供VIP的工具，不進(jìn)行故障轉(zhuǎ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

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

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

轉(zhuǎn)載請注明本文地址：http://specialneedsforspecialkids.com/yun/129981.html

發(fā)表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

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

TA的文章

消息中間件故障分析一例

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

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

閱讀 1132·2023-01-11 13:20
雙節(jié)點RAC實例2 HANG 故障分析一例

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

閱讀 4100·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2704·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

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

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

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

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

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

相關(guān)文章

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

PostgreSQL 中文資料匯總

人工智能幫助千萬用戶完成「隱形征信」計算

基于騰訊云CVM自建高可用Redis實踐

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

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

ORA-600處理一例

雙節(jié)點RAC實例2 HANG 故障分析一例

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

CRS啟動報錯CRS-1656處理分享

oracle 12CR2打補丁報錯處理一例

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

最新活動