Migrate from Heroku Postgres

A Heroku Postgres database moves to SupaHost as a normal pg_dump file. On Business and Enterprise you restore it with pg_restore through the SSH tunnel. On Builder and Pro there's no direct Postgres access, so you load it through Studio, MCP or a script, or stage it in a Supabase Cloud project and use the one-click import. Large databases are easiest on Business.

Plan

• Check the size. heroku pg:info -a recipe-api shows it. It must fit your plan's shared database quota (8 GB Builder, 25 GB Pro, 50 GB Business).
• Check how your app connects. If it uses DATABASE_URL with a Postgres driver or ORM (ActiveRecord, Django, Sequelize, Prisma), note that a SupaHost project's Postgres port isn't on the internet. Plan to move data access to the Data API (supabase-js or HTTP), or, on Business, run the app on a server that keeps an SSH tunnel open.
• Pick a cutover window. Put the app in maintenance mode (heroku maintenance:on) before the final backup so no writes are lost.

Export from Heroku

heroku pg:backups:capture -a recipe-api
curl -o recipe-api.dump "$(heroku pg:backups:url -a recipe-api)"

The file is in pg_dump custom format.

Transform

Heroku installs extensions into a schema called heroku_ext, and your column defaults may reference it (for example heroku_ext.uuid_generate_v4()). Convert the dump to SQL and point those references at SupaHost's extensions schema:

pg_restore --no-owner --no-privileges -n public -f recipe-api.sql recipe-api.dump
sed -i 's/heroku_ext\./extensions./g' recipe-api.sql

Then create the extensions you use in the target project first, for example in Studio's SQL editor:

create extension if not exists "uuid-ossp" with schema extensions;
create extension if not exists pg_trgm with schema extensions;

Use pg_dump/pg_restore 17 tools; SupaHost runs Postgres 17.

Load

Business and Enterprise. Add your SSH key in Dashboard → your project → Connect → SSH & CLI, copy the string from Connect → Database → Show connection string, then:

ssh -N -L 6543:127.0.0.1:5432 [email protected] &
psql "postgresql://postgres:<password>@127.0.0.1:6543/p_app_one" -v ON_ERROR_STOP=1 -f recipe-api.sql

Local port 6543 avoids a clash with a Postgres already running on your machine. ON_ERROR_STOP halts on the first error so you never end up with half a schema; if it stops on a line about the public schema itself (such as COMMENT ON SCHEMA public), delete that line and run again.

Builder and Pro. Pick one:

• Restore the dump into a temporary Supabase Cloud project, then use the one-click import. This is the most complete route for anything beyond a few hundred MB.
• Run the schema part of the SQL file in Studio's SQL editor (or over MCP with apply_migration), then load each table from CSV with the table editor's import, or insert rows in batches with supabase-js and the service_role key.

After loading

Tables in public are reachable through the Data API, so turn on Row Level Security for every table before you share the anon key:

select format('alter table public.%I enable row level security;', tablename)
from pg_tables where schemaname = 'public' and not rowsecurity;

Run the statements it prints, add policies, then analyze;. If you loaded data with CSV, fix sequences (duplicate key after importing).

Switch the app and verify

Point the app at https://app-one.acme.supahost.space with the anon key (browser) or service_role key (server only). Compare row counts with the source. This gives estimates for every table after analyze; run select count(*) on the tables that matter most for exact numbers:

select relname, n_live_tup from pg_stat_user_tables order by relname;

Keep the Heroku database until you've run the app on SupaHost for a few days.

Related

• Importing data (CSV, SQL dumps)
• SSH access and direct Postgres
• Connecting directly to Postgres
• Row Level Security