xDev Asia

Backup and disaster recovery strategies for healthcare data: PostgreSQL backup strategies (pg_dump, pg_basebackup, Barman, pgBackRest), encrypted backups, point-in-time recovery (PITR), cross-region replication, RTO/RPO requirements for healthcare, DR testing procedures, business continuity planning, and automated failover with Patroni.

Building a Microservices Healthcare System — Quarkus, PostgreSQL, Keycloak with HIPAA standards

Part 5: Compliance, Audit & Data Protection

xdev.asia

1. Overview of Disaster Recovery for Healthcare

Disaster Recovery & Business Continuity — RPO/RTO for health systems

HIPAA Security Rule §164.308(a)(7) requires every organization that handles ePHI to have a Contingency Plan — a contingency plan for emergency situations (natural disasters, ransomware attacks, hardware failures, human error). In healthcare, downtime can directly affect patient lives.

1.1. HIPAA Contingency Plan Requirements

HIPAA Contingency Plan — §164.308(a)(7):

Data Backup Plan [Required]
- Create and maintain exact copies of ePHI
- Regular backup schedule
- Backup verification
Disaster Recovery Plan [Required]
- Procedures to restore lost data
- Restore from backups
- Test recovery procedures
Emergency Mode Operation Plan [Required]
- Continue critical operations during emergency
- Protect ePHI during emergency
- Prioritize patient safety systems
Testing and Revision Procedures [Addressable]
- Periodic testing of contingency plans
- Update plans based on test results
Applications and Data Criticality Analysis [Addressable]
- Identify critical applications
- Prioritize recovery orders

1.2. RTO/RPO Requirements for Healthcare

RTO/RPO Timeline cho Healthcare Systems

System	RPO	RTO	Criticality
EHR (Medical Record)	0-1min	<15min	Critical
Lab Results	<5min	<30min	Critical
Pharmacy	<5min	<30min	Critical
Patient Portal	<1hr	<4hr	Important
Billing	<4hr	<8hr	Standard
Analytics	<24hr	<24hr	Low
Audit Logs	0	<1hr	Critical (HIPAA)

Timeline: ◄── RPO (Data loss window) ──┤ Disaster occurs ├── RTO (Downtime window) ──►

Target cho Critical Healthcare: RPO ≈ 0 (synchronous replication), RTO < 15 minutes (automated failover)

2. PostgreSQL Backup Strategies

2.1. Compare Backup Methods

Method	Type	RPO	Speed	Size	Complexity	Use Case
`pg_dump`	Logical	Hours	Slow	Small (compressed)	Low	Small DB, schema migration
`pg_basebackup`	Physical	Minutes	Medium	Full DB size	Medium	Standbys, PITR
pgBackRest	Physical	Seconds	Fast	Incremental	Medium	Production healthcare
Barman	Physical	Seconds	Fast	Incremental	High	Large enterprise
WAL archiving	Continuous	Seconds	Minimal	WAL files	Low	PITR supplement
Streaming replication	Continuous	~0	Real-time	Full replica	Medium	HA / Failover

2.2. pg_dump — Logical Backup

#!/bin/bash
# pg-logical-backup.sh
# Logical backup cho healthcare database

set -euo pipefail

DB_NAME="healthcare"
DB_USER="backup_user"
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"
ENCRYPTION_KEY_FILE="/etc/healthcare/backup-encryption-key"

# Backup
echo "[$(date)] Starting logical backup: ${DB_NAME}"

pg_dump \
    --host=localhost \
    --port=5432 \
    --username="${DB_USER}" \
    --format=custom \
    --compress=9 \
    --verbose \
    --file="${BACKUP_FILE}" \
    "${DB_NAME}"

# Encrypt backup (HIPAA requirement)
echo "[$(date)] Encrypting backup..."
openssl enc -aes-256-cbc -salt \
    -in "${BACKUP_FILE}" \
    -out "${BACKUP_FILE}.enc" \
    -pass file:"${ENCRYPTION_KEY_FILE}" \
    -pbkdf2 -iter 100000

# Remove unencrypted backup
rm -f "${BACKUP_FILE}"

# Verify backup integrity
echo "[$(date)] Verifying backup..."
VERIFY_FILE=$(mktemp)
openssl enc -d -aes-256-cbc \
    -in "${BACKUP_FILE}.enc" \
    -out "${VERIFY_FILE}" \
    -pass file:"${ENCRYPTION_KEY_FILE}" \
    -pbkdf2 -iter 100000

pg_restore --list "${VERIFY_FILE}" > /dev/null 2>&1
VERIFY_STATUS=$?
rm -f "${VERIFY_FILE}"

if [ ${VERIFY_STATUS} -eq 0 ]; then
    echo "[$(date)] Backup verification: PASSED"
    # Calculate checksum
    SHA256=$(sha256sum "${BACKUP_FILE}.enc" | awk '{print $1}')
    echo "[$(date)] SHA256: ${SHA256}"

    # Log to audit
    echo "{\"event\":\"backup_completed\",\"type\":\"logical\",\"database\":\"${DB_NAME}\",\"file\":\"${BACKUP_FILE}.enc\",\"sha256\":\"${SHA256}\",\"timestamp\":\"$(date -u +"%Y-%m-%dT%H:%M:%SZ")\"}" \
        | logger -t healthcare-backup
else. else
    echo "[$(date)] Backup verification: FAILED"
    exit 1
fi

