Striim 3.10.1 documentation

PostgreSQL setup

Striim reads change data from PostgreSQL.

Note

PostgreSQLReader requires logical replication. Amazon RDS for PostgreSQL supports logical replication, but Azure Database for PostgreSQL and Google Cloud SQL for PostgreSQL currently do not.

Before Striim applications can use the PostgreSQLReader adapter, a PostgreSQL administrator with the necessary privileges must do the following. The following examples assume that the name of the PostgreSQL role created for use by PostgreSQLReader is striim and the database containing the tables to be read is mydb.

in Linux and Windows

This will require a reboot, so it should probably be performed during a maintenance window.

  1. In striim/native/wal2json, find the directory for the operating system of your PostgreSQL host, and copy the plugin file it contains to PostgreSQL's lib directory. If there is no directory for your operating system, Contact Striim support.

    Warning

    Known issue (DEV-21129): the plugins in striim/native/wal2json do not support TOAST columns. To read TOAST columns with PostgreSQL Reader, you must compile a plugin from the 9b579240ec8735e762920bdeff8ca02e201bfa03 commit dated 24 Feb. 2020 (see https://github.com/eulerto/wal2json/commits/master) and copy it to PostgreSQL's lib directory.

    In Windows, to find the location of the lib directory, open a command prompt, navigate to PostgreSQL's bin directory, and enter the following command:

    pg_config --pkglibdir

  2. Edit postgressql.conf, set the following options, and save the file. The values for max_replication_slots and max_wal_senders may be higher but there must be one of each available for each instance of PostgreSQLReader. max_wal_senders cannot exceed the value of max_connections.

    wal_level = logical
max_replication_slots = 1
max_wal_senders = 1

  3. Edit pg_hba.conf and add the following record, replacing <IP address> with the Striim server's IP address. If you have a multi-node cluster, add a record for each server that will run PostgreSQLReader. Then save the file and restart PostgreSQL.

    local  replication  striim  <IP address>/0  trust

  4. Restart PostgreSQL.

  5. Enter the following command to create the replication slot:

    pg_recvlogical -d mydb --slot striim_slot --create-slot -P wal2json

    If you plan to use multiple instances of PostgreSQLReader, create a separate slot for each.

  6. Create a role with the REPLICATION attribute for use by PostgreSQLReader and give it select permission on the schema(s) containing the tables to be read. Replace ****** with a strong password and myschema with the name of your schema.

    CREATE ROLE striim WITH LOGIN PASSWORD '******' REPLICATION;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO striim;
in Amazon RDS for PostgreSQL

You must set up replication in the master instance. This will require a reboot, so it should probably be performed during a maintenance window.

Amazon RDS supports logical replication only for PostgreSQL version 9.4.9, higher versions of 9.4, and versions 9.5.4 and higher. Thus PostgreSQLReader can not be used with PostgreSQL 9.4 - 9.4.8 or 9.5 - 9.5.3 on Amazon RDS.

  1. Go to your RDS dashboard, select Parameter groups > Create parameter group, enter posstgres-logical-decoding as the Group name and Description, then click Create.

    createParameterGroup.png

  2. Click postgres-logical-decoding.

    parameterGroups.png

  3. Enter logical_ in the Parameters field to filter the list, set rds.logical_replication to 1, and click Save changes.

    set_replication.png

  4. In the left column, click Databases, then click the name of your database, click Modify, scroll down to Database options, change DB parameter group to postgres-logical-decoding, then scroll down to the bottom and click Continue.

  5. Select Apply immediately > Modify DB instance.

  6. Reboot the instance.

  7. Restart PostgreSQL.

  8. In PSQL, enter the following command to create the replication slot:

    SELECT pg_create_logical_replication_slot('striim_slot', 'wal2json');

  9. Create a role with the REPLICATION attribute for use by PostgreSQLReader and give it select permission on the schema(s) containing the tables to be read. Replace ****** with a strong password and myschema with the name of your schema.

    CREATE ROLE striim WITH LOGIN PASSWORD '******';
GRANT rds_replication TO striim;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO striim;
In this section: