Linux VPS Database Failover Configuration Tutorial: Complete PostgreSQL and MySQL High Availability Setup with Automatic Recovery in 2026

Understanding Database Failover for VPS High Availability

Database failover configuration creates automatic recovery systems that protect your applications from downtime. When your primary database server fails, a properly configured failover system instantly switches traffic to a standby replica.

This happens without manual intervention. Your applications keep running while the system handles the switch in the background.

This tutorial covers complete failover setup for PostgreSQL streaming replication and MySQL master-slave configurations on Ubuntu 24.04 VPS instances. You'll implement monitoring scripts, automatic promotion logic, and health checks that ensure recovery happens fast.

Web applications running on HostMyCode VPS hosting need reliable database failover. This protects against hardware failures and network issues.

PostgreSQL Streaming Replication Failover Setup

PostgreSQL streaming replication provides real-time data synchronization between primary and standby servers. Start by configuring the primary database server with replication settings.

Edit the PostgreSQL configuration file:

sudo nano /etc/postgresql/16/main/postgresql.conf

Add these replication parameters:

wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/16/archive/%f'
log_replication_commands = on

Configure host-based authentication for replication connections:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add the replication entry:

host replication replica 192.168.1.0/24 md5

Create a replication user with proper privileges:

sudo -u postgres createuser --replication --pwprompt replica

Restart PostgreSQL to apply configuration changes:

sudo systemctl restart postgresql

PostgreSQL Standby Server Configuration

Set up the standby server by creating a base backup from the primary. Stop PostgreSQL on the standby server first:

sudo systemctl stop postgresql

Remove the existing data directory and create a fresh backup:

sudo rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h PRIMARY_SERVER_IP -D /var/lib/postgresql/16/main -U replica -P -v -R

The `-R` flag automatically creates recovery.conf with proper standby settings. Verify the standby configuration file was created:

sudo -u postgres cat /var/lib/postgresql/16/main/postgresql.auto.conf

Start PostgreSQL on the standby server:

sudo systemctl start postgresql

Check replication status from the primary server:

sudo -u postgres psql -c "SELECT client_addr, state, sync_state FROM pg_stat_replication;"

Implementing PostgreSQL Automatic Failover

Create a failover monitoring script that checks primary server health. The script promotes the standby when needed.

This script performs continuous health checks and handles automatic promotion.

Create the failover script:

sudo nano /usr/local/bin/pg_failover_monitor.sh

Add the monitoring logic:

#!/bin/bash
PRIMARY_HOST="192.168.1.10"
STANDBY_HOST="192.168.1.11"
REPL_USER="replica"
DB_NAME="postgres"
CHECK_INTERVAL=10
FAILURE_THRESHOLD=3

failure_count=0

while true; do
    if pg_isready -h $PRIMARY_HOST -U $REPL_USER -d $DB_NAME > /dev/null 2>&1; then
        failure_count=0
        echo "$(date): Primary server is healthy"
    else
        ((failure_count++))
        echo "$(date): Primary server check failed. Count: $failure_count"
        
        if [ $failure_count -ge $FAILURE_THRESHOLD ]; then
            echo "$(date): Promoting standby server to primary"
            sudo -u postgres pg_promote
            
            # Update application connection strings here
            /usr/local/bin/update_app_config.sh $STANDBY_HOST
            
            echo "$(date): Failover completed"
            exit 0
        fi
    fi
    
    sleep $CHECK_INTERVAL
done

Make the script executable:

sudo chmod +x /usr/local/bin/pg_failover_monitor.sh

You can enhance this monitoring by integrating with existing database monitoring systems. This provides comprehensive performance tracking.

MySQL Master-Slave Failover Configuration

Configure MySQL master-slave replication with automatic failover using MySQL's built-in replication features. Start by configuring the master server settings.

Edit the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add master configuration parameters:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = true
log-slave-updates = true
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

Restart MySQL and create a replication user:

sudo systemctl restart mysql
sudo mysql -u root -p

Execute these SQL commands:

CREATE USER 'repl'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

Note the binary log file name and position from the SHOW MASTER STATUS output.

MySQL Slave Server Setup and Configuration

Configure the slave server with unique server-id and replication settings:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add slave configuration:

