# Migrate from Heroku Postgres

> Move a Heroku Postgres database to a SupaHost project: capture a backup, clean up heroku_ext references, restore it over the SSH tunnel or another load path, and switch your app.

Source: https://supahost.app/kb/migrate-from-heroku · Updated 2026-11-11

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

```bash
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:

```bash
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:

```sql
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:

```bash
ssh -N -L 6543:127.0.0.1:5432 supahost@acme.supahost.space &
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](/kb/migrate-from-supabase-cloud). 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:

```sql
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](/kb/duplicate-key-sequence)).

## 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:

```sql
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)](/docs/database/import-data)
- [SSH access and direct Postgres](/docs/platform/ssh)
- [Connecting directly to Postgres](/kb/direct-postgres-connection)
- [Row Level Security](/docs/database/row-level-security)