PostgreSQL pgBackRest: Complete Backup and Recovery Guide

Abhishek14 Oct 202615 min read

Master PostgreSQL backup and recovery with pgBackRest. Learn installation, configuration, backup strategies, PITR, and monitoring for production environments.

pgBackRest is a reliable backup and restore solution for PostgreSQL that provides parallel backup, asynchronous archiving, multiple repositories, full/differential/incremental backups, and point-in-time recovery (PITR). This comprehensive guide covers everything from installation to advanced recovery scenarios.

Introduction to pgBackRest
Installation and Setup
Configuration
Backup Strategies
Recovery and PITR
Advanced Features
Monitoring and Maintenance
Production Best Practices

Introduction to pgBackRest

Why pgBackRest?

pgBackRest offers significant advantages over traditional PostgreSQL backup methods:

bash

# Traditional pg_dump approach (limited)
pg_dump mydb > mydb_backup.sql

# vs pgBackRest capabilities
pgbackrest --stanza=main backup --type=incr
pgbackrest --stanza=main restore --delta
pgbackrest --stanza=main restore --type=time --target="2024-02-02 10:30:00"

Key Features

Feature	pgBackRest	pg_dump	pg_basebackup
Parallel Processing	✅ Yes	❌ No	❌ No
Incremental Backups	✅ Yes	❌ No	❌ No
Point-in-Time Recovery	✅ Yes	❌ No	✅ Yes
Multiple Repositories	✅ Yes	❌ No	❌ No
Compression	✅ Advanced	✅ Basic	✅ Basic
Cloud Storage	✅ S3/Azure/GCS	❌ No	❌ No
Backup Verification	✅ Automatic	❌ Manual	❌ Manual
WAL Archiving	✅ Automatic	❌ No	❌ Manual

Installation and Setup

Installation on Ubuntu/Debian

bash

# Install PostgreSQL and pgBackRest
sudo apt update
sudo apt install postgresql postgresql-contrib pgbackrest

# Verify installation
pgbackrest version
postgres --version

# Create pgbackrest user
sudo adduser pgbackrest
sudo usermod -aG postgres pgbackrest

# Setup SSH keys for remote backup (if needed)
sudo -u pgbackrest ssh-keygen -t rsa -b 4096
sudo -u postgres ssh-keygen -t rsa -b 4096

# Copy public keys between servers
# On database server:
sudo -u postgres ssh-copy-id pgbackrest@backup-server

# On backup server:
sudo -u pgbackrest ssh-copy-id postgres@db-server

Installation on CentOS/RHEL

bash

# Install EPEL repository
sudo yum install epel-release

# Install PostgreSQL repository
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install packages
sudo yum install postgresql15-server postgresql15-contrib pgbackrest

# Initialize PostgreSQL
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

Docker Setup

yaml

# docker-compose.yml for pgBackRest testing
version: '3.8'

services:
  postgres:
    image: postgres:15
    container_name: postgres-main
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres123
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
      - ./pg_hba.conf:/etc/postgresql/pg_hba.conf
      - wal_archive:/var/lib/postgresql/wal-archive
      - pgbackrest_spool:/var/spool/pgbackrest
    command: >
      postgres 
      -c config_file=/etc/postgresql/postgresql.conf
    networks:
      - postgres_net

  pgbackrest:
    image: pgbackrest/pgbackrest:latest
    container_name: pgbackrest
    volumes:
      - pgbackrest_repo:/var/lib/pgbackrest
      - pgbackrest_spool:/var/spool/pgbackrest
      - wal_archive:/var/lib/postgresql/wal-archive
      - ./pgbackrest.conf:/etc/pgbackrest/pgbackrest.conf
    depends_on:
      - postgres
    networks:
      - postgres_net

volumes:
  postgres_data:
  pgbackrest_repo:
  pgbackrest_spool:
  wal_archive:

networks:
  postgres_net:
    driver: bridge

Configuration

PostgreSQL Configuration

bash

# Edit postgresql.conf
sudo vim /etc/postgresql/15/main/postgresql.conf

# Key settings for pgBackRest

sql

-- PostgreSQL configuration for pgBackRest
-- /etc/postgresql/15/main/postgresql.conf

