topic Re: Loading Data from PostgreSQL Source to PostgreSQL Target - Need to only capture inserts as part of CDC in Qlik Replicate

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

GayathriTechno — Fri, 22 Nov 2024 06:18:56 GMT

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.

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

GayathriTechno — Fri, 22 Nov 2024 10:13:30 GMT

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

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

aarun_arasu — Fri, 22 Nov 2024 11:11:46 GMT

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

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

john_wang — Fri, 22 Nov 2024 15:38:16 GMT

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:

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:
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.

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

Heinvandenheuvel — Fri, 22 Nov 2024 16:13:51 GMT

>>> "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

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

GayathriTechno — Tue, 26 Nov 2024 10:42:20 GMT

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.

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

GayathriTechno — Tue, 26 Nov 2024 10:50:48 GMT

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-

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

DesmondWOO — Wed, 27 Nov 2024 02:46:02 GMT

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

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

GayathriSan — Wed, 27 Nov 2024 09:59:17 GMT

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-

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

john_wang — Mon, 02 Dec 2024 14:07:05 GMT

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.