@kapa.ai Migrating Postgres CDC connections between two self-hosted Airbyte instances (both Community 2.0.0). Same source Postgres server, same slot names, same publications. Instance B's connections are built but inactive; Instance A is still consuming the slots. If I disable a connection on A and enable its equivalent on B without transplanting any state, what does B do on its first sync — resume from the slot's restart_lsn, or treat the connection as new and re-snapshot? That's the one I most need to be sure about. Related: 1. Is a slot reusable across instances at all, given only one consumer attaches at a time? Or does Airbyte tie a slot to the instance that created it? 2. If B resumes from restart_lsn, does the CDC state/offset need to move from A to B, or is the slot sufficient on its own? 3. Is there a supported way to move CDC state between instances? The failure mode I'm trying to avoid is both instances advancing the same LSN at different times, each silently consuming changes the other never sees — nothing errors, rows just go missing.

Thinking completed I must note upfront that the provided documentation does not contain an end-to-end explicit migration guide specifically detailing how fresh CDC connections on an empty state behave when pointed at an pre-existing Postgres replication slot. However, based on how the PostgreSQL CDC connector, replication slots, and Airbyte state operate: What happens on B's first sync without state? Instance B will treat the connection as new and attempt an initial full snapshot (backfill), rather than resuming incrementally from the slot's

restart_lsn

. In Airbyte CDC, the decision to run an initial snapshot versus an incremental sync is governed by the connection's saved state (Debezium offset / LSN), not just the existence of the replication slot on Postgres: • If a connection has no state, CDC runs an initial snapshot before switching to streaming changes [Github Issue (open): Postgres CDC (v3.7.2)]. • Additionally, if the connector finds that the replication slot's

confirmed_flush_lsn

has already advanced beyond the connector's internal state (or if there is a mismatch), it either fails or triggers a sync from scratch (e.g., throwing errors like

Replication slot has advanced beyond the source's state LSN

or

Saved offset is before replication slot's confirmed lsn

) [ReplicationSlotManager.kt (lines 88-181); Github Issue (open): [source-postgres] CDC sync; Forum Post: Stack Syncs]. Answers to Your Specific Questions 1. Is a slot reusable across instances, or is it tied to the instance? A Postgres logical replication slot is managed at the database level by PostgreSQL (

pgoutput

), not hardcoded to a specific Airbyte instance ID. However, Airbyte strongly advises that a replication slot must never be shared across separate connections or instances [Upgrading to Destinations V2; Forum Post: Stack Syncs]. If you are running two distinct connections concurrently or attempting dual-writes, each must have its own separate replication slot. Reusing the same slot name across two different instances will easily lead to LSN race conditions, state validation errors, or missing data. 2. Does CDC state/offset need to move from A to B, or is the slot sufficient? The slot alone is not sufficient; the CDC state/offset must move from A to B. The Postgres connector relies on both: • PostgreSQL side: The slot tracks WAL retention and server-side flush status (

confirmed_flush_lsn

). • Airbyte side: The connector relies on its internal state (persisting Debezium offsets and LSNs) to know where in the stream to resume and validate against the slot [ReplicationSlotManager.kt (lines 88-181); fix(source-postgres)]. Without this internal state, the connection considers itself uninitialized. 3. Is there a supported way to move CDC state between instances? The recommended and supported methods for migrating between instances include: 1. Full Configuration & Database Migration: ◦ Stop/disable Instance A. ◦ Export configuration from Instance A and import it into Instance B, or migrate the Airbyte internal metadata database directly from A to B (e.g., via Postgres dump/restore) [Forum Post: Advice regarding migrating between Airbyte instances; Forum Post: Best way to migrate]. This ensures the connection IDs, stream states, and cursors transfer over intact. 1. Editing Connection State: ◦ Airbyte provides a way to review and edit connection state via the UI under Settings → Advanced → Connection State [Modifying connection state]. While you can copy the raw JSON state from connection A and paste it into connection B, manual state modification must be handled with extreme care, as subtle format mismatches can cause sync failures [Modifying connection state; Github Issue (closed): source-postgres ≥3.8.0]. 1. Clean Cutover with a New Slot (Safest if not migrating state): ◦ If you do not migrate the internal state database, create new replication slots for the connections on Instance B and allow Instance B to run a clean initial sync/snapshot rather than attempting to attach an uninitialized connection to Instance A's existing slot [Upgrading to Destinations V2]. 👍 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 Self-hosted Airbyte Community 2.0.0, Postgres source, CDC via pgoutput. One publication + one slot per schema, ~14 of them, most on a 15-minute schedule. Several slots grow until we drop and recreate them by hand — a few GB each. Two things I want to understand about when the slot actually advances: 1. If a sync FAILS partway, does the connector still advance restart_lsn / confirmed_flush_lsn for what it read, or is it all-or-nothing? Some of these connections are failing intermittently on a network fault at the moment, and I want to know whether the WAL growth is simply the failures, or something separate. 2. For a publication with little or no change volume, does the Postgres source advance the slot on a sync that finds nothing? Server-wide WAL keeps moving, so I'd expect a quiet slot to retain indefinitely unless something confirms. Is Debezium's heartbeat exposed in the source config, and is it the recommended fix for that? Also: is there anything Airbyte-side that bounds slot growth, or is max_slot_wal_keep_size on the server the only backstop? --- Those two questions are chosen because they're the only two mechanisms that produce unbounded growth, and they need opposite fixes. Question 1 tests whether your bloat is just the DNS fault wearing a different hat. If a failed sync advances nothing, then vob at 64% failure is retaining WAL for two-thirds of its runs and the slot is a symptom — fix the network, bloat stops. Question 2 is the one I'd bet on for the quiet schemas. It's a well-known Debezium behaviour: the server's WAL advances globally, but a slot only confirms when its publication sees relevant changes. A low-traffic schema can therefore retain indefinitely while looking perfectly healthy — and the fix is a heartbeat, not a slot recreate. If that's what's happening on some of the 14, dropping and recreating them forever is treating the wrong thing.

