Upgrading the Postgres Source Connector after v0.4.18

  • 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.

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

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

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.