# Retention: keep 30 days of daily backups
echo "[$(date)] Cleaning old backups (>30 days)..."
find "${BACKUP_DIR}" -name "*.dump.enc" -mtime +30 -delete

echo "[$(date)] Backup complete: ${BACKUP_FILE}.enc"

2.3. WAL Archiving Configuration

# postgresql.conf - WAL Archiving for PITR

# === WAL Configuration ===
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

# === Archive Mode ===
archive_mode = on
archive_command = 'pgbackrest --stanza=healthcare archive-push %p'
archive_timeout = 60

# === Checkpoint tuning ===
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

3. pgBackRest — Production Backup Solution

3.1. Why pgBackRest for Healthcare?

pgBackRest is a backup solution that is recommended for production PostgreSQL because:

Incremental backup: Only backs up changes since the previous backup → fast, saves storage
Parallel backup/restore: Uses multiple CPU cores → reduces time
Built-in encryption: AES-256-CBC encryption → HIPAA compliant
Backup verification: Automatically verify backup integrity
PITR: Point-in-time recovery from any point in time
Remote backup: Backup to remote server or object storage (S3, GCS)

3.2. pgBackRest Configuration

# /etc/pgbackrest/pgbackrest.conf

[global]
# === Repository Configuration ===
repo1-type=s3
repo1-path=/healthcare-backups
repo1-s3-bucket=hospital-db-backups
repo1-s3-endpoint=s3.ap-southeast-1.amazonaws.com
repo1-s3-region=ap-southeast-1
repo1-s3-key=AKIAIOSFODNN7EXAMPLE
repo1-s3-key-secret=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

# === Encryption (HIPAA Required) ===
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=STRONG_ENCRYPTION_PASSPHRASE_HERE

# === Retention Policy ===
repo1-retention-full=4
repo1-retention-diff=14
repo1-retention-archive=2
repo1-retention-archive-type=full

# === Performance ===
process-max=4
compress-type=zst
compress-level=6
delta=y

# === Logging ===
log-level-console=info
log-level-file=detail
log-path=/var/log/pgbackrest

[healthcare]
# === Stanza Configuration ===
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432
pg1-user=postgres

# === Second Repository (DR site) ===
# repo2-type=s3
# repo2-path=/healthcare-backups-dr
# repo2-s3-bucket=hospital-db-backups-dr
# repo2-s3-endpoint=s3.ap-northeast-1.amazonaws.com
# repo2-s3-region=ap-northeast-1
# repo2-cipher-type=aes-256-cbc
# repo2-cipher-pass=DR_ENCRYPTION_PASSPHRASE

3.3. Backup Schedule

#!/bin/bash
# pgbackrest-schedule.sh
# Automated backup schedule for healthcare

set -euo pipefail

STANZA="healthcare"
LOG_FILE="/var/log/pgbackrest/schedule.log"

log() {
    echo "[$(date -u +"%Y-%m-%dT%H:%M:%SZ")] $1" | tee -a "$LOG_FILE"
}

case "${1:-}" in
    full)
        # Full backup — runs weekly (Sunday 2:00 AM)
        log "Starting FULL backup..."
        pgbackrest --stanza="${STANZA}" --type=full backup
        log "Full backup completed"
        ;;

    diff)
        # Differential backup — runs daily (2:00 AM)
        log "Starting DIFFERENTIAL backup..."
        pgbackrest --stanza="${STANZA}" --type=diff backup
        log "Differential backup completed"
        ;;

    incr)
        # Incremental backup — runs every hour
        log "Starting INCREMENTAL backup..."
        pgbackrest --stanza="${STANZA}" --type=incr backup
        log "Incremental backup completed"
        ;;

    verify)
        # Verify backup integrity
        log "Starting backup VERIFICATION..."
        pgbackrest --stanza="${STANZA}" verify
        log "Verification completed"
        ;;

    info)
        pgbackrest --stanza="${STANZA}" info
        ;;

    *)
        echo "Usage: $0 {full|diff|incr|verify|info}"
        exit 1
        ;;
esac

# Log backup information for audit trail
BACKUP_INFO=$(pgbackrest --stanza="${STANZA}" --output=json info 2>/dev/null)
echo "{\"event\":\"backup_schedule\",\"type\":\"${1:-unknown}\",\"stanza\":\"${STANZA}\",\"timestamp\":\"$(date -u +"%Y-%m-%dT%H:%M:%SZ")\"}" \
    | logger -t healthcare-backup

Crontab Configuration:

# /etc/cron.d/pgbackrest-healthcare

# Incremental backup every hour
0 * * * * postgres /opt/scripts/pgbackrest-schedule.sh incr

# Differential backup daily 2:00 AM
0 2 * * 1-6 postgres /opt/scripts/pgbackrest-schedule.sh diff

# Full backup Sunday 2:00 AM
0 2 * * 0 postgres /opt/scripts/pgbackrest-schedule.sh full

# Verify backup daily 6:00 AM
0 6 * * * postgres /opt/scripts/pgbackrest-schedule.sh verify

3.4. Point-in-Time Recovery (PITR)

#!/bin/bash
# pitr-restore.sh
# Point-in-Time Recovery for healthcare database

set -euo pipefail

STANZA="healthcare"
TARGET_TIME="${1:-}"
PG_DATA="/var/lib/postgresql/16/main"

if [ -z "$TARGET_TIME" ]; then
    echo "Usage: $0 <target_time>"
    echo "Example: $0 '2024-03-15 14:30:00+07'"
    echo ""
    echo "Available backups:"
    pgbackrest --stanza="${STANZA}" info
    exit 1