[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = true
log-slave-updates = true
read_only = 1
relay-log = relay-bin
relay-log-index = relay-bin.index

Restart MySQL and configure replication:

sudo systemctl restart mysql
sudo mysql -u root -p

Set up the slave connection:

CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='strong_password',
    MASTER_AUTO_POSITION=1;

START SLAVE;
SHOW SLAVE STATUS\G;

Verify that Slave_IO_Running and Slave_SQL_Running both show "Yes" status.

MySQL Automatic Failover with MHA

Install MySQL Master High Availability (MHA) for automated failover management. MHA monitors replication health and performs automatic master switching when failures occur.

Install MHA manager on a dedicated monitoring server:

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager_0.58-0_all.deb
sudo dpkg -i mha4mysql-manager_0.58-0_all.deb
sudo apt-get install -f

Install MHA node package on all MySQL servers:

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node_0.58-0_all.deb
sudo dpkg -i mha4mysql-node_0.58-0_all.deb

Create MHA configuration file:

sudo nano /etc/mha/app1.cnf

Add the cluster configuration:

[server default]
user=mha
password=mha_password
ssh_user=ubuntu
repl_user=repl
repl_password=strong_password
ping_interval=3
secondary_check_script=masterha_secondary_check -s 192.168.1.11

[server1]
hostname=192.168.1.10
candidate_master=1

[server2]
hostname=192.168.1.11
candidate_master=1

Test MHA configuration:

masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf

Database Connection Pool Failover Integration

Configure application connection pools to work with database failover. Modern applications should use connection pooling that automatically detects failed connections.

The pool redirects to available servers when it detects problems.

For Node.js applications using MySQL, configure connection pooling with failover detection:

const mysql = require('mysql2');

const pool = mysql.createPool({
  host: '192.168.1.10',
  user: 'app_user',
  password: 'app_password',
  database: 'myapp',
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true,
  pool: {
    min: 2,
    max: 10
  }
});

pool.on('connection', function (connection) {
  console.log('Connected as id ' + connection.threadId);
});

pool.on('error', function(err) {
  console.log('Database connection error: ', err);
  if(err.code === 'PROTOCOL_CONNECTION_LOST') {
    // Attempt reconnection
  }
});

PostgreSQL applications can use connection pooling libraries like node-postgres with retry logic:

const { Pool } = require('pg');

const pool = new Pool({
  host: '192.168.1.10',
  user: 'app_user',
  password: 'app_password',
  database: 'myapp',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Proper connection pool configuration ensures applications handle failover events gracefully. This prevents dropping user sessions during database switches.

Monitoring and Health Checks

Implement comprehensive monitoring that tracks replication lag, connection counts, and server availability. Create monitoring scripts that integrate with your alerting system.

PostgreSQL replication monitoring script:

#!/bin/bash
PRIMARY="192.168.1.10"
STANDBY="192.168.1.11"

# Check replication lag
LAG=$(sudo -u postgres psql -h $PRIMARY -c "SELECT COALESCE(EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())), 0) AS lag_seconds;" -t 2>/dev/null)

if [ "$LAG" -gt 30 ]; then
    echo "WARNING: Replication lag is ${LAG} seconds"
    # Send alert
fi

# Check standby connectivity
if ! pg_isready -h $STANDBY > /dev/null 2>&1; then
    echo "ERROR: Standby server unreachable"
fi

MySQL replication monitoring:

#!/bin/bash
MASTER="192.168.1.10"
SLAVE="192.168.1.11"

# Check slave status
SLAVE_STATUS=$(mysql -h $SLAVE -u monitor -p$MONITOR_PASS -e "SHOW SLAVE STATUS\G" 2>/dev/null)

IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "ERROR: Slave replication not running properly"
fi

if [ "$SECONDS_BEHIND" -gt 60 ]; then
    echo "WARNING: Slave is $SECONDS_BEHIND seconds behind master"
fi

Testing Failover Scenarios

Regular failover testing ensures your configuration works correctly during actual outages. Create controlled test scenarios that simulate different failure conditions.

Test PostgreSQL failover by stopping the primary server:

# On primary server
sudo systemctl stop postgresql

# Monitor failover logs
tail -f /var/log/pg_failover.log

# Verify standby promotion
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

Test MySQL failover with MHA:

# Simulate master failure
sudo systemctl stop mysql

# Monitor MHA logs
tail -f /var/log/masterha/app1/manager.log

# Check failover completion
masterha_check_status --conf=/etc/mha/app1.cnf

Document test results and recovery times. This establishes baseline performance metrics for your failover system.

Ready to implement reliable database failover for your applications? HostMyCode's managed VPS hosting provides the reliable infrastructure and expert support you need for high-availability database configurations.

Frequently Asked Questions

How long should database failover take?

Well-configured automatic failover should complete within 30-60 seconds for PostgreSQL and 60-120 seconds for MySQL with MHA, depending on network conditions and detection thresholds.

Can I run applications during failover testing?

Yes, but expect brief connection interruptions during the failover window. Use connection pooling with retry logic to minimize application impact during testing.

What's the difference between synchronous and asynchronous replication for failover?

Synchronous replication ensures zero data loss but adds latency to write operations. Asynchronous replication provides better performance but may lose recent transactions during failover.

How do I handle split-brain scenarios in database failover?

Use proper fencing mechanisms and quorum-based decision making. Tools like MHA and Patroni include split-brain protection through master checking and automated conflict resolution.