Supabase Pro tier includes nightly automated backups and 7-day point-in-time recovery (PITR). Sounds solid until you lose a month of data because someone's script corrupted 10,000 rows and nobody noticed for 3 weeks. PITR covers you for 7 days. After that, you're reading commit logs like it's a murder mystery. For SaaS with customers paying for their data, that's not a strategy — it's a lawsuit waiting to happen.
The Aidxn pattern: three backup tiers running in parallel. Tier 1 is nightly pg_dump exports to S3 — full SQL dumps you can restore instantly to any Postgres instance. Tier 2 adds weekly checksums so you can detect data drift before it spreads. Tier 3, for paranoid clients, is a read-only Postgres replica running offsite (we use Render or AWS) pulling via WAL, letting you fork the database to debug weird state without touching production.
Why Supabase's Default Backups Aren't Enough
PITR is a recovery window, not a recovery strategy. You can only restore to a point 7 days or less into the past. Corruption, cascade deletes, runaway scripts — if they happen and go unnoticed for a week, you've crossed the PITR boundary. Nightly backups exist but are locked to Supabase's infrastructure; exporting them is a manual affair. And you can't query them without spinning up a full restoration. For compliance-heavy clients (finance, healthcare), you need auditable, independent backup copies stored in your own account. For SaaS, that means S3 with versioning, checksums, and a restore playbook you've actually tested.
Tier 1: Nightly pg_dump → S3
Every night at 2am UTC, dump your entire database as SQL and upload it to S3. This takes 5–10 minutes for most SaaS databases. The dump is a point-in-time snapshot you can restore to any Postgres instance without Supabase.
-- In Supabase, create a service role user for backups (optional, limits blast radius)
create user backup_user with password 'strong-random-password';
grant connect on database postgres to backup_user;
grant usage on schema public to backup_user;
grant select on all tables in schema public to backup_user;
Deploy a Supabase Edge Function that runs nightly (triggered by pg_cron) and shells out to pg_dump:
// supabase/functions/backup-to-s3/index.ts
import { serve } from 'https://deno.land/std@0.208.0/http/server.ts';
import { S3Client, PutObjectCommand } from 'https://esm.sh/@aws-sdk/client-s3@3.500.0';
const s3 = new S3Client({ region: 'us-east-1' });
serve(async (req) => {
try {
const dbUrl = Deno.env.get('DATABASE_URL')!;
const dumpName = `backup-${new Date().toISOString().split('T')[0]}.sql`;
// Run pg_dump
const cmd = new Deno.Command('pg_dump', {
args: ['-Fc', dbUrl], // -Fc = custom format (compressed, faster restore)
stdout: 'piped',
stderr: 'piped',
});
const process = cmd.spawn();
const dump = await process.output();
if (!dump.success) {
throw new Error('pg_dump failed: ' + new TextDecoder().decode(dump.stderr));
}
// Upload to S3
const uploadCmd = new PutObjectCommand({
Bucket: Deno.env.get('BACKUP_BUCKET'),
Key: `daily-backups/${dumpName}`,
Body: dump.stdout,
ContentType: 'application/octet-stream',
});
await s3.send(uploadCmd);
return new Response(
JSON.stringify({ success: true, size: dump.stdout.byteLength, key: dumpName }),
{ status: 200, headers: { 'Content-Type': 'application/json' } }
);
} catch (error) {
console.error('Backup failed:', error);
return new Response(
JSON.stringify({ success: false, error: error.message }),
{ status: 500 }
);
}
});
Schedule it with pg_cron: `select cron.schedule('backup-to-s3', '0 2 * * *', 'select net.http_post(...)')`. Your S3 bucket has versioning enabled, so older backups aren't deleted. A month of nightly dumps costs ~$5 in S3 storage.
Tier 2: Weekly Checksum Validation
Every Sunday, calculate checksums of critical tables (users, payments, orders) and compare against last week. If a table's row count or data hash changed unexpectedly, you know something broke mid-week. This catches data drift before backups overwrite the evidence.
-- Create a checksums table
create table backup_checksums (
id uuid primary key default gen_random_uuid(),
table_name text not null,
row_count bigint,
data_hash text,
checked_at timestamp default now()
);
-- Edge Function runs weekly (Sundays at 3am UTC)
// supabase/functions/weekly-checksum/index.ts
const tables = ['users', 'payments', 'orders', 'products'];
for (const table of tables) {
const { data, error } = await supabase
.from(table)
.select('id', { count: 'exact' });
if (error) continue;
// Simple hash: count + max id (detects adds/deletes)
const maxId = data[data.length - 1]?.id || 'null';
const hash = `${data.length}:${maxId}`;
await supabase
.from('backup_checksums')
.insert({
table_name: table,
row_count: data.length,
data_hash: hash
});
}
// Alert if checksums diverge
const { data: lastWeek } = await supabase
.from('backup_checksums')
.select('*')
.gte('checked_at', new Date(Date.now() - 7 * 86400000).toISOString())
.order('checked_at', { ascending: false })
.limit(1);
if (lastWeek && lastWeek[0].row_count !== data.length) {
// Send alert: data changed unexpectedly
}
Tier 3: Offsite Read-Only Replica (For Paranoid Clients)
For healthcare, finance, or compliance-heavy clients, run a read-only Postgres replica in a separate cloud account (Render, AWS RDS, or DigitalOcean). It streams WAL logs from your primary, staying within seconds of live data, and never accepts writes. If production corrupts, you can fork the replica and debug it as-of that moment.
-- In Supabase primary, create a replication user
create user replication_user with replication encrypted password 'strong-password';
grant connect on database postgres to replication_user;
-- Configure connection: Settings → Networking → Allowed addresses
-- Add your replica's IP
-- On your replica host (e.g. Render Postgres)
-- In the UI, set primary_conninfo:
-- primary_conninfo = 'host=your-supabase-db.supabase.co port=5432 user=replication_user password=xxx dbname=postgres'
-- Start recovery:
-- SELECT pg_wal_replay_resume();
-- Verify it's replicating:
-- SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
The replica lags by ~1–5 seconds and costs ~$40/month. You can't use it for reads (risks stalling replication), but you can pause replication, fork a clone, and query it to diagnose issues.
Restore Drill: Test It Quarterly
Backups that don't restore are just hard drives dying in the dark. Once a quarter, spin up a fresh Postgres instance, restore yesterday's pg_dump, and verify row counts match. Set a reminder. Make it boring. A 2-hour restore from a 500MB dump should take ~30 minutes end-to-end.
Six FAQs
How long should I keep nightly backups?
At least 90 days. 30 days catches immediate oopsies; 90 days is insurance against multi-week bugs. S3 storage is cheap — 90 daily backups of 500MB costs ~$15/month. Keep tier 3 backups (replicas, cold archives) indefinitely for compliance.
Can I restore a pg_dump to Supabase?
Yes. Create a new Supabase project, grab the connection string, and run `pg_restore -d [connection string] backup.sql`. Takes ~10 minutes for a large database. You now have a copy for forensics without touching production.
What if my backup corrupts?
S3 versioning prevents silent corruption. Every upload creates a new object version. If a dump is corrupted, you can restore an older version. Also: test restores quarterly so you catch corruption before you need it.
Do I need all 3 tiers?
Depends on your SLA and clients. Tier 1 (S3 dumps) is non-negotiable for SaaS. Tier 2 (checksums) catches data drift early. Tier 3 (replica) is for compliance-heavy or large customers. Start with 1+2, add 3 if clients demand it.
Can I automate restore tests?
Yes, but it's overkill unless you're post-incident paranoid. A quarterly manual check is faster. If you want automation: monthly, spin up a throwaway RDS instance, restore the oldest backup, verify it works, tear it down. Cost: ~$10 per test.
Does pg_dump work with Supabase's managed backups?
Yes, independently. Your nightly dumps don't interfere with Supabase's backups. They're two separate systems — Supabase handles PITR on their side, you handle long-term snapshots on yours. Belt and suspenders.
The Bottom Line
Supabase's built-in backups are table stakes, not a strategy. Nightly pg_dump to S3 gives you independent, auditable snapshots. Weekly checksums catch data drift before it spreads. Offsite replicas handle compliance paranoia. Together, they mean you sleep through the night even when someone's script goes rogue at 3am. For SaaS with customers' livelihoods on the line, that's worth the 30 minutes to set up. Ready to build resilient infrastructure? Check Aidxn Design pricing for backend partnerships. For more on database safety, see Supabase Edge Functions + pg_cron.