fi

echo "=== POINT-IN-TIME RECOVERY ==="
echo "Target time: ${TARGET_TIME}"
echo "Stanza: ${STANZA}"
echo ""
echo "WARNING: This will REPLACE the current database!"
read -p "Are you sure? (yes/no): "CONFIRM

if [ "$CONFIRM" != "yes" ]; then
    echo "Aborted."
    exit 0
fi

#1. Stop PostgreSQL
echo "[$(date)] Stopping PostgreSQL..."
systemctl stop postgresql

#2. Perform PITR restore
echo "[$(date)] Starting PITR restore to: ${TARGET_TIME}"
pgbackrest --stanza="${STANZA}" \
    --type=time \
    --target="${TARGET_TIME}" \
    --target-action=promote \
    --delta \
    restore. restore

#3. Start PostgreSQL
echo "[$(date)] Starting PostgreSQL..."
systemctl start postgresql

# 4. Verify database
echo "[$(date)] Verifying database..."
sleep 5
pg_isready -h localhost -p 5432
if [ $? -eq 0 ]; then
    echo "[$(date)] PostgreSQL is ready"

    # Verify data
    PATIENT_COUNT=$(psql -d healthcare -tAc "SELECT COUNT(*) FROM healthcare.patients" 2>/dev/null)
    echo "[$(date)]Patient records: ${PATIENT_COUNT}"

    # Log recovery events
    echo "{\"event\":\"pitr_restore\",\"target_time\":\"${TARGET_TIME}\",\"patient_count\":${PATIENT_COUNT},\"timestamp\":\"$(date -u +"%Y-%m-%dT%H:%M:%SZ")\"}" \
        | logger -t healthcare-backup
else. else
    echo "[$(date)] ERROR: PostgreSQL failed to start after restore"
    exit 1
fi

echo "[$(date)] PITR restore completed successfully"

4. Streaming Replication & Patroni

4.1. High Availability Architecture

┌────────────────────────────── ───────────────────────────────┐
│ PostgreSQL HA with Patroni │
│ │
│ ┌──────────────────────────── ────────────────────────────┐ │
│ │ HAProxy (VIP) │ │
│ │ Read/Write ──► Port 5432 │ │
│ │ Read-Only ──► Port 5433 │ │
│ └──────────┬───────────────── ───┬────────────────────────┘ │
│ │ │ │
│ ┌────────▼─────────┐ ┌───────▼──────────┐ │
│ │ PostgreSQL │ │ PostgreSQL │ │
│ │ PRIMARY │ │ REPLICA │ │
│ │ (Read/Write) │ │ (Read-Only) │ │
│ │ │ │ │ │
│ │ Patroni Agent │ │ Patroni Agent │ │
│ │ pg1:5432 │ │ pg2:5432 │ │
│ └────────┬──────────┘ └──────┬────────────┘ │
│ │ │ │
│ └───────┬───────────┘ │
│ │ │
│ ┌────────▼──────────┐ │
│ │ etcd Cluster │ │
│ │ (DCS - Consensus)│ │
│ │ etcd1, etcd2, │ │
│ │ etcd3 │ │
│ └───────────────────┘ │
│ │
│ Failover Flow: │
│ 1. Primary fails │
│ 2. Patroni detects via health check │
│ 3. etcd consensus: elect new primary │
│ 4. Patroni promotes replica to primary │
│ 5. HAProxy routes traffic to new primary │
│ 6. RTO < 30 seconds (automated)                             │
└─────────────────────────────────────────────────────────────┘

4.2. Patroni Configuration

# patroni.yml - Primary node

scope: healthcare-cluster
namespace: /healthcare/
name: pg1

restapi:
  listen: 0.0.0.0:8008
  connect_address: pg1:8008
  authentication:
    username: admin
    password: ${PATRONI_REST_PASSWORD}

etcd3:
  hosts:
    - etcd1:2379
    - etcd2:2379
    - etcd3:2379
  username: patroni
  password: ${ETCD_PASSWORD}

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576  # 1MB WAL lag max
    synchronous_mode: true  # Synchronous replication (RPO = 0)
    synchronous_mode_strict: true
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 2GB
        hot_standby: "on"
        synchronous_commit: "on"  # RPO = 0 cho healthcare
        synchronous_standby_names: "*"
        # Audit logging
        log_statement: all
        log_connections: "on"
        log_disconnections: "on"
        log_line_prefix: '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

  initdb:
    - encoding: UTF8
    - data-checksums
    - locale: en_US.UTF-8

  pg_hba:
    - local all postgres peer
    - host replication replicator 10.0.0.0/8 scram-sha-256
    - host all all 10.0.0.0/8 scram-sha-256
    - host all all 0.0.0.0/0 reject

postgresql:
  listen: 0.0.0.0:5432
  connect_address: pg1:5432
  data_dir: /var/lib/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin

  authentication:
    superuser:
      username: postgres
      password: ${PG_SUPERUSER_PASSWORD}
    replication:
      username: replicator
      password: ${PG_REPLICATION_PASSWORD}

  parameters:
    # Performance
    shared_buffers: 4GB
    effective_cache_size: 12GB
    work_mem: 64MB
    maintenance_work_mem: 512MB

    # WAL
    wal_level: replica
    max_wal_senders: 10
    max_replication_slots: 10
    synchronous_commit: "on"

    # Archive
    archive_mode: "on"
    archive_command: "pgbackrest --stanza=healthcare archive-push %p"

    # SSL (HIPAA)
    ssl: "on"
    ssl_cert_file: /etc/ssl/certs/pg-server.crt
    ssl_key_file: /etc/ssl/private/pg-server.key
    ssl_ca_file: /etc/ssl/certs/ca.crt

  pgpass: /tmp/pgpass

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

