Can't restore backup from deleted postgres database

Google Cloud Database
1

Hi,

I deleted a Postgres database but now I’d like to recover a backup from it. The backup is still there, I can see it with the CLI:

gcloud sql backups list --project=PROJECT_ID -i -
ID WINDOW_START_TIME ERROR STATUS INSTANCE
[ ... snip ...]
1670050800000 2022-12-03T07:00:00.000+00:00 - SUCCESSFUL OLD_DATABASE_NAME

This blog post seems to imply it should be possible to restore the backup to a new Postgres instance. But that doesn’t work. From the CLI, I see a 503 error code:

$ gcloud sql backups restore 1670050800000 \
> --restore-instance=NEW_DATABASE_NAME \
> --project=PROJECT_NAME \
> --backup-instance=OLD_DATABASE_NAME

All current data on the instance will be lost when the backup is restored.
Do you want to continue (Y/n)? y
ERROR: (gcloud.sql.backups.restore) HttpError accessing <https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_NAME/instances/NEW_DATABASE_NAME/restoreBackup?alt=json>: response: <{'vary': 'Origin, X-Origin, Referer', 'content-type': 'application/json; charset=UTF-8', 'content-encoding': 'gzip', 'date': 'Sat, 03 Dec 2022 12:26:23 GMT', 'server': 'ESF', 'cache-control': 'private', 'x-xss-protection': '0', 'x-frame-options': 'SAMEORIGIN', 'x-content-type-options': 'nosniff', 'alt-svc': 'h3=":443"; ma=2592000,h3-29=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000,quic=":443"; ma=2592000; v="46,43"', 'transfer-encoding': 'chunked', 'status': 503}>, content <{
"error": {
"code": 503,
"message": "Service temporarily unavailable.",
"errors": [
 {
"message": "Service temporarily unavailable.",
"domain": "global",
"reason": "serverException"
 }
 ]
 }
}

And from the “Activity” tab on the dashboard, I see a different 500 error:

Error message Internal error (HTTP 500): RPC error looking up backup run for source instance

Has anyone been able to do this successfully?

2

What version of the SDK are you running? I just tested this using (I’m on the latest version, 410.0.0) and it doesn’t allow me to list the backups after the instance has bee deleted (ERROR: (gcloud.sql.backups.list) HTTPError 403: The client is not authorized to make this request.). This is the expected behavior of backups as it should be tied with the instance. The post you referenced is almost 2 years old and I’m wondering if you’re using and older version of gcloud which uses an older version of the API and allows you to query the backups, but the same gcloud is also trying to use an older/deprecated API for restores as well (v1beta4 as per your output) and hence it not working.

EDIT: oh nvm, I see you’re using the - wildcard, which I didn’t try…

EDIT 2: okay, so if you look at the blog post you referenced, the user there was able to lookup backups by referencing the deleted instance’s name. Something I was not able to do (hence the error I got). You’re listing the backups with a wildcard ‘-’, which may be listing backups that GCP has yet to clean up. Now onto the restore command, you have to reference the instance the backup is from, but as you can no longer reference it (as the blog post was able to ~2 years ago), you now get the 503 error. I think currently it’s working as it was originally intended and I don’t think you can restore from a backup of a deleted instance.

3

Hi,

I’m interested in having a disaster recovery plan in case of a deleted instance. I’m also getting 503 when running

gcloud sql backups restore <backup-id> --restore-instance=<new-instance-id> --backup-instance=<deleted-instance-id>

Is that the expected behavior? how could I spin up a new instance using an old backup of a deleted instance otherwise?

4

Hello, we are trying to restore a SQL backup.

We have two projects

  • staging

  • pcapi-perf-96c84080

  • production

  • rebuild-staging-20231018-f4433f

We want to restore a backup made on production/rebuild-staging-20231018-f4433f on staging/pcapi-perf-96c84080

This is what we have tried :

gcloud beta sql backups restore 1697637392218 --restore-instance=pcapi-perf-96c84080 --backup-instance=rebuild-staging-20231018-f4433f --project=staging --quiet

and

gcloud sql backups restore 1697637392218 --restore-instance=pcapi-perf-96c84080 --backup-instance=rebuild-staging-20231018-f4433f --project=staging

Both end up in 503:

{
“error”: {
“code”: 503,
“message”: “Service temporarily unavailable.”,
“errors”: [
{
“message”: “Service temporarily unavailable.”,
“domain”: “global”,
“reason”: “serverException”
}
]
}
}

If we remove --project, then we have a ERROR: (gcloud.sql.backups.restore) HTTPError 403: The client is not authorized to make this request.

Did you solve this out ?

5

Hello!

I found out that is not possible to restore the backup to an existing instance. It gave me 503 too.
It might work if the restored instance is a fresh cloud sql instance.(don’t have time to test it though and be sure about it)

My question back then was related to a deleted instance and whether is possible to restore a backup after the cloud db instance gets deleted. This is not available for now and that is why it gives 503.

6

I found that using the https API, it does not produce a 503, downside is it become async. Hopefully you can make it synchronous, this is how I did in our action: https://github.com/pass-culture/infrastructure/blob/60b6594/.github/workflows/rebuild-staging.yml#L238C1-L248C151

  • name: Restore snapshot of temp instance to staging instance
    run: |
    echo STAGING_RESTORE_OPERATION_NAME=$(curl -sS -X POST
    -H “Authorization: Bearer $(gcloud auth print-access-token)”
    -H “Content-Type: application/json; charset=utf-8”
    -d ‘{“restoreBackupContext”:{“backupRunId”: ${{ env.TEMP_INSTANCE_LAST_BACKUP_ID }},“project”: “${{ env.TEMP_INSTANCE_PROJECT }}”,“instanceId”: “${{ env.TEMP_INSTANCE_NAME }}”}}’
    “https://sqladmin.googleapis.com/v1/projects/${{ env.STAGING_INSTANCE_PROJECT }}/instances/${{ env.STAGING_INSTANCE_NAME }}/restoreBackup” | jq -r .name) >> $GITHUB_ENV

  • name: Wait for database restore staging instance operations to finish
    run: gcloud sql operations wait ${{ env.STAGING_RESTORE_OPERATION_NAME }} --project=${{ env.STAGING_INSTANCE_PROJECT }} --timeout=1200 --quiet

I assume the 503 is a bug in gcloud cli and should be fixed soon or later.