运维最新Thingworx高可用集群部署（四）-Pgpool-II安装，真香定律

2401_83947434

已于 2024-05-08 11:55:32 修改

阅读量761

点赞数 25

分类专栏：程序员文章标签：运维

于 2024-05-08 11:55:31 首次发布

本文链接：https://blog.csdn.net/2401_83947434/article/details/138566438

版权

程序员专栏收录该内容

130 篇文章 0 订阅

订阅专栏

最全的Linux教程，Linux从入门到精通

======================

linux从入门到精通(第2版)
Linux系统移植
Linux驱动开发入门与实战
LINUX 系统移植第2版
Linux开源网络全栈详解从DPDK到OpenFlow

华为18级工程师呕心沥血撰写3000页Linux学习笔记教程

第一份《Linux从入门到精通》466页

====================

内容简介

====

本书是获得了很多读者好评的Linux经典畅销书**《Linux从入门到精通》的第2版**。本书第1版出版后曾经多次印刷，并被51CTO读书频道评为“最受读者喜爱的原创IT技术图书奖”。本书第﹖版以最新的Ubuntu 12.04为版本，循序渐进地向读者介绍了Linux 的基础应用、系统管理、网络应用、娱乐和办公、程序开发、服务器配置、系统安全等。本书附带1张光盘，内容为本书配套多媒体教学视频。另外,本书还为读者提供了大量的Linux学习资料和Ubuntu安装镜像文件，供读者免费下载。

华为18级工程师呕心沥血撰写3000页Linux学习笔记教程

本书适合广大Linux初中级用户、开源软件爱好者和大专院校的学生阅读，同时也非常适合准备从事Linux平台开发的各类人员。

需要《Linux入门到精通》、《linux系统移植》、《Linux驱动开发入门实战》、《Linux开源网络全栈》电子书籍及教程的工程师朋友们劳烦您转发+评论

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以点击这里获取！

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

logdir = ‘/var/run/pgpool’
listen_addresses = ‘’
port = 9999
pcp_listen_addresses = '’
pcp_port = 9898

backend_hostname0 = ‘10.10.10.71’
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/data/pg_data’
backend_flag0 = ‘ALLOW_TO_FAILOVER’