-- WAL settings
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 60

-- Checkpoint settings for better backup performance
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB

-- Connection settings
max_connections = 200
shared_buffers = 256MB

-- Logging for monitoring
log_destination = 'stderr'
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

-- Performance monitoring
shared_preload_libraries = 'pg_stat_statements'
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all

pgBackRest Configuration

bash

# Create pgbackrest configuration
sudo vim /etc/pgbackrest/pgbackrest.conf

ini

# /etc/pgbackrest/pgbackrest.conf
# Main configuration file for pgBackRest

[global]
# Repository settings
repo1-type=posix
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-diff=2
repo1-retention-archive=7

# Optional: S3 repository
repo2-type=s3
repo2-s3-bucket=my-pgbackrest-backups
repo2-s3-region=us-west-2
repo2-s3-endpoint=s3.amazonaws.com
repo2-path=/backups/postgres
repo2-retention-full=12
repo2-retention-archive=30

# Logging
log-level-console=info
log-level-file=debug
log-path=/var/log/pgbackrest

# Performance settings
process-max=4
archive-async=y
spool-path=/var/spool/pgbackrest

# Compression and encryption
compress=y
compress-type=lz4
compress-level=3

# Optional: Encryption
# repo1-cipher-type=aes-256-cbc
# repo1-cipher-pass=your-encryption-password

[main]
# PostgreSQL cluster settings
pg1-host=localhost
pg1-path=/var/lib/postgresql/15/main
pg1-port=5432
pg1-user=postgres

# Optional: Standby server
# pg2-host=standby-server
# pg2-path=/var/lib/postgresql/15/main
# pg2-port=5432
# pg2-user=postgres

# Recovery settings
recovery-option=primary_conninfo=host=localhost port=5432 user=replicator
recovery-option=restore_command=pgbackrest --stanza=main archive-get %f "%p"

Initialize Stanza

bash

# Create and initialize the stanza
sudo -u postgres pgbackrest --stanza=main stanza-create

# Check stanza configuration
sudo -u postgres pgbackrest --stanza=main check

# Verify repository
sudo -u postgres pgbackrest --stanza=main info

Setup Archive Command

sql

-- Connect to PostgreSQL and set archive command
ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=main archive-push %p';

-- Reload configuration
SELECT pg_reload_conf();

-- Verify archiving is working
SELECT * FROM pg_stat_archiver;

-- Force a WAL switch to test archiving
SELECT pg_switch_wal();

Backup Strategies

Full Backup

bash

# Create initial full backup
sudo -u postgres pgbackrest --stanza=main backup --type=full

# Full backup with specific repository
sudo -u postgres pgbackrest --stanza=main backup --type=full --repo=1

# Full backup with custom label
sudo -u postgres pgbackrest --stanza=main backup --type=full --annotation="monthly-backup"

# Verify backup
sudo -u postgres pgbackrest --stanza=main info

Incremental and Differential Backups

bash

# Differential backup (changes since last full backup)
sudo -u postgres pgbackrest --stanza=main backup --type=diff

# Incremental backup (changes since last backup of any type)
sudo -u postgres pgbackrest --stanza=main backup --type=incr

# Schedule different backup types
# Full backup weekly, differential daily, incremental hourly

Automated Backup Schedule

bash

# Create backup script
sudo vim /usr/local/bin/pgbackrest-backup.sh

bash

#!/bin/bash
# /usr/local/bin/pgbackrest-backup.sh
# Automated pgBackRest backup script

set -e

STANZA="main"
LOG_FILE="/var/log/pgbackrest/backup-$(date +%Y%m%d).log"
EMAIL="[email protected]"

# Function to send notification
send_notification() {
    local status=$1
    local message=$2
    echo "$(date): $message" >> "$LOG_FILE"
    
    if [ "$status" = "ERROR" ]; then
        echo "$message" | mail -s "pgBackRest Backup Failed" "$EMAIL"
    fi
}

