SaFi Bank Space : Cloud PostgreSQL backups

Backups help you restore lost data to your Cloud SQL instance. Additionally, if an instance is having a problem, you can restore it to a previous state by using the backup to overwrite it. Enable automated backups for any instance that contains necessary data. Backups protect your data from loss or damage.

Enabling automated backups, along with transaction logging, is also required for some operations, such as clone and replica creation.

Costs

By default, for each instance, Cloud SQL retains seven automated backups, in addition to on-demand backups. You can configure how many automated backups to retain (from 1 to 365). A lower rate is charged for backup storage than for other types of instances.

Cloud SQL doesn't take a backup of an instance if you stop or delete the instance. In case you delete an instance, the data is preserved for 4 days.

The pricing page for more information.

Backup and restore operations can't be used to upgrade a database to a later version. You can only restore from a backup to an instance with the same database version.

Backup size

Cloud SQL backups are incremental. They contain only data that changed after the previous backup was taken. Your oldest backup is a similar size to your database, but the sizes of subsequent backups depend on the rate of change of your data. When the oldest backup is deleted, the size of the next oldest backup increases so that a full backup still exists.

Types of backups

On-demand backups
- Can be done any time
- Can be done for any instance, whether the instance has automatic backups enabled or not.
- Not automatically deleted the way automated backups are => can have a long-term effect on your billing charges.
Automated backups
- Taken daily, within a 4-hour backup window. The backup starts during the backup window. When possible, schedule backups when your instance has the least activity.
- Up to seven most recent backups are retained, by default. Automated backups are halted if your instance has been stopped for more than 36 hours. Configure how many automated backups to retain, from 1 to 365.
- Backup and transaction log retention values can be changed from the default setting.

Where backups are stored

Backups locations include:

Default locations that Cloud SQL selects, based on the location of the original instance.
- Backups are stored in the multiregion that is geographically closest to the location of your Cloud SQL instance.
  Example:
  - If the Cloud SQL instance is in asia-southeast1, your backups are stored in the asia multi-region by default.
Custom locations that you choose when you do not want to use the default location.

For a complete list of valid regional values, see Instance Locations. For a complete list of multi-regional values, see Multi-regional locations.

Automated backup and transaction log retention

Automated backups are used to restore a Cloud SQL instance. A combination of automated backups and transaction logs are used to perform a point-in-time recovery.

While transaction logs are counted in days, automated backups are not guaranteed to occur on a day boundary. Different units are used for these retention settings. Automated backup retention is a count and can be set from one to 365 backups. Transaction log retention is in days and can be set from 1 to 7. The default value for both is 7.

The transaction log retention setting must always be less than or equal to the backup retention setting.
Transaction logs older than the last backup are automatically deleted.

Logs are purged once daily, not continuously. When the number of days of log retention is the same as the number of backups, insufficient log retention can result.
Example:

Setting log retention to 7 days and backup retention to 7 backups means that between 6 and 7 days of logs will be retained.

> It’s recommended setting the number of backups to at least one more than the days of log retention to guarantee a minimum of specified days of log retention.

> High write activity to the database can generate a large volume of transaction logs, which can consume significant disk space, and lead to disk growth for auto storage increase enabled instances. It’s recommended sizing instance storage to account for transaction log retention.

Setting automated backup retention.

Setting transaction log retention.

Enable Point-in-time recovery and set Transaction log retention via Terraform configuration

resource "google_sql_database_instance" "postgres_instance_p-i-t-r" {
  name             = "postgres-instance-with-p-i-t-r"
  region           = "asia-southeast1"
  database_version = "POSTGRES_13"
  settings {
    tier = "db-custom-2-7680"
    backup_configuration {
      enabled                        = true
      point_in_time_recovery_enabled = true 
      start_time                     = "20:55"
      transaction_log_retention_days = "3" # set the number of days to retain write-ahead logs (from 1 to 7)
    }
  }
  deletion_protection = false # set to true to prevent destruction of the resource
}

Set automated backup retention via Terraform configuration

resource "google_sql_database_instance" "default" {
  name             = "postgres-instance-backup-retention"
  region           = "asia-southeast1"
  database_version = "POSTGRES_13"
  settings {
    tier = "db-custom-2-7680"
    backup_configuration {
      enabled                        = true
      backup_retention_settings {
        retained_backups               = 365
        retention_unit                 = "COUNT"
      }
    }
  }
  deletion_protection = false # set to true to prevent destruction of the resource
}

What happens during a restore?

When you restore an instance, the following data from the primary instance are restored to the new instance:

Databases
Users

> The flags from the primary instance are not restored. Any flags previously set on the target instance are retained after the restore

> The restore operation causes the instance to restart.