Amazon RDS for PostgreSQL as a Source

Overview

This page describes the setup process using the Log Server Agent option for one-way mirroring and synchronization when replicating data from a Amazon RDS for PostgreSQL database. For mirroring and synchronization replications using RDS for PostgreSQL as a source, Syniti Replicate offers the Log Server Agent option that uses a Windows service and a Log Server component to query the PostgreSQL log for increased performance when dealing with large amounts of data.

The setup process for a Refresh replication can usually be completed using the Syniti Replicate wizards without additional documentation, as it does not involve access to the PostgreSQL log.

User Permissions

• For Refresh: Grant Select on the source database

• For Mirroring or change data capture (CDC): Syniti recommend to use a superuser permissions. If you prefer not to use the master user account, the account you wish to use must have the rds_superuser role and the rds_replication role. The rds_replication role grants permissions to manage logical slots and to stream data using logical slots.

RDS-Specific Considerations

RDS PostgreSQL requires configuring logical replication for Mirroring (CDC). Please ensure the following:

• rds_superuser Role: In RDS, you don't have full superuser access. Instead, users with the rds_superuser role can perform administrative tasks. If creating replication slots or logical replication is involved, use this role.

• Replication Slots: Make sure that the database parameter group for the RDS PostgreSQL cluster has rds.logical_replication enabled.

-- Grant required permissions to the Mirroring (CDC) user
CREATE USER cdc_user WITH PASSWORD 'your_password';
GRANT CONNECT ON DATABASE your_database TO cdc_user;
GRANT USAGE ON SCHEMA your_schema TO cdc_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA your_schema TO cdc_user;

-- Grant replication role if logical replication is needed
ALTER USER cdc_user WITH REPLICATION;

AWS IAM Permissions

In addition to database permissions, appropriate AWS Identity and Access Management (IAM) permissions might be necessary if you are integrating with other AWS services (e.g., AWS Database Migration Service for CDC). The following IAM policies may be relevant:

• RDS Full Access: Grants full access to RDS resources, allowing management of the RDS PostgreSQL cluster.

• DMS Full Access: If using AWS Data Migration Services (DMS) for data migration, ensure the necessary IAM policies for AWS DMS service roles are in place.

Amazon RDS for PostgreSQL System Settings

Syniti Replicate supports Amazon RDS PostgreSQL versions 11 and above as a source database for mirroring. It requires every connection to PostgreSQL from Syniti Replicate use a dedicated replication slot such that the PostgreSQL replication slot feature is used effectively. A single slot represents an ordered stream of changes that occur on a specific database on the origin server. For more information, refer to the PostgreSQL Documentation website.

Note that if you enable a replication slot from Syniti Replicate without using it, the slot will consume resources and prevent the database cleanup functionality to purge old records from the transaction log, because they do not appear as consumed by an existing slot. Therefore, it is important to remove replication slots from the database by removing the Syniti Replicate connection if it is not used in replication.

Enabling Amazon RDS for PostgreSQL for Mirroring

Prerequisites

• Set up the Amazon RDS for PostgreSQL database as a source for transactional replications (CDC) in Syniti Replicate using the detailed instructions provided on Working with AWS-managed PostgreSQL databases as a DMS source.

• Ensure that you complete the steps in the following sections:

  • Prerequisites for Using a PostgreSQL Database as a Source for AWS DMS

  • Setting Up an Amazon RDS PostgreSQL DB Instance as a Source (Using CDC with an RDS for PostgreSQL DB Instance section)

Once you have reviewed and completed the above instructions, complete the below steps.

Setting up PostgreSQL in Amazon RDS for Logical Replication

Note

The below screen shots are for reference only. The actual AWS UI might differ.

1. Log in to your AWS account and access the Amazon RDS database instance that you have created.

   
   

2. Access the Details > Configuration section of your instance and take note of the Parameter Group used for your RDBMS.

   
   
   Generally, when an instance is initially created, RDS will configure a set of parameters by assigning a Default Parameter Group to the instance. A default parameter group is a set of database parameters that is standard and is the same for all instances of that database type. To customize your parameter settings (as required to enable transactional replications in Syniti Replicate), create a Custom Parameter Group and assign it to the instance.

3. Select Parameter Groups from the side panel. A list of parameter groups is displayed.

4. Select Create Parameter Group to create a custom group. In this process, make sure you indicate the type of database (PostgreSQL) and the version that your instance is running to select the correct type of parameter group.

   
   
   Once the group is created, modify its properties to allow logical replication.

5. In the Parameter Groups view, select the newly created group.

6. Search for the logical_replication property and set its value to 1.

7. Search for the wal_sender_timeout property and set its value to 0.

8. Access Instances > Details and select Modify.

9. Access the Database Options section and select the Custom Parameter Group that you have created, and the select Continue. RDS will then let you save these settings. You can select the option to save the changes at the next planned maintenance time or immediately. Even if you opt for immediate changes, the database status will be set as unavailable for some time.

10. Once the database is available, select Instance Actions and select Reboot. These settings will take effect after the database has been rebooted.

    
    

PostgreSQL is ready to be used from Syniti Replicate and you can start setting up the transactional replication from the Management Center.

Create a Source Connection with Amazon RDS PostgreSQL

Open the Management Center, under the source connections create a new connection

1. Select PostgreSQL as the database from the list.

2. Select PostgreSQL .NET Driver as a Provider.

3. For lower versions of Syniti Replicate 10.7.0.21 locate the .NET Assembly Npgsql.dll in the installation folder for the PostgreSQL .NET Provider. For Syniti Replicate 10.7.0.21 or higher the .NET assembly is included with the installation package.

4. Add the connection properties.

5. Complete the Wizard.

Enable Transactional Replication Wizard

For transactional replications (mirroring and synchronization), use the Enable Transactional Replication wizard after setting up a source connection. The following fields require specific information for PostgreSQL.

Log Type Screen

Select the Log Server Agent option.

Log Settings

Replication Slot: Use Existing Slot

Replication slots are channels that you open on the database to allow Syniti Replicate to receive data changes from the log. While the default is to use a new slot specifically for a Syniti Replicate connection, it is possible to reuse an existing slot if it is not consumed by any other connection. A replication slot must be uniquely assigned to a single Syniti Replicate connection.

Replication Slot: Add New Slot

Syniti Replicate needs to define a "replication_slot" for every connection that it uses to replicate from Amazon RDS for PostgreSQL. This option allows you to set up a new slot from Syniti Replicate. However, the new slot will be added only if the maximum number of slots has not been exceeded. This value is set in the postgresql.conf file. Be aware that if slots are created then left unused, they still consume resources, so you should always manage replication slots carefully.

Plugin Type

Ensure that the test_decoding option is selected from the Menu list.

Limitations in Mirroring Mode

• Transactional replication (Mirroring) using Amazon RDS for PostgreSQL as source is supported only via Log-based.

• Triggers-based replication is not supported.