# Function to perform backup
perform_backup() {
    local backup_type=$1
    
    echo "$(date): Starting $backup_type backup for stanza $STANZA" >> "$LOG_FILE"
    
    if pgbackrest --stanza="$STANZA" backup --type="$backup_type" >> "$LOG_FILE" 2>&1; then
        send_notification "SUCCESS" "$backup_type backup completed successfully"
        
        # Show backup info
        pgbackrest --stanza="$STANZA" info >> "$LOG_FILE" 2>&1
        
        return 0
    else
        send_notification "ERROR" "$backup_type backup failed"
        return 1
    fi
}

# Main execution
case "${1:-incr}" in
    full)
        perform_backup "full"
        ;;
    diff)
        perform_backup "diff"
        ;;
    incr)
        perform_backup "incr"
        ;;
    *)
        echo "Usage: $0 {full|diff|incr}"
        exit 1
        ;;
esac

# Cleanup old logs
find /var/log/pgbackrest -name "backup-*.log" -mtime +30 -delete

exit $?

bash

# Make script executable
sudo chmod +x /usr/local/bin/pgbackrest-backup.sh

# Setup cron jobs
sudo crontab -e

# Add backup schedule
# Full backup every Sunday at 2 AM
0 2 * * 0 /usr/local/bin/pgbackrest-backup.sh full

# Differential backup daily at 2 AM (except Sunday)
0 2 * * 1-6 /usr/local/bin/pgbackrest-backup.sh diff

# Incremental backup every 4 hours
0 */4 * * * /usr/local/bin/pgbackrest-backup.sh incr

Advanced Backup Configuration

bash

# Parallel backup for faster performance
sudo -u postgres pgbackrest --stanza=main backup --type=full --process-max=8

# Backup with specific compression
sudo -u postgres pgbackrest --stanza=main backup --type=full --compress-type=zst --compress-level=3

# Backup to specific repository
sudo -u postgres pgbackrest --stanza=main backup --type=full --repo=2

# Backup with verification
sudo -u postgres pgbackrest --stanza=main backup --type=full --checksum-page

# Stop backup if running too long
sudo -u postgres pgbackrest --stanza=main backup --type=full --backup-timeout=3600

Recovery and PITR

Full Database Restore

bash

# Stop PostgreSQL before restore
sudo systemctl stop postgresql

# Remove existing data directory (be careful!)
sudo rm -rf /var/lib/postgresql/15/main

# Restore latest backup
sudo -u postgres pgbackrest --stanza=main restore

# Start PostgreSQL
sudo systemctl start postgresql

# Verify restore
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

Point-in-Time Recovery (PITR)

bash

# Restore to specific time
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/15/main

# Restore to specific timestamp
sudo -u postgres pgbackrest --stanza=main restore \
    --type=time \
    --target="2024-02-02 10:30:00" \
    --target-action=promote

# Restore to specific transaction ID
sudo -u postgres pgbackrest --stanza=main restore \
    --type=xid \
    --target="12345678" \
    --target-action=promote

# Restore to specific WAL location
sudo -u postgres pgbackrest --stanza=main restore \
    --type=lsn \
    --target="0/15000000" \
    --target-action=promote

sudo systemctl start postgresql

Selective Restore

bash

# Restore specific database
sudo -u postgres pgbackrest --stanza=main restore \
    --db-include=myapp_db

# Restore excluding specific databases
sudo -u postgres pgbackrest --stanza=main restore \
    --db-exclude=test_db,temp_db

# Restore specific tablespace
sudo -u postgres pgbackrest --stanza=main restore \
    --tablespace-map=old_location=new_location

# Delta restore (only restore changed files)
sudo -u postgres pgbackrest --stanza=main restore --delta

# Force restore over existing data
sudo -u postgres pgbackrest --stanza=main restore --force

Advanced Recovery Scenarios

sql

-- Create test scenario for PITR
CREATE DATABASE pitr_test;
\c pitr_test

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount DECIMAL(10,2),
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert some data
INSERT INTO transactions (amount, description) VALUES
(100.00, 'Initial deposit'),
(50.00, 'Purchase 1'),
(-25.00, 'Refund');

-- Note the time before problematic transaction
SELECT NOW();  -- Remember this timestamp

-- Simulate problematic transaction
INSERT INTO transactions (amount, description) VALUES
(-1000000.00, 'Accidental large withdrawal');

-- Drop table accidentally
DROP TABLE transactions;

bash

# Recover to before the problem occurred
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/15/main

