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

Run the compatibility scan before starting

DB9 supports most PostgreSQL workloads, but some features are not available. Running these checks first avoids surprises mid-migration.

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

Quick compatibility scan

Connect to your source database and check for unsupported features:

SQL

-- Table partitioning (DB9: PARTITION BY RANGE on a timestamp/timestamptz key only)
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 (supported; create these roles in DB9 first, see step 2)
SELECT DISTINCT r AS policy_role
FROM pg_policies, unnest(roles) AS r
WHERE r <> 'public';

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

-- Logical replication (DB9 offers logical decoding from replication slots, but cannot subscribe)
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

SQL

SELECT extname FROM pg_extension WHERE extname NOT IN (
  'http', 'uuid-ossp', 'hstore', 'fs9', 'pg_cron', 'parquet', 'zhparser', 'vector',
  'embedding', 'pgcrypto', 'plpgsql'
) ORDER BY extname;

▶ Run

Anything this returns has no counterpart in DB9. CREATE EXTENSION accepts exactly the eleven names in the list above and raises 42704 extension is not available for every other name — PostGIS, pg_trgm, ltree, citext and the rest of the contrib ecosystem included. Eight of the eleven are functional: http, uuid-ossp (uuid_generate_v4() only), fs9, pg_cron, parquet, zhparser, vector and embedding.

The remaining three, hstore, pgcrypto and plpgsql, are metadata-only shims — hstore values, for one, are not supported. They are in the list because dumps containing them restore cleanly — not because they bring anything with them.

pgcrypto imports cleanly, then fails at call time

CREATE EXTENSION pgcrypto returns CREATE EXTENSION and writes a pg_extension row, so the query above will not flag it and the import will look clean. It registers no functions. 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 the migration you thought went fine.

gen_random_uuid() and digest() keep working, but they are DB9 built-ins and need no extension at all. Hash with digest(data, 'sha256'), sha256() or md5(); move password hashing and symmetric encryption into your application layer before you cut over. 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.

PL/pgSQL check

DB9 Difference: PL/pgSQL limitations

DB9 supports basic PL/pgSQL, including CONTINUE, dynamic EXECUTE, exception handling and nested blocks, but not WHILE or FOREACH loops, or cursor operations. The same rules apply to a procedure body run with CALL. Review all stored functions and procedures before migrating.

DB9 supports basic PL/pgSQL: variable declarations, IF/ELSIF, CASE, FOR loops (including EXIT WHEN and CONTINUE WHEN), PERFORM, RAISE, RETURN, dynamic EXECUTE with INTO and USING, exception handling (BEGIN...EXCEPTION) and nested BEGIN...END blocks — in a CREATE FUNCTION body, a DO block, and a procedure run with CALL. It does not support:

• WHILE and FOREACH loops
• Cursor operations

WHILE and FOREACH surface at call time, not at CREATE FUNCTION time

CREATE FUNCTION succeeds for a WHILE or FOREACH loop and the function is registered in pg_proc — the error is raised on each call, with 42601 (syntax error at or near "WHILE", or "FOREACH").

A migration that only replays DDL will therefore look clean for these. Exercise each migrated function before cutting over.

Cursor declarations are the exception: they are rejected when the function is created, and the function is not registered — c CURSOR FOR ... with 42601 (unexpected token in PL/pgSQL type declaration: FOR), and c refcursor with 42704 (type "refcursor" does not exist).

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';

Review any matches and rewrite them before migrating.

Trigger functions need one more check. CREATE TRIGGER ... BEFORE refuses a function that uses a counted FOR loop, a bare 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 FOR r IN SELECT … LOOP query loop is the exception and is allowed; it needs a declared record variable, which anything coming from PostgreSQL already has, since PostgreSQL rejects an undeclared one at CREATE FUNCTION. 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'   -- over-matches: CASE also hits CASE expressions and
                                               -- LOOP also hits FOR r IN SELECT ... LOOP, both accepted
       OR p.prosrc ~* '\mEXCEPTION\s+WHEN\M'
       OR p.prosrc ~* '\mBEGIN\M.*\mBEGIN\M');

What Changes

