Skip to main content

Amazon Aurora PostgreSQL Configuration

Welcome to your detailed guide on configuring PostgreSQL as a source database for Boltic Pipes! This document will take you through each crucial step required to integrate PostgreSQL seamlessly with Boltic’s powerful data pipeline framework.

Boltic Pipes excels in synchronizing and transferring data across various platforms. To set up PostgreSQL as a source, you’ll need to fine-tune several settings to ensure optimal operation and data integrity. This guide will provide you with the essential configurations and best practices to make your PostgreSQL setup both robust and efficient.

Suggested User Creation​

We recommend creating a user named boltic_cdc_user for handling replication and login tasks. Follow the steps below for setting up the user and configuring necessary permissions and privileges.

Here is the complete documentation in Markdown format:

Configuring Amazon Aurora PostgreSQL for Logical Replication​

This guide provides step-by-step instructions for enabling logical replication in Amazon Aurora PostgreSQL for Change Data Capture (CDC) in Boltic Pipes.

1. Modify Aurora Parameter Groups​

  1. Log in to the Amazon RDS console.

  2. In Left Navigation Panel , Go to Parameter Groups Section .

  3. Select the respective Parameter Group attached to your RDS and click on actions to edit parameter groups .

  4. Modify Parameters.

    Modify Aurora Parameter Groups

To enable logical replication, modify the following parameters in your Aurora parameter group:

ParameterValueDescription
rds.logical_replication1Enables write-ahead logging (WAL) at the logical level.
max_replication_slots10Defines the maximum number of replication slots.
wal_sender_timeout0Prevents PostgreSQL from terminating inactive replication connections.

Important Notes​

  • The rds.logical_replication parameter is static and requires a DB instance reboot for changes to take effect.
  • When the DB instance reboots, the wal_level parameter is automatically set to logical.

Verify Configuration​

To confirm that the wal_sender_timeout value is correctly set, execute the following query:

SHOW wal_sender_timeout;

2. Configure Database User and Permissions​

2.1 Create a Database User​

Run the following SQL command to create a new user for logical replication. Replace <database_username> and <password> with actual values. Log in to the database as a user with the rds_superuser role and run the following command:

CREATE USER <database_username> WITH LOGIN PASSWORD '<password>';

Example:

CREATE USER boltic_cdc_user WITH LOGIN PASSWORD 'your_secure_password';

2.2 Grant Necessary Privileges​

Replace <database_name> and <schema_name> with your actual database and schema names.

Create a Publication for Logical Replication​

CREATE PUBLICATION bolt_pipe_publication FOR ALL TABLES WITH (publish_via_partition_root=true);

This publication captures changes for all existing and future tables in the database.

Grant Permissions to the User​

GRANT CONNECT ON DATABASE <database_name> TO <database_username>;
GRANT CREATE ON DATABASE <database_name> TO <database_username>;
GRANT USAGE ON SCHEMA <schema_name> TO <database_username>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <database_username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <database_username>;
GRANT rds_replication TO <database_username>;

3. Create a Heartbeat Table​

The heartbeat table tracks changes and maintains replication activity.

CREATE TABLE <database_name>.<schema_name>.__boltic_heartbeat();

Grant Required Privileges on the Heartbeat Table​

GRANT SELECT, INSERT, UPDATE, DELETE ON <database_name>.<schema_name>.__boltic_heartbeat TO <database_username>;

Add Tracking Columns to the Heartbeat Table​

ALTER TABLE <database_name>.<schema_name>.__boltic_heartbeat
ADD COLUMN IF NOT EXISTS id SERIAL PRIMARY KEY,
ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

By following these steps, your Amazon Aurora PostgreSQL instance will be configured for logical replication, enabling seamless CDC integration with Boltic Pipes.