# Restore to before the problem (use the timestamp noted above)
sudo -u postgres pgbackrest --stanza=main restore \
    --type=time \
    --target="2024-02-02 10:25:00" \
    --target-action=promote

sudo systemctl start postgresql

# Verify recovery
sudo -u postgres psql pitr_test -c "SELECT * FROM transactions;"

Recovery Monitoring

bash

# Monitor recovery progress
sudo -u postgres tail -f /var/log/postgresql/postgresql-*.log

# Check recovery status
sudo -u postgres psql -c "SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();"

# Monitor WAL replay lag
sudo -u postgres psql -c "
SELECT 
    CASE 
        WHEN pg_is_in_recovery() THEN 
            extract(epoch from now() - pg_last_xact_replay_timestamp())::int 
        ELSE 0 
    END AS lag_seconds;
"

Advanced Features

Multiple Repositories

ini

# pgbackrest.conf with multiple repositories
[global]
# Local repository
repo1-type=posix
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-archive=7

# S3 repository
repo2-type=s3
repo2-s3-bucket=prod-backups
repo2-s3-region=us-west-2
repo2-path=/postgres/main
repo2-retention-full=12
repo2-retention-archive=30

# Azure repository
repo3-type=azure
repo3-azure-account=mystorageaccount
repo3-azure-container=backups
repo3-path=/postgres/main
repo3-retention-full=6
repo3-retention-archive=14

[main]
pg1-path=/var/lib/postgresql/15/main

bash

# Backup to specific repository
sudo -u postgres pgbackrest --stanza=main backup --repo=1
sudo -u postgres pgbackrest --stanza=main backup --repo=2

# Restore from specific repository
sudo -u postgres pgbackrest --stanza=main restore --repo=2

# Info from all repositories
sudo -u postgres pgbackrest --stanza=main info

Standby Configuration

ini

# Configuration for primary and standby
[global]
repo1-type=posix
repo1-host=backup-server
repo1-host-user=pgbackrest
repo1-path=/var/lib/pgbackrest

[main]
# Primary server
pg1-host=primary-server
pg1-host-user=postgres
pg1-path=/var/lib/postgresql/15/main

# Standby server
pg2-host=standby-server
pg2-host-user=postgres
pg2-path=/var/lib/postgresql/15/main

# Standby-specific settings
recovery-option=standby_mode=on
recovery-option=primary_conninfo=host=primary-server port=5432 user=replicator

Backup Verification

bash

# Verify backup integrity
sudo -u postgres pgbackrest --stanza=main check

# Detailed verification with checksum validation
sudo -u postgres pgbackrest --stanza=main backup --type=full --checksum-page

# Verify specific backup
sudo -u postgres pgbackrest --stanza=main info --set=20240202-103000F

# Test restore without actually restoring
sudo -u postgres pgbackrest --stanza=main restore --dry-run --type=time --target="2024-02-02 10:30:00"

Encryption Configuration

ini

# pgbackrest.conf with encryption
[global]
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass-file=/etc/pgbackrest/encryption.key

# Or use environment variable
# export PGBACKREST_REPO1_CIPHER_PASS="your-encryption-password"

[main]
pg1-path=/var/lib/postgresql/15/main

bash

# Create encryption key file
sudo mkdir -p /etc/pgbackrest
sudo openssl rand -base64 32 | sudo tee /etc/pgbackrest/encryption.key
sudo chmod 600 /etc/pgbackrest/encryption.key
sudo chown postgres:postgres /etc/pgbackrest/encryption.key

Monitoring and Maintenance

Monitoring Scripts

bash

# Create monitoring script
sudo vim /usr/local/bin/pgbackrest-monitor.sh

bash

#!/bin/bash
# pgBackRest monitoring script

STANZA="main"
LOG_FILE="/var/log/pgbackrest/monitor.log"
ALERT_EMAIL="[email protected]"

# Function to log messages
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S'): $1" >> "$LOG_FILE"
}

# Function to send alert
send_alert() {
    local subject="$1"
    local message="$2"
    echo "$message" | mail -s "$subject" "$ALERT_EMAIL"
    log_message "ALERT: $subject - $message"
}

