Airbyte postgres cdc isn't ingesting records except when "burst" syncs happen occasionally. But ingesting of recrods should happne at every sync, obviously there are records being updated during the day. What are the troubleshooting steps I should take on this?

Thinking completed When an Airbyte Postgres CDC connection only ingests records in intermittent bursts (often returning 0 records during regular syncs despite ongoing database activity), it is typically caused by one of two issues: 1. Low transaction volume on the captured tables, preventing the replication slot’s Log Sequence Number (LSN) from advancing. 2. Insufficient initial waiting time, meaning the connector times out before reading through the backlog of changes. 3. Mismatches between the Airbyte catalog and the Postgres publication, causing events to be filtered out. Here are the troubleshooting steps to diagnose and fix this issue: 1. Check the Sync Logs for Filtered Events or LSN Warnings Inspect your Airbyte sync logs during the runs that return 0 records: • Look for warnings like:Received 10001 events which were all filtered out, so no offset could be committed. This prevents the replication slot from acknowledging the processed WAL offsets...This happens when there are database transactions occurring, but they belong to tables that are either excluded from the publication or filtered out by Airbyte [Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. • Check if you see:Received offset commit request... but ignoring it. LSN flushing is not allowed yetor logs indicating

no progress since last heartbeat

[Github Issue (open): [source-postgresql] CDC sync is only allowing WAL to be emptied every two syncs]. 2. Configure a Heartbeat Action Query (Recommended Fix) In Postgres logical replication via Debezium, if the tables included in your publication have low or intermittent transaction volumes compared to the rest of the database, the replication slot's

confirmed_flush_lsn

cannot advance. This causes changes to lag behind and only be flushed when high-activity bursts occur [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>]. To resolve this, set up a heartbeat mechanism: 1. Create a heartbeat table in your Postgres database:CREATE TABLE airbyte_heartbeat ( id SERIAL PRIMARY KEY, timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp, text TEXT ); 2. Add the table to your Airbyte publication:ALTER PUBLICATION publication_name ADD TABLE airbyte_heartbeat; 3. Configure the connector: In Airbyte, edit your Postgres Source settings under the CDC configuration (Advanced / Optional fields) and add the Debezium heartbeat query:INSERT INTO airbyte_heartbeat (text) VALUES ('heartbeat');_(Note: You do not need to sync the

airbyte_heartbeat

table in your connection catalog)_ [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. 3. Verify the Publication and Catalog Alignment Ensure that all tables you intend to sync are actually part of the PostgreSQL publication: • The Airbyte UI allows selecting tables even if they are not in the publication; however, unincluded tables will not replicate [Documentation Integrations > Sources > Postgres > Cloud SQL for PostgreSQL>]. • Ensure each table has an appropriate replica identity configured:ALTER TABLE table_name REPLICA IDENTITY DEFAULT;_(or

REPLICA IDENTITY FULL

if tables use TOAST/large column values)_ [Documentation Integrations > Sources > Postgres > Cloud SQL for PostgreSQL>]. 4. Increase the Initial CDC Waiting Time If your database generates a high volume of transactions across other tables, the connector may need more time to parse through the WAL before it encounters changes for the tracked tables [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>]. • If the connector closes before reaching your records, adjust the Initial CDC waiting time in the Postgres source configuration (default is 1200 seconds / 20 minutes; valid range is 120 to 2400 seconds) to allow the connector enough time to read through the change logs [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>]. 5. Check Replication Slot Status and Upgrade Connector Check the replication slot lag directly on your Postgres database:

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn 
FROM pg_replication_slots 
WHERE slot_type = 'logical';

Ensure that no other process is sharing or reusing the replication slot Airbyte is using [Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. Additionally, ensure you are running the latest version of the Postgres source connector, as several LSN flushing and CDC synchronization bugs were addressed across connector releases [Forum Post: Stack Syncs and Automated Full Resyncs on CDC; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai Is it because there is so much retained wal to read, syncs prioritize reading it over ingesting records...? I just don't know why it wouldn't ingest recrods

Thinking completed The knowledge sources do not explicitly describe a mechanism where Airbyte "prioritizes reading retained WAL over ingesting records." Rather, reading the WAL and ingesting records are part of the exact same process: Airbyte (via Debezium) streams and decodes the WAL sequentially to find changes. However, having a large amount of retained WAL is directly linked to syncs failing to ingest records, and it typically happens for two reasons: 1. The connector times out before reaching the tracked changes Postgres writes all database changes sequentially into the WAL. If your database experiences activity on tables that are not part of your Airbyte sync (or if there is a massive backlog of WAL segments to parse), the connector must read through and discard all those unrelated log entries before it encounters the records intended for replication [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>; Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. When the connector starts, it waits up to the configured Initial Waiting Time to find new data. If it spends that entire window scanning through WAL records that get filtered out, it can reach the timeout and complete or abort the sync with 0 records ingested [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>]. 2. Low activity on publication tables prevents LSN advancement In Postgres logical replication, if the specific tables in your Airbyte publication have low transaction activity, the replication slot's

confirmed_flush_lsn

cannot advance—even if other parts of the database are constantly writing WAL [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>]. When this happens: • Postgres retains more and more WAL files because the slot has not acknowledged an updated LSN [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>; Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. • Airbyte repeatedly filters out records during regular syncs, logging warnings like:Received 10001 events which were all filtered out, so no offset could be committed. This prevents the replication slot from acknowledging the processed WAL offsets...[Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. • Only when a large "burst" of changes finally occurs on the tracked tables does the connector successfully capture enough events to commit the LSN and advance the offset, making it appear as though data is only ingested in occasional bursts [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>; Discuss Forum Post: 3263]. Recommended adjustments: • Set up a Heartbeat Table / Query: This forces regular write events on the publication, ensuring that the connector's LSN advances on every sync and prevents WAL bloat [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>]. • Increase Initial Waiting Time: In the Postgres source CDC settings, increase

initial_waiting_seconds

(default is 1200 seconds / 20 minutes) if the connector is timing out before it can process through the backlog of change logs [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>; PostgresSourceConfigurationSpecification.kt]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai I think there are so much wal being generated, and syncs can't keep up. Are other tables not in the publication also part of wal generation on the replication lsot?

Thinking completed Yes. All write activity across your entire PostgreSQL database generates WAL records, including changes to tables that are not part of your publication [Forum Post: Stack Syncs and Automated Full Resyncs on CDC; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. Here is how this affects your replication slot and causes syncs to fall behind: 1. The WAL is shared database-wide: PostgreSQL writes every transaction sequentially to the Write-Ahead Log. Even if a table is not included in the publication, its changes still generate WAL entries [Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. 2. Airbyte must read and filter out unrelated WAL events: Because WAL entries are in sequential order, the replication slot must stream all those changes. The connector (via Debezium) decodes the WAL and filters out events belonging to tables outside your publication or Airbyte catalog [Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. 3. High volume on non-publication tables prevents the slot from acknowledging progress: When high transaction activity occurs on unmonitored tables, Airbyte processes thousands of events that are discarded. Because no tracked table events are being emitted, the connector cannot commit offsets or advance the replication slot's

confirmed_flush_lsn

[Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. This causes PostgreSQL to hold onto the WAL files without clearing them, leading to a growing backlog [Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. 4. Syncs time out while scanning the backlog: If WAL is generated faster than the connector can read and discard it, or if the connector reaches its configured waiting time before reaching the tables it cares about, the sync will finish or time out without ingesting any records [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>]. How to address this: • Configure a Heartbeat Query: Setting up an

airbyte_heartbeat

table inside your publication and configuring the source connector's Debezium heartbeat query periodically writes tracked events into the WAL [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>]. This forces the replication slot to acknowledge progress and flush the LSN, allowing PostgreSQL to reclaim WAL disk space and preventing the connector from falling behind [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>; Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. • Increase the Initial CDC waiting time: In the Postgres source CDC settings, increase the waiting time (up to 2400 seconds) so the connector has sufficient time to read through heavy WAL backlogs [Documentation Integrations > Sources > Postgres > Troubleshooting Postgres Sources>]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai we already have the heartbeat query set up and initla cdc waiting time set to 2400. My hypothesis is that syncs are not reaching the records-to-ingest in typical syncs since there are so much wal to go through, and once it gets to read it, there are millions of records to ingest. And while it's at it, wal from other un-related tables will accumulate. Looks like a viciou cycle though

Thinking completed Your hypothesis describes a known pattern with PostgreSQL CDC under heavy, database-wide write loads [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them; Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. When database-wide write volume is extremely high, Airbyte spends most of the sync reading and filtering out WAL entries for tables outside your publication. If this filtering is slow, it can take hours just to scan to the relevant LSN positions [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them; Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. Once the connector finally reaches tracked records, destination backpressure or slow record flushes can stall the sync further, accumulating even more WAL in the meantime and perpetuating the cycle [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them]. Based on the knowledge sources, here are the troubleshooting steps and architectural bottlenecks to address this: 1. Address Destination Backpressure and Buffering A common reason CDC appears stuck or slow while reading heavy logs is backpressure from the destination [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them]: • When the destination takes too long to flush or load data into staging/final tables, throughput drops, which prevents the source connector from reading through the WAL quickly [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them]. • If you are syncing to Snowflake or a warehouse with buffer/concurrency limits, check the destination buffer configuration (e.g., increasing destination buffer size parameters or ensuring staging mode is used) [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them]. 2. Limit Streams per Connection Airbyte support notes that running many CDC tables in a single connection causes destination buffer contention, leading to throughput drops [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them]: • A recommended guideline is to limit the number of CDC tables to a maximum of 30 per connection [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them]. • If you have dozens of tables in a single connection, split them across multiple connections [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them]. (Note: Each separate CDC source connector will need its own dedicated PostgreSQL replication slot, as slots cannot be shared) [Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. 3. Check for Checkpointing & State Persistence Issues CDC syncs can struggle if state/checkpoints are not saved frequently enough: • If a sync runs for a long time reading WAL or staging records and fails/times out before committing state, the next sync must start from the previously saved LSN all over again, repeating the wasted work [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them]. • Ensure you are running a recent version of both the Postgres Source connector and the destination connector, as newer versions have improved CDC performance and LSN checkpoint handling [Forum Post: Postgres CDC connector stuck , never stops reading from source and staging files while never copying them; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. 4. Increase Sync Frequency Leaving long intervals between syncs allows the database to generate massive WAL backlogs between runs. If your connection is running on an hourly or daily schedule: • Increasing the sync frequency (e.g., running every 15–30 minutes) reduces the volume of WAL accumulated between syncs, making it easier for the connector to process each batch before reaching timeout thresholds [Documentation Platform > Understanding-airbyte > CDC best practices>; Github Issue (open): CDC not working for Google AlloyDB(Postgres)]. 5. Verify the Heartbeat Execution Ensure the heartbeat query is actually inserting rows frequently enough: • Check the heartbeat table in PostgreSQL to verify that timestamps or IDs are constantly being written during the sync. • If the heartbeat is only queried infrequently or if the database WAL generation vastly outpaces the heartbeat frequency, the connector might still process thousands of filtered events between heartbeat intervals [Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai Help me troubleshoot this error: "Replication slot 'airbyte_prod_postgres_to_bigquery_slot' has advanced beyond the source's state LSN. Confirmed flush LSN: LSN{3162E/1142B570}, source LSN: LSN{3162D/70058BE0}." I'm using postgres cdc to bigquery and made the LSN behavori to "While reading data"

Thinking completed This error occurs because the PostgreSQL replication slot's

confirmed_flush_lsn

is further ahead in the Write-Ahead Log than the last saved state LSN recorded in Airbyte [Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>; ReplicationSlotManager.kt]. Because the slot has already acknowledged and cleared data past Airbyte's starting point, Airbyte cannot resume from its saved state without risking data loss, causing the validation check to fail [Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>; ReplicationSlotManager.kt]. Here is what causes this and how to resolve it: Why this happened 1. Impact of the "While reading Data" setting: The documentation specification for

lsn_commit_behaviour

explicitly notes: "If While reading Data is selected, in case of a downstream failure (while loading data into the destination), next sync would result in a full sync." [PostgresSourceConfigurationSpecification.kt]When set to

While reading Data

, Airbyte continuously flushes and acknowledges the LSN to Postgres while reading records from the WAL, before they are confirmed as committed by the BigQuery destination. If the sync encounters an interruption, timeout, or failure while loading into BigQuery, Postgres has already advanced the replication slot's

confirmed_flush_lsn

, leaving the Airbyte state file behind [Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>; PostgresSourceConfigurationSpecification.kt]. 2. Known connector bugs with LSN advancing: There have been several reported bugs across

source-postgres

versions where the connector committed an LSN higher than the saved offset, or where PgJDBC automatically advanced

confirmed_flush_lsn

even when zero events were ingested for publication tables [Github Issue (closed): [source-postgres] Airbyte Commits the Wrong LSN When Taking the Initial Snapshot; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. Troubleshooting and Resolution Steps 1. Change LSN Commit Behavior back to "After loading Data in the destination" In your Postgres Source settings under the CDC configuration: • Change LSN commit behavior back to the default:

After loading Data in the destination

[PostgresSourceConfigurationSpecification.kt]. • This ensures that LSNs are only committed back to Postgres after records have successfully landed in BigQuery, preventing the replication slot from getting ahead of the destination state [PostgresSourceConfigurationSpecification.kt]. 2. Recover the Connection State Because the replication slot has moved ahead of the state LSN, the connection cannot resume incrementally as-is [ReplicationSlotManager.kt]. Choose one of the following recovery options: • Option A: Re-sync data (Recommended) ◦ Change the connection's Invalid CDC position behavior setting from

Fail sync

to

Re-sync Data

(or trigger a Reset/Refresh from the UI) [Github Issue (closed): [source-postgres] Airbyte Commits the Wrong LSN When Taking the Initial Snapshot; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. ◦ This will re-snapshot the tables and realign Airbyte's state with the slot [Github Issue (closed): [source-postgres] Airbyte Commits the Wrong LSN When Taking the Initial Snapshot; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. • Option B: Manually advance the state LSN (Advanced / Data loss risk) ◦ If you cannot perform a full resync and accept skipping any unread WAL between the two positions, users have worked around this by manually updating the connection state so that Airbyte's state LSN matches the current

confirmed_flush_lsn

from Postgres [Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. 3. Update the Postgres Source Connector Version Check what version of

source-postgres

you are using: • Versions between

3.6.19

and

3.7.0

had major known regressions where

confirmed_flush_lsn

advanced unexpectedly past the saved state [Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. • Fixes for these state/LSN alignment issues were introduced in

3.6.34

,

3.7.2

, and later releases (such as

3.8.x

) [Github Issue (closed): [source-postgres] Airbyte Commits the Wrong LSN When Taking the Initial Snapshot; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>; fix(source-postgres): fix CDC replication slot LSN not advancing]. Upgrading to the latest connector version is recommended. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.