# How to Backup a Supabase Database

- Tool: Supabase
- Difficulty: Intermediate
- Time required: 10-15 min
- Compatibility: Supabase (all plans for pg_dump, Pro+ for auto backups), PostgreSQL 15+
- Last updated: March 2026

## TL;DR

To backup a Supabase database, use pg_dump with your project's connection string to create a full SQL dump. Pro plan users also get automatic daily backups and point-in-time recovery through the Dashboard. For scheduled backups, set up a cron job that runs pg_dump and stores the output in a secure location. Always test restoring from your backups to verify they work before you need them.

## Backing Up Your Supabase Database with pg_dump and Dashboard Tools

Your Supabase project is a full PostgreSQL database, which means you can use standard PostgreSQL backup tools like pg_dump. This tutorial covers three backup strategies: manual pg_dump for on-demand backups, Supabase's built-in automatic backups for Pro+ plans, and scheduled cron jobs for automated recurring backups. You will also learn how to verify your backups work by testing a restore.

## Before you start

- A Supabase project with data you want to back up
- PostgreSQL client tools installed (pg_dump, psql) — install via brew install libpq or apt-get install postgresql-client
- Your database connection string from Dashboard > Settings > Database
- Terminal/command line access

## Step-by-step guide

### 1. Find your database connection string

Go to your Supabase Dashboard and navigate to Settings > Database. Copy the connection string under the Connection string section. Select the URI format. This string contains your host, port, database name, and credentials. For pg_dump, use the direct connection (port 5432), not the connection pooler (port 6543), because pg_dump requires a direct connection for schema operations.

```
# Connection string format:
# postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres

# Find it in Dashboard > Settings > Database > Connection string > URI
```

**Expected result:** You have the direct database connection string copied and ready to use.

### 2. Create a manual backup with pg_dump

Run pg_dump with your connection string to create a full SQL dump of your database. The --clean flag adds DROP statements before CREATE statements, making the dump restorable to a fresh database. The --if-exists flag prevents errors if objects do not exist during restore. Pipe the output to a file with a timestamp for easy identification.

```
# Full database backup to a timestamped SQL file
pg_dump "postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres" \
  --clean \
  --if-exists \
  --no-owner \
  --no-privileges \
  > backup_$(date +%Y%m%d_%H%M%S).sql

# Compressed backup (recommended for large databases)
pg_dump "postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres" \
  --clean \
  --if-exists \
  --no-owner \
  --no-privileges \
  | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz

# Schema-only backup (no data)
pg_dump "postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres" \
  --schema-only \
  > schema_$(date +%Y%m%d_%H%M%S).sql
```

**Expected result:** A .sql or .sql.gz backup file is created in your current directory.

### 3. Back up specific schemas or tables

You can back up specific schemas or tables instead of the entire database. This is useful when you only need to back up your application data (public schema) without system schemas. You can also back up individual tables if you need a quick snapshot of specific data.

```
# Back up only the public schema
pg_dump "postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres" \
  --schema=public \
  --clean \
  --if-exists \
  > public_schema_backup.sql

# Back up a specific table
pg_dump "postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres" \
  --table=public.orders \
  --data-only \
  > orders_data_backup.sql

# Back up storage metadata (bucket configs and policies)
pg_dump "postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres" \
  --schema=storage \
  > storage_schema_backup.sql
```

**Expected result:** Targeted backup files are created for the specified schemas or tables.

### 4. Use automatic daily backups on Pro plans

Supabase Pro plan and above includes automatic daily backups with 7-day retention. Go to Dashboard > Database > Backups to view available backups and their timestamps. Pro plan also offers point-in-time recovery (PITR), allowing you to restore your database to any second within the retention window. These backups are managed by Supabase and require no configuration.

```
# No code needed — automatic backups are managed by Supabase
# View backups: Dashboard > Database > Backups
# PITR: Available on Pro plan, 7-day retention window
# To restore: Contact Supabase support or use the Dashboard restore button
```

**Expected result:** Automatic backups are visible in the Dashboard with timestamps and restore options.

### 5. Schedule automated backups with cron

For production systems, set up a cron job that runs pg_dump on a schedule. This script creates compressed backups, retains only the last 7 days of backups, and can optionally upload to cloud storage. Save the script and add it to your system's crontab.

```
#!/bin/bash
# backup-supabase.sh — Run daily via cron

DB_URL="postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres"
BACKUP_DIR="/home/user/backups/supabase"
RETENTION_DAYS=7

mkdir -p $BACKUP_DIR

# Create compressed backup
pg_dump "$DB_URL" --clean --if-exists --no-owner --no-privileges \
  | gzip > "$BACKUP_DIR/backup_$(date +%Y%m%d_%H%M%S).sql.gz"

# Remove backups older than retention period
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $(date)"

# Add to crontab (run daily at 2 AM):
# crontab -e
# 0 2 * * * /home/user/scripts/backup-supabase.sh >> /home/user/logs/backup.log 2>&1
```

