Database Disaster Recovery

Database disaster recovery (DR) encompasses the policies, procedures, and tools necessary to recover

database systems after unexpected events that cause data loss or system failure. A robust DR strategy

is essential for maintaining business continuity and minimizing downtime in case of hardware failures,

software corruption, human errors, or natural disasters.

In this lesson, we'll explore comprehensive disaster recovery strategies for PostgreSQL databases,

including different backup techniques, recovery procedures, high availability configurations, and

business continuity planning. You'll learn how to implement reliable disaster recovery solutions that

align with your organization's recovery time objectives (RTOs) and recovery point objectives (RPOs).

Disaster Recovery Statistics

According to a 2023 industry survey, organizations with comprehensive database disaster recovery

plans experience 83% less downtime during incidents and recover 4.5 times faster than those without

formalized plans. Yet, only 37% of organizations regularly test their database recovery procedures.

Backup Strategies

Recovery Procedures

High Availability

Business Continuity

PostgreSQL Backup Methods

PostgreSQL offers several methods for creating backups, each with different trade-offs in terms of

backup size, speed, flexibility, and impact on system performance. The right backup approach depends

on your specific requirements and constraints.

Logical Backups

Logical backups export database content as SQL statements or in other text formats. These backups

contain the commands needed to recreate the database schema and populate it with data.

Key tools and methods:

: Creates a consistent backup of a single database

pg_dumpall

: Backs up all databases in a cluster, including global objects

Custom format (-Fc)

: Creates a compressed, parallelizable backup file

Directory format (-Fd)

: Creates a directory with one file per table

Plain text (-Fp)

: Creates a SQL script file

Advantages:

Selective backup and restore of specific databases, schemas, or tables

Platform and version independence (can restore to different PostgreSQL versions)

Human-readable format (when using plain text)

Ability to modify data during restore

No need for filesystem-level access to the database files

Disadvantages:

Slower than physical backups, especially for large databases

Larger backup size for binary data

Slower restore process

Requires database server processing resources during backup

Physical Backups

Physical backups involve copying the database's data files, WAL files, and configuration

files. These backups provide a bit-for-bit copy of the database at the filesystem level.

Key methods:

File system backups

: Direct copying of database files

pg_basebackup

: Creates a binary copy of the database cluster files

Continuous archiving

: Combines a base backup with archived WAL files

Advantages:

Faster backup and restore for large databases

Lower impact on database server processing resources

Ability to perform point-in-time recovery

Smaller backup size (when combined with incremental approaches)

Disadvantages:

Requires stopping the server or obtaining a consistent snapshot

Platform and version dependent (cannot restore to different PostgreSQL versions)

Requires filesystem-level access to the database files

Less flexibility for selective restore

Continuous Archiving and PITR

Continuous archiving combines a base backup with Write-Ahead Log (WAL) archiving to enable

Point-In-Time Recovery (PITR), allowing you to restore your database to any specific moment.

Key components:

Base backup

: A complete copy of the database at a point in time

WAL archiving

: Continuous archiving of transaction log segments

Recovery configuration

: Settings that control the recovery process

Advantages:

Recover to any point in time, not just to when backups were taken

Minimize data loss in case of failure

Lower storage requirements compared to frequent full backups

Ability to recover from logical data corruption or user errors

Implementation steps:

Configure WAL archiving in PostgreSQL

Create a base backup using pg_basebackup or another method

Continuously archive WAL files to a safe location

For recovery, restore the base backup and replay WAL files

Point-in-time recovery configuration (PostgreSQL 12+):

Backup Tools and Solutions

While PostgreSQL's native tools provide robust backup capabilities, various third-party

tools and solutions extend these capabilities with additional features like scheduling,

monitoring, and automation.

Open Source Tools

Barman (Backup and Recovery Manager)

: Supports incremental backups, parallel backup/restore, backup cataloging, and remote operations

pgBackRest

: Features parallel backup/restore, delta restores, and encryption

: Cloud-focused backup tool with compression and encryption

pg_probackup

: Supports incremental, differential backups and retention policies

: Specialized tool for copying databases between servers

Commercial Solutions

