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

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
curl (for Storage API calls)
- Usually pre-installed on macOS/Linux
- Windows: Included in Git Bash
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

Clone or download this repository

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

Make the script executable
```
chmod +x supabase-backup.sh
```
Create your configuration file
```
cp config.sh.example config.sh
```
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:

Connect to your Supabase database (via SSH tunnel if configured)
Create timestamped backup directory
Export database, schemas, policies, functions, and metadata
Compress everything into a tar.gz archive
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:

Create SSH tunnel on port 5432
Switch DB_HOST to localhost
Connect through the tunnel
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"

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

"SSH connection failed"

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

"Storage metadata failed"

Verify SUPABASE_URL in config.sh
Check SUPABASE_SERVICE_KEY is correct
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

Never commit config.sh to version control - It contains sensitive credentials
Use .gitignore to exclude config.sh and backups

Restrict file permissions:

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

Store backups securely - Encrypt or move to secure location
Use SSH tunnel instead of direct connection when possible
Rotate service role keys periodically
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

Create new Supabase project
Update config.sh with new project credentials

Restore database structure and data:

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

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:

Check the troubleshooting section above
Review logs in supabase_backups/[timestamp]/logs/
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

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
.gitignore		.gitignore
README.md		README.md
config.sh.example		config.sh.example
supabase-backup.sh		supabase-backup.sh

ykarateke/supabase-backup-script

Folders and files

Latest commit

History

Repository files navigation