backend_application_name0 = ‘server1’
backend_hostname2 = ‘10.10.10.73’
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = ‘/data/pg_data’
backend_flag2 = ‘ALLOW_TO_FAILOVER’
backend_application_name0 = ‘server2’
流复制相关配置
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = ‘stream’
sr_check_period = 5
sr_check_user = ‘repuser’
sr_check_password = ‘Repuser@2024_RP’
sr_check_database = ‘postgres’
数据库故障转移(故障后处理)
health_check_period = 10
health_check_timeout = 20
health_check_user = ‘postgres’
health_check_password = ‘Postgres@2024_PG’
health_check_database = ‘postgres’
failover_command = ‘/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R’
follow_master_command = ‘/etc/pgpool-II/follow_master.sh %d %h %p %D %m %H %M %P %r %R’
recovery_user = ‘postgres’
recovery_password = ‘Postgres@2024_PG’
recovery_1st_stage_command = ‘recovery_1st_stage’
watchdog(看门狗)配置(用于检测pgpool-ii 节点状态, 为后续pgpool故障处理提供依据)
use_watchdog = on
wd_hostname = ‘10.10.10.71’
wd_port = 9000
虚拟IP指定
delegate_IP = ‘10.10.10.101’
if_cmd_path = ‘/sbin’
if_up_cmd = ‘/usr/bin/sudo /sbin/ip addr add KaTeX parse error: Expected group after '_' at position 4: _IP_̲/24 dev ens192 label ens192:0’
if_down_cmd = ‘/usr/bin/sudo /sbin/ip addr del KaTeX parse error: Expected group after '_' at position 4: _IP_̲/24 dev ens192’
arping_cmd = ‘/usr/bin/sudo /usr/sbin/arping -U KaTeX parse error: Expected group after '_' at position 4: _IP_̲ -w 1 -I ens192’
watchdog 健康检查
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = ‘10.10.10.72’
heartbeat_destination_port0 = 9694
heartbeat_device0 = ‘ens192’
heartbeat_destination0 = ‘10.10.10.73’
heartbeat_destination_port0 = 9694
heartbeat_device0 = ‘ens192’
其他pgpgool节点链接信息(多台请增加配置)
other_pgpool_hostname0 = ‘10.10.10.72’
other_pgpool_port0 = 9999
other_wd_port0 = 9000
watchdog 发生故障后, 处理的相关配置(宕机, pgpool进程终止)# 当某个节点故障后
other_pgpool_hostname1 = ‘10.10.10.73’
other_pgpool_port1 = 9999
other_wd_port1 = 9000
failover_when_quorum_exists = on
failover_require_consensus = on
allow_multiple_failover_requests_from_node = on
enable_consensus_with_half_votes = on
#### 修改failover.sh
PGHOME=/usr/pgsql-12
REPL_SLOT_NAME=${FAILED_NODE_HOST//[-.]/_}
POSTGRESQL_STARTUP_USER=postgres
SSH_KEY_FILE=id_rsa_pgpool
ssh -T ${SSH_OPTIONS} P O S T G R E S Q L S T A R T U P U S E R @ {POSTGRESQL_STARTUP_USER}@ POSTGRESQLSTARTUPUSER@{NEW_MASTER_NODE_HOST} ${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c “select pg_promote(true,30)”
#### 修改follow\_master.sh
#!/bin/bash
This script is run after failover_command to synchronize the Standby with the new Primary.
First try pg_rewind. If pg_rewind failed, use pg_basebackup.
set -o xtrace
exec > >(logger -i -p local1.info) 2>&1
Special values:
1) %d = node id
2) %h = hostname
3) %p = port number
4) %D = database cluster path
5) %m = new primary node id
6) %H = new primary node hostname
7) %M = old master node id
8) %P = old primary node id
9) %r = new primary port number
10) %R = new primary database cluster path
11) %N = old primary node hostname
12) %S = old primary node port number
13) %% = ‘%’ character
NODE_ID=“$1”
NODE_HOST=“$2”
NODE_PORT=“$3”
NODE_PGDATA=“$4”
NEW_MASTER_NODE_ID=“$5”
NEW_MASTER_NODE_HOST=“$6”
OLD_MASTER_NODE_ID=“$7”
OLD_PRIMARY_NODE_ID=“$8”
NEW_MASTER_NODE_PORT=“
9
"
N
E
W
M
A
S
T
E
R
N
O
D
E
P
G
D
A
T
A
=
"
9" NEW_MASTER_NODE_PGDATA="
9"NEWMASTERNODEPGDATA="{10}”
PGHOME=/usr/pgsql-12
ARCHIVEDIR=/data/pg_arch
REPLUSER=repuser
REPLUSER_PD=Repuser@2024_RP
PCP_USER=postgres
PG_USER=postgres
PG_PD=Postgres@2024_PG
PGPOOL_PATH=/usr/bin
PCP_PORT=9898
REPL_SLOT_NAME=KaTeX parse error: Expected group after '_' at position 18: …ODE_HOST//[-.]/_̲} POSTGRESQL_ST…{SSH_KEY_FILE}"
logger -i -p local1.info follow_master.sh: start: Standby node ${NODE_ID}
Check the connection status of Standby
${PGHOME}/bin/pg_isready -h ${NODE_HOST} -p ${NODE_PORT} > /dev/null 2>&1
if [
?
−
n
e
0
]
;
t
h
e
n
l
o
g
g
e
r
−
i
−
p
l
o
c
a
l
1.
i
n
f
o
f
o
l
l
o
w
m
a
s
t
e
r
.
s
h
:
n
o
d
e
i
d
=
? -ne 0 ]; then logger -i -p local1.info follow_master.sh: node_id=
?−ne0];thenlogger−i−plocal1.infofollowmaster.sh:nodeid={NODE_ID} is not running. skipping follow master command
exit 0
fi
Test passwordless SSH
ssh -T ${SSH_OPTIONS} P O S T G R E S Q L S T A R T U P U S E R @ {POSTGRESQL_STARTUP_USER}@ POSTGRESQLSTARTUPUSER@{NEW_MASTER_NODE_HOST} ls /tmp > /dev/null
if [
?
−
n
e
0
]
;
t
h
e
n
l
o
g
g
e
r
−
i
−
p
l
o
c
a
l
1.
i
n
f
o
f
o
l
l
o
w
m
a
s
t
e
r
.
s
h
:
p
a
s
s
w
o
r
d
l
e
s
s
S
S
H
t
o
p
o
s
t
g
r
e
s
@
? -ne 0 ]; then logger -i -p local1.info follow_master.sh: passwordless SSH to postgres@
?−ne0];thenlogger−i−plocal1.infofollowmaster.sh:passwordlessSSHtopostgres@{NEW_MASTER_NODE_HOST} failed. Please setup passwordless SSH.
exit 1
fi
Get PostgreSQL major version
PGVERSION=${PGHOME}/bin/initdb -V | awk '{print $3}' | sed 's/\..*//' | sed 's/\([0-9]*\)[a-zA-Z].*/\1/'
if [
P
G
V
E
R
S
I
O
N
−
g
e
12
]
;
t
h
e
n
R
E
C
O
V
E
R
Y
C
O
N
F
=
PGVERSION -ge 12 ]; then RECOVERYCONF=
PGVERSION−ge12];thenRECOVERYCONF={NODE_PGDATA}/myrecovery.conf
else
RECOVERYCONF=${NODE_PGDATA}/recovery.conf
fi
Synchronize Standby with the new Primary.
logger -i -p local1.info follow_master.sh: pg_rewind for node $NODE_ID
Create replication slot “${REPL_SLOT_NAME}”
${PGHOME}/bin/psql -h ${NEW_MASTER_NODE_HOST} -p N E W M A S T E R N O D E P O R T − c " S E L E C T p g c r e a t e p h y s i c a l r e p l i c a t i o n s l o t ( ′ {NEW_MASTER_NODE_PORT} \ -c "SELECT pg_create_physical_replication_slot(' NEWMASTERNODEPORT −c"SELECTpgcreatephysicalreplicationslot(′{REPL_SLOT_NAME}');" >/dev/null 2>&1
if [ KaTeX parse error: Expected group as argument to '\"' at end of input: …ication slot \"{REPL_SLOT_NAME}" failed. You may need to create replication slot manually.
fi
ssh -T ${SSH_OPTIONS} P O S T G R E S Q L S T A R T U P U S E R @ {POSTGRESQL_STARTUP_USER}@ POSTGRESQLSTARTUPUSER@{NODE_HOST} "
set -o errexit
${PGHOME}/bin/pg_ctl -w -m f -D ${NODE_PGDATA} stop
${PGHOME}/bin/pg_rewind -D ${NODE_PGDATA} --source-server=\"user=${PG_USER} password=${PG_PD} host=${NEW_MASTER_NODE_HOST} port=${NEW_MASTER_NODE_PORT}\"
rm -rf ${NODE_PGDATA}/pg_replslot/*
sed -i '/primary_conninfo/d' ${NODE_PGDATA}/postgresql.auto.conf
cat >> ${NODE_PGDATA}/postgresql.auto.conf << EOT
primary_conninfo = ‘user=
R
E
P
L
U
S
E
R
p
a
s
s
w
o
r
d
=
{REPLUSER} password=
REPLUSERpassword={REPLUSER_PD} host=
N
E
W
M
A
S
T
E
R
N
O
D
E
H
O
S
T
p
o
r
t
=
{NEW_MASTER_NODE_HOST} port=
NEWMASTERNODEHOSTport={NEW_MASTER_NODE_PORT} sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any’
EOT
if [ ${PGVERSION} -ge 12 ]; then
touch ${NODE_PGDATA}/standby.signal
echo \"standby_mode = 'on'\" > ${NODE_PGDATA}/standby.signal
else
echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}
fi
#${PGHOME}/bin/pg_ctl -l /dev/null -w -D ${NODE_PGDATA} start
sudo systemctl restart postgresql-12
"
If pg_rewind failed, try pg_basebackup
if [ $? -ne 0 ]; then
logger -i -p local1.error follow_master.sh: end: pg_rewind failed. Try pg_basebackup.
fi
If start Standby successfully, attach this node
if [ $? -eq 0 ]; then
# Run pcp_attact_node to attach Standby node to Pgpool-II.
${PGPOOL_PATH}/pcp_attach_node -w -h localhost -U $PCP_USER -p ${PCP_PORT} -n ${NODE_ID}
if [ $? -ne 0 ]; then
logger -i -p local1.error follow_master.sh: end: pcp_attach_node failed
exit 1
fi
else
# If start Standby failed, drop replication slot "${REPL_SLOT_NAME}"
${PGHOME}/bin/psql -h ${NEW_MASTER_NODE_HOST} -p ${NEW_MASTER_NODE_PORT} \
-c "SELECT pg_drop_replication_slot('${REPL_SLOT_NAME}');" >/dev/null 2>&1
if [ $? -ne 0 ]; then
logger -i -p local1.error follow_master.sh: drop replication slot \"${REPL_SLOT_NAME}\" failed. You may need to drop replication slot manually.
fi
logger -i -p local1.error follow_master.sh: end: follow master command failed
exit 1
fi
logger -i -p local1.info follow_master.sh: end: follow master command complete
exit 0
#### 修改recovery\_1st\_stage
#!/bin/bash
This script is executed by “recovery_1st_stage” to recovery a Standby node.
set -o xtrace
exec > >(logger -i -p local1.info) 2>&1
PRIMARY_NODE_PGDATA=“$1”
DEST_NODE_HOST=“$2”
DEST_NODE_PGDATA=“$3”
PRIMARY_NODE_PORT=“$4”
DEST_NODE_ID=“$5”
DEST_NODE_PORT=“$6”
PRIMARY_NODE_HOST=
(
h
o
s
t
n
a
m
e
)
P
G
H
O
M
E
=
/
u
s
r
/
p
g
s
q
l
−
12
A
R
C
H
I
V
E
D
I
R
=
/
d
a
t
a
/
p
g
a
r
c
h
R
E
P
L
U
S
E
R
=
r
e
p
u
s
e
r
R
E
P
L
U
S
E
R
P
D
=
R
e
p
u
s
e
r
@
202
4
R
P
R
E
P
L
S
L
O
T
N
A
M
E
=
(hostname) PGHOME=/usr/pgsql-12 ARCHIVEDIR=/data/pg_arch REPLUSER=repuser REPLUSER_PD=Repuser@2024_RP REPL_SLOT_NAME=
(hostname)PGHOME=/usr/pgsql−12ARCHIVEDIR=/data/pgarchREPLUSER=repuserREPLUSERPD=Repuser@2024RPREPLSLOTNAME={DEST_NODE_HOST//[-.]/_}
POSTGRESQL_STARTUP_USER=postgres
SSH_KEY_FILE=id_rsa_pgpool
SSH_OPTIONS=“-o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i ~/.ssh/${SSH_KEY_FILE}”
logger -i -p local1.info recovery_1st_stage: start: pg_basebackup for Standby node $DEST_NODE_ID
Test passwordless SSH
ssh -T ${SSH_OPTIONS} P O S T G R E S Q L S T A R T U P U S E R @ {POSTGRESQL_STARTUP_USER}@ POSTGRESQLSTARTUPUSER@{DEST_NODE_HOST} ls /tmp > /dev/null
if [
?
−
n
e
0
]
;
t
h
e
n
l
o
g
g
e
r
−
i
−
p
l
o
c
a
l
1.
i
n
f
o
r
e
c
o
v
e
r
y
1
s
t
s
t
a
g
e
:
p
a
s
s
w
o
r
d
l
e
s
s
S
S
H
t
o
p
o
s
t
g
r
e
s
@
? -ne 0 ]; then logger -i -p local1.info recovery_1st_stage: passwordless SSH to postgres@
?−ne0];thenlogger−i−plocal1.inforecovery1ststage:passwordlessSSHtopostgres@{DEST_NODE_HOST} failed. Please setup passwordless SSH.
exit 1
fi
Get PostgreSQL major version
PGVERSION=${PGHOME}/bin/initdb -V | awk '{print $3}' | sed 's/\..*//' | sed 's/\([0-9]*\)[a-zA-Z].*/\1/'
if [
P
G
V
E
R
S
I
O
N
−
g
e
12
]
;
t
h
e
n
R
E
C
O
V
E
R
Y
C
O
N
F
=
PGVERSION -ge 12 ]; then RECOVERYCONF=
PGVERSION−ge12];thenRECOVERYCONF={DEST_NODE_PGDATA}/myrecovery.conf
else
RECOVERYCONF=${DEST_NODE_PGDATA}/recovery.conf
fi
Create replication slot “${REPL_SLOT_NAME}”
${PGHOME}/bin/psql -p
P
R
I
M
A
R
Y
N
O
D
E
P
O
R
T
<
<
E
O
Q
S
E
L
E
C
T
p
g
c
r
e
a
t
e
p
h
y
s
i
c
a
l
r
e
p
l
i
c
a
t
i
o
n
s
l
o
t
(
′
{PRIMARY_NODE_PORT} << EOQ SELECT pg_create_physical_replication_slot('
PRIMARYNODEPORT<<EOQSELECTpgcreatephysicalreplicationslot(′{REPL_SLOT_NAME}');
EOQ
Execute pg_basebackup to recovery Standby node
ssh -T ${SSH_OPTIONS} P O S T G R E S Q L S T A R T U P U S E R @ {POSTGRESQL_STARTUP_USER}@ POSTGRESQLSTARTUPUSER@DEST_NODE_HOST "
set -o errexit
rm -rf $DEST_NODE_PGDATA/*
rm -rf $ARCHIVEDIR/*
${PGHOME}/bin/pg_basebackup -h $PRIMARY_NODE_HOST -U $REPLUSER -w -p $PRIMARY_NODE_PORT -D $DEST_NODE_PGDATA -X stream
cat >> ${NODE_PGDATA}/postgresql.auto.conf << EOT
primary_conninfo = ‘user=
R
E
P
L
U
S
E
R
p
a
s
s
w
o
r
d
=
{REPLUSER} password=
REPLUSERpassword={REPLUSER_PD} host=
P
R
I
M
A
R
Y
N
O
D
E
H
O
S
T
p
o
r
t
=
{PRIMARY_NODE_HOST} port=
PRIMARYNODEHOSTport={PRIMARY_NODE_PORT} sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any’
EOT
if [ ${PGVERSION} -ge 12 ]; then
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数Linux运维工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年Linux运维全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Linux运维知识点，真正体系化！
由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新
如果你觉得这些内容对你有帮助，可以添加VX：vip1024b （备注Linux运维获取）
最后的话
最近很多小伙伴找我要Linux学习资料，于是我翻箱倒柜，整理了一些优质资源，涵盖视频、电子书、PPT等共享给大家！
资料预览
给大家整理的视频资料：
给大家整理的电子书资料：
如果本文对你有帮助，欢迎点赞、收藏、转发给朋友，让我有持续创作的动力！
一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
x学习资料，于是我翻箱倒柜，整理了一些优质资源，涵盖视频、电子书、PPT等共享给大家！
资料预览
给大家整理的视频资料：
[外链图片转存中…(img-nM9Md52r-1712719786212)]
给大家整理的电子书资料：
[外链图片转存中…(img-I5QMOJe9-1712719786214)]
如果本文对你有帮助，欢迎点赞、收藏、转发给朋友，让我有持续创作的动力！
一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
[外链图片转存中…(img-o5Q4vbTk-1712719786215)]
454
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
