Home / Guides
Migration guide

On-Prem PostgreSQL to Amazon RDS: Checks, Dump, Restore

By · November 12, 2026 · 15 min read

The short version

To migrate an on-prem PostgreSQL database to AWS RDS, check four things on the source first: extensions RDS does not offer, objects only a real superuser can create, roles, and tablespaces. Then take a pg_dump -Fc (or -Fd for a parallel dump), create the roles on RDS yourself, and pg_restore -j N --no-owner --no-privileges --no-tablespaces as the RDS master user, which is not a superuser. Re-grant privileges afterwards, including on large objects, and compare row counts before you switch. For a cutover with almost no downtime, load the bulk the same way and close the gap with logical replication or AWS DMS.

How to migrate a Postgres database to AWS RDS: pick the method

AWS's import overview recommends native PostgreSQL tools for a homogeneous migration of an entire database, and AWS DMS for most other cases, including when you need to keep replicating until cutover. In practice:

Situation Method Downtime
Database you can stop for the length of a dump and restore pg_dump + pg_restore Dump plus restore time
Larger database, short window Native logical replication: publication on-prem, subscription on RDS Final catch-up only
You want AWS to run the copy and the change stream AWS DMS, full load plus CDC Final catch-up only

The dump and restore is also step one of the two replication options in most real runs, because it is how the schema gets there. So that is the part I ran end to end.

What I ran

No AWS account for these guides, so the RDS side is from the AWS docs and labelled as such. The self-managed side ran for real on PostgreSQL 16.14 (the QA container I use for these guides) with the PostgreSQL 18.6 client tools from Homebrew's libpq. I built a source database with the things I usually find on an old server: an application role that owns everything, a read-only role, a stray superuser account, five extensions, a GIN index in its own tablespace, 25 large objects behind the lo extension, a file_fdw foreign table and an event trigger. 5,000 customers and 200,000 orders.

The target was a second database on the same server, restored by a role created with the same attributes AWS lists for the RDS master user, LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION, from the rds_superuser page. It has none of the extra grants rds_superuser carries, so it is stricter than RDS in places. I point those out below.

Pre-migration checks on the source

-- version, encoding, locale
SELECT current_setting('server_version') AS version,
       pg_encoding_to_char(encoding) AS encoding,
       datcollate, datctype, datlocprovider AS provider, datcollversion
FROM pg_database WHERE datname = current_database();

-- extensions, and whether a non-superuser could create them
SELECT e.extname, e.extversion, a.trusted
FROM pg_extension e
JOIN pg_available_extension_versions a
  ON a.name = e.extname AND a.version = e.extversion
ORDER BY 1;
             version             | encoding | datcollate |  datctype  | provider | datcollversion
---------------------------------+----------+------------+------------+----------+----------------
 16.14 (Debian 16.14-1.pgdg13+1) | UTF8     | en_US.utf8 | en_US.utf8 | c        | 2.41
(1 row)

  extname  | extversion | trusted
-----------+------------+---------
 adminpack | 2.1        | f
 citext    | 1.6        | t
 file_fdw  | 1.0        | f
 lo        | 1.1        | t
 pg_trgm   | 1.6        | t
 plpgsql   | 1.0        | t
(6 rows)

I compared that list with the PostgreSQL 16 table on AWS's extension versions page:

Extension Trusted On the RDS for PostgreSQL 16 list Plan
citext 1.6 yes yes, 1.6 restore as is
lo 1.1 yes yes, 1.1 restore as is
pg_trgm 1.6 yes yes, 1.6 restore as is
adminpack 2.1 no not listed leave behind
file_fdw 1.0 no not listed leave behind, with its server and foreign table

On a live instance the same page says SHOW rds.extensions; prints what that instance allows, with a note that extensions added in a minor version can show inaccurately there.

The trusted column matters for the restore. The CREATE EXTENSION docs say a trusted extension "can be installed by any user who has CREATE privilege on the current database"; the others usually need a superuser. rds_superuser can add any extension RDS supports, per AWS, so on RDS the real question is the list, not the flag.

Next, objects only a superuser can create, then roles, tablespaces and large objects:

           kind           |     name
--------------------------+---------------
 event trigger            | log_ddl
 foreign table (file_fdw) | app.host_feed
(2 rows)

               rolname                | rolsuper | rolcreaterole | rolcreatedb | rolreplication | rolbypassrls
