Back to Practical SQL Queries
Q15Question 15 of 34

Designing a production backup strategy

Key Concepts
  • RPO (maximum acceptable data loss) drives backup frequency; near-zero RPO needs continuous WAL archiving.
  • RTO (maximum acceptable downtime) drives the restore method; tight RTO favors physical restores and standbys.
  • Daily physical base backups combined with continuous WAL archiving give point-in-time recovery.
  • Tiered retention keeps dailies for a week, weeklies for a month, and monthlies for years per regulation.
  • The 3-2-1 rule requires three copies on two media with one off-site cross-region copy.
  • Automated restore drills into a scratch environment must run on a schedule and alert on failure.
  • Backup credentials must be separate from production; use object-lock storage to resist ransomware.

Definition

Design backups backward from RPO and RTO: daily full plus continuous WAL, tiered retention, cross-region copies, and tested restores.
  • RPO drives frequency – the Recovery Point Objective (how much data you can afford to lose) sets backup frequency; near-zero RPO requires continuous WAL archiving.
  • RTO drives method – the Recovery Time Objective (how fast you must be back) favors fast physical restores and warm standbys over slow logical replays.
  • Tiered retention – keep hourly/daily for a short window, weekly for a month, monthly for years as regulation requires.
  • Off-site copies – follow the 3-2-1 rule with at least one cross-region copy; encrypt at rest and in transit.
  • Tested restores – automate restore drills into a scratch environment and alert on failure, because an untested backup is a hope, not a plan.

For National Bank of India, the backup strategy is the bank's insurance policy for its data: how often it photographs the vault, how long it keeps old photographs, where it stores duplicates so one fire cannot destroy them all, and regular fire drills to prove it can actually reopen the vault. It is driven by two promises: lose at most a few seconds of data, and be back within an hour.

Understanding the Concepts

What are RPO and RTO and why do they drive the strategy?

The Recovery Point Objective is the maximum acceptable data loss measured in time. The Recovery Time Objective is the maximum acceptable downtime. Both are business decisions that set the technical requirements.

DIAGRAM
┌──────────────┬────────────────────────────────────┐
│ RPO Target   │ Required Backup Frequency           │
├──────────────┼────────────────────────────────────┤
│ 24 hours     │ Daily full backup                   │
│ 1 hour       │ Hourly incremental + daily full     │
│ Near zero    │ Continuous WAL archiving + daily full│
└──────────────┴────────────────────────────────────┘

┌──────────────┬────────────────────────────────────┐
│ RTO Target   │ Required Restore Method              │
├──────────────┼────────────────────────────────────┤
│ Hours        │ Physical restore from backup         │
│ Minutes      │ Warm standby with streaming repl.    │
│ Seconds      │ Hot standby with automatic failover  │
└──────────────┴────────────────────────────────────┘

For National Bank of India, RPO is near zero and RTO is under one hour, so Pankaj uses daily physical base backups plus continuous WAL archiving.

How do you set up daily full backups with continuous WAL?

Combine periodic base backups with continuous WAL archiving for PITR between fulls.

BASH
#!/bin/bash
# Daily base backup for National Bank of India
pg_basebackup -D /backup/daily/$(date +%Y%m%d) \
    -Ft -z -P --checkpoint=fast

# Upload to off-site storage
aws s3 sync /backup/daily/$(date +%Y%m%d) \
    s3://nbi-backups/daily/$(date +%Y%m%d)/
SQL
-- PostgreSQL WAL archiving configuration
-- postgresql.conf
-- wal_level = replica
-- archive_mode = on
-- archive_command = 'aws s3 cp %p s3://nbi-wal-archive/%f'

What is a tiered retention policy?

Retention is tiered by age and regulation requirements.

DIAGRAM
┌──────────────┬─────────────┬──────────────────────────┐
│ Tier         │ Retention   │ Purpose                   │
├──────────────┼─────────────┼──────────────────────────┤
│ Daily        │ 7 days      │ Recent PITR               │
│ Weekly       │ 4 weeks     │ Medium-term recovery      │
│ Monthly      │ 12 months   │ Quarterly audit           │
│ Yearly       │ 7+ years    │ Banking regulatory (RBI)  │
└──────────────┴─────────────┴──────────────────────────┘
BASH
# Automated retention cleanup
find /backup/daily -mtime +7 -delete
aws s3 ls s3://nbi-backups/weekly/ | awk '{print $4}' | \
    head -n -4 | xargs -I {} aws s3 rm s3://nbi-backups/weekly/{}

What is the 3-2-1 backup rule?

Keep at least three copies of data, on two different media types, with one copy off-site.

TEXT
Copy 1: Local disk on the database server
Copy 2: Network-attached storage in the same data center
Copy 3: Cross-region cloud storage (S3 in ap-south-2)

For cloud deployments, ensure at least one cross-region copy so a regional outage does not lose everything. Encrypt at rest and in transit.

BASH
# Cross-region replication with encryption
aws s3api put-bucket-replication \
    --bucket nbi-backups-primary \
    --replication-configuration file://replication.json

