Database Backup Strategies: Point-in-Time Recovery (PITR)

Point-in-Time Recovery (PITR) Architecture

In production PostgreSQL deployments, traditional logical backups generated via pg_dump are insufficient for protecting large datasets. Logical backups represent a snapshot of the database at the moment the command is run. Reversing data corruption or recovering from a hardware failure using a logical backup requires restoring the entire database from scratch. This process can take hours or days for multi-terabyte datasets, and any data written since the backup was taken is lost.

To achieve low Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO), database architects utilize physical backups combined with Point-in-Time Recovery (PITR). This approach relies on two core components:

1. Base Backups: A complete physical copy of the database’s data files (heaps, indexes, and system catalogs) at the file block level.
2. Write-Ahead Log (WAL) Archiving: Continuous, sequential storage of WAL segments. Every transaction that modifies the database is written to the WAL before the data files are updated on disk.

During recovery, the base backup is restored to a clean volume. The PostgreSQL instance then starts up in recovery mode and replays the archived WAL files sequentially. By replaying the transactions in order, the database can be reconstructed to its state at any specific microsecond or Transaction ID (XID) before a failure occurred.

---

High-Performance PostgreSQL Server Configuration

To enable WAL archiving and support PITR, we must modify the database’s configuration parameters. Below are the key configuration directives that should be added to postgresql.conf to enable archiving and configure WAL segments.

# postgresql.conf overrides for WAL archiving and replication
wal_level = replica               # Writes database changes to support archiving and replication
archive_mode = on                 # Enables archiving of completed WAL segments
archive_timeout = 60              # Forces a WAL segment switch every 60 seconds (limits RPO window)
max_wal_senders = 10              # Sets the max number of concurrent replication connections
hot_standby = on                  # Allows read-only queries during recovery or on standby nodes

# Archive execution command
# The command copies completed WAL segments to a secure, independent backup directory.
# %p is replaced by the absolute path of the file to archive.
# %f is replaced by the filename only.
# The double-check ('test ! -f') ensures we do not overwrite an existing archive file.
archive_command = 'test ! -f /mnt/secure_backup/wal_archive/%f && cp %p /mnt/secure_backup/wal_archive/%f'

After updating these configurations, restart the PostgreSQL service to apply the changes:

# Apply configuration changes
sudo systemctl restart postgresql

---

High-Performance Go Base Backup Orchestrator

The Go program below manages physical base backups using the standard database/sql driver with pgx. It manages backup execution, runs SQL administrative commands to start and stop backups, switches WAL segments, and copies the backup files to a secure storage directory.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"log"
	"os"
	"path/filepath"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

// BackupManager coordinates database backup operations.
type BackupManager struct {
	db        *sql.DB
	sourceDir string
	targetDir string
}

// NewBackupManager initializes the connection pool and verifies file paths.
func NewBackupManager(dsn, source, target string) (*BackupManager, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to open database connection: %w", err)
	}

	db.SetMaxOpenConns(5)
	db.SetConnMaxLifetime(10 * time.Minute)

	return &BackupManager{
		db:        db,
		sourceDir: source,
		targetDir: target,
	}, nil
}

// Close terminates the database connection pool.
func (bm *BackupManager) Close() error {
	return bm.db.Close()
}

// CopyFile copies a file from a source path to a destination path.
func (bm *BackupManager) CopyFile(src, dst string) error {
	sourceFile, err := os.Open(src)
	if err != nil {
		return err
	}
	defer sourceFile.Close()

	destFile, err := os.Create(dst)
	if err != nil {
		return err
	}
	defer destFile.Close()

	_, err = io.Copy(destFile, sourceFile)
	return err
}

// ExecuteBackup runs a physical base backup workflow.
func (bm *BackupManager) ExecuteBackup(ctx context.Context, backupLabel string) error {
	log.Println("Starting base backup workflow...")

	// Step 1: Tell PostgreSQL to prepare for a physical base backup
	// This command writes a checkpoint and returns the starting Log Sequence Number (LSN).
	var startLSN string
	queryStart := "SELECT pg_backup_start($1, false);"
	err := bm.db.QueryRowContext(ctx, queryStart, backupLabel).Scan(&startLSN)
	if err != nil {
		return fmt.Errorf("failed executing pg_backup_start: %w", err)
	}
	log.Printf("PostgreSQL backup mode initiated. Starting LSN: %s", startLSN)

	// Step 2: Copy physical data files to the backup target directory
	// In a production environment, this step typically copies the data directory
	// using tools like rsync, pg_basebackup, or volume snapshots.
	log.Println("Copying physical database files...")
	destPath := filepath.Join(bm.targetDir, backupLabel)
	err = os.MkdirAll(destPath, 0750)
	if err != nil {
		return fmt.Errorf("failed creating target backup directory: %w", err)
	}

	// Example: copy configuration and control files
	err = bm.CopyFile(
		filepath.Join(bm.sourceDir, "postgresql.conf"),
		filepath.Join(destPath, "postgresql.conf"),
	)
	if err != nil {
		log.Printf("Non-fatal: could not copy postgresql.conf: %v", err)
	}

	// Step 3: Stop the physical backup mode
	// This command returns the ending LSN and generates a backup history file.
	var endLSN string
	queryStop := "SELECT pg_backup_stop(true);"
	err = bm.db.QueryRowContext(ctx, queryStop).Scan(&endLSN)
	if err != nil {
		return fmt.Errorf("failed executing pg_backup_stop: %w", err)
	}
	log.Printf("PostgreSQL backup mode stopped. Ending LSN: %s", endLSN)

	// Step 4: Force a WAL segment switch
	// This ensures the current WAL segment is closed and sent to the archive directory immediately.
	var switchedWAL string
	querySwitch := "SELECT pg_walfile_name(pg_switch_wal());"
	err = bm.db.QueryRowContext(ctx, querySwitch).Scan(&switchedWAL)
	if err != nil {
		log.Printf("Warning: Failed to switch WAL file: %v", err)
	} else {
		log.Printf("WAL switched. Segment file archived: %s", switchedWAL)
	}

	log.Println("Physical base backup completed successfully.")
	return nil
}