backend_hostname1 = ‘10.10.10.72’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/data/pg_data’
backend_flag1 = ‘ALLOW_TO_FAILOVER’
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} ${POSTGRESQL_STARTUP_USER}@$ {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" NEW_MASTER_NODE_PGDATA="$ {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: \dotsODE_HOST//[-.]/_̲} POSTGRESQL_ST\dots$ {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 [ $ne 0 ]; then logger -i -p local1.info follow_master.sh: node_id=$ {NODE_ID} is not running. skipping follow master command
exit 0
fi

Test passwordless SSH

ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@$ {NEW_MASTER_NODE_HOST} ls /tmp > /dev/null

if [ $ne 0 ]; then logger -i -p local1.info follow_master.sh: passwordless SSH to postgres@$ {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 [ $PG V ERS I ON - g e 12]; t h e n RECO V ER Y CONF =$ {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 ${NEW_MASTER_NODE_PORT} \ -c "SELECT pg_create_physical_replication_slot('$ {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} ${POSTGRESQL_STARTUP_USER}@$ {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= ${REPLUSER} password=$ {REPLUSER_PD} host= ${NEW_MASTER_NODE_HOST} port=$ {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

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= $hostname) PGHOME=/usr/pgsql-12 ARCHIVEDIR=/data/pg_arch REPLUSER=repuser REPLUSER_PD=Repuser@2024_RP REPL_SLOT_NAME=$ {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} ${POSTGRESQL_STARTUP_USER}@$ {DEST_NODE_HOST} ls /tmp > /dev/null

if [ $ne 0 ]; then logger -i -p local1.info recovery_1st_stage: passwordless SSH to postgres@$ {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 [ $PG V ERS I ON - g e 12]; t h e n RECO V ER Y CONF =$ {DEST_NODE_PGDATA}/myrecovery.conf
else
RECOVERYCONF=${DEST_NODE_PGDATA}/recovery.conf
fi

Create replication slot “${REPL_SLOT_NAME}”

${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ SELECT pg_create_physical_replication_slot('$ {REPL_SLOT_NAME}');
EOQ

Execute pg_basebackup to recovery Standby node

ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@$ 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= ${REPLUSER} password=$ {REPLUSER_PD} host= ${PRIMARY_NODE_HOST} port=$ {PRIMARY_NODE_PORT} sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any’
EOT
if [ ${PGVERSION} -ge 12 ]; then
touch ${DEST_NODE_PGDATA}/standby.signal
echo “standby_mode = ‘on’” > ${NODE_PGDATA}/standby.signal
else
echo “standby_mode = ‘on’” >> ${RECOVERYCONF}
fi
"

if [ $? -ne 0 ]; then

 ${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ

SELECT pg_drop_replication_slot(‘${REPL_SLOT_NAME}’);
EOQ

 logger -i -p local1.error recovery_1st_stage: end: pg_basebackup failed. online recovery failed
 exit 1

logger -i -p local1.info recovery_1st_stage: end: recovery_1st_stage complete
exit 0


#### 修改pgpool\_remote\_start

#!/bin/bash

This script is run after recovery_1st_stage to start Standby node.

DEST_NODE_HOST=“$1”
DEST_NODE_PGDATA=“$2”

PGHOME=/usr/pgsql-12
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 pgpool_remote_start: start: remote start Standby node $DEST_NODE_HOST

Test passwordless SSH

ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@$ {DEST_NODE_HOST} ls /tmp > /dev/null

if [ $ne 0 ]; then logger -i -p local1.info pgpool_remote_start: passwordless SSH to postgres@$ {DEST_NODE_HOST} failed. Please setup passwordless SSH.
exit 1
fi

Start Standby node

ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@$ {DEST_NODE_HOST} "

为了做好运维面试路上的助攻手，特整理了上百道 【运维技术栈面试题集锦】 ，让你面试不慌心不跳，高薪offer怀里抱！

这次整理的面试题，小到shell、MySQL，大到K8s等云原生技术栈，不仅适合运维新人入行面试需要，还适用于想提升进阶跳槽加薪的运维朋友。

本份面试集锦涵盖了

174 道运维工程师面试题
128道k8s面试题
108道shell脚本面试题
200道Linux面试题
51道docker面试题
35道Jenkis面试题
78道MongoDB面试题
17道ansible面试题
60道dubbo面试题
53道kafka面试
18道mysql面试题
40道nginx面试题
77道redis面试题
28道zookeeper

总计 1000+ 道面试题，内容又全含金量又高

174道运维工程师面试题

1、什么是运维?

2、在工作中，运维人员经常需要跟运营人员打交道，请问运营人员是做什么工作的?

3、现在给你三百台服务器，你怎么对他们进行管理?

4、简述raid0 raid1raid5二种工作模式的工作原理及特点

5、LVS、Nginx、HAproxy有什么区别?工作中你怎么选择?

6、Squid、Varinsh和Nginx有什么区别，工作中你怎么选择?

7、Tomcat和Resin有什么区别，工作中你怎么选择?

8、什么是中间件?什么是jdk?

9、讲述一下Tomcat8005、8009、8080三个端口的含义？

10、什么叫CDN?

11、什么叫网站灰度发布?

12、简述DNS进行域名解析的过程?

13、RabbitMQ是什么东西?

14、讲一下Keepalived的工作原理?

15、讲述一下LVS三种模式的工作过程?

16、mysql的innodb如何定位锁问题，mysql如何减少主从复制延迟?

17、如何重置mysql root密码?

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以点击这里获取！

区别，工作中你怎么选择?

8、什么是中间件?什么是jdk?

9、讲述一下Tomcat8005、8009、8080三个端口的含义？

10、什么叫CDN?

11、什么叫网站灰度发布?

12、简述DNS进行域名解析的过程?

13、RabbitMQ是什么东西?

14、讲一下Keepalived的工作原理?

15、讲述一下LVS三种模式的工作过程?

16、mysql的innodb如何定位锁问题，mysql如何减少主从复制延迟?

17、如何重置mysql root密码?

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以点击这里获取！

2401_83947434

关注

25
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
运维最新Thingworx高可用集群部署（四）-Pgpool-II安装，真香定律

最全的Linux教程，Linux从入门到精通第一份《Linux从入门到精通》466页内容简介====本书是获得了很多读者好评的Linux经典畅销书**《Linux从入门到精通》的第2版**。本书第1版出版后曾经多次印刷，并被51CTO读书频道评为“最受读者喜爱的原创IT技术图书奖”。本书第﹖版以最新的Ubuntu 12.04为版本，循序渐进地向读者介绍了Linux 的基础应用、系统管理、网络应用、娱乐和办公、程序开发、服务器配置、系统安全等。本书附带1张光盘，内容为本书配套多媒体教学视频。
复制链接

扫一扫