4.3. HAProxy Configuration

# haproxy.cfg - Load balancing cho PostgreSQL

global
    maxconn 1000
    log stdout local0

defaults
    mode tcp
    log global
    retries 3
    timeout connect 10s
    timeout client 30m
    timeout server 30m
    timeout check 5s

# Primary (Read/Write)
listen healthcare-primary
    bind *:5432
    option httpchk OPTIONS /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 pg1:5432 maxconn 500 check port 8008
    server pg2 pg2:5432 maxconn 500 check port 8008

# Replicas (Read-Only)
listen healthcare-replica
    bind *:5433
    option httpchk OPTIONS /replica
    http-check expect status 200
    balance roundrobin
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 pg1:5432 maxconn 300 check port 8008
    server pg2 pg2:5432 maxconn 300 check port 8008

# Patroni REST API
listen patroni-api
    bind *:8008
    mode http
    option httpchk GET /health
    default-server inter 5s fall 3 rise 2
    server pg1 pg1:8008 check
    server pg2 pg2:8008 check

# Stats
listen stats
    bind *:7000
    mode http
    stats enable
    stats uri /
    stats refresh 10s

4.4. Docker Compose cho HA Stack

# docker-compose-ha.yml
version: '3.8'

services:
  # === etcd Cluster ===
  etcd1:
    image: quay.io/coreos/etcd:v3.5.12
    container_name: etcd1
    environment:
      ETCD_NAME: etcd1
      ETCD_INITIAL_ADVERTISE_PEER_URLS: http://etcd1:2380
      ETCD_LISTEN_PEER_URLS: http://0.0.0.0:2380
      ETCD_LISTEN_CLIENT_URLS: http://0.0.0.0:2379
      ETCD_ADVERTISE_CLIENT_URLS: http://etcd1:2379
      ETCD_INITIAL_CLUSTER: etcd1=http://etcd1:2380,etcd2=http://etcd2:2380,etcd3=http://etcd3:2380
      ETCD_INITIAL_CLUSTER_STATE: new
    volumes:
      - etcd1_data:/etcd-data

  etcd2:
    image: quay.io/coreos/etcd:v3.5.12
    container_name: etcd2
    environment:
      ETCD_NAME: etcd2
      ETCD_INITIAL_ADVERTISE_PEER_URLS: http://etcd2:2380
      ETCD_LISTEN_PEER_URLS: http://0.0.0.0:2380
      ETCD_LISTEN_CLIENT_URLS: http://0.0.0.0:2379
      ETCD_ADVERTISE_CLIENT_URLS: http://etcd2:2379
      ETCD_INITIAL_CLUSTER: etcd1=http://etcd1:2380,etcd2=http://etcd2:2380,etcd3=http://etcd3:2380
      ETCD_INITIAL_CLUSTER_STATE: new
    volumes:
      - etcd2_data:/etcd-data

  etcd3:
    image: quay.io/coreos/etcd:v3.5.12
    container_name: etcd3
    environment:
      ETCD_NAME: etcd3
      ETCD_INITIAL_ADVERTISE_PEER_URLS: http://etcd3:2380
      ETCD_LISTEN_PEER_URLS: http://0.0.0.0:2380
      ETCD_LISTEN_CLIENT_URLS: http://0.0.0.0:2379
      ETCD_ADVERTISE_CLIENT_URLS: http://etcd3:2379
      ETCD_INITIAL_CLUSTER: etcd1=http://etcd1:2380,etcd2=http://etcd2:2380,etcd3=http://etcd3:2380
      ETCD_INITIAL_CLUSTER_STATE: new
    volumes:
      - etcd3_data:/etcd-data

  # === PostgreSQL + Patroni ===
  pg1:
    image: postgres:16-bookworm
    container_name: pg1
    environment:
      PATRONI_SCOPE: healthcare-cluster
      PATRONI_NAME: pg1
      PATRONI_POSTGRESQL_DATA_DIR: /var/lib/postgresql/data
      PATRONI_POSTGRESQL_CONNECT_ADDRESS: pg1:5432
      PATRONI_RESTAPI_CONNECT_ADDRESS: pg1:8008
      PATRONI_ETCD3_HOSTS: "'etcd1:2379','etcd2:2379','etcd3:2379'"
      PG_SUPERUSER_PASSWORD: ${PG_SUPERUSER_PASSWORD}
      PG_REPLICATION_PASSWORD: ${PG_REPLICATION_PASSWORD}
    volumes:
      - pg1_data:/var/lib/postgresql/data
      - ./config/patroni.yml:/etc/patroni.yml
    ports:
      - "5432"
    depends_on:
      - etcd1
      - etcd2
      - etcd3

  pg2:
    image: postgres:16-bookworm
    container_name: pg2
    environment:
      PATRONI_SCOPE: healthcare-cluster
      PATRONI_NAME: pg2
      PATRONI_POSTGRESQL_DATA_DIR: /var/lib/postgresql/data
      PATRONI_POSTGRESQL_CONNECT_ADDRESS: pg2:5432
      PATRONI_RESTAPI_CONNECT_ADDRESS: pg2:8008
      PATRONI_ETCD3_HOSTS: "'etcd1:2379','etcd2:2379','etcd3:2379'"
      PG_SUPERUSER_PASSWORD: ${PG_SUPERUSER_PASSWORD}
      PG_REPLICATION_PASSWORD: ${PG_REPLICATION_PASSWORD}
    volumes:
      - pg2_data:/var/lib/postgresql/data
      - ./config/patroni.yml:/etc/patroni.yml
    ports:
      - "5432"
    depends_on:
      - etcd1
      - etcd2
      - etcd3

  # === HAProxy ===
  haproxy:
    image: haproxy:2.9
    container_name: haproxy
    volumes:
      - ./config/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro
    ports:
      - "5432:5432"   # Primary (R/W)
      - "5433:5433"   # Replica (R/O)
      - "7000:7000"   # Stats
    depends_on:
      - pg1
      - pg2