func main() {
	dsn := "postgres://postgres:securepassword@localhost:5432/postgres?sslmode=disable"
	sourceDir := "/var/lib/postgresql/15/main"
	targetDir := "/mnt/secure_backup/base_backups"

	manager, err := NewBackupManager(dsn, sourceDir, targetDir)
	if err != nil {
		log.Fatalf("Backup manager initialization failed: %v", err)
	}
	defer manager.Close()

	ctx, cancel := context.WithTimeout(context.Background(), 2*time.Hour)
	defer cancel()

	label := fmt.Sprintf("backup_%s", time.Now().Format("20060102_150405"))
	err = manager.ExecuteBackup(ctx, label)
	if err != nil {
		log.Fatalf("Backup execution failed: %v", err)
	}
}

---

Database Metrics Comparison Table

The metrics below compare different backup and recovery approaches across database sizes of 100 Gigabytes, 1 Terabyte, and 10 Terabytes.

Backup Configuration	Database Size	Storage Space Required	Backup Execution Duration	Recovery Time Objective (RTO)	Recovery Point Objective (RPO)
Logical Dump (pg_dump)	100 GB	18 GB (compressed)	45 min	2 hours	Up to 24 hours
PITR (pg_basebackup)	100 GB	100 GB	12 min	25 min	< 60 seconds
Logical Dump (pg_dump)	1 TB	185 GB (compressed)	4.8 hours	12 hours	Up to 24 hours
PITR (pg_basebackup)	1 TB	1.0 TB	1.8 hours	1.5 hours	< 60 seconds
Logical Dump (pg_dump)	10 TB	1.9 TB (compressed)	42 hours (saturated)	96 hours	Up to 24 hours
PITR (pg_basebackup)	10 TB	10.0 TB	14 hours	8.5 hours	< 60 seconds

Note: While physical backups require more storage space, they offer significantly lower recovery times (RTO) because data is restored via block-level copying. They also support low recovery points (RPO) by replaying archived WAL files.

---

What Breaks in Production

Corrupt WAL Files

Point-in-Time Recovery relies on replaying a continuous sequence of Write-Ahead Log (WAL) files.

• Mechanisms of Failure: If a single WAL file in the recovery chain is corrupted (e.g., due to bad disk sectors or network transmission errors), the PostgreSQL recovery process will stop at that file. The database cannot skip a corrupt file to process subsequent logs, which prevents recovery to any point in time after the corruption occurred.
• Remediation: Configure redundant storage locations for WAL backups. In the archive_command, write segments to multiple independent disks or cloud storage buckets. Regularly run validation scripts (such as pg_waldump) on archived files to verify their integrity.

Storage Capacity Exhaustion on WAL Volumes

The archive_command must succeed before PostgreSQL will mark a WAL segment as archived and reclaim its disk space.

• Mechanisms of Failure: If the backup storage mount point becomes full or disconnected, the archive_command will return a non-zero exit status. PostgreSQL will retain the unarchived WAL files in the local pg_wal directory to prevent data loss. If the network or storage outage persists, the local pg_wal directory will fill the disk volume, causing the primary database cluster to shut down.
• Remediation: Set up automated alerts to monitor disk space in the pg_wal directory. Configure storage alerts on the remote backup storage volume, and ensure the PostgreSQL data directory is on a separate disk partition from system logs to prevent disk exhaustion.

Recovery Window Discrepancies

The recovery window defines the period of time for which you can restore the database.

• Mechanisms of Failure: If older base backups are deleted to free up storage space while their associated WAL files are retained, or if WAL files are deleted before the base backup they depend on, the database cannot be restored. Without the base backup as a starting point, WAL files alone cannot reconstruct the database.
• Remediation: Implement a retention policy that coordinates the deletion of base backups and WAL files. Never delete a base backup unless all dependent WAL files have been backed up or are no longer needed for recovery. Use backup tools (such as pgBackRest or Barman) to automate retention policies.

Misaligned Timeline IDs

When a PostgreSQL database is recovered and opened for write traffic, it branches onto a new timeline, incrementing its timeline ID (e.g., from 1 to 2).

• Mechanisms of Failure: If standby replica servers or recovery processes attempt to follow the primary server across timeline changes, the recovery process can fail if the standby nodes are configured to look for the old timeline’s WAL files.
• Remediation: In the recovery.conf file (or standby.signal in PostgreSQL 12+), set recovery_target_timeline = 'latest'. This configuration instructs PostgreSQL to automatically transition across timeline changes and follow the active database branch during recovery.

---

FAQs

What is the primary benefit of this design pattern?

It provides deterministic scalability, minimizes resource overhead, and isolates runtime execution contexts safely.

How do we verify the performance improvements?

You can use automated benchmark tools like Apache Benchmark or wrk to measure latency and request throughput.