Logical Replication Setup

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 synchronization frequency event to set up logical replication on your postgres database please follow the instructions on this page dlh io supports both pgoutput and test decoding logical replication plugin extensions types pgoutput proves to provide the most granular selectivity of cdc replication capability pgoutput allows control of individual tables of individual schemas to only be chosen for replication, as well as an option for all tables across all schemas in the database the test decoding plugin configuration only allows cdc replication control at the database level (thus, all tables of all schemas) which can be overkill for some data pipeline requirements both pg output and test decoding replication allow for insert, updates, and deletes to be tracked where xmin does not track deletes use the links below for instruction on how to set up your respective logical replication mode test decoding replication setup docid\ ifqjhkm099aoilqhcfo76 pgoutput replication setup docid\ mrb 2gddsiobd0olldryo other logical replication details because logical replication is based on the core of the underlying postgresql system and wal, there are some common analysis approaches that can be taken across all types of logical replication as described below overall big picture replication view select from pg stat replication; get the time lag in human readable format select now() pg last xact replay timestamp() as time lag; general logical replication slot details this table provides the basic information about the logic replication you have created when datalakehouse io is retrieving from the slot, the active colum will typically be set to "t" instead of "f" for true and false repsectively select from pg replication slots; peeking at logical slot changes there are several parameters for working with the pg logical slot peek changes function as further described here, https //pgpedia info/p/pg logical slot peek changes html https //pgpedia info/p/pg logical slot peek changes html , for testing the flow of wal changes as identifying the lsns select from pg logical slot peek changes('datalakehouseio replication slot', null, null, 'include xids', '0'); changes to the pg last wal replay lsn is not leveraged by datalakehouse io as this relates to a streaming replication standby replication instance, where datalakehouse io is on demand frequency sync scheduled query based 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 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')); logical replication failues in a standard process where the ddl of your postgresql tables do not change much there is very little cause for failure other than not have a sync frequency that is not aligned with the volume of data your database tables produce commensurate with the disk space of the database/server since logical replication does not track ddl changes an error could occur if a ddl change is made but downstream impacts are not considered in the case of datalakehouse io a manual change may be required on your target system in order to reflect a ddl change on your source system when errors, if any occur, please report them immediately by opening up a support ticket methods used on your source database side to clear issues may include things such as using pg replication origin advance, to skip the transaction that is failing other references ( https //www postgresql org/docs/9 4/catalog pg replication slots html)postgresql https //www postgresql org/docs/9 4/catalog pg replication slots html)postgresql replication slots fields/columns available https //techcommunity microsoft com/t5/azure database for postgresql/change data capture in postgres how to use logical decoding and/ba p/1396421 https //techcommunity microsoft com/t5/azure database for postgresql/change data capture in postgres how to use logical decoding and/ba p/1396421