Move your Postgres to BataDB

One command copies your database into BataDB, then checks its own work. It works from any Postgres you can reach with a connection string: Neon, Supabase, AWS RDS and Aurora, PlanetScale, Railway, Render, DigitalOcean, Heroku, or your own server.

npm install -g @batadata/cli
bata login
bata import --source "$DATABASE_URL" --name shop

A real run: a 105 MB app database with 9 tables, moved and verified in 37 seconds, including creating the project.

  Import into BataDB

  › Inspecting source database
  › Source: shop · PostgreSQL 17.10 (Debian 17.10-1.pgdg12+1) · 105 MB · 9 tables
  › Checking pg_dump / psql versions
  › Creating project shop (serverless · PostgreSQL 17)
  › Waiting for target compute to be ready
  ›   …still waiting (15s)
  › Checking target extension availability
  › Migrating schema + data (pg_dump | psql)
  › Verifying row counts + sequences

> Imported into <project-id> - 9 tables verified.

  Project ID   <project-id>
  PostgreSQL   17
  Connection   postgresql://<redacted>

  Connect: bata connect <project-id>
  Connection string: bata db url --project <project-id>

That source had foreign keys, serial and identity columns, a standalone sequence, an enum, jsonb, arrays, a view, a trigger, trigram and vector indexes, and half a million pgbench rows. A schema diff of both sides came back empty, and the next insert on BataDB took the next id. Your source is only read, never changed.

Moving from Neon? Migrating from Neon is the deep dive.

Before you start#

Client tools#

bata import runs the standard pg_dump and psql on your machine. Install both from the same PostgreSQL release, at least the source server's major version: psql must also be at least as new as pg_dump.

pg_dump --version
psql --version

# macOS
brew install postgresql@17
export PATH="$(brew --prefix postgresql@17)/bin:$PATH"

# Ubuntu or Debian
sudo apt install postgresql-client-17

Use 18 instead of 17 if your source runs PostgreSQL 18. Too old is caught before anything is created:

  › Inspecting source database
  › Source: shop · PostgreSQL 17.10 (Debian 17.10-1.pgdg12+1) · 105 MB · 9 tables
  › Checking pg_dump / psql versions
Error: Your pg_dump is version 16, but the source server is PostgreSQL 17. A client older than the source can't reliably migrate it (e.g. psql 16 can't parse pg_dump 17's \restrict meta-commands). Install PostgreSQL 17 (or newer) client tools and re-run.
  macOS: brew install libpq  ·  Ubuntu: sudo apt install postgresql-client-17

BataDB runs PostgreSQL 16 and 17. A new project matches your source's major, so a 16 stays 16. A 14 or 15 lands on 16, and an 18 lands on 17.

Your account#

On the Free plan, add a card first (bata billing card add): a Free team runs no compute without one, and the free allowance stays free. Free also stops at 1 GB of storage by default. For a bigger database, turn on pay as you go with bata billing stop-at-limit off. See Pricing and billing.

The direct URL, not the pooled one#

Most hosts give you two connection strings. Use the direct one.

pg_dump opens its session with session-level settings: no timeouts, and an empty search_path. Through a transaction pooler, those land on a shared server connection that your app's next request picks up. Its queries would then run with no timeout, and unqualified table names would stop resolving.

So bata import refuses anything shaped like a pooler before it connects. This is a real transaction-mode PgBouncer:

$ bata import --source "$POOLED_URL" --name shop
Error: --source looks like a connection pooler (port 6543 is a connection-pooler port). bata import needs a direct connection.
  pg_dump sets session settings (timeouts, search_path) that a transaction pooler would leak to your database's other clients. Use the direct connection string instead. Neon: remove -pooler from the host (and from options=endpoint=... if present). Supabase: db.<project-ref>.supabase.co on port 5432. Render: the External Database URL on port 5432, not the 6432 pool. Heroku: DATABASE_URL, not DATABASE_CONNECTION_POOL_URL. DigitalOcean: the cluster on port 25060, not a 25061 pool. Railway: DATABASE_PUBLIC_UNPOOLED_URL when PgBouncer is on. If this is a direct server, or a pooler in SESSION mode, add --allow-pooled-source.

