Preparing your AWS RDS/Aurora PostgreSQL source database Innovation Release

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 Agent to connect to the source and perform replication.

Enable logical replication

To perform logical replication, the EDB DMS Agent in reader mode requires:

• rds.logical_replication set to 1
• wal_level set to logical
• Optionally, max_wal_size set to the recommended setting of 8 GB or higher for improved migration performance

Because AWS RDS databases are managed by a cloud service provider, you can't change their default configurations with psql. Instead, create a DB cluster parameter group with replication enabled and associate it with your RDS instance.

1. Create a DB cluster parameter group using either the AWS Management Console, AWS CLI, or RDS API. Ensure the DB cluster parameter group family matches the Postgres version your RDS database is using.

   See Parameter groups for Amazon RDS for additional configuration information.

2. Associate the created DB cluster parameter group with your source Amazon RDS database using either the AWS Management Console, AWS CLI, or RDS API.

3. Modify parameters in a DB cluster parameter group using either the AWS Management Console, AWS CLI, or RDS API.

   • For this DB cluster parameter group, you must set rds.logical_replication to 1. After you configure this, the database sets wal_level to logical.

   • Ensure max_wal_size is configured for adequate WAL LSN lifetime.

     Note

     If your source database is an Amazon Aurora PostgreSQL instance, you won't be able to modify max_wal_size as it is managed by AWS. Only Amazon RDS PostgreSQL instances allow you to edit the max_wal_size parameter.

     Set the max_wal_size value large enough that production traffic generates mostly timed checkpoints rather than requested checkpoints based on WAL size.

     The streaming migration process also requires changes to be available in the WAL until they can be streamed to durable message storage in the cloud infrastructure of EDB DMS. Setting max_wal_size too small can affect performance. It can also interfere with the migration process by allowing Postgres LSNs to be dropped from the WAL before they can be streamed. We recommend setting max_wal_size to at least 8 GB. Consult your DBA to determine the appropriate size.

4. To apply the changes, reboot your AWS RDS database.

5. Verify that the settings were set correctly by connecting to the database and checking whether wal_level is now set to logical and that max_wal_size is set as configured.

   → WrapCopy

   SHOW WAL_LEVEL;
   SHOW MAX_WAL_SIZE;

   The output is logical.

Create a role for CDC migration

1. To create a role for the migration, connect to the source database as postgres or a user with superuser access:

   → WrapCopy

   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>.

2. Create a role for migration with LOGIN:

   → WrapCopy

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

3. Grant the role RDS_REPLICATION abilities:

   → WrapCopy

   GRANT RDS_REPLICATION to <MIGRATION_ROLE>;

4. <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 original owner of the table and <MIGRATION_ROLE>.

   → WrapCopy

   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.

   • <TABLE_NAME> is the name of a table to migrate and is case sensitive.

5. Grant USAGE of the source schemas containing tables to be migrated to the migration role:

   → WrapCopy

   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.

6. 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:

   → WrapCopy

   GRANT SELECT ON ALL TABLES IN SCHEMA <DB_SCHEMA> TO <MIGRATION_ROLE>;

   For each table, use:

   → WrapCopy

   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 and is case sensitive.

7. Grant CREATE privileges on the source database to the migration role. This is required by the pgoutput plugin so the user can create publications.

   → WrapCopy

   GRANT CREATE ON DATABASE <PG_DB_NAME> to <MIGRATION_ROLE>

Validate your configuration

After you configure the source database, validate your Postgres configuration. For this, you need the configuration validation script packaged with the DMS Agent.

Prerequisites

1. Ensure the machine where you're running the DMS Agent has Java/OpenJDK 17 or later installed.

2. Install the EDB DMS Agent.

3. Navigate to the EDB DMS Agent folder in /opt/cdcagent/.

Run the config validation script

This example shows how to run the validation script.

1. Create an array that contains the name of all the tables that need to be migrated in <schema_name>.<table_name> format. For example:

   → WrapCopy

   arr=(test1.table1 test1.table2 test1.table3 test1.table4)

2. Run the script with the necessary parameters. For example:

   → WrapCopy

   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 get a response similar to the following:

   → WrapCopy

    *** [Transporter] - Get PostgreSQL Major Version
   PostgreSQL major version: 16
   
    *** [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: 8 GB
   [Pass] max_wal_size is at least 8 GB.
   
    *** [Transporter] - Validate checkpoints
   checkpoints_timed: 424
   checkpoints_req: 1
   [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 CREATE privilege on database
   [Pass] Role 'debezium' has CREATE privilege on database 'postgres'.

   Your database is ready for CDC migration.