@kapa.ai, i have setup incremental append pipeline for airbyte its from postgress to s3 postgres source version ---> 3.6.15 and airbyte version is 1.7.0 but everything when the replication is running it is doing full refresh what could be the issue ?

There are a few known issues that could cause incremental syncs to behave like full refreshes with the Postgres source connector. Here's what the knowledge sources indicate: Possible Causes 1. CTID Cursor Reset to (0,0) — Bug in Connector Version 3.7.0 If you're using CTID-based incremental sync (non-CDC mode), there is a known bug where the cursor resets to

(0,0)

when a sync returns 0 records for a stream, causing a full table re-read on the next sync. [CTID cursor bug] However, this bug was reported for version 3.7.0, and you're on 3.6.15, so this specific bug may not apply to you directly. A fix was released in version 3.7.2. [CTID fix PR] 2. CDC-Related LSN Issues If you're using CDC mode, there are known issues with certain connector versions where the

confirmed_flush_lsn

advances past the saved state LSN, causing the connector to trigger a full re-sync. Notably: • Version 3.6.15 has been reported as a working version by users who encountered CDC issues in later versions (3.6.18+). [CDC LSN bug] So if you're on 3.6.15 with CDC, this particular LSN bug is less likely to be the cause. 3. Cursor Field Not Properly Configured For standard (non-CDC) incremental sync, the Postgres connector requires a user-defined cursor field. If the cursor field is not set correctly, the sync may not behave incrementally. Make sure you have explicitly defined a cursor field in your connection settings. [incremental append docs] 4. Cursor Field Value Not Updating Even with a cursor field set, if records are modified without updating the cursor field value, the incremental sync won't detect them as new/changed records. [incremental append docs] Recommendations 1. Verify your replication method: Are you using CDC or standard cursor-based incremental sync? 2. Check your cursor field: Ensure it is properly defined and is being updated with new/modified records. 3. Review sync logs: Look for messages like

"Searching for WAL resume position"

or

"Saved offset is before replication slot's confirmed lsn"

which would indicate CDC-specific issues. 4. Check the

Invalid CDC position behavior

setting (if using CDC): If set to

Re-sync data

, it will silently trigger a full refresh when the CDC position is invalid. [CDC LSN issue] Could you share whether you're using CDC or standard cursor mode, and any relevant log output? That would help narrow down the root cause further. 👍 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 am using CDC how i can resolve this issue

Since you're using CDC mode with Postgres source version 3.6.15, here's what the knowledge sources indicate: Good News First Version 3.6.15 has been confirmed by multiple users as a working version that does not exhibit the LSN advancement bug introduced in 3.6.19+. [CDC LSN bug] So the PgJDBC regression is unlikely to be your root cause. Most Likely Cause: WAL/LSN Position Issue The most common reason CDC triggers a full refresh is this log message: >

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

This happens when the WAL records needed for incremental sync have been removed by Postgres before Airbyte could read them. [Postgres troubleshooting] How to Fix It 1. Add a Heartbeat Table (Recommended for Low-Traffic Tables) If your publication tables don't have high transaction volumes, the

confirmed_flush_lsn

may not advance properly. Fix this by creating a heartbeat table:

CREATE TABLE airbyte_heartbeat (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp,
    text TEXT
);

Then add it to your publication:

ALTER PUBLICATION <publicationName> ADD TABLE airbyte_heartbeat;

Then configure the Debezium heartbeat query in the Postgres source connector settings:

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

[WAL disk consumption fix] 2. Add a High-Traffic Table to the Publication (Read-Only Fix) If you don't want to give Airbyte write access, add a high-traffic table to the publication to keep the LSN advancing:

ALTER PUBLICATION <publicationName> ADD TABLE <high_volume_table>;

You don't need to actually sync this table. [WAL disk consumption fix] 3. Sync More Frequently If WAL files are being purged between syncs, increasing sync frequency can help prevent the LSN from falling behind. [Postgres troubleshooting] 4. Check Your Publication Configuration Ensure that the tables in your Airbyte catalog match exactly the tables in your publication. A mismatch can cause events to pile up unacknowledged in the WAL, eventually causing issues. [CDC forum post] Next step: Check your sync logs for the message

"Saved offset is before Replication slot's confirmed_flush_lsn"

to confirm this is the root cause, then apply the heartbeat table fix as the most reliable solution. 👍 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, getting this error Failure in source: Saved offset is before replication slot's confirmed lsn. Please reset the connection, and then increase WAL retention and/or increase sync frequency to prevent this from happening in the future. See https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting#under-cdc-incremental-mode-there-are-still-full-refresh-syncs for more details.

This confirms the root cause — the WAL records needed for your incremental CDC sync have been removed by Postgres before Airbyte could read them. Here's how to resolve it: Immediate Fix: Reset the Connection As the error message states, you need to reset the connection first. This will trigger a one-time full refresh to re-establish the CDC state. Then Prevent It from Recurring Option 1: Add a Heartbeat Table (Recommended) This is the most reliable fix for low-traffic tables. [WAL disk consumption fix] Step 1: Create the heartbeat table:

CREATE TABLE airbyte_heartbeat (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp,
    text TEXT
);

Step 2: Add it to your publication:

ALTER PUBLICATION <publicationName> ADD TABLE airbyte_heartbeat;

Step 3: In the Airbyte Postgres source connector UI, go to Advanced → Debezium heartbeat query and enter:

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

This keeps the LSN advancing even when your tracked tables have no changes. Option 2: Add a High-Traffic Table to the Publication (No Write Access Needed)

ALTER PUBLICATION <publicationName> ADD TABLE <high_volume_table>;

You don't need to sync this table — just having it in the publication keeps the LSN moving. [WAL disk consumption fix] Option 3: Increase Sync Frequency Syncing more frequently reduces the chance of WAL files being purged before Airbyte reads them. [Postgres troubleshooting] Option 4: Increase WAL Retention Set a higher

wal_keep_size

on your Postgres instance to retain more WAL files. The default is

0

. [Postgres troubleshooting] Recommended path: Reset the connection, then implement the heartbeat table solution to prevent this from happening again. 👍 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 what should be wal keep size ?

The knowledge sources do not provide a specific recommended value for

wal_keep_size

. However, they do offer the following guidance: • The default value is

0

(no WAL retention), which is why WAL files can be purged before Airbyte reads them. [Postgres troubleshooting] • The recommended WAL retention period is 7 days, with a minimum of longer than your sync frequency. [CDC best practices] For example: The exact

wal_keep_size

value (in MB) you need depends on how much WAL your database generates during your retention period. The knowledge sources don't specify an exact MB value, but the key principle is: set it high enough to cover at least your sync interval, with 7 days as the optimal target. That said, the heartbeat table approach is generally more reliable than relying solely on

wal_keep_size

, since it actively keeps the LSN advancing rather than just retaining more WAL files. [WAL disk consumption fix] 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help., with interactive elements

@kapa.ai, which airbyte version should i use ?

The knowledge sources do not contain enough information to make a specific Airbyte platform version recommendation for your use case (Postgres CDC → S3 with source version 3.6.15). What the knowledge sources do mention is: • Users reported CDC issues on Airbyte v1.5.0 with newer connector versions (3.6.28+). [CDC LSN bug] • You are currently on Airbyte v1.7.0 with connector version 3.6.15, which is a known stable version for CDC. [CDC LSN bug] Since your issue is the WAL/LSN position problem (not a platform version issue), upgrading or downgrading the Airbyte platform version is unlikely to fix it. The root cause is WAL files being purged before Airbyte reads them, which is addressed by: 1. Resetting the connection (immediate fix) 2. Adding a heartbeat table to keep the LSN advancing (long-term fix) 3. Increasing sync frequency or

wal_keep_size

I'd recommend staying on your current Airbyte version and focusing on the heartbeat table solution rather than changing the platform version. 👍 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 did reset the connection but it didn't worked

Since resetting the connection didn't resolve the issue, here are additional steps to try based on the knowledge sources: 1. Implement the Heartbeat Table (Most Important) Resetting alone won't prevent the issue from recurring if the underlying WAL problem isn't addressed. The heartbeat table is the most reliable fix: [WAL disk consumption fix]

-- Step 1: Create the heartbeat table
CREATE TABLE airbyte_heartbeat (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp,
    text TEXT
);

-- Step 2: Add it to your publication
ALTER PUBLICATION <publicationName> ADD TABLE airbyte_heartbeat;

Then in the Airbyte UI, go to your Postgres source → Advanced → Debezium heartbeat query and enter:

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

2. Check If the State Is Still Corrupted After Reset There is a known bug where clearing/resetting a connection may not fully clear the state if there are disabled or removed streams still holding an old LSN in the state. [state reset bug] If this is the case, the only confirmed fix mentioned in the knowledge sources is deleting the connection entirely and recreating it. 3. Verify Your Publication Configuration Make sure the tables in your Airbyte catalog exactly match the tables in your publication. A mismatch causes WAL events to pile up unacknowledged, which can cause this error to persist. [CDC forum post] Recommended next steps: 1. Delete and recreate the connection (not just reset) 2. Before running the first sync, set up the heartbeat table and configure the Debezium heartbeat query 3. Then run the initial sync 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.