PostgreSQL Log-Based Configuration
  • 3 Minutes to read
  • Dark
    Light
  • PDF

PostgreSQL Log-Based Configuration

  • Dark
    Light
  • PDF

Prerequisites

  • Make sure you're running PostgreSQL 10 or higher.
  • Pgoutput plugin.
  • Server and Database permissions are required to change server configuration (depending on the PostgreSQL cloud/on-prem provider).

Notes on PostgreSQL Server Architecture

  • DDL changes are not supported by Logical Decoding. Rivery is unable to report DDL change events to the database as a result of this.
  • Only the primary servers in the cluster can use logical decoding replication slots. It won't work with warm or hot standby replicas. Log fetching will stop and the River will fail if the primary server fails or becomes detached.
  • Only UTF-8 character encoding databases are supported.


Configuring PostgreSQL on Amazon RDS

AWS RDS PostgreSQL v10+ comes pre-configured with pgoutput and logical replication, so all you have to do is enable them as follows:

  1. To get started, go to the AWS RDS console.

  2. Click Services in the upper left corner, then RDS.
    image.png

  3. Under Parameter Groups, select Create Parameter Group.

  4. Select the parameter group family that corresponds to your PostgreSQL server version.

  5. Give it a name (for example, Postgresql-rivery-cdc), and click Create.

  6. Enter the parameter group you just created. We can now change and enable some configurations in the parameter group associated with the current server.
    The following parameters must be modified:
    rds.logical_replication = 1

  7. Execute the following query as the DB master user to ensure that the wal level parameter is set to logical: 'show wal_level'.

  8. Navigate to the Databases section and select the CDC Postgres server you want, then click Modify.

  9. In the DB parameter group section, select the parameter group created in step 3.
    image.png

  10. Select Continue, then Apply Immediately under Modification Scheduling.
    image.png


Permissions for Replication Slots Configuration

The Replication role must be granted permission to the Rivery user in order to enable the replication slot.

The REPLICATION role must be granted permission to the Rivery user in order for the replication slot to be enabled.
If the server does not already have a REPLICATION role, you can create one with the following SQL command ( should be replaced with a desired role name):

  • CREATE ROLE <replication_role_name> REPLICATION LOGIN;
    
  • After creating the role, create a new Rivery login (if one does not already exist for Rivery's Log-Based extraction):

       CREATE USER rivery WITH <replication_role_name>
    
  • If the user for Rivery already exists, grant the user the new role:

     ALTER USER rivery WITH <replication_role_name>;
    


Configure Replica Identity to Each Non-keyed or Unique Table

It's necessary to set the replication identity of the tables in order to ensure that all of the table's old values are retrieved from the Write-Ahead Log (WAL) in Postgresql during DELETE events. There are four different types of REPLICA IDENTITY in Postgres that control how events are written to the logical replication stream:

  • DEFAULT - The previous values for a table's primary key columns are only present in UPDATE and DELETE events; in the case of UPDATE, only the primary columns with changed values are present.
  • NOTHING - The UPDATE and DELETE events will not include any information about the previous value of any table column.
  • FULL - The previous values of all the table's columns will be stored in the UPDATE and DELETE events.
  • INDEX - UPDATE and DELETE events will contain the previous values of the columns contained in the index definition named index name; however, only the indexed columns with changed values will be present in the case of UPDATE.

Note:
Tables must be set to REPLICA IDENTITY FULL in cases of DELETE and UPDATE events. Any non-key column will be written to the WAL as a NULL value if the DELETE event is not performed. Run the following query to set the REPLICA IDENTITY to FULL:

ALTER TABLE <schema>.<table> REPLICA IDENTITY FULL;


Configuring Log Base in an On-Premise Database

Run the following command in your server to set up PostgreSQL Log Based in an on-premise database:

# REPLICATION 

wal_level = logical
max_wal_senders = 4
max_replication_slots = 4


Connect to Rivery

Follow the PostgreSQL Connection documentation to connect to the Rivery console.


Was this article helpful?