https://linen.dev logo
Join Slack
Powered by
Our Airbyte Connectors to PostgreSQL occasionally ...
# help-connector-development
s
Our Airbyte Connectors to PostgreSQL occasionally throw "Saved offset is before replication slot's confirmed lsn" errors:
Copy code
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.
We have tried both options for "Refresh your data" -- namely "Refresh streams and retain records" and "Refresh streams and remove records" -- as well as "Clear your data" and none of these address the issue. The only way we've found to fix this is to completely drop and recreate the Connection and/or the Source. This is fine when it's occasional, but we are now trying to stream data from PostgreSQL every two minutes (i.e., near real-time (NRT)). Last night, after running fine for hours, the Connector inexplicably started throwing the above error and has failed since. ---------------------------------- Here are the LSN lines from the last four successful syncs, with the last line being the first failed sync. I suspect that last "Saved offset LSN" shouldn't be the same value as the sync before it (i.e., 
19508285977976
).
Copy code
2026-05-06 22:39:33 info INFO main i.a.i.s.p.c.PostgresDebeziumStateUtil(isSavedOffsetAfterReplicationSlotLSN):69 Replication slot confirmed_flush_lsn : 19508081354072 Saved offset LSN : 19508121385984

2026-05-06 22:43:34 info INFO main i.a.i.s.p.c.PostgresDebeziumStateUtil(isSavedOffsetAfterReplicationSlotLSN):69 Replication slot confirmed_flush_lsn : 19508124153352 Saved offset LSN : 19508167298192

2026-05-06 22:46:59 info INFO main i.a.i.s.p.c.PostgresDebeziumStateUtil(isSavedOffsetAfterReplicationSlotLSN):69 Replication slot confirmed_flush_lsn : 19508172623296 Saved offset LSN : 19508244459200

2026-05-06 22:48:34 info INFO main i.a.i.s.p.c.PostgresDebeziumStateUtil(isSavedOffsetAfterReplicationSlotLSN):69 Replication slot confirmed_flush_lsn : 19508244459200 Saved offset LSN : 19508285977976

2026-05-06 22:52:33 info INFO main i.a.i.s.p.c.PostgresDebeziumStateUtil(isSavedOffsetAfterReplicationSlotLSN):69 Replication slot confirmed_flush_lsn : 19508297437136 Saved offset LSN : 19508285977976
------------------------------------- Both myself and our Chief Architect have spent hours troubleshooting this issue and cannot find a solution. I suspect there is an issue in Airbyte's handling of LSNs. We need any ideas y'all have, please. If Airbyte is not reliable, using it for NRT is a non-starter.
FWIW, our current wal config: • `wal_buffers`: 64mb • `wal_decode_buffer_size`: 512kb • `wal_keep_size`: 0 <-- This might be relevant. https://postgresqlco.nf/doc/en/param/wal_keep_size/ • `wal_receiver_status_interval`: 10s • `wal_receiver_timeout`: 1min • `wal_retrieve_retry_interval`: 5s • `wal_segment_size`: 64mb • `wal_sender_timeout`: 0 • `wal_skip_threshold`: 2mb • `wal_summary_keep_time`: 10d • `wal_writer_delay`: 200ms • `max_slot_wal_keep_size`: -1 <-- that's the infinite wal size for a slot • `max_wal_size`: 1504mb <-- that just controls the size of each file chunk on disk, not retention
i hate this but this may address the immediate issue:
I anticipate this ^ isn't going to work and it's going to have the same issue. If the Connector doesn't auto-resync successfully following an upcoming CDC/WAL error, we will try a heartbeat query as referenced in the thread. the people later in that thread had some luck with setting up a heartbeat query (see screenshot, docs here). This would require some setup in PostgreSQL. Given the majority of these "Saved offset is before replication slot's confirmed lsn" errors seem to occur overnight, I wonder if this is somehow related to lower transaction volumes during those times. If so, this heartbeat query should address the issue by creating some data movement. Instructions from that thread: > Yes, the heartbeat appears to have alleviated the issue entirely for us. > How to add a heartbeat table: > 1. Create a simple table - I had columns "id", "value", and "updated", though I could have skipped the "value" column in hindsight > 2. Add this table to the publication - this is critical > 3. Put a query like this into the debezium heartbeat query field in the source: 
update yourSchema.yourTable set value='heartbeat', updated=now();
> You do NOT need to replicate this stream in airbyte for this to work. In fact we don't have the schema this table is in selected in the source configuration. > This is definitely just a workaround though. There is clear underlying problem that should be addressed. I'm not sure if it's a problem with Airbyte, Debezium, or both. I vehemently agree that this issue needs to be addressed, to whoever may be listening from Airbyte.
f
Hello @Seth Terrell, I came across the same issue and I know how frustrating this is ... My understanding is that this issue happens when the tables you added to your publication have not changed since last sync. When this happens, airbyte sends the signal to the PostgreSQL replicator to flush the lsn, but does not update its offset ... This leads to a delta between the last collected offset and the last offset in the replicator. To solve this, there is a cleaner workaround that we found is to add a heartbeat table to the puvlication, that is always changed between reoplications because records are inserted by airbyte before the collect starts. This workaround became so popular that airbyte added it to its docs - https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting#simple-fix-read-only
s
thank you, @Francisco Carneiro. The heartbeat table is/was our next step when/if the Connector fails with the LSN error again. What's very strange and against what I anticipated is that after setting the Source's "Invalid CDC position behavior (Advanced)" Advanced Setting to "Re-sync data" (from the default of "Fail sync"), the Connector has not face-planted in several days. I suspect this is just because the LSN error hasn't recurred and not that the LSN error occurred and the Source recovered, but I haven't proven that. I'd try to prove this, but there is a log file for every two minutes so using the Airbyte UI to search these is out. I could conceivably search the logs en masse in GCP and may do so. We'll let this run until it doesn't and if it's the LSN error again, we'll try the heartbeat table.
p
Hi @Seth Terrell! I have been facing the same issue. Any chance you succeeded debugging that?
s
It still hasn't failed since setting the Source's "Invalid CDC position behavior (Advanced)" Advanced Setting to "Re-sync data" (from the default of "Fail sync"),
p
I tried that same setting last week, but although I stopped getting the LSN error, the syncs started loading all records over and over, like a continuous full refresh loop. In the end, I had tons of unnecessary and duplicated data being written to my S3 (Postgres → S3). Did you notice that behavior when evaluating the Timeline?
s
I suspected it would just repull everything too and was concerned as well. I looked in the logs for every Airbyte component I could find in GCP and couldn't even find the original LSN errors, but with the original sync count of around 4M records, I haven't seen any sync counts since that approach that number, so it appears it hasn't been doing a full-sync. But I did reduce the sync frequency for the time being to 1 hour, so it may not prompt the LSN issue. this is problematic at best.
❤️ 1
p
thank you for sharing!! I've been trying to debug that for a while
s
if you figure it out please post your learnings here. we're definitely not the only ones with this issue.
❤️ 1
6 Views
Open in Slack
PreviousNext
Powered by