# Check last backup age
check_backup_age() {
    local last_backup=$(pgbackrest --stanza="$STANZA" info --output=json | jq -r '.[] | .backup[-1].timestamp.stop')
    local backup_epoch=$(date -d "$last_backup" +%s)
    local current_epoch=$(date +%s)
    local age_hours=$(( (current_epoch - backup_epoch) / 3600 ))
    
    if [ "$age_hours" -gt 25 ]; then
        send_alert "pgBackRest: Backup Too Old" "Last backup is $age_hours hours old"
        return 1
    else
        log_message "Last backup age: $age_hours hours (OK)"
        return 0
    fi
}

# Check WAL archiving
check_wal_archiving() {
    local failed_archives=$(sudo -u postgres psql -t -c "SELECT failed_count FROM pg_stat_archiver;" | xargs)
    local last_archive_time=$(sudo -u postgres psql -t -c "SELECT last_archived_time FROM pg_stat_archiver;" | xargs)
    
    if [ "$failed_archives" -gt 0 ]; then
        send_alert "pgBackRest: WAL Archive Failures" "Failed archives: $failed_archives, Last successful: $last_archive_time"
        return 1
    else
        log_message "WAL archiving: OK (Last: $last_archive_time)"
        return 0
    fi
}

# Check repository space
check_repository_space() {
    local repo_path="/var/lib/pgbackrest"
    local usage=$(df "$repo_path" | awk 'NR==2 {print $5}' | sed 's/%//')
    
    if [ "$usage" -gt 85 ]; then
        send_alert "pgBackRest: Repository Space Critical" "Repository usage: ${usage}%"
        return 1
    elif [ "$usage" -gt 75 ]; then
        send_alert "pgBackRest: Repository Space Warning" "Repository usage: ${usage}%"
        return 1
    else
        log_message "Repository space usage: ${usage}% (OK)"
        return 0
    fi
}

# Check backup integrity
check_backup_integrity() {
    if pgbackrest --stanza="$STANZA" check > /dev/null 2>&1; then
        log_message "Backup integrity check: PASSED"
        return 0
    else
        send_alert "pgBackRest: Integrity Check Failed" "Backup integrity check failed for stanza $STANZA"
        return 1
    fi
}

# Main monitoring execution
log_message "Starting pgBackRest monitoring"

check_backup_age
check_wal_archiving  
check_repository_space
check_backup_integrity

log_message "Monitoring completed"

bash

# Make script executable and schedule
sudo chmod +x /usr/local/bin/pgbackrest-monitor.sh

# Add to cron (run every hour)
echo "0 * * * * /usr/local/bin/pgbackrest-monitor.sh" | sudo crontab -

Performance Monitoring

sql

-- Monitor archiving performance
SELECT 
    archived_count,
    last_archived_wal,
    last_archived_time,
    failed_count,
    last_failed_wal,
    last_failed_time,
    stats_reset
FROM pg_stat_archiver;

-- Monitor WAL generation rate
SELECT 
    current_setting('archive_timeout'),
    pg_current_wal_lsn(),
    pg_walfile_name(pg_current_wal_lsn());

-- Check checkpoint statistics
SELECT 
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint,
    buffers_clean,
    maxwritten_clean,
    buffers_backend,
    buffers_backend_fsync,
    buffers_alloc,
    stats_reset
FROM pg_stat_bgwriter;

Backup Reports

bash

# Generate backup report
sudo -u postgres pgbackrest --stanza=main info --output=json | jq '.[] | {
    stanza: .name,
    database: .db[0].version,
    repository: .backup[0].archive.start,
    backups: [.backup[] | {
        type: .type,
        timestamp: .timestamp.stop,
        size: .info.size,
        runtime: .info.runtime
    }]
}'

# Weekly backup report
cat > /usr/local/bin/pgbackrest-report.sh << 'EOF'
#!/bin/bash
STANZA="main"
REPORT_FILE="/tmp/pgbackrest-weekly-report.txt"