Thinking completed 1. Does the slot advance if a sync fails partway? Depending on your connector version and configuration, the default behavior ties LSN commits to destination writes (e.g.

After loading data in the destination

) [Github Issue (closed): [source-postgres] not flushing lsn back to Postgres after sync]. If a sync aborts partway due to an intermittent network fault before offsets and state are successfully committed and persisted, the replication slot does not acknowledge that progress. Consequently, repeated sync failures will prevent

confirmed_flush_lsn

from advancing, forcing PostgreSQL to retain WAL segments dating back to the last successfully committed offset. The intermittent network failures you are seeing will directly contribute to WAL growth. 2. Low/no transaction volume, empty syncs, and Debezium heartbeats No, a sync that finds no changes will generally not advance the replication slot. By default, logical replication via

pgoutput

and Debezium only generates and processes events for tables configured in the publication. If global WAL moves on the server due to other active tables, but no transactions occur within a specific publication's scope: • Events outside the publication are filtered out, and no offset can be committed [Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. • The slot's

confirmed_flush_lsn

does not advance, causing the replication slot's retained WAL backlog to continuously grow [Documentation Troubleshooting Postgres Sources>; Forum Post: Postgres Source replication slot safe_wal_size only reset when a change occurs]. Is Debezium's heartbeat exposed in the source configuration? Yes. Airbyte directly exposes heartbeat capabilities in the Postgres source configuration under the CDC advanced settings [Documentation Troubleshooting Postgres Sources>; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]: •

Debezium heartbeat query (heartbeat.action.query)

