Migrating from Amazon RDS or Aurora PostgreSQL

Before you start

Moving from RDS uses the standard PostgreSQL tools, pg_dump and pg_restore, as in our guide for Heroku, Supabase and Neon. RDS databases are often large and busy, and moving one of those takes planning, so this article covers a parallel copy, a full rehearsal and a cut-over you can time to the minute. Aurora PostgreSQL works the same way; wherever it says RDS below, Aurora applies too.

RDS snapshots cannot be imported: they are a format only AWS can read. The data has to come across as a dump. Start by looking at the source:

SELECT version();
SELECT pg_size_pretty(pg_database_size(current_database()));
\dx

• Version. We offer PostgreSQL 17 (the default) and 18. Your client tools must be at least the source's major version: install 17 or 18. If the source already runs 18, create the new database on 18.
• Size. Pick a plan whose disk fits the data with room to grow (sizes on the pricing page). At its storage limit a database becomes read-only (see Storage). High-availability plans can be changed later without downtime, but a smaller plan only accepts your data if it is under 85% of that plan's storage (see Plans and resizing).
• Extensions. Compare the \dx list with what you need. The ones PostgreSQL marks as trusted, such as pgcrypto, citext, hstore, pg_trgm and uuid-ossp, your role can create. Others need a superuser: write to [email protected] about them before you plan the move.

What stays behind

Some objects on RDS belong to AWS and have no meaning anywhere else. The roles rds_superuser and rdsadmin do not exist here, and neither do the extensions aws_s3, aws_commons and aws_lambda, which call AWS services from SQL. If your code uses them, move that work into your application first.

On our side you restore as your database's role, u_<id>, which owns the app database but is not a superuser. Dumping with --no-owner and --no-acl leaves out the owners and grants of RDS, so everything you restore belongs to that role and the AWS roles never come up.

You can also leave data behind. Tables of logs or events you no longer need can keep their structure and lose their rows with --exclude-table-data, which makes both the dump and the restore shorter.

A parallel dump and restore

Run the dump close to RDS: an EC2 instance in the same AWS region reads the database far faster than a machine across the internet. AWS charges for the data that leaves its network on the way to us, wherever you run the dump. The dump is compressed, so copying it out moves fewer bytes than restoring across the internet from the instance would. Give the instance a disk large enough for the dump.

Use the directory format, which pg_dump can write with several jobs at once. Each job is one connection, so leave room under the source's connection limit. On Aurora, dump from the cluster (writer) endpoint.

pg_dump -Fd -j 8 --no-owner --no-acl \
  --exclude-table-data 'audit_log' \
  -d "$RDS_URL" -f app.dir

pg_restore --no-owner --no-acl -j 8 -d "$ELECTRADB_URL" app.dir
vacuumdb --analyze-only -j 4 -d "$ELECTRADB_URL"

ELECTRADB_URL is the connection string from the database page with your password added and &sslrootcert=system appended, so libpq checks our certificate against your system's certificate authorities. If you use an IP allowlist, add the public address of the EC2 instance. You can run pg_restore on the instance itself, straight from the dump directory, or copy the directory to another machine first.

The last step collects the planner's statistics, which a restore does not do. Sequences come with the dump. An error about COMMENT ON EXTENSION is harmless; read the other errors at the end of the restore before you go on.

Rehearse the whole move

Do the full migration once while RDS keeps serving traffic, on the plan you will use. A rehearsal answers the questions a cut-over cannot afford to: how long the dump and the restore take, which errors come up, and whether your application works against the copy.

1. Time the dump and the restore separately. Try more jobs on either side if one of them is slow.
2. Compare row counts and the highest ids of your key tables on both sides, and a checksum of the ones where every value matters:

SELECT count(*), max(id) FROM orders;
SELECT md5(string_agg(t::text, '|' ORDER BY id)) FROM customers t;

3. Point a staging copy of your application at the new database and run its tests. Set a connect timeout of 5 to 10 seconds in its connection settings (see Timeouts and failover).
4. Write down every step and command, with the times you measured. That list is your cut-over plan.

The cut-over

A dump copies one moment. We do not offer replication into an ElectraDB database (your role cannot create a subscription), so there is no way to stream the last changes across while RDS keeps taking writes. The final copy needs a write freeze on RDS, as long as the dump and the restore you rehearsed.

Keep the freeze short

• Restore with as many jobs as the rehearsal showed help, and dump in parallel too.
• Leave behind the data you do not need, as above.
• Move large tables that only ever grow, such as history or events, ahead of time if your application allows it: copy them days before, then during the freeze copy only the rows added since, by id or timestamp, and leave them out of the final dump.
• Pick a time of low traffic and tell your users beforehand.

On the day

1. Stop every writer: put the application in maintenance mode, and stop workers, scheduled jobs and anything else that writes. Check pg_stat_activity on RDS until no sessions are left.
2. Run the final dump and restore, with --clean --if-exists on pg_restore so it drops what the rehearsal left first. Analyze and run your checks.
3. Note the time, then switch your application's connection string and start it.
4. Watch the logs and the database page until traffic looks as it did before.

After the move

Your database is backed up continuously, and you can restore it to any second of the last 7 days into a new database. With the time you noted, you can get back the data exactly as it arrived from RDS if something goes wrong in the first days.

On high-availability plans, the database page also shows a read-only port that reads from the standby. If you sent reporting queries to an RDS read replica, point them there (see High availability and replication).