Backups and Restoring Data

This page documents how our services hosted in GCP are backed up, and how to restore data from a backup.

Backups

Our services are backed up in two ways, through the GCP native backup configuration and through a scheduled job which creates exports of the database and stores them in GCP buckets in a separate project.

Cloud SQL Backups

The documentation for Google Cloud SQL backups can be found here. For all of our services using Cloud SQL, the SQL instances should have automatic backups enabled, and they should retain at least 7 backups.

Scheduled Database Exports

The infrastructure project Database Backup Scheduled Jobs deploys a GitLab scheduled job to export data from our databases and store them in GCP buckets. The Google Cloud SQL Backup tool is used to perform these backups.

The backups can be viewed in the SQL Backup Production project in GCP.

Restoring Data

If a Cloud SQL Backup is available for the time frame you want to restore to, that backup should be used. Cloud SQL backups are kept for 7 days, if the time frame you want to restore to is longer than 7 days a Database Export must be used instead.

Restoring from Cloud SQL Backups

To restore from the native Cloud SQL backups follow the process outlined in the GCP documentation.

Additional information about the restore process can be found here. Note that the SQL instance is restarted as part of the restore process, so some service instability is expected when the restore operation is carried out. It may be necessary to notify users of this instability if the service being restored is undergoing active use.

Restoring from Database Exports

First the correct database export should be found in the SQL Backup Production project, and the gs:// URL to the SQL export should be noted.

Eached backed up SQL instance has a storage bucket, which is named:

live-<gcp-project-id>-<sql-instance-id>

Backups are named with the date that the backup was created, and should be found in a folder matching the database's name in the service's bucket.

The database exports are expecting to run from a blank slate, so before importing the data the existing databases should be deleted from the SQL instance. Navigate to the SQL instance in the GCP console, select 'Databases' from the navigation bar and delete all the databases in the instance. Alternatively, the gcloud utility can be used to delete databases using the command:

gcloud sql databases delete <DATABASE> --instance=<INSTANCE>

Once this is done, recreate each deleted database (using the same name as it had previously) in the instance:

gcloud sql databases create <DATABASE> --instance=<INSTANCE>

You can then restore data from a database export following the process outlined in the GCP documentation.

Finally, many deployments have a post-deployment configuration which may need to be re-run. Instructions for this will be found in the readme for the deployment repository of the service. In most cases, the specific instructions required will depend on the service being restored, but will typically be the configuration of database users and database access rights.