Area	Standard PostgreSQL	DB9
Connection string	postgresql://user:pass@host:5432/dbname	postgresql://<tenant_id>.<role>@pg.db9.io:5433/postgres
Port	5432 (default)	5433
Database name	Custom	Always postgres
Username	Standard roles	<tenant_id>.<role> format (e.g., a1b2c3d4e5f6.admin)
Transaction isolation	SERIALIZABLE fully enforced	READ COMMITTED and REPEATABLE READ enforced, but some write conflicts fail at COMMIT with 40001 instead of at the statement; SERIALIZABLE is downgraded to REPEATABLE READ with a warning on the wire protocol, and rejected with an error over the HTTP SQL API
Connection pooling	External (PgBouncer, pgpool)	Application-side pooling
Replication	Logical and streaming	Logical decoding from replication slots; no CREATE SUBSCRIPTION and no physical replication
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
Extensions	Community ecosystem	Fixed built-in set only: http, uuid-ossp, fs9, pg_cron, parquet, zhparser, vector, embedding — plus hstore, pgcrypto and plpgsql as metadata-only shims
Indexes	All types fully functional	B-tree, GIN, HNSW, and Hash (single-column, non-unique, integer/OID); GiST/SP-GiST/BRIN rejected

See the Compatibility Matrix for the complete list.

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 2 removes or rewrites.
• Data types — TEXT, INTEGER, BIGINT, BOOLEAN, TIMESTAMPTZ, UUID, JSONB, arrays, FLOAT8, NUMERIC, BYTEA, and vectors. A DB9 NUMERIC value holds at most 28–29 significant digits and rejects 'NaN' and 'Infinity': a row holding a larger or non-finite value fails to import (INSERT or COPY) with 22P02, and decimal places beyond that capacity are rounded off without an error — see Data types.
• 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

• 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 cleanup checks in step 2 read and edit the file as text, which a custom (-Fc) or directory (-Fd) archive is not.

   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

   Provider	Connection notes
   AWS RDS	Use the endpoint hostname and master user credentials. Ensure the security group allows outbound connections from your machine.
   Google Cloud SQL	Use Cloud SQL Auth Proxy or allowlist your IP. Direct connection: host:5432/dbname.
   Azure Database	Use the {user}@{server} username format Azure requires.
   DigitalOcean	Use 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
   
   # 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 the names DB9 accepts: http, uuid-ossp, fs9, pg_cron, parquet, zhparser, vector and embedding, plus pgcrypto and plpgsql, which DB9 accepts as metadata shims. Drop CREATE EXTENSION hstore too: DB9 accepts it, but does not support hstore values, and while it is registered pg_dump cannot dump your DB9 database.

   Partitioned tables need rewriting rather than removing. DB9 accepts PARTITION BY RANGE on a timestamp or timestamptz key only — LIST and HASH fail with 42601, and any other key type with 0A000 RANGE partitioning currently requires a timestamp or timestamptz key. Even a table that qualifies does not restore as dumped: pg_dump attaches each partition with ALTER TABLE ONLY ... ATTACH PARTITION, which DB9 does not parse (42601), so the partition is left as a stand-alone table whose rows never show through the parent. Replace each CREATE TABLE + ATTACH PARTITION pair with CREATE TABLE <partition> PARTITION OF <parent> FOR VALUES FROM (...) TO (...), and declare the parent’s keys in its own CREATE TABLE: once a partitioned table has partitions, CREATE INDEX on it fails with 0A000, and so does an ALTER TABLE that adds, drops, renames or alters its columns, keys or constraints (OWNER TO still works). Each partition gets its own copy of those keys, so drop the per-partition key statements pg_dump writes as well. A partitioned table cannot have an identity column — creating its first partition fails with 0A000 partition identity-column inheritance is not yet supported — so declare that column BIGSERIAL in the rewrite. After the import, only plain INSERT and SELECT go through the parent: UPDATE, DELETE, TRUNCATE and COPY ... FROM on it fail with 0A000, so run them against a partition. INSERT ... ON CONFLICT fails with 0A000 on the parent and on its partitions alike.

   Common cleanup patterns

   Terminal

   # Remove all CREATE EXTENSION except supported ones
   sed -E -i.bak -e '/CREATE EXTENSION/!b' \
     -e '/uuid-ossp|vector|embedding|http|fs9|pg_cron|parquet|zhparser|pgcrypto|plpgsql/!d' \
     export.sql

   Or manually review and remove the flagged lines.

   Keep vector, but rewrite how the dump names it. pg_dump writes the type, its operator classes and its operators schema-qualified — public.vector(1536), public.vector_cosine_ops, OPERATOR(public.<=>) — and DB9 accepts them only unqualified: the table’s CREATE TABLE fails with 42704 type "public.vector" does not exist, a view that uses OPERATOR(public.<=>) fails with internal error, and when psql reaches that table’s COPY it reads the rows as SQL — depending on the psql version, later tables can lose their rows too. Strip the qualifier:

   Terminal

   # Write the vector type, its operator classes and its operators unqualified
   sed -E -i.bak \
     -e 's/(^|[^[:alnum:]_])public\.vector([^[:alnum:]_]|_(l2|ip|cosine)_ops|$)/\1vector\2/g' \
     -e 's/OPERATOR\(public\.(<->|<#>|<=>)\)/\1/g' \
     export.sql

   Tables such as public.vectors or public.vector_store keep their qualified names. pgvector functions the dump writes qualified, such as public.l2_distance(...) in a view or a default, fail with 42883 the same way; remove their public. by hand.

   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 3 and before step 4. The compatibility scan above lists them. 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.

