https://linen.dev logo
Join Slack
Powered by
hi all! I’ve been trying to sync my postgres DB to...
# replication-troubleshooting
s
hi all! I’ve been trying to sync my postgres DB to BigQuery using GCS staging approach. I’ve encountered “No timezone information found” error on my cursor column. It is of type 
timestamp
in my Postgres DB. This matches what another user was experiencing in September of this year: https://github.com/airbytehq/airbyte/issues/17319 I created a small sub-table of the data i want to sync in postgres and I changed it to 
timestamp with time zone
and it currently seems to be syncing using the BigQuery destination (however I changed it the beta denormalized version). It is currently syncing okay. I don’t really want to have to change the data type for the main table as it is quite large. Has anyone else experienced this problem before?
u
Hello Sean Soutar, it's been a while without an update from us. Are you still having problems or did you find a solution?
s
hey @Marcos Marx (Airbyte) thanks for following up. I had to put this on the back-burner but I am going to look at it again today/Monday so I’ll report back.
🙏 1
Hi @Marcos Marx (Airbyte) 👋 I had another go today and this is the crux of the issue I think.
Copy code
2022-12-16 06:57:49 ERROR c.n.s.DateTimeValidator(tryParse):82 - Invalid date-time: No timezone information: 2017-01-30T17:29:43.822133
2022-12-16 06:57:49 ERROR c.n.s.DateTimeValidator(tryParse):82 - Invalid date-time: No timezone information: 2017-01-30T17:29:43.822133
2022-12-16 06:57:49 ERROR c.n.s.DateTimeValidator(tryParse):82 - Invalid date-time: No timezone information: 2017-01-30T17:29:43.822133
I am using a cursor called 
entry_time
that is of type 
timestamp
The sync mode is incremental append Source: Postgres database, I am only syncing a single table Destination: Big Query using GCS staging (tried both the standard and the Big Query denormalized variant). I’ve tried both with and without normalization enabled but both fail their syncs I actually noticed a different error today when the transformation is disabled
Copy code
2022-12-16 06:53:51 [43mdestination[0m > Exception while starting consumer
Stack Trace: com.amazonaws.services.s3.model.AmazonS3Exception: Forbidden (Service: Amazon S3; Status Code: 403; Error Code: 403 Forbidden; Request ID: null; S3 Extended Request ID: null; Proxy: null), S3 Extended Request ID: null
This seems a bit strange as I am not sure why S3 is being used if this connector uses google storage to dump the records before uploading to BigQuery. Any help would be greatly appreciated as I am very keen to get a proof of concept going ASAP. Thanks!
Update: I managed to do a small sync of 20 records using standard inserts instead of GCS staging without any transformations Postgres connector: 1.0.34 Big Query: 0.2.3
s
Hey @Sean Soutar, Thanks for the question, sorry to hear the sync isn’t working with GCS staging regularly. Are you still experiencing this problem? As far as the question above about S3, this issue provides some additional context: https://github.com/airbytehq/airbyte/issues/14135 tldr; we use the same library to handle s3 and gcs, the error implies your gcs service account may not have the right permissions. Let me know if this helps 😉 Thanks for being patient.
s
hey @Saj Dider (Airbyte) sorry for the late response! I eventually got it working party parrot
There were a few issues at play but the main one was that the prefix I defined for the objects in GCS staging was invalid. I am not sure if this has been fixed in future versions where it alerts a user to an invalid prefix but once i corrected that then the objects were uploaded to cloud storage successfully
75 Views
Open in Slack
PreviousNext
Powered by