: HA solution with backup/restore capabilities

EnterpriseDB Backup and Recovery Tool

: Enterprise-grade backup solution

Cloud Provider Backup Services

: AWS Backup, Azure Backup, GCP Backup

Veeam Backup & Replication

: Enterprise backup solution with PostgreSQL support

Veritas NetBackup

: Enterprise-level backup with PostgreSQL integration

Example: pgBackRest configuration

Example: Barman configuration

Backup Strategy Design

Designing an effective backup strategy requires balancing various factors including recovery

objectives, performance impact, storage requirements, and operational complexity.

Recovery Objectives

Define your Recovery Time Objective (RTO) and Recovery Point Objective (RPO):

: The maximum acceptable time to restore service after a failure

: The maximum acceptable data loss measured in time

Example: An RPO of 15 minutes means you need a backup strategy that ensures

no more than 15 minutes of data could be lost in a disaster. This might require continuous archiving

with WAL shipping every 15 minutes.

Backup Schedule

Determine the frequency and type of backups based on your recovery objectives:

Full backups

: Complete copy of the database, typically weekly

Incremental backups

: Only changes since the last backup, typically daily

Differential backups

: Changes since the last full backup, typically daily

Continuous archiving

: Ongoing archiving of WAL files, typically every few minutes

Example schedule: Weekly full backups on Sunday, daily incremental backups

Monday-Saturday, and continuous WAL archiving with a maximum 15-minute lag.

Storage and Retention

Define where backups will be stored and for how long:

Storage locations

: Local, remote, cloud, or hybrid

Retention periods

: How long to keep different backup types

Storage tiering

: Moving older backups to less expensive storage

Immutable storage

: Protection against ransomware and unauthorized deletion

Example policy: Keep 4 weeks of full backups, 14 days of incremental backups,

and 7 days of WAL archives. Store the most recent week on local storage and move older backups to cloud

archive storage.

Security and Compliance

Ensure backups meet security requirements and compliance regulations:

Encryption

: Encrypt backups at rest and in transit

Access controls

: Limit who can access or restore backups

Audit logging

: Track backup and restore operations

Compliance

: Ensure backups meet regulatory requirements

Example: For financial data, encrypt all backups with AES-256, require multi-factor

authentication for access, log all backup operations, and retain backups for 7 years as required by regulations.

Backup Strategy Checklist

Define recovery objectives (RTO and RPO)

Select appropriate backup methods

Establish backup schedule and frequency

Define retention periods and storage locations

Implement security and encryption

Document restore procedures

Automate backup processes

Implement monitoring and alerting

Regularly test backup restoration

Periodically review and update the strategy

1# Example: Creating a logical backup with pg_dump (custom format)
2pg_dump -Fc -d mydb -f mydb_backup.dump
3
4# Example: Creating a logical backup of specific tables
5pg_dump -Fc -d mydb -t schema1.table1 -t schema1.table2 -f tables_backup.dump
6
7# Example: Creating a logical backup of all databases with pg_dumpall
8pg_dumpall -f all_databases.sql
9
10# Example: Restoring from a logical backup
11pg_restore -d mydb_restored mydb_backup.dump

