Fairdb Setup Backup

You are configuring pgBackRest with Wasabi S3 storage for automated PostgreSQL backups. Follow SOP-003 precisely.

Prerequisites Check

Verify before starting:

1. PostgreSQL 16 is installed and running
2. Wasabi S3 account is active with bucket created
3. AWS CLI credentials are available
4. At least 50GB free disk space for local backups

Step 1: Install pgBackRest

# Add pgBackRest repository
sudo apt-get install -y software-properties-common
sudo add-apt-repository -y ppa:pgbackrest/backrest
sudo apt-get update

# Install pgBackRest
sudo apt-get install -y pgbackrest

# Verify installation
pgbackrest version

Step 2: Configure Wasabi S3 Credentials

# Create pgBackRest configuration directory
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /var/lib/pgbackrest
sudo mkdir -p /var/log/pgbackrest
sudo mkdir -p /var/spool/pgbackrest

# Set ownership
sudo chown -R postgres:postgres /var/lib/pgbackrest
sudo chown -R postgres:postgres /var/log/pgbackrest
sudo chown -R postgres:postgres /var/spool/pgbackrest

# Store Wasabi credentials (secure these!)
export WASABI_ACCESS_KEY="YOUR_WASABI_ACCESS_KEY"
export WASABI_SECRET_KEY="YOUR_WASABI_SECRET_KEY"
export WASABI_BUCKET="fairdb-backups"
export WASABI_REGION="us-east-1"  # Or your Wasabi region
export WASABI_ENDPOINT="s3.us-east-1.wasabisys.com"  # Adjust for your region

Step 3: Create pgBackRest Configuration

# Create main configuration file
sudo tee /etc/pgbackrest/pgbackrest.conf << EOF
[global]
# General Options
process-max=4
log-level-console=info
log-level-file=detail
start-fast=y
stop-auto=y
archive-async=y
archive-push-queue-max=4GB
spool-path=/var/spool/pgbackrest

# S3 Repository Configuration
repo1-type=s3
repo1-s3-endpoint=${WASABI_ENDPOINT}
repo1-s3-bucket=${WASABI_BUCKET}
repo1-s3-region=${WASABI_REGION}
…

Step 4: Configure PostgreSQL for pgBackRest

# Update PostgreSQL configuration
sudo tee -a /etc/postgresql/16/main/postgresql.conf << 'EOF'

# pgBackRest Archive Configuration
archive_mode = on
archive_command = 'pgbackrest --stanza=fairdb archive-push %p'
archive_timeout = 60
max_wal_senders = 3
wal_level = replica
wal_log_hints = on
EOF

# Restart PostgreSQL
sudo systemctl restart postgresql

Step 5: Initialize Backup Stanza

# Create the stanza
sudo -u postgres pgbackrest --stanza=fairdb stanza-create

# Verify stanza
sudo -u postgres pgbackrest --stanza=fairdb check

Step 6: Create Backup Scripts

# Full backup script
sudo tee /opt/fairdb/scripts/backup-full.sh << 'EOF'
#!/bin/bash
set -e

LOG_FILE="/var/log/fairdb/backup-full-$(date +%Y%m%d-%H%M%S).log"
echo "Starting full backup at $(date)" | tee -a $LOG_FILE

# Perform full backup to both repositories
sudo -u postgres pgbackrest --stanza=fairdb --type=full --repo=1 backup 2>&1 | tee -a $LOG_FILE
sudo -u postgres pgbackrest --stanza=fairdb --type=full --repo=2 backup 2>&1 | tee -a $LOG_FILE

# Verify backup
sudo -u postgres pgbackrest --stanza=fairdb --repo=1 info 2>&1 | tee -a $LOG_FILE

echo "Full backup completed at $(date)" | tee -a $LOG_FILE

# Send notification (implement webhook/email here)
…

Step 7: Schedule Automated Backups

# Add to root's crontab for automated backups
cat << 'EOF' | sudo tee /etc/cron.d/fairdb-backups
# FairDB Automated Backup Schedule
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# Weekly full backup (Sunday 2 AM)
0 2 * * 0 root /opt/fairdb/scripts/backup-full.sh

# Daily differential backup (Mon-Sat 2 AM)
0 2 * * 1-6 root /opt/fairdb/scripts/backup-differential.sh

# Hourly incremental backup (business hours)
0 9-18 * * 1-5 root /opt/fairdb/scripts/backup-incremental.sh

# Backup verification (daily at 5 AM)
0 5 * * * postgres pgbackrest --stanza=fairdb --repo=1 check
…

Step 8: Create Restore Procedures

# Point-in-time recovery script
sudo tee /opt/fairdb/scripts/restore-pitr.sh << 'EOF'
#!/bin/bash
# FairDB Point-in-Time Recovery Script

if [ $# -ne 1 ]; then
    echo "Usage: $0 'YYYY-MM-DD HH:MM:SS'"
    exit 1
fi

TARGET_TIME="$1"
BACKUP_PATH="/var/lib/postgresql/16/main"

echo "WARNING: This will restore the database to $TARGET_TIME"
echo "Current data will be LOST. Continue? (yes/no)"
read CONFIRM

if [ "$CONFIRM" != "yes" ]; then
…

Step 9: Test Backup and Restore

# Perform test backup
sudo -u postgres pgbackrest --stanza=fairdb --type=full backup

# Check backup info
sudo -u postgres pgbackrest --stanza=fairdb info

# List backups
sudo -u postgres pgbackrest --stanza=fairdb info --output=json

# Test restore to alternate location
sudo mkdir -p /tmp/pgbackrest-test
sudo chown postgres:postgres /tmp/pgbackrest-test
sudo -u postgres pgbackrest --stanza=fairdb \
    --pg1-path=/tmp/pgbackrest-test \
    --type=latest \
    restore

Step 10: Monitor Backup Health

# Create monitoring script
sudo tee /opt/fairdb/scripts/check-backup-health.sh << 'EOF'
#!/bin/bash
# FairDB Backup Health Check

# Check last backup time
LAST_BACKUP=$(sudo -u postgres pgbackrest --stanza=fairdb info --output=json | \
    jq -r '.[] | .backup[-1].timestamp.stop')

# Convert to seconds
LAST_BACKUP_EPOCH=$(date -d "$LAST_BACKUP" +%s)
CURRENT_EPOCH=$(date +%s)
HOURS_AGO=$(( ($CURRENT_EPOCH - $LAST_BACKUP_EPOCH) / 3600 ))

# Alert if backup is older than 25 hours
if [ $HOURS_AGO -gt 25 ]; then
    echo "ALERT: Last backup was $HOURS_AGO hours ago!"
    # Send alert (implement notification here)
…