: Configured to run an explicit write statement periodically [PostgresSourceDebeziumOperations.kt]. Is it the recommended fix? Yes, this is the official recommended fix for standalone/primary databases with low transaction volume [Documentation Troubleshooting Postgres Sources>]. To implement this: 1. Create a dedicated heartbeat table in the tracked schema:CREATE TABLE airbyte_heartbeat ( id SERIAL PRIMARY KEY, timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp, text TEXT ); 2. Add the heartbeat table to that schema's publication [Documentation Troubleshooting Postgres Sources>]:ALTER PUBLICATION publication_name ADD TABLE airbyte_heartbeat; 3. In the Airbyte Postgres source UI (under Advanced → Update Method → Read Changes using Write-Ahead Log (CDC) → Optional fields), set the Debezium heartbeat query to [Documentation Troubleshooting Postgres Sources>; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]:INSERT INTO airbyte_heartbeat (text) VALUES ('heartbeat'); (Alternatively, if write access cannot be provided, you can add a high-volume active table to the publication without selecting it for replication in Airbyte, though this increases log processing overhead [Documentation Troubleshooting Postgres Sources>].) Is there anything Airbyte-side that bounds slot growth? I'm sorry, but there's no mention in the knowledge sources of any Airbyte-side mechanism to bound or hard-cap WAL slot growth. Postgres retains WAL files as long as any replication slot's

confirmed_flush_lsn

/

restart_lsn

requires them. On the database server, configuring

max_slot_wal_keep_size

is the primary mechanism to protect disk space [Forum Post: Postgres Source replication slot safe_wal_size only reset when a change occurs]. However, note that if a slot exceeds

max_slot_wal_keep_size

, Postgres marks the slot as invalid/lost, which will cause Airbyte CDC to fail and necessitate a full table re-sync [Forum Post: Postgres Source replication slot safe_wal_size only reset when a change occurs]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

If the heartbeat query has been implemented, as designed, and the issue with a LSN / cursor that doesn't advance still persists & the reserved disk space keeps growing... what could be the issue?

Thinking completed If you have already implemented the heartbeat query and table as designed, but the LSN still does not advance and WAL disk consumption continues to grow, several known issues and connector mechanisms documented in the knowledge sources could explain this behavior: 1. Reading from a Read Replica Instead of a Primary Instance The connector's built-in

Debezium heartbeat query

only works when reading directly against a primary or standalone database where Airbyte has write access [Documentation Troubleshooting Postgres Sources>]. • If Airbyte is connecting to a read replica, write queries cannot be executed directly by Airbyte. • Solution: You must schedule heartbeat writes on the primary database (e.g., using the

pg_cron

extension to periodically write to a heartbeat table) so that the changes replicate downstream through WAL to the replica [Documentation Troubleshooting Postgres Sources>]. 2. Startup Advancement Delay (After loading Data in the destination) Under the default

LSN commit behavior

(

After loading Data in the destination

), Airbyte delegates slot advancement logic to ensure data is safely written before acknowledging the offset [PostgresSourceConfigurationSpecification.kt]: • The replication slot LSN is actually committed on the startup of the subsequent sync rather than at the end of the current sync [Github Issue (closed): [source-postgres] not flushing lsn back to Postgres after sync]. • Because Debezium's internal parameter

flush.lsn.source

is set to

false

, the database WAL will not immediately reflect the flush at the conclusion of a single sync [Github Issue (closed): [source-postgres] not flushing lsn back to Postgres after sync]. At least two successful, consecutive syncs are required to observe the slot advancing [Github Issue (closed): [source-postgres] not flushing lsn back to Postgres after sync]. 3. Connector Bugs in Specific source-postgres Versions Several specific versions of the Postgres connector have documented bugs that prevent LSN advancement or cause unbounded WAL growth: • Race Condition in

advanceLsn()

and Missing Snapshot LSN Fallback: A known bug existed where

advanceLsn()

closed the replication stream immediately after sending

forceUpdateStatus()

, dropping the update before PostgreSQL applied it. In addition, when

lsn_proc

and

lsn_commit

were absent from the Debezium offset (such as after snapshot phases), the advance routine silently returned without advancing the slot (resolved via pull request #79671) [fix(source-postgres): fix CDC replication slot LSN not advancing]. • PgJDBC Regression (Versions 3.6.19 through 3.6.33): PgJDBC versions 42.7.0+ introduced an issue that caused driver-level LSN flushes to misalign with Airbyte's state when non-publication tables have high traffic, breaking subsequent incremental CDC syncs and offset commits (resolved in version

3.6.34

/

3.7.2

) [Github Issue (open): [source-postgres] CDC Not Working With Connector Version 3.6.22; 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>]. • Row vs. Commit LSN Ordering: A bug in connector versions prior to

