Obtaining an active-passive ProxySQL on FreeBSD

notify 30 {
  match "system" "CARP";
  match "type" "(MASTER)";
  match "subsystem" "9@em0";
  action "/root/psqlrestore.sh $type $subsystem";
  action "logger -p local0.notice -t CARP-PROXYSQL '$subsystem role is MASTER. Restoring ProxySQL configuration '";
 };

notify 30 {
  match "system" "CARP";
  match "type" "(BACKUP)";
  match "subsystem" "9@em0";
  action "/root/disablepsqlbkp.sh $type $subsystem";
  action "logger -p local0.notice -t CARP-PROXYSQL '$subsystem role is BACKUP. Disabling ProxySQL backups'";
 };
 
 

All components working together

Under normal conditions, the master node will be copying online sqlite backups to the standby node through SSH. Those backups will accumulate on a specific directory and the same script will take care of keeping the number of files under control. Upon failover/switchover, the following will happen:

1) A CARP role switch is triggered whether manually or because of a host failure.

2) devd detects the change in the vhid VIP role and triggers the restore script.

3) This script finds the latest sqlite backup, copies it to the ProxySQL data directory, set the right privileges and starts the ProxySQL service.

4) The script will also enable a cron task to start shipping backups to the opposite node.

5) If it was a switchover and the old master node is alive, devd will execute a script that will disable sqlite backups (disablepsqlbkp.sh) and enable a received backups purge task.

Next steps

Even though this solution was tested successfully, points such as the following need to be considered before deploying it in production:

• Add logic to prevent corrupted backup restores to affect failover.
• Proper sqlite backups monitoring.
• Proper monitoring to ensure that the CARP role and the ProxySQL state are not opposite.