Backup & Restore Procedures

Scheduled pg_dump via cron or pg_cron, S3/R2 upload for off-site storage, point-in-time restore walkthrough, and testing restore in a staging environment before needing it in production.

Backup and Restore

What you'll learn

Implement a daily automated backup strategy, test restoration, and configure point-in-time recovery for production.

What Needs Backing Up

Data	Importance	Backup method
PostgreSQL database	Critical	pg_dump + WAL
`.env` file	Critical	Secret manager / offline storage
`docker-compose.yml`	High	Git repository
SSL certificates	High	Let's Encrypt auto-renews
Agent prompts	Medium	In database (covered by pg_dump)

Agent prompts, issues, and cost events are all in PostgreSQL — one database backup covers everything.

Daily Automated Backup

#!/bin/bash
# /etc/cron.daily/paperclip-backup

BACKUP_DIR="/backups/paperclip"
DATE=$(date +%Y-%m-%d)
KEEP_DAYS=30

mkdir -p "$BACKUP_DIR"

# Dump the database
docker compose exec -T postgres pg_dump \
  -U paperclip \
  -Fc \                    # custom format (compressed)
  paperclip \
  > "$BACKUP_DIR/paperclip-$DATE.dump"

# Verify the dump
pg_restore --list "$BACKUP_DIR/paperclip-$DATE.dump" > /dev/null \
  && echo "Backup OK: paperclip-$DATE.dump" \
  || { echo "BACKUP FAILED"; exit 1; }

# Upload to S3/Backblaze
aws s3 cp "$BACKUP_DIR/paperclip-$DATE.dump" \
  "s3://your-bucket/paperclip-backups/paperclip-$DATE.dump"

# Remove old local backups
find "$BACKUP_DIR" -name "*.dump" -mtime +$KEEP_DAYS -delete

Make executable:

chmod +x /etc/cron.daily/paperclip-backup

Testing a Restore

Test this monthly — a backup you've never restored is worth nothing:

# Restore to a test database
pg_restore \
  -U postgres \
  --clean \
  --if-exists \
  -d paperclip_test \
  "/backups/paperclip/paperclip-2024-01-15.dump"

# Verify a few rows
psql -U postgres -d paperclip_test -c "SELECT COUNT(*) FROM issues;"
psql -U postgres -d paperclip_test -c "SELECT name FROM companies;"

Point-in-Time Recovery (PITR)

For production, enable WAL archiving:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://your-bucket/wal/%f'
restore_command = 'aws s3 cp s3://your-bucket/wal/%f %p'

With WAL archiving, you can restore to any second in the database's history:

# In recovery.conf (or postgresql.conf in PG12+)
restore_command = 'aws s3 cp s3://your-bucket/wal/%f %p'
recovery_target_time = '2024-01-15 14:30:00 UTC'

Supabase Managed Backups

If you're using Supabase as your PostgreSQL host, daily backups are included at the Pro tier and above. No setup needed — restore from the Supabase Dashboard.

Checkpoint ✓

[ ] You have a backup script that runs daily and uploads to remote storage
[ ] You know how to restore from a .dump file to a test database
[ ] You understand the difference between full backups and PITR