# Object lock for ransomware protection
aws s3api put-object-lock-configuration \
    --bucket nbi-backups-primary \
    --object-lock-configuration '{"ObjectLockEnabled":"Enabled","Rule":{"DefaultRetention":{"Mode":"GOVERNANCE","Days":30}}}'

How do you automate restore testing?

An untested backup is a hope, not a plan. Schedule automated restore drills.

BASH
#!/bin/bash
# Weekly restore drill for National Bank of India
set -e

# 1. Download latest base backup
aws s3 sync s3://nbi-backups/daily/latest /tmp/restore-test/

# 2. Restore into a scratch instance
pg_restore -d restore_test_db /tmp/restore-test/base.tar.gz

# 3. Verify data integrity
psql restore_test_db -c "SELECT COUNT(*) FROM accounts;"
psql restore_test_db -c "SELECT COUNT(*) FROM transactions;"

# 4. Measure restore time
echo "Restore completed in $SECONDS seconds"

# 5. Clean up
dropdb restore_test_db
rm -rf /tmp/restore-test/

# 6. Alert if restore time exceeds RTO
if [ $SECONDS -gt 3600 ]; then
    echo "ALERT: Restore time $SECONDS exceeds 1-hour RTO"
fi

How do you protect backups from the same blast radius?

Backup credentials must be separate from production credentials. Use immutable or object-lock storage to resist ransomware.

JAVA
package com.bank.backup;

public class BackupSecurityChecklist {
    // 1. Separate IAM role for backup access (not the app role)
    // 2. Object lock / immutable storage on backup bucket
    // 3. MFA delete enabled on the backup bucket
    // 4. Backup encryption with a separate KMS key
    // 5. No DELETE permission on backup bucket from app credentials
}

How do you monitor backup health?

Monitor backup success, archive continuity, and backup age.

SQL
-- PostgreSQL: check WAL archiving health
SELECT archived_count, failed_count,
       last_archived_wal, last_archived_time,
       now() - last_archived_time AS archive_lag
FROM pg_stat_archiver;
JAVA
package com.bank.monitoring;

public class BackupMonitor {
    public void checkBackupAge(int maxAgeHours) {
        // Query backup metadata store
        // Alert if latest backup is older than maxAgeHours
        // Alert if WAL archive has gaps
    }
}

What should a senior engineer document in the runbook?

  • Step-by-step recovery procedure with estimated times.
  • Contact list and escalation path.
  • Credentials and access instructions for backup storage.
  • Decision tree: when to PITR vs when to failover vs when to restore from logical backup.
  • Tested RTO measurement from the most recent drill.

The runbook must be rehearsed so the recovery time is measured, not guessed.

Visual Overview

DIAGRAM
┌───────────────────────────────────────────────────────┐
│          Production Backup Strategy                    │
│                                                       │
│  RPO ◄──── drives ────► Backup Frequency              │
│  RTO ◄──── drives ────► Restore Method                │
│                                                       │
│  ┌─────────┐  ┌───────────┐  ┌───────────────┐       │
│  │ Daily   │  │ Continuous│  │ Cross-Region  │       │
│  │ Physical│──│ WAL       │──│ Off-Site Copy │       │
│  │ Base    │  │ Archiving │  │ (Encrypted)   │       │
│  └─────────┘  └───────────┘  └───────────────┘       │
│       │              │              │                 │
│       └──────┬───────┘              │                 │
│              ▼                      │                 │
│     ┌────────────────┐              │                 │
│     │ Tiered         │              │                 │
│     │ Retention      │◄─────────────┘                 │
│     │ 7d/4w/12m/7y   │                                │
│     └────────┬───────┘                                │
│              ▼                                        │
│     ┌────────────────┐                                │
│     │ Automated      │                                │
│     │ Restore Drills │                                │
│     │ (weekly)       │                                │
│     └────────────────┘                                │
└───────────────────────────────────────────────────────┘

Key Takeaway

A production backup strategy is designed backward from the business recovery targets. The Recovery Point Objective sets the frequency: for a bank that is near zero, so daily full base backups plus continuous WAL archiving provide point-in-time recovery between fulls. The Recovery Time Objective sets the method, favoring fast physical restores and warm standbys over slow logical replays.

At National Bank of India the retention is tiered: a week of dailies, a month of weeklies, and years of monthlies to satisfy RBI regulation. Every backup follows the 3-2-1 principle with an encrypted cross-region off-site copy on object-lock storage so neither a regional outage nor ransomware can destroy all copies.

The part Pankaj insists on is automated verification: a scheduled restore drill into a scratch environment that actually measures the recovery time against the RTO and alerts on failure, because an untested backup is only a hope. He also monitors archive continuity so there are no WAL gaps, keeps backup credentials separate from production to limit blast radius, and rehearses the documented runbook so the recovery time is measured rather than guessed.

Remember one sentence: design backups backward from RPO and RTO with daily full plus continuous WAL, tiered retention, cross-region copies, and tested restores.

---

Share
Use ← → arrow keys to navigate