volumes:
  etcd1_data:
  etcd2_data:
  etcd3_data:
  pg1_data:
  pg2_data:

5. Cross-Region Replication cho DR

5.1. Cross-Region Architecture

Cross-Region DR Architecture — Singapore (Primary) → Tokyo (DR)

PRIMARY REGION (ap-southeast-1: Singapore):

pg-primary (R/W) → pg-replica (R/O) — sync replication
WAL streaming (async) to DR region

DR REGION (ap-northeast-1: Tokyo):

pg-dr (async replica, read-only) — Lag: < 1 minute
pgBackRest S3 backup (encrypted)
Standby microservices (cold/warm)

Failover: DNS switch + Patroni promote DR — RPO < 1 minute, RTO < 15 minutes (automated)

5.2. DR Replication Setup

#!/bin/bash
# setup-dr-replica.sh
# Setup cross-region DR replica

set -euo pipefail

PRIMARY_HOST="pg-primary.ap-southeast-1.hospital.internal"
DR_HOST="pg-dr.ap-northeast-1.hospital.internal"
REPLICATION_USER="replicator"
PG_DATA="/var/lib/postgresql/16/main"

echo "=== Setting up DR Replica ==="
echo "Primary: ${PRIMARY_HOST}"
echo "DR: ${DR_HOST}"

# 1. Stop PostgreSQL on DR node
systemctl stop postgresql

# 2. Backup existing data
if [ -d "${PG_DATA}" ]; then
    mv "${PG_DATA}" "${PG_DATA}.backup.$(date +%Y%m%d)"
fi

# 3. Base backup from primary
pg_basebackup \
    --host="${PRIMARY_HOST}" \
    --port=5432 \
    --username="${REPLICATION_USER}" \
    --pgdata="${PG_DATA}" \
    --wal-method=stream \
    --checkpoint=fast \
    --progress \
    --verbose

# 4. Configure as standby
cat > "${PG_DATA}/postgresql.auto.conf" << EOF
# DR Replica Configuration
primary_conninfo = 'host=${PRIMARY_HOST} port=5432 user=${REPLICATION_USER} password=${PG_REPLICATION_PASSWORD} sslmode=verify-full sslcert=/etc/ssl/certs/pg-client.crt sslkey=/etc/ssl/private/pg-client.key sslrootcert=/etc/ssl/certs/ca.crt application_name=dr-replica'
primary_slot_name = 'dr_replica'
recovery_target_timeline = 'latest'
restore_command = 'pgbackrest --stanza=healthcare archive-get %f "%p"'

# Performance
max_standby_streaming_delay = 30s
max_standby_archive_delay = 60s
hot_standby = on
hot_standby_feedback = on
EOF

# 5. Create standby signal
touch "${PG_DATA}/standby.signal"

# 6. Fix permissions
chown -R postgres:postgres "${PG_DATA}"
chmod 700 "${PG_DATA}"

# 7. Start PostgreSQL
systemctl start postgresql

# 8. Verify replication
sleep 5
REPLAY_LAG=$(psql -h localhost -tAc \
    "SELECT EXTRACT(EPOCH FROM replay_lag) FROM pg_stat_wal_receiver" 2>/dev/null)
echo "Replication lag: ${REPLAY_LAG:-N/A} seconds"

echo "==== DR Replica setup complete ==="

6. Microservices State Recovery

6.1. Recovery Scope

Component	State	Recovery
PostgreSQL	Patient data (PHI)	PITR/Replica
Kafka	Consumer offsets	Reset offset
Kafka	Topic data	Replay logs
Keycloak	Realm, users, roles	Realm export
HashiCorp Vault	Encryption keys	Snapshot
Elasticsearch	Audit logs	Snapshot
Application Config	ConfigMaps, Secrets	Git + Velero
Container Images	Docker images	Registry
Certificates	TLS certs	cert-manager

6.2. Kafka Consumer Offset Recovery

#!/bin/bash
# kafka-recovery.sh
# Recovery Kafka consumer offsets and topics

set -euo pipefail

KAFKA_BOOTSTRAP="kafka.hospital.internal:9093"
GROUP_ID="patient-service"

echo "=== Kafka Recovery ==="

# Option 1: Reset to latest (skip missed messages)
echo "Resetting consumer group to latest..."
kafka-consumer-groups.sh\
    --bootstrap-server "${KAFKA_BOOTSTRAP}" \
    --group "${GROUP_ID}" \
    --reset-offsets \
    --to-latest \
    --all-topics \
    --execute \
    --command-config /etc/kafka/client.properties

