Supabase Remote Backup Script

A comprehensive bash script to backup your remote Supabase instance, including database schemas, data, RLS policies, functions, storage metadata, and more.

⚠️ Important Notice

Tested Environment: This script has been developed and tested with self-hosted Supabase instances running on Coolify (local/VPS deployments).

Cloud Supabase Compatibility: While the script should work with official Supabase Cloud instances, it has not been tested in that environment. If you're using Supabase Cloud:

• Connection methods may differ
• Direct database access might be restricted
• SSH tunneling might not be available
• Some features may not work as expected

Use at your own risk and please test with non-critical data first. Contributions and feedback from cloud users are welcome!

Features

• Complete Database Backup: Backs up all schemas (public, auth, storage)
• Multiple Formats: Creates both custom binary dump and human-readable SQL files
• RLS Policies: Exports Row Level Security policies
• Functions & Procedures: Backs up all custom functions and stored procedures
• Storage Metadata: Saves storage bucket configurations
• SSH Tunnel Support: Secure connection through SSH tunnel
• Automatic Compression: Creates compressed archives (tar.gz or zip)
• Restore Capability: Easy restoration from backups
• Cross-Platform: Works on macOS, Linux, and Windows (Git Bash/Cygwin)

Prerequisites

Required Tools

1. PostgreSQL Client Tools (pg_dump, psql, pg_restore)

   • macOS: brew install postgresql@15
   • Ubuntu/Debian: sudo apt-get install postgresql-client
   • Fedora/CentOS: sudo yum install postgresql
   • Windows: Download from postgresql.org

2. curl (for Storage API calls)

   • Usually pre-installed on macOS/Linux
   • Windows: Included in Git Bash

3. SSH access (optional, only if using SSH tunnel)

Supabase Requirements

• Database credentials (host, port, database name, user, password)
• Supabase project URL
• Service role key (for Storage backup)
• SSH access to server (if using SSH tunnel)

Installation

1. Clone or download this repository

   git clone https://github.com/ykarateke/supabase-backup-script.git
   cd supabase-backup-script

2. Make the script executable

   chmod +x supabase-backup.sh

3. Create your configuration file

   cp config.sh.example config.sh

4. Edit config.sh with your settings

   nano config.sh

   or use your favorite text editor.

Configuration

Edit config.sh and fill in your Supabase details:

Database Connection

# For direct connection: use your server IP
DB_HOST="your-server-ip"

# For SSH tunnel: use "localhost" after tunnel is established
# DB_HOST="localhost"

DB_PORT="5432"
DB_NAME="postgres"
DB_USER="postgres"
DB_PASSWORD="your-database-password"

Supabase API Settings

Get these from your Supabase project settings (Settings → API):

SUPABASE_URL="https://your-project-id.supabase.co"
SUPABASE_SERVICE_KEY="your-service-role-key"

SSH Tunnel Settings (Optional)

Only required if connecting through SSH tunnel:

SSH_HOST="your-server-ip"
SSH_USER="root"
POSTGRES_INTERNAL_IP="10.0.0.1"  # Internal PostgreSQL IP from docker network

To find your PostgreSQL internal IP:

ssh root@your-server-ip
docker inspect supabase-db | grep IPAddress

PostgreSQL Path (macOS only)

Uncomment the appropriate line in config.sh:

# macOS Intel:
export PATH="/usr/local/opt/postgresql@15/bin:$PATH"

# macOS Apple Silicon:
export PATH="/opt/homebrew/opt/postgresql@15/bin:$PATH"

Usage

Run a Backup

./supabase-backup.sh

This will:

1. Connect to your Supabase database (via SSH tunnel if configured)
2. Create timestamped backup directory
3. Export database, schemas, policies, functions, and metadata
4. Compress everything into a tar.gz archive
5. Open the backup folder automatically

Test Connection

./supabase-backup.sh test

Tests database connectivity without performing backup.

Restore from Backup

./supabase-backup.sh restore /path/to/backup/full_backup.dump

⚠️ Warning: This will overwrite existing data! You'll be prompted for confirmation.

Get Help

./supabase-backup.sh help

Backup Structure

supabase_backups/
├── YYYYMMDD_HHMMSS/
│   ├── database/
│   │   ├── full_backup.dump      # Binary format (for pg_restore)
│   │   └── full_backup.sql       # SQL format (human-readable)
│   ├── migrations/
│   │   └── schema.sql            # Schema structure only
│   ├── metadata/
│   │   ├── policies.sql          # RLS policies
│   │   ├── functions.sql         # Custom functions
│   │   ├── tables.txt            # Table list
│   │   └── indexes.txt           # Index list
│   ├── storage/
│   │   └── buckets.json          # Storage bucket config
│   └── logs/
│       └── database_backup.log   # Detailed backup log
└── supabase_backup_YYYYMMDD_HHMMSS.tar.gz  # Compressed archive

Connection Methods

Method 1: Direct Connection

Simplest method if your database is publicly accessible:

# In config.sh:
DB_HOST="your-server-ip"  # or your-database-host.com
DB_PORT="5432"

Method 2: SSH Tunnel (Recommended for Security)

More secure, connects through SSH:

# In config.sh:
DB_HOST="your-server-ip"  # Initially set to server IP
SSH_HOST="your-server-ip"
SSH_USER="root"
POSTGRES_INTERNAL_IP="10.0.0.1"  # Found via: docker inspect supabase-db

The script will automatically:

1. Create SSH tunnel on port 5432
2. Switch DB_HOST to localhost
3. Connect through the tunnel
4. Close tunnel when done

Method 3: Manual SSH Tunnel

Set up tunnel manually before running script:

# In terminal 1: Create tunnel
ssh -L 5432:10.0.0.1:5432 root@your-server-ip

# In terminal 2: Run backup
# In config.sh set:
DB_HOST="localhost"
# Leave SSH settings empty

./supabase-backup.sh

Scheduled Backups (Cron)

To run automatic backups, add to crontab:

# Edit crontab
crontab -e

# Add line for daily backup at 2 AM:
0 2 * * * /path/to/supabase-backup/supabase-backup.sh >> /path/to/supabase-backup/cron.log 2>&1

# Or weekly on Sundays at 3 AM:
0 3 * * 0 /path/to/supabase-backup/supabase-backup.sh >> /path/to/supabase-backup/cron.log 2>&1

For macOS, you may need to use launchd instead of cron.

Troubleshooting

"pg_dump not found"

Install PostgreSQL client tools:

• macOS: brew install postgresql@15
• Linux: sudo apt-get install postgresql-client

"Cannot connect to database"

1. Check your credentials in config.sh
2. Test connection: ./supabase-backup.sh test
3. Verify database host is accessible: ping your-database-host
4. Check firewall settings allow port 5432

"SSH connection failed"

1. Test SSH manually: ssh root@your-server-ip
2. Ensure you can connect without script
3. Check SSH_HOST, SSH_USER in config.sh
4. Verify SSH key is set up or you can enter password

"Storage metadata failed"

1. Verify SUPABASE_URL in config.sh
2. Check SUPABASE_SERVICE_KEY is correct
3. Ensure service role key has proper permissions

Permission Denied

Make script executable:

chmod +x supabase-backup.sh

"config.sh not found"

Create config file:

cp config.sh.example config.sh
nano config.sh

Security Best Practices

1. Never commit config.sh to version control - It contains sensitive credentials
2. Use .gitignore to exclude config.sh and backups
3. Restrict file permissions:

   chmod 600 config.sh  # Only owner can read/write

4. Store backups securely - Encrypt or move to secure location
5. Use SSH tunnel instead of direct connection when possible
6. Rotate service role keys periodically
7. Keep backup files encrypted if storing long-term

What Gets Backed Up

✅ Included

• All tables and data in public, auth, and storage schemas
• Table structures and relationships
• Row Level Security (RLS) policies
• Custom functions and stored procedures
• Triggers
• Indexes
• Sequences
• Storage bucket configurations
• Database roles and permissions (structure only)

❌ Not Included

• Actual storage files (images, documents, etc.) - only bucket metadata
• System tables and PostgreSQL internals
• Realtime subscriptions
• Edge Functions code
• Environment variables

Restoring to a New Supabase Instance

1. Create new Supabase project

2. Update config.sh with new project credentials

3. Restore database structure and data:

   ./supabase-backup.sh restore /path/to/backup/full_backup.dump

4. Manually recreate:

   • Storage buckets (use storage/buckets.json as reference)
   • Edge Functions
   • Environment variables
   • API keys and secrets

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT License - feel free to use and modify as needed.

Support

If you encounter issues:

1. Check the troubleshooting section above
2. Review logs in supabase_backups/[timestamp]/logs/
3. Open an issue with error details

Changelog

Version 1.0.0

• Initial release
• Database backup with multiple formats
• RLS policies and functions export
• Storage metadata backup
• SSH tunnel support
• Cross-platform compatibility
• Automatic compression
• Restore functionality