Creating this ticket on behalf of Orfium, as they are experiencing issues using the Postgres Source Connector. Below is the detailed breakdown of the issue Giannis from Orfium shared, as well as the logs. I
@gianniskod can you fill out the additional details below.
- Is this your first time deploying Airbyte?: No
- OS Version / Instance:
- Memory / Disk:
- Airbyte Version: What version are you using now?
- Source name/version: Posrgres 0.44.4 / 1.0.5
- Destination name/version: Snowflake?
- Step: The issue is happening during sync.
Practically, there is one source Postgres Aurora Database in which we have already connected Fivetran and now we are trying to connect Airbyte also. Fivetran connector has been set up using a replication slot with the ‘test-decoding’ plugin. The important note here is that using this plugin, you don’t have to set up any publication for specific tables, and instead, it reads all the published changes through the WAL files and Fivetran chooses which tables to replicate, based on the schema configuration that each connector has (For more information check here and here).
Now for Airbyte, we have created a new replication slot using
**pgoutput** plugin connected with a publication slot that has registered for 2 tables. One of these tables was also configured with Fivetran.
So our problem is that after completing the initial sync on Airbyte all the respective syncs have not fetched any data from the replication slot, even if there were thousands of them. From our investigation, the replication slot seems that has been stuck on the same LSN pointer for the last 15-20 days at least.
Of course, we have tried to increase the initial time-out change, but we didn’t see any change. Our last resort is to drop and recreate the replication slot along with the publication slot and see if that fixes the issue. (below you can find logs from one sync to check if there are any issues).
Here the problem emerged after upgrading the Postgres Connector from 0.44.4 to 1.0.5 version. From the provided logs you can check that the first problematic sync was related to the missing configuration of the connector since there were new settings that needed to be configured after the upgrade. So after applying the changes on the connector, we got 1 successful sync, and from that point, all the subsequent syncs started working in an unexpected way.
Practically, the connector instead of trying to identify the changes from the last sync, it started to scan all the tables rows like it was a reset operation. We left one sync like this to complete and then we noticed that all the next’s had the same behavior.
As a result, we
- Rollbacked the connector’s version to 0.44.4
- Reset the connection + the next sync to full resync the tables.
- Applied one regular sync after the full resync, and again it read all the raws of the tables.
- Attempted more sync, and it worked as expected.
I know that this scenario seems a little bit complex to replicate and for sure there may be steps that weren’t completed with the expected process, but our main questions here are:
In order to upgrade our Postgres Connectors, do we have to create new connections? Or it should work with the already configured?
If we have to create a new connection, should we do it with the same connector or we should create a new one?