Prepare AWS Aurora PostgreSQL/AWS RDS PostgreSQL for RDI

Prepare AWS Aurora PostgreSQL databases to work with RDI

Follow the steps in the sections below to prepare an AWS Aurora PostgreSQL or AWS RDS PostgreSQL database to work with RDI.

Create and apply parameter group

RDI requires some changes to database parameters. On AWS RDS and AWS Aurora, you change these parameters via a parameter group.

1. In the Relational Database Service (RDS) console, navigate to Parameter groups.

   If you have no existing parameter group, create a new parameter group with the following settings:

   Name	Value
   Parameter group name	Enter a suitable parameter group name, like rdi-aurora-pg or rdi-rds-pg
   Description	(Optional) Enter a description for the parameter group
   Engine Type	Choose Aurora PostgreSQL for Aurora PostgreSQL or PostgreSQL for AWS RDS PostgreSQL.
   Parameter group family	Choose aurora-postgresql15 for Aurora PostgreSQL or postgresql13 for AWS RDS PostgreSQL.

   Select Create to create the parameter group.

   If you do have an existing parameter group, select it and then either:

   • Select Edit from Parameter group actions to modify the parameter group with the settings shown in the table above.
   • Select Copy from Parameter group actions to copy the existing parameter group and then modify the copy with the settings shown in the table above.

2. Ensure that the parameter group you have just created or modified is selected and then select Edit. Change the following parameter:

   Name	Value
   rds.logical_replication	1

   Select Save Changes to apply the changes to the parameter group.

3. Go back to your database on the RDS console, select Modify and then scroll down to Additional Configuration. Set the DB Cluster Parameter Group to the group you just created.

   Select Save changes to apply the parameter group to your database.

4. Reboot your database instance. See Rebooting a DB instance within an Aurora cluster or Rebooting a DB instance (RDS) for more information.

Create Debezium user

The Debezium connector needs a user account to connect to PostgreSQL. This user must have appropriate permissions on all databases where you want Debezium to capture changes.

1. Connect to PostgreSQL as the postgres user and create a new user for the connector:

   CREATE ROLE <username> WITH LOGIN PASSWORD '<password>' VALID UNTIL 'infinity';
   

   Replace <username> and <password> with a username and password for the new user.

2. Grant the user the necessary replication permissions:

   GRANT rds_replication TO <username>;
   

   Replace <username> with the username of the Debezium user.

3. Connect to your database as the postgres user and grant the new user access to one or more schemas in the database:

   GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <username>;
   

   Replace <username> with the username of the Debezium user and <schema> with the schema name.

4. Connect to your database as the postgres user and allow the Debezium user to connect to the database:

   GRANT CONNECT ON DATABASE <database> TO <username>;
   

   Replace <database> with the name of the database and <username> with the username of the Debezium user.

5. Connect to your database as the postgres user and grant the new user usage on the schema:

   GRANT USAGE ON SCHEMA <schema> TO <username>;
   

   Replace <schema> with the schema name and <username> with the username of the Debezium user.

6. For example, your database setup might use three roles:

   • postgres: the administrator setting up RDI.
   • app_migrations: the role that creates application tables.
   • debezium: the user RDI uses to read those tables.

   These names are examples. Use your existing roles.

   The administrator can grant Debezium access to existing tables, so the initial snapshot works. Later, app_migrations creates a new table. Defaults set for postgres do not give Debezium access to that table, so reading it can fail.

   Set the defaults for the role that creates the tables. In this example, use app_migrations as <table_creator> and debezium as the Debezium user.

   Connect as the table creator and grant the Debezium user access to future tables:

   ALTER DEFAULT PRIVILEGES FOR ROLE <table_creator> IN SCHEMA <schema>
       GRANT SELECT ON TABLES TO <username>;
   

   Replace <table_creator> with the role that runs CREATE TABLE, often your migration role. Replace <schema> with the schema name and <username> with the Debezium user. An administrator with permission to change that role's defaults can also run this command.

   Repeat for each table creator and schema. This command covers future tables only. Keep the grants for existing tables.

7. Connect to your database as the postgres user and create a publication containing only the tables RDI should capture. First check their replica identities. Replace the example table names with your selected tables:

   CREATE PUBLICATION rdi_publication
   FOR TABLE public.customers, public.orders;
   

   Set publication.name to rdi_publication and publication.autocreate.mode to disabled under the source's advanced source settings. On Redis Cloud, enter these values under Collector properties. Update the publication's table list when you change the tables RDI captures.

   Alternatively, let RDI manage the publication with publication.autocreate.mode: filtered after granting the required ownership and creation permissions.

   Use FOR ALL TABLES only when you intend to publish every current and future table. An application UPDATE or DELETE on any published table without replica identity can fail, even if RDI does not capture that table. If you already created an all-tables publication, follow Replace an existing all-tables publication.