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

Running PostgreSQL on Amazon Elastic Compute Cloud (Amazon EC2) provides users with complete control over the database engine and operating system. However, this autonomy comes with the responsibility of managing operational tasks such as patching, backups, replication, failover, storage management, and security hardening. As workloads increase, so does the complexity of these tasks.

Transitioning to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition alleviates these burdens by offering a fully managed service. This migration allows for automated backups, point-in-time recovery, automated failover, automatic storage scaling, and integrated security features like encryption, IAM database authentication, and AWS Secrets Manager integration. Consequently, development teams can redirect their focus from database administration to application development.

This article outlines the steps to migrate a self-managed PostgreSQL database from Amazon EC2 to either Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition. The migration leverages the auto-migration feature within the Amazon RDS console, powered by AWS Database Migration Service (AWS DMS).

As this is a PostgreSQL-to-PostgreSQL migration, AWS DMS utilizes its homogeneous data migration capability, employing PostgreSQL’s native tools instead of a general-purpose replication engine. This approach ensures an accurate mapping of the source schema, data types, table partitions, and secondary objects like functions and stored procedures to the target, typically resulting in a significantly faster migration compared to heterogeneous DMS tasks.

Why the RDS console migration feature?

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

• pg_dump/pg_restore — A straightforward option that necessitates full downtime during the dump and restore process, lacking built-in change data capture (CDC).
• Manual AWS DMS task — Provides comprehensive control over replication settings, selection rules, and transformations, but requires manual configuration of replication instances, endpoints, and tasks.
• Third-party tools (such as Bucardo, pgloader) — May cater to specific use cases but lack native AWS integration and managed monitoring.

The RDS console approach is particularly advantageous for those seeking the reliability of DMS with minimal setup effort, especially for uncomplicated PostgreSQL-to-PostgreSQL migrations that do not necessitate table filtering or custom transformations.

Migration process overview

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

• Full load: Ideal for smaller databases or when a maintenance window is permissible. The target remains unavailable during the restore process.
• CDC only: Suitable when the schema/data already exists on the target, requiring only the replication of ongoing changes. This mode allows for minimal downtime (brief cutover).
• Full load + CDC: Recommended for production migrations that demand minimal downtime. This method performs an initial sync through logical replication and subsequently captures ongoing changes until cutover.

Note that the duration of the migration is contingent upon the database size, network throughput, and write activity on the source. The sample database in this walkthrough completes in mere minutes. For production workloads, it is advisable to conduct a test migration to gauge timing and plan the cutover window accordingly.

Prerequisites

Before initiating the migration, ensure you have the following:

• Source: PostgreSQL 10.4+ running on Amazon EC2. Refer to supported sources for homogeneous migrations.
• Target: RDS for PostgreSQL or Aurora PostgreSQL (same or higher version). Consult 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: Store source and target credentials as secrets.
• Networking: Security groups must allow TCP 5432 traffic between the EC2 source and the RDS/Aurora target.
• Source database user: Superuser (for CDC) or SELECT-only (for full load). Ensure the DMS IP address is added to pg_hba.conf.
• Target database user: Role of rds_superuser with CREATE, SELECT, INSERT, UPDATE, DELETE, and TRUNCATE privileges.
• Target parameter group: Set rds.logical_replication = 1 (requires reboot).

For comprehensive setup details, refer to Setting up for homogeneous data migrations and Using PostgreSQL as a DMS source.

Considerations and limitations

While the RDS console migration feature greatly simplifies the process, there are essential considerations to keep in mind before proceeding:

• Unsupported objects: Tablespaces, roles, extensions, operator classes, and event triggers will not be migrated.
• Large objects: bytea columns are supported, but lo_* large objects cannot be replicated through logical replication.
• Sequence values: Sequences may not reflect the latest nextval after migration. Execute SELECT setval() post-cutover.
• Reboot required: Enabling rds.logical_replication on the target necessitates 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, as they are not auto-replicated and require a migration restart to incorporate new tables.
• No selection rules: The RDS console auto-migrate feature migrates all tables without support for filtering.

For a complete list of limitations, see Limitations for DMS homogeneous data migrations.

Perform homogeneous migration

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

Source database setup

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

• wal_level – Set to logical.
• max_replication_slots – Increase this value to greater than 1.
• max_wal_senders – Set this value to greater than 1.
• wal_sender_timeout – Configure this value to 0 to disable the timeout mechanism for inactive replication connections.

After updating these parameters, restart your PostgreSQL source database.

Connect to PostgreSQL on the EC2 instance and create a database named 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 utilizes 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: Automatically created with the RDS instance.

Configure target database permissions

AWS DMS requires specific permissions to migrate data to your target RDS for PostgreSQL or Aurora PostgreSQL database. Connect to your target RDS or Aurora instance and execute 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 static parameter is essential for the publisher-subscriber replication model employed by DMS homogeneous migrations.

Execute migration using the RDS console

Follow these steps to execute the migration:

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. Review migration settings.
8. Choose Migrate and check for success notifications or review any error messages.
9. Monitor progress through CloudWatch logs.
10. Verify completion.

Verify migration

Once the migration is complete, 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  -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;

After confirming the migration, you can also verify CDC and full load replication by inserting new data into the source PostgreSQL instance and checking that the target reflects these changes.

Post-migration optimization

Upon completing the migration and switching traffic to the target, perform the following maintenance operations for optimal query performance:

-- Connect to the migrated database on RDS/Aurora
psql -h  -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 that REINDEX DATABASE and VACUUM FULL are blocking operations, so they should be scheduled during low-traffic maintenance windows. 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 smoothly on the target, adhere to these security best practices to fortify your environment:

• Disable or remove the DMS user: The dms_user account was created with elevated replication privileges for migration purposes. After migration, disable or remove this account.
• Rotate the RDS master password: Immediately after migration, rotate the RDS master password through Secrets Manager.
• Enforce SSL/TLS connections: Ensure all connections to your RDS instance utilize SSL/TLS encryption.
• Turn on encryption: Verify that encryption at rest is enabled during instance creation.

Troubleshooting

Below are common errors you may encounter during the migration process, along with their resolutions.

PostgreSQL-specific errors

Error 1: Permission denied for replication

Error message:

# Error message example

Reason: The DMS database user lacks the REPLICATION role privilege necessary for creating a logical replication publication on the source.

Resolution: Grant the REPLICATION role privilege to the DMS user.

Error 2: WAL level not set to logical

Error message:

# Error message example

Reason: The source PostgreSQL instance has wal_level set to replica or minimal instead of logical.

Resolution: Update the wal_level to logical and restart the instance.

Error 3: Publication does not exist

Error message:

# Error message example

Reason: The logical replication publication was not created on the source database prior to starting the DMS migration task.

Resolution: Create the publication on the source database.

Error 4: Insufficient replication slots

Error message:

# Error message example

Reason: The max_replication_slots parameter is set too low, and all available slots are occupied by existing replication connections.

Resolution: Increase the max_replication_slots value.

Error 5: Connection refused

Error message:

# Error message example

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

Resolution: Adjust the pg_hba.conf and security group rules accordingly.

Comprehensive DMS user privileges

# Connect to target RDS PostgreSQL
psql -h  -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;

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, navigate to the Data migrations tab, select your migration, and choose Delete.
2. Drop the logical replication publication and slot on the source:

# Connect to target RDS PostgreSQL
psql -h  -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;

3. Delete Secrets Manager secrets — In the Secrets Manager console, delete postgres-ec2-source-secret and the target secret (if manually created).
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.