0 / 0
Replicating Amazon RDS for PostgreSQL data
Replicating Amazon RDS for PostgreSQL data

Replicating Amazon RDS for PostgreSQL data

You can replicate data from Amazon RDS for PostgreSQL to other databases using Data Replication.

Amazon RDS for PostgreSQL is a PostgreSQL relational database that runs on the Amazon Relational Database Service (RDS).

Supported versions

PostgreSQL database versions 9.4, 9.5, 9.6, 10, 11 and 12

Steps to configure Amazon RDS for PostgreSQL as a source for Data Replication

1. Configure the PostgreSQL database for replication

  1. Check that your Amazon RDS for PostgreSQL database instance has the proper privileges for replication. Using a database client, run the following query.
     SELECT setting FROM pg_settings WHERE NAME='rds.logical_replication';
    
  2. If your PostgreSQL database instance has the proper privileges, the setting column will have value on, and you can skip to the next section. If the value is not on, modify the database parameters to enable replication.

  3. Create a DB parameter group. Observe that you have an existing parameter group named default.postgresversion. Create a new parameter group, selecting parameter group family postgresversion, where version matches the existing parameter group version.

  4. Associate a DB parameter group with your database instance.
  5. Modify parameters in the DB parameter group
    1. Set rds.logical_replication to 1. This enables logical replication.
    2. Set max_replication_slots and max_wal_sender to the number of concurrent Data Replication assets you wish to run.
    3. Save the changes.
  6. Apply the changes. Open the RDS console, in the navigation pane select Databases, select your database instance, and from the Actions menu select Reboot.

2. Configure security to allow connections from IBM Cloud

Many Amazon RDS for PostgreSQL database instances have Public access but do not allow connections from IBM Cloud. In this step, you will grant access for IBM Cloud to connect to the database.

  1. Open the RDS console. In the navigation pane select Databases, select your database instance, and click the Connectivity & security link.
  2. Under heading VPC security groups, click the link for the active security group.
  3. Scroll down and click Edit inbound rules.
  4. Click Add rule. Select Type PostgreSQL, Source Anywhere.
  5. Click Save rules. This will also modify the Outbound rules accordingly.

3. Grant roles to the database username

Ask your database administrator to grant the roles rds_superuser and rds_replication to the username you will use in the connection for data replication.

4. Create a connection in a project

  1. Go to the site to log in with your personal credentials:

  2. Click on an existing project. If you do not yet have a project, see Configuring a project.

  3. Click New asset and select Connection.
  4. Select Amazon RDS for PostgreSQL connection. Fill in a name and optionally a description. To find the values for the Database, Hostname and Port, sign in to the AWS Console, navigate to Databases and select your database instance. The hostname is the Endpoint value listed on the Connectivity & security tab.
    Under Credentials, fill in the username and password. Use the username described in Step 3.

    Checkmark Port is SSL-enabled.

    Uncheck Validate the SSL certificate.
  5. Click Test connection. If this is not successful, check that the credentials and database were entered correctly and test again.
  6. Click Create.

Next step: Add data replication assets

Amazon RDS for PostgreSQL setup

For setup instructions, see these topics:

Restriction

You can use this connection only as a source for Data Replication. You cannot use this connection as a target connection.

Learn more

Parent topic: Supported Data Replication connections