Backups in Managed Service for PostgreSQL

Managed Service for PostgreSQL supports automatic and manual database backups.

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. This is achieved by supplementing the backup selected as the starting point for recovery with entries from the write-ahead logs (WAL) of later cluster backups.

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.

PITR is enabled by default.

When creating backups and restoring data from them to a given point in time, keep the following in mind:

• 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.

• It takes some time to create and upload a WAL archive to object storage. This is why the cluster state stored in the object storage may differ from the actual one.

You can learn more about PITR in the PostgreSQL documentation.

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

Creating backupsCreating backups

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

All cluster data is automatically backed up every day. You cannot disable such 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 time is 22:00 - 23:00 UTC (Coordinated Universal Time).

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

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

For more information about creating a backup manually, see Managing backups.

Storing backupsStoring backups

Specifics of storing backups in Managed Service for PostgreSQL:

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

• The total backup size is a sum of data copy and WAL sizes. The size of WAL data depends on the amount of made changes and is comparable to the backup size. You can get both values with a list of backups.

• The retention time for backups of an existing cluster depends on the method used to create such backups:

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

  • Manually created backups are stored with no time limit.

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

• Backup storage is not subject to quotas or limits for cluster storage space.

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

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

Checking backup recoveryChecking backup recovery

To test how backup works, restore a cluster from a backup and check the integrity of your data.