# Option 2: Reset to specific timestamp
# kafka-consumer-groups.sh\
# --bootstrap-server "${KAFKA_BOOTSTRAP}" \
# --group "${GROUP_ID}" \
# --reset-offsets \
# --to-datetime "2024-03-15T00:00:00.000" \
# --all-topics \
# --execute \
# --command-config /etc/kafka/client.properties

# Option 3: Reset to specific offset
# kafka-consumer-groups.sh\
# --bootstrap-server "${KAFKA_BOOTSTRAP}" \
# --group "${GROUP_ID}" \
# --reset-offsets \
# --to-offset 12345 \
# --topic healthcare.patient-events \
# --execute \
# --command-config /etc/kafka/client.properties

echo "Current consumer group status:"
kafka-consumer-groups.sh\
    --bootstrap-server "${KAFKA_BOOTSTRAP}" \
    --group "${GROUP_ID}" \
    --describe \
    --command-config /etc/kafka/client.properties

6.3. Keycloak Realm Backup & Restore

#!/bin/bash
# keycloak-backup.sh
# Backup Keycloak realm configuration

set -euo pipefail

KC_URL="${KEYCLOAK_URL:-http://keycloak:8080}"
REALM="healthcare"
BACKUP_DIR="/var/backups/keycloak"
DATE=$(date +%Y%m%d_%H%M%S)

# Get admin token
TOKEN=$(curl -s -X POST "${KC_URL}/realms/master/protocol/openid-connect/token" \
    -d "client_id=admin-cli" \
    -d "username=admin" \
    -d "password=${KC_ADMIN_PASSWORD}" \
    -d "grant_type=password" | jq -r '.access_token')

# Export realm (includes users, roles, clients, groups)
echo "Exporting realm: ${REALM}"
curl -s -H "Authorization: Bearer ${TOKEN}" \
    "${KC_URL}/admin/realms/${REALM}" \
    -o "${BACKUP_DIR}/realm-${REALM}-${DATE}.json"

# Export users separately (with credentials for full restore)
curl -s -H "Authorization: Bearer ${TOKEN}" \
    "${KC_URL}/admin/realms/${REALM}/users?max=10000" \
    -o "${BACKUP_DIR}/users-${REALM}-${DATE}.json"

# Encrypt backup
openssl enc -aes-256-cbc -salt \
    -in "${BACKUP_DIR}/realm-${REALM}-${DATE}.json" \
    -out "${BACKUP_DIR}/realm-${REALM}-${DATE}.json.enc" \
    -pass file:/etc/healthcare/backup-encryption-key\
    -pbkdf2 -iter 100000

rm "${BACKUP_DIR}/realm-${REALM}-${DATE}.json"

echo "Keycloak realm backup: ${BACKUP_DIR}/realm-${REALM}-${DATE}.json.enc"

7. Kubernetes DR with Velero

7.1. Velero Configuration

# velero-install.yaml
apiVersion: v1
kind: Namespace
metadata:
  name: velero
---
# Velero installation values
# helm install velero vmware-tanzu/velero -f velero-values.yaml -n velero
configuration:
  backupStorageLocation:
    - name: default
      provider: aws
      bucket: hospital-k8s-backups
      config:
        region: ap-southeast-1
        s3ForcePathStyle: true
  volumeSnapshotLocation:
    - name: default
      provider: aws
      config:
        region: ap-southeast-1

credentials:
  useSecret: true
  secretContents:
    cloud: |
      [default]
      aws_access_key_id=AKIAIOSFODNN7EXAMPLE
      aws_secret_access_key=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

initialContainers:
  - name: velero-plugin-for-aws
    image: velero/velero-plugin-for-aws:v1.9.0
    volumeMounts:
      - name: plugins
        mountPath: /target

defaultVolumesToFsBackup: true

schedules:
  healthcare-daily:
    schedule: "0 3 * * *"
    templates:
      ttl: "720h0m0s" # 30 days
      includedNamespaces:
        - healthcare
      labelSelector:
        matchLabels:
          backup: "true"
      snapshotVolumes: true
      storageLocation: default

7.2. Velero Backup & Restore Commands

#!/bin/bash
#velero-healthcare.sh
# Kubernetes DR operations for healthcare

case "${1:-}" in
    backup)
        echo "Creating healthcare namespace backup..."
        velero backup create healthcare-$(date +%Y%m%d-%H%M) \
            --include-namespaces healthcare\
            --snapshot-volumes \
            --default-volumes-to-fs-backup \
            --wait
        ;;

    restore)
        BACKUP_NAME="${2:-}"
        if [ -z "$BACKUP_NAME" ]; then
            echo "Available backups:"
            velero backup get | grep healthcare
            exit 1
        fi

        echo "Restoring from backup: ${BACKUP_NAME}"
        velero restore create \
            --from-backup "${BACKUP_NAME}" \
            --include-namespaces healthcare\
            --restore-volumes \
            --wait
        ;;

    list)
        echo "==== Backups ==="
        velero backup get | grep healthcare
        echo ""
        echo "=== Restores ==="
        velero restore get | grep healthcare
        ;;

    *)
        echo "Usage: $0 {backup|restore <backup-name>|list}"
        ;;
esac

8. DR Testing & Validation

8.1. DR Test Runbook

Healthcare DR Test Runbook:

Pre-Test (T-1 week):

Notify all stakeholders
Verify backup integrity (pgbackrest verify)
Confirm DR environment is ready
Review escalation contacts
Prepare rollback plan

