Question

How to export data from DigitalOcean to Google Cloud Services (BigQuery)?

Hi!

Is there a way to send data from DigitalOcean (DO) to Google Cloud Services (GCS) by standard means (whithout intermediate soft)?

  1. I followed step by step Datastream setup with DO as a source for BigQuery as a destination. All intermediate tests passed (including connection test) but when i actually start Datastream it fails due to some unspecified connection problem. No specific error code shown.
  2. I also tried to migrate DO to GCS by standard migration service of GCS and it also fails everytime (failed on full dump).

I see there’s a number of 3rd party integration software to perform such task which drives me into thinking DO might not be working with GCS by default. Are DigitalOcean and Google Sloud Service compatible at all?

Submit an answer
Answer a question...

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Arseniy • December 21, 2022

I created empty database and set up datastream and it worked fine (status “working”). The moment i added table into it datatstream failed with the following error “The log file position has been lost. This might be due to a long disconnect or error, a purge on the log, or a short log retention policy. This stream will need to be recreated.”

So i pretty much sure it’s about expire_logs_days set to 0 by default. I just can’t understand how to change it.

Arseniy • December 21, 2022

Hi!

I have a hypothesis the issue is due to expire_logs_days=0 in DO by default.

Is there any way to set it =7 days as it’s required on GCS side?

When trying “set global expire_logs_days=7;” on MySQL Workbench i get an error “Error Code: 1227. Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation”

Ho do i get those permissions on it there a way to change expire_logs_days parameter at all in DO?

Bobby Iliev
Site Moderator
Site Moderator badge
• December 15, 2022

Hi there,

The Managed DigitalOcean databases have logical replication enabled and I’ve used them for some CDC pipelines.

One thing to keep in mind is that because the doadmin user is not a superuser, you will not be able to create a publication for all tables.

But you can create a publication with the tables you want to replicate by explicitly specifying them, eg:

CREATE PUBLICATION datastream FOR TABLE table1, table2;

Source.

Do you see any errors in the DigitalOcean logs after you try to start the data stream?

I could suggest following the GCP guide and making sure that you are not hitting any of the limitations that they’ve mentioned here:

https://cloud.google.com/datastream/docs/sources-postgresql

Let me know how it goes!

Best,

Bobby