Amazon RDS/Aurora PostgreSQL Setup Guide

Introduction

Amazon Relational Database Service (RDS) and Amazon Aurora are fully managed database services offered by Amazon Web Services (AWS). They provide a scalable and reliable infrastructure for running PostgreSQL databases in the cloud.
This document will guide you through the process of configuring PostgreSQL on Amazon RDS or Aurora.

Prerequisites

• Make sure you're running PostgreSQL 10 or higher.
• Server and Database permissions are required to change server configuration (depending on the PostgreSQL cloud/on-prem provider).

Notes on PostgreSQL Server Architecture

• DDL changes are not supported by Logical Decoding. Rivery is unable to report DDL change events to the database as a result of this.
• Only the primary servers in the cluster can use logical decoding replication slots. It won't work with warm or hot standby replicas. Log fetching will stop and the River will fail if the primary server fails or becomes detached.
• Only UTF-8 character encoding databases are supported.

PostgreSQL setup for Amazon RDS / Aurora

Prerequisite:
Make sure to enable logical decoding for a PostgreSQL RDS instance following the PostgreSQL documentation.

AWS RDS PostgreSQL v10+ comes pre-configured with logical replication, so all you have to do is enable it as follows:

1. To get started, go to the AWS RDS console.

2. Click Services in the upper left corner, then RDS.
   

3. Under Parameter Groups, select Create Parameter Group.

4. Select the parameter group family that corresponds to your PostgreSQL server version.

5. On Aurora cluster, choose DB cluster parameter group as the parameter group type. On RDS, choose DB paramter group.

6. Give it a name (for example, Postgresql-rivery-cdc), and click Create.

7. Enter the parameter group you just created. We can now change and enable some configurations in the parameter group associated with the current server.

The following parameters must be modified:

rds.logical_replication = 1
rds.log_retention_period = 10080 # 7 days
wal_sender_timeout = 0

7. Click Save Changes.

8. Navigate to the Databases section and select the CDC Postgres server you want, then click Modify.

9. In the DB parameter group section, select the parameter group created in step 3.
   

10. Select Continue, then Apply Immediately under Modification Scheduling.
    

11. Validate - Execute the following query as the DB master user to ensure that the wal level parameter is set to logical:

SHOW wal_level

Configuring the RDS Superuser Role in Amazon RDS

The 'rds_superuser' is a special user in Amazon RDS for PostgreSQL that is used for maintenance operations on a database instance. The 'rds_superuser' role often used for administrative tasks such as creating and managing databases, users, and tables.

In this section, we will outline the steps to configure the rds_superuser role in Amazon RDS for PostgreSQL.

Prerequisites

You will require the following before you begin:

• Access to an Amazon Aurora PostgreSQL database
• Access to a client that supports the PostgreSQL protocol, such as the 'psql' command-line tool.

Configuration Process

1. Set the current session's role to rds_superuser. This role grants the superuser privileges required to perform administrative tasks on the RDS instance.

To set the role, connect to the RDS instance using an administrative account and run the following command:

SET ROLE rds_superuser;

2. Create a new role named replication_role with the REPLICATION LOGIN option. This role will allow the user to connect to the RDS instance and replicate data.

To create the role, run the following command:

CREATE ROLE replication_role WITH REPLICATION LOGIN;

3. After creating the role, we need to create a user account named Rivery with a password and the REPLICATION option. This user will connect to the RDS instance and perform replication tasks.

To create the user, run the following command:

CREATE USER Rivery WITH PASSWORD 'my-secret-pw' REPLICATION;

4. Finally, we will grant the replication_role to the Rivery user. This grants the necessary permissions for the user to perform replication tasks.

To grant the role, run the following command:

GRANT replication_role TO Rivery;

Please Note:
In Amazon Aurora PostgreSQL, the 'rds_superuser' role is a strong role that needs to be handled with caution.

Connect to Rivery

Follow the PostgreSQL Connection documentation to connect to the Rivery console.