Restore to a specific timestamp

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

[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.

```bash
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.