Loading Data from PostgreSQL Source to PostgreSQL Target - Need to only capture inserts as part of CDC

[GayathriTechno]

Hi Team,

We are trying to replicate data from PostgreSQL Source to PostgreSQL target, we are having a requirement to start with only CDC (not Full Load) and to capture only inserts as part of CDC and ignore delete or update.

We also observed that updates are captured as Delete + Insert when we do replication from PostgreSQL source to PostgreSQL target.

Considering this, is there any way to achieve our requirement in Qlik Replicate.

[GayathriTechno]

Hi Sonia,

We tried the suggestion to use store change alone, however we are seeing insert and deletes in ct table.

Could you please help us to understand, what will be the reason for getting update as Delete + Insert while using PostgreSQL as source and target.

Also, please let us know what should be adjusted in _ct table as per below suggestion.

For updates captured as delete + insert, review the __CT tables and adjust the replication task settings accordingly.

Thanks

[aarun_arasu]

Hello ,

Thanks for reaching out to Qlik community , I am just curious to understand  the below statement 

We also observed that updates are captured as Delete + Insert when we do replication from PostgreSQL source to PostgreSQL target.

I just tried to reproduce this behavior with postgres as source and target

When I inserted a record , the operation came as "INSERT"

Similarly , When I updated the record , the operation came as "UPDATE"

Can you try to add a header column in Qlik replicate  as shown below to capture the operation ?

Regards

Arun

[john_wang]

Hello @G ayathriTechno ,

It is straightforward to filter out all operations except for INSERT when replicating to a target. You can apply this filter at either the Table Level or the Global Level. For example, at the Table Level:

Both UPDATE and DELETE operations will be excluded from replication, ensuring that only INSERT operations are replicated to the target.

However, please take note of the following:

1. Primary Key (PK) Violations:
   PK violations may occur if a row with a specific PK value is deleted and then inserted again. To handle such scenarios, you should configure the replication task to operate in UPSERT mode:

   

2. GUI Display of Operations:
   Although the replication process filters out UPDATE and DELETE operations, these operations might still appear in the GUI. Please note that they are ignored during the replication process and do not impact the target.

Regarding the observation:

>> We also observed that updates are captured as Delete + Insert when we do replication from PostgreSQL source to PostgreSQL target.

I have not observed this behavior in my local tests. You may use the pg_logical_slot_peek_changes() function to inspect the operations recorded in the PostgreSQL Write-Ahead Log (WAL). Compare the WAL output with the behavior observed in Qlik Replicate to further diagnose the issue.

Hope this helps.

John.

[Heinvandenheuvel]

>>> "We also observed that updates are captured as Delete + Insert when we do replication from PostgreSQL source to PostgreSQL target."

Are you sure they are CAPTURED that way or is this statement based on observing them being applied that way? With task error handling set to 'ingnore missing data row on update" replicate transforms update on source to delete + insert, ditto for 'duplicate pk on insert'.

As always, for the best support please indicate the exact source and target DBs with their version numbers as well as the replicate version used, and possibly the replicate server OS (less relevant).

Hein

[GayathriTechno]

Hi  

Thanks for replicating the scenario.

when we try replicate the data from actual source table in PostgreSQL database, we are unable to see the update as update rather delete+insert.

In the attached screenshot, we can see there is no update count, but in real time there are updates only going on in source database

Thanks in advance for any responses.

[GayathriTechno]

Hi,

Thanks for the response,

However, in our case we are getting the updates as delete + insert, due to this we have a lot of inser operation which are not true inserts and not required to replicate in target.

Kindly suggest a way to identify the root cause of the behavior in PostgreSQL to PostgreSQL.

Also please note that our source and target are Amazon RDS for PostgreSQL  database, But we used normal PostgreSQL type during endpoint creation.

Thanks-

[DesmondWOO]

Hi,

To determine whether it is an UPDATE or an INSERT followed by a DELETE, could you check the changes by running pg_logical_slot_peek_changes on your PostgreSQL database?

Regards,
Desmond

[GayathriSan]

HI @DesmondWOO 

Thank you for the response,

However, we don't have required privileges to run  pg_logical_slot_peek_changes  on source database instance.

Are there any other possible ways through which can we identify the root cause?

Thanks-

[john_wang]

Hello @GayathriSan ,

To identify the operations recorded in PostgreSQL WAL files, the best approach is to use a privileged account and execute the pg_logical_slot_peek_changes() function. This method helps in effectively troubleshooting the issue.

Regards,

John.