Restore to a specific timestamp

y with database size. A 5TB database dump may take days to restore, violating modern uptime requirements.

• Snapshots are block-level copies. They restore quickly but capture the filesystem state, including any corruption or accidental deletes present at the snapshot time. They also lack transactional consistency guarantees in some configurations.
• WAL Archiving separates the base backup from transaction logs. Recovery involves restoring the base and replaying logs up to a specific timestamp. This allows recovery to the exact second before an error, with RTO determined primarily by base backup size and network throughput, not total database volume.

Core Solution

Implementing a production-grade backup and recovery system requires decoupling backup storage from production infrastructure, enforcing immutability, and automating verification. This section outlines the implementation using PostgreSQL as the reference architecture, leveraging pgBackRest for robust management, though the principles apply to MySQL, MongoDB, and other systems.

Architecture Decisions

1. Dedicated Backup Repository: Backups must reside in a separate storage account or bucket with distinct IAM credentials. This prevents a compromised production role from deleting backups.
2. Immutability: Use object lock features (e.g., AWS S3 Object Lock, Azure Immutable Blob Storage) to prevent deletion or modification of backups for a retention period. This is the primary defense against ransomware.
3. Continuous Archiving: Enable WAL archiving to stream transaction logs to the repository continuously, ensuring RPO is limited only by network latency.
4. Encryption: Encrypt backups at rest using KMS keys managed separately from the database encryption keys. Encrypt in transit via TLS.

Step-by-Step Implementation

1. Configure WAL Archiving

Modify the database configuration to enable archiving. For PostgreSQL:

-- postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=prod archive-push %p'

2. Deploy pgBackRest with Immutability

pgBackRest is the industry standard for PostgreSQL backup management, supporting delta restores, parallelism, and S3 integration.

Configuration (pgbackrest.conf):

[global]
repo1-type=s3
repo1-s3-bucket=my-immutable-backup-bucket
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-region=us-east-1
repo1-storage-verify-tls=y
repo1-cipher-pass=<cipher_key>
repo1-retention-full=7
repo1-retention-diff=30
process-max=4
log-level-console=info
log-level-file=detail

[prod]
pg1-host=db-primary.internal
pg1-path=/var/lib/postgresql/data
pg1-user=postgres

Enable Object Lock on S3 Bucket: Using AWS CLI or Terraform, enforce a retention period.

aws s3api put-object-lock-configuration \
    --bucket my-immutable-backup-bucket \
    --object-lock-configuration ObjectLockEnabled=ENABLED,Rule="{DefaultRetention={Mode=COMPLIANCE,Days=30}}"

3. Automate Backup Orchestration with TypeScript

Integrate backup triggers into your CI/CD or deployment pipeline using a TypeScript orchestrator. This ensures backups are validated and tagged with deployment metadata.

import { exec } from 'child_process';
import { promisify } from 'util';
import { S3Client, PutObjectCommand } from '@aws-sdk/client-s3';

const execAsync = promisify(exec);

interface BackupConfig {
  stanza: string;
  s3Bucket: string;
  metadata: Record<string, string>;
}

export class BackupOrchestrator {
  private s3Client: S3Client;

  constructor() {
    this.s3Client = new S3Client({ region: process.env.AWS_REGION });
  }

  async executeFullBackup(config: BackupConfig): Promise<void> {
    try {
      // 1. Trigger pgBackRest full backup
      const { stdout } = await execAsync(
        `pgbackrest --stanza=${config.stanza} --type=full backup`
      );
      console.log('Backup initiated:', stdout);

      // 2. Verify backup integrity
      await this.verifyBackup(config.stanza);

      // 3. Upload metadata for audit trail
      await this.uploadMetadata(config);

      console.log('Backup completed and verified successfully.');
    } catch (error) {
      console.error('Backup failed:', error);
      throw new Error(`Backup orchestration failed: ${error}`);
    }
  }

  private async verifyBackup(stanza: string): Promise<void> {
    // pgBackRest verify checks the repository integrity
    await execAsync(`pgbackrest --stanza=${stanza} verify`);
  }

  private async uploadMetadata(config: BackupConfig): Promise<void> {
    const metadataKey = `backups/${config.stanza}/metadata/${Date.now()}.json`;
    await this.s3Client.send(new PutObjectCommand({
      Bucket: config.s3Bucket,
      Key: metadataKey,
      Body: JSON.stringify(config.metadata),
      Metadata: { type: 'backup-metadata' }
    }));
  }
}

4. Implement Point-in-Time Recovery (PITR)

Recovery requires restoring the base backup and replaying WAL files to the target timestamp.

# Restore to a specific timestamp
pgbackrest --stanza=prod \
  --type=time \
  --target="2024-05-20 14:30:00 UTC" \
  restore

After restoration, validate the database state before promoting the instance to production. Use a read-only mode initially to confirm data integrity.

Pitfall Guide

Common Mistakes