It exits with code 5 and, with --json, the code POOLED_SOURCE. The same database through its direct URL imported cleanly.

The check reads the host, the port and the parameters: a pooler host, port 6543 or 6432, pgbouncer=true, and the pool ports of Heroku, DigitalOcean and Crunchy Bridge. It cannot see a pooler hidden behind a generic proxy, so pick the direct URL yourself. Add --allow-pooled-source only for a direct server that happens to look pooled, or a pooler in session mode.

Reach the source from where you run the import#

Run the import from a machine that can open a connection to the source: allow its IP in the host's firewall, IP allow list or security group. Keep the host's sslmode in the URL. For a database on a private network, run the import from a machine inside it, or tunnel (see Handy commands).

Find your direct URL#

Every host below has a pooled string and a direct one. This is where each keeps the direct one.

Neon#

• Use: the connection string with Connection pooling turned off in the Connect dialog. Its host has no -pooler.
• Not: the -pooler host. If your string has options=endpoint=...-pooler, remove -pooler there too.
• New Neon projects run PostgreSQL 18: install the 18 client tools. It lands on a PostgreSQL 17 project.

bata import --source "$NEON_DATABASE_URL" --name my-app

Flags, phases, the version gap and the Neon cutover are in Migrating from Neon.

Supabase#

• Use: the Direct connection string from the project's Connect panel: db.<project-ref>.supabase.co on port 5432.
• Not: the transaction pooler on port 6543.
• The direct host is IPv6-only unless you bought Supabase's IPv4 add-on. On an IPv4-only network, use the Session pooler string instead and add --allow-pooled-source: session mode keeps one server connection per client, so nothing leaks.
• Your database moves. Supabase Auth, Storage and Edge Functions stay behind.
• Supabase databases carry Supabase's own schemas, roles and extensions, and your policies probably name the anon and authenticated roles. Create those roles on an empty BataDB project first, then import into that project (Roles your policies name). If the import stops on something else of Supabase's, the error names it: drop it on the source if you don't use it, or leave its schema out with the manual pipe.

bata create my-app            # prints the project id
# create the roles your policies name, then:
bata import --source "$SUPABASE_DB_URL" --project <project-id> --yes

AWS RDS#

For RDS and Aurora alike.

• Use: the instance endpoint (RDS) or the cluster's writer endpoint (Aurora), port 5432.
• Not: an RDS Proxy endpoint.
• The database must be reachable from your machine: public access on, and an inbound rule for your IP on port 5432 in its security group. Turn both off again after the move.
• Add ?sslmode=require. Newer engine versions refuse connections without TLS.
• A paused Aurora Serverless cluster wakes on the first connection, which can take longer than the import's 10-second connect timeout. Wake it first with psql "$RDS_DATABASE_URL" -c 'select 1', or run the import with PGCONNECT_TIMEOUT=60.

bata import --source "$RDS_DATABASE_URL" --name my-app

PlanetScale#

• Use: the connection string on port 5432, which goes straight to Postgres.
• Not: the PgBouncer port, 6432.
• SSL is required. Keep the sslmode the dashboard gives you.

bata import --source "$PLANETSCALE_DATABASE_URL" --name my-app

Railway#

• Use: DATABASE_PUBLIC_URL from the database service's Variables tab. If you added PgBouncer to the service, use DATABASE_PUBLIC_UNPOOLED_URL.
• Not: the pooled public URL. It sits behind Railway's TCP proxy, so the pooler check cannot spot it: pick the unpooled one yourself.
• Turn on public networking for the database first.
• Railway's Postgres template runs PostgreSQL 18: install the 18 client tools.

