Nov 16, 202610 min read

How to Migrate Neon Postgres to Amazon RDS

Move a Neon database into RDS or Aurora in your own AWS account: dump over the direct connection, replace Neon Auth, the Data API's RLS policies and Neon-only extensions, then cut over with a dump and restore or logical replication.

Illustration for How to Migrate Neon Postgres to Amazon RDS

Neon is Postgres, so the data comes out with the standard tools, but a few parts of a Neon project only work on Neon: the pooled connection string, Neon Auth's tables in the neon_auth schema, Row Level Security policies that call auth.user_id() for the Data API, and extensions like pg_session_jwt that exist to serve those features. One reason to move is to put the database in the same AWS account and VPC as the services, on Amazon RDS for PostgreSQL or Aurora.

This guide moves the database into RDS in your own AWS account: an inventory of what depends on Neon, the schema edits, a restore with Encore or by hand, and a cutover with either a dump and restore or logical replication.

Ways to move Neon Postgres to RDS at a glance

Encore in your AWS accountRDS by hand
Who creates RDSEncore, on the first deployYou, with Terraform or the console
Reaching the private databaseencore db conn-uriBastion or SSM port forwarding
Schema afterwardsBootstrap migration, then files in the repoYour migration tool
Access checksAuth handler plus owner filters in queriesYour API code, or rewritten RLS policies
Scale to zeroAurora Serverless v2 with a minimum of 0 ACUsAurora Serverless v2, configured by you
DowntimeFinal dump and restore, or a logical replication cutoverFinal dump and restore, or a logical replication cutover

Inventory what depends on Neon

Every step below uses the direct connection string, the hostname without -pooler. Neon's pooled endpoint runs PgBouncer in transaction mode, and Neon's own guidance is to use the direct string for pg_dump, pg_restore, migrations and logical replication, since they rely on SET and session state. Check the server version too, since your pg_dump should be at least that version and the RDS target should not be older:

~
$ export NEON_DIRECT_URL="postgresql://app_owner:...@ep-cool-darkness-123456.us-east-2.aws.neon.tech/app?sslmode=require"$ psql "$NEON_DIRECT_URL" -c "SHOW server_version;"$ pg_dump --version

Foreign keys from your tables into Neon-managed schemas:

SELECT conrelid::regclass AS from_table, confrelid::regclass AS to_table, conname FROM pg_constraint WHERE contype = 'f' AND connamespace = 'public'::regnamespace AND confrelid::regclass::text ~ '^(neon_auth|auth)\.';

Policies and the expressions they use, from SELECT tablename, policyname, qual FROM pg_policies WHERE schemaname = 'public';, the schemas besides public from SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname NOT IN ('information_schema', 'public');, and the extensions from SELECT extname, extversion FROM pg_extension;. Compare the extension names with the RDS extension list for your target version: pgvector, pg_trgm and pgcrypto are there, while pg_session_jwt, which powers the Data API's auth.user_id(), and Neon's own neon and neon_utils have no RDS counterpart.

Three decisions follow from that output:

  • Neon Auth. Neon Auth stores users and sessions in tables under neon_auth. Create a public.users table with the columns your app reads, copy the user IDs and emails into it and point foreign keys at it, so every owner_id stays valid whichever auth provider comes next. Sessions and password hashes belong to the auth migration, not this one.
  • RLS. Policies on auth.user_id() depend on the Data API passing a JWT per request. A backend on RDS usually connects as one role, so either move the owner check into the queries, as in the code below, or rewrite each policy to read a per-transaction setting such as current_setting('app.user_id', true) that your backend sets.
  • Branches. Only the branch you dump moves, and preview and development branches are recreated on the AWS side as separate environments, which start from migrations instead of a copy of production data.

How do you migrate Neon Postgres to RDS with Encore?

Encore

Encore brings infrastructure from code to your AWS account: the database is a declaration in the service that uses it, and the first deploy creates the database server for the environment. According to the AWS infrastructure docs, a new RDS instance defaults to PostgreSQL 18.3 on db.m5.large with Multi-AZ, 7 days of backups, encryption at rest and deletion protection, and an environment can use Aurora PostgreSQL instead, including Aurora Serverless v2 with a minimum capacity of 0 ACUs so it pauses when idle, the closest match to Neon's scale to zero. The cleaned Neon schema becomes the first migration, and the checks RLS used to make move into an auth handler and the queries.

Application code passes through Encore and infrastructure guardrails to resources provisioned in your own AWS or GCP account.
Encore provisions the infrastructure your application needs in your own AWS or GCP account.

Set up the app

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

Dump your own schema over the direct connection and turn it into the bootstrap migration:

