This is really annoying with Google Cloud SQL. A backup is being created every night. We want to run integration tests on demand with latest data. Why can’t we just download a backup file directly, to spin up a test DB in our infra?
Why do we have to spin up a SQL instance, import the backup there, to be able to extract a DB dump to storage and download it from there, when the backup is already there? It’s absurd! And it’s our data.
We could spin up a SQL instance for the duration of the tests obviously, but
a.) that is of course in GCP interests as it will cost more
b.) it’s operationally more challenging as we’re not allowed to store the gcloud tokens in a github action as that is notoriously unsafe.
We really just would like to download the backup file.
The backup functionality provided by Cloud SQL uses disk snapshot, which is internal to GCP, hence it cannot used outside GCP environment or accessed directly. If you want use the database backups outside GCP, Cloud SQL Export and Import functionality should be used.
Thanks Kiran for reply. Ewok, It would be good if you can elloborate your option, as you can create a Clone of instance for the duration of the test, which will be similar cost when you are testing outside the GCP?
I see. @kshenoy I understand. However, exporting to backup is done every night already. Now if I have to export again to a SQL dump file from the prod DB (it takes 10 minutes or so in our case), even if the consensus is that it is not affecting the prod DB, it’s not something we like to have to do on a live DB.
Because what you are suggesting requires to extract the SQL dump from the live DB, which takes x time, and then import it in the target, which also takes x time, and I am afraid this is becoming too long.
Now to spin up an instance for the duration of the test, and restore from backup -
Yes, theoretically that is possible, although
I really don’t like your API, because if you have to state the source instance and a dest instance. This is error prone, I would like to 100% avoid the case that by mistake someone sets the dest instance to the live instance (which is the source!) and have the catastrophic situation of restoring the backup into the running instance. Having the restore directly from backup ID and not having to mention the source at all would be safer.
To do this from github actions, we have to set the gcloud token into the github action, and we are internally forbidden to do that, as it is notoriously unsafe. The alternatives are not many, as I see it, maybe using OpenID connect or something but I haven’t been able to wrap my head around how to do that safely yet. I thought OpenID was rather something for frontends.