Database Backups with pgBackRest¶

pgBackRest provides reliable, compressed, and encrypted backups for PostgreSQL with integration into Blacklake's metadata system.

Architecture¶

graph TB
    subgraph "PostgreSQL Cluster"
        PG0[Patroni PG0<br/>Primary]
        PG1[Patroni PG1<br/>Replica]
    end

    subgraph "Backup System"
        PBR[pgBackRest]
        CRON[Backup Cron]
    end

    subgraph "Storage"
        S3[S3/MinIO<br/>Backup Storage]
    end

    subgraph "Metadata"
        BL[Blacklake<br/>Metadata Registry]
    end

    PG0 --> PBR
    PG1 --> PBR
    PBR --> S3
    CRON --> PBR
    PBR --> BL

pgBackRest Configuration¶

Global Settings¶

[global]
repo1-type=s3
repo1-path=/blacklake/backups/postgres
repo1-s3-endpoint=${S3_ENDPOINT}
repo1-s3-region=${S3_REGION}
repo1-s3-bucket=${S3_BUCKET}
repo1-s3-key=${AWS_ACCESS_KEY_ID}
repo1-s3-key-secret=${AWS_SECRET_ACCESS_KEY}
repo1-s3-verify-tls=n
process-max=4
start-fast=y
compress-type=zstd
compress-level=6
delta=y
spool-path=/var/spool/pgbackrest
retention-full=4
retention-diff=14
repo1-retention-full-type=time

Database Configuration¶

[blacklake]
pg1-path=/var/lib/postgresql/data
pg1-port=5432
pg1-user=postgres
pg1-host=patroni-pg0

Backup Types¶

Full Backups¶

Complete Database: All data and WAL files
Frequency: Weekly (configurable)
Retention: 4 full backups
Compression: Zstandard (zstd) level 6

Differential Backups¶

Incremental: Changes since last full backup
Frequency: Daily
Retention: 14 differential backups
Size: Smaller than full backups

Incremental Backups¶

WAL Archiving: Continuous WAL file archiving
Point-in-Time Recovery: Support for PITR
Frequency: Continuous (via archive_command)

Backup Process¶

1. Initialize pgBackRest¶

# Create stanza
pgbackrest --stanza=blacklake --log-level-console=info stanza-create

# Verify configuration
pgbackrest --stanza=blacklake check

2. Full Backup¶

# Create full backup
pgbackrest --stanza=blacklake --type=full backup

# Check backup info
pgbackrest --stanza=blacklake info

3. Differential Backup¶

# Create differential backup
pgbackrest --stanza=blacklake --type=diff backup

4. Incremental Backup¶

# Create incremental backup
pgbackrest --stanza=blacklake --type=incr backup

Blacklake Integration¶

Backup Registration Script¶

#!/usr/bin/env bash
set -euo pipefail

# Create backup
pgbackrest --stanza=blacklake --type=full backup

# Generate manifest
ts=$(date -u +%Y%m%dT%H%M%SZ)
backup_dir="backups/blacklake"
manifest_file="${backup_dir}/full-${ts}.json"

mkdir -p "$backup_dir"
pgbackrest --stanza=blacklake info --output=json > "$manifest_file"

# Create JSON-LD metadata
blmeta=".bl/objects/backup-${ts}.jsonld"
mkdir -p .bl/objects

cat > "$blmeta" <<EOF
{
  "@context": "../contexts/blacklake.context.jsonld",
  "id": "bl:backup:${ts}",
  "type": ["Object", "dcterms:Dataset"],
  "title": "Postgres full backup ${ts}",
  "description": "pgBackRest full backup with WAL, stanza blacklake",
  "mime": "application/json",
  "size": $(wc -c < "$manifest_file"),
  "checksum": "$(sha256sum "$manifest_file" | awk '{print $1}')",
  "algo": "sha256",
  "created": "$(date -u +%Y-%m-%dT%H:%M:%SZ)",
  "keyword": ["backup", "postgres", "pgbackrest", "blacklake"]
}
EOF

# Register in Blacklake
blacklake meta add "$manifest_file" \
    --title "Postgres full backup ${ts}" \
    --description "pgBackRest full backup with WAL, stanza blacklake" \
    --creator "pgBackRest Backup System" \
    --keywords "backup,postgres,pgbackrest,blacklake"