~/tasks-app
$ mkdir -p tasks/migrations$ pg_dump --schema-only --schema=public --no-owner --no-acl "$NEON_DIRECT_URL" > tasks/migrations/1_bootstrap.up.sql

--no-owner and --no-acl drop the ALTER ... OWNER lines and the grants to Neon roles such as neon_superuser, which do not exist on RDS. Then edit the file by hand: remove ENABLE ROW LEVEL SECURITY and the CREATE POLICY lines, CREATE EXTENSION lines for Neon-only extensions, and foreign keys into neon_auth, and add the users table they pointed at. For an app with tasks and API tokens, the result looks like this:

-- tasks/migrations/1_bootstrap.up.sql -- From: pg_dump --schema-only --schema=public --no-owner --no-acl "$NEON_DIRECT_URL" -- with RLS policies on auth.user_id() and the pg_session_jwt extension removed. CREATE TABLE users ( id TEXT PRIMARY KEY, email TEXT NOT NULL UNIQUE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE api_tokens ( token_hash TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE tasks ( id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, title TEXT NOT NULL, done BOOLEAN NOT NULL DEFAULT false, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX tasks_owner_created_idx ON tasks (owner_id, created_at DESC);

The service declares the database, resolves bearer tokens to users in the auth handler and filters every query by owner:

// tasks/tasks.go package tasks import ( "context" "crypto/sha256" "encoding/hex" "errors" "time" "encore.dev/beta/auth" "encore.dev/beta/errs" "encore.dev/storage/sqldb" ) // RDS for PostgreSQL in your AWS account, local Postgres with encore run. var db = sqldb.NewDatabase("tasks", sqldb.DatabaseConfig{ Migrations: "./migrations", }) // Replaces the Data API's JWT check: resolve a bearer token to a user ID. // //encore:authhandler func AuthHandler(ctx context.Context, token string) (auth.UID, error) { sum := sha256.Sum256([]byte(token)) var userID string err := db.QueryRow(ctx, `SELECT user_id FROM api_tokens WHERE token_hash = $1`, hex.EncodeToString(sum[:]), ).Scan(&userID) if errors.Is(err, sqldb.ErrNoRows) { return "", &errs.Error{Code: errs.Unauthenticated, Message: "invalid token"} } if err != nil { return "", err } return auth.UID(userID), nil } type Task struct { ID int64 `json:"id"` Title string `json:"title"` Done bool `json:"done"` CreatedAt time.Time `json:"created_at"` } type ListResponse struct { Tasks []Task `json:"tasks"` } // The owner filter that used to live in an RLS policy on auth.user_id(). // //encore:api auth method=GET path=/tasks func List(ctx context.Context) (*ListResponse, error) { uid, _ := auth.UserID() rows, err := db.Query(ctx, ` SELECT id, title, done, created_at FROM tasks WHERE owner_id = $1 ORDER BY created_at DESC LIMIT 100`, string(uid)) if err != nil { return nil, err } defer rows.Close() resp := &ListResponse{Tasks: []Task{}} for rows.Next() { var t Task if err := rows.Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt); err != nil { return nil, err } resp.Tasks = append(resp.Tasks, t) } return resp, rows.Err() } type CreateParams struct { Title string `json:"title"` } //encore:api auth method=POST path=/tasks func Create(ctx context.Context, p *CreateParams) (*Task, error) { if p.Title == "" { return nil, &errs.Error{Code: errs.InvalidArgument, Message: "title is required"} } uid, _ := auth.UserID() t := Task{Title: p.Title} err := db.QueryRow(ctx, ` INSERT INTO tasks (owner_id, title) VALUES ($1, $2) RETURNING id, done, created_at`, string(uid), p.Title, ).Scan(&t.ID, &t.Done, &t.CreatedAt) return &t, err }

encore run starts Postgres locally and applies the bootstrap migration, which is the quickest way to find a leftover neon_auth or auth.user_id() reference before anything reaches AWS. The owner filter now sits in Go, where tests and code review can see it, and a coding agent extending the service adds 2_add_due_dates.up.sql, runs it against the local database, and the next deploy applies the same file to RDS.

Deploy and load the data

Connect your AWS account as described in Connect your cloud account, create an environment on AWS and push, so Encore creates RDS in a private subnet and applies the schema. Then load users first, since tasks references them, and the rest of the data after, through the temporary connection string from encore db conn-uri covered in the database docs:

~/tasks-app
$ git push encore$ pg_dump --data-only --schema=public --format=custom -f data.dump "$NEON_DIRECT_URL"$ export RDS_URL="$(encore db conn-uri tasks --env=prod --admin)"$ pg_restore --data-only --no-owner --no-acl --single-transaction -d "$RDS_URL" data.dump$ psql "$RDS_URL" -c "ANALYZE;"

A data-only dump includes setval calls for each sequence, so identity columns continue from the right value after the restore. If the users live only in neon_auth, export the IDs and emails you need to a CSV with \copy first and load it into users before the restore.

Best fit: teams bringing a Neon database into their own AWS account who want RDS or Aurora created from the backend code, access checks they can test, and the same schema in every new environment.

RDS by hand

Create the RDS instance, a DB subnet group in private subnets and a security group, then reach it from your machine through a bastion with Session Manager port forwarding, and apply the same cleaned schema and data:

~
$ aws ssm start-session --target $BASTION_ID \ --document-name AWS-StartPortForwardingSessionToRemoteHost \ --parameters '{"host":["'$RDS_HOST'"],"portNumber":["5432"],"localPortNumber":["5433"]}'$ psql "host=127.0.0.1 port=5433 user=postgres dbname=app" -f schema-cleaned.sql$ pg_restore --data-only --no-owner --no-acl -h 127.0.0.1 -p 5433 -U postgres -d app data.dump

What remains is yours to build: a non-master role for the app with only the grants it needs, the connection string in Secrets Manager, a parameter group if you rely on non-default settings, and a way to apply the next migration to every environment.

Best fit: teams with an existing RDS layout in Terraform, or databases that keep RLS and need roles and policies designed by hand.

The cutover

For a database that dumps and restores in minutes, the simplest cutover is a short write freeze: stop the app and any serverless functions or Data API clients that write to Neon, take the final data dump, restore it, run ANALYZE;, compare count(*) and max(created_at) for the main tables on both sides, and point the app at RDS.

For a larger database, replicate first and switch later. Enabling logical replication on the Neon project permanently changes wal_level to logical and restarts its computes, and according to Neon's logical replication guide an inactive replication slot is removed after about 40 hours and a subscriber keeps the compute from scaling to zero. With the schema already applied on RDS, publish on Neon and subscribe from RDS:

-- On Neon, over the direct connection CREATE PUBLICATION to_rds FOR TABLES IN SCHEMA public; -- On RDS, as the master user CREATE SUBSCRIPTION from_neon CONNECTION 'host=ep-cool-darkness-123456.us-east-2.aws.neon.tech dbname=app user=app_owner password=... sslmode=require' PUBLICATION to_rds;

The subscription copies existing rows and then streams changes, which needs an outbound route from the RDS subnets to Neon's endpoint and the RDS address in Neon's IP allow list if you use one. The PostgreSQL documentation notes that schema changes and sequence values are not replicated, so freeze migrations until the cutover and, once writes to Neon have stopped and the subscriber has caught up, move each sequence past its column's highest value before the app writes to RDS:

SELECT setval(pg_get_serial_sequence('tasks', 'id'), (SELECT COALESCE(max(id), 1) FROM tasks));

Without that step the first insert on RDS fails with a unique violation on the primary key, which is what happens when a copied table still has its sequence at the start value. Then run DROP SUBSCRIPTION from_neon; on RDS and drop the publication on Neon, and keep the Neon project unchanged for a few days so rolling back only means pointing the app at it again.

How to choose

Pick Encore when the backend is moving to your own AWS account and you want RDS or Aurora, its network and the schema history created from the service code, with Neon Auth and Data API pieces replaced by code you can run locally. Pick RDS by hand when an infrastructure team owns the database layout, or when you plan to keep RLS and design the roles yourself. In both cases, use a dump and restore when the database is small enough for a short write freeze, and logical replication when it is not.

Frequently asked questions

How do I migrate a Neon database to Amazon RDS?

Dump the public schema with pg_dump --schema-only --no-owner --no-acl over Neon's direct connection string, not the -pooler one, remove references to the neon_auth schema, RLS policies on auth.user_id() and Neon-only extensions, and apply it on RDS. Then load the data with pg_dump --data-only and pg_restore, or replicate it with a publication on Neon and a subscription on RDS, and compare row counts before switching the app.

Can I use pg_dump with Neon's pooled connection?

No. Neon's pooled connection string goes through PgBouncer in transaction mode, which does not support the SET statements pg_dump and pg_restore use. Use the direct connection string, the hostname without the -pooler suffix, for dumps, restores, migrations and logical replication.

Can I replicate from Neon to RDS with no downtime?

Close to it. Enable logical replication on the Neon project, which permanently sets wal_level to logical and restarts its computes, create a publication for your tables, and subscribe to it from RDS. Sequences are not replicated, so set each one to the max of its column on RDS before writes start there, and drop the subscription once the app has moved.

Is there an RDS option that scales to zero like Neon?

Aurora Serverless v2 can pause when idle if its minimum capacity is set to 0 ACUs. Encore can provision Aurora PostgreSQL for an environment and lets you set an instance's class to db.serverless with a capacity range from 0 to 256 ACUs.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders