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.
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.
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 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.
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.
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'));
