Requirements

• PostgreSQL 10 or above
• Logical replication level set in postgresql.conf
• Changebase user with read access for all tracked tables
• Changebase metadata table (defaults to changebase_metadata) if tracking metadata
• Connection access from Changebase to the database (in pg_hba.conf and VPC if applicable)

Configuration

Set Logical Replicaiton

For Self Hosted / Bare Metal / VM / Docker etc....

To enable Changebase to stream changes from the database, the wal_level setting in the postgresql.conf file needs to be set to logical and at minimum, the max_replication_slots and max_wal_senders needs to greater than 1. It's recommended to set the max_replication_slots and max_wal_senders to 5.

# PGDATA/postgresql.conf

wal_level=logical
max_replication_slots=5
max_wal_senders=5

For AWS RDS or Aurora

• Go to the Configuration tab for you DB cluster.
• Find your cluster parameter group. You will either edit the parameters for this group or create a copy of this parameter group to edit. If you create a copy you will need to change your cluster's parameter group before restarting.
• Within the parameter group page, search for rds.logical_replication. Select this row and click on the Edit parameters button. Set this value to 1.
• Wait for a maintenance window to automatically restart the instance or restart it manually.

For Azure Databasae

Use the Azure CLI to do the following:

az postgres server configuration set --resource-group group --server-name server --name azure.replication_support --value logical
az postgres server restart --resource-group group --name server

Create the Changebase Role

Log into the database as and administrator and create the Changebase role:

CREATE ROLE changebase
WITH REPLICATION LOGIN PASSWORD '<changebase_password>';

createuser --replication --login --pwprompt changebase

Create the Changebase metadata table

CREATE TABLE changebase_metadata ( version int PRIMARY KEY, data jsonb default '{}' );

psql <dbname> -c "CREATE TABLE changebase_metadata ( version int PRIMARY KEY, data jsonb default '{}' );"

The metadata table is used to capture additional information about any events that occur within the transaction.

For example, to capture information about the user performing the action, insert the following during the transaction:

INSERT INTO changebase_metadata ( version, data )
VALUES ( 1, '{"user":{"id":1,"name":"Moses"}}' )
ON CONFLICT ( version ) DO UPDATE SET version = 1, data = '{"user":{"id":1,"name":"Moses"}}';

Any data inserted will be captured. Some fields have special meaning:

• user.id: Your internal identifier for the user.
• user.email: The email of the user.
• user.username: The username of the user.
• user.name: The name of the user.

In addition to what is inserted in the metadata table, the following values are also captured:

• changebase.database.id: The id of the database.
• changebase.database.systemid: The unique system identifier of the PostgreSQL cluster.
• changebase.database.timeline: The PostgreSQL timeline id
• lsn: The LSN (Log Sequence Number) of the transaction.
• xid: The PostgreSQL transaction id.

Grant the Changebase user database access

-- This grants changebase the ability to replicate from tables in the `public` schema. If you 
-- have any other schema that you want replicated, issue the all the commands for those
-- schemas as well.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO changebase;

-- Alter the default privileges to grant changebase select access to future tables.
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO changebase; 

Note that any tables that don't have a primary key will need to have the replica identity set to either full or a unique index on that table.

Create the replication slot

SELECT
    *
FROM pg_create_logical_replication_slot('changebase', 'pgoutput');

pg_recvlogical -d <dbname> --slot=changebase --create-slot -P pgoutput

Update pg_hba.conf

host  <dbname>    changebase <<changebase-ip>> scram-sha-256
host  replication    changebase  <<changebase-ip>> scram-sha-256

Testing the Replication Slot

pg_recvlogical -d <dbname> \
    -f - \
    -U changebase \
    -S changebase \
    --option=proto_version=1 \
    --option=publication_names=changebase --start

References