bata import --source "$DATABASE_PUBLIC_URL" --name my-app

Render#

• Use: the External Database URL from the database's Info page, port 5432.
• Not: the PgBouncer pool URL.
• If you restricted access, allow your IP under the database's Access Control. TLS is always on.

bata import --source "$RENDER_EXTERNAL_DATABASE_URL" --name my-app

DigitalOcean#

• Use: the cluster's connection details (port 25060).
• Not: a connection pool's details (port 25061).
• Add your IP to the cluster's trusted sources. SSL is required (sslmode=require).

bata import --source "$DO_DATABASE_URL" --name my-app

Heroku#

• Use: DATABASE_URL, on port 5432: heroku config:get DATABASE_URL -a <your-app>.
• Not: DATABASE_CONNECTION_POOL_URL (port 5433).
• Add ?sslmode=require. Heroku can rotate the credentials at any time, so fetch the URL right before you run the import.
• Private and Shield databases cannot be reached from outside their Space. Run the import from inside it.

bata import --source "$HEROKU_DATABASE_URL" --name my-app

Self-hosted#

On a VPS, in Docker, or on your laptop.

• Use: the Postgres server itself, usually port 5432.
• Not: a PgBouncer in front of it.
• Allow the importing machine in pg_hba.conf and the firewall. Or skip both: install the CLI on the server and import from localhost, or tunnel over SSH (see Handy commands).

bata import --source "$SOURCE_DATABASE_URL" --name my-app

What import checks#

Every check runs before the step that needs it, so a problem stops the import early and says what to fix.

Source#

A wrong password, host or sslmode fails here, with the server's own message:

  › Inspecting source database
Error: Could not read the source database: connection to server at "<host>", port <port> failed: FATAL:  password authentication failed for user "postgres"
  Check the --source URI (host, credentials, sslmode) and that the server is reachable.

Extensions#

BataDB offers more than 90 extensions, including pgcrypto, uuid-ossp, citext, pg_trgm, hstore and vector. The check compares your source against the exact list the new project offers, before any data moves. This source still had adminpack, which PostgreSQL 17 removed:

  › Checking target extension availability
Error: The source uses extension(s) the target can't provide: adminpack.
  BataDB may not offer these yet. Drop them on the source (DROP EXTENSION …) before importing, or contact BataDB support to request them.

When the check fails on a project the import just created, it deletes that project for you. To see the full list, run this on any BataDB project:

bata db query "SELECT name FROM pg_available_extensions ORDER BY name" --project <project-id>

Restore#

The restore streams pg_dump --no-owner --no-privileges into psql -v ON_ERROR_STOP=1, statement by statement, and stops at the first error. A common one is a role. Roles are not copied, and this source had a row-level security policy for an app_user role:

  › Migrating schema + data (pg_dump | psql)

  Error: Restore failed - the target rejected part of the dump.

    ERROR:  role "app_user" does not exist

The fix is to create the role on BataDB, then import again. That run passed verification with its policy in place. See Roles your policies name.

A failed restore keeps the project, with whatever it restored so far, so you can look at it. For a clean retry, delete it and import again:

bata projects delete <project-id> --yes
bata import --source "$DATABASE_URL" --name my-app

Verify#

After the restore, the import counts the rows of every table it copied and reads every sequence, on both sides. Any difference fails the import with a table of what differs, so a success line means they matched. Importing into an existing project with --project --yes, tables and sequences that only the target has are left alone and listed in the result, not counted as a difference. With --json, the result carries the numbers:

"verify": {
  "tables_checked": 9,
  "sequences_checked": 5,
  "extensions_missing_on_target": [],
  "target_only_tables": [],
  "target_only_sequences": []
}

Verify compares counts and sequence values, not every row's contents. For a schema check on top, see Handy commands.

