Skip to content
Discord Get Started

Migrate from PostgreSQL

This guide covers migrating from any PostgreSQL installation — self-hosted, AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL, or other managed services — to DB9. The process uses standard pg_dump for export and psql for import.

For platform-specific guides, see Migrate from Neon or Migrate from Supabase.

Before You Start: Compatibility Check

Section titled “Before You Start: Compatibility Check”

DB9 supports most PostgreSQL workloads, but some features are not available. Run these checks against your existing database before migrating.

Quick compatibility scan

Section titled “Quick compatibility scan”

Connect to your source database and check for unsupported features:

SQL
-- Table partitioning (not supported)
SELECT count(*) AS partitioned_tables
FROM pg_partitioned_table;


-- Table inheritance (not supported)
SELECT count(*) AS inherited_tables
FROM pg_inherits;


-- Row-level security policies (not supported)
SELECT count(*) AS rls_policies
FROM pg_policies;


-- Foreign data wrappers (not supported)
SELECT count(*) AS fdw_servers
FROM pg_foreign_server;


-- Logical replication (not supported)
SELECT count(*) AS publications
FROM pg_publication;


-- Advisory locks in use (supported, but semantics differ)
SELECT count(*) AS advisory_locks
FROM pg_locks WHERE locktype = 'advisory';

If any of these return non-zero counts, review whether your application depends on them. Most checks above target unsupported features that need refactoring before migration; advisory lock usage needs a separate semantic review because DB9 advisory locks are node-local.

Extension check

Section titled “Extension check”
SQL
SELECT extname FROM pg_extension WHERE extname NOT IN (
  'plpgsql', 'uuid-ossp', 'hstore', 'vector'
) ORDER BY extname;

DB9 supports 9 built-in extensions: http, uuid-ossp, hstore, fs9, pg_cron, parquet, zhparser, vector, embedding. Extensions not in this list (PostGIS, pg_trgm, ltree, citext, etc.) are not available and raise 42704 extension is not available.

Two further names — pgcrypto and plpgsql — are accepted as metadata shims so dumps containing them restore cleanly, but neither adds any functions. In particular CREATE EXTENSION pgcrypto succeeds while crypt(), gen_salt(), hmac(), encrypt() and pgp_sym_encrypt() remain unavailable (42883). See Extensions.

Note: gen_random_uuid() works in DB9 without any extension — no need for pgcrypto.

PL/pgSQL check

Section titled “PL/pgSQL check”

DB9 supports basic PL/pgSQL: variable declarations, IF/ELSIF, CASE, FOR loops (including EXIT WHEN and CONTINUE WHEN), PERFORM, RAISE, RETURN, and dynamic EXECUTE with INTO and USING — in a CREATE FUNCTION body as well as a DO block. It does not support:

  • WHILE and FOREACH loops
  • Cursor operations
  • Exception handling (BEGIN...EXCEPTION) inside a CREATE FUNCTION body — use a DO block
  • Nested BEGIN...END blocks inside a CREATE FUNCTION body — use a DO block

See Advanced SQL — PL/pgSQL for the full host comparison.

SQL
-- Find functions that may use unsupported PL/pgSQL features
SELECT proname, prosrc
FROM pg_proc
WHERE prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql')
  AND (prosrc ~* '\m(WHILE|FOREACH|CURSOR|REFCURSOR)\M'
       OR prosrc ~* '\mEXCEPTION\s+WHEN\M'
       OR prosrc ~* '\mBEGIN\M.*\mBEGIN\M');

Review any matches and rewrite them before migrating.

Trigger functions need one more check. CREATE TRIGGER ... BEFORE refuses a function that uses a loop, a CASE statement, EXECUTE, an exception handler or a nested block (0A000, or 42601 for WHILE and FOREACH), even though CREATE FUNCTION accepted it — so a restored dump fails at the CREATE TRIGGER statement. A CASE expression on the right of an assignment is fine, and AFTER triggers accept FOR loops, CASE statements and EXECUTE. See Advanced SQL — Triggers. To find candidates on the source database:

SQL
-- Find functions attached as BEFORE triggers that use constructs DB9 refuses there
SELECT DISTINCT p.proname
FROM pg_trigger t
JOIN pg_proc p ON p.oid = t.tgfoid
WHERE NOT t.tgisinternal
  AND (t.tgtype & 2) <> 0              -- BEFORE triggers
  AND (p.prosrc ~* '\m(LOOP|CASE|EXECUTE)\M'   -- CASE also matches CASE expressions, which DB9 accepts
       OR p.prosrc ~* '\mEXCEPTION\s+WHEN\M'
       OR p.prosrc ~* '\mBEGIN\M.*\mBEGIN\M');

What Changes

Section titled “What Changes”
AreaStandard PostgreSQLDB9
Connection stringpostgresql://user:pass@host:5432/dbnamepostgresql://tenant.role@pg.db9.io:5433/postgres
Port5432 (default)5433
Database nameCustomAlways postgres
UsernameStandard rolestenant_id.role format (e.g., a1b2c3d4e5f6.admin)
Transaction isolationSERIALIZABLE fully enforcedREAD COMMITTED and REPEATABLE READ enforced; SERIALIZABLE is downgraded to REPEATABLE READ with a warning on the wire protocol, and rejected with an error over the HTTP SQL API
Connection poolingExternal (PgBouncer, pgpool)Application-side pooling
ReplicationLogical and streamingNot supported
LISTEN/NOTIFYSupportedSupported over pgwire (delivered on commit); the stateless HTTP SQL API can NOTIFY but cannot LISTEN
ExtensionsCommunity ecosystem9 built-in only
IndexesAll types fully functionalB-tree, GIN, and HNSW full; GiST/Hash/SP-GiST/BRIN rejected

See the Compatibility Matrix for the complete list.

What Stays the Same

Section titled “What Stays the Same”
  • SQL — DML (SELECT, INSERT, UPDATE, DELETE, UPSERT), DDL (CREATE TABLE, ALTER, DROP), JOINs, CTEs, window functions, subqueries, and RETURNING work without changes, apart from the gaps listed in the Compatibility Matrix and the pg_dump statements that step 4 rewrites.
  • Data types — TEXT, INTEGER, BIGINT, BOOLEAN, TIMESTAMPTZ, UUID, JSONB, arrays, FLOAT8, NUMERIC, BYTEA, and vectors.
  • Wire protocol — pgwire v3 (Simple Query, Extended Query, COPY, prepared statements). Any PostgreSQL driver works.
  • ORMs — Prisma, Drizzle, SQLAlchemy, TypeORM, Sequelize, Knex, and GORM are tested at 98-100% compatibility.

Prerequisites

