Supabase’s backup and point-in-time restore (PITR) capabilities are often misunderstood as just a simple "download my database" feature.
Let’s see this in action. Imagine you have a Supabase project, and you’ve accidentally deleted a critical table. You need to get it back.
# First, let's check our current backups.
# This command shows you the available backup snapshots.
supabase db list-backups
This command will output something like:
ID | NAME | CREATED_AT
---------|----------------------|-------------------------
b7d9c0b9 | auto-backup-2023-10-27 | 2023-10-27T08:00:00Z
a3f1e8d2 | auto-backup-2023-10-26 | 2023-10-26T08:00:00Z
...
Now, if you want to restore to a specific point in time, say, just before you deleted that table, you’d use a command like this. Notice we’re specifying a timestamp within the backup’s retention period.
# Restore to a specific point in time (e.g., 2023-10-27 07:59:59 UTC)
supabase db restore --from-backup b7d9c0b9 --restore-point '2023-10-27T07:59:59Z'
This command doesn’t overwrite your current database. Instead, it creates a new database instance with the data restored to that precise moment. You’ll then need to attach this restored database to your project.
The core problem Supabase’s PITR solves is data corruption or accidental deletion. Instead of just having full backups that are hours or even a day old, PITR allows you to recover your database to any point in time within your configured retention window. This is achieved by Supabase continuously archiving your database’s Write-Ahead Log (WAL) files. These WAL files are essentially a chronological record of every single change made to your database. When you request a PITR, Supabase takes the most recent full backup and then replays the relevant WAL segments up to your specified timestamp.
The key levers you control are the backup retention period and the restore point. The retention period dictates how far back you can go for a PITR. Supabase’s default is 7 days, but you can configure this. The restore point is the exact timestamp you want to rewind to.
What many users don’t realize is that while you can restore to any point in time, the process itself involves creating a new database instance. You can’t directly "rewind" your live production database. This is a safety mechanism. After a PITR, you’ll have a new database instance. You then need to:
- Verify the restored data in this new instance.
- Migrate the restored data back to your primary database, or switch your application to point to the restored instance if appropriate. This often involves using
pg_dumpandpg_restoreor similar tools to transfer data between the two databases, or performing a controlled failover.
The next problem you’ll likely encounter is managing the lifecycle of these restored database instances, especially if you perform multiple restores for testing or recovery drills.