Migrate self-managed PostgreSQL to Amazon RDS using the RDS console

Running PostgreSQL on Amazon Elastic Compute Cloud (Amazon EC2) gives you full control over the database engine and operating system. But it also means you own every operational task: patching, backups, replication, failover, storage management, and security hardening. This overhead grows with your workload.

Migrating to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition offloads these responsibilities to a fully managed service. You get automated backups, point-in-time recovery, automated failover, automatic storage scaling, and built-in security features including encryption, IAM database authentication, and AWS Secrets Manager integration. This frees your team to focus on application development rather than database administration.

In this post, we show you how to migrate a self-managed PostgreSQL database running on Amazon EC2 to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition. The migration uses the auto-migration capability built into the Amazon RDS console and powered by AWS Database Migration Service (AWS DMS).

Because this is a PostgreSQL-to-PostgreSQL migration, AWS DMS uses its homogeneous data migration capability, which uses PostgreSQL’s own native tools rather than a general-purpose replication engine. This means the migration precisely maps your source schema, data types, table partitions, and secondary objects such as functions and stored procedures to the target. This typically results in a substantially faster migration than heterogeneous DMS migration tasks.

Why the RDS console migration feature?

Several approaches exist for migrating PostgreSQL from EC2 to a managed service:

• pg_dump/pg_restore — Simple but requires full downtime for the duration of the dump and restore. No built-in change data capture (CDC).
• Manual AWS DMS task — Full control over replication settings, selection rules, and transformations, but requires manually configuring replication instances, endpoints, and tasks.
• Third-party tools (such as Bucardo, pgloader) — May suit specific use cases but lack native AWS integration and managed monitoring.

The RDS console approach is ideal when you want the reliability of DMS with minimal setup effort, particularly for straightforward PostgreSQL-to-PostgreSQL migrations that don’t require table filtering or custom transformations.

Migration process overview

AWS DMS homogeneous data migrations use PostgreSQL’s native tooling in a serverless environment and support three replication modes.

• Full load: Best for small databases or when a maintenance window is acceptable. Target is unavailable during restore.
• CDC only: Use when schema/data already exists on target and you only need to replicate ongoing changes. Minimal downtime (brief cutover).
• Full load + CDC: Best for production migrations needing minimal downtime. Performs initial sync through logical replication, then captures ongoing changes until cutover.

Note: Migration duration depends on database size, network throughput, and write activity on the source. The sample database in this walkthrough completes in minutes. For production workloads, we recommend running a test migration to estimate timing and plan your cutover window accordingly.

Prerequisites

Before starting the migration, make sure you have the following:

• Source: PostgreSQL 10.4+ running on Amazon EC2. See supported sources for homogeneous migrations.
• Target: RDS for PostgreSQL or Aurora PostgreSQL (same or higher version). See Creating an RDS DB instance or Creating an Aurora DB cluster.
• IAM role granting DMS access to databases, Secrets Manager, and Amazon CloudWatch. See Creating IAM resources for homogeneous data migrations.
• Secrets Manager: Source and target credentials stored as secrets.
• Networking: Security groups allowing TCP 5432 between EC2 source, and RDS/Aurora target.
• Source database user: Superuser (for CDC) or SELECT-only (for full load). Add the DMS IP address to pg_hba.conf.
• Target database user: rds_superuser role with CREATE, SELECT, INSERT, UPDATE, DELETE, TRUNCATE.
• Target parameter group: rds.logical_replication = 1 (requires reboot).

For complete setup details, see Setting up for homogeneous data migrations and Using PostgreSQL as a DMS source.

Considerations and limitations

Although the RDS console migration feature simplifies the process significantly, there are important considerations to be aware of before starting your migration.

