Upgrading the Postgres Source Connector after v0.4.18

Connector Questions & Issues
, , , ,
1
  • Is this your first time deploying Airbyte?: No
  • OS Version / Instance: Ubuntu
  • Memory / Disk: JOB_MAIN_CONTAINER_MEMORY_REQUEST=2Gi, JOB_MAIN_CONTAINER_MEMORY_LIMIT=4Gi
  • Deployment: Kubernetes v1.22.9
  • Airbyte Version: v0.39.17-alpha
  • Source name/version: Postgres v0.4.18
  • Destination name/version: Snowflake v0.4.20
  • Step: Sync
  • Description: We recently upgraded Airbyte to the latest version and wanted to upgrade our connectors to the latest versions as well. We started at Postgres v0.4.14 and had no issues until v0.4.19. There were a few issues there:
  1. We got this error in the normalization: 002108 (22000): SQL compilation error: cannot change column EXPIRY from type VARCHAR(16777216) to TIMESTAMP_NTZ(9) Seems like this version starts to recognize timestamps, but all of our previous values are varchars.
  2. Even on sync failure, the cursor was updated. It was clear because the format of the cursor changed, from “2022-06-22T12:57:03.460945Z” to “2022-06-22T13:01:14.158140”. So some data would be missed if I did not reset the cursor after.
  3. I could not downgrade because the cursor format changed. I had to manually edit the state table to return the cursor to its previous format so that v0.4.18 would work again.

Seems like issues 2 and 3 are avoidable if there is some way to get around issue 1. Do you have any recommendations for how to complete this upgrade? I know we could just reset all of our data, but we have hundreds of tables, tens of connections, and some tables > 1TB which are very difficult to get working and we don’t want to go through that again.

I thought that the cursor would not be updated if a sync failed, yet it seems like this happened. Is there a way to ensure that the cursor will not be updated unless the sync succeeds?

For reference, I have attached the logs and a screenshot of the UI for this testing period. I have also attached a table showing the cursor after each sync for each version.

Screen Shot 2022-06-22 at 3.37.01 PM
Screen Shot 2022-06-22 at 3.37.01 PM1130×336 47.6 KB

Screen Shot 2022-06-22 at 3.07.21 PM
Screen Shot 2022-06-22 at 3.07.21 PM3590×1094 217 KB

0418-2.txt (200.2 KB)
0419.txt (492.4 KB) (this is the one that failed; I cancelled the second attempt)
0418.txt (200.7 KB)
0417.txt (200.7 KB)
0416.txt (200.8 KB)

Thanks for your help!

Kind regards,

Andrew

2

Hey have created an issue for this https://github.com/airbytehq/airbyte/issues/14042

3

Thanks! I’m happy to leave our connectors at Postgres 0.4.18 for now, but I would like to upgrade sometime in the future.

4

Hi there from the Community Assistance team.
We’re letting you know about an issue we discovered with the back-end process we use to handle topics and responses on the forum. If you experienced a situation where you posted the last message in a topic that did not receive any further replies, please open a new topic to continue the discussion. In addition, if you’re having a problem and find a closed topic on the subject, go ahead and open a new topic on it and we’ll follow up with you. We apologize for the inconvenience, and appreciate your willingness to work with us to provide a supportive community.