I'm using Airbyte to send a PostgreSQL source to BigQuery desination using CDC incremental. I need to upgrade the PostgreSQL source server, which will drop the replication slot. Is there any way to gracefully resume Airbyte CDC in PostgreSQL after dropping the replication slot for a major version upgrade?

Based on the knowledge sources provided, I can offer some guidance on resuming Airbyte CDC in PostgreSQL after dropping the replication slot for a major version upgrade. However, it's important to note that there isn't a specific "graceful" method mentioned for this exact scenario. Here are the steps you can take: 1. Upgrade your PostgreSQL server as planned, which will drop the replication slot. 2. After the upgrade, you'll need to recreate the replication slot and publication for Airbyte. According to the [Postgres Troubleshooting documentation](https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting#under-cdc-incremental-mode-there-are-still-full-refresh-syncs), you should:

sql    CREATE PUBLICATION airbyte_publication FOR TABLES IN SCHEMA public;    SELECT pg_create_logical_replication_slot('airbyte_slot', 'pgoutput');

Make sure to adjust the schema name if you're not using the public schema. 3. In Airbyte, you'll need to reset the connection and perform a full resync. This is because the CDC state will be lost when the replication slot is dropped. 4. After the full resync, subsequent syncs should resume incremental CDC as normal. It's worth noting that this process will effectively restart your CDC process from scratch, so there may be a period where you'll need to sync all data again. Additionally, for future upgrades, you might want to consider the recommendation from the [Upgrading to Destinations V2 documentation](https://docs.airbyte.com/release_notes/upgrading_to_destinations_v2#upgrade-paths-for-connections-using-cdc): "You can optionally dual write, but this requires resyncing historical data from the source. You must create a new Postgres source with a different replication slot than your existing source to preserve the integrity of your existing connection." This approach could potentially allow you to maintain continuity during upgrades, though it would require setting up a parallel connection. Remember to monitor your syncs after the upgrade to ensure they're functioning as expected with the new PostgreSQL version.

@Sean Kibler Did you find a solution for this, or did you have to run a full refresh?

@William Whelan I have not found a solution, I haven't performed the upgrade yet but I'm anticipating having to run a full refresh. My understanding is that Airbyte does not have the granular control available to tell it to just resume from wherever the replication slot LSN is. Debezium (which Airbyte uses for PostgreSQL) has instructions on handling this but I'm not sure they are possible to perform in Airbyte. Perhaps I just haven't done enough digging. Here's the relevant Debezium documentation: https://debezium.io/documentation/reference/stable/connectors/postgresql.html#upgrading-postgresql Even if the Debezium recommended procedure is possible it still puts me in a hard decision between increased downtime (waiting for our sync to finish) or minimized downtime but require a full refresh which will be costly and time consuming.