Supascale
Sign Up

Partial Restores for Self-Hosted Supabase: Recover a Single Table

Recover a single table or a handful of rows on self-hosted Supabase without a full restore. pg_restore tricks, scratch databases, and Supabase-specific traps.

Cover Image for Partial Restores for Self-Hosted Supabase: Recover a Single Table

Someone ran a DELETE without a WHERE clause. A migration dropped a column that turned out to matter. A bad cron job overwrote half of a lookup table. On self-hosted Supabase, the instinctive answer is "restore last night's backup" — but a full restore is the nuclear option. It rolls back every table to last night, throwing away a day of perfectly good writes in orders to fix a mistake in coupons.

What you usually want is a partial restore: pull one table — or one set of rows — out of an existing backup and surgically put it back, while the rest of the database keeps running. If you're already taking regular backups, you have everything you need. This guide covers how to do it with pg_restore, how to avoid the traps specific to Supabase's schema layout, and when point-in-time recovery is the better tool.

Why full restores are the wrong tool for small mistakes

A full restore has two costs that partial restores avoid:

  1. Data loss everywhere else. Restoring a 2 a.m. backup at 4 p.m. deletes fourteen hours of writes across every table, not just the broken one. For a production app, that can be worse than the original incident.
  2. Downtime. A full restore typically means stopping services, dropping and recreating the database, and replaying the whole dump. On a 50 GB database that's not a five-minute operation.

A partial restore touches only the damaged objects. Users on unaffected parts of the app never notice. The trade-off — and it's real — is that partial restores require more care: foreign keys, sequences, triggers, and Supabase's own schemas all conspire against a naive pg_restore -t.

Prerequisite: use custom-format dumps

Whether a partial restore is easy or miserable depends on the format of your backup.

# Plain SQL — one giant text file, hard to restore selectively
pg_dump "$DB_URL" > backup.sql

# Custom format — indexed archive, supports selective restore
pg_dump -Fc "$DB_URL" > backup.dump

# Directory format — same benefits, plus parallel dump/restore
pg_dump -Fd -j 4 "$DB_URL" -f backup_dir/

Custom (-Fc) and directory (-Fd) formats are archives with a table of contents. pg_restore can list that TOC and extract exactly the objects you ask for. With a plain SQL dump, your options degrade to grepping a multi-gigabyte text file for COPY public.coupons — doable, but fragile.

If your current backup script produces plain SQL, switch it now, before you need it. Test that the switch worked, too — backup procedures you've never tested have a way of failing exactly when it matters.

The safe pattern: restore into a scratch database

The tempting shortcut is restoring straight into production:

# Tempting, but risky
pg_restore -d "$PROD_URL" --clean -t coupons backup.dump

Don't. --clean drops the live table first, and pg_restore -t restores only the table — not the indexes' dependencies, not the grants, and with no regard for foreign keys pointing at it. If anything fails mid-restore, you've made the incident worse.

The safer pattern is a two-step: restore the table into a scratch database, then copy the data across yourself.

Step 1 — restore the table somewhere harmless:

createdb -h localhost -U postgres scratch_restore

pg_restore -h localhost -U postgres -d scratch_restore \
  --no-owner --no-acl \
  -t coupons backup.dump

--no-owner --no-acl matters on Supabase: dumps reference roles like supabase_admin and per-table grants that may not resolve cleanly in a scratch database. (This got stricter after the June 2026 image changes moved Studio and postgres-meta from supabase_admin to postgres — see the June 2026 breaking changes prep guide if your dumps predate your upgrade.)

Step 2 — move the data into production deliberately:

For a full table replacement inside one transaction:

BEGIN;
ALTER TABLE public.coupons DISABLE TRIGGER USER;
TRUNCATE public.coupons;
-- \copy via psql, piped from the scratch database:
-- psql $SCRATCH -c "\copy coupons TO STDOUT" | psql $PROD -c "\copy coupons FROM STDIN"
ALTER TABLE public.coupons ENABLE TRIGGER USER;
COMMIT;

