Pgsql master-Slave +keepalived high-availability configuration

Environment Description:


Host and IP:

192.168.11.177 Main Library

192.168.11.180 Standby Library

192.168.11.210 VIP

System:

centos7.2

PGSQL9.6 Master-Slave installed configuration complete (refer to my previous blog)

Installation configuration:

1. Installation Configuration keepalived

Primary and Standby Installation:

# yum Install-y keepalived

Main configuration:

# vi/etc/keepalived/keepalived.conf

Bal_defs {

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

ROUTER_ID PG

}


Vrrp_script Chk_pgsql {

Script "/etc/keepalived/scripts/pgsql_check.sh"

Interval 2


Weight-5

Fall 2

Rise 1

}



Vrrp_instance Vi_1 {
State BACKUP
Interface eth0
VIRTUAL_ROUTER_ID 61
Priority 100

Nopreempt

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}


Track_script {

Chk_pgsql

}


virtual_ipaddress {

192.168.11.210

}

}

Configuration:

# vi/etc/keepalived/keepalived.conf

Bal_defs {


Smtp_server 127.0.0.1


Smtp_connect_timeout 30


ROUTER_ID PG

}

Vrrp_script Chk_pgsql {


Script "/etc/keepalived/scripts/pgsql_check.sh"

Interval 2

Weight-5

Fall 2

Rise 1


}



Vrrp_instance Vi_1 {


State BACKUP


Interface eth0

VIRTUAL_ROUTER_ID 61

Priority 80

Nopreempt

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Chk_pgsql


} 

 virtual_ipaddress {


192.168.11.210
}

}

Attention:

Here virtual_router_id the default value of 51 will be error bogus VRRP packet received on ETH0!!!, so the value is changed to 61.

Master and Standby create directories and scripts

# mkdir/etc/keepalived/scripts

# vi/etc/keepalived/scripts/pgsql_check.sh

#!/bin/bash

#Judge whether pg is alive


A= ' Ps-c postgres--no-header | Wc-l '


#Judge where vip floats


B= ' IP A | grep 192.168.11.210 | Wc-l '


#Judge whether the slave library is waiting


C= ' Ps-ef | grep Postgres | grep ' Startup process ' | Wc-l '


#Judge whether the main library from the library link is normal


D= ' Ps-ef | grep Postgres | grep ' receiver ' | Wc-l '


#Judge whether the main library connection slave library is normal


E= ' Ps-ef | grep Postgres | grep ' Sender ' | Wc-l '


#If pg is dead, write the message to journal and close keepalived

If [$A-eq 0];then

echo "' Date" +%y-%m-%d--%h:%m:%s "' PostgreSQL stop so VIP stop" >>/etc/keepalived/log/check_pg.log

Systemctl Stop keepalived

Else

#Judging that the main hang, the VIP floated from the position of ascension, so that he could read and write

If [$B-eq 1-a $C-eq 1-a $D-eq 0];then

Su-postgres-c "Pg_ctl promote-d/data/pg_data"

echo "' Date" +%y-%m-%d--%h:%m:%s "' Standby promote" >>/etc/keepalived/log/check_pg.log

Fi

#Determine that you are the Lord and lose contact

If [$B-eq 1-a $C-eq 0-a $D-eq 0-a $E-eq 0];then

Sleep 10

echo "' Date" +%y-%m-%d--%h:%m:%s "' can ' t find standby" >>/etc/keepalived/log/check_pg.log

Fi

Fi

Primary and Standby configuration log:

Modify/etc/sysconfig/keepalived

Change keepalived_options= "-D" to keepalived_options= "-d-d-S 0"

# vi/etc/rsyslog.conf

Add the following configuration:

#keepalived-S 0

Local0.*/var/log/keepalived.log

Primary and Standby service

# Systemctl Start Keepalived.service

# Systemctl Enable Keepalived.service

2, stop the main library service, and switch the main library for the standby library

Stop the main library service, and then found that the VIP on the main library disappears, the VIP generated on the standby library, the repository becomes the main library, you can build the database to build the table and other operations.

Switch the original master library to standby:

$ cd/data/pg_data

$ RM-RF *

$ pg_basebackup-h 192.168.11.180-u repuser-d/data/pg_data-x stream-p

$ mv Recovery.done recovery.conf

$ VI recovery.conf

Primary_conninfo = ' host=192.168.11.177 port=5432 user=repuser password=password123! Keepalives_idle=60 '

>>

Primary_conninfo = ' host=192.168.11.180 port=5432 user=repuser password=password123! Keepalives_idle=60 '

Boot host keepalived

# Systemctl Start keepalived

3. Check and verify

View original standby service and library status

$ PS-EF | grep postgres

Postgres 1081 1 0 Aug07? 00:00:06/usr/local/postgresql/bin/postgres-d/data/pg_data

Postgres 1083 1081 0 Aug07? 00:00:01 Postgres:checkpointer Process

Postgres 1084 1081 0 Aug07? 00:00:02 Postgres:writer Process

Postgres 1085 1081 0 Aug07? 00:00:00 postgres:stats Collector Process

Postgres 13961 1081 0 11:09? 00:00:00 Postgres:wal Writer Process

Postgres 13962 1081 0 11:09? 00:00:00 postgres:autovacuum Launcher Process

Postgres 13963 1081 0 11:09? 00:00:00 Postgres:archiver Process Last was 000000020000000000000010

Postgres 27065 1081 0 11:25? 00:00:00 Postgres:wal Sender Process Repuser 192.168.11.177 (47074) streaming 0/11000060

Root 27922 3590 0 11:26 pts/1 00:00:00 grep--color=auto postgres

postgres=# SELECT pg_is_in_recovery from Pg_is_in_recovery ();

Pg_is_in_recovery

-------------------

F

(1 row)

View the original main library service and library status

$ PS-EF | grep postgres

Postgres 2602 1 0 11:25 pts/1 00:00:00/usr/local/postgresql/bin/postgres-d/data/pg_data

Postgres 2603 2602 0 11:25? 00:00:00 Postgres:startup Process Recovering 000000020000000000000011

Postgres 2604 2602 0 11:25? 00:00:00 Postgres:checkpointer Process

Postgres 2605 2602 0 11:25? 00:00:00 Postgres:writer Process

Postgres 2606 2602 0 11:25? 00:00:00 postgres:stats Collector Process

Postgres 2607 2602 0 11:25? 00:00:00 Postgres:wal receiver Process streaming 0/11000060

Postgres 2613 2325 0 11:25 pts/1 00:00:00 grep--color=auto postgres

postgres=# SELECT pg_is_in_recovery from Pg_is_in_recovery ();

Pg_is_in_recovery

-------------------

T

(1 row)

High-availability scenarios:

Pgsql Master + keepalived

Resources:

192.168.11.177 Main Library (called Server A)

192.168.11.180 repository (called Server B)

192.168.11.210 VIP

1), Analog a pgsql service stop

b take over VIP (automatic)

b from library to main library (automatic)

Start a Pgsql service and configure a as a standby (manual)

2), Analog B's Pgsql service stop

A take over VIP (automatic)

A from the library to the main library (automatic)

Start B's Pgsql service and configure B as standby (manual)

Reference:

79001890

Pgsql master-Slave +keepalived high-availability configuration