what is the issue in this https://github.com/airbytehq/airbyte/issues/47547

I apologize, but I don't have access to the specific GitHub issue you've linked (https://github.com/airbytehq/airbyte/issues/47547) in my current knowledge sources. The documents provided don't contain information about this particular issue. To accurately answer your question, I would need more details about the issue you're referring to. If you could provide more information or context about the problem described in that GitHub issue, I'd be happy to try and help based on the general knowledge I have about Airbyte. If you're experiencing a specific problem with Airbyte, I recommend checking the latest version of Airbyte to see if the issue has been resolved, or opening a new issue on the Airbyte GitHub repository if the problem persists.

@kapa.ai 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 2GB. • 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.

Based on the information provided in your question and the knowledge sources available, I can offer some insights and suggestions regarding your CDC issue with PostgreSQL and Airbyte. 1. Underlying cause of incremental changes not being detected: The most likely cause of this issue is related to the WAL (Write-Ahead Log) management in PostgreSQL. According to the [Airbyte documentation](https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting#under-cdc-incremental-mode-there-are-still-full-refresh-syncs), there are scenarios where full refresh syncs can occur even when CDC is enabled. This happens when the WALs needed for the incremental sync have been removed by PostgreSQL. The documentation states:

Normally under the CDC mode, the Postgres source will first run a full refresh sync to read the snapshot of all the existing data, and all subsequent runs will only be incremental syncs reading from the write-ahead logs (WAL). However, occasionally, you may see full refresh syncs after the initial run.

When this happens, you might see the following log:

Saved offset is before Replication slot's confirmed_flush_lsn, Airbyte will trigger sync from scratch

2. Specific logs or configurations to examine: To troubleshoot this issue, you should look for the log message mentioned above in your Airbyte logs. Additionally, you can check the following: a. WAL retention settings: Although you've set

max_slot_wal_keep_size

,

max_wal_size

, and

wal_keep_size

to 2GB, this might not be sufficient depending on your data volume and sync frequency. You may need to increase these values further. b. Sync frequency: If your syncs are too infrequent, PostgreSQL might remove the necessary WAL files before Airbyte can process them. Try increasing the sync frequency. c. Replication slot status: Check the status of your replication slot in PostgreSQL to ensure it's not falling behind. d. CDC requirements: Ensure that your tables meet all the [CDC requirements](https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting#cdc-requirements), such as having primary keys and not being views. To resolve this issue, you can try the following: 1. Increase WAL retention: Set higher values for

wal_keep_size

and

max_slot_wal_keep_size

. The documentation suggests:

sql
   ALTER SYSTEM SET wal_keep_size = '10GB';

2. Increase sync frequency: Schedule more frequent syncs to prevent WAL files from being removed before Airbyte can process them. 3. Monitor replication slot: Regularly check the status of your replication slot to ensure it's not falling behind. 4. Check table structure: Ensure all tables you're syncing have primary keys and meet other CDC requirements. 5. Consider enabling heartbeat events: As mentioned in a [forum post](https://discuss.airbyte.io/t/2675), enabling heartbeat events might help prevent WAL backlog issues:

Based on the information provided in your question and the knowledge sources available, I can offer some insights and suggestions regarding your CDC issue with PostgreSQL and Airbyte. 1. Underlying cause of incremental changes not being detected: The most likely cause of this issue is related to the WAL (Write-Ahead Log) management in PostgreSQL. According to the [Airbyte documentation](https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting#under-cdc-incremental-mode-there-are-still-full-refresh-syncs), there are scenarios where full refresh syncs can occur even when CDC is enabled. This happens when the WALs needed for the incremental sync have been removed by PostgreSQL. The documentation states: > Normally under the CDC mode, the Postgres source will first run a full refresh sync to read the snapshot of all the existing data, and all subsequent runs will only be incremental syncs reading from the write-ahead logs (WAL). However, occasionally, you may see full refresh syncs after the initial run. When this happens, you might see the following log: > Saved offset is before Replication slot's confirmed_flush_lsn, Airbyte will trigger sync from scratch 2. Specific logs or configurations to examine: To troubleshoot this issue, you should look for the log message mentioned above in your Airbyte logs. Additionally, you can check the following: a. WAL retention settings: Although you've set

max_slot_wal_keep_size

,

max_wal_size

, and

wal_keep_size

to 2GB, this might not be sufficient depending on your data volume and sync frequency. You may need to increase these values further. b. Sync frequency: If your syncs are too infrequent, PostgreSQL might remove the necessary WAL files before Airbyte can process them. Try increasing the sync frequency. c. Replication slot status: Check the status of your replication slot in PostgreSQL to ensure it's not falling behind. d. CDC requirements: Ensure that your tables meet all the [CDC requirements](https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting#cdc-requirements), such as having primary keys and not being views. To resolve this issue, you can try the following: 1. Increase WAL retention: Set higher values for

wal_keep_size

and

max_slot_wal_keep_size

. The documentation suggests:

sql    ALTER SYSTEM SET wal_keep_size = '10GB';

2. Increase sync frequency: Schedule more frequent syncs to prevent WAL files from being removed before Airbyte can process them. 3. Monitor replication slot: Regularly check the status of your replication slot to ensure it's not falling behind. 4. Check table structure: Ensure all tables you're syncing have primary keys and meet other CDC requirements. 5. Consider enabling heartbeat events: As mentioned in a [forum post](https://discuss.airbyte.io/t/2675), enabling heartbeat events might help prevent WAL backlog issues: > Consider to either adjust your filter configuration or enable heartbeat events (via the heartbeat.interval.ms option) to avoid this situation. If these steps don't resolve the issue, you may need to reset the connection and perform a new initial sync. Also, ensure you're using the latest version of the Airbyte PostgreSQL source connector, as there have been performance improvements in recent versions.

@kapa.ai how to do this Debezium heartbeat query (Advanced)

A message from kapa.ai

To set up a Debezium heartbeat query in Airbyte for a PostgreSQL source, you can follow these steps: 1. Create a heartbeat table in your PostgreSQL database:

sql CREATE TABLE airbyte_heartbeat (     id SERIAL PRIMARY KEY,     text VARCHAR(255),     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

2. Add this table to the Airbyte publication:

sql ALTER PUBLICATION <your_publication_name> ADD TABLE airbyte_heartbeat;

3. When configuring your PostgreSQL source in Airbyte, you need to set the

heartbeat_action_query

property. This query will be periodically executed by Airbyte on the

airbyte_heartbeat

table. For example, you can set it to:

sql INSERT INTO airbyte_heartbeat (text) VALUES ('heartbeat')

This setup is described in the [Airbyte documentation](https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting#advanced-wal-disk-consumption-and-heartbeat-action-query), which states: "A workaround for this situation is to artificially add events to a heartbeat table that the Airbyte use has write access to. This will ensure that Airbyte can process the WAL and prevent disk space to spike." The documentation provides these steps: 1. Create a table (e.g.

airbyte_heartbeat

) in the database and schema being tracked. 2. Add this table to the airbyte publication. 3. Configure the

heartbeat_action_query

property while setting up the source-postgres connector. This approach helps prevent issues with WAL disk consumption and ensures that Airbyte can process the WAL even when there are no changes to the tracked tables. It's particularly useful in situations where there are a large volume of changes, but only a small percentage of them are being made to the databases, schemas, and tables configured for capture. For more detailed information, you can refer to the [Debezium documentation](https://debezium.io/documentation/reference/stable/connectors/postgresql.html#postgresql-wal-disk-space) linked in the Airbyte docs.