Section titled “Prerequisites”
  • Access to your source PostgreSQL database
  • pg_dump installed locally (should match or be close to your source PostgreSQL version)
  • DB9 CLI installed: curl -fsSL https://db9.ai/install | sh
  • A DB9 account: db9 create --name my-app to create your target database

  1. Export from PostgreSQL

    Schema and data (plain SQL)

    Terminal
    pg_dump --no-owner --no-privileges --no-comments \
      "postgresql://user:pass@your-host:5432/your_database" \
      > export.sql

    Schema only

    Terminal
    pg_dump --schema-only --no-owner --no-privileges \
      "postgresql://user:pass@your-host:5432/your_database" \
      > schema.sql

    Specific tables

    Terminal
    pg_dump --no-owner --no-privileges -t users -t orders -t products \
      "postgresql://user:pass@your-host:5432/your_database" \
      > tables.sql

    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.

    Flags explained:

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

    Locale and encoding settings in the pg_dump output (like SET client_encoding) are accepted and safely ignored by DB9, which operates in UTF-8 only.

    Managed PostgreSQL notes

    ProviderConnection notes
    AWS RDSUse the endpoint hostname and master user credentials. Ensure the security group allows outbound connections from your machine.
    Google Cloud SQLUse Cloud SQL Auth Proxy or allowlist your IP. Direct connection: host:5432/dbname.
    Azure DatabaseUse the {user}@{server} username format Azure requires.
    DigitalOceanUse the connection string from the database dashboard. Requires sslmode=require.

  2. Clean the Export

    Review the export for features DB9 does not support:

    Terminal
    # Unsupported extensions
    grep "CREATE EXTENSION" export.sql
    

    # Table partitioning
    grep -i "PARTITION BY\|PARTITION OF" export.sql
    

    # Row-level security
    grep -i "ROW LEVEL SECURITY\|CREATE POLICY" export.sql
    

    # Table inheritance
    grep -i "INHERITS" export.sql
    

    # Foreign data wrappers
    grep -i "CREATE SERVER\|CREATE FOREIGN TABLE" export.sql
    

    # Replication
    grep -i "CREATE PUBLICATION\|CREATE SUBSCRIPTION" export.sql
    

    # Rules
    grep -i "CREATE RULE" export.sql

    Remove or comment out any matches. For extensions, keep only those DB9 supports: uuid-ossp, hstore, vector, plus pgcrypto and plpgsql, which DB9 accepts as metadata shims.

    Common cleanup patterns

    Terminal
    # Remove all CREATE EXTENSION except supported ones
    sed -E -i.bak -e '/CREATE EXTENSION/!b' -e '/uuid-ossp|hstore|vector|pgcrypto|plpgsql/!d' export.sql
    

    # Remove RLS
    sed -i.bak '/ENABLE ROW LEVEL SECURITY/d; /CREATE POLICY/,/;$/d' export.sql

    Or manually review and remove the flagged lines.

  3. Create the DB9 Database

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

    Database creation is synchronous and completes in under a second.

  4. Import into DB9

    Import with psql over pgwire in two passes: the schema while the tables are still empty, then the data streamed straight from the source. Apply the step 2 cleanup to schema.sql first — the same commands work on it.

    Terminal
    # 1. 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
    

    # 2. Import schema
    psql "$(db9 db connect my-app --output quiet)" -f schema.sql
    

    # 3. Stream data directly from source to DB9
    pg_dump --data-only --no-owner \
      "postgresql://user:pass@your-host:5432/your_database" \
      | psql "$(db9 db connect my-app --output quiet)"

    This pipes COPY statements through pgwire without intermediate files. DB9 supports COPY in TEXT and CSV formats. psql keeps going after an error, so scan its output for ERROR: lines.

    Import errors

    If import fails partway through:

    • Unsupported DDL — check the error message for the specific statement, remove it from the SQL file, and re-run.
    • Data type mismatch — DB9 does not support XML, CIDR/MACADDR, or most range types (INET is supported). Cast or remove these columns. Bit-string and composite columns fail in a less obvious way and are worth handling before you export. A bit(n) column with n of 2 or more checks that its input is a bit string, but does not enforce the declared length: short values are silently right-padded with zeros and long ones truncated, where PostgreSQL raises 22026 — and bit(n) is exactly what pg_dump emits for it, so nothing in the dump path flags the problem. Convert those to BYTEA or an integer bitmask before exporting. Single-flag bit(1) columns are fine as they are: they become BOOLEAN and round-trip '1'/'0' correctly. A varbit column happens to be safer only because pg_dump writes it as bit varying(n), which DB9 cannot parse. A composite column accepts a text literal without validating it and can never be read back field-wise — flatten it into scalar columns. See the compatibility matrix.
    • Encoding errors — DB9 is UTF-8 only. Non-UTF-8 data will fail with “invalid byte sequence for encoding UTF8”. Convert the source data to UTF-8 before export.

  5. Update Your Application

    Connection string

    Diff
    DATABASE_URL=postgresql://user:password@your-host:5432/your_database
    DATABASE_URL=postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres?sslmode=require

    Key differences:

    • Username: {tenant_id}.{role} format
    • Port: 5433
    • Database: Always postgres
    • TLS: Required (sslmode=require)

    Connection pooling

    If you use an external connection pooler (PgBouncer, pgpool), remove it and configure pooling in your application:

    TypeScript
    // node-postgres
    const pool = new pg.Pool({
      connectionString: process.env.DATABASE_URL,
      max: 10,
      idleTimeoutMillis: 30000,
    });
    Python
    # SQLAlchemy
    engine = create_engine(
        DATABASE_URL,
        pool_size=10,
        pool_pre_ping=True,
    )

    For ORM-specific connection setup, see the integration guides: Prisma, Drizzle, SQLAlchemy, GORM.

    LISTEN/NOTIFY

    LISTEN and NOTIFY work as they do in PostgreSQL: notifications are queued inside the transaction and delivered to listening sessions on commit.

    SQL
    -- session A
    LISTEN order_events;
    

    -- session B
    NOTIFY order_events, 'order-1234';

    LISTEN requires a session that stays open, so it must run over a direct pgwire connection (db9 db connect <db>, or any Postgres driver). The stateless HTTP SQL API — db9 db sql and the SDK’s sql() method — can issue NOTIFY, but cannot hold a subscription to receive.

    Sequences and SERIAL columns

    SERIAL, BIGSERIAL, and identity columns work in DB9 once step 4’s rewrite has been applied, and the data stream in step 4 already restores each sequence position — pg_dump ends its data section with setval calls. To check a sequence against its table:

    Terminal
    db9 db sql my-app -q "SELECT last_value FROM users_id_seq"
    db9 db sql my-app -q "SELECT max(id) FROM users"

    To move a sequence, pass setval a literal value. DB9 cannot take the value from a subquery — SELECT setval('users_id_seq', (SELECT max(id) FROM users)) fails with XX000 internal error — so have psql build the statement and run it with \gexec:

    Terminal
    psql "$(db9 db connect my-app --output quiet)" <<'SQL'
    SELECT format('SELECT setval(%L, %s, %L)', 'users_id_seq', COALESCE(max(id), 1), max(id) IS NOT NULL) FROM users \gexec
    SQL

  6. Validate

    Check schema

    Terminal
    db9 db dump my-app --ddl-only

    Compare with your original schema.

    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 against the source database.

    Run your test suite

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

    Common differences to watch for

    • SERIALIZABLE isolation — DB9 does not implement SERIALIZABLE. On the wire protocol it is accepted with a WARNING and silently downgraded to REPEATABLE READ, so code that relies on serializable guarantees (write-skew prevention) keeps running without failing. Audit those transactions and add explicit SELECT ... FOR UPDATE locks or unique constraints.
    • Index access methods — only btree, gin, and hnsw can actually be created. GiST, Hash, SP-GiST, and BRIN are rejected at CREATE INDEX time with access method "..." is not supported (0A000), so a dump that contains them will fail to restore those statements too. GIN itself is fully functional — full-text search and JSONB containment use index scans.
    • Advisory locks — pg_advisory_lock() and related functions are available, but coordination is node-local (not cross-process/global). For strict row-level coordination semantics, use SELECT ... FOR UPDATE.

