Prerequisites

The following section describes the prerequisites for working with the Google Cloud AlloyDB for PostgreSQL source endpoint.

Drivers and libraries

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.

Prerequisites for capturing changes

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

To do this:

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

   • alloydb.logical_decoding=on
   • max_replication_slots: Every 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 Replicate user to be able to work with replication:

   ALTER USER replicate-user WITH REPLICATION;

   Where replicate-user is the user specified in the endpoint settings.

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.