**Expected result:** Automated backups run daily, creating compressed SQL files with automatic cleanup of old backups.

## Complete code example

File: `backup-supabase.sh`

```bash
#!/bin/bash
# Supabase Database Backup Script
# Schedule with cron: 0 2 * * * /path/to/backup-supabase.sh

set -euo pipefail

# Configuration
DB_URL="${SUPABASE_DB_URL:?'Set SUPABASE_DB_URL environment variable'}"
BACKUP_DIR="${BACKUP_DIR:-/home/user/backups/supabase}"
RETENTION_DAYS="${RETENTION_DAYS:-7}"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Create backup directory
mkdir -p "$BACKUP_DIR"

echo "[$(date)] Starting Supabase backup..."

# Full compressed backup
pg_dump "$DB_URL" \
  --clean \
  --if-exists \
  --no-owner \
  --no-privileges \
  --format=custom \
  --file="$BACKUP_DIR/backup_${TIMESTAMP}.dump"

# Verify the backup file is not empty
BACKUP_SIZE=$(stat -f%z "$BACKUP_DIR/backup_${TIMESTAMP}.dump" 2>/dev/null || stat --printf="%s" "$BACKUP_DIR/backup_${TIMESTAMP}.dump")
if [ "$BACKUP_SIZE" -lt 1000 ]; then
  echo "[$(date)] ERROR: Backup file suspiciously small ($BACKUP_SIZE bytes)"
  exit 1
fi

echo "[$(date)] Backup created: backup_${TIMESTAMP}.dump ($BACKUP_SIZE bytes)"

# Clean up old backups
DELETED=$(find "$BACKUP_DIR" -name "backup_*.dump" -mtime +"$RETENTION_DAYS" -delete -print | wc -l)
echo "[$(date)] Cleaned up $DELETED old backup(s)"

# Optional: Upload to S3 or cloud storage
# aws s3 cp "$BACKUP_DIR/backup_${TIMESTAMP}.dump" \
#   "s3://my-backups/supabase/backup_${TIMESTAMP}.dump"

echo "[$(date)] Backup complete"
```

## Common mistakes

- **Using the connection pooler URL (port 6543) for pg_dump instead of the direct connection (port 5432)** — undefined Fix: pg_dump requires a direct connection to PostgreSQL. Use port 5432, not 6543. Check your connection string in Dashboard > Settings > Database.
- **Never testing backup restores, then discovering the backup is corrupted or incomplete when needed** — undefined Fix: Regularly test restores to a local Supabase instance (supabase start, then psql < backup.sql) or a separate test project. A backup you have not tested is not a real backup.
- **Storing backup files with database credentials in a public or unencrypted location** — undefined Fix: Store backups in an encrypted location with restricted access. Encrypt backup files with gpg if storing on disk. Never commit backup files or connection strings to version control.

## Best practices

- Run pg_dump with --clean --if-exists --no-owner --no-privileges for portable, restorable backups
- Use compressed format (--format=custom or gzip) for large databases to save storage space
- Maintain your own pg_dump backups even if you have Supabase's automatic daily backups on Pro plans
- Test restoring from backups regularly to verify integrity — at least once per month
- Store backups in a different location than your database (separate cloud provider, different region)
- Use environment variables for database credentials in backup scripts, never hardcode them
- Keep a minimum of 7 days of backup retention, with longer retention for critical production databases
- Log backup operations with timestamps so you can audit when backups ran and whether they succeeded

## Frequently asked questions

### Does Supabase automatically back up my database?

Yes, but only on Pro plans and above. Free plan projects do not have automatic backups. Pro plan includes daily backups with 7-day retention and point-in-time recovery. Regardless of your plan, you should maintain your own pg_dump backups.

### Can I use pg_dump on the free plan?

Yes. pg_dump works with any Supabase plan because it connects directly to your PostgreSQL database. The connection string is available in Dashboard > Settings > Database regardless of your plan.

### How large can a pg_dump backup file get?

It depends on your data. A database with 1 million rows might produce a 500 MB SQL file. Using --format=custom or gzip compression typically reduces the file size by 70-90%. For very large databases, consider backing up individual schemas or tables.

### Does pg_dump back up RLS policies and functions?

Yes. pg_dump includes table definitions, RLS policies, functions, triggers, indexes, and all other database objects. Use --schema-only if you want structure without data.

### Can I restore a backup to a different Supabase project?

Yes. Use psql with the target project's connection string to restore: psql 'postgresql://...' < backup.sql. Use --no-owner and --no-privileges when creating the backup to avoid permission issues.

### What about backing up Supabase Storage files?

pg_dump backs up storage metadata (bucket configurations, policies) but not the actual files stored in S3. For file backups, use the storage API to list and download files programmatically, or use the Supabase CLI.

### Can RapidDev set up automated backups for my Supabase project?

Yes. RapidDev can configure automated backup pipelines including scheduled pg_dump scripts, cloud storage uploads, backup verification, and disaster recovery procedures for your Supabase database.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-backup-supabase-database
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-backup-supabase-database