Migrate from Neon

bata import moves a Postgres database into a new BataDB project with one command. The source can be a Neon project, an RDS instance, a local database, or anything else that speaks Postgres.

It uses the standard pg_dump and psql client tools, so there is no proprietary format. The dump streams straight into the target without touching your disk. Before it reports success, it compares row counts and sequence values on both sides.

Moving from another host? Move your Postgres to BataDB is the general guide: where every host keeps its direct URL, real output from each check, the cutover and handy commands. This page is the Neon deep dive.

BataDB runs PostgreSQL 16 and 17. A source on PostgreSQL 18 (Neon offers it) lands on a PostgreSQL 17 project. Most schemas move cleanly across that gap. The version caveat explains what does not.

Prerequisites#

1. The bata CLI, signed in with bata login or an API key:

   Terminal

   npm install -g @batadata/cli
   bata login                              # or: export BATA_API_KEY=<your-api-key>

2. A card on file, on the Free plan. A Free team runs no compute until a card is on file, so the import cannot create its target without one. Get a link with bata billing card add. The free allowance stays free.

3. Room for the data, on the Free plan. Free includes 1 GB of storage and, by default, stops at its allowance instead of billing overage. When your database is larger, turn on pay as you go first with bata billing stop-at-limit off. See Pricing and billing.

4. Local pg_dump and psql at least as new as the source server. pg_dump can dump its own major version or older, never a newer one. psql must also be at least as new as pg_dump, because it replays the stream pg_dump writes. For a PostgreSQL 18 source, install the 18 client tools:

   Terminal

   # macOS (Homebrew)
   brew install postgresql@18
   export PATH="$(brew --prefix postgresql@18)/bin:$PATH"
   
   # Ubuntu or Debian (from the PostgreSQL apt repository if your release
   # does not ship version 18)
   sudo apt install postgresql-client-18

   Check what you have:

   Terminal

   pg_dump --version
   psql --version

   You do not have to get this right by hand. bata import checks both versions before it creates anything, and names the version to install when one is too old.

The command#

bata import --source <postgres-uri> [--name <name> | --project <id>] [--pg <major>] [--allow-pooled-source] [--yes] [--json]

--name and --project cannot be combined: one creates a project, the other reuses one.

Examples#

# New project named after the source database ("neondb")
bata import --source "postgresql://<user>:<password>@ep-example-123456.us-east-2.aws.neon.tech/neondb?sslmode=require"

# New project with an explicit name
bata import --source "$NEON_DATABASE_URL" --name production-clone

# Into an existing project that already has objects, unattended
bata import --source "$NEON_DATABASE_URL" --project <project-id> --yes --json

What it does#

bata import prints one line per phase. With --json it prints only the final result.

1. Check the tools. It confirms pg_dump and psql are on your PATH.
2. Inspect the source. It reads the database name, server version, size, table count and installed extensions. A bad URI or an unreachable server fails here, with the connection error.
3. Check tool versions. pg_dump must be at least the source's major. psql must be at least the source's major and at least pg_dump's.
4. Resolve the target. With --project, it looks the project up. Otherwise it creates a serverless project on the smallest compute size (1 CU). Then it waits up to 180 seconds for the compute to be ready.
5. Guard an existing target. With --project, it counts the objects already there: tables, views, materialized views, sequences, foreign tables, schemas, types and functions. If there are any, it stops unless you passed --yes, because the restore could collide with them.
6. Check extensions. It compares the extensions your source uses against the ones the target can provide (pg_available_extensions). If one is missing, it stops with EXTENSION_UNSUPPORTED before any data moves.
7. Migrate. It runs pg_dump --no-owner --no-privileges --encoding=UTF8 on the source and pipes it into psql -v ON_ERROR_STOP=1 on the target. On the first restore error it stops and prints the errors. The UTF-8 encoding keeps a non-UTF-8 source from being garbled in transit.
8. Verify. It counts every table's rows on both sides, and compares every sequence's value. Any difference is printed as a table, and the command exits non-zero.
9. Done. It prints the project id, the PostgreSQL version, and the connection string.

With --json, the password in the result's connection string reads REDACTED, so a saved log never holds it. Get the full string with bata db url --project <project-id>.

The restore does not use --single-transaction. One transaction that runs for hours fails as a whole when a single connection drops. Statement by statement, with ON_ERROR_STOP=1, it stops at the first real error and shows it to you.

Source newer than target#

BataDB runs PostgreSQL 16 and 17. A source one major newer, such as PostgreSQL 18 into 17, restores cleanly for standard schemas. There is one automatic fix and one limit.

• Fixed for you. Newer pg_dump versions write SET transaction_timeout = 0; at the top of the dump, which PostgreSQL 16 rejects. bata import removes that line from the dump's opening settings, and only there. Nothing in your schema or data is changed.
• Not hidden. A data type, function, syntax or storage option that does not exist on the target's major fails during the restore. bata import prints the psql errors and exits non-zero. It never skips the failing objects or claims success. Change that part of the schema to something the target supports, then run the import again.

Tables, indexes, constraints, sequences, views and common extensions usually move with no changes.

Checking the result yourself#

The import already compares row counts and sequences. To spot-check a table:

# On the source
psql "$NEON_DATABASE_URL" -Atc "SELECT count(*) FROM your_table"

# On the target
bata db query "SELECT count(*) FROM your_table" --project <project-id>

