Backups in Managed Service for PostgreSQL

Managed Service for PostgreSQL supports automatic and manual database backups.

For clusters running an unsupported DBMS version, restoring from backups is not available.

To restore a cluster from a backup, follow this guide.

Creating a backupCreating a backup

The first and every seventh automatic backups as well as all manually created backups are full backups of all databases. To save space, other backups are incremental and only store the data that has changed since the previous backup.

Both full and incremental backups are created using the wal-g utility. The Point-in-Time Recovery (PITR) is implemented using data from Write Ahead Logs (WAL). For more information, see PITR in Managed Service for PostgreSQL.

To reduce the load on the master, the backup data is taken from a replica.

All cluster data is automatically backed up once a day. You cannot disable automatic backups. However, when creating or editing a cluster, you can set the following parameters for automatic backups:

• Retention time.
• Time interval during which the backup starts. The default value is 22:00 - 23:00 UTC (Coordinated Universal Time).

Once created, a backup is compressed for storage. The exact backup size is not displayed.

Backups are only created on running clusters. If you are not using your Managed Service for PostgreSQL cluster 24/7, check the settings of backup start time. A cluster that has no backups cannot be stopped.

Learn about creating manual backups in Managing backups.

Storing a backupStoring a backup

Storing backups in Managed Service for PostgreSQL:

• Backups are stored in object storage as binary files and are encrypted using GPG. Each cluster has its own encryption keys.

• The total backup size is defined as the sum of the data copy size and the WAL size. The WAL size depends on the number of changes and can exceed the size of the data copy. You can get both values from the backup list.

• The retention time for backups of an existing cluster depends on the way they were created:

  • Automatic backups are stored for 7 days by default. When creating or reconfiguring a cluster, you can specify a different retention period between 7 and 60 days.

  • Automatic backups created with backup policies can be stored from 7 to 1095 days (up to three years).

  • Manual backups are stored with no time limit.

• After you delete a cluster, all its backups are kept for seven days.

• Quotas and limits for cluster storage do not apply to backup storage.

• Backups are stored in an object storage and do not take up space in the cluster storage. If there are N GB of free space in the cluster, the first N GB of backups are stored free of charge.

  For more information, see the Managed Service for PostgreSQL pricing policy.

Recovery from a backupRecovery from a backup

By restoring a cluster from a backup, you create a new cluster. You need to specify all the cluster's settings, just as when creating a new cluster. If your folder lacks resources to create such a cluster, you will not be able to restore from the backup. The average backup recovery speed is 10 MBps per database core.

The cluster is restored to a selected point in time using PITR. The new cluster will reflect the state of:

• Existing cluster at the time of archiving the most recent WAL.
• Deleted cluster at the time of archiving the last WAL.

When restored from a backup, the cluster is recovered as a whole, with all its databases. You cannot select specific databases.

PITR in Managed Service for PostgreSQLPITR in Managed Service for PostgreSQL

Managed Service for PostgreSQL allows you to restore the cluster state to any point in time (Point-in-Time-Recovery, PITR) after the creation of the oldest full backup. For this purpose, the backup selected as the recovery starting point is updated with archived entries from the WAL logs.

For example, if the backup operation ended on August 10, 2020 at 12:00:00 UTC, the current date is August 15, 2020, 19:00:00 UTC, and the most recent WAL was saved on August 15, 2020, 18:50:00 UTC, the cluster can be restored to any state between August 10, 2020, 12:00:01 UTC and August 15, 2020, 18:50:00 UTC, inclusive.

WAL consists of 16 MB files that are archived in a running cluster when the required size is reached or if the time specified by the archive timeout cluster-level DBMS setting has passed since a file was last archived. The archive is then uploaded to object storage.

PITR is enabled by default.

For more information about PITR, see this PostgreSQL guide.