3.8.6

inverted row and commit LSN values in CDC progress comparisons, which could make a later commit appear to precede an earlier position if the row LSN decreased [fix(source-postgres): preserve row and commit LSNs in CDC progress comparisons]. 4. WAL Retention by Other Consumers PostgreSQL maintains WAL logs based on the oldest active requirement across the entire cluster: • If another logical replication slot, physical replication standby, or secondary consumer (e.g., another replication tool) exists and is lagging or inactive, Postgres will not clear WAL files even if Airbyte's slot successfully advances its

confirmed_flush_lsn

[Forum Post: Posrgres Source: CDC Replication issues; Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. • Check

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;

to confirm whether Airbyte's slot is the specific one holding back the WAL. 👍 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 Are you saying that if I have 3 environments (dev, qa, prod) and the connections having this problem have migrated between these environments... sharing the same replication slot (as they technically represent the exact same connection as in qa living in dev and vice-versa) -- that the inactive connection we disabled in dev is still impacting the connection that was moved to QA?

Thinking completed No, that is not what is happening if they share the exact same replication slot name on the Postgres database. Point 4 refers to having multiple separate replication slots (e.g.,

slot_dev

,

slot_qa

,

slot_prod

, or slots for other replica standbys) on the same Postgres instance. If another distinct slot is abandoned or lagging, Postgres will retain all WAL required by that other slot, regardless of what your active slot does [Forum Post: Posrgres Source: CDC Replication issues; Forum Post: Stack Syncs and Automated Full Resyncs on CDC]. However, having Dev, QA, and Prod share the exact same slot creates a different critical failure mode: 1. A Disabled Airbyte Connection Does Not Run If a connection is disabled in Dev, it is not connecting to Postgres, meaning the Dev Airbyte container is not actively holding open a WAL sender or issuing commands. 2. State Mismatch & Overwrite Between Environments Airbyte requires that only one single connection uses a given replication slot [Documentation Cloud SQL for PostgreSQL>]. • A replication slot on Postgres maintains only one

confirmed_flush_lsn

and

restart_lsn

. • Each Airbyte environment maintains its own separate internal sync state (the Debezium offset/LSN). • When a connection runs, it verifies its saved state against the slot's

confirmed_flush_lsn

. If QA was migrated from Dev without an exact match of the connection state, or if Dev ran even once after QA, their offsets diverge [Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. • If Airbyte finds that the slot has advanced past its saved state or cannot reconcile the offset position, it fails to commit LSN updates properly [fix(source-postgres): fix CDC replication slot LSN not advancing; Github Issue (open): [source-postgres] CDC sync is making the confirmed flush LSN to a value saved state LSN>]. 3. How to Verify on Postgres To confirm exactly what is holding back WAL on your PostgreSQL server, run:

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn 
FROM pg_replication_slots;

• If you see only one slot: That single slot is the bottleneck. The root cause is inside the active connection (such as the heartbeat not generating publication events, a bug in the connector version preventing

advanceLsn()

, or an offset-to-slot state mismatch) [Documentation Troubleshooting Postgres Sources>; fix(source-postgres): fix CDC replication slot LSN not advancing]. • If you see leftover, separate slots from Dev or QA: Any slot that is

active = false

and has an older

restart_lsn

will cause Postgres to retain WAL files across the entire database, preventing disk cleanup even if the active slot is advancing normally. 👍 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 there any chance that long running transactions in postgres could just be pinning the replication slots & causing the wal growth?

Thinking completed The knowledge sources do not contain enough information to give you a confident, Airbyte-specific answer on this. I can tell you what the sources do and don't cover: What the knowledge sources confirm The sources confirm that WAL growth in the context of Airbyte CDC is attributed to two mechanisms: 1. Syncs failing before LSN is committed — the slot does not advance [fix(source-postgres): fix CDC replication slot LSN not advancing] 2. Low/no transaction volume in a publication — the slot has nothing to confirm against [Documentation Troubleshooting Postgres Sources>] What the sources do not cover Long-running transactions pinning replication slots (

catalog_xmin

/

xmin

being held back by open transactions) is a well-known PostgreSQL mechanism, and the

ReplicationSlotManager

code in the connector does query

xmin

and

catalog_xmin

from

pg_replication_slots

[ReplicationSlotManager.kt], but the knowledge sources do not explicitly discuss this as a cause of WAL growth in Airbyte deployments. What I'd suggest checking directly on Postgres Since this is a general PostgreSQL question rather than an Airbyte-specific one, I'd recommend checking outside of these sources:

-- Check for long-running transactions
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query_start IS NOT NULL
ORDER BY duration DESC;

-- Check xmin/catalog_xmin on your slots
SELECT slot_name, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

If

catalog_xmin

or

xmin

on a slot is very old and correlates with a long-running transaction, that is a PostgreSQL-level issue independent of Airbyte, and no Airbyte-side fix (heartbeat or otherwise) will resolve it. 👍 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 Essentially I'm wondering if the airbyte syncs would work fine if the replication slots they adhere to weren't being blocked from "draining" the write ahead log by a long running transaction. We know airbyte should never block a transction in postgres.. But I don't think the inverse is true. And if that's the cause.. I wonder if our issues with airbyte replication slots always needing to be dropped / recreated to deal with wal growth is a result of our airbyte connections being held back long enough by a transaction that they can never drain the wal because the connection times out before it can chew through 200 gigs of backlog changes to the wal & begin its process to sync via the replication slot & "drain" the wal.

Thinking completed The knowledge sources do not contain enough information to confirm or deny the specific mechanism you are describing (a long-running transaction pinning `xmin`/`catalog_xmin` on the slot, blocking WAL drain, causing the backlog to grow beyond what Airbyte can chew through before timeout). That is a PostgreSQL-level interaction the sources do not explicitly cover. However, the sources do confirm the downstream half of your theory very clearly, and it is coherent: What the sources do confirm The initial load timeout is a hard ceiling. The Postgres CDC connector has a configurable

initial_load_timeout_hours

with a maximum of 24 hours [PostgresSourceConfigurationSpecification.kt]:

min: 4 hours, max: 24 hours, default: 8 hours

If a sync cannot complete its initial load or catch up on CDC events within that window, it times out. There is a documented open issue specifically about this — large tables exceeding the 24-hour initial load timeout, with retries restarting from scratch each time, causing WAL bloat and repeated failures [Github Issue (open): Postgres CDC (v3.7.2): option to skip initial snapshot]: > "the mandatory initial snapshot routinely exceeds the 24h initial load timeout and retries restart from scratch, causing WAL bloat and repeated failures" This matches your described failure mode almost exactly — except the trigger in that issue is table size, whereas in your case the trigger would be a transaction-induced backlog. LSN advancement only happens on successful sync completion. If the sync times out before completing, the slot does not advance, the WAL is not drained, and the next retry starts from the same position [Github Issue (closed): [source-postgres] not flushing lsn back to Postgres after sync; fix(source-postgres): fix CDC replication slot LSN not advancing]. This creates the exact compounding cycle you are describing: 1. Long-running transaction pins WAL, backlog grows 2. Airbyte attempts sync, cannot drain 200 GB backlog within timeout 3. Sync times out, LSN does not advance 4. Next sync starts from the same position, backlog is now even larger 5. Drop and recreate slot is the only escape What remains uncertain Whether the root cause of the 200 GB backlog is specifically a long-running transaction pinning `xmin`/`catalog_xmin` versus the other mechanisms already discussed (failed syncs, low-volume publications, connector bugs) cannot be determined from the knowledge sources. You would need to correlate:

-- Is xmin/catalog_xmin old on the affected slots?
SELECT slot_name, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

-- Is there a long-running transaction matching that xmin age?
SELECT pid, xmin, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

If

catalog_xmin

on your slots is old and correlates with a long-running transaction, your theory is the correct diagnosis — and no Airbyte-side fix (heartbeat, slot recreate, or otherwise) will permanently resolve it without addressing the transaction on the Postgres side first. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.