For recovering specific rows — the more common case — pull the scratch table in with postgres_fdw and cherry-pick:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER scratch FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'scratch_restore', host 'localhost');
CREATE USER MAPPING FOR postgres SERVER scratch
  OPTIONS (user 'postgres', password '...');

CREATE FOREIGN TABLE coupons_backup (LIKE public.coupons)
  SERVER scratch OPTIONS (table_name 'coupons');

-- Re-insert only what was deleted
INSERT INTO public.coupons
SELECT * FROM coupons_backup b
WHERE NOT EXISTS (
  SELECT 1 FROM public.coupons c WHERE c.id = b.id
);

Step 3 — fix the sequence. If the table uses a serial or identity column, restored rows don't advance the sequence, and the next insert will collide:

SELECT setval(
  pg_get_serial_sequence('public.coupons', 'id'),
  (SELECT COALESCE(MAX(id), 1) FROM public.coupons)
);

Tables keyed by UUID skip this step entirely — one of several operational reasons covered in UUID vs bigint primary keys for self-hosted Supabase.

Supabase-specific traps

A self-hosted Supabase database is not a plain Postgres database, and partial restores hit the differences head-on.

Don't blind-restore auth or storage tables. auth.users has triggers (and often your own handle_new_user trigger) that fire on insert; storage.objects rows are pointers to files that must actually exist in your storage backend. Restoring storage.objects without the corresponding files gives you a bucket full of 404s — the database and the files are separate backup concerns, as covered in storage backup: the forgotten piece. If you must repair auth data, disable user triggers for the transaction and reconcile identities manually.

RLS policies and grants live with the table definition. If you restore with --no-acl and recreate a table (rather than just re-inserting rows), you've silently dropped its grants and its RLS policies — the table may become invisible to your app or, worse, wide open through the Data API. After any restore that touched DDL, verify:

SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public';
SELECT * FROM pg_policies WHERE tablename = 'coupons';

Foreign keys constrain your order of operations. If coupon_redemptions references coupons, you can't truncate the parent while children exist. Either restore parents before children, or use TRUNCATE ... CASCADE with full awareness of what it cascades to. Practice this on the scratch database first — that's what it's there for.

When PITR beats a partial restore

Partial restores recover tables from a snapshot. But if the incident is "we need everything as of 14:32, right before the bad deploy," what you want is point-in-time recovery: restore the base backup plus WAL to the exact moment before the mistake — into a scratch instance — then extract the affected table from there using the same postgres_fdw pattern above.

The two techniques compose. PITR answers "get me the data as it was at time T"; partial restore answers "move only these objects into production." Together they let you recover a single table as of one minute before the incident, which is usually the actual goal.

Where Supascale fits

Everything above assumes you have recent, restorable, custom-format backups to work from — which is exactly the part most self-hosted setups get wrong. Supascale handles the unglamorous half of this workflow: scheduled backups shipped to S3-compatible storage, retention you configure once, and one-click full restores for the cases where the nuclear option really is the right call.

For the surgical cases, having every nightly dump sitting in S3 means "restore into a scratch database" starts with a download, not a panicked search for whether the cron job that writes backups has actually been running. The pricing is a one-time license — from $99, unlimited projects — so backup coverage isn't a per-instance cost decision.

To be honest about the boundary: no tool automates the judgment in a partial restore. Which rows to keep, how to reconcile foreign keys, whether the sequence needs fixing — that's your call, made with psql open. What tooling can guarantee is that when you need to make that call, the backup exists, it's in the right format, and it's a click away.

Key takeaways

  • Full restores punish every table for one table's mistake. Reach for partial restores first when the damage is localized.
  • Use pg_dump -Fc (or -Fd). Selective restore is only practical from archive formats — change your backup script before the incident, not during it.
  • Never restore directly into production. Scratch database first, then move data deliberately with \copy or postgres_fdw.
  • Respect Supabase's schemas. auth and storage tables have triggers and external dependencies; RLS and grants need verifying after any DDL-level restore.
  • Fix sequences, check foreign keys, and test the whole dance on a throwaway database before you need it under pressure.

Further Reading