Replicating PostgreSQL data

Restrictions

• 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.

Supported versions

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

Configuring the PostgreSQL database for replication

To set up PostgreSQL:

1. Open the postgresql.conf file in text editor. The location of the file can vary depending on your operating system and PostgreSQL installation, but it is typically located in the data directory of your PostgreSQL installation.

2. Search for the wal_level parameter in the file.

3. Change the value of the wal_level parameter from its current setting to logical. If the wal_level parameter does not exist in the file, you can add it to the end of the file as a new line.

4. Set the max_replication_slots and max_wal_senders parameters to the number of concurrent Data Replication assets you want to run.

5. Save the changes to the postgresql.conf file and exit the text editor.

6. Restart the PostgreSQL server for the changes to take effect. On most systems, you can do this by running the following command. This command will vary depending on the operating system and how PostgreSQL is installed.

   sudo systemctl restart postgresql
   

7. Apply the changes to the database.

8. Grant access to connections from IBM Cloud.

9. Grant roles to the database username. Ask your database administrator to grant the roles superuser and replication to the username you use in the connection for Data Replication by running:

   ALTER USER username WITH superuser;
   ALTER USER username WITH replication;
   

Connecting to PostgreSQL in a project:

To create a connection in a project in Cloud Pak for Data as a Service:

1. From the main navigation pane, click Projects > View all projects.
2. Select an existing project. If you do not have a project, create a new project.
3. On the Assets tab of your project, click New asset and select Connection.
4. Select PostgreSQL connection.
   1. Enter a name.
   2. Enter the Database, Hostname, and Port.
5. Under Credentials, enter the username and password. Use the username that is provided to you by your database administrator.
6. Select Port is SSL-enabled.
7. Make sure that Validate the SSL certificate is not selected.
8. Click Test connection. If this is not successful, check that the credentials and database were entered correctly and test again.
9. Click Create.

Next step

Starting Data Replication

Parent topic: Supported Data Replication connections