Replicating Amazon RDS for PostgreSQL data
You can replicate data from Amazon RDS for PostgreSQL to other databases with Data Replication.
To set up replication for PostgreSQL, first configure your Amazon RDS for PostgreSQL service, and then add a connection to it in a project.
- You can use this connection only as a source for Data Replication. You cannot use this connection as a target connection.
- You can replicate only some PostgreSQL data types. See Supported PostgreSQL data types.
PostgreSQL database versions 9.4, 9.5, 9.6, 10, 11 and 12
Configuring the PostgreSQL database for replication
To set up PostgreSQL in your Amazon account:
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';Note: Do not alter the replica identity for schemas and tables you are replicating. Data Replication will stop if you modify the replica identity to be less than full.
Create a DB parameter group. Confirm that you have an existing parameter group named default.postgresversion. Create a new parameter group, selecting parameter group family postgresversion, where
versionmatches the existing parameter group version.
- 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.
- Associate a DB parameter group with your database instance.
- Modify parameters in the DB parameter group
- Set rds.logical_replication to 1. This enables logical replication.
- Set max_replication_slots and max_wal_sender to the number of concurrent Data Replication assets you want to run.
- Save the changes.
- Apply the changes. Open the RDS console, in the navigation pane select Databases, select your database instance. From the Actions menu, select Reboot.
- Grant access to connections from IBM Cloud. Many Amazon RDS for PostgreSQL database instances have Public access but do not allow connections from IBM Cloud. To grant access for IBM Cloud to connect to the database:
- Open the RDS console. In the navigation pane select Databases, select your database instance, and click Connectivity & security.
- Under heading VPC security groups, click the link for the active security group.
- Scroll down and click Edit inbound rules.
- Click Add rule. Select type PostgreSQL and source Anywhere.
- Click Save rules. This will also modify the Outbound rules.
- Grant roles to the database username. Ask your database administrator to grant the roles
rds_replicationto the username you use in the connection for Data Replication.
Connecting to Amazon RDS for PostgreSQL in a project:
To create a connection in a project in Cloud Pak for Data as a Service:
- From the main navigation pane, click Projects > View all projects.
- Select an existing project. If you do not have a project, create a new project.
- On the Assets tab of your project, click New asset and select Connection.
- Select Amazon RDS for PostgreSQL connection.
- Enter a name.
- 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 that is listed on the Connectivity & security tab.
- Under Credentials, enter the username and password. Use the username that is provided to you by your database administrator.
- Select Port is SSL-enabled.
- Make sure that Validate the SSL certificate is not selected.
- Click Test connection. If this is not successful, check that the credentials and database were entered correctly and test again.
- Click Create.
- Amazon RDS for PostgreSQL
- Creating an Amazon RDS DB Instance
- Connecting to a DB Instance Running the PostgreSQL Database Engine
Parent topic: Supported Data Replication connections