• Unsupported objects: Tablespaces, roles, extensions, operator classes, and event triggers are not migrated.
• Large objects: bytea columns are supported. However, lo_* large objects are not replicated through logical replication.
• Sequence values: Sequences might not reflect the latest nextval after migration. Run SELECT setval() post-cutover.
• Reboot required: Enabling rds.logical_replication on the target requires a reboot.
• WAL retention: The source retains the write-ahead log (WAL) until consumed. Monitor disk usage on write-heavy databases.
• DDL changes: Avoid schema changes (DDL) on the source during CDC. They are not auto-replicated and require a migration restart to pick up new tables.
• No selection rules: The RDS console auto-migrate feature migrates all tables. Filtering is not supported.

For the full list of limitations, see Limitations for DMS homogeneous data migrations.

Perform homogeneous migration

The following sections walk you through preparing the source database, storing credentials, configuring the target, running the migration, and verifying the results.

Source database setup

Modify the following settings at the parameter group level, which is mapped to the database:

• wal_level – Set to logical.
• max_replication_slots – Set to a value greater than 1.
• max_wal_senders – Set to a value greater than 1.
• wal_sender_timeout – Set the value to 0. This parameter ends replication connections that are inactive longer than the specified number of milliseconds. The default is 60,000 milliseconds (60 seconds). Setting the value to 0 disables the timeout mechanism.

After you update the preceding parameters, restart your PostgreSQL source database.

Connect to PostgreSQL on the EC2 instance and create a database called testmigratedb:

# Connect to PostgreSQL on EC2
psql -h [IP_Address] -U postgres

-- Create database
CREATE DATABASE testmigratedb;

-- Connect to the database
\c testmigratedb

