Prerequisites

The following section describes the prerequisites for working with the Qlik Replicate PostgreSQL source endpoint.

Client side

Replicate for Windows

Install PostgreSQL ODBC Driver psqlodbc_13_xx_xxxx-x64 on the Replicate machine.

Replicate for Linux

Download PostgreSQL 13 ODBC driver, and client programs and libraries.

• Example PostgreSQL ODBC file name: postgresql13-odbc-13.02.0000-1PGDG.rhel8.x86_64.rpm
• PostgreSQL client programs and libraries file name: postgresql13-13.8-1PGDG.rhel<version>.x86_64.rpm

Then, open a shell prompt and do the following:

1. Stop the Replicate service and optionally confirm that it has stopped as described in Replicate server procedures.

2. Install PostgreSQL 13 client programs and libraries.

3. Install the ODBC driver for PostgreSQL 13.

4. Change the working directory to <Replicate-Install-Dir>/bin.

5. Copy the driver location to the site_arep_login.sh file:

   echo "export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:/usr/lib:/usr/lib64:/usr/pgsql-13/lib" >> site_arep_login.sh

   This will add the driver to "LD_LIBRARY_PATH" and update the driver location in the site_arep_login.sh file.

6. Optionally, confirm that the driver location was copied:

   cat site_arep_login.sh

7. Makes sure that the /etc/odbcinst.ini file contains an entry for PostgreSQL, as in the following example:

   [PostgreSQL]

   Description = PostgreSQL ODBC driver

   Driver = /usr/pgsql-13/lib/psqlodbc.so

   Setup = /usr/pgsql-13/lib/psqlodbcw.so

   UsageCount = 1

8. Start the Replicate service and optionally confirm that it has started as described in Replicate server procedures.

Server side

• The IP address of the Replicate machine must be added to the pg_hba.conf configuration file with the "replication" keyword in the database field.

  Example:

  host replication all 176.123.1.212/32 trust

• Make sure that the test_decoding output plugin (found in the postgresql12-contrib package) is installed.

• The user specified in the connection settings must be granted read-write permissions to the source database to allow the creation of "slots" on the database. The slots are required in order to read the changes.

• The following parameters and values must be set in the postgresql.conf configuration file.

  wal_level = logical
  max_replication_slots = number of replication slots

  Where number of replication slots must be greater than one and should be set according to the number of tasks that you want to run. For example, to run five tasks you need to set max_replication_slots = 5. Slots open automatically as soon as a task starts and remain open, even when task is no longer running. Note that open slots need to be manually deleted.

  max_wal_senders = number of concurrent tasks

  Where number of concurrent tasks must be greater than one and should be set according to the number of tasks that you are running concurrently. For example, to run three tasks concurrently you need to set max_wal_senders = 3.

• The wal_sender_timeout parameter terminates connections that are inactive longer than the specified number of milliseconds. The default timeout is 60 seconds. To disable the timeout mechanism (optional), set this parameter to zero.

  Information note

  By default, the value of the wal_sender_timeout parameter is interpreted by the server as milliseconds. To explicitly specify seconds, append an "s" to the value as in the following example:

  wal_sender_timeout=60s

For more information on the configuration parameters, see the Replication section under Server Configuration in the PostgreSQL online help.

Required permissions

The user specified in the endpoint connection settings must be granted the following permissions on the PostgreSQL database:

• For Full Load tasks: Standard SELECT

• For CDC tasks: Superuser

  See also Using an account without the "superuser" role.