Preparing AWS RDS source databases

Before migrating data from an AWS RDS 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.

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

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

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

4. Reboot your AWS RDS database to apply the changes.

5. Verify that the settings have been set correctly by connecting to the database and checking whether wal_level is now set to logical.

   → WrapCopy

   SHOW WAL_LEVEL;

   The output should be 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 new role for migration with LOGIN:

   → WrapCopy

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

3. Grant the new 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 the original owner of the table and the <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.

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

   ALTER DEFAULT PRIVILEGES IN SCHEMA <DB_SCHEMA> GRANT SELECT ON TABLES 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.

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>