Preparing your AWS RDS/Aurora PostgreSQL source database v1.3.2

The December 2025 Innovation Release of EDB Postgres AI is available. For more information, see the release notes.

Before migrating data from an AWS RDS for PostgreSQL or Aurora PostgreSQL database using the EDB Data Migration Service, you must prepare the source database to allow the EDB DMS Reader to connect to the source and perfom replication.

Enable logical replication

To perform logical replication, the EDB DMS Reader requires rds.logical_replication set to 1 and wal_level set to logical.

Because AWS RDS databases are databases managed by a cloud service provider, their default configurations cannot be changed directly with psql. Instead, create a parameter group with replication enabled and associate it with your RDS instance.

Create a new parameter group using either the AWS Management Console, AWS CLI or RDS API. Ensure the parameter group family matches the postgres version your RDS database is using.
See Parameter groups for Amazon RDS for additional configuration information.
Associate the created parameter group with your source Amazon RDS database using either the AWS Management Console, AWS CLI or RDS API.
Modify a parameter in a parameter group using either the AWS Management Console, AWS CLI or RDS API.
For this parameter group, you must set rds.logical_replication to 1. After you configure this, the database will automatically set wal_level to logical.
Reboot your AWS RDS database to apply the changes.
Verify that the settings have been set correctly by connecting to the database and checking whether wal_level is now set to logical.
```
SHOW WAL_LEVEL;
```
The output should be logical.

Create a role for CDC migration

To create a role for the migration, connect to the source database as postgres or a user with superuser access:
```
psql -h <PG_HOST> -p <PG_PORT> -U <PG_USERNAME> -d <PG_DB_NAME>
```
Where:
- <PG_DB_NAME> is the name of the Postgres database source to connect to.
- <PG_HOST> is the Postgres database host.
- <PG_PORT> is the Postgres database port.
- <PG_USERNAME> is an administrative user or superuser who can create and grant roles, alter ownership of tables to migrate, and create a replication slot.
This command prompts you for the password associated with <PG_USERNAME>.

Create a new role for migration with LOGIN:

CREATE ROLE <MIGRATION_ROLE> WITH LOGIN PASSWORD '<MIGRATION_ROLE_PASSWORD>';

Grant the new role RDS_REPLICATION abilities:
```
GRANT RDS_REPLICATION to <MIGRATION_ROLE>;
```
<MIGRATION_ROLE> must be the owner of the source tables you want to migrate so it can autocreate Postgres publications.
A way to do this is to create an additional role (here <REPLICATION_GROUP>) which will be the new table owner, and grant this role to both the the original owner of the table and the <MIGRATION_ROLE>.
```
CREATE ROLE <REPLICATION_GROUP>;
GRANT <REPLICATION_GROUP> TO <MIGRATION_ROLE>;
GRANT <REPLICATION_GROUP> TO <ORIGINAL_OWNER>;
ALTER TABLE <TABLE_NAME> OWNER TO <REPLICATION_GROUP>;
```
Where:
- <MIGRATION_ROLE> is the name of the Postgres role or user to use for migration database access.
- <ORIGINAL_OWNER> is the original production owner of the table.
- <REPLICATION_GROUP> is the name of a role used to own the source tables to migrate for publication autocreation.
Grant USAGE of the source schemas containing tables to be migrated to the migration role:
```
GRANT USAGE ON SCHEMA <DB_SCHEMA> TO <MIGRATION_ROLE>;
```
The new <MIGRATION_ROLE> has schema access, but still needs SELECT access to the source tables of those schemas.
Grant SELECT on source tables to the migration role. You can grant access across a schema or for each table.
For all tables in a schema, use this command:
```
ALTER DEFAULT PRIVILEGES IN SCHEMA <DB_SCHEMA> GRANT SELECT ON TABLES to <MIGRATION_ROLE>
```
For each table, use:
```
GRANT SELECT ON <TABLE_NAME> TO <MIGRATION_ROLE>
```
Where:
- <DB_SCHEMA> is the database schema name for the tables to migrate.
- <MIGRATION_ROLE> is the name of the Postgres role or user to use for migration database access.
- <TABLE_NAME> is the name of a table to migrate.
Grant CREATE privileges on the source database to the migration role. This is required by the pgoutput plugin so the user can create publications.
```
GRANT CREATE ON DATABASE <PG_DB_NAME> to <MIGRATION_ROLE>
```

Validate your configuration

Now that you have configured the source database, validate your Postgres configuration. For this, you need the configuration validation script packaged with the DMS Reader.

Prerequisites

Ensure the machine from where you are running the Reader has Java/OpenJDK 17 or later installed.
Install the EDB DMS Reader.
Navigate to the EDB DMS Reader folder in /opt/cdcreader/.

Run the config validation script

To illustrate, consider the following example:

Create an array that contains the name of all the tables that need to be migrated in <schema_name>.<table_name> format. In this example:
```
arr=(test1.table1 test1.table2 test1.table3 test1.table4)
```

Run the script with the necessary parameters. In this example:

PG_USERNAME=postgres PG_PASSWORD=password PG_HOST=localhost PG_PORT=5432     DB_NAME=postgres DBZ_USERNAME=debezium DBZ_PASSWORD=dbz ./postgresConfigValidation.sh     "${arr[@]}"

You should get a response similar to the following:

 *** [Transporter] - Validate WAL Level
wal_level:  logical
[Pass] wal_level is 'logical'.

 *** [Transporter] - Validate max WAL senders
max_wal_senders: 10
[Pass] max_wal_senders is at least 1.

 *** [Transporter] - Validate max replication slots
max_replication_slots: 10
[Pass] max_replication_slots is at least 1.

 *** [Transporter] - Validate max WAL size
max_wal_size: 1 GB
[Fail] max_wal_size (1 GB) is less than 8 GB

 *** [Transporter] - Validate checkpoints
checkpoints_timed: 378
checkpoints_req: 3
[Pass] Timed checkpoints are more frequent than requested checkpoints.

 *** [Transporter] - Check debezium user role
[Pass] User 'debezium' is present
[Pass] User 'debezium' has replication permission

 *** [Transporter] - Check SELECT privilege on the tables to be migrated
[Pass] User debezium has select privilege on all tables to be migrated.

 *** [Transporter] - Check presence of a replication slot
[Fail] Replication slot 'debezium' is not present

Note

In the previous example, the max_wal_size check failed, but the migration ran without any issues. Therefore, although this check might fail, a lower setting may be sufficient in certain use cases depending on the workload on the source database.

Additionally, you can ignore the failure message related to the replication slot. This is an outdated check that is no longer required as the EDB DMS Reader automatically creates and manages the required replication slot. The check will be removed in a future version of the postgresConfigValidation.sh script.

Your database is ready for CDC migration.

↑ Up

Migrate your AWS RDS/Aurora PostgreSQL database to an HM-managed database