Every new BataDB project comes with a small public.health_check table and two internal schemas, neon and neon_migration. The import leaves them out of its comparison. A table of yours with the same name is copied and checked like any other.

The cutover#

The import is the easy part. This is the order that keeps a production move boring.

1. Rehearse. Import into a throwaway project: bata import --source "$DATABASE_URL" --name my-app-rehearsal. Time it: that is roughly your write freeze. Point a staging deploy at it and run your tests. Need a scratch copy for a risky test? Branch it with bata db branch create test --project <project-id>: a copy-on-write fork of the imported data. Delete the rehearsal project when you are done.

2. Freeze writes on the source. Turn on maintenance mode, scale background workers to zero and pause cron jobs. pg_dump reads one consistent snapshot, so anything written after it starts is not copied.

3. Import for real. bata import --source "$DATABASE_URL" --name my-app. It verifies itself. Spot-check a table you care about anyway.

4. Swap the connection strings. Your app gets the pooled string, migrations get the direct one:

   Terminal

   bata db url --project <project-id> --json   # prints "pooled" and "direct"

   Set DATABASE_URL to pooled and DIRECT_URL to direct in every environment. See Connecting.

5. Point your ORM's migrations at the direct string. Prisma takes a session-level advisory lock, which a transaction pooler cannot hold:

   Prisma schema

   datasource db {
     provider  = "postgresql"
     url       = env("DATABASE_URL") // pooled: the app
     directUrl = env("DIRECT_URL")   // direct: prisma migrate
   }

   Drizzle, Turbine, Knex, Rails and Django: run migrations with the direct string, serve traffic with the pooled one.

6. Give deployed code an app key, if it uses one. Code that talks to BataDB over HTTP, such as an edge function, gets an app key: it never expires, rotates without downtime, and can only touch the tables you grant.

   Terminal

   bata keys create --app --name web --project <project-id> --grant select:orders --grant insert:orders

   Never deploy an admin key or your laptop's login.

7. Deploy, smoke test, unfreeze. Watch errors and bata usage for a day.

8. Keep the old database as your rollback. The import never writes to the source. Rolling back is pointing DATABASE_URL back at it. Anything written to BataDB after the switch exists only on BataDB, so pick your rollback window before you switch, and keep the old database, untouched, until you are past it.

One import moves one database: the one named in the URL. A server with several databases takes one import per database, each into its own project.

Handy commands#

Know your source#

Version, size, tables, extensions, the roles your row-level security policies name, and the other databases on the server, in one query:

-- preflight.sql
SELECT current_setting('server_version') AS version,
       pg_size_pretty(pg_database_size(current_database())) AS size,
       (SELECT count(*) FROM pg_tables
         WHERE schemaname NOT IN ('pg_catalog', 'information_schema')) AS tables,
       (SELECT string_agg(extname, ', ' ORDER BY extname) FROM pg_extension) AS extensions,
       (SELECT string_agg(DISTINCT r, ', ') FROM pg_policies, unnest(roles) AS r
         WHERE r <> 'public') AS roles_in_policies,
       (SELECT string_agg(datname, ', ' ORDER BY datname) FROM pg_database
         WHERE NOT datistemplate AND datname <> 'postgres') AS databases;

$ psql "$SOURCE_URL" -X -x -f preflight.sql
-[ RECORD 1 ]-----+-------------------------------------------
version           | 17.10 (Debian 17.10-1.pgdg12+1)
size              | 105 MB
tables            | 9
extensions        | citext, pg_trgm, pgcrypto, plpgsql, vector
roles_in_policies |
databases         | shop

Import variants#

# New project named after the source database
bata import --source "$DATABASE_URL"

# New project, your name for it
bata import --source "$DATABASE_URL" --name my-app

# Pick the PostgreSQL major of the new project (16 or 17)
bata import --source "$DATABASE_URL" --name my-app --pg 16

# Into an existing project that already holds objects
bata import --source "$DATABASE_URL" --project <project-id> --yes

