postgres backup restore how-to

This page describes how to restore a Cloud SQL Postgres database from one of the automated daily backups that Nais takes of your instance.

Cloud SQL keeps the last seven daily backups by default. You can restore a backup either in-place (overwriting the current instance) or into a separate instance (to inspect the data without touching production).

Restoring in-place overwrites all current data

Restoring a backup to the same instance returns the database to the state it had when the backup was taken. All data written after that point is lost, including any point-in-time recovery logs. The instance is unavailable while the restore runs, so your application will have downtime.

Info

This guide covers the daily backups stored in GCP. The separate on-prem disaster backup is only used in case of a catastrophic failure in GCP. Contact the Nais team on Slack if you need data restored from it.

Before you begin ¶

  • Install and authenticate the gcloud CLI, or use the Google Cloud Console.
  • naisdevice connected.
  • You need access to the GCP project where the instance runs. Your team has full access to view and restore backups for its own instances.

The instance name defaults to your application name. If you are unsure of the instance name or project, find them in the Cloud SQL dashboard or in your nais.yaml.

When using gcloud, set the project you are working in:

bash

Find the backup to restore ¶

List the available backups and note the ID of the one you want:

bash

Example output:

Plaintext

Go to the instance's backups page and note the backup you want to restore:

Plaintext

Restore in-place (same instance) ¶

Use this when you want to roll the production database back to an earlier state.

Warning

This overwrites all current data on the instance and causes downtime. Consider stopping the application first so it does not write to the database during the restore:

bash
bash

Confirm the prompt to start the restore. The command returns once the operation is queued; you can follow its progress with:

bash
  1. Go to https://console.cloud.google.com/sql/instances/<instance-name>/backups.
  2. Find the backup you want and click Restore.
  3. In Choose restore destination, select Overwrite the source instance.
  4. Enter the instance name to confirm, then click Restore.

Start the application again once the restore completes:

bash

Verify that the application is running and that the data is as expected.

Restore into a separate instance ¶

Use this when you want to inspect or extract data from a backup without affecting the running database — for example to recover a few accidentally deleted rows.

Info

The target instance must already exist and have the same Postgres major version and equal or greater storage than the source. Restoring does not create a new instance. If you only need a copy to look at the data, cloning the instance is usually simpler, since cloning creates a new instance for you.

Restore a backup taken from the source instance into an existing target instance:

bash
  1. Go to https://console.cloud.google.com/sql/instances/<source-instance-name>/backups.
  2. Find the backup you want and click Restore.
  3. In Choose restore destination, select the existing target instance.
  4. Confirm to start the restore.

Warning

The target instance is a plain Cloud SQL instance, not managed by Nais. It will not have the users, secrets and certificates that Naiserator generates. To connect to it, follow Connect to a cloned database.

Troubleshooting ¶

If a restore fails, the current data on the instance is left unchanged. Check the failed operation for the reason:

bash

Common causes are a target instance with a smaller disk or a different Postgres major version than the backup.

Further reading ¶