How to migrate from Amazon RDS to PostgreSQL
Move from Amazon RDS to PostgreSQL you run: inventory extensions and roles, copy by dump or logical replication, replace IAM auth and RDS Proxy, then cut over.
- Data and streaming
- 7 steps
- OpenBao
In short
- Leaving Amazon RDS is a database move, not a rewrite: PostgreSQL runs on both sides, so the schema, the queries and the application code come across unchanged.
- The RDS master user is not a PostgreSQL superuser, so roles can only be dumped without their passwords, and each login role gets its password again on the new server.
- Logical replication copies a busy database while RDS stays live, once rds.logical_replication is set and the instance rebooted. Sequences, schema changes and large objects do not replicate.
- At cutover, writes stop, replication lag reaches zero, sequences are set and connection strings switch. RDS stays untouched as the fallback for an agreed period.
On this page (16)
Moving from Amazon RDS for PostgreSQL to a PostgreSQL server you control is a database move, not a rewrite. Both sides run PostgreSQL, so the schema, the queries and the application code come across unchanged. What needs planning is what RDS adds around the database: roles whose passwords it will not let anyone read, AWS-specific extensions, parameter groups, IAM database authentication and RDS Proxy, and a cutover that loses no write. A small database moves by dump and restore in a short window; a large or busy one is copied by logical replication while RDS stays live. This guide covers both, in the order we run them.
Why teams move from Amazon RDS to PostgreSQL
Three reasons come up again and again.
- The database where you decide. PostgreSQL runs on a machine dedicated to you, on your premises or in a region you choose, and answers only on your private network. Its backups go where you decide too.
- The whole server, not a managed slice. On RDS, AWS documents that you cannot reach the host or connect as a PostgreSQL superuser; the master user holds
rds_superuserprivileges instead. On your own server, the superuser,postgresql.confand the choice of extensions are yours. - Standard PostgreSQL, with nothing AWS-shaped around it. IAM tokens, RDS Proxy and the
aws_s3andaws_lambdaextensions tie code to AWS. Without them, a plain dump takes the database anywhere, the next move included.
What you give up is what RDS automates: failover across availability zones, automated backups with point-in-time restore from the console, read replicas created in a few clicks, Performance Insights and its successor CloudWatch Database Insights, and the links to IAM, Secrets Manager and CloudWatch. On your own server each of these is built and run, which is the part Pilae takes on.
Step 1: inventory the instance
Read the instance from RDS itself, connected as the master user, in every database you move. The queries below are standard PostgreSQL.
-- Version, and the size of each database
SELECT version();
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database WHERE NOT datistemplate;
-- Extensions installed in this database
SELECT extname, extversion FROM pg_extension ORDER BY extname;
-- Tables without a primary key
SELECT c.oid::regclass AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND NOT EXISTS (SELECT 1 FROM pg_constraint k WHERE k.conrelid = c.oid AND k.contype = 'p');
-- Large objects
SELECT count(*) FROM pg_largeobject_metadata;
-- Roles that sign in with IAM tokens, granted rds_iam directly or through another role
SELECT rolname FROM pg_roles
WHERE pg_has_role(oid, 'rds_iam', 'member') AND rolname <> 'rds_iam';
-- Who is connected now
SELECT usename, application_name, client_addr, count(*)
FROM pg_stat_activity WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3;
What each answer decides:
- Extensions.
aws_s3(withaws_commons, which AWS installs alongside it),aws_lambdaandrds_toolsare AWS’s own extensions for its managed PostgreSQL. List the code that calls them: an import from S3 or a call to Lambda from SQL becomes a job outside the database. Every other extension must be offered by the new server, whereSELECT name FROM pg_available_extensionslists what can be installed. - Tables without a primary key. Logical replication identifies rows by their replica identity, which is the primary key by default. PostgreSQL’s documentation is explicit that once such a table is in a publication that replicates updates and deletes, those operations fail on the publisher, which here is your production RDS instance. Add a primary key before creating the publication, or, as a fallback the documentation calls inefficient, set
REPLICA IDENTITY FULL. - Large objects are not replicated, and PostgreSQL’s documentation offers no workaround other than storing the data in ordinary tables. A database that holds them takes the dump path, or moves that data into
byteacolumns first. - IAM roles are rebuilt in step 3.
- Connections. Behind RDS Proxy,
pg_stat_activityshows the proxy’s address, not the application’s, so complete the list from application configuration, scheduled jobs, BI tools and anything that reads credentials from Secrets Manager.
Then the AWS side: the DB parameter group, the read replicas and what reads from them, any RDS Proxy in front of the instance, and the database’s size against the time you can stop writes. aws rds describe-db-parameters --db-parameter-group-name <group> --source user lists the parameters someone changed from the defaults.
Step 2: choose a dump or logical replication
The size of the database and the length of the write pause you can accept decide the method.
Two ways to copy the data out of RDS
Dump and restore
- Writes stop for as long as the dump and the restore take
- Schema, data, large objects and sequences arrive in one pass
- Nothing changes on the RDS instance
- Suits a small database or a long window
Logical replication
- RDS stays live while the copy catches up; writes pause only at cutover
- Needs
rds.logical_replicationand a reboot of the RDS instance - Carries table rows only: no schema changes, sequences or large objects
- Every table needs a primary key or a replica identity
Either way, the new server can run a newer major version than RDS. PostgreSQL documents that pg_dump output loads into newer major versions (use the pg_dump of the new server’s version), and lists replication between major versions among the uses of logical replication.
What the copy costs to leave AWS. The whole database crosses AWS’s boundary once, and with logical replication every change made until the cutover follows it. Data transferred out of RDS to the internet is billed per gigabyte beyond a free monthly allowance, at the rates on the RDS for PostgreSQL pricing page. AWS also offers free data transfer out to eligible customers who move all of their data off AWS, or all of their data off one service, on request to AWS Support; the same page describes a separate process for EU customers switching under the Data Act. Ask before the first copy, not after the invoice.
Step 3: rebuild roles, configuration and sign-in
Build the new server before any data moves, so the copy lands in a database whose roles and settings are already right.
Roles come across without passwords
AWS documents the master user as NOSUPERUSER, and PostgreSQL keeps password hashes in pg_authid, which it does not make publicly readable. A full pg_dumpall therefore fails on RDS. With --no-role-passwords, pg_dumpall reads roles from pg_roles instead, which is what the PostgreSQL documentation suggests when access to pg_authid is restricted:
# Every role, its attributes and memberships, without passwords
pg_dumpall -h acme-db.abc123.eu-central-1.rds.amazonaws.com -U postgres \
--roles-only --no-role-passwords -f roles.sql
Edit the file before replaying it on the new server. Remove the roles AWS creates (rdsadmin, rdstopmgr and those named rds_) with every grant of them, and replace each grant of rds_superuser with the privileges that role actually needs. Check the lines for the master user too: the file records it as NOSUPERUSER, and replayed as is, it applies those attributes to a role of the same name on the new server. Then give each login role its password again, from the secret store where its application keeps it, or issue a new one, which is a good moment to rotate.
Parameter groups become a configuration file
The parameter group becomes postgresql.conf, kept in your repository. Start from the parameters listed in step 1, and leave three kinds behind: parameters named rds., which mean nothing outside RDS; values written as formulas over the instance’s memory (DBInstanceClassMemory), which are sized again for the new machine rather than copied; and modules in shared_preload_libraries you no longer use. Keep the ones you do, such as pg_stat_statements or pg_cron.
IAM authentication becomes directory sign-in
A role granted rds_iam signs in with a token RDS generates on request, and AWS notes that IAM authentication then takes precedence over its password. On the new server, people sign in with their directory account through PostgreSQL’s LDAP or GSSAPI (Kerberos) authentication, set per database, role and network in pg_hba.conf. LDAP only checks the password, so each person’s role exists in the database first, with the grants their directory group should give them.
Applications get their own role. Where IAM tokens were chosen so that no password sat in a configuration file, OpenBao’s database secrets engine is the closer match: it creates a PostgreSQL role on request, with a password that expires with its lease. Either way, code that called generate-db-auth-token or loaded an AWS driver plugin for IAM goes back to a standard connection.
RDS Proxy becomes PgBouncer
Applications that connected through RDS Proxy connect through PgBouncer. The difference that matters is session state. AWS documents that RDS Proxy pins a client to one database connection when it sees SET, prepared statements, temporary tables, cursors, advisory locks or LISTEN. PgBouncer’s transaction pooling does not pin: it simply does not support SET, LISTEN, SQL-level PREPARE or session advisory locks. The CloudWatch metric DatabaseConnectionsCurrentlySessionPinned shows which proxies pinned sessions; their applications go to a PgBouncer pool in session mode, or connect directly.
Step 4: copy the data
By dump and restore
# Dump one database in parallel, in directory format
pg_dump -h acme-db.abc123.eu-central-1.rds.amazonaws.com -U postgres \
-d app -Fd -j 4 -f app.dump
# Restore it on the new server, once the roles from step 3 exist
createdb -h db.acme.internal -U postgres -O app_owner app
pg_restore -l app.dump > app.list # comment out the AWS extensions with a leading ;
pg_restore -h db.acme.internal -U postgres -d app -j 4 -L app.list app.dump
vacuumdb -h db.acme.internal -U postgres -d app --analyze-in-stages
Large objects are in the dump by default. The list file is how the AWS extensions from step 1 stay out of the restore: each line commented out with a semicolon is skipped. vacuumdb --analyze-in-stages gives the planner statistics before the first query arrives.
By logical replication from RDS
- Turn it on at RDS. In a custom DB parameter group (the default group cannot be changed), set
rds.logical_replicationto1, then reboot the instance, because the parameter is static. Plan the reboot in a maintenance window. AWS’s logical replication guide notes that the setting increases WAL generation. - Open a path from the new server to RDS. The new server connects to the RDS endpoint as a subscriber: over a VPN or a private connection into the VPC, or through a security group rule that admits only the new server’s address. Keep TLS on and verify the server with AWS’s certificate bundle.
- Create a replication role and a publication on RDS, in each database you move:
-- On RDS, in the database being moved
CREATE ROLE migration_repl LOGIN PASSWORD '<password>';
GRANT rds_replication TO migration_repl;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migration_repl;
CREATE PUBLICATION move_app FOR ALL TABLES;
- Copy the schema. Logical replication does not carry it. Take
pg_dump --schema-onlyfrom RDS and restore it on the new server, with the AWS extensions commented out as above. - Subscribe on the new server. The subscription copies every table, then follows each change:
-- On the new server, in the database restored from the schema dump
CREATE SUBSCRIPTION move_app
CONNECTION 'host=acme-db.abc123.eu-central-1.rds.amazonaws.com dbname=app
user=migration_repl password=<password>
sslmode=verify-full sslrootcert=/etc/postgresql/global-bundle.pem'
PUBLICATION move_app;
- Watch the copy. On the new server,
pg_subscription_relshows each table’s state; every row readsr, for ready, once its initial copy is done. On RDS,pg_replication_slotsshows the slot the subscription created.
Watch the slot until the cutover. AWS warns that a logical replication slot that nobody reads from lets WAL accumulate and can quickly fill the instance’s storage, so a stopped subscriber is an alert, not a detail. Freeze schema changes for the duration, or apply them to the new server first: PostgreSQL’s documentation notes that replication errors when changes arrive that the subscriber’s tables cannot hold, and that applying additive changes to the subscriber first avoids most such errors. A table created during the run is created on the new server too, then picked up with ALTER SUBSCRIPTION move_app REFRESH PUBLICATION.
RDS also supports the pglogical extension, which AWS describes as predating PostgreSQL’s built-in logical replication. The built-in publications and subscriptions need nothing installed on the new server, so they are the route we take.
Aurora PostgreSQL follows the same procedure. AWS documents rds.logical_replication in the DB cluster parameter group, a reboot of the writer instance, and the writer as the publisher, since Aurora does not support logical decoding from its read replicas.
Step 5: verify the copy
Check twice: during the parallel run, to find structural problems while there is time, and in the cutover window, with writes stopped, for the final comparison.
- Row counts for every table, on both sides.
- Checksums on the tables that matter, compared between RDS and the new server:
-- Run on both sides: the row count and a checksum of the table, in key order
SELECT count(*), md5(string_agg(o::text, ',' ORDER BY o.id)) FROM orders o;
- Your own test suite, run against the new server by a staging copy of each application, before anything switches.
- Reports and read-only jobs pointed at the new server during the run, where a wrong number gets noticed by the people who read it.
Step 6: cut over
The cutover, in order
- Stop writes: Applications that write are stopped or put in maintenance, and
pg_stat_activityon RDS shows no client sessions. - Drain: The replication lag on RDS reaches zero.
- Sequences: Each sequence on the new server is set from RDS.
- Verify: Row counts and checksums match.
- Switch: Connection strings point at the new server, and applications start.
Wait for the lag to reach zero. On RDS, the logical slot shows how much WAL the new server has not yet confirmed:
-- On RDS: bytes of WAL the subscription has not confirmed yet
SELECT slot_name, active,
pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';
Set the sequences. Rows arrive with the IDs RDS gave them, but PostgreSQL’s documentation lists sequence data among the restrictions of logical replication: the sequences on the new server do not follow the values RDS hands out, so the first insert would collide with an existing row. Once writes have stopped, read every sequence on RDS and set it on the new server, database by database:
psql "$RDS_URL" -At -c "
SELECT format('SELECT setval(%L, %s, true);',
quote_ident(schemaname) || '.' || quote_ident(sequencename), last_value)
FROM pg_sequences WHERE last_value IS NOT NULL" | psql "$NEW_URL"
Connect to RDS as a role that can read every sequence: pg_sequences shows no value for a sequence the current role may not read, and the query skips it.
Switch the connection strings. Replace the rds.amazonaws.com endpoint with a DNS name you control, so the next change is a DNS change. Clients that verify the server certificate against AWS’s bundle get the new server’s CA instead. Then stop following RDS: DROP SUBSCRIPTION move_app; on the new server drops the replication slot on RDS as well, so RDS stops keeping WAL for it.
Step 7: keep RDS as the fallback, then retire it
Leave the RDS instance running and unchanged for a period agreed before the cutover: at least one full business cycle, month-end jobs included. Block the application roles from signing in to it (ALTER ROLE app_rw NOLOGIN; on RDS), so a forgotten configuration fails loudly instead of writing to the old database.
Decide before the day what the fallback means. RDS holds the data as it was at cutover. Going back after the new server has taken writes means carrying them back: plan a reverse subscription, with a publication on the new server and a subscription on RDS created at cutover with copy_data = false, which needs a path from RDS back to the new server; or agree that the fallback is the state at cutover and rehearse what that costs.
When the period ends:
- Keep a portable copy. A final
pg_dumpstored outside AWS, beside your new backups. An RDS snapshot restores only as a new RDS instance. - Delete the instance. Delete its read replicas first, because AWS promotes each one to a standalone instance when its source is deleted. Turn deletion protection off, then choose whether to take a final snapshot and whether to retain automated backups. A final snapshot stays until you delete it, retained automated backups stay for the instance’s retention period, and both are billed until they are deleted.
- Remove what surrounded it: the RDS Proxy, the parameter groups, the security group rules, the replication role, and the IAM policies that grant
rds-db:connect.
Running PostgreSQL in production
What RDS did for you is now part of running the server.
- A dedicated machine with local disks. Memory decides how much of the working data stays cached, so it grows with the database rather than with users, and latency on the write-ahead log is what most workloads feel first.
- Continuous WAL archiving and full backups to a bucket in another location, which is what point-in-time recovery needs: the counterpart of RDS automated backups.
- A standby fed by streaming replication, for failover and read-heavy reporting, where RDS gave you a Multi-AZ deployment or read replicas.
- PgBouncer, so hundreds of application connections do not become hundreds of server processes.
- Query statistics. The
pg_stat_statementsmodule tracks planning and execution statistics for every statement, the per-query view you had in Performance Insights; it is loaded throughshared_preload_libraries. - Upgrades. Minor releases carry security fixes; a major version is a planned upgrade of the whole cluster.
Pilae operates PostgreSQL this way, on a dedicated machine with its configuration kept as a file in your repository, PgBouncer in front and, where you need one, a standby with failover planned and rehearsed. A nightly full backup goes to a second site in the same jurisdiction and, from the Business plan, the write-ahead log is archived there continuously, so recovery can target any minute inside the retention your contract specifies, and on Enterprise a restore test every month is recorded in the console. Probes check the server every 60 seconds and alert a Pilae engineer when replication lags or disk space runs low. Pilae engineers use separate named accounts, and every session is logged. The Pilae Agent plans each upgrade, takes a verified backup, waits for your approval and applies it in your window. PostgreSQL runs on your premises or on dedicated machines in Pilae Cloud, answering only on your private network.
Let Pilae run the move
Pilae runs Amazon RDS to PostgreSQL migrations at a fixed price: the inventory of the instance, the new server with its configuration as code, roles and sign-in, the copy by dump or logical replication, the verification, and the cutover with RDS kept as the fallback. Then we operate PostgreSQL for you under a contract that, from the Business plan, includes a monthly availability commitment with service credits. See migration services for what the fixed price covers, or tell us about the database you run on RDS and we will scope it.
Questions
Can we export roles and passwords from Amazon RDS?
How much downtime does the move need?
Will AWS-specific extensions work on the new server?
Does the same procedure work for Aurora PostgreSQL?
Is PostgreSQL open source?
How long does an Amazon RDS to PostgreSQL migration take?
Let us run the move for you
An inventory, a parallel run and a cutover plan, at a fixed price. Then we operate the app.
- Go deeperSaaS migrationA fixed-scope migration off Google Workspace, Microsoft 365, Zapier, Airtable and ChatGPT to open-source apps, with a parallel run and a cutover plan.
- CheckManaged operationsPatching, daily encrypted backups, monitoring and incident response for your open-source apps, under an SLA with an availability commitment of up to 99.9%.
