Skip to content
PLANOVI Docs

Backup Automation & Disaster Recovery

Database backups run automatically every night to guarantee zero data loss in the event of catastrophic server hardware failure or accidental data deletion.

⏰ Nightly Automated Backups (backup_databases.sh)

Automated database backups are scheduled via the root user’s crontab:

Terminal window
# Runs daily at 02:00 AM UTC
0 2 * * * /opt/vps-stack/scripts/backup_databases.sh > /dev/null 2>&1

Script Workflow

The script /opt/vps-stack/scripts/backup_databases.sh:

  1. Creates the target directory /opt/vps-stack/backups if missing.
  2. Extracts live PostgreSQL dumps via docker exec -t supabase-db pg_dump -U postgres postgres.
  3. Compresses the raw SQL dump stream using gzip -9 to minimize disk footprint: 
    /opt/vps-stack/backups/planovi_db_backup_YYYY-MM-DD_HHMMSS.sql.gz
  4. Enforces a 14-day retention policy by deleting archive files older than 14 days (find ... -mtime +14 -delete).
  5. Logs execution metrics and backup file sizes.

💾 Manual Snapshot Execution

Before performing major database schema migrations or Edge Function refactorings, create an immediate manual snapshot:

Terminal window
# Execute immediate backup
sudo /opt/vps-stack/scripts/backup_databases.sh


# Verify the newly created archive
ls -lh /opt/vps-stack/backups/

🔄 Disaster Recovery: Restoring a Database Backup

Follow this runbook to restore a PostgreSQL snapshot onto supabase-db:

Step 1: Identify Backup File

Terminal window
cd /opt/vps-stack/backups
ls -lth *.sql.gz
# Example: planovi_db_backup_2026-09-20_020001.sql.gz

Step 2: Stop Dependent Containers

To prevent write conflicts and active connections during restoration:

Terminal window
cd /opt/vps-stack
docker stop supabase-postgrest supabase-auth supabase-edge-runtime

Step 3: Stream Restoration into PostgreSQL

Terminal window
gunzip -c /opt/vps-stack/backups/planovi_db_backup_2026-09-20_020001.sql.gz | \
    docker exec -i supabase-db psql -U postgres -d postgres

Step 4: Restart Stack & Verify Integrity

Terminal window
docker start supabase-postgrest supabase-auth supabase-edge-runtime


# Verify database connection and tables
docker exec -it supabase-db psql -U postgres -d postgres -c "\dt"