blacklake add "$manifest_file"
blacklake commit -m "backup: full ${ts}"
blacklake push

Automated Scheduling¶

# Add to crontab
# Full backup weekly (Sunday 2 AM)
0 2 * * 0 /scripts/backup.sh

# Differential backup daily (2 AM)
0 2 * * 1-6 /scripts/backup-diff.sh

# Cleanup old backups (3 AM)
0 3 * * * /scripts/backup-cleanup.sh

Restore Procedures¶

Point-in-Time Recovery (PITR)¶

# Stop PostgreSQL
systemctl stop postgresql

# Restore to specific time
pgbackrest --stanza=blacklake restore \
    --target-time="2024-01-15 14:30:00" \
    --target-action=promote

# Start PostgreSQL
systemctl start postgresql

Full Restore¶

# Stop PostgreSQL
systemctl stop postgresql

# Restore latest full backup
pgbackrest --stanza=blacklake restore

# Start PostgreSQL
systemctl start postgresql

Restore to Different Location¶

# Restore to different directory
pgbackrest --stanza=blacklake restore \
    --pg1-path=/var/lib/postgresql/restore

Monitoring¶

Backup Status¶

# Check backup status
pgbackrest --stanza=blacklake info

# List available backups
pgbackrest --stanza=blacklake info --output=json

Blacklake Metadata¶

# List backup metadata
blacklake search "backup postgres"

# Get specific backup info
blacklake meta export --format json | jq '.backup'

Health Checks¶

# Verify backup integrity
pgbackrest --stanza=blacklake check

# Test restore (dry run)
pgbackrest --stanza=blacklake restore --dry-run

Troubleshooting¶

Common Issues¶

1. Archive Command Failures¶

Symptoms: WAL files not being archived Resolution:

# Check archive command
psql -c "SHOW archive_command;"

# Test archive command manually
pgbackrest --stanza=blacklake archive-push /path/to/wal/file

2. S3 Connection Issues¶

Symptoms: Backup failures to S3 Resolution:

# Test S3 connectivity
aws s3 ls s3://blacklake-backups/

# Check credentials
pgbackrest --stanza=blacklake check

3. Insufficient Space¶

Symptoms: Backup failures due to space Resolution:

# Check available space
df -h /var/spool/pgbackrest

# Clean old backups
pgbackrest --stanza=blacklake expire

Debugging¶

Enable Verbose Logging¶

# Verbose backup
pgbackrest --stanza=blacklake --log-level-console=debug backup

# Check logs
tail -f /var/log/pgbackrest/blacklake-backup.log

Test Configuration¶

# Validate configuration
pgbackrest --stanza=blacklake check

# Test stanza creation
pgbackrest --stanza=blacklake stanza-create --dry-run

Security¶

Encryption¶

# Enable encryption
cipher-type=aes-256-cbc
cipher-pass=your-encryption-key

Access Control¶

# Restrict pgBackRest access
chmod 600 /etc/pgbackrest/pgbackrest.conf
chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

S3 Security¶

# Use IAM roles instead of keys
repo1-s3-key-type=role
repo1-s3-role=pgbackrest-role

Performance Tuning¶

Compression¶

# Adjust compression level
compress-type=zstd
compress-level=6  # 1-22, higher = better compression

Parallel Processing¶

# Increase parallel processes
process-max=4

Network Optimization¶

# Adjust network settings
repo1-s3-uri-style=path
repo1-s3-endpoint=https://s3.amazonaws.com

Best Practices¶

Backup Strategy¶

Full Backups: Weekly with 4-week retention
Differential Backups: Daily with 2-week retention
WAL Archiving: Continuous for PITR
Testing: Regular restore tests

Monitoring¶

Backup Success: Monitor backup completion
Storage Usage: Track backup storage consumption
Performance: Monitor backup duration
Integrity: Regular backup verification

Documentation¶

Recovery Procedures: Document restore steps
Contact Information: Backup team contacts
Escalation: Define backup failure procedures

Next Steps¶

PostgreSQL HA - High availability setup
Pooling - Connection pooling configuration
Observability - Monitoring and alerting