Migrate from Heroku Postgres

Heroku Postgres is a managed PostgreSQL service tightly integrated with the Heroku platform. Since it runs standard PostgreSQL, migration to DB9 uses pg_dump for export and psql for import.

For the general PostgreSQL migration guide, see Migrate from PostgreSQL.

What Changes and What Stays the Same

Stays the same

• SQL compatibility — DB9 supports the same DML, DDL, joins, CTEs, window functions, and subqueries you use in Heroku Postgres. Most queries work without changes.
• PostgreSQL drivers — Any driver that connects via pgwire (node-postgres, psycopg, pgx, JDBC) works with DB9.
• ORM compatibility — Prisma, Drizzle, SQLAlchemy, TypeORM, Sequelize, Knex, and GORM are tested and supported.
• Data types — Common types (TEXT, INTEGER, BIGINT, BOOLEAN, TIMESTAMPTZ, UUID, JSONB, arrays, vectors) work identically.

Changes

Area	Heroku Postgres	DB9
Connection string	postgresql://user:pass@host.compute-1.amazonaws.com:5432/dbname	postgresql://<tenant_id>.<role>@pg.db9.io:5433/postgres
Port	5432	5433
Database name	Auto-generated (e.g., d1a2b3c4e5f6g7)	Always postgres
Connection pooling	Built-in connection pooling add-on	No built-in pooler — use application-side pooling
Credentials	Rotate periodically (Heroku manages)	Static credentials in connection string
Extensions	Most community extensions available	Fixed built-in set: http, vector, fs9, pg_cron, embedding, uuid-ossp, parquet, zhparser — plus hstore, pgcrypto and plpgsql as metadata-only shims
Replication	Followers (streaming replication)	Not supported
LISTEN/NOTIFY	Supported	Supported over pgwire (delivered on commit); the stateless HTTP SQL API can NOTIFY, but a LISTEN there is accepted and never receives anything
Dataclips	Supported (shareable SQL queries)	Not available — use DB9 CLI or psql
pg:backups	Heroku CLI automated backups	CLI-based backup (db9 db dump)

Review the Compatibility Matrix for the full list of supported and unsupported features.

Prerequisites

• Access to your Heroku app and database
• Heroku CLI installed (npm install -g heroku or brew tap heroku/brew && brew install heroku)
• pg_dump installed locally (comes with PostgreSQL client tools)
• DB9 CLI installed: curl -fsSL https://db9.ai/install | sh
• A DB9 account: db9 create --name my-app to create your target database

1. Get Your Heroku Connection String

   Terminal

   heroku config:get DATABASE_URL -a your-app-name

   The connection string looks like:

   postgresql://username:password@ec2-xx-xx-xx-xx.compute-1.amazonaws.com:5432/d1a2b3c4e5f6g7

   Alternatively, use heroku pg:credentials:url -a your-app-name for detailed connection information.

2. Export from Heroku Postgres

   Use pg_dump with the Heroku connection string. Heroku requires SSL:

   Schema and data (plain SQL format)

   Terminal

   pg_dump --no-owner --no-privileges --no-comments \
     "$(heroku config:get DATABASE_URL -a your-app-name)?sslmode=require" \
     > export.sql

   Schema only

   Terminal

   pg_dump --schema-only --no-owner --no-privileges \
     "$(heroku config:get DATABASE_URL -a your-app-name)?sslmode=require" \
     > schema.sql

   Flags explained:

   • --no-owner — omits ALTER ... OWNER TO statements that reference Heroku-specific roles
   • --no-privileges — omits GRANT/REVOKE statements
   • --no-comments — omits COMMENT ON statements

   Use plain SQL format (default): the cleanup checks in step 3 read and edit the file as text, which a custom (-Fc) or directory (-Fd) archive is not.

   Alternative: Heroku pg:backups

   You can also create a backup and download it, but the pg_dump approach gives you a plain SQL file that is easier to clean and import.