Rollback Plan

Section titled “Rollback Plan”

Your source database is unchanged by the migration. To revert:

  1. Switch DATABASE_URL back to the original PostgreSQL connection string.
  2. If you need to export data created in DB9:
Terminal
# Small databases
db9 db dump my-app -o db9-export.sql
psql "postgresql://user:pass@your-host:5432/your_database" -f db9-export.sql


# Large databases — use COPY per table, over pgwire
psql "$(db9 db connect my-app --output quiet)" \
  -c "COPY users TO STDOUT WITH (FORMAT csv, HEADER)" > users.csv
psql "postgresql://user:pass@your-host:5432/your_database" \
  -c "COPY users FROM STDIN WITH (FORMAT csv, HEADER)" < users.csv

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, export 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, or accept a brief period of dual-writes.
  • UTF-8 only — DB9 does not support other encodings. Ensure your data is UTF-8 before export.
  • Plain SQL format — export with pg_dump’s default plain-text format; the SERIAL and identity rewrites in step 4 edit schema.sql as text.
  • 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 — see step 4.
  • No custom extensions — only the 9 built-in extensions are available. If your application depends on PostGIS, ltree, pg_trgm, or other community extensions, those features will not be available.
  • No custom index access methods — only btree, gin, and hnsw can be created.

Next Pages

Section titled “Next Pages”