Logical Replication Setup (Aiven.io)

Logical Replication is the main means for allowing PostgreSQL to have Change Data Capture (CDC) logic to track changes and retrieve only the delta/changes to your database tables synchronized. This increases speed of the synchronization so that any system synchronizing data need not retrieve all records from the database tables all the time on each synchornization frequency event. To set up logical replication on your Postgres database please follow the instructions on this page.

Aiven.io

Access the database you desire to configure to synchronize using datalakehouse.io and let's turn on logical replication with the test_decoding plugin following the steps below, with Aiven.io as the data source service.

Connect to the CLI

Type your user login via the command line:

• avn user login name@domain.com

enter your password when prompted.

Confirm all is working by running simple CLI command:

• avn cloud list

Call the service in question (find the service deployed in the Aiven.io panel):

• avn service cli pg-#######

This should render you in a prompt in the terminal, ex: defaultdb=>

Aiven.io Notes for Setting Up Logical Replication :: test_decoding

Aiven.io provides PostgreSQL instances that support Logical Replication. In the remainder of this document page we highlight a few notes to consider for an Aiven.io implementation of PostgreSQL logical replicationconfiguration.

Execute and Record the Following Settings

select setting from pg_settings where name ='wal_level';
select setting from pg_settings where name ='max_replication_slots';
select setting from pg_settings where name ='wal_sender_timeout';
select setting from pg_settings where name ='max_wal_senders';

Ensure that:

• WAL_LEVEL = 'logical'
• WAL_SENDER_TIMEOUT = 0 or 60000 (0 = infinity)
• MAX_REPLICATION_SLOTS >= 8
• MAX_WAL_SENDERS >= 16

Any changes for the above can be done in the Aiven.io Advanced Configuration section of your PostgreSQL service.

In aiven.io the default WAL_LEVEL_SENDERS is set to 0. There are very few other settings you can configure for aiven.io that are not already set.

Disk Space Increase is Normal

Aiven.io has a good article on standard operation increase in disk space due to WAL, https://developer.aiven.io/docs/products/postgresql/concepts/pg-disk-usage.html

Run any of the main postgreSQL commands to check disk space on the instance:

• \l or \l+
• SELECT pg_size_pretty(pg_database_size('yourdbname'));