1# Example: Creating a physical backup with pg_basebackup
2pg_basebackup -D /backup/base -F tar -z -P
3
4# Example: Setting up continuous archiving (in postgresql.conf)
5wal_level = replica
6archive_mode = on
7archive_command = 'test ! -f /backup/archive/%f && cp %p /backup/archive/%f'
8
9# Example: Restoring from a physical backup
10# 1. Stop PostgreSQL
11systemctl stop postgresql
12
13# 2. Clear the data directory
14rm -rf /var/lib/postgresql/data/*
15
16# 3. Restore the base backup
17tar -xzf /backup/base/base.tar.gz -C /var/lib/postgresql/data
18
19# 4. Create recovery.conf (PostgreSQL 11 or earlier)
20echo "restore_command = 'cp /backup/archive/%f %p'" > /var/lib/postgresql/data/recovery.conf
21
22# 5. Start PostgreSQL
23systemctl start postgresql

1# postgresql.conf settings for WAL archiving
2wal_level = replica  # or logical
3archive_mode = on
4archive_command = 'rsync -a %p backup-server:/archive/%f'
5archive_timeout = 300  # seconds

1# Create a recovery configuration file: postgresql.auto.conf or postgresql.conf
2cat > /var/lib/postgresql/data/postgresql.auto.conf << EOF
3restore_command = 'cp /backup/archive/%f %p'
4recovery_target_time = '2023-05-15 13:45:00'
5recovery_target_action = 'promote'
6EOF
7
8# Create a standby.signal file to enter recovery mode
9touch /var/lib/postgresql/data/standby.signal
10
11# Start PostgreSQL, which will enter recovery mode
12systemctl start postgresql
13
14# After recovery completes, the server will be promoted automatically
15# For PostgreSQL 11 and earlier, a similar configuration would be in recovery.conf

1# /etc/pgbackrest.conf
2[global]
3repo1-path=/var/lib/pgbackrest
4log-level-console=info
5log-level-file=debug
6
7[demo]
8pg1-path=/var/lib/postgresql/data
9
10# Create a full backup
11pgbackrest --stanza=demo backup
12
13# Create a differential backup
14pgbackrest --stanza=demo --type=diff backup
15
16# Create an incremental backup
17pgbackrest --stanza=demo --type=incr backup
18
19# Restore to latest backup
20pgbackrest --stanza=demo restore
21
22# Restore to a point-in-time
23pgbackrest --stanza=demo --type=time --target="2023-05-15 13:45:00" restore

1# /etc/barman.conf
2[barman]
3barman_user = barman
4configuration_files_directory = /etc/barman.d
5barman_home = /var/lib/barman
6log_file = /var/log/barman/barman.log
7log_level = INFO
8compression = gzip
9retention_policy = RECOVERY WINDOW OF 7 days
10
11[pg_server]
12description = "PostgreSQL Server"
13conninfo = host=pg-server user=barman dbname=postgres
14backup_method = rsync
15streaming_conninfo = host=pg-server user=streaming_barman dbname=postgres
16streaming_archiver = on
17slot_name = barman
18create_slot = auto
19backup_options = concurrent_backup
20retention_policy = RECOVERY WINDOW OF 7 days

Recovery Planning and Documentation

Effective disaster recovery requires detailed planning and documentation of recovery procedures.

These procedures should be clear, comprehensive, and regularly tested to ensure they work when needed.

Recovery Runbooks

Create detailed, step-by-step recovery procedures for different disaster scenarios:

Complete database server failure

Data corruption or logical errors

Accidental data deletion

Storage system failure

Network or connectivity issues

Site or regional disaster

Each runbook should include:

Prerequisites and required resources

Step-by-step recovery instructions

Decision points and alternatives

Verification and validation steps

Contact information for key personnel

Expected timeframes for each step

Recovery Testing

Regularly test recovery procedures to ensure they work as expected and to identify areas for improvement:

Tabletop exercises

: Walk through recovery scenarios without executing them

Functional testing

: Test individual recovery components in isolation

Full recovery testing

: Perform complete recovery in a test environment

Chaos engineering

: Deliberately introduce failures to test resilience

Disaster recovery drills

: Periodically simulate disasters and execute recovery procedures

Document test results and use them to refine procedures:

Actual recovery time vs. RTO

Actual data loss vs. RPO

Problems encountered during recovery

Improvements to implement

PostgreSQL Recovery Techniques

PostgreSQL provides several recovery techniques for different disaster scenarios. Understanding

these techniques and when to use them is essential for effective disaster recovery.

Full Database Recovery

Full database recovery involves restoring the entire database from a backup. This is typically

used when the database server has failed completely or the database has become corrupted.

Recovery from logical backup:

Recovery from physical backup (PostgreSQL 12+):

Point-in-Time Recovery (PITR)

Point-in-Time Recovery allows you to restore a database to a specific moment in time,

rather than just to when a backup was taken. This is useful for recovering from logical

errors like accidental data deletion or corruption.

PITR process:

Restore from the most recent base backup before the target time

Configure recovery settings to replay WAL files up to the target point

Start the database, which enters recovery mode

PostgreSQL replays WAL files until it reaches the target point

After recovery completes, the database becomes available

PostgreSQL 12+ PITR example:

Recovery target options:

recovery_target_time

: Recover to a specific timestamp

recovery_target_xid

: Recover to a specific transaction ID

recovery_target_name

: Recover to a named restore point

recovery_target_lsn

: Recover to a specific WAL Log Sequence Number

recovery_target='immediate'

: Recover only until a consistent state is reached

Creating restore points:

Partial and Object-Level Recovery

In some cases, you may need to recover only specific objects (tables, schemas, or databases)

rather than the entire database. This is useful when only certain data has been lost or corrupted.

Partial recovery with pg_dump/pg_restore:

Recovering a deleted table using a standby server:

Alternatives for object-level recovery:

Use a tool like

to synchronize a database with a primary server

Set up a temporary database instance recovered to the desired point in time

Use logical replication to selectively replicate objects

For tables with slow-changing data, maintain audit logs or history tables

Specialized Recovery Scenarios

Some disaster recovery scenarios require specialized approaches beyond standard recovery procedures.

Recovering from data corruption:

Isolate and identify the corruption (logical or physical)

For physical corruption, use VACUUM FULL to rebuild tables

For logical corruption, use PITR to a point before the corruption

If only specific tables are affected, recover just those tables

Consider using tools like pg_amcheck to detect corruption

Recovering from WAL corruption:

Identify the corrupted WAL segment

If possible, restore the segment from a backup or replica

If the segment is unavailable, you may need to skip it (data loss!)

Create a recovery.conf with appropriate settings to handle the situation

Recovering from system catalog corruption:

System catalog corruption is particularly serious and often requires a full restore:

Attempt to dump user data if possible

Restore from the most recent backup

If no backup exists, create a new database and rebuild from application data

Consider using tools like pg_resetwal as a last resort

Critical Warning

Tools like pg_resetwal can cause data loss and should only be used as a last resort when there are

no viable backups and data loss is acceptable. Always consult with PostgreSQL experts before using

these tools in production.

1# Restoring from a pg_dump custom format backup
2# 1. Create a new database (if needed)
3createdb -h localhost -U postgres mydb_restored
4
5# 2. Restore from backup
6pg_restore -h localhost -U postgres -d mydb_restored mydb_backup.dump
7
8# Options for pg_restore:
9# -j [num] - Number of parallel jobs for restore
10# --clean - Drop database objects before recreating them
11# --create - Create the database before restoring
12# --exit-on-error - Abort on error
13# --single-transaction - Restore as a single transaction

1# 1. Stop PostgreSQL
2systemctl stop postgresql
3
4# 2. Remove or rename the current data directory
5mv /var/lib/postgresql/data /var/lib/postgresql/data.old
6
7# 3. Create a new empty data directory
8mkdir -p /var/lib/postgresql/data
9chown postgres:postgres /var/lib/postgresql/data
10chmod 700 /var/lib/postgresql/data
11
12# 4. Extract the base backup
13tar -xzf /backup/base/base.tar.gz -C /var/lib/postgresql/data
14chown -R postgres:postgres /var/lib/postgresql/data
15chmod 700 /var/lib/postgresql/data
16
17# 5. Create recovery configuration
18cat > /var/lib/postgresql/data/postgresql.auto.conf << EOF
19# If you need point-in-time recovery
20restore_command = 'cp /backup/archive/%f %p'
21EOF
22
23# 6. Create signal file to enter recovery mode (if needed)
24touch /var/lib/postgresql/data/recovery.signal
25
26# 7. Start PostgreSQL
27systemctl start postgresql
28
29# 8. Monitor recovery progress
30tail -f /var/log/postgresql/postgresql-12-main.log

1# 1. Restore the base backup as described above
2
3# 2. Configure point-in-time recovery
4cat > /var/lib/postgresql/data/postgresql.auto.conf << EOF
5restore_command = 'cp /backup/archive/%f %p'
6recovery_target_time = '2023-05-15 13:45:00'
7recovery_target_inclusive = true
8recovery_target_action = 'promote'
9EOF
10
11# 3. Create recovery signal file
12touch /var/lib/postgresql/data/recovery.signal
13
14# 4. Start PostgreSQL
15systemctl start postgresql
16
17# 5. Monitor recovery progress
18tail -f /var/log/postgresql/postgresql-12-main.log
19
20# PostgreSQL will:
21# - Enter recovery mode
22# - Replay WAL files until the specified time
23# - Promote itself to normal operation after reaching the target time

1-- Create a named restore point before critical operations
2SELECT pg_create_restore_point('before_schema_migration');
3
4-- Later, recover to this point using:
5-- recovery_target_name = 'before_schema_migration'

1# Restore specific tables from a backup
2pg_restore -h localhost -U postgres -d mydb --table=schema1.table1 --table=schema1.table2 mydb_backup.dump
3
4# Restore a specific schema
5pg_restore -h localhost -U postgres -d mydb --schema=schema1 mydb_backup.dump
6
7# List the contents of a backup
8pg_restore -l mydb_backup.dump > backup_contents.list
9
10# Edit backup_contents.list to include only desired objects, then:
11pg_restore -h localhost -U postgres -d mydb -L backup_contents.list mydb_backup.dump

1# 1. Set up a standby server that follows the primary but is paused at a point in time
2#    before the table was deleted (using recovery_target_time)
3
4# 2. Extract the table data from the standby
5pg_dump -h standby-server -U postgres -d mydb -t schema1.deleted_table > deleted_table.sql
6
7# 3. Restore the table to the primary
8psql -h primary-server -U postgres -d mydb -f deleted_table.sql

1-- Check for physical corruption
2SELECT pg_stat_file('base/16384/1234');  -- Replace with actual file paths
3
4-- Run VACUUM FULL to rebuild a corrupted table
5VACUUM FULL my_corrupted_table;
6
7-- Use pg_amcheck extension to check for corruption
8CREATE EXTENSION amcheck;
9SELECT bt_index_check(index => c.oid)
10FROM pg_class c, pg_index i
11WHERE c.oid = i.indexrelid;

1# In postgresql.auto.conf, to skip WAL corruption (PostgreSQL 12+)
2restore_command = 'cp /backup/archive/%f %p'
3recovery_target_time = '2023-05-15 14:00:00'  # After the corruption
4restore_command_timeout = 30  # Give up on restore after 30 seconds
5wal_skip_threshold = '64MB'   # Skip up to 64MB of WAL data if needed

High Availability Architectures

High Availability (HA) configurations ensure database accessibility even when hardware failures,

network issues, or other disruptions occur. These architectures complement backup and recovery

strategies by minimizing downtime and service interruptions.

PostgreSQL Replication

PostgreSQL replication creates and maintains copies of a database on separate servers,

enabling failover and load balancing. PostgreSQL supports several replication methods.

Physical Replication (Streaming Replication):

Streaming replication creates exact block-level copies of the entire database cluster:

Block-level replication of the entire database cluster

Low overhead on the primary server

Minimal replication lag (typically milliseconds)

Standby servers can serve read-only queries

Requires same PostgreSQL version and platform

Logical Replication:

Logical replication replicates data changes at the logical level (rows and transactions):

Row-level replication of selected tables

Supports different PostgreSQL versions

Can replicate between different platforms

Selective replication of databases, schemas, or tables

Higher overhead on the primary server

More complex to set up and manage

Synchronous vs. Asynchronous Replication:

PostgreSQL supports both synchronous and asynchronous replication modes:

Asynchronous

: Primary server doesn't wait for standby to confirm WAL application (default)

Synchronous

: Primary waits for confirmation before considering a transaction committed

Automated Failover Solutions

Automated failover systems monitor database health and automatically promote a standby server

when the primary fails. These systems typically include functionality for leader election,

health monitoring, and client redirection.

A template for high availability PostgreSQL with a focus on automated failover:

Uses DCS (etcd, Consul, Zookeeper) for leader election

REST API for cluster management

Health checking and automatic failover

Dynamic configuration management

Supports synchronous and asynchronous replication

A lightweight replication manager for PostgreSQL:

Monitors replication health

Automates standby setup and failover

Can be integrated with external monitoring

Supports cascading replication

Witness servers for improved failover decisions

Middleware that provides connection pooling, load balancing, and failover:

Connection pooling reduces connection overhead

Load balancing across multiple PostgreSQL servers

Automated failover capabilities

Query caching for improved performance

Online recovery of failed nodes

Cloud-native PostgreSQL manager for high availability:

Built for Kubernetes environments

Distributed architecture with sentinel nodes

Automatic failover and recovery

Uses a store (etcd, Consul) for cluster state

Proxy service for client connections

Example: Patroni configuration

Client Redirection Considerations

Complete failover solutions must include a mechanism for redirecting clients

to the new primary after failover. Options include:

Virtual IP (VIP)

: A floating IP address that moves with the primary role

DNS updates

: Automatically update DNS records to point to the new primary

Connection poolers

: PgBouncer or PgPool-II can direct connections to the current primary

Proxy services

: HAProxy or similar can route traffic to the current primary

Load balancers

: Cloud load balancers with health checks

Multi-Site and Geographic Distribution

Multi-site and geographically distributed PostgreSQL deployments protect against site-wide

and regional disasters. These configurations ensure business continuity even when an entire

data center or geographic region becomes unavailable.

Multi-Region Streaming Replication:

Standard streaming replication can work across regions, but with some considerations:

Higher network latency impacts performance, especially with synchronous replication

Network bandwidth constraints may limit replication throughput

Cross-region failover requires careful planning and testing

Consider using asynchronous replication across regions to minimize performance impact

Cascading Replication:

Cascading replication allows standby servers to stream data to other standby servers:

Reduces load on the primary server

Helps with geographic distribution

Allows for more complex replication topologies

Standby servers can serve read queries in their respective regions

Bi-Directional Replication:

For cases where you need write capabilities in multiple regions:

Logical replication can be configured in both directions

Multi-master solutions like BDR (Bi-Directional Replication)

Careful planning needed to handle conflicts and data integrity

Applications must be designed to handle eventual consistency

Cloud Provider Solutions:

Aurora Global Database

RDS Cross-Region Read Replicas

DMS for replication

Azure Database for PostgreSQL Hyperscale (Citus)

Read replicas across regions

Geo-redundant backups

Google Cloud

Cloud SQL cross-region replicas

AlloyDB global distribution

Managed read replicas

Disaster Recovery Planning for Multi-Site Deployments

Multi-site deployments require specific disaster recovery considerations:

Define clear policies for when to fail over to another region

Consider regulatory and compliance requirements for data residency

Implement monitoring that works across regions

Test cross-region failover regularly

Ensure backup strategies work consistently across all regions

Plan for potential network partitions between sites

Monitoring and Alerting

Effective monitoring and alerting are crucial for high availability and disaster recovery.

They help detect problems early, trigger failover when needed, and verify that recovery

processes are working correctly.

Key Metrics to Monitor:

Server health

: CPU, memory, disk space, and I/O utilization

PostgreSQL process

: Process status, uptime, and resource usage

Connection status

: Current connections, connection rate, connection errors

Replication status

: Replication lag, WAL sender/receiver status

Transaction activity

: Transactions per second, commit rate

Query performance

: Slow queries, query latency, statement statistics

Backup status

: Backup success/failure, time since last backup

Lock contention

: Lock wait times, blocked queries

Database size

: Overall size, growth rate, table and index sizes

Monitoring Tools:

PostgreSQL-Specific

pg_stat_* views

pg_stat_statements

pgAdmin monitoring

pg_activity

Open Source

Prometheus + Grafana

Nagios/Icinga

Telegraf + InfluxDB

Commercial

EnterpriseDB tools

Cloud provider monitoring

Monitoring Replication and HA Status:

Alerting Best Practices:

Define appropriate thresholds

based on your SLAs and business requirements

Implement graduated alerting

with warning and critical levels

Set up alert routing

to ensure the right people are notified

Reduce alert noise

by filtering, aggregating, and correlating alerts

Include actionable information

in alerts to guide response

Implement incident response procedures

for different types of alerts

Regularly review and refine alerting

based on experience

Critical Alerts for Disaster Recovery

These alerts should trigger immediate investigation and potential DR procedures:

Primary database server unreachable

Replication lag exceeding defined thresholds

Replication connection failures

Data corruption detected

Backup failures

Disk space critically low

Multiple transaction rollbacks or errors

Database cannot accept connections

1# Primary server configuration (postgresql.conf)
2wal_level = replica  # or logical
3max_wal_senders = 10
4max_replication_slots = 10
5wal_keep_size = 1GB  # PostgreSQL 13+ (wal_keep_segments in older versions)
6
7# Primary server authentication (pg_hba.conf)
8host replication replicator 192.168.1.0/24 md5
9
10# Standby server configuration (postgresql.conf)
11hot_standby = on
12
13# Standby server recovery configuration (PostgreSQL 12+)
14# In postgresql.auto.conf:
15primary_conninfo = 'host=primary-server port=5432 user=replicator password=password'
16restore_command = 'cp /path/to/archive/%f %p'
17
18# Create standby.signal file to enter standby mode
19touch /var/lib/postgresql/data/standby.signal

1-- On the publisher server
2-- 1. Set wal_level = logical in postgresql.conf and restart
3-- 2. Create a publication
4CREATE PUBLICATION my_publication FOR TABLE users, products, orders;
5
6-- On the subscriber server
7-- 1. Create the same tables as on the publisher
8-- 2. Create a subscription
9CREATE SUBSCRIPTION my_subscription
10  CONNECTION 'host=publisher-server port=5432 dbname=mydb user=replicator password=password'
11  PUBLICATION my_publication;
12
13-- Check replication status
14SELECT * FROM pg_stat_subscription;

1# Synchronous replication configuration (postgresql.conf)
2synchronous_standby_names = 'FIRST 1 (standby1, standby2)'
3# This means: wait for acknowledgment from the first standby in the list
4
5# Other options:
6synchronous_standby_names = 'ANY 1 (standby1, standby2)'
7# This means: wait for acknowledgment from any one standby
8
9synchronous_commit = on  # Default, can be set to 'remote_apply' for stronger guarantee

1# patroni.yml
2scope: postgres-cluster
3namespace: /postgres-cluster/
4name: node1
5
6restapi:
7  listen: 0.0.0.0:8008
8  connect_address: 192.168.1.101:8008
9
10etcd:
11  host: 192.168.1.110:2379
12
13bootstrap:
14  dcs:
15    ttl: 30
16    loop_wait: 10
17    retry_timeout: 10
18    maximum_lag_on_failover: 1048576
19    postgresql:
20      use_pg_rewind: true
21      parameters:
22        wal_level: replica
23        hot_standby: "on"
24        wal_keep_segments: 100
25        max_wal_senders: 10
26        max_replication_slots: 10
27        wal_log_hints: "on"
28
29postgresql:
30  listen: 0.0.0.0:5432
31  connect_address: 192.168.1.101:5432
32  data_dir: /var/lib/postgresql/data
33  bin_dir: /usr/lib/postgresql/14/bin
34  pgpass: /tmp/.pgpass
35  authentication:
36    replication:
37      username: replicator
38      password: rep-pass
39    superuser:
40      username: postgres
41      password: admin-pass
42
43tags:
44  nofailover: false
45  noloadbalance: false
46  clonefrom: false
47  nosync: false

1# Configure a standby to act as a cascading server (postgresql.conf)
2hot_standby = on
3max_wal_senders = 10
4wal_keep_size = 1GB
5
6# On the third server, connect to the standby rather than the primary
7# In postgresql.auto.conf:
8primary_conninfo = 'host=standby-server port=5432 user=replicator password=password'

1-- Check replication status on the primary
2SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
3       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
4FROM pg_stat_replication;
5
6-- Check replication status on a standby
7SELECT received_lsn, replay_lsn,
8       pg_wal_lsn_diff(received_lsn, replay_lsn) AS replay_lag_bytes,
9       pg_last_wal_receive_lsn(),
10       pg_last_wal_replay_lsn(),
11       pg_last_xact_replay_timestamp()
12FROM pg_stat_wal_receiver;
13
14-- Calculate replication lag in seconds (on standby)
15SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds;

Disaster Recovery Planning

Comprehensive disaster recovery planning integrates technical solutions with business processes to

ensure continuity of operations during and after a disaster event.

Disaster Recovery Documentation

Create and maintain comprehensive disaster recovery documentation:

Disaster Recovery Plan (DRP)

: Detailed procedures for different disaster scenarios

Contact Information

: Key personnel and their roles during recovery

: Database servers, applications, dependencies, and configurations

Recovery Procedures

: Step-by-step instructions for recovery operations

Testing Schedule

: Regular testing plans and results

SLAs and RTOs

: Recovery time objectives and service level agreements

Ensure documentation is:

Accessible during disasters (including offline copies)

Regularly reviewed and updated

Clear and understandable by staff under pressure

Version-controlled and with change history

Recovery Objectives

Define clear, measurable recovery objectives based on business requirements:

Recovery Time Objective (RTO)

: Maximum acceptable downtime

Recovery Point Objective (RPO)

: Maximum acceptable data loss

Recovery Consistency Objective (RCO)

: Required consistency across related systems

Service Level Agreements (SLAs)

: Promised performance and availability metrics

Align technical solutions with business requirements:

Business Criticality

Typical RTO

Typical RPO

Suggested Solution

Seconds - Minutes

Multi-region HA with synchronous replication

Automated failover with asynchronous replication

Standby server with continuous archiving

Regular backups with documented recovery

DR Testing

Implement a regular testing program to validate your disaster recovery capabilities:

Regular testing schedule

with increasing scope and complexity

Documentation of test results

and lessons learned

Improvement plans

based on test outcomes

Different test types

from component testing to full simulation

Testing approaches by increasing complexity:

Paper tests

: Review and walk through recovery procedures

Component tests

: Test specific recovery components like backup restore

Simulation tests

: Simulate failures in non-production environments

Parallel tests

: Recover to a separate environment without affecting production

Full interruption tests

: Controlled production failover exercises

Database-Specific Considerations

Address these PostgreSQL-specific considerations in your business continuity planning:

Connection management

: How applications reconnect after failover

Replication strategy

: Synchronous vs. asynchronous trade-offs

Data consistency

: Acceptable consistency levels for different workloads

PostgreSQL version compatibility

: Ensuring version compatibility in recovery

Extension and configuration consistency

: Maintaining consistent settings

Monitoring and alerting

: Database-specific monitoring solutions

Security credentials

: Managing and securing database credentials

DR Plan Maintenance

Disaster recovery plans should be living documents that evolve with your environment. Establish a

regular review cycle and update the plan when:

New applications are deployed or existing ones are retired

Database architecture or infrastructure changes

Business requirements or SLAs change

Staff roles and responsibilities change

Test results indicate needed improvements

New recovery technologies or methods become available

Regulations or compliance requirements change

Implementing a comprehensive database disaster recovery strategy is essential for ensuring business

continuity and minimizing the impact of unexpected events. By combining robust backup strategies,

well-defined recovery procedures, high availability configurations, and thorough business continuity

planning, you can protect your PostgreSQL databases against various failure scenarios.

Remember that disaster recovery is not just about technical solutions but also about people and

processes. Regular testing, clear documentation, and staff training are just as important as the

technical implementation. By taking a holistic approach to disaster recovery, you can ensure that

your organization is prepared to respond effectively to database disasters and minimize their impact.

Additional Resources

PostgreSQL Documentation - High Availability and Replication

Official documentation on backup, recovery, and replication

PostgreSQL Backup and Recovery Best Practices

Detailed guide to implementing backup and recovery

Business Continuity Institute Resources

General business continuity planning guidance

PostgreSQL Replication Tools Documentation

Documentation for Patroni, Repmgr, and other replication tools

Ready to Practice?

Head to the interactive lab to practice implementing disaster recovery solutions with hands-on exercises.

Open Interactive Lab