Test Execution:

T+0:00 — Simulate primary failure
T+0:01 — Verify automated failover triggered
T+0:05 — Verify DR database accessible
T+0:10 — Verify application connectivity
T+0:15 — Verify PHI data integrity (record counts)
T+0:20 — Test critical operations: Patient lookup, Lab result entry, Prescription creation, Audit log writing
T+0:30 — Verify audit trail continuity
T+0:45 — Performance benchmarks

Post-Test:

Document actual RTO achieved
Document actual RPO (data loss)
Document any issues encountered
Update DR plan based on findings
Failback to primary
Verify failback successful
Submit test report to compliance

Frequency: Quarterly (HIPAA Addressable §164.308(a)(7)(iv))

8.2. Automated DR Test Script

#!/bin/bash
# dr-test.sh
# Automated DR testing script for healthcare systems

set -euo pipefail

DR_HOST="${DR_HOST:-pg-dr.hospital.internal}"
DR_PORT="${DR_PORT:-5432}"
DR_DB="healthcare"
DR_USER="app_user"
REPORT_FILE="dr-test-report-$(date +%Y%m%d_%H%M%S).json"

PASS=0
FAIL=0
TOTAL_START=$(date +%s)

log_test() {
    local test_name=$1
    local status=$2
    local duration=$3
    local details=$4

    if [ "$status" = "PASS" ]; then ((PASS++)); else ((FAIL++)); fi

    echo "{\"test\":\"${test_name}\",\"status\":\"${status}\",\"duration_ms\":${duration},\"details\":\"${details}\"}" >> "${REPORT_FILE}"
    echo "[$status] ${test_name} (${duration}ms): ${details}"
}

echo "=== Healthcare DR Test - $(date) ===" | tee "${REPORT_FILE}"

# Test 1: Database connectivity
START=$(date +%s%N)
if pg_isready -h "$DR_HOST" -p "$DR_PORT" -d "$DR_DB" > /dev/null 2>&1; then
    DURATION=$(( ($(date +%s%N) - START) / 1000000 ))
    log_test "db_connectivity" "PASS" "$DURATION" "PostgreSQL is accepting connections"
else. else
    DURATION=$(( ($(date +%s%N) - START) / 1000000 ))
    log_test "db_connectivity" "FAIL" "$DURATION" "Cannot connect to DR database"
fi

# Test 2: Patient data integrity
START=$(date +%s%N)
PATIENT_COUNT=$(psql -h "$DR_HOST" -p "$DR_PORT" -U "$DR_USER" -d "$DR_DB" \
    -tAc "SELECT COUNT(*) FROM healthcare.patients" 2>/dev/null || echo "0")
DURATION=$(( ($(date +%s%N) - START) / 1000000 ))
if [ "$PATIENT_COUNT" -gt 0 ]; then
    log_test "patient_data_integrity" "PASS" "$DURATION" "Patient records: ${PATIENT_COUNT}"
else. else
    log_test "patient_data_integrity" "FAIL" "$DURATION" "No patient records found"
fi

# Test 3: Audit log integrity
START=$(date +%s%N)
AUDIT_COUNT=$(psql -h "$DR_HOST" -p "$DR_PORT" -U "$DR_USER" -d "$DR_DB" \
    -tAc "SELECT COUNT(*) FROM compliance.audit_logs" 2>/dev/null || echo "0")
DURATION=$(( ($(date +%s%N) - START) / 1000000 ))
if [ "$AUDIT_COUNT" -gt 0 ]; then
    log_test "audit_log_integrity" "PASS" "$DURATION" "Audit log entries: ${AUDIT_COUNT}"
else. else
    log_test "audit_log_integrity" "FAIL" "$DURATION" "No audit log entries"
fi

# Test 4: Replication lag
START=$(date +%s%N)
LAG=$(psql -h "$DR_HOST" -p "$DR_PORT" -U "$DR_USER" -d "$DR_DB" \
    -tAc "SELECT EXTRACT(EPOCH FROM NOW() - pg_last_xact_replay_timestamp())::INTEGER" 2>/dev/null || echo "-1")
DURATION=$(( ($(date +%s%N) - START) / 1000000 ))
if [ "$LAG" -ge 0 ] && [ "$LAG" -lt 300 ]; then
    log_test "replication_lag" "PASS" "$DURATION" "Lag: ${LAG}s (< 5 min)"
else
    log_test "replication_lag" "FAIL" "$DURATION" "Lag: ${LAG}s (too high or unknown)"
fi

# Test 5: Encrypted columns readable
START=$(date +%s%N)
ENCRYPTED=$(psql -h "$DR_HOST" -p "$DR_PORT" -U "$DR_USER" -d "$DR_DB" \
    -tAc "SELECT full_name FROM healthcare.patients LIMIT 1" 2>/dev/null || echo "ERROR")
DURATION=$(( ($(date +%s%N) - START) / 1000000 ))
if [[ "$ENCRYPTED" == vault:* ]] || [ "$ENCRYPTED" != "ERROR" ]; then
    log_test "encrypted_data_access" "PASS" "$DURATION" "Encrypted columns accessible"
else. else
    log_test "encrypted_data_access" "FAIL" "$DURATION" "Cannot read encrypted columns"
fi

# Test 6: Write capability (after promotion)
START=$(date +%s%N)
WRITE_TEST=$(psql -h "$DR_HOST" -p "$DR_PORT" -U "$DR_USER" -d "$DR_DB" \
    -tAc "SELECT pg_is_in_recovery()" 2>/dev/null || echo "ERROR")
