AWS RDS PostgreSQL Set Up

AWS RDS PostgreSQL stands out as one of the most widely used production databases. Its global adoption and everyday usage have prompted Amazon to make it exceptionally user-friendly. New users can set up an RDS PostgreSQL instance from scratch in just a few minutes. Additionally, if you already have an existing instance, it’s straightforward to extract the necessary configuration for Streamkap streaming.

In this section, we will explore various methods to set up and configure AWS RDS PostgreSQL to ensure compatibility with Streamkap.

‍

Setting up a New AWS RDS PostgreSQL Instance from Scratch

‍

Note: To set up a new AWS RDS PostgreSQL instance, you need IAM permissions for creating and  managing RDS instances, parameter groups, subnet groups, networking, security, monitoring, and optional encryption and tagging.

If you are restricted by permissions, please ask your AWS administrator to grant you core RDS and networking permissions.

‍

Step 1: Access the AWS Management Console

• Log in to your AWS management Console and type “RDS” on the search bar. 
• Click on “RDS” as shown below

Step 2: Create a new RDS PostgreSQL Instance

• Step 1 will take you to the RDS dashboard. From there, click on either "Databases" in the top left corner or "DB Instances" under the Resources section as depicted below.

• Select the region where you want your RDS instance to be hosted, then click "Create database" as shown in the following screenshot

• Click “Standard create” radio button in the Choose a database creation method section
• Click “PostgreSQL” radio button in the Engine option section
• Scroll down and choose “PostgreSQL 16.3-R3” from Engine version drop down menu as shown below

• Under the Templates section choose between “Production”, “Dev/Test”, “Free tier” based on your need.
• If you're deploying sub-second latency streaming for the first time, we recommend selecting the "Free tier" as shown in the following screenshot to familiarize yourself with the end-to-end process without incurring cloud costs.

• In the Settings section plug in a unique value for some text
  • DB Instance identifier 
  • Master username
• Click “Self managed” radio button on the Credentials management section and fill in values for Master password some text
  • Confirm master password

• In the Connectivity section, click “Yes” for Public access as illustrated below 

• In the Additional configuration section, plug in an Initial database name as shown below

• Scroll to the bottom and click “Create database”. Wait until your DB instance becomes available as shown below

‍

Step 3: Configuring Your PostgreSQL Instance for Streamkap Compatibility

By default, an AWS RDS PostgreSQL instance is not compatible with Change Data Capture (CDC). To enable sub-second latency streaming, minor adjustments must be made to the database to make it CDC-compatible.

In this section of the guide, we will

1. Create a new parameter group 
2. Modify rds.logical_replication = 1
3. Modify wal_sender_timeout = 0
4. Attach the new parameter group to our PostgreSQL Instance
5. Restart the PostgreSQL Instance to apply changes

• On your RDS dashboard click on “Parameter groups” on the left side and then click on “Create parameter groups” as shown in the following screenshot

• Plug in a “Parameter group name”, “Description”, choose “PostgreSQL” as engine type, choose “postgres16” as parameter group family and choose “DB Parameter Group” as Type    and click “Create” as shown below.

Once your parameter group is created, click on your parameter group and click on the “Edit” button on the right-side corner of the screen.

• On the search barsome text
  • Type “rds.logical_replication” and add “1” as value as shown below.
  • Type “wal_sender_timeout” and add “0” as value as shown below.

• Click “Save Changes”.

We have successfully created a parameter group configured for Change Data Capture (CDC). The next step is to attach this parameter group to the PostgreSQL instance to enable CDC compatibility for the database.

• Open your PostgreSQL instance from the RDS dashboard page and click “Modify” on the top right corner as illustrated below

• Scroll to the “Additional configuration” section. Under “DB Parameter Group”, choose the newly created parameter group from the list. Scroll to the bottom of the screen and click “Continue”.

• On the Modify DB instance page ensure Change Data Capture compatible parameter group shows as new value, under “Schedule modification”, click “Apply immediately” radio button and then click on “Modify DB instance”.

‍

Step 4: Test Your PostgreSQL Instance for Change Data Capture (CDC) and Streamkap Compatibility

It’s crucial to verify that changes made to the PostgreSQL instance are properly reflected within the database. To do this, connect to your RDS PostgreSQL instance using tools like DBeaver and run the following commands to ensure CDC functionality is working as expected.

Note: If you encounter a timeout error while accessing your AWS PostgreSQL instance from your local machine, it’s possible that the security group is not allowing inbound traffic from your local machine. To resolve this, you’ll need to modify the inbound rules of your security group to permit access from your local machine to your cloud PostgreSQL instance.

‍

‍

Configuring an Existing RDS PostgreSQL Instance for Streamkap Compatibility

‍

If you already have an existing AWS RDS PostgreSQL instance, it may or may not be compatible with change data capture (CDC). To determine if it is compatible, we will perform two tests. Both tests must pass in order to confirm that the instance is CDC-compatible.

To perform these tests, connect to your RDS PostgreSQL instance using tools like DBeaver and run the following commands to ensure CDC functionality is working as expected.

• Show wal_level;some text
  • We expect “logical” as output
• Show wal_sender_timeoutsome text
  • We expect “0” as output

Both tests must return the expected values (wal_level = logical and wal_sender_timeout = 0) for the RDS instance to be CDC-compatible. If either test fails, adjustments to the RDS parameter group may be required.

If these tests do not return the expected values, you will need to modify the existing custom RDS parameter group or create a new parameter group with the necessary changes to enable Change Data Capture (CDC) compatibility. Specifically, you'll need to configure the following parameters.

If you already have an existing custom RDS parameter group skip to step 2.

‍

Step 1: Create a Customer RDS Parameter Group

• On your RDS dashboard click on “Parameter groups” on the left side and then click on “Create parameter groups” as shown in the following screenshot

• Plug in a “Parameter group name”, “Description”, choose “PostgreSQL” as engine type, choose “{your Postgres engine version}” as parameter group family and choose “DB Parameter Group” as type and click “Create” as show below.

• Once your parameter group is created, click on your parameter group and click on the “Edit” button on the right-side corner of the screen.
• On the search barsome text
  • Type “rds.logical_replication” and add “1” as value as shown below.
  •  Type “wal_sender_timeout” and add “0” as value as shown below.

• Click “Save Changes”.

We have successfully created a parameter group configured for Change Data Capture (CDC). The next step is to attach this parameter group to your existing PostgreSQL instance to enable CDC compatibility for the database.Step 2: Attaching Parameter Group to RDS PostgreSQL Instance

• Open your PostgreSQL instance from the RDS dashboard page and click “Modify” on the top right corner as illustrated below   

• Scroll to the “Additional configuration” section. Under “DB Parameter Group” choose the newly created parameter group from the list. Scroll to the bottom of the screen and click “Continue”.

• On the Modify DB instance page ensure Change Data Capture compatible parameter group shows as new value, under “Schedule modification”, click “Apply immediately” radio button and then click on “Modify DB instance”.

• After the modification of your RDS PostgreSQL instance is complete, run the following commands again to ensure CDC functionality is working as expected.

‍

‍

‍