# For CI and agents: one JSON result, the exit code tells you what happened
bata import --source "$DATABASE_URL" --name my-app --json > import.json
jq -r '.target.project_id' import.json

# A source that is slow to wake (a paused serverless database)
PGCONNECT_TIMEOUT=60 bata import --source "$DATABASE_URL" --name my-app

Exit codes: 0 success, 1 a failed check, restore or verify, 4 not signed in, 5 the command needs a change (a pooled source, a missing flag, or objects already in the target without --yes), 6 the target is still starting, 7 billing needs a person. The full list is in the CLI reference.

Connection strings#

bata db url --project <project-id>          # the direct string alone, for scripts
bata db url --project <project-id> --json   # direct and pooled, plus project and branch
TARGET_URL=$(bata db url --project <project-id>)

Rehearse on branches#

bata db branch create rehearsal --project <project-id> --expires-in 7d
bata db branches --project <project-id>
bata db url --project <project-id> --branch rehearsal
bata db branch delete rehearsal --project <project-id> --yes

Check row counts and sequences yourself#

The import already does this. To see it with your own eyes, save these two files and run them on both sides. If your source has its own public.health_check table, take it out of the exclusions.

-- counts.sql: one exact row count per table
SELECT string_agg(format('SELECT %L AS t, count(*) AS n FROM %I.%I',
                         schemaname || '.' || tablename, schemaname, tablename),
                  ' UNION ALL ' ORDER BY schemaname, tablename) || ' ORDER BY 1'
  FROM pg_tables
 WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'neon', 'neon_migration')
   AND (schemaname, tablename) <> ('public', 'health_check')
\gexec

-- sequences.sql: where every sequence stands
SELECT schemaname || '.' || sequencename AS seq, last_value
  FROM pg_sequences
 WHERE schemaname NOT IN ('neon', 'neon_migration')
   AND (schemaname, sequencename) <> ('public', 'health_check_id_seq')
 ORDER BY 1;

$ psql "$SOURCE_URL" -XAt -v ON_ERROR_STOP=1 -f counts.sql > source-counts.txt
$ psql "$TARGET_URL" -XAt -v ON_ERROR_STOP=1 -f counts.sql > target-counts.txt
$ diff source-counts.txt target-counts.txt && echo "row counts match"
row counts match
$ cat target-counts.txt
public.customers|1990
public.events|50000
public.order_items|59501
public.orders|19900
public.pgbench_accounts|500000
public.pgbench_branches|5
public.pgbench_history|0
public.pgbench_tellers|50
public.products|300
$ psql "$SOURCE_URL" -XAt -v ON_ERROR_STOP=1 -f sequences.sql > source-seqs.txt
$ psql "$TARGET_URL" -XAt -v ON_ERROR_STOP=1 -f sequences.sql > target-seqs.txt
$ diff source-seqs.txt target-seqs.txt && echo "sequences match"
sequences match
$ cat target-seqs.txt
public.customers_id_seq|2000
public.events_id_seq|50000
public.invoice_number_seq|1041
public.orders_id_seq|20000
public.products_id_seq|300

Read any error before you trust a match: two empty files also compare equal.

customers has 1,990 rows but its sequence stands at 2,000: rows were deleted on the source. The sequence came across exactly, so the next customer on BataDB gets id 2001, not a duplicate.

Compare the schemas#

A schema-only dump of each side, minus what BataDB adds to every project. --exclude-extension needs pg_dump 17 or newer.

set -o pipefail
dump_schema() {
  pg_dump "$1" --schema-only --no-owner --no-privileges \
    --exclude-schema=neon --exclude-schema=neon_migration \
    --exclude-table=public.health_check \
    --exclude-extension=neon --exclude-extension=pg_stat_statements \
  | grep -v -E '^(--|\\(un)?restrict )'
}

