Usually, database failover is called 'Failover'. When the master and slave are in asynchronous or synchronous replication, if the master fails, we have two options to activate the slave as the master to provide services.
I. Manual Failover
Activate the slave using the 'pg_ctl promote' command.
[halo@localhost ~]$ 2022-05-18 09:57:33.015 CST [1508] FATAL: could not receive data from WAL stream: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
2022-05-18 09:57:33.016 CST [1504] LOG: invalid record length at 0/302D640: wanted 24, got 0
2022-05-18 09:57:33.020 CST [2602] FATAL: could not connect to the primary server: could not connect to server: Connection refused
Is the server running on host "node1"(10.16.16.165) and accepting
TCP/IP connections on port 1921?When the master fails, errors appear in the slave system log, indicating that it cannot connect to the master.
[halo@localhost ~]$ pg_ctl promote
waiting for server to promote....2022-05-18 09:58:22.101 CST [1504] LOG: received promote request
2022-05-18 09:58:22.101 CST [1504] LOG: redo done at 0/302D608
2022-05-18 09:58:22.101 CST [1504] LOG: last completed transaction was at log time 2022-05-18 09:37:14.742818+08
2022-05-18 09:58:22.143 CST [1504] LOG: selected new timeline ID: 2
2022-05-18 09:58:22.228 CST [1504] LOG: archive recovery complete
2022-05-18 09:58:22.243 CST [1503] LOG: database system is ready to accept connections
done
server promotedOnce the pg_ctl promote command completes correctly, the slave is promoted to master and can provide services normally.
halo0root=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)If the original master's issue is resolved, we usually convert the original master into a standby slave of the new master. Using the pg_rewind command can achieve this without needing to rebuild the slave.
pg_rewind -D $PGDATA --source-server='host=node2 user=pgrewind password=123456 dbname=halo0root' -P
Note, the '-D' parameter above points to the local directory, 'node2' can be an IP address, and the user needs to be a superuser.
After pg_rewind execution is complete, manually create the file 'standby.signal':
touch $PGDATA/standby.signalAnd delete the information in the postgresql.auto.conf file. Then add the following content to postgresql.conf:
primary_conninfo = 'user=replica password=123456 channel_binding=prefer host=node1 port=1921 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=halo target_session_attrs=any'This way, the original master can become a slave of the new master. Now start the node1 database, and it becomes a slave of node2:
[halo@localhost halo]$ pg_ctl start
waiting for server to start....2022-05-18 10:57:15.956 CST [6220] LOG: ending log output to stderr
2022-05-18 10:57:15.956 CST [6220] HINT: Future log output will go to log destination "csvlog".
2022-05-18 10:57:15.956 CST [6220] LOG: starting Halo 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-05-18 10:57:15.957 CST [6220] LOG: listening on IPv4 address "0.0.0.0", port 1921
2022-05-18 10:57:15.957 CST [6220] LOG: listening on IPv6 address "::", port 1921
2022-05-18 10:57:15.960 CST [6220] LOG: listening on Unix socket "/var/run/halo/.s.PGSQL.1921"
2022-05-18 10:57:15.966 CST [6221] LOG: database system was interrupted while in recovery at log time 2022-05-17 10:38:22 CST
2022-05-18 10:57:15.966 CST [6221] HINT: If this has occurred more than once, data might be corrupted and you might need to choose an earlier recovery target.
2022-05-18 10:57:16.569 CST [6221] LOG: entering standby mode
2022-05-18 10:57:16.574 CST [6221] LOG: redo starts at 0/302D558
2022-05-18 10:57:16.577 CST [6221] LOG: consistent recovery state reached at 0/304DFA8
2022-05-18 10:57:16.577 CST [6221] LOG: invalid record length at 0/304DFA8: wanted 24, got 0
2022-05-18 10:57:16.578 CST [6220] LOG: database system is ready to accept read-only connections
2022-05-18 10:57:16.584 CST [6226] LOG: started streaming WAL from primary at 0/3000000 on timeline 2
done
server startedII. Automatic Failover
We implement automatic failover for 2 nodes based on keepalived.
Install keepalived on the master and slave using the root user
yum install -y keepalivedAfter installation, rename the original keepalived.conf file in the /etc/keepalived path
Place your own keepalived.conf file and health_check.sh file under /etc/keepalived
global_defs {
router_id halo_13
vrrp_skip_check_adv_addr
vrrp_garp_interval 0
vrrp_gna_interval 0
script_user root
enable_script_security
}
vrrp_script health_check {
script "/etc/keepalived/health_check.sh /u01/app/halo/product/dbms/13 /data/halo 10.16.16.169"
interval 1
weight -5
fall 2
rise 1
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 12345
}
track_script {
health_check
}
virtual_ipaddress {
10.16.16.169
}
}health_check.sh script
#!/bin/bash
PGHOME=$1
PGDATA=$2
VIPADDR=$3
A=`ps -C postgres --no-header | wc -l`
B=`ip a | grep $VIPADDR | wc -l`
C=`ps -ef | grep postgres | grep 'startup recovering' | wc -l`
D=`ps -ef | grep postgres | grep 'walreceiver' | wc -l`
echo "===`date "+%Y-%m-%d--%H:%M:%S"`===" >> /tmp/health_check.log
echo "Status A: $A" >> /tmp/health_check.log
echo "Status VIP: $B" >> /tmp/health_check.log
echo "Status C: $C" >> /tmp/health_check.log
echo "Status D: $D " >> /tmp/health_check.log
if [ $A -eq 0 ]; then
echo "`date "+%Y-%m-%d--%H:%M:%S"` failed" >> /tmp/health_check.log
systemctl stop keepalived
else
if [ $B -eq 1 -a $C -eq 1 -a $D -eq 0 ]; then
su - halo -c "$PGHOME/bin/pg_ctl promote -D $PGDATA"
echo "`date "+%Y-%m-%d--%H:%M:%S"` standby promote " >> /tmp/health_check.log
fi
fi
# start keepalived
systemctl start keepalivedStart the master first, then the slave.
When the master fails, the slave automatically detects the master failure and promotes itself to master. The VIP also migrates from the master to the slave simultaneously, and the application can continue running normally without configuration changes (the application needs to support reconnection).