Database DR: PostgreSQL & MySQL

Your Database Is Your Most Critical Asset

For most applications, the database is the single most critical component to protect. Losing your application servers is inconvenient — you can redeploy from code. Losing your database means losing your business data, your customers' information, and potentially your entire business.

This guide covers practical disaster recovery strategies for PostgreSQL and MySQL, the two most popular open-source databases in the Indian cloud ecosystem.

PostgreSQL Disaster Recovery

Streaming Replication

PostgreSQL streaming replication sends Write-Ahead Log (WAL) records from a primary server to one or more standby servers in real time.

**Setting up the primary server:**

-- postgresql.conf on primary
wal_level = replica
max_wal_senders = 5
wal_keep_size = '2GB'
synchronous_standby_names = 'standby1'

-- Create replication role CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password'; ```

# pg_hba.conf — allow standby to connect
host replication replicator standby-ip/32 scram-sha-256

**Setting up the standby server:**

# Take a base backup from primary
pg_basebackup -h primary-ip -U replicator -D /var/lib/postgresql/data -Fp -Xs -P -R

# The -R flag creates standby.signal and configures primary_conninfo # Start PostgreSQL on standby systemctl start postgresql ```

Synchronous vs Asynchronous Replication

**Synchronous replication** guarantees zero data loss but adds write latency:

-- On primary: wait for standby confirmation
synchronous_standby_names = 'FIRST 1 (standby1, standby2)'
synchronous_commit = on

For Indian businesses processing financial transactions, synchronous replication to a standby in the same datacenter (latency under 1ms) with asynchronous replication to a cross-region standby provides both durability and disaster protection.

Point-in-Time Recovery (PITR)

PITR allows you to restore your database to any specific moment:

# Archive WAL files continuously
archive_mode = on
archive_command = 'cp %p /archive/%f'

# Recovery configuration restore_command = 'cp /archive/%f %p' recovery_target_time = '2025-01-15 14:30:00 IST' recovery_target_action = 'promote' ```

PITR is invaluable for recovering from accidental data deletion. If a developer drops a table at 2:30 PM, you can restore to 2:29 PM.

Logical Replication

PostgreSQL logical replication (version 10+) replicates specific tables rather than the entire cluster:

-- On publisher (primary)
CREATE PUBLICATION dr_pub FOR TABLE orders, customers, payments;

-- On subscriber (DR standby) CREATE SUBSCRIPTION dr_sub CONNECTION 'host=primary-ip dbname=myapp user=replicator' PUBLICATION dr_pub; ```

Useful when you only need to replicate critical tables to a DR site while excluding large log tables.

MySQL Disaster Recovery

MySQL Replication

MySQL offers several replication modes:

**Asynchronous replication (default):**

-- On primary (my.cnf)
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7

-- Create replication user CREATE USER 'repl'@'standby-ip' IDENTIFIED BY 'secure_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'standby-ip'; ```

-- On standby
[mysqld]
server-id = 2
relay_log = /var/log/mysql/relay-bin.log
read_only = 1

CHANGE MASTER TO MASTER_HOST='primary-ip', MASTER_USER='repl', MASTER_PASSWORD='secure_password', MASTER_AUTO_POSITION=1; START SLAVE; ```

**Semi-synchronous replication:**

-- Install plugin on primary
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 3000; -- 3 seconds

-- Install plugin on standby INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SET GLOBAL rpl_semi_sync_slave_enabled = 1; ```

Group Replication

MySQL Group Replication provides multi-primary or single-primary replication with automatic failover:

[mysqld]
plugin_load_add = 'group_replication.so'
group_replication_group_name = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
group_replication_start_on_boot = OFF
group_replication_local_address = 'node1:33061'
group_replication_group_seeds = 'node1:33061,node2:33061,node3:33061'
group_replication_single_primary_mode = ON

Group Replication is ideal for high-availability MySQL clusters where automatic failover is critical.

Automating Database Failover

Manual failover under pressure is error-prone. Use tools like:

Patroni for PostgreSQL

Patroni manages PostgreSQL HA with automatic failover:

# patroni.yml
scope: postgres-cluster
namespace: /db/
name: node1

restapi: listen: 0.0.0.0:8008

etcd: host: etcd1:2379

bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 synchronous_mode: true ```

ProxySQL for MySQL

ProxySQL acts as a smart proxy that routes queries to the appropriate MySQL server and handles failover:

-- Configure backend servers
INSERT INTO mysql_servers (hostname, port, weight)
VALUES
  ('primary-host', 3306, 1000),
  ('standby-host', 3306, 100);

-- Configure health checks UPDATE mysql_servers SET max_latency_ms = 500; LOAD MYSQL SERVERS TO RUNTIME; ```

Backup Strategy for Databases

Combine replication with regular backups:

#!/bin/bash
# PostgreSQL backup with WAL archiving

# Daily base backup pg_basebackup -h localhost -D /backups/base/$(date +%Y%m%d) -Ft -z -P

# Retain for 30 days find /backups/base/ -maxdepth 1 -mtime +30 -exec rm -rf {} \;

# Verify backup integrity pg_verifybackup /backups/base/$(date +%Y%m%d) ```

#!/bin/bash
# MySQL backup with mysqldump and compression

mysqldump --all-databases --single-transaction \ --routines --triggers --events \ | gzip > /backups/mysql/full_$(date +%Y%m%d).sql.gz

# Or use Percona XtraBackup for hot backups xtrabackup --backup --target-dir=/backups/xtrabackup/$(date +%Y%m%d) ```

Monitoring Replication Health

Monitor these critical metrics:

**Replication lag**: Time difference between primary and standby
**WAL/Binlog generation rate**: Indicates write load
**Connection status**: Standby connected to primary
**Disk space on standby**: Ensure adequate space for WAL/relay logs

-- PostgreSQL replication monitoring
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- MySQL replication monitoring SHOW SLAVE STATUS\G -- Check Seconds_Behind_Master ```

Conclusion

Database disaster recovery requires a layered approach: streaming replication for real-time protection, point-in-time recovery for accidental data loss, automated failover for minimizing downtime, and regular backups for ultimate safety. Choose the replication strategy that matches your RPO and RTO requirements, automate failover, and monitor replication health continuously.

Database Disaster Recovery: PostgreSQL and MySQL Replication Strategies