Prerequisites

The following section lists the prerequisites for working with Qlik Replicate and a Google Cloud SQL for PostgreSQL source.

Replicate for Windows

PostgreSQL ODBC Driver psqlodbc_13_xx_xxxx-x64 must be installed 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.

CDC prerequisites

To be able to capture changes from the database, you need to set up logical replication.

To set up logical replication:

1. On Google Cloud platform, set the following flags in the PostgreSQL settings:

   • cloudsql.logical_decoding=on
   • max_replication_slots: Every connection (that is, task) creates a new slot; this parameter configures the amount of simultaneous connections that can be established to the database. If you encounter performance issues, increasing this value might help.
   • max_wal_senders: The number of tasks that can run in parallel. If you encounter performance issues, increasing this value might help.

   For more information on these parameters, see https://www.postgresql.org/docs/current/runtime-config-replication.html

2. On the database, alter the user specified in the endpoint settings (username) to support replication:

   ALTER USER username WITH REPLICATION;

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.