{
    echo "PostgreSQL pgBackRest Weekly Report"
    echo "Generated: $(date)"
    echo "========================================="
    echo
    
    echo "Stanza Information:"
    pgbackrest --stanza="$STANZA" info
    echo
    
    echo "Recent Backup History:"
    pgbackrest --stanza="$STANZA" info --output=json | jq -r '.[] | .backup[-5:] | .[] | "\(.timestamp.stop) \(.type) \(.info.size)"'
    echo
    
    echo "WAL Archive Status:"
    sudo -u postgres psql -c "SELECT * FROM pg_stat_archiver;"
    echo
    
    echo "Repository Usage:"
    df -h /var/lib/pgbackrest
    
} > "$REPORT_FILE"

# Email the report
mail -s "pgBackRest Weekly Report" [email protected] < "$REPORT_FILE"
EOF

chmod +x /usr/local/bin/pgbackrest-report.sh

# Schedule weekly report
echo "0 8 * * 1 /usr/local/bin/pgbackrest-report.sh" | crontab -

Production Best Practices

Security Hardening

bash

# Secure file permissions
sudo chmod 750 /etc/pgbackrest
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown -R postgres:postgres /etc/pgbackrest

sudo chmod 750 /var/lib/pgbackrest
sudo chown -R postgres:postgres /var/lib/pgbackrest

# Secure log directories
sudo chmod 750 /var/log/pgbackrest
sudo chown -R postgres:postgres /var/log/pgbackrest