3. Create the DB9 Database

   Terminal

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

   ▶ Run

   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. Import schema
   psql "$(db9 db connect my-app --output quiet)" -f schema.sql
   
   # 2. 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.

   What a pg_dump file needs before it loads

   A standard pg_dump file does now import in a single pass, as written (checked on DB9 build 9b35dd37b): DB9 runs the nextval('public.users_id_seq'::regclass) defaults of SERIAL and BIGSERIAL columns, the ALTER TABLE ... ALTER COLUMN id ADD GENERATED ... AS IDENTITY statements that make a column an identity column, and the post-data key statements. Each identity column keeps its GENERATED ALWAYS or BY DEFAULT behaviour, and pg_dump’s data section sets every sequence position (see Sequence Functions). What still shapes how you run it:

   • Keep pg_dump’s default COPY data. With --inserts or --column-inserts, pg_dump writes each row of a table with a GENERATED ALWAYS identity column as INSERT ... OVERRIDING SYSTEM VALUE, and DB9 does not parse OVERRIDING (42601 syntax error at or near "OVERRIDING"), so none of that table’s rows load, and rows that reference them fail their foreign keys (23503).
   • Primary keys are added after the data, so import into a database that has never had a replication slot. pg_dump loads each table’s rows first and adds its primary key afterwards. DB9 runs that both with plain psql -f and inside one transaction (psql --single-transaction, -1). The exception is a database on which a logical replication slot has been created, even one dropped since: there, adding a primary key to a table that already holds rows fails with 55000 unjournaled restore or physical row rewrite is not supported while Realtime capture is enabled, and every foreign key that references that table fails after it. The two-pass import above adds every key while the tables are still empty, so it loads cleanly into either kind of database, and it stays the recommended path for a schema you have had to edit.
   • db9 db sql -f cannot run a pg_dump file, with or without --direct. By default it sends the file through the HTTP SQL API, which cannot carry COPY ... FROM stdin data (error: unexpected message from server) and rejects the \restrict line that current pg_dump releases write. The API also rejects a request larger than about 2 MB (Error 413) and returns Error 504 after about 15 seconds. Statements that had not run by then are skipped, and the ones that had are kept unless the file wraps them in BEGIN / COMMIT.

   db9 db connect returns a temporary DSN

   The DSN that db9 db connect prints carries a short-lived token (10 minutes). That is ample for a schema load or a CI job, but for a multi-hour bulk import use a static password instead — db9 db reset-password my-app, or create a dedicated user with db9 db users my-app create --username importer --password <password>.

   Do not substitute db9 db status --json | jq -r .connection_string here: that field deliberately omits the password, so psql fails with FATAL: Password authentication failed.

   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. timetz / time with time zone columns import, offsets included — except a column with DEFAULT CURRENT_TIME or DEFAULT now(), which fails with 42804; drop the default before exporting. Casts, interval arithmetic, AT TIME ZONE and EXTRACT do not work on them — see the compatibility matrix before relying on them in queries. Bit-string columns import cleanly: bit(n) and bit varying(n) — both of the spellings pg_dump emits — parse, and the declared length is enforced exactly as in PostgreSQL (22026 for a bit(n) mismatch, 22001 for an over-length varbit(n)), so no conversion is needed. The one difference is that bit(1) and bare bit become BOOLEAN; they round-trip '1'/'0' correctly but report as boolean in the catalog. Composite columns are the ones to handle before you export: 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: Supported, and used by default — set sslmode=require explicitly (the server does not enforce a minimum)

   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 a subscription must be held 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 import as they stand, 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"

   ▶ Run

   To move a sequence, setval takes its value straight from a subquery, so one statement does it:

   Terminal

   psql "$(db9 db connect my-app --output quiet)" \
     -c "SELECT setval('users_id_seq', (SELECT COALESCE(max(id), 1) FROM users))"

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"

   ▶ Run

   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. 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.
   • Index access methods — btree, gin, hnsw, and hash can be created; GiST, 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 is functional — full-text search and JSONB containment use index scans for a literal or a driver-bound probe. For full-text, a *_tsquery() call must name its configuration as a literal (websearch_to_tsquery('english', $1) is indexed); omitting or binding the configuration, or computing the probe with an expression (jsonb_build_object(...), ARRAY[lower(...)], a subquery), silently falls back to a sequential scan. Hash indexes are planned and used for equality lookups, within narrower bounds than PostgreSQL’s: integer and OID columns only (hash indexes currently support only integer and OID columns), no UNIQUE and no multicolumn (both 0A000). A hash index on a text column is the common case that does not carry over — use btree there.
   • 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

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

