Backup and Recovery

Backups

PostgreSQL Backups: A cluster can have multiple backups. They are created:

1. When a cluster is created.
2. When the PostgreSQL version is changed to a higher major version.
3. When a Point-In-Time-Recovery (PITR) operation is conducted.

PostgreSQL sends data to only one backup destination at a time. The system combines "Base Backups" with continuous Write-Ahead Log (WAL) archiving. For more information, refer to the PostgreSQL Documentation. While the pg_basebackup utility creates regular base backups, the system continuously archives WAL records to supplement them. Consequently, a backup represents a time range rather than a single point in time.

How do backups work in PostgreSQL v2 clusters?

In the v2 API, you control how long backups are retained by setting backup.retentionDays (an integer between 1 and 365) when you create or update a cluster. Pre-existing clusters and clusters created without this field default to a 7-day retention window. If you reduce the value, backups older than the new window are purged.

You can choose and change in which IONOS CLOUD Object Storage location the backups are stored using the backup.location setting.

A pg_basebackup is taken once per day. Additional base backups are made after a major version upgrade and at cluster deletion.

In contrast to PostgreSQL v1, WAL files are first archived to a local disk. Every 5 minutes, they are uploaded to Object Storage; only then are they available for PITR. The pg_stat_archiver only shows the progress of archiving to local disk and does not indicate whether the WAL is available for PITR.

How do backups work in PostgreSQL v1 clusters?

Data is added to the backup in chunks of 16MB or after 30 minutes, whichever comes first. Failures and delays in archiving do not prevent writes to the cluster. If you restore from a backup then only the data that is present in the backup will be restored. This means that you may lose up to the last 30 minutes or 16MB of data if all replicas lose their data at the same time.

You can restore from any backup of any PostgreSQL cluster as long as the backup was created with the same or an older PostgreSQL major version.

Backups are stored encrypted in an IONOS CLOUD Object Storage bucket in the same region your database is in. Databases in regions without IONOS CLOUD Object Storage will be backed up to eu-central-2. In PostgreSQL v2, backup locations are a dedicated resource. You can query and configure the geographic location of your backups using the /backup-locations endpoint.

Warning: A database is stopped from all transactions since the last WAL segment is written to a partial WAL file and shipped to the IONOS CLOUD Object Storage. This also happens when you delete a database. We provide an additional security timeout of 5 minutes to stop and delete the database gracefully. However, under rare circumstances, this last WAL Segment might not be written to the IONOS CLOUD Object Storage, and these transactions could get lost.

As an additional security mechanism, you can check which data has been backed up before deleting the database. To verify which was the last archived WAL segment and at what time it was written you can connect to the database and get information from the pg_stat_archiver.

SELECT now(); # verify server time
SELECT * FROM pg_stat_archiver; # get information about last archival wal and time

The last_archived_time might be older than 30 minutes. WAL files are created with a specific timeout, which is expected if there is no new data added.

Recovery

We provide PITR. When recovering from a backup, the user chooses a specific backup and provides a time (optional), so that the new cluster will have all the data from the old cluster up until that time (exclusively). If the time was not provided, the current time will be used.

It is possible to set the recovery target time to a time in the future. If the end of the backup is reached before the recovery target time is met then the recovery will complete with the latest data available. In PostgreSQL v2, this is the recoveryTargetDatetime property (named recoveryTargetTime in v1). For more information, see Restore from Backup (v2) or Restore from Backup (v1).

Note: WAL records shipping is a continuous process and the backup is continuously catching up with the workload. Should you require that all the data from the old cluster is completely available in the new cluster, stop the workload before recovery.