--------------------------------------+----------+---------------+-------------+----------------+--------------
 on_prem_postgresql_to_amazon_rds_app | f        | f             | f           | f              | f
 on_prem_postgresql_to_amazon_rds_dba | t        | f             | f           | f              | f
 on_prem_postgresql_to_amazon_rds_ro  | f        | f             | f           | f              | f
 postgres                             | t        | t             | t           | t              | t
(4 rows)

     relname      | relkind |                spcname
------------------+---------+---------------------------------------
 orders_note_trgm | i       | on_prem_postgresql_to_amazon_rds_fast
(1 row)

 large_objects | pg_largeobject_size
---------------+---------------------
            25 | 56 kB
(1 row)

The first query unions pg_event_trigger, functions in untrusted languages, C functions outside an extension (exclude pg_catalog, or you get 86 built-in C functions back, encoding conversions and the snowball dictionary, as I did on the first try) and pg_foreign_table. The tablespace query must exclude pg_global for the same reason. What each finding means on RDS:

Record the row counts now. I used them at the end: 5,000 customers, 200,000 orders, 25 documents.

pg_dump with parallel jobs: -Fc does not do it

The obvious command fails:

$ pg_dump -Fc -j 4 -f src.dump "$SRC"
pg_dump: error: parallel backup only supported by the directory format

The pg_dump docs say -j works only with the directory format, the one format where several processes can write at once. Two ways out, both fine for RDS: dump with -Fd -j 4 and restore in parallel, or dump with -Fc single-threaded and still restore with pg_restore -j, which accepts custom and directory archives. I used the directory format, and dumped the roles separately, because pg_dump does not save roles or tablespaces (pg_dumpall docs):

$ pg_dump -Fd -j 4 -f src.dir "$SRC"
$ pg_dumpall --roles-only --no-role-passwords -f roles.sql -d "$SRC_ADMIN"

--no-role-passwords reads from pg_roles instead of pg_authid; roles come back with a null password and you set new ones on RDS. You cannot run pg_dumpall against RDS anyway: AWS's import page notes it needs superuser rights that an RDS instance does not grant.

What a plain pg_restore does as a non-superuser

My source and target share one server, so after the dump I dropped the three application roles to make the target start with none of them, as a fresh RDS instance would. Then, as the non-superuser admin, a restore with no flags at all (blank lines removed, ... where I cut):

$ pg_restore -j 4 -d "$TGT" src.dir
pg_restore: error: could not execute query: ERROR:  unrecognized configuration parameter "transaction_timeout"
Command was: SET transaction_timeout = 0;
pg_restore: error: could not execute query: ERROR:  role "on_prem_postgresql_to_amazon_rds_app" does not exist
Command was: ALTER SCHEMA app OWNER TO on_prem_postgresql_to_amazon_rds_app;
pg_restore: error: could not execute query: ERROR:  permission denied to create extension "adminpack"
HINT:  Must be superuser to create this extension.
Command was: CREATE EXTENSION IF NOT EXISTS adminpack WITH SCHEMA pg_catalog;
pg_restore: error: could not execute query: ERROR:  extension "adminpack" does not exist
Command was: COMMENT ON EXTENSION adminpack IS 'administrative functions for PostgreSQL';
pg_restore: error: could not execute query: ERROR:  permission denied to create extension "file_fdw"
HINT:  Must be superuser to create this extension.
Command was: CREATE EXTENSION IF NOT EXISTS file_fdw WITH SCHEMA public;
...
pg_restore: error: could not execute query: ERROR:  role "on_prem_postgresql_to_amazon_rds_app" does not exist
Command was: ALTER LARGE OBJECT 19159 OWNER TO on_prem_postgresql_to_amazon_rds_app;
...
pg_restore: error: could not execute query: ERROR:  permission denied for tablespace on_prem_postgresql_to_amazon_rds_fast
Command was: CREATE INDEX orders_note_trgm ON app.orders USING gin (note public.gin_trgm_ops);
pg_restore: error: pg_restore:could not execute query: ERROR:  role "on_prem_postgresql_to_amazon_rds_ro" does not exist
Command was: GRANT USAGE ON SCHEMA app TO on_prem_postgresql_to_amazon_rds_ro;
...
pg_restore: error: could not execute query: ERROR:  permission denied to create event trigger "log_ddl"
HINT:  Must be superuser to create an event trigger.
Command was: CREATE EVENT TRIGGER log_ddl ON ddl_command_end
   EXECUTE FUNCTION app.log_ddl();
...
pg_restore: warning: errors ignored on restore: 54

The log held 50 ERROR lines, while the closing warning counted 54. Grouped: 29 for the missing owner role (25 large objects, three tables, the schema), 4 for the missing read-only role, 6 for transaction_timeout (below), one for handing the event trigger's function to postgres, and the rest for the two untrusted extensions, the foreign server and table that depend on one of them, the event trigger and the tablespace. pg_restore keeps going by default and prints a count at the end, so a restore that "finished" can still be missing an index.

Two of these are stricter here than on RDS. The CREATE EVENT TRIGGER docs say "Only superusers can create event triggers", but AWS's DMS source page has you create one while logged in with an account holding rds_superuser, so RDS allows it. And rds_superuser can create any extension on the RDS list. Neither adminpack nor file_fdw is on that list, so on RDS those two would fail for a different reason.

The transaction_timeout errors are a client version issue. The 18.6 pg_restore sent SET transaction_timeout = 0 on its connections, and PostgreSQL 16 has no such setting. They were harmless here, but the pg_dump docs say plainly that loading into an older major version is not guaranteed. Use client tools that match the RDS engine version.

Create the roles first, without the superuser attributes

Replaying the pg_dumpall file as the admin role created the roles but rejected every ALTER ROLE line, including ones that set NOSUPERUSER:

CREATE ROLE
psql:roles.sql:17: ERROR:  permission denied to alter role
DETAIL:  Only roles with the SUPERUSER attribute may change the SUPERUSER attribute.
...
psql:roles.sql:22: ERROR:  role "postgres" already exists
psql:roles.sql:23: ERROR:  permission denied to alter role
DETAIL:  Only roles with the SUPERUSER attribute may alter roles with the SUPERUSER attribute.

pg_dumpall writes every attribute out explicitly, and a non-superuser may not set SUPERUSER, not even to NOSUPERUSER. I dropped the postgres and superuser dba lines and stripped SUPERUSER, plus REPLICATION and BYPASSRLS while I was at it:

$ grep -E '^(CREATE|ALTER) ROLE' roles.sql \
    | grep -v -E 'ROLE (postgres|on_prem_postgresql_to_amazon_rds_dba)[ ;]' \
    | sed -E 's/ (NO)?(SUPERUSER|REPLICATION|BYPASSRLS)//g' > roles_rds.sql
$ cat roles_rds.sql
CREATE ROLE on_prem_postgresql_to_amazon_rds_app;
ALTER ROLE on_prem_postgresql_to_amazon_rds_app WITH INHERIT NOCREATEROLE NOCREATEDB LOGIN;
CREATE ROLE on_prem_postgresql_to_amazon_rds_ro;
ALTER ROLE on_prem_postgresql_to_amazon_rds_ro WITH INHERIT NOCREATEROLE NOCREATEDB LOGIN;

That file loaded with no errors. The DBA who had SUPERUSER on the old box gets GRANT rds_superuser TO ... on RDS, if anyone.

The restore with the RDS flags

Leave out what RDS cannot take by editing the archive's table of contents. pg_restore -l lists it, and a line starting with ; is skipped by -L:

$ pg_restore -l src.dir > toc.list
$ sed -E -i '' '/adminpack|file_fdw|csv_files|host_feed|EVENT TRIGGER/s/^/;/' toc.list
$ grep '^;[0-9]' toc.list
;5; 3079 19112 EXTENSION - adminpack
;3605; 0 0 COMMENT - EXTENSION adminpack
;6; 3079 19122 EXTENSION - file_fdw
;3607; 0 0 COMMENT - EXTENSION file_fdw
;2197; 1417 19185 SERVER - csv_files postgres
;227; 1259 19186 FOREIGN TABLE app host_feed postgres
;3435; 3466 19190 EVENT TRIGGER - log_ddl postgres

$ pg_restore --no-owner --no-privileges --no-tablespaces -j 4 -L toc.list -d "$TGT" src.dir
pg_restore: error: could not execute query: ERROR:  unrecognized configuration parameter "transaction_timeout"
Command was: SET transaction_timeout = 0;
...
pg_restore: warning: errors ignored on restore: 2

Only the client-version noise was left. With --no-owner the connecting user owns everything it creates, --no-privileges skips the GRANTs, and --no-tablespaces builds the trigram index in the default tablespace. That is what you want on RDS, where AWS's own EC2 import example restores as the master user.

