Skip to content
Discord Get Started

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 the DB9 CLI or psql for import.

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

What Changes and What Stays the Same

Section titled “What Changes and What Stays the Same”

Stays the same

Section titled “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

Section titled “Changes”
AreaHeroku PostgresDB9
Connection stringpostgresql://user:pass@host.compute-1.amazonaws.com:5432/dbnamepostgresql://tenant.role@pg.db9.io:5433/postgres
Port54325433
Database nameAuto-generated (e.g., d1a2b3c4e5f6g7)Always postgres
Connection poolingBuilt-in connection pooling add-onNo built-in pooler — use application-side pooling
CredentialsRotate periodically (Heroku manages)Static credentials in connection string
ExtensionsMost community extensions available9 built-in (http, vector, fs9, pg_cron, embedding, hstore, uuid-ossp, parquet, zhparser)
ReplicationFollowers (streaming replication)Not supported
LISTEN/NOTIFYSupportedNot supported
DataclipsSupported (shareable SQL queries)Not available — use DB9 CLI or psql
pg:backupsHeroku CLI automated backupsCLI-based backup (db9 db dump)

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

Prerequisites

Section titled “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). DB9 does not support pg_restore with the custom (-Fc) or directory (-Fd) formats — import via SQL text only.

  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

    This returns immediately with the connection string and credentials.

  5. Import into DB9

    Option A: CLI import (recommended for most databases)

    Terminal
    db9 db sql my-app -f export.sql

    Suitable for databases up to the API import limits (50,000 rows or 16 MB per table).

    Option B: Direct psql import (for larger databases)

    Terminal
    psql "$(db9 db status my-app --json | jq -r .connection_string)" -f export.sql

    Streams SQL through pgwire without API size limits.

    Option C: COPY for bulk data

    Terminal
    # Import schema first
    psql "$(db9 db status my-app --json | jq -r .connection_string)" -f schema.sql
    

    # Then stream data directly from Heroku into DB9
    pg_dump --data-only --no-owner \
      "$(heroku config:get DATABASE_URL -a your-app-name)?sslmode=require" \
      | psql "$(db9 db status my-app --json | jq -r .connection_string)"

    DB9 supports COPY in CSV and TEXT formats over pgwire.

  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

    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"

    Compare row counts against the source Heroku database.

    Run your test suite

    Terminal
    DATABASE_URL="$(db9 db status my-app --json | jq -r .connection_string)" npm test

    Check for unsupported features

    If your tests fail, check these common differences:

    • SERIALIZABLE isolation — DB9 does not support SERIALIZABLE and returns an error. Use REPEATABLE READ or READ COMMITTED instead
    • LISTEN/NOTIFY — not supported; use polling or an external message queue
    • Advisory locks — available, but coordination is node-local. For strict row-level coordination, use SELECT ... FOR UPDATE

Rollback Plan

Section titled “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
  1. 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 (up to 50,000 rows or 16 MB per table). For larger databases, use psql to stream individual tables with COPY.

Caveats

Section titled “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.
  • Dump size limits — The db9 db sql -f API import has limits (50,000 rows, 16 MB per table). For larger databases, use direct psql connection for import.
  • 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

Section titled “Next Pages”