# Setup logrotate for pgBackRest logs
sudo cat > /etc/logrotate.d/pgbackrest << 'EOF'
/var/log/pgbackrest/*.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 640 postgres postgres
    postrotate
        /bin/kill -HUP $(cat /var/run/postgresql/*.pid 2>/dev/null) 2>/dev/null || true
    endscript
}
EOF

Performance Optimization

ini

# High-performance pgbackrest.conf
[global]
# Use maximum available cores
process-max=8

# Optimize compression
compress=y
compress-type=lz4
compress-level=1

# Enable asynchronous archiving
archive-async=y
spool-path=/var/spool/pgbackrest

# Network optimization for remote repositories
protocol-timeout=600
db-timeout=1800

# Memory settings
buffer-size=64MB

[main]
pg1-path=/var/lib/postgresql/15/main

Disaster Recovery Planning

bash

# Create disaster recovery documentation
cat > /opt/postgres/dr-procedures.md << 'EOF'
# PostgreSQL Disaster Recovery Procedures

## Scenario 1: Complete Server Loss

1. Provision new server with same specifications
2. Install PostgreSQL and pgBackRest
3. Copy pgbackrest.conf configuration
4. Restore from backup:
   ```bash
   pgbackrest --stanza=main restore

Start PostgreSQL and verify

Scenario 2: Corruption Detected

Stop PostgreSQL immediately
Identify corruption extent

Restore from nearest clean backup:

bash

pgbackrest --stanza=main restore --type=time --target="YYYY-MM-DD HH:MM:SS"

Start PostgreSQL in recovery mode
Verify data integrity

Scenario 3: Accidental Data Loss

Identify approximate time of data loss

Restore to point before loss:

bash

pgbackrest --stanza=main restore --type=time --target="YYYY-MM-DD HH:MM:SS" --target-action=promote

Start PostgreSQL
Verify recovered data

Contact Information

Primary DBA: [email protected]
Secondary DBA: [email protected]
Emergency Escalation: +1-800-xxx-xxxx EOF


### Testing and Validation

```bash
# Create automated restore test
cat > /usr/local/bin/pgbackrest-test-restore.sh << 'EOF'
#!/bin/bash
# Automated restore testing script

STANZA="main"
TEST_DIR="/tmp/pgbackrest-test-$(date +%Y%m%d-%H%M%S)"
LOG_FILE="/var/log/pgbackrest/test-restore.log"

log_message() {
    echo "$(date): $1" | tee -a "$LOG_FILE"
}

# Create test directory
mkdir -p "$TEST_DIR"

log_message "Starting restore test to $TEST_DIR"

# Test restore
if pgbackrest --stanza="$STANZA" restore --pg1-path="$TEST_DIR" --force >> "$LOG_FILE" 2>&1; then
    log_message "Restore test PASSED"
    
    # Basic validation
    if [ -f "$TEST_DIR/PG_VERSION" ]; then
        pg_version=$(cat "$TEST_DIR/PG_VERSION")
        log_message "Restored PostgreSQL version: $pg_version"
    fi
    
    # Check key files
    for file in postgresql.conf pg_hba.conf recovery.conf; do
        if [ -f "$TEST_DIR/$file" ]; then
            log_message "Found required file: $file"
        fi
    done
    
    # Cleanup
    rm -rf "$TEST_DIR"
    log_message "Test restore completed successfully"
    exit 0
else
    log_message "Restore test FAILED"
    exit 1
fi
EOF

chmod +x /usr/local/bin/pgbackrest-test-restore.sh

# Schedule monthly restore tests
echo "0 3 1 * * /usr/local/bin/pgbackrest-test-restore.sh" | crontab -

Capacity Planning

sql

-- Monitor database growth for capacity planning
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables 
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- WAL generation statistics
SELECT 
    date_trunc('hour', stats_reset) as hour,
    sum(wal_records) as wal_records,
    pg_size_pretty(sum(wal_bytes)) as wal_bytes
FROM pg_stat_wal
GROUP BY hour
ORDER BY hour DESC
LIMIT 24;

Conclusion

pgBackRest provides enterprise-grade backup and recovery capabilities for PostgreSQL environments. Key benefits include:

Operational Benefits:

Parallel processing for faster backups and restores
Incremental backups reduce storage and network overhead
Point-in-time recovery for precise data recovery
Multiple repositories for redundancy and geographic distribution
Automated verification ensures backup integrity

Implementation Best Practices:

Comprehensive configuration with appropriate retention policies
Regular monitoring of backup operations and repository health
Automated testing of restore procedures
Security hardening with proper permissions and encryption
Documentation of disaster recovery procedures

Production Considerations:

Performance tuning based on system resources and requirements
Capacity planning for backup storage growth
Regular testing of disaster recovery scenarios
Monitoring and alerting for proactive issue detection
Staff training on emergency procedures

For expert assistance with pgBackRest implementation and PostgreSQL backup strategies, our team provides comprehensive PostgreSQL consulting services and backup solution design.

Need help implementing pgBackRest for your PostgreSQL environment? Our certified PostgreSQL experts can help you design and implement robust backup and recovery solutions. Contact us for a free consultation.

Written by

Abhishek

KEEP LEARNING WITH THEDBADMIN

COURSENextGen DBA + AI Engineer BootcampProduction PostgreSQL, tuning, HA, pgvector & AIEnroll →PDF · FreePostgreSQL ArchitecturePDF · 23 pagesBuy now →

Keep reading

Featured course · New batch

NextGen DBA + AI Engineer Bootcamp

Instructor-led, lab-first training for production DBAs: PostgreSQL, MongoDB, Docker, Kubernetes and AI.

Live labs on real execution plans
Backup, PITR & HA in production
pgvector, RAG & AI for DBAs

Explore the Courses →All courses

🛒 FROM THE SHOP

PostgreSQL Architecture

PDF · 23 pages

Free

View product

PostgreSQL pgBackRest: Complete Backup and Recovery Guide

Table of Contents

Introduction to pgBackRest

Why pgBackRest?

Key Features

Installation and Setup

Installation on Ubuntu/Debian

Installation on CentOS/RHEL

Docker Setup

Configuration

PostgreSQL Configuration

pgBackRest Configuration

Initialize Stanza

Setup Archive Command

Backup Strategies

Full Backup

Incremental and Differential Backups

Automated Backup Schedule

Advanced Backup Configuration

Recovery and PITR

Full Database Restore

Point-in-Time Recovery (PITR)

Selective Restore

Advanced Recovery Scenarios

Recovery Monitoring

Advanced Features

Multiple Repositories

Standby Configuration

Backup Verification

Encryption Configuration

Monitoring and Maintenance

Monitoring Scripts

Performance Monitoring

Backup Reports

Production Best Practices

Security Hardening

Performance Optimization

Disaster Recovery Planning

Scenario 2: Corruption Detected

Scenario 3: Accidental Data Loss

Contact Information

Capacity Planning

Conclusion

Operational Benefits:

Implementation Best Practices:

Production Considerations:

Turn this guide into a skill you can prove

Related articles

NextGen DBA + AI Engineer Bootcamp

🛒 FROM THE SHOP