$ dump_schema "$SOURCE_URL" > source-schema.sql && dump_schema "$TARGET_URL" > target-schema.sql
$ diff source-schema.sql target-schema.sql && echo "schemas match"
schemas match

If your source uses pg_stat_statements too, drop that exclude so both sides keep it.

Roles your policies name#

bata import copies tables, data, indexes, views, functions, triggers and policies, but not roles, grants or ownership: everything belongs to your BataDB project's owner. A policy that names a role needs that role on BataDB first. This creates every role your policies name, as a role that cannot log in:

psql "$SOURCE_URL" -XAt -v ON_ERROR_STOP=1 \
  -c "SELECT DISTINCT format('CREATE ROLE %I NOLOGIN;', r) FROM pg_policies, unnest(roles) AS r WHERE r <> 'public'" \
  | psql "$TARGET_URL" -X -v ON_ERROR_STOP=1

The source writes the statements and format('%I') quotes each name, so an odd role name stays a name. If a role already exists on the target, psql stops and names it.

Run it against an empty project before you import, for example one made with bata create, then import with --project <project-id> --yes. For a role your app logs in as, use bata db roles create <name> instead: it gives the role a password.

Manual pipe: pg_dump | psql#

Use bata import when you can. Use the pipe when you need to shape the dump: one schema, or a table left out.

set -o pipefail
pg_dump "$SOURCE_URL" --no-owner --no-privileges --encoding=UTF8 \
    --exclude-schema=neon --exclude-schema=neon_migration \
  | awk 'p{print;next}
         /^[[:space:]]*$/||/^--/||/^\\(un)?restrict/||/^SET .*;[[:space:]]*$/||/^SELECT pg_catalog\.set_config\(.*\);[[:space:]]*$/{
           if($0 ~ /^SET[[:space:]]+transaction_timeout/)next; print; next }
         {p=1;print}' \
  | psql "$TARGET_URL" -X -q -v ON_ERROR_STOP=1 > /dev/null

• TARGET_URL is the direct string of an empty project: TARGET_URL=$(bata db url --project <project-id>).
• set -o pipefail matters. Without it the pipe reports success when pg_dump fails, because psql happily restores an empty stream.
• The awk step removes SET transaction_timeout = 0;, which pg_dump 17 and newer write and a PostgreSQL 16 target rejects (unrecognized configuration parameter "transaction_timeout"). It only touches the opening settings, never your data. A 17 target doesn't need it, and it does no harm there.
• Shape the dump with pg_dump flags: --exclude-schema=<name> to leave a schema out, --exclude-table=audit_log to leave a table out, --exclude-table-data=audit_log to keep the table but not its rows.
• Avoid --schema=public. It writes CREATE SCHEMA public, which stops the restore because every database already has one, and it leaves out every CREATE EXTENSION.
• Then check the result with counts.sql and sequences.sql. The pipe does not verify anything.

Reach a private source#

Tunnel to a database that only listens inside its own network, then import from the tunnel's local end:

ssh -N -L 15432:localhost:5432 you@your-server &
bata import --source "postgresql://<user>:<password>@localhost:15432/<database>" --name my-app

Leaving again#

The same tools work in the other direction. BataDB is plain Postgres, so pg_dump against your project's direct string makes a normal dump you can restore anywhere. Leave out what BataDB adds to every project (--exclude-extension needs pg_dump 17 or newer):

pg_dump "$(bata db url --project <project-id>)" --no-owner --no-privileges \
    --exclude-schema=neon --exclude-schema=neon_migration \
    --exclude-table=public.health_check --exclude-extension=neon > my-app.sql

Troubleshooting#

See also#

• Migrating from Neon: the Neon deep dive.
• Migrating from Prisma: moving a Prisma app's data and code.
• Connecting: pooled and direct strings, Prisma, scale to zero.
• App keys: the key for a deployed app.
• bata import --help: the flags of your installed CLI.