[source-postgres] Change Data Capture (CDC) Not Working

[Nanagoud007]

Topic

I have enabled Change Data Capture (CDC) on my PostgreSQL source following the instructions provided in the Airbyte PostgreSQL source documentation. However, during each sync, Airbyte is loading the entire dataset instead of only the changes. This issue persists even when there are no updates in the source database.

Relevant information

Steps Taken:

• Enabled logical replication in PostgreSQL.
• Created a replication slot and a publication as per the documentation.
• Set max_slot_wal_keep_size, max_wal_size, and wal_keep_size to 10GB.
• Verified that CDC is correctly configured on the PostgreSQL side.

Logs:This 4th time ran sync logdefault_workspace_logs_1388_txt.txt

Expected Behavior:
Airbyte should only sync the changed records instead of reloading the entire dataset on every sync.

Actual Behavior:
Each sync loads the whole dataset, resulting in increased load times and performance issues.

Additional Information:
PostgreSQL Version: 16
Airbyte Version: 0.58.0 >

Questions:

1. What could be the underlying cause of the incremental changes not being detected?
2. Are there specific logs or configurations I should examine to troubleshoot this issue?

I would appreciate any assistance in diagnosing and resolving this problem.

[jduckwo]

I am running into a similar issue. It appears that the subscriber to the replication slot is not being registered properly. When I check the active status of the replication slot, it is set to active=false and the restart_lsn is not advancing as expected. I have dropped/recreated the PG publication, PG Replication Slot, AB Source, AB Destination, and AB Connection objects and still cannot get the incremental replication to work properly. I also verified in the Airbyte logs that the query against the pg_replication_slots view does find the replication slot.

Here is my current config:

• Airbyte version: 1.1.0
• Airbyte Postgres Source Version: 3.6.22
• Airbyte Postgres Destination Version: 2.4.0

Postgres config:

• Postgres 16

ALTER SYSTEM SET wal_keep_size = '1GB';
SELECT pg_reload_conf();
SHOW wal_keep_size;

CREATE ROLE airbyte_role;
ALTER ROLE airbyte_role REPLICATION LOGIN;

GRANT SELECT ON TABLE public.table_1 TO airbyte_role;
ALTER TABLE public.table_1 REPLICA IDENTITY DEFAULT;
 
CREATE PUBLICATION airbyte_publication FOR TABLES IN SCHEMA public;
SELECT pg_create_logical_replication_slot('airbyte_slot', 'pgoutput');

I also would appreciate any help getting this resolved. If helpful, I can provide my logs in addition to the ones in the original post.

[Nanagoud007]

@jduckwo active status will be true when the sync is running

[jduckwo]

@Nanagoud007 I queried the status repeatedly while the sync was running and did not see it ever return as active. Also, the restart_lsn is not advancing as expected. Here are the logs from my latest run:

default_workspace_logs_84_txt.txt

[Nanagoud007]

@jduckwo in my log this is causing the full refresh for me
2024-10-28 10:04:37 �[46mplatform�[0m > SOURCE analytics [airbyte/source-postgres:3.6.22] | Type: db-sources-cdc-cursor-invalid | Value: 1

[Nanagoud007]

@jduckwo i have analysed your attached log the sync mode is full refresh in you case
ime","airbyte_type":"timestamp_with_timezone"}}},supportedSyncModes=[full_refresh],sourceDefined
=true,defaultCursorField=[_ab_cdc_lsn],sourceDefinedPrimaryKey=[[id]],namespace=reporting,isResumable=true,additionalProperties={}],syncMode=full_refresh,cursorField=[_ab_cdc_lsn],destinationSyncMode=overwrite,primaryKey=[[id]],generationId=55,minimumGenerationId=55,syncId=84,additionalProperties={}] will sync in resumeable full refresh mode.

[jduckwo]

There are multiple tables and views in that specific log. Some of those are set to have a full sync while others are not. table_1 should be set to incremental. I am currently upgrading Airbyte to the latest version and recreating all my resources. Once that is done, I will upload a cleaner log with the one table.

[Nanagoud007]

@jduckwo sure, first you try to give publisher to one table let me know if you make any progress, i tried other method xmin column it re-syncing very past only down site is no deletions are captured

[jduckwo]

I rebuilt my Airbyte installation, source, destination, and connection. I dropped and recreated my PG publication and replication slot. I granted access to 1 table table_1 to the database role I am using for the connection.

table_1

• There are 6255 records in the table
• The table size is 2Mb

results

• The replication slot is now active when the sync runs.
• The restart_lsn is now advancing as expected.
• The sync is still doing a FULL OVERWRITE of the data instead of an INCREMENTAL append as expected.

I attached the logs from my latest run. They should be cleaner then the earlier log I posted.

default_workspace_logs_12_txt.txt

[marcosmarxm]

Hello @jduckwo and @Nanagoud007 I shared this issue with the connector team to take a look.

@Nanagoud007 it looks you're using an old version of Airbyte. Is it possible to upgrade to latest? Also can you share what is the source connector version you're using?