Partial migrations from transaction mistakes

What you see

A migration stopped with an error, and now the schema is in between: the new column exists but the index doesn't, or the table was created but the policies weren't. Re-running the migration fails with:

ERROR:  relation "invoice_lines" already exists

or

ERROR:  column "currency" of relation "invoices" already exists

Why it happens

Postgres can run DDL (create table, alter table, create policy) inside a transaction and roll all of it back on error. You only get that if the statements actually ran in one transaction. Common ways they don't:

• psql runs each statement on its own by default. psql -f migration.sql keeps going after an error, and everything before the error stays applied.
• A commit in the middle of the file, or several begin; ... commit; blocks, splits it into separate pieces.
• Statements that can't run in a transaction, like create index concurrently, vacuum or alter type ... add value on older Postgres versions, force the tool to run statements one by one.
• Pasting part of a script into a SQL editor, or running it in several goes.

How to fix it

1. See what was applied. Compare the migration with the real schema:

select column_name, data_type
from information_schema.columns
where table_schema = 'public' and table_name = 'invoices';

select indexname from pg_indexes where tablename = 'invoices';
select policyname from pg_policies where tablename = 'invoices';

2. Look for invalid indexes. A failed create index concurrently leaves an index that exists but isn't used:

select c.relname
from pg_index i join pg_class c on c.oid = i.indexrelid
where not i.indisvalid;

Drop it with drop index concurrently <name>; and create it again.

3. Finish or undo. Write a short follow-up script for the remaining steps only, or for reversing the applied ones, and run it in a transaction. Making the original migration idempotent also lets you re-run it safely:

begin;
alter table public.invoices add column if not exists currency text not null default 'EUR';
create index if not exists invoice_lines_invoice_id_idx on public.invoice_lines (invoice_id);
drop policy if exists "Owners read lines" on public.invoice_lines;
create policy "Owners read lines" on public.invoice_lines
  for select to authenticated
  using ( invoice_id in (select id from public.invoices where owner_id = (select auth.uid())) );
commit;

4. Fix your migration history if you use one. If the migration is recorded in supabase_migrations.schema_migrations but only half of it ran, apply the rest as a new migration rather than editing the old one, so every environment ends up the same.

5. If data was damaged, not just schema, restore the project to just before the migration (see below).

Make migrations all-or-nothing

• Wrap each migration in begin; ... commit;, and don't put commit anywhere else in it.
• In psql, stop at the first error and use one transaction for the file:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 --single-transaction -f 20261111_add_currency.sql

• Keep statements that can't run in a transaction (create index concurrently) in their own migration file.
• Try the migration on a branch or a local copy first.

On SupaHost

• The MCP server's apply_migration tool runs the whole migration and its history entry in one transaction, so it can't half-apply. The history goes into supabase_migrations.schema_migrations, the same table the Supabase CLI uses.
• On Pro and Business, point-in-time recovery can restore one project to the second before a bad migration without touching your other projects. On Builder, restore from a daily backup.
• Branching (Pro and up) gives you a copy of a project to rehearse a migration on.

Related

• SQL migrations
• Migrations workflow: local to SupaHost
• Freezing writes during a migration
• Restoring a project