-- Create first table
CREATE TABLE migratetable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create second table
CREATE TABLE homogeneoustable (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    quantity INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data into migratetable
INSERT INTO migratetable (name, email) VALUES
('John Doe', 'john.doe@example.com'),
('Jane Smith', 'jane.smith@example.com'),
('Bob Johnson', 'bob.johnson@example.com');

-- Insert sample data into homogeneoustable
INSERT INTO homogeneoustable (product_name, price, quantity) VALUES
('Laptop', 999.99, 10),
('Mouse', 29.99, 50),
('Keyboard', 79.99, 30);

-- Verify data
SELECT * FROM migratetable;
SELECT * FROM homogeneoustable;

-- Create DMS user
CREATE USER dms_user WITH PASSWORD '[PASSWORD]!';

-- Grant necessary privileges for Full Load
GRANT CONNECT ON DATABASE testmigratedb TO dms_user;
GRANT USAGE ON SCHEMA public TO dms_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dms_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dms_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dms_user;

-- Additional privileges for CDC (Change Data Capture)
ALTER USER dms_user WITH REPLICATION;

-- Verify privileges
\du dms_user

-- Create publication for logical replication (required for CDC)
CREATE PUBLICATION dms_publication FOR ALL TABLES

Create source secrets on AWS Secrets Manager

AWS DMS uses AWS Secrets Manager to securely store and retrieve database credentials for both the source and target endpoints during migration.

Source secret configuration:

• Secret name: postgres-ec2-source-secret.
• Username: dms_user.
• Password: [PASSWORD]!.
• Server address: EC2 private IP address.
• Database name: testmigratedb.
• Port: 5432 (PostgreSQL default).

Target secret: Auto-created with the RDS instance.

Configure target database permissions

AWS DMS requires certain permissions to migrate data to your target RDS for PostgreSQL or Aurora PostgreSQL database.

Connect to your target RDS or Aurora instance and run the following script:

create database db_name;
CREATE USER your_user WITH LOGIN PASSWORD 'your_password';
GRANT CONNECT ON DATABASE db_name to your_user;
GRANT CREATE ON DATABASE db_name to your_user;
GRANT USAGE ON SCHEMA schema_name TO your_user;
GRANT CREATE ON SCHEMA schema_name TO your_user;
GRANT UPDATE, INSERT, SELECT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA schema_name TO your_user;
GRANT rds_superuser TO your_user;
GRANT rds_replication TO your_user;

Set rds.logical_replication = 1 in the target DB parameter group and reboot the instance. This is a static parameter required for the publisher-subscriber replication model used by DMS homogeneous migrations.

Execute migration using the RDS console

Complete the following steps:

1. Navigate to the RDS console Databases page.
2. Select the target RDS or Aurora database instance.
3. Choose Data migrations tab → Create data migration.
4. Configure the source (EC2 PostgreSQL).
5. Configure the target (RDS PostgreSQL).
6. Select the migration type (Full load or Full load and CDC).
7. View migration settings.
8. Choose Migrate and verify the success notification or review any error messages with reasons.

   

9. Monitor progress through CloudWatch logs.
10. Verify completion.

Verify migration

After the migration completes, connect to the target RDS instance and confirm that all tables, row counts, and data match the source.

# Connect to target RDS PostgreSQL
psql -h <RDS-Endpoint> -U postgres -d postgres

-- Connect to migrated database
\c testmigratedb

-- List all tables
\dt

-- Verify data in migratetable
SELECT * FROM migratetable;

-- Verify data in homogeneoustable
SELECT * FROM homogeneoustable;

-- Check row counts
SELECT COUNT(*) FROM migratetable;
SELECT COUNT(*) FROM homogeneoustable;

postgres=> \c testmigratedb
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
You are now connected to database "testmigratedb" as user "postgres".

testmigratedb=> \dt
                List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | homogeneoustable | table | postgres
 public | migratetable     | table | postgres
(2 rows)

testmigratedb=> SELECT COUNT(*) FROM migratetable;
 count
-------
     4
(1 row)

testmigratedb=> SELECT COUNT(*) FROM homogeneoustable;
 count
-------
     3
(1 row)

Verify CDC and full load replication

On the source PostgreSQL 10.0 instance:

testmigrated=# INSERT INTO migratetable (name, email) VALUES ('Charlie Davis', 'charlie.davis@example.com');
INSERT 0 1
testmigrated=# INSERT INTO homogeneoustable (product_name, price, quantity) VALUES ('Monitor', 349.99, 20);
INSERT 0 1
testmigrated=# select * from homogeneoustable;
 id | product_name | price  | quantity |         created_at
----+--------------+--------+----------+----------------------------
  1 | Laptop       | 999.99 |       10 | 2026-02-25 00:00:08.865164
  2 | Mouse        |  29.99 |       50 | 2026-02-25 00:00:08.865164
  3 | Keyboard     |  79.99 |       30 | 2026-02-25 00:00:08.865164
  4 | Monitor      | 349.99 |       20 | 2026-02-25 16:58:40.810747
(4 rows)

testmigrated=# select * from migratetable;
 id |     name      |           email           |         created_at
----+---------------+---------------------------+----------------------------
  1 | John Doe      | john.doe@example.com      | 2026-02-25 08:00:01.745175
  2 | Jane Smith    | jane.smith@example.com    | 2026-02-25 00:00:01.745175
  3 | Bob Johnson   | bob.johnson@example.com   | 2026-02-25 08:00:01.745175
  4 | Alice Brown   | alice.brown@example.com   | 2026-02-25 02:00:12.029536
  5 | Charlie Davis | charlie.davis@example.com | 2026-02-25 16:58:24.821607
(5 rows)

# Connect to EC2 PostgreSQL
psql -h <your-endpoint> -U postgres -d postgres
Password for user postgres:
psql (15.15)
Type "help" for help.

postgres=# \c testmigratedb
You are now connected to database "testmigratedb" as user "postgres".
testmigratedb=#
testmigratedb=# SELECT COUNT(*) FROM migratetable;
SELECT COUNT(*) FROM homogeneoustable;
 count
-------
     5
(1 row)

 count
-------
     4
(1 row)

On the target RDS for PostgreSQL instance:

[testmigrated]=> select * from homogeneoustable;
 id | product_name | price  | quantity |         created_at
----+--------------+--------+----------+----------------------------
  1 | Laptop       | 999.99 |       10 | 2026-02-25 08:00:08.865164
  2 | Mouse        |  29.99 |       50 | 2026-02-25 00:00:08.865164
  3 | Keyboard     |  79.99 |       30 | 2026-02-25 00:00:08.865164
  4 | Monitor      | 349.99 |       20 | 2026-02-25 16:58:40.810747
(4 rows)

[testmigrated]=> select * from migratetable;
 id |     name      |           email           |         created_at
----+---------------+---------------------------+----------------------------
  1 | John Doe      | john.doe@example.com      | 2026-02-25 00:00:01.745175
  2 | Jane Smith    | jane.smith@example.com    | 2026-02-25 00:00:01.745175
  3 | Bob Johnson   | bob.johnson@example.com   | 2026-02-25 00:00:01.745175
  4 | Alice Brown   | alice.brown@example.com   | 2026-02-25 12:07:12.920536
  5 | Charlie Davis | charlie.davis@example.com | 2026-02-25 16:58:26.821667
(5 rows)

[testmigrated]=> SELECT COUNT(*) FROM migratetable;
 count
-------
     5
(1 row)

[testmigrated]=> SELECT COUNT(*) FROM homogeneoustable;
 count
-------
     4
(1 row)

After you insert more data on the source EC2 instance, the count on the target increases.

testmigrated=> SELECT COUNT(*) FROM migratatable;
 count
-------
     6
(1 row)

testmigrated=> SELECT COUNT(*) FROM homogeneoustable;
 count
-------
     5
(1 row)

The expected result is that the row count on the target reflects the newly inserted rows from the source, which confirms that CDC replication is working:

-- Row count on target should reflect newly inserted rows from source
-- confirming CDC replication is working as expected

Post-migration optimization

After the migration is complete and traffic has been switched to the target, run the following maintenance operations for optimal query performance:

-- Connect to the migrated database on RDS/Aurora
psql -h <RDS-Endpoint> -U postgres -d testmigratedb

-- Update table statistics for the query planner
ANALYZE VERBOSE;

-- Rebuild all indexes (run during low-traffic period)
REINDEX DATABASE testmigratedb;

-- Reclaim storage and update visibility map (for heavily updated tables)
VACUUM FULL ANALYZE;

Note: REINDEX DATABASE and VACUUM FULL are blocking operations. Schedule them during a low-traffic maintenance window. For Aurora PostgreSQL, consider using pg_repack as a non-blocking alternative.

Post-migration security hardening

After the migration is complete and the application is running successfully on the target, follow these security best practices to harden your environment.

• Disable or remove the DMS user: The dms_user account was created with elevated replication privileges for the purpose of migration. After migration is complete, disable or remove this account:

  -- Connect to the source EC2 PostgreSQL instance
  psql IP_Address -U postgres -d postgres
  
  -- Option 1: Disable login (recommended --- preserves audit trail)
  ALTER USER dms_user WITH NOLOGIN;
  
  -- Option 2: Revoke all privileges
  REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM dms_user;
  REVOKE rds_replication FROM dms_user;
  
  -- Option 3: Drop the user entirely (only if no dependencies exist)
  DROP USER dms_user;

• Rotate the RDS master password: Immediately after migration, rotate the RDS master password:
  • Navigate to Secrets Manager → Your Secret → Rotation.
  • Turn on automatic rotation with a schedule (such as every 30 days).
• Enforce SSL/TLS connections: Make sure all connections to your RDS instance use SSL/TLS encryption.

  -- Verify SSL is enforced on RDS
  -- In your RDS parameter group, set:
  -- rds.force_ssl = 1

• Turn on encryption: Encryption at rest should be turned on at instance creation. Verify under RDS → Configuration → Storage encrypted = Yes.

Troubleshooting

The following are common errors you might encounter during the migration process, and their resolutions.

PostgreSQL-specific errors

Error 1: Permission denied for replication

Error message:

ERROR: permission denied to create publication

Reason: The DMS database user lacks the REPLICATION role privilege, which is required to create a logical replication publication on the source.

Resolution:

ALTER USER dms_user WITH REPLICATION;
GRANT rds_replication TO dms_user;

Error 2: WAL level not set to logical

Error message:

ERROR: logical decoding requires wal_level >= logical

Reason: The source PostgreSQL instance has wal_level set to replica or minimal instead of logical, which is required for CDC-based migrations using logical replication.

Resolution:

# Edit postgresql.conf
sudo vi /var/lib/pgsql/data/postgresql.conf

# Set wal_level
wal_level = logical

# Restart PostgreSQL
sudo systemctl restart postgresql

Error 3: Publication does not exist

Error message:

ERROR: publication "dms_publication" does not exist

Reason: The logical replication publication was not created on the source database before starting the DMS migration task. DMS homogeneous migrations require an active publication to subscribe to.

Resolution:

CREATE PUBLICATION dms_publication FOR ALL TABLES;
SELECT * FROM pg_publication;

Error 4: Insufficient replication slots

Error message:

ERROR: all replication slots are in use

Reason: The max_replication_slots parameter is set too low, and all available slots are consumed by existing replication connections or prior migration attempts that were not cleaned up.

Resolution:

# Edit postgresql.conf
sudo vi /var/lib/pgsql/data/postgresql.conf

# Increase replication slots
max_replication_slots = 10
max_wal_senders = 10

# Restart PostgreSQL
sudo systemctl restart postgresql

Error 5: Connection refused

Error message:

ERROR: could not connect to server: Connection refused

Reason: The source PostgreSQL instance is not configured to accept connections from the DMS replication server, typically a missing entry in pg_hba.conf or a security group rule blocking port 5432.

Resolution:

# Edit pg_hba.conf
sudo vi /var/lib/pgsql/data/pg_hba.conf

# Add entry for DMS connection
host all all [IP_ADDRESS] md5

# Restart PostgreSQL
sudo systemctl restart postgresql

Comprehensive DMS user privileges

-- Connect to database
\c testmigratedb

-- Grant all necessary privileges
GRANT CONNECT ON DATABASE testmigratedb TO dms_user;
GRANT USAGE ON SCHEMA public TO dms_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dms_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dms_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dms_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO dms_user;

-- For CDC
ALTER USER dms_user WITH REPLICATION;
GRANT rds_replication TO dms_user;

-- Verify all privileges
\du+ dms_user

Clean up

To avoid ongoing charges, delete the resources created during this walkthrough:

1. Delete the DMS migration task — In the RDS console, select the database and navigate to the Data migrations tab, select your migration, and choose Delete.
2. Drop the logical replication publication and slot on the source:

   DROP PUBLICATION dms_publication;
   SELECT pg_drop_replication_slot('slot_name');

3. Delete Secrets Manager secrets — In the Secrets Manager console, delete postgres-ec2-source-secret and the target secret (if manually created). Choose Delete immediately or set a recovery window.
4. Delete the target RDS/Aurora instance — In the RDS console, select the target DB instance, choose Actions → Delete. Deselect Create final snapshot if this was a test.
5. Delete the DMS IAM role — In the IAM console, delete the role created for DMS access.
6. Clean up security groups — Remove any inbound rules added specifically for this migration.
7. Terminate the source EC2 instance (if no longer needed) — In the EC2 console, select the instance and choose Instance state → Terminate.

Conclusion

In this post, we demonstrated how to migrate a self-managed PostgreSQL database on Amazon EC2 to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL using AWS DMS homogeneous data migrations. We covered how DMS uses PostgreSQL’s native tools — pg_dump, pg_restore, and built-in logical replication to migrate schema objects, data, and ongoing changes with minimal downtime.

We also walked through the key prerequisites including WAL configuration for CDC, superuser permissions for replication, and the publisher-subscriber model that keeps your source database available throughout the migration. With these native AWS migration tools, you can move to a fully managed PostgreSQL service while reducing operational overhead and maintaining data consistency during cutover.

If you have questions about PostgreSQL migrations using DMS, leave them in the comments.

About the authors