Verification covers every table and sequence on the source. A source table or sequence that is missing on the target, or whose count or value differs, fails the import with VERIFY_MISMATCH.

On a --project --yes import, the target may hold tables or sequences the source does not have. Those are left untouched and are not part of the comparison. The import says so: a warning in normal output, and target_only_tables and target_only_sequences in the --json result. A newly created project should never have any. If it does, the import fails with VERIFY_FAILED.

If a verification query cannot run at all, the import also fails with VERIFY_FAILED. It never reports success without a completed comparison.

Objects the platform adds#

Every BataDB project has a small public.health_check table and its sequence. The import ignores them on the target: they never trip the --yes guard and never show up as target-only objects.

If your source has its own public.health_check table, the import warns you first. Into a newly created project, it drops the target's copy so yours restores and is verified like any other table. Into an existing --project, the restore will likely fail with relation ... already exists; rename the source table first.

Two internal schemas, neon and neon_migration, are skipped on both sides. The target maintains its own copies. If your own data lives in a schema with one of those names, rename it before you import.

Extensions#

Run this on a BataDB project to see every extension it offers:

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

You do not have to compare lists by hand. The import's extension check stops before any data moves if your source uses something the target cannot provide.

Common extensions such as pgcrypto, pg_trgm, uuid-ossp, citext and vector are available.

Moving a production app#

The command moves the data. These are the steps around it for a real cutover.

1. Install the client tools. See Prerequisites.

2. Stop writes on the source. Use a maintenance window, or pause the app's writers. pg_dump reads a consistent snapshot, but anything written after it starts does not come across.

3. Import. Run bata import --source "$NEON_DATABASE_URL" --name <app>. Verification runs on its own. Spot-check one table that matters to you anyway.

4. Check roles. The dump is restored with --no-owner --no-privileges. Every object is owned by the role in your BataDB connection string. Source roles and grants are not recreated. An app with one DATABASE_URL needs nothing more. An app with several roles re-creates its grants after the import.

5. Point the app at BataDB. Use the pooled connection string for app traffic and serverless functions, the direct one for migrations, or @batadata/serverless for edge runtimes. See Connecting. Deploy, run your smoke tests, then stop the Neon writer.

6. Sequences carry over. They are verified, so inserts continue from the right value. You do not need setval.

7. Run migrations on the direct connection string. Most migration tools, such as prisma migrate deploy, take a session-level advisory lock. The pooled connection string runs in transaction mode: each transaction can land on a different server connection, and session state is reset after every transaction. A session lock taken there protects nothing. With Prisma, declare a directUrl:

   Prisma schema

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

   directUrl has no fallback, so set the variable in every environment, including local and CI. The app's own queries are fine on the pooled string.

   If a migration ever hangs waiting for an advisory lock, find the holder over the direct connection string and end it:

   SQL

   SELECT l.pid, a.state, age(now(), a.state_change) AS idle_for
     FROM pg_locks l JOIN pg_stat_activity a USING (pid)
    WHERE l.locktype = 'advisory' AND l.granted;
   -- then, for an idle holder:
   SELECT pg_terminate_backend(<pid>);

8. Keep the Neon project read-only for a while. It is your rollback.

One Neon project can hold several databases. bata import moves one database per run: the one named in the URI. Run it once per database, each into its own BataDB project.

Manual fallback: pg_dump | psql#

Use bata import when you can. It does everything below, plus the version checks, the extension check, verification and cleanup. Use the manual pipe only when you need to customize the dump, for example to pick schemas or exclude tables.

# 1. Create the target and get its direct connection string
PROJECT=$(bata create my-app --json | jq -r .id)
TARGET=$(bata db url --project "$PROJECT")

# 2. Stream the dump into the target. The awk filter removes the
#    newer-only SET transaction_timeout line, and only from the opening
#    settings. --encoding=UTF8 keeps a non-UTF-8 source intact.
#    The two excluded schemas are internal ones the target already has.
pg_dump "$NEON_DATABASE_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" -v ON_ERROR_STOP=1

# 3. Spot-check
psql "$TARGET" -Atc "SELECT count(*) FROM your_table"

Why awk and not grep -v 'SET transaction_timeout'? A grep removes that line anywhere in the stream, including inside a function body or a COPY data row, which would silently change your data. The awk filter does what bata import does. It only looks at the opening block of blank lines, comments, \restrict, SET and set_config lines. It stops filtering at the first real statement.

If the restore stops on a type or syntax the target's major does not have, see Source newer than target.

Troubleshooting#

When the restore or verification fails#

If bata import created the project and then the restore or verification fails, it keeps the project. A partial restore can take a long time to produce and helps you diagnose the failure. The output names the project id. With --json, the result carries target.project_id and target.created: true.

The clean retry is to delete the partial project and run the import again:

bata projects delete <project-id> --yes
bata import --source "$SOURCE" --name <name>

The CLI also suggests retrying into the same project with --project <project-id> --yes. That only works when the failed run created no objects. Otherwise the restore stops at the first object that already exists.

A failed extension check is different. It happens before any data moves, so a project created for that run is deleted for you.

See also#

• Move your Postgres to BataDB: the general guide, for any host.
• Migrating from Prisma: moving a Prisma app's data and code.
• Turbine ORM: using Turbine with your imported database.
• bata import --help: the flag list for your installed CLI.