1. Snapshot Dependency: Relying solely on volume snapshots. Snapshots are not backups; they are fast, local copies. If the underlying storage array fails or credentials are compromised, snapshots are lost.
2. Unverified Backups: Assuming backups work because the process exits with code 0. Corruption can occur silently. Backups must be restored periodically to a staging environment to validate integrity.
3. Single Credential Scope: Using the same IAM role or API key for production access and backup storage. A breach of the production environment immediately compromises the backup repository.
4. Ignoring RTO Calculations: Designing a backup strategy based on storage cost rather than recovery time. A cheap backup that takes 48 hours to restore may cause more business damage than the data loss itself.
5. Backup Bloat: Retaining excessive backups without lifecycle policies. This leads to uncontrolled storage costs and makes recovery operations slower due to larger manifest files.
6. Logical Backups for Large Databases: Using pg_dump or mysqldump for multi-terabyte databases. The restoration time becomes prohibitive. Physical backups with WAL archiving are mandatory for scale.
7. Restoring to Production Directly: Restoring a backup over the production database without validation. This can overwrite good data with a corrupted backup or fail to account for schema changes made after the backup.

Best Practices

• 3-2-1 Rule: Maintain 3 copies of data, on 2 different media types, with 1 copy offsite/immutably stored.
• Least Privilege for Backups: Backup agents should only have read access to database files and write access to the backup repository. They should never have delete permissions.
• Automated Recovery Drills: Schedule monthly automated restore tests to a ephemeral environment. Alert on failure immediately.
• Separate Encryption Keys: Use distinct KMS keys for database encryption and backup encryption. This allows key rotation for one without affecting the other.
• Metadata Tagging: Tag backups with application version, git commit hash, and schema version. This enables correlation between application deployments and data states.

Production Bundle

Action Checklist

• Define RTO/RPO: Document exact recovery time and point objectives for each database tier.
• Enable WAL/Binlog Archiving: Configure continuous archiving for all critical databases.
• Implement Immutability: Enable object lock or compliance mode on backup storage buckets.
• Decouple Credentials: Create dedicated IAM roles for backup agents with restricted permissions.
• Schedule Recovery Drills: Automate monthly restore tests and monitor success rates.
• Encrypt Backups: Ensure encryption at rest and in transit using managed keys.
• Monitor Backup Health: Set up alerts for backup failures, latency, and storage capacity.
• Document Runbooks: Create step-by-step recovery procedures for common failure scenarios.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Startup / Low Volume	Managed Service Snapshots + Daily Logical Dump	Low operational overhead; sufficient for small datasets where RTO can be hours.	Low storage and compute costs.
High-Throughput Transactional App	Physical Base Backup + Continuous WAL Archiving (PITR)	Enables second-level RPO and sub-hour RTO; handles large data volumes efficiently.	Medium storage cost for log retention; higher compute for archiving.
Compliance-Heavy Enterprise	Multi-Region Replication + Air-Gapped Immutable Backups	Meets strict regulatory requirements; protects against regional disasters and insider threats.	High cost for cross-region transfer and redundant infrastructure.
Ransomware-Sensitive Environment	WAL Archiving with S3 Object Lock (Compliance Mode)	Prevents attackers from deleting backups; ensures clean recovery point even after compromise.	Moderate cost for object lock; negligible impact on performance.

Configuration Template

pgBackRest Production Configuration (pgbackrest.conf):

[global]
repo1-type=s3
repo1-s3-bucket=prod-backups-immutable
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-region=us-east-1
repo1-storage-verify-tls=y
repo1-cipher-pass=${PGBACKREST_CIPHER}
repo1-retention-full=7
repo1-retention-diff=14
repo1-retention-archive=7
process-max=4
archive-async=y
log-level-console=info
log-level-file=detail

[prod-cluster]
pg1-host=db-primary.internal
pg1-path=/var/lib/postgresql/data
pg1-user=postgres
pg1-port=5432
pg2-host=db-replica.internal
pg2-path=/var/lib/postgresql/data
pg2-user=postgres
pg2-port=5432

Terraform S3 Immutability Policy:

resource "aws_s3_bucket" "backups" {
  bucket = "prod-backups-immutable"
}

resource "aws_s3_bucket_object_lock_configuration" "backups" {
  bucket = aws_s3_bucket.backups.id

  rule {
    default_retention {
      mode = "COMPLIANCE"
      days = 30
    }
  }
}

Quick Start Guide

1. Install Backup Tool: Install pgBackRest on the database host or a dedicated backup server.

   apt-get install pgbackrest
   

2. Configure Repository: Create the pgbackrest.conf file with S3 credentials and immutability settings. Ensure the S3 bucket has object lock enabled.
3. Create Stanza and Backup: Initialize the stanza and run the first full backup.

   pgbackrest --stanza=prod-cluster stanza-create
   pgbackrest --stanza=prod-cluster backup --type=full
   

4. Enable Archiving: Update postgresql.conf to use pgbackrest as the archive command and reload the configuration.

   pg_ctlcluster 14 main reload
   

5. Verify Recovery: Perform a test restore to a temporary directory to validate the backup integrity.

   mkdir -p /tmp/restore-test
   pgbackrest --stanza=prod-cluster --pg1-path=/tmp/restore-test restore
   

Execute this guide in a non-production environment first. Validate all recovery steps against your specific RTO/RPO requirements before deploying to production.