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.

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.

All cluster data is automatically backed up every 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).

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 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 be comparable to 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.

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

Testing recovery from a backupTesting recovery from a backup

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