3. Clean the Export

   The pg_dump output may contain statements that DB9 does not support. Remove or comment out:

   • CREATE EXTENSION for extensions DB9 does not have — remove any CREATE EXTENSION whose name is not one of http, uuid-ossp, fs9, pg_cron, parquet, zhparser, vector, embedding. Any other name raises 42704 extension is not available and stops the import there — except hstore, which DB9 accepts. Remove it anyway: DB9 does not support hstore values, and while it is registered pg_dump cannot dump your DB9 database. Two more names, pgcrypto and plpgsql, are accepted and can stay — but read the caution below before you rely on either.
   • Schema-qualified vector names — keep vector, but rewrite how the dump names it rather than removing it. pg_dump writes the type, its operator classes and its operators as public.vector(1536), public.vector_cosine_ops and OPERATOR(public.<=>), which DB9 rejects — the type with 42704 type "public.vector" does not exist — so that table and its rows do not load. Strip the qualifier in schema.sql, the file you import below (and in export.sql if you load that in one pass): sed -E -i.bak -e 's/(^|[^[:alnum:]_])public\.vector([^[:alnum:]_]|_(l2|ip|cosine)_ops|$)/\1vector\2/g' -e 's/OPERATOR\(public\.(<->|<#>|<=>)\)/\1/g' schema.sql — see Migrate from PostgreSQL for what it changes.
   • CREATE PUBLICATION / CREATE SUBSCRIPTION — DB9 cannot subscribe to logical replication (CREATE SUBSCRIPTION fails with 42601), and the source’s publications belong to its own replication setup. Remove both.
   • Table partitioning — DB9 partitions only by RANGE on a timestamp or timestamptz key, and it cannot parse the ALTER TABLE ... ATTACH PARTITION statements pg_dump writes for each partition (42601). Recreate a qualifying table by hand as described in Migrate from PostgreSQL, or merge its partitions into one table.

   pgcrypto imports cleanly, then fails at call time

   pgcrypto and plpgsql are the two names that will not announce themselves during the migration. CREATE EXTENSION pgcrypto returns CREATE EXTENSION and writes a pg_extension row, so the import looks clean and nothing flags it — but it registers no functions of its own. crypt(), gen_salt(), hmac(), encrypt()/decrypt(), pgp_sym_encrypt() and gen_random_bytes() each fail with 42883 function ... does not exist the first time your application calls them, which may be long after you have cut over.

   gen_random_uuid() and digest() keep working — both are DB9 built-ins that need no extension at all. If your Heroku schema hashes passwords with crypt()/gen_salt(), move that into your application layer before the cutover. plpgsql is a shim too, but a harmless one: PL/pgSQL is compiled into the server and works whether or not the statement runs.

   Details: Extensions.

   A quick way to identify issues:

   Terminal

   # Check for unsupported extensions
   grep "CREATE EXTENSION" export.sql
   
   # Check for partitioning
   grep -i "PARTITION" export.sql
   
   # Check for replication
   grep -i "PUBLICATION\|SUBSCRIPTION" export.sql

   Row-level security

   Keep your row-level security statements. ENABLE / FORCE ROW LEVEL SECURITY and CREATE POLICY import from a pg_dump file, and DB9 enforces the policies for roles that are not the table owner. See Row-Level Security. One thing needs attention:

   • Roles. pg_dump does not export roles, and a policy that names one (CREATE POLICY ... TO app_user) fails with 42704 role "app_user" does not exist. Create each role in DB9 after step 4 and before step 5. For example: psql "$(db9 db connect my-app --output quiet)" -c "CREATE ROLE app_user LOGIN PASSWORD '<password>'". --no-privileges also leaves out the grants, so grant those roles their table and sequence privileges again after the import.

4. Create the DB9 Database

   Terminal

   db9 create --name my-app --show-connection-string

   ▶ Run

   This returns immediately with the connection string and credentials.

5. Import into DB9

   Import over pgwire with psql in two passes: schema.sql from step 2 while the tables are still empty, then the data streamed straight from Heroku. Run the step 3 checks against schema.sql, remove what they flag and apply the vector rewrite before importing it.

   Terminal

   # Import the schema, with the statements flagged in step 3 removed
   psql "$(db9 db connect my-app --output quiet)" -f schema.sql
   
   # Then stream the data from Heroku into DB9
   pg_dump --data-only --no-owner \
     "$(heroku config:get DATABASE_URL -a your-app-name)?sslmode=require" \
     | psql "$(db9 db connect my-app --output quiet)"

   DB9 supports COPY in CSV and TEXT formats over pgwire. psql keeps going after an error, so scan its output for ERROR: lines.

   What a pg_dump file needs before it loads

   A one-pass psql -f export.sql now completes: DB9 runs the nextval('...'::regclass) defaults, the ADD GENERATED ... AS IDENTITY statements and the post-data ADD PRIMARY KEY statements that pg_dump writes, and each identity column keeps its GENERATED ALWAYS or BY DEFAULT behaviour. What still needs attention:

   • --inserts / --column-inserts: pg_dump then writes each row of a table with a GENERATED ALWAYS identity column as INSERT ... OVERRIDING SYSTEM VALUE, which DB9 cannot parse (42601), so none of that table’s rows load, and rows that reference them fail their foreign keys (23503). Keep pg_dump’s default COPY data.
   • db9 db sql -f cannot import a pg_dump file, with or without --direct: neither path can carry COPY ... FROM stdin data, and the HTTP SQL API it uses by default rejects requests over about 2 MB.

   Details: Migrate from PostgreSQL.

