How to safely freeze and drain Supabase Auth and Storage writes for a consistent PostgreSQL + Storage backup? #53350
Replies: 4 comments
|
Short answer first: there is no supported cross-service write barrier on Supabase Cloud. There is no maintenance mode that freezes Postgres + Auth + Storage together, so a single consistent recovery point across all three has to be assembled per service rather than enforced with one switch. Here is what each service actually offers, and the recipe I would use for your DR test. Postgres — this is the only part with a real point-in-time mechanism. Auth — you can stop new signups, but you cannot freeze Auth writes. Storage — there is no global write lock, and signed URLs cannot be fenced individually. Pausing the project is not an option for a production app. Only Free projects can be paused (Settings → General → Project availability → Pause Project). Paid projects cannot be paused, so that lever does not exist for the setup you are describing. Recipe I would use for the DR test:
If you need a single verifiable recovery point with zero post-T writes, the honest answer is that Supabase Cloud does not offer that primitive today; the closest you get is the per-service procedure above, or running the DR test against a PITR clone plus a bucket copy where nothing can write in the first place. One more thing, since your post has a few more questions I want to cover: background workers (pg_cron / pg_net) — there is no Supabase-level drain signal for them either. You can inspect pg_cron schedules from inside Postgres, but that is Postgres-level visibility, not a cross-service "all writes drained" proof, so the same conclusion holds. And for a maintenance window: Supabase Cloud has no supported read-only or maintenance mode, so the per-service procedure above is the closest supported approach. Since you already have support request SU-494532 open, the maintainers may be able to confirm whether anything beyond the documented surface exists. |
|
Short version: the platform does not offer this today, and the gap is structural rather than a setting you have missed. Supabase backups, including PITR, are database-only. The docs describe them as a physical backup of the database directory plus WAL archiving, and restoring or cloning from one brings over the database contents and the auth user data, but explicitly not Storage objects or bucket configuration. So there is no platform snapshot that covers Postgres and your files at a single recovery point. Two plan details matter here as well: PITR is a paid add-on, and Free projects get no automatic backups at all, which is why the docs point Free users at CLI dumps. There is also no supported write freeze for the managed services, for reasons inside your own list:
What you can build is an application-level window with bounded, checkable consistency instead of an atomic snapshot:
If your support ticket produces a different official answer I would genuinely like to hear it, but I would plan around the ceiling above. |
|
Hi @rsnadvisors — just checking in: did my answer above resolve this for you? If it did, marking it as the answer would help others with the same issue find it. If it didn't, let me know what happened when you tried it and I'll dig further. |
|
Adding the database-side piece the two answers above left open, since your question list mentions Postgres writes only briefly: you can fence the app schema itself, and the drain is verifiable. It is not a platform switch — it is a GRANT change — and what it misses is just as important as what it catches. 1. The barrier (schema-level, no ownership change needed) This rejects every write through the Data API / PostgREST for client roles, because PostgREST switches to revoke insert, update, delete on all tables in schema public from anon, authenticated;The part people forget is tables created later — grants are per-object, so a migration running during the window re-opens the door: alter default privileges in schema public revoke insert, update, delete on tables from anon, authenticated;Two ownership caveats, both relevant given the diagnostics you posted (table owner
2. What this fence does not close
3. Drain evidence — one query, then a diff
select pid, usename, state, xact_start, wait_event_type, left(query, 120) as query
from pg_stat_activity
where usename in ('anon', 'authenticated') and state <> 'idle';The proof that the window held is a before/after write diff on something every write path in your app must touch. If your tables carry select max(created_at) from public.<your_write_table>;run before the barrier and again before the snapshot — equal timestamps plus an empty One correction to keep the certificate honest: Auth token refreshes keep writing The honest summary: the barrier gives you a bounded window with a certificate, not an atomic cross-service snapshot — and the certificate (empty |
Uh oh!
There was an error while loading. Please reload this page.
Hello Supabase community,
We are preparing a consistent backup and independent disaster-recovery test for a production application using Supabase Cloud.
Our goal is to capture PostgreSQL data, Auth state, Storage metadata, and physical Storage objects at a consistent recovery point.
We have already validated our local backup orchestration and recovery-key procedures. However, we cannot yet establish a verifiable write barrier across Supabase-managed services.
We would appreciate guidance on the following questions:
Auth: Is there a supported mechanism to temporarily reject new Auth writes while allowing existing operations, sessions, hooks, and retries to reach a terminal state?
Storage: How can we prevent new writes through REST, existing signed upload URLs, TUS, S3 multipart uploads, and internal Storage jobs?
Drain verification: Does Supabase expose request IDs, operation states, or telemetry that can prove all previously admitted Auth and Storage writes have completed or been cancelled?
Consistent snapshots: Is there an officially supported method to obtain a consistent recovery point covering PostgreSQL and the physical Storage objects, rather than database metadata alone?
Background workers: How can we determine whether managed pg_cron or pg_net processes have pending operations that could affect backup consistency?
Operational procedure: What is the recommended approach for a short maintenance window that preserves read access for backup extraction while preventing all new writes?
We are specifically looking for supported mechanisms and verifiable completion criteria, not just a PostgreSQL read-only setting.
We have an existing support request, SU-494532, concerning this issue.
Any official documentation, implementation guidance, or clarification from Supabase maintainers would be greatly appreciated.
Thank you.
All reactions