Does the CDC capture type detect deleted rows from a table if they ar Truncated in Postgres?

Discussions Source to Target
1

It seems that the CDC capture does not see Truncated rows in the WAL for Postgres. Is this expected?

If so how do you handle tables that get Truncated and loaded in a source to know rows were deleted when you want to preserve deleted rows in the target?

2

Hi @binghamc,

Unfortunately this isn’t supported by Postgres CDC. CDC doesn’t catch TRUNCATE TABLE by definition as it isn’t a DML, but a DDL. Behind the scenes, truncate table is just signing the table for being purged by the database and disconnects the storage from the table. Therefore we can’t get these rows from the wal itself.
This is known and expected behavior in cdc, see here: PostgreSQL: Re: WAL streaming and dropping a large table

For handling the scenario you described, you could use standard extraction to compare snapshots of data once loaded into the target dwh tables.

Hope that helps,
Taylor