After the restore: owners and grants

Everything now belonged to the admin role. Two things failed when I put it back, and both will look familiar on RDS 16, where the master user is not a superuser (I ran them on my stand-in, not on RDS):

ALTER TABLE app.orders OWNER TO on_prem_postgresql_to_amazon_rds_app;
ERROR:  must be able to SET ROLE "on_prem_postgresql_to_amazon_rds_app"

The ALTER TABLE docs say you must be able to SET ROLE to the new owner, and that role needs CREATE on the schema. So: GRANT on_prem_postgresql_to_amazon_rds_app TO on_prem_postgresql_to_amazon_rds_admin, then ALTER SCHEMA app OWNER TO ... first, then the tables. In the other order the tables fail with permission denied for schema app. Sequences owned by identity columns moved with their tables.

The read-only role got USAGE on the schema and SELECT on all tables, and still could not read a document:

ERROR:  permission denied for large object 19159

Large objects carry their own privileges: SELECT to read, UPDATE to write. GRANT SELECT ON ALL TABLES does not touch them, so my read-only role could not have read them on the source either, and --no-privileges leaves out whatever large object grants the source did have (lomacl in pg_largeobject_metadata shows them). A loop over pg_largeobject_metadata running GRANT SELECT ON LARGE OBJECT <oid> TO ... fixed it, and the same query then returned invoice-1.pdf with 2,200 bytes.

Verify the counts

Same query on both sides, including a hash over every large object's bytes and the identity sequence:

                  db                  | customers | orders | order_total | large_objects |              lo_md5              | orders_seq
--------------------------------------+-----------+--------+-------------+---------------+----------------------------------+------------
 on_prem_postgresql_to_amazon_rds_src |      5000 | 200000 | 99999000.00 |            25 | 09df9a1c17c336e5fd730e45786629de |     200000
(1 row)

                  db                  | customers | orders | order_total | large_objects |              lo_md5              | orders_seq
--------------------------------------+-----------+--------+-------------+---------------+----------------------------------+------------
 on_prem_postgresql_to_amazon_rds_tgt |      5000 | 200000 | 99999000.00 |            25 | 09df9a1c17c336e5fd730e45786629de |     200000
(1 row)

Counts are the minimum; per-column aggregates and checksums are in How to Validate Data After a Migration. For the flags themselves, see pg_dump and pg_restore: the Commands That Matter.

The RDS side: settings for the load (not run)

From AWS's import page, for the import only: backup retention 0 and Multi-AZ off on the instance, and in a custom parameter group maintenance_work_mem of 512 MB to 4 GB depending on the host, max_wal_size 4096, checkpoint_timeout 1800, synchronous_commit off, wal_buffers 8192 and autovacuum 0, then back to production values. Default parameter groups cannot be edited, so you create a custom one and attach it; wal_buffers is a static parameter there and needs a reboot. After the load, AWS's EC2 import page recommends a DB snapshot before anything else, so a mishap does not mean loading again. If you have not settled on plain RDS yet, Aurora vs RDS: Which One to Migrate To compares the two.

Short downtime: logical replication or DMS (not run)

Native logical replication runs from your server to RDS: a publication on the on-prem side and a subscription on RDS. The publisher needs wal_level = logical and enough max_replication_slots and max_wal_senders. AWS's logical replication page shows the CREATE SUBSCRIPTION on the target with a connection string to the source host. On the RDS side, logical slots are turned on with the static rds.logical_replication parameter: set it to 1 in a custom parameter group and reboot, and RDS sets wal_level, max_wal_senders, max_replication_slots and max_connections with it. You need that when the RDS instance itself has to publish, for example a rollback stream back to the old server; AWS warns that a slot nobody reads from can fill the instance's storage.

With AWS DMS and a self-managed source, CDC needs wal_level = logical, max_replication_slots and max_wal_senders above 1 in postgresql.conf, and a source account with superuser rights. DMS replicates changes for tables with primary keys; without one, updates cannot be applied unless you work around it with replica identity. Load the schema with the restore above first. The cutover sequence and lag checks are in Zero Downtime Database Migration with CDC.

If what you have is a SQL dump from another engine and not a running PostgreSQL server, the SQL to PostgreSQL converter turns it into PostgreSQL SQL you can then load into RDS with psql.

Sources