# Any size — pg_dump 17 or newer, over pgwire (see the notes below)
pg_dump --no-owner --no-privileges -N extensions -N cron \
  "$(db9 db connect my-app --output quiet)" -f db9-export.sql
psql "postgresql://user:pass@your-host:5432/your_database" -f db9-export.sql

# One table at a time — COPY, 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

COPY ... TO STDOUT requires pgwire, and only the table form

Run COPY ... TO STDOUT through psql, not through db9 db sql -q. The HTTP SQL API cannot carry a COPY stream and fails with error: unexpected message from server.

Only COPY <table> [(columns)] TO STDOUT is supported. The query form — COPY (SELECT ...) TO STDOUT, the usual way to export a filtered or joined result — is rejected on both transports with 0A000:

ERROR: Unsupported COPY TO STDOUT syntax. Supported: COPY [schema.]table [(col1, col2, ...)] TO STDOUT [WITH (options)]

To export a subset, wrap the query in a view and COPY the view — COPY <view> TO STDOUT works, including HEADER and a column list:

Terminal

psql "$(db9 db connect my-app --output quiet)" -q \
  -c "CREATE VIEW export_v AS SELECT id, email FROM users WHERE active" \
  -c "COPY export_v TO STDOUT WITH (FORMAT csv, HEADER)" > active_users.csv

-q matters here: without it psql writes the CREATE VIEW command tag to stdout and it lands in the CSV. Keep COPY as the only statement in its own -c as well — a COPY sent in a multi-statement batch (SELECT 1; COPY ...) fails with a bare internal error (0A000).

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 as above, or export individual tables with COPY. What to know about pg_dump against DB9:

• Use version 17 or newer. DB9 reports server version 17.0, and an older pg_dump stops with server version mismatch.
• Keep the flags. -N extensions is what lets the dump finish: without it, a registered fs9 or embedding extension stops it with unrecognized provolatile value for function .... Together with -N cron it also keeps DB9’s own extensions and cron schemas out of the dump. --no-owner --no-privileges leaves out DB9’s admin role and its grants, which do not exist on your server.
• What it cannot dump. A database that has a partitioned table (cannot export persisted RANGE partition ...), or one with the hstore extension registered (column "typanalyze" does not exist — run DROP EXTENSION hstore first). A parallel dump (-j) fails as well, because pg_export_snapshot() does not exist; dump serially.
• Create vector tables on the target first. A vector column is dumped as a bare vector, with no schema and no dimension, so its CREATE TABLE fails on a pgvector server. Worse, once that table’s COPY fails, psql reads its rows as SQL and loses the COPY of the tables the dump writes after it too. Before importing, run CREATE EXTENSION vector on the target and create each table that has a vector column yourself, with its dimension. The import still prints errors for those tables — type "vector" does not exist for the dump’s CREATE TABLE, and already exists or multiple primary keys for the sequences and keys you created — but it loads their rows. Recreate their HNSW indexes afterwards.
• Importing into PostgreSQL 16 or older prints one harmless error, unrecognized configuration parameter "transaction_timeout".

Caveats

• No zero-downtime migration — DB9 cannot subscribe to logical replication (CREATE SUBSCRIPTION is not supported), so it cannot follow the source database while you cut over. 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 so the step 2 checks can read and edit it.
• 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 — the built-in set is fixed and compiled into the server, so there is nothing to install and no version skew between databases, but also no way to add to it. If your application depends on PostGIS, ltree, pg_trgm, or other community extensions, those features will not be available and CREATE EXTENSION raises 42704. Watch pgcrypto in particular: the statement is accepted but supplies no functions, so crypt()/gen_salt() fail at call time rather than at import — see the extension check above.
• No custom index access methods — only btree, gin, hnsw, and hash can be created, and hash is limited to single-column, non-unique indexes on integer or OID columns.

Next Pages

• Compatibility Matrix — full list of supported and unsupported features
• Connect — connection string format and authentication
• Migrate from Neon — Neon-specific migration
• Migrate from Supabase — Supabase-specific migration
• Production Checklist — deployment readiness
• Limits and Quotas — operational limits