Troubleshooting PostgresSQL CDC not picking up UPDATE or DELETE records.


Userlevel 4
Badge

Problem:

While running PostgresSQL CDC origin pipelines, you notice that some updates/deletes made in your origin DB are not being picked up by your CDC origin.

 

Background:

There are certain prerequisite tasks that are required to use Postgres CDC pipelines. After checking that these steps are completed, if you are still notice missing UPDATE or DELETE records in your pipeline, then follow the steps below:

 

Problem:

You notice that your Postgres CDC origin is only picking up INSERTs for a table, but not DELETEs or UPDATEs. You may notice these changes are excluded from your CDC record's changes list:

Explanation:

This indicates an issue with WAL not giving information identifying which row is affected by these changes.

This typically happens if your table does not have any indexes defined. If your table does not have any indexes, then UPDATEs and DELETEs changes for this table will be show up as null. INSERT changes will still be picked up since they do reference a particular row in your source DB.

Solution:

  1. Add an index, for instance a primary key column to your Postgres table for UPDATEs and DELETEs to be picked up by your pipeline.
  2. You could also alter the source table to generate replication ids, which will publish the entire record to WAL. This method is less ideal, especially for wide tables since it will publish all columns to WAL, it will be more efficient to use a PK.
ALTER TABLE <table_name> REPLICA IDENTITY FULL;

 


0 replies

Be the first to reply!

Reply