6. Update Your Application

   Connection string

   Replace the Heroku DATABASE_URL with DB9’s:

   Diff

   DATABASE_URL=postgresql://user:pass@ec2-xx-xx-xx-xx.compute-1.amazonaws.com:5432/d1a2b3c4e5f6g7
   DATABASE_URL=postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres?sslmode=require

   If your app runs on Heroku, set the new config var:

   Terminal

   heroku config:set DATABASE_URL="postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres?sslmode=require" -a your-app-name

   Key differences:

   • Username: DB9 uses <tenant_id>.<role> format (e.g., a1b2c3d4e5f6.admin)
   • Port: 5433 (the PostgreSQL default 5432 works too)
   • Database: Always postgres
   • Host: pg.db9.io
   • Credentials: Static (Heroku rotates credentials periodically — DB9 does not)

   Connection pooling

   If you use Heroku’s connection pooling add-on, remove the pooled connection string and configure application-side pooling:

   TypeScript

   const pool = new pg.Pool({
     connectionString: process.env.DATABASE_URL,
     max: 10,
     idleTimeoutMillis: 30000,
     ssl: { rejectUnauthorized: false },
   });

   Heroku Dataclips

   Heroku Dataclips (shareable SQL queries) have no DB9 equivalent. Use the DB9 CLI to run queries:

   Terminal

   db9 db sql my-app -q "SELECT * FROM users LIMIT 10"

   For ORMs, see the integration guides: Prisma, Drizzle, SQLAlchemy.

7. Validate

   Check schema

   Terminal

   db9 db dump my-app --ddl-only

   ▶ Run

   Compare the output with your original schema to confirm all tables, indexes, and constraints were created.

   Check row counts

   Terminal

   db9 db sql my-app -q "SELECT count(*) FROM users"
   db9 db sql my-app -q "SELECT count(*) FROM orders"

   ▶ Run

   Compare row counts against the source Heroku database.

   Run your test suite

   Terminal

   DATABASE_URL="$(db9 db connect my-app --output quiet)" npm test

   Check for unsupported features

   If your tests fail, check these common differences:

   • SERIALIZABLE isolation — DB9 does not implement SERIALIZABLE. Requesting it on the wire protocol raises a WARNING and silently downgrades the transaction to REPEATABLE READ, so it will not fail loudly. Audit transactions that relied on serializability and add explicit SELECT ... FOR UPDATE locks or unique constraints. At the downgraded level, the transaction that loses a SELECT ... FOR UPDATE race fails at COMMIT with 40001 rather than at the statement, so make sure your retry logic covers COMMIT as well; a unique-constraint conflict fails the INSERT with 23505, as in PostgreSQL. See Transactions
   • LISTEN/NOTIFY — supported over a direct pgwire connection; the stateless HTTP SQL API can NOTIFY, but a LISTEN there is accepted and never receives anything
   • Advisory locks — available, but coordination is node-local. For strict row-level coordination, use SELECT ... FOR UPDATE

Rollback Plan

If you need to revert:

1. Your Heroku database is unchanged — switch DATABASE_URL back:

Terminal

# Re-attach the original Heroku Postgres add-on URL
heroku config:set DATABASE_URL="$(heroku pg:credentials:url -a your-app-name | grep postgresql://)" -a your-app-name

2. If you need to export data created in DB9 back to Heroku:

Terminal

# Export from DB9
db9 db dump my-app -o db9-export.sql

# Import to Heroku
psql "$(heroku config:get DATABASE_URL -a your-app-name)?sslmode=require" \
  -f db9-export.sql

The db9 db dump command outputs plain SQL. Its limit of 50,000 rows or 16 MB applies to the whole database, not to each table: a larger database is refused with Error 413 and no file is written. For larger databases, use pg_dump 17 or newer instead — see the PostgreSQL guide’s rollback plan for the command, the cases it cannot dump, and the extra step for vector columns — or use psql to stream individual tables with COPY.

Caveats

• No zero-downtime migration — DB9 cannot subscribe to logical replication (CREATE SUBSCRIPTION is not supported), so it cannot follow your Heroku database during the cutover. Plan a maintenance window for the cutover.
• Extension gaps — If your Heroku database uses extensions not in DB9’s built-in set, those features will not be available and CREATE EXTENSION raises 42704 at import time. pgcrypto is the exception worth singling out: DB9 accepts the statement but registers no functions, so crypt() and gen_salt() fail only when something calls them. Check your CREATE EXTENSION statements.
• Import with psql, not db9 db sql -f — db9 db sql -f cannot run a pg_dump file, with or without --direct, and the HTTP SQL API it uses by default also rejects requests over about 2 MB and gives up after about 15 seconds. Import over pgwire, schema first.
• Credential rotation — Heroku periodically rotates database credentials. DB9 credentials are static. If your application handles credential rotation, simplify that logic.
• Heroku add-on ecosystem — Heroku add-ons that depend on DATABASE_URL (logging, monitoring, analytics) will not automatically connect to DB9. Reconfigure or replace them.
• No followers — Heroku Postgres supports follower databases (read replicas). DB9 does not. If you use followers for read scaling, consolidate to a single connection.

Next Pages

• Compatibility Matrix — full list of supported and unsupported PostgreSQL features
• Connect — connection string format and authentication options
• Migrate from PostgreSQL — general PostgreSQL migration path
• Production Checklist — deployment readiness