Use keepalived to achieve high availability of PostgreSQL

First, the function

Keepalived provides a health check of the objects in the server pool, and also enables fault isolation of failed machines and services, removes them from the service pool, and keepalived automatically joins the service pool if the failed machine or service returns to normal. The PostgreSQL database can sometimes be down, so two servers are configured with keepalived, the primary server is responsible for user interaction, the server is responsible for real-time backups, and is highly available in the event of a failure.

Second, the principle of work

When Master and Slave are working properly, Master is responsible for the service, Slave is responsible for backup;
When Master hangs up slave normal, slave takes over the service, while turning off the master-slave copy function;
When Master returns to normal, the data is synchronized from slave, the master-slave replication function is turned off after the data is synchronized, and the master identity is restored, while slave waits for the master synchronization data to complete before resuming slave identity.

Third, the configuration process

Environment : master:10.6.1.143
slave:10.6.1.144
Virtural IP Address (VIP): 10.6.1.200

Steps :

1. Install keepalived on master and slave
$ sudo apt-get install keepalived

2, modify the master and slave/etc/hosts files
$ sudo vim/etc/hosts

3. After the default installation is complete keepalived There is no configuration file, so we need to create it manually:

First, create the following configuration file on master:
$ sudo vim/etc/keepalived/keepalived.conf

This file mainly configures monitoring scripts, monitoring time, monitoring network, virtual machine IP and other information

! Configuration File for Keepalived
Global_defs {
router_id POSTGRESQL-HA2
}

Vrrp_script Monitor_pgsql_status {
Script "/etc/keepalived/scripts/monitor_pgsql_status.sh"
Interval 30
}

Vrrp_instance V_postgresql-ha {
State BACKUP
Interface eth0
notify_master/etc/keepalived/scripts/to_master.sh
notify_backup/etc/keepalived/scripts/to_backup.sh
notify_stop/etc/keepalived/scripts/to_backup.sh

VIRTUAL_ROUTER_ID 51
Priority 80
Advert_int 1
Garp_master_delay 10

Authentication {
Auth_type PASS
Auth_pass Password

}

Track_script {
Monitor_pgsql_status
}

virtual_ipaddress {
Virtual ip/Port Dev eth1
}
}

Then, create the following configuration file on slave:
$ sudo vim/etc/keepalived/keepalived.conf! Configuration File for Keepalived
Global_defs {
router_id POSTGRESQL-HA2
}

Vrrp_script Monitor_pgsql_status {
Script "/etc/keepalived/scripts/monitor_pgsql_status.sh"
Interval 30
}

Vrrp_instance V_postgresql-ha {
State BACKUP
Interface eth0
notify_master/etc/keepalived/scripts/to_master.sh
notify_backup/etc/keepalived/scripts/to_backup.sh
notify_stop/etc/keepalived/scripts/to_backup.sh

VIRTUAL_ROUTER_ID 51
Priority 80
Advert_int 1
Garp_master_delay 10

Authentication {
Auth_type PASS
Auth_pass Password

}

Track_script {
Monitor_pgsql_status
}

virtual_ipaddress {
Virtual ip/Port Dev eth1
}
}

4. Create a Redis-monitoring script on master and slave
Attention:
1. You need to create a postgres user in PostgreSQL and give the Select Execute permission (whose permissions are limited to the minimum range);
2. Modify the variable psql and VIP according to the actual situation.
$ mkdir/etc/keepalived/scripts
$ vim/etc/keepalived/scripts/monitor_pgsql_status.sh
5. Write the following key scripts for operation:
notify_master/etc/keepalived/scripts/to_master.sh
notify_backup/etc/keepalived/scripts/to_backup.sh
Because the keepalived is called according to the state when the state is converted:
When the master state is entered, the Notify_master is called and the corresponding action is taken in the to_master.sh
Notify_backup is called when the backup status is entered and the appropriate action is taken in to_backup.sh
Call Notify_backup when the keepalived program terminates

6. Add executable permissions to the script:
$ sudo chmod +x/etc/keepalived/scripts/*.sh

You can use the following command to switch between master and slave mode:
sudo su
/etc/init.d/keepalived restart

Attached: Agent deployment process:
Unzip and install the DBCheck package
Assigning permissions and testing to start.sh-stop.sh scripts
Enter the/etc/keepalived/scripts directory
At the end of the to_master.sh script add: Cd/home/test/dbcheck &&./start.sh conf
At the beginning of the to_backup.sh script, add: Cd/home/test/dbcheck &&./stop.sh Kill

Use keepalived to achieve high availability of PostgreSQL