Skip to content
Discord Get Started

Migrate from Amazon RDS

This guide covers migrating from Amazon RDS for PostgreSQL (or Aurora PostgreSQL) to DB9. The process uses standard 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

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 RDS PostgreSQL. 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”
AreaAmazon RDS PostgreSQLDB9
Connection stringpostgresql://master:pass@instance.region.rds.amazonaws.com:5432/dbnamepostgresql://<tenant_id>.<role>@pg.db9.io:5433/postgres
Port5432 (default)5433
Database nameCustomAlways postgres
Connection poolingExternal (PgBouncer on EC2, RDS Proxy)No built-in pooler — use application-side pooling
Extensions80+ supportedFixed built-in set: http, vector, fs9, pg_cron, embedding, uuid-ossp, parquet, zhparser — plus hstore, pgcrypto and plpgsql as metadata-only shims
IAM authenticationSupportedNot supported — use connection string credentials
Read replicasSupportedNot supported
ReplicationLogical and streaming replicationLogical decoding from replication slots; no CREATE SUBSCRIPTION and no physical replication
Table partitioningSupportedPARTITION BY RANGE on a timestamp or timestamptz key only
LISTEN/NOTIFYSupportedSupported over pgwire (delivered on commit); the stateless HTTP SQL API can NOTIFY, but a LISTEN there is accepted and never receives anything
Automated backupsPoint-in-time recovery, snapshotsCLI-based backup (db9 db dump)
Multi-AZAutomatic failoverManaged by DB9

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

Prerequisites

Section titled “Prerequisites”
  • Access to your RDS instance (master user credentials or IAM auth)
  • Network access to the RDS instance (your machine must be able to reach it — check security groups and VPC settings)
  • pg_dump installed locally (should match or be close to your RDS 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. Ensure Network Access to RDS

    RDS instances are typically inside a VPC. To run pg_dump from your local machine, you need one of:

    • Public accessibility enabled on the RDS instance, with your IP allowed in the security group
    • SSH tunnel through a bastion host in the same VPC
    • AWS SSM Session Manager port forwarding
    Terminal
    # SSH tunnel example
    ssh -L 5432:my-instance.region.rds.amazonaws.com:5432 ec2-user@bastion-host
    

    # Then use localhost:5432 in your pg_dump command

    If using IAM authentication, generate a temporary password:

    Terminal
    export PGPASSWORD=$(aws rds generate-db-auth-token \
      --hostname my-instance.region.rds.amazonaws.com \
      --port 5432 \
      --username master \
      --region us-east-1)

  2. Export from RDS

    Use pg_dump with your RDS connection details.

    Schema and data (plain SQL format)

    Terminal
    pg_dump --no-owner --no-privileges --no-comments \
      "postgresql://master:password@my-instance.region.rds.amazonaws.com:5432/mydb?sslmode=require" \
      > export.sql

    Schema only

    Terminal
    pg_dump --schema-only --no-owner --no-privileges \
      "postgresql://master:password@my-instance.region.rds.amazonaws.com:5432/mydb?sslmode=require" \
      > schema.sql

    Flags explained:

    • --no-owner — omits ALTER ... OWNER TO statements that reference RDS-specific roles (rds_superuser, rds_replication, etc.)
    • --no-privileges — omits GRANT/REVOKE statements for RDS role hierarchy
    • --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.

    Exclude RDS-internal schemas

    If your dump includes RDS-specific schemas, exclude them:

    Terminal
    pg_dump --no-owner --no-privileges --no-comments \
      -N rdsadmin -N rds_tools \
      "postgresql://master:password@my-instance.region.rds.amazonaws.com:5432/mydb?sslmode=require" \
      > export.sql

  3. Clean the Export

    RDS exports may contain features DB9 does not support. Remove or comment out:

    • CREATE EXTENSION for extensions DB9 does not have — RDS offers 80+ extensions; DB9 accepts a fixed set. 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.
    • Foreign data wrappers — CREATE SERVER, CREATE FOREIGN TABLE (common with postgres_fdw or mysql_fdw on RDS).

    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
    

    # Check for FDW
    grep -i "CREATE SERVER\|FOREIGN TABLE" 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

    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 RDS. 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 RDS into DB9
    pg_dump --data-only --no-owner \
      "postgresql://master:password@my-instance.region.rds.amazonaws.com:5432/mydb?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.

  6. Update Your Application

    Connection string

    Replace the RDS connection string with DB9’s:

    Diff
    DATABASE_URL=postgresql://master:password@my-instance.region.rds.amazonaws.com:5432/mydb?sslmode=require
    DATABASE_URL=postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres?sslmode=require

    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 (not region-specific endpoints)

    IAM authentication

    If you use RDS IAM authentication to generate temporary passwords, remove that logic. DB9 uses static credentials in the connection string.

    RDS Proxy

    If you use RDS Proxy for connection pooling, remove it and configure pooling at the application level:

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

    AWS SDK integrations

    If your application uses AWS SDK to interact with RDS (automated snapshots, instance management), those APIs will not apply to DB9. Use the DB9 CLI for database management instead.

    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 RDS 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
    • 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). GIN is functional for a literal or bound-parameter probe — for @@, a tsquery value or a *_tsquery() call that names its configuration (omitting or binding the configuration, or a probe computed by an expression, falls back to a sequential scan), and hash covers equality lookups on single, non-unique integer or OID columns

Rollback Plan

Section titled “Rollback Plan”

If you need to revert:

  1. Your RDS instance is unchanged — switch DATABASE_URL back to the RDS connection string.
  2. If you need to export data created in DB9 back to RDS:
Terminal
# Export from DB9
db9 db dump my-app -o db9-export.sql


# Import to RDS
psql "postgresql://master:password@my-instance.region.rds.amazonaws.com:5432/mydb?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

Section titled “Caveats”
  • No zero-downtime migration — DB9 cannot subscribe to logical replication (CREATE SUBSCRIPTION is not supported), so you cannot use RDS logical replication to stream changes into it. Plan a maintenance window or accept a brief cutover period.
  • Extension gaps — RDS offers 80+ extensions; DB9 ships a fixed set compiled into the server. Check your CREATE EXTENSION statements carefully. Common RDS extensions with no DB9 equivalent include PostGIS, pg_trgm, pg_stat_statements and ltree — each raises 42704 at import. 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.
  • 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.
  • No read replicas — RDS supports read replicas for scaling reads. DB9 does not have read replicas. If your application relies on read/write splitting, consolidate to a single connection string.
  • No automated backups — RDS provides automated point-in-time recovery and snapshots. DB9 uses CLI-based backup (db9 db dump). Set up your own backup schedule.
  • AWS ecosystem loss — CloudWatch metrics, Performance Insights, and other AWS-integrated monitoring will not apply. Use DB9’s built-in monitoring.

Next Pages

Section titled “Next Pages”