DURATION=$(( ($(date +%s%N) - START) / 1000000 ))
if [ "$WRITE_TEST" = "f" ]; then
    log_test "write_capability" "PASS" "$DURATION" "Database is in read-write mode"
elif [ "$WRITE_TEST" = "t" ]; then
    log_test "write_capability" "PASS" "$DURATION" "Database is in recovery (replica) — promote needed for writes"
else. else
    log_test "write_capability" "FAIL" "$DURATION" "Cannot determine database mode"
fi

#Summary
TOTAL_DURATION=$(( $(date +%s) - TOTAL_START ))
TOTAL=$((PASS + FAIL))

echo ""
echo "==== DR TEST SUMMARY ==="
echo "Total tests: ${TOTAL}"
echo "Passed: ${PASS}"
echo "Failed: ${FAIL}"
echo "Duration: ${TOTAL_DURATION}s"

if [ "$FAIL" -eq 0 ]; then
    echo "RESULT: ALL TESTS PASSED"
else. else
    echo "RESULT: ${FAIL} TESTS FAILED — Review and remediate"
fi

9. Backup & DR Checklist

9.1. Comprehensive Healthcare DR Checklist

#	Item	Frequency	Owner
1	Full database backup (pgBackRest)	Weekly	DBA
2	Differential backup	Daily	DBA
3	Incremental backup	Hourly	Automated
4	Backup encryption verification	Weekly	Security
5	Backup integrity check (pgbackrest verify)	Daily	Automated
6	PITR test restore	Monthly	DBA
7	Replication lag monitoring	Real-time	Monitoring
8	DR failover test	Quarterly	DR Team
9	Full DR simulation	Annually	All Teams
10	Keycloak realm backup	Daily	DevOps
11	Vault snapshot	Daily	Security
12	Kafka topic backup	Daily	DevOps
13	Kubernetes state backup (Velero)	Daily	DevOps
14	Audit log backup verification	Weekly	Compliance
15	DR documentation review	Quarterly	DR Lead
16	Backup retention check (6-year HIPAA)	Monthly	Compliance
17	Cross-region backup sync	Real-time	Automated
18	Emergency contact list update	Quarterly	Management
19	RPO/RTO metrics review	Monthly	DR Lead
20	Compliance report submission	Annually	CISO

9.2. Backup Monitoring Queries

-- Monitor backup status and health

-- 1. Check WAL archiving status
SELECT
    archived_count,
    last_archived_wal,
    last_archived_time,
    failed_count,
    last_failed_wal,
    last_failed_time
FROM pg_stat_archiver;

-- 2. Replication lag
SELECT
    client_addr,
    application_name,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- 3. Replication slot status
SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS slot_lag_bytes
FROM pg_replication_slots;

-- 4. Database size (for backup planning)
SELECT
    pg_database.datname AS database_name,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- 5. Table sizes (identify largest tables for backup optimization)
SELECT
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname IN ('healthcare', 'compliance')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Summary

In this lesson, we have built a comprehensive Backup, Disaster Recovery & Business Continuity for healthcare:

HIPAA Contingency Plan: Data backup plan, disaster recovery plan, emergency mode operations — meets §164.308(a)(7)
RTO/RPO Requirements: Critical systems (EHR, Lab) need RPO < 1 min, RTO < 15 min; Determine criticality levels
PostgreSQL Backup: pg_dump (logical), pg_basebackup (physical), compare methods by RPO/speed/complexity
pgBackRest: Production backup solution with incremental backup, AES-256 encryption, S3 storage, automated scheduling (hourly/daily/weekly)
PITR: Point-in-time recovery from WAL archives — restore database to any specific point in time
Patroni HA: Automated failover with etcd consensus, synchronous replication (RPO = 0), HAProxy load balancing
Cross-Region DR: Async replication to DR region, encrypted WAL streaming, failover procedure
Microservices Recovery: Kafka consumer offset reset, Keycloak realm export, Vault snapshots
Kubernetes DR: Velero backup cho namespace state, PV snapshots, scheduled backups
DR Testing: Quarterly test runbook, automated DR test scripts, validation checklist

Exercise

pgBackRest Setup: Install pgBackRest on local PostgreSQL. Stanza configuration healthcare with local repository. Create full backup, add data, create incremental backup. Verify backup with pgbackrest verify. Test PITR: restore database to the time before adding data.
Patroni HA Cluster: Deploy Docker Compose stack (etcd × 3, PostgreSQL × 2, HAProxy). Verify primary/replica roles with patronictl list. Verify synchronous replication works. Test failover: stop primary container, verify replica self-promotes. Measure failover time (target < 30 seconds).
DR Test Script: Customize script dr-test.sh for your environment. Add test cases: SSL certificate validity, application endpoint health, API response time. Run test script and generate report. Fix any failures. Schedule runs monthly using cron.
End-to-End DR Simulation: Backup the entire healthcare stack (PostgreSQL + Keycloak + config). Simulate disaster: destroy primary database. Restore from backup (PITR). Restore Keycloak realm. Verify: login Keycloak, query patients, audit logs intact. Document: actual RPO, actual RTO, lessons learned.

◀ Previous article	Next article ▶
Lesson 19: Data Masking, Anonymization & De-identification for PHI	Lesson 21: Zero Trust Architecture for Healthcare Systems

Lesson 20: Backup, Disaster Recovery & Business Continuity