Disaster Recovery
Battle-tested backup strategies and auto-configured Point-in-Time Recovery
Point-in-Time Recovery (PITR) allows rolling back a PostgreSQL cluster to any specific moment in the past, preventing data loss from software defects or human errors. Pigsty uses pgBackRest for PITR, with configurable backup strategies using local filesystems or object storage like MinIO.
Time Travel for Databases: Roll back your cluster to any point in time, protecting against software defects, human errors, and data corruption scenarios that high availability cannot address.
Overview
Pigsty provides enterprise-grade Point-in-Time Recovery with zero-configuration setup, automated backups, and flexible restore options. Built on pgBackRest with MinIO/S3 support, it protects against data corruption, human errors, and logical disasters.
Reduced RPO
Minimize Recovery Point Objective with continuous WAL archiving
Data Integrity
Enhanced data integrity protection against corruption
Disaster Recovery
Improved disaster recovery capabilities with flexible restore options
How PITR Works
PITR requires two key components working together to enable point-in-time recovery:
Base Backups
Uses pgBackRest to create database cluster snapshots with multiple backup types:
- Full backups: Complete database cluster snapshots
- Incremental backups: Only changes since the last backup
- Differential backups: Changes since the last full backup
- Scheduled backups: Periodic backups configured via Crontab
WAL Archiving
Continuously archives Write-Ahead Log (WAL) segment files:
- Continuous archiving: Real-time WAL file preservation
- Automatic management: WAL files and cleanup handled automatically
- Optional feature: Can be disabled if PITR is not needed
Implementation
Pigsty provides two default backup strategies with flexible configuration options:
Local Filesystem Strategy
- Frequency: Daily full backups
- Storage: Local filesystem storage
- Use case: Single-node or local development environments
MinIO/S3 Strategy
- Frequency: Weekly full backup with daily incremental backups
- Storage: Object storage (MinIO, S3)
- Use case: Production environments with distributed storage
Configuration Options
Backup configuration is highly flexible with options to specify:
- Repository type: Local, S3, or other supported backends
- Retention policies: How long to keep backups
- Encryption: Secure backup storage
- Storage locations: Multiple backup destinations
Recovery Options
Recovery operations should be performed carefully as they will replace the current database state.
Recovery commands allow restoring to various points in time:
Specific LSN or Transaction ID
Restore to a particular Log Sequence Number or transaction:
pg-pitr --lsn="0/1234567"
pg-pitr --xid="12345"While PITR is powerful for data recovery, it should ideally be combined with High Availability solutions for comprehensive data protection against both logical and physical failures.