Supabase Database Backups — Automatic and Manual Backup Strategies

# automatic_backups.py
# Understanding Supabase automatic backup schedule

def backup_schedule():
    schedules = {
        "Free Tier": "Daily full backup, 7-day retention",
        "Pro Plan": "Daily full backup, 14-day retention",
        "Team Plan": "Daily full backup, 30-day retention",
        "Enterprise": "Custom backup schedule and retention",
    }
    
    print("Automatic Backup Schedule:")
    for plan, schedule in schedules.items():
        print(f"  {plan:15s} | {schedule}")
    
    print()
    print("Backup content:")
    print("  - Full database dump (all tables, indexes, functions)")
    print("  - Does NOT include storage files or auth config")
    print("  - Backups stored in Supabase-managed storage")

backup_schedule()

# Get your database connection string from Supabase Dashboard:
# Settings > Database > Connection string (URI)

# Download full database backup
pg_dump "postgresql://postgres:password@db.example.supabase.co:5432/postgres" \
  --format=custom \
  --file=supabase-backup.dump

# Download as plain SQL
pg_dump "postgresql://postgres:password@db.example.supabase.co:5432/postgres" \
  --format=plain \
  --file=supabase-backup.sql

# Compress the backup
gzip supabase-backup.dump

# manual_backup.py
# Manual backup process

def manual_backup_steps():
    print("Manual Backup Steps:")
    print()
    print("1. Get database connection string from Settings > Database")
    print("2. Run pg_dump with the connection string")
    print("3. Choose format: custom (restorable) or plain (readable)")
    print("4. Compress the output file")
    print("5. Store in a secure, separate location")
    print()
    print("Security notes:")
    print("  - Backup files contain ALL database data")
    print("  - Encrypt backup files before storing externally")
    print("  - Never commit backup files to version control")

manual_backup_steps()

# pitr.py
# Point-in-time recovery concept

def pitr_explanation():
    print("Point-in-Time Recovery (PITR):")
    print()
    print("Available on: Pro plan and above")
    print("Retention: Up to 7 days (Pro)")
    print()
    print("How it works:")
    print("  1. Supabase takes periodic base backups")
    print("  2. WAL (Write-Ahead Log) files are archived continuously")
    print("  3. You can restore to ANY point in time")
    print("  4. WAL files contain every database change")
    print()
    print("Use PITR when:")
    print("  - Accidental DELETE or UPDATE without WHERE clause")
    print("  - Schema migration error")
    print("  - Data corruption from application bug")
    print("  - Ransomware or malicious data modification")

pitr_explanation()

# In Supabase Dashboard:
# 1. Go to Database > Backups
# 2. Click "Restore" on the backup you want
# 3. Confirm the restoration
# 4. Wait for the process to complete

# For manual restore with pg_restore:
pg_restore --clean --if-exists \
  --dbname "postgresql://postgres:password@db.example.supabase.co:5432/postgres" \
  supabase-backup.dump

# restore_checklist.py
# Restoration checklist

def restore_checklist():
    print("Restoration Checklist:")
    print()
    print("Before restore:")
    print("  [ ] Notify users of scheduled downtime")
    print("  [ ] Take a current backup (in case you need to revert)")
    print("  [ ] Verify the backup file is not corrupted")
    print("  [ ] Check backup timestamp matches expected data state")
    print()
    print("During restore:")
    print("  [ ] The database is unavailable during restore")
    print("  [ ] Do not make any changes to the database")
    print("  [ ] Monitor the restore progress")
    print()
    print("After restore:")
    print("  [ ] Verify data integrity with test queries")
    print("  [ ] Check application connectivity")
    print("  [ ] Notify users that service is restored")

restore_checklist()

import subprocess
import datetime
import os

def backup_database():
    timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
    filename = f"backup_{timestamp}.dump.gz"
    
    print(f"Starting backup: {filename}")
    
    # Step 1: pg_dump
    print("Step 1: Running pg_dump...")
    # subprocess.run(["pg_dump", "--format=custom", "-f", f"backup_{timestamp}.dump", conn_string])
    
    # Step 2: Compress
    print("Step 2: Compressing backup...")
    # subprocess.run(["gzip", f"backup_{timestamp}.dump"])
    
    # Step 3: Upload to storage
    print("Step 3: Uploading to storage bucket...")
    
    # Step 4: Cleanup old backups
    print("Step 4: Cleaning backups older than 30 days...")
    
    print(f"Backup complete: {filename}")

backup_database()