Striim Migration Service for Google Cloud Documentation

Set up the PostgreSQL source

in Linux or Windows

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

  1. Download the appropriate wal2json plugin for your operating system and copy it to PostgreSQL's lib directory:

    Warning

    Known issue (DEV-21129): these plugins 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 PostgreSQL Reader. 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 SMSGC 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 PostgreSQL Reader, create a separate slot for each.

  6. Create a role with the REPLICATION attribute for use by SMSGC 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 or Amazon Aurora

You must set up replication in the master instance. This will require a reboot, so it should 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;