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.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	9 built-in (http, vector, fs9, pg_cron, embedding, hstore, uuid-ossp, parquet, zhparser)
Replication	Followers (streaming replication)	Not supported
LISTEN/NOTIFY	Supported	Supported over pgwire (delivered on commit); the stateless HTTP SQL API can NOTIFY but cannot LISTEN
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 import step rewrites schema.sql as text before loading it, and a custom (-Fc) or directory (-Fd) archive is not text.

   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 for extensions not in: http, uuid-ossp, hstore, fs9, pg_cron, parquet, zhparser, vector, embedding.
   • CREATE PUBLICATION / CREATE SUBSCRIPTION — DB9 does not support logical replication.
   • Row-level security policies — CREATE POLICY, ALTER TABLE ... ENABLE ROW LEVEL SECURITY.
   • Table partitioning — PARTITION BY, CREATE TABLE ... PARTITION OF.

   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 RLS
   grep -i "ROW LEVEL SECURITY\|CREATE POLICY" export.sql
   
   # Check for replication
   grep -i "PUBLICATION\|SUBSCRIPTION" export.sql

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 and remove what they flag before importing it.

   Terminal

   # Rewrite pg_dump's SERIAL and identity columns into forms DB9 runs (see the caution below)
   sed -i.bak "s/nextval('\([^']*\)'::regclass)/nextval('\1')/g" schema.sql
   perl -0pi.orig -e 's/ALTER TABLE (\S+) ALTER COLUMN (\S+) ADD GENERATED (?:ALWAYS|BY DEFAULT) AS IDENTITY \(\s*SEQUENCE NAME (\S+)(.*?)\);/CREATE SEQUENCE $3$4;\nALTER SEQUENCE $3 OWNED BY $1.$2;\nALTER TABLE $1 ALTER COLUMN $2 SET DEFAULT nextval(\x27$3\x27);/gs' schema.sql
   
   # 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.

   Don’t load export.sql in one pass

   • Primary keys: pg_dump adds each primary key after that table’s rows are loaded, and DB9 refuses to add a primary key to a table that already has rows (0A000). A one-pass psql -f export.sql therefore leaves those tables without their primary keys, and the foreign keys that reference them fail too (42830). Importing schema.sql first adds the keys while the tables are empty.
   • SERIAL columns: pg_dump writes their default as nextval('public.users_id_seq'::regclass). DB9 accepts it, but every INSERT that relies on it fails with 22P02 invalid input syntax for type regclass. The sed line rewrites it to nextval('public.users_id_seq'), which works.
   • Identity columns: pg_dump adds them with ALTER TABLE ... ADD GENERATED ... AS IDENTITY, which DB9 cannot parse (42601). The column is left with no default, so every INSERT that omits it fails with 23502. The perl line replaces each one with a sequence and a nextval default.
   • 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, not 5432
   • 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
   • LISTEN/NOTIFY — supported over a direct pgwire connection; the stateless HTTP SQL API can NOTIFY but cannot LISTEN
   • 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 psql to stream individual tables with COPY.

Caveats

• No zero-downtime migration — DB9 does not support logical replication. 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. 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