努力挣扎的生活
yangfk
2025-06-30

postgreSQL通过keepalived配置主从自动切换

//

# postgreSQL通过keepalived配置主从自动切换

  • 通过shell脚本结合keepalived，绑定vip，做主从自动切换，并自动配置主从，keepalived使用非抢占模式

  • shell脚本先监测vip是否存在，如果不存在会自动配置主从，当vip存在的时候，会把从状态切换为主，切换过程可能有数据丢失风险，自行承担，先手动测试好，再添加定时任务

  • pg_change_slave_to_master.sh 如果你安装postgreSql 不是容器运行，根据你的安装目录调整

pg_slave

名称 说明
192.168.123.224 keepalived虚拟vip
192.168.123.247 pg数据01
192.168.123.248 pg数据02
create_pg_slave.sh 自动配置pg 从脚本
keepalived.conf keepalived 配置文件
notify_script.sh keepalived 触发脚本
pg_change_slave_to_master.sh 修改pg从为主脚本
replica 主从复制用户密码：replica@pgsql
  • 安装数据库，配置好主从，安装keepalived

**前期准备工作，配置主从 **

# 通过容器启动postgreSQL-12

修改postgresql.conf ,pg_hba.conf ,添加主从用户,重启数据库
  • docker-compose.yaml
services:
  mes-postgis:
    restart: always
    image: postgis/postgis:12-3.2
    container_name: mes-postgis
    volumes:
      - /etc/localtime:/etc/localtime
      - /etc/timezone:/etc/timezone
      - ./postgis-data:/var/lib/postgresql/data
      - ./backup:/opt/backup
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: fPYEFEzw6fPMp@1
      ALLOW_IP_RANGE: 0.0.0.0/0
    network_mode: bridge
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  • pg_hba.conf
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0               md5
host    replication 	replica		172.30.201.0/24            md5
1
2
3
4
5

# 服务器配置：192.168.123.247

  • keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth2
    virtual_router_id 51
    priority 80
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1qaz!2wsx
    }
    virtual_ipaddress {
        192.168.123.224
    }
    notify_master "/data/scripts/notify_script.sh MASTER"
    notify_backup "/data/scripts/notify_script.sh BACKUP"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  • create_pg_slave.sh
#!/bin/bash

Date=$(date +%F_%H%M)

PG_Data=/data/location-uhpp/postgis-data
containerName=mes-postgis

export PATH=$PATH:/usr/local/bin

#先判断另一台主机IP端口是否可以通
    database_IP="192.168.123.247"
    if ! $(nc -z -w 2 ${database_IP} 5432 2>&1 > /dev/null ); then
      echo "$database_IP 5432 port not accessable"
      exit 2
    fi


if $(ps -ef |grep -v grep |grep walreceiver >/dev/null );then
  echo 'pg slave is ok.'
  exit 0
else
  if $(hostname -I |grep 192.168.123.224 >/dev/null );then
    echo "Vip 在主机: $(hostname)"
    exit 0
  fi
fi



#当目录存在，重命名
test -d $PG_Data && mv $PG_Data ${PG_Data}_${Date}
docker stop ${containerName}

#----------------------修改IP，从密码--------------#################

docker run  -e PGPASSWORD="replica@pgsql" -v ${PG_Data}:/var/lib/postgresql/data --rm --name pg_slave_init postgis/postgis:12-3.2 /bin/bash -c "/usr/bin/pg_basebackup -D /var/lib/postgresql/data -h 192.168.123.248 -p 5432 -U replica -Xstream -P"

if  [ $? -ne 0 ];then
  echo 'docker pg_basebackup exec failed'
  exit 1
fi


#配置从配置参数
if ! $(grep -v '#primary_conninfo'  ${PG_Data}/postgresql.conf |grep primary_conninfo >/dev/null);then

#----------------------修改IP，从密码--------------#################

cat >>${PG_Data}/postgresql.conf<<'EOF'
primary_conninfo = 'host=192.168.123.248 port=5432 user=replica password=replica@pgsql'
recovery_target_timeline = latest
recovery_min_apply_delay = 0s
EOF
fi

#配置只读模式
echo 'standby_mode = "on"' >> ${PG_Data}/standby.signal

chmod 600 ${PG_Data}/postgresql.conf
chmod 600 ${PG_Data}/standby.signal
cd ${PG_Data} && test -f pg_hba.conf && chown -R 999:999 ${PG_Data}/


#根据你的目录启动docker pg service

cd ${PG_Data}/../
docker-compose -f docker-compose-pg.yml up -d
sleep 5
docker  exec ${containerName} bash -c  'psql -U postgres -d postgres -c "select pg_is_in_recovery();"'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
  • notify_script.sh
#!/bin/bash

# 获取状态参数
STATE=$1
INSTANCE=$2

case "$STATE" in
    "MASTER")
        echo "[$(date)] Instance $INSTANCE became MASTER" >> /var/log/keepalived_notify.log
        # 在这里可以执行主服务器相关的操作，例如启动服务
        #bash  /data/scripts/control_application_start_stop.sh
        flock -n /tmp/pg_slave_to_master /bin/bash -x /data/scripts/pg_change_slave_to_master.sh >/dev/null 2>&1 &
        ;;
    "BACKUP")
        echo "[$(date)] Instance $INSTANCE became BACKUP" >> /var/log/keepalived_notify.log
        # 在这里可以执行备份服务器相关的操作，例如停止服务
        # bash  /data/scripts/control_application_start_stop.sh
        ;;
    "FAULT")
        echo "[$(date)] Instance $INSTANCE entered FAULT state" >> /var/log/keepalived_notify.log
        # 在这里可以执行故障状态相关的操作，例如发送通知
        ;;
    *)
        echo "[$(date)] Unknown state $STATE for instance $INSTANCE" >> /var/log/keepalived_notify.log
        ;;
esac
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
  • pg_change_slave_to_master.sh
#!/bin/bash

Date=$(date +%F_%H%M)
containerName=mes-postgis

if $(hostname -I |grep 192.168.123.224 >/dev/null );then
    echo "Vip 在主机: $(hostname)"

#切换从为主库
    if ! $(ps -ef |grep -v grep |grep walwriter >/dev/null );then
      docker exec -u 999  ${containerName} bash -c "/usr/lib/postgresql/12/bin/pg_ctl promote -D /var/lib/postgresql/data/" >>/tmp/pg_rep.log
      docker exec ${containerName} bash -c 'sed -i "s/primary_conninfo/#primary_conninfo/g" /var/lib/postgresql/data/postgresql.conf' >>/tmp/pg_rep.log
      docker exec ${containerName} bash -c 'sed -i "s/recovery_target_timeline/#recovery_target_timeline/g" /var/lib/postgresql/data/postgresql.conf' >>/tmp/pg_rep.log
      docker exec ${containerName} bash -c 'sed -i "s/recovery_min_apply_delay/#recovery_min_apply_delay/g" /var/lib/postgresql/data/postgresql.conf' >>/tmp/pg_rep.log
    fi

else

    echo "Vip 不在主机: $(hostname)"
    ps -ef |grep -v grep |grep walreceiver

fi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 服务器配置：192.168.123.248

  • keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    interface eno3
    virtual_router_id 51
    priority 100
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1qaz!2wsx
    }
    virtual_ipaddress {
        192.168.123.224
    }
    notify_master "/data/scripts/notify_script.sh MASTER"
    notify_backup "/data/scripts/notify_script.sh BACKUP"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
  • create_pg_slave.sh
#!/bin/bash

Date=$(date +%F_%H%M)

PG_Data=/data/location-uhpp/postgis-data
containerName=mes-postgis

export PATH=$PATH:/usr/local/bin


#先判断另一台主机IP端口是否可以通
    database_IP="192.168.123.247"
    if ! $(nc -z -w 2 ${database_IP} 5432 2>&1 > /dev/null ); then
      echo "$database_IP 5432 port not accessable"
      exit 2
    fi



if $(ps -ef |grep -v grep |grep walreceiver >/dev/null );then
  echo 'pg slave is ok.'
  exit 0
else
  if $(hostname -I |grep 192.168.123.224 >/dev/null );then
    echo "Vip 在主机: $(hostname)"
    exit 0
  fi
fi



#当目录存在，重命名
test -d $PG_Data && mv $PG_Data ${PG_Data}_${Date}
docker stop ${containerName}

#----------------------修改IP，从密码--------------#################

docker run  -e PGPASSWORD="replica@pgsql" -v ${PG_Data}:/var/lib/postgresql/data --rm --name pg_slave_init postgis/postgis:12-3.2 /bin/bash -c "/usr/bin/pg_basebackup -D /var/lib/postgresql/data -h 192.168.123.247 -p 5432 -U replica -Xstream -P"

if  [ $? -ne 0 ];then
  echo 'docker pg_basebackup exec failed'
  exit 1
fi


#配置从配置参数
if ! $(grep -v '#primary_conninfo'  ${PG_Data}/postgresql.conf |grep primary_conninfo >/dev/null);then

#----------------------修改IP，从密码--------------#################

cat >>${PG_Data}/postgresql.conf<<'EOF'
primary_conninfo = 'host=192.168.123.247 port=5432 user=replica password=replica@pgsql'
recovery_target_timeline = latest
recovery_min_apply_delay = 0s
EOF
fi

#配置只读模式
echo 'standby_mode = "on"' >> ${PG_Data}/standby.signal

chmod 600 ${PG_Data}/postgresql.conf
chmod 600 ${PG_Data}/standby.signal
cd ${PG_Data} && test -f pg_hba.conf && chown -R 999:999 ${PG_Data}/


#根据你的目录启动docker pg service

cd ${PG_Data}/../
docker-compose -f docker-compose-pg.yml up -d
sleep 5
docker  exec ${containerName} bash -c  'psql -U postgres -d postgres -c "select pg_is_in_recovery();"'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
  • notify_script.sh
#!/bin/bash

# 获取状态参数
STATE=$1
INSTANCE=$2

case "$STATE" in
    "MASTER")
        echo "[$(date)] Instance $INSTANCE became MASTER" >> /var/log/keepalived_notify.log
        # 在这里可以执行主服务器相关的操作，例如启动服务
        #bash  /data/scripts/control_application_start_stop.sh
        flock -n /tmp/pg_slave_to_master /bin/bash -x /data/scripts/pg_change_slave_to_master.sh >/dev/null 2>&1 &
        ;;
    "BACKUP")
        echo "[$(date)] Instance $INSTANCE became BACKUP" >> /var/log/keepalived_notify.log
        # 在这里可以执行备份服务器相关的操作，例如停止服务
        #flock -n /tmp/pg_create_slave /bin/bash -x /data/scripts/create_pg_slave.sh >/dev/null 2>&1 &
        ;;
    "FAULT")
        echo "[$(date)] Instance $INSTANCE entered FAULT state" >> /var/log/keepalived_notify.log
        # 在这里可以执行故障状态相关的操作，例如发送通知
        ;;
    *)
        echo "[$(date)] Unknown state $STATE for instance $INSTANCE" >> /var/log/keepalived_notify.log
        ;;
esac
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
  • pg_change_slave_to_master.sh
#!/bin/bash

Date=$(date +%F_%H%M)
containerName=mes-postgis

if $(hostname -I |grep 192.168.123.224 >/dev/null );then
    echo "Vip 在主机: $(hostname)"

#切换从为主库
    if ! $(ps -ef |grep -v grep |grep walwriter >/dev/null );then
      docker exec -u 999  ${containerName} bash -c "/usr/lib/postgresql/12/bin/pg_ctl promote -D /var/lib/postgresql/data/" >>/tmp/pg_rep.log
      docker exec ${containerName} bash -c 'sed -i "s/primary_conninfo/#primary_conninfo/g" /var/lib/postgresql/data/postgresql.conf' >>/tmp/pg_rep.log
      docker exec ${containerName} bash -c 'sed -i "s/recovery_target_timeline/#recovery_target_timeline/g" /var/lib/postgresql/data/postgresql.conf' >>/tmp/pg_rep.log
      docker exec ${containerName} bash -c 'sed -i "s/recovery_min_apply_delay/#recovery_min_apply_delay/g" /var/lib/postgresql/data/postgresql.conf' >>/tmp/pg_rep.log
    fi

else

    echo "Vip 不在主机: $(hostname)"
    ps -ef |grep -v grep |grep walreceiver

fi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 配置定时脚本监测

切换过程可能有数据丢失风险（主从复制流未完成，这个时候从切换为主），自行承担，先手动测试好，再添加定时任务

  • 两台服务器都配置定时任务,flock 命令防止定时任务在配置主从未完成的时候再次配置主从

crontab -l

*/30 * * * * /usr/sbin/ntpdate -u ntp1.aliyun.com >/dev/null 2>&1 &

#pg自动创建从库
* * * * * flock -n /tmp/pg_create_slave /bin/bash -x /data/scripts/create_pg_slave.sh >/dev/null 2>&1 &

#pg从切换为主
* * * * * flock -n /tmp/pg_change_slave_to_master /bin/bash -x /data/scripts/pg_change_slave_to_master.sh >/dev/null 2>&1 &
1
2
3
4
5
6
7
8
//
如果此文章对您有帮助，点击 -->> 请博主喝咖啡 (opens new window)
上次更新: 2025/06/30, 18:26:56
postgreSQL维护
Redis安装

← Redis安装→

最近更新
01
ubuntu2204编译安装php7.4.33
 06-30
02
nginx之resolver解析
 06-19
03
cephadm部署分布式存储
 05-30
更多文章>
Theme by Vdoing | Copyright © 2019-2025 yangfk | 湘ICP备2021014415号-1
×
//