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.

At any time, Postgres only ships to one backup. We use Base Backups combined with continuous Write Ahead Logs (WAL) archiving. A base backup is done through pg\_basebackup regularly, and then WAL records are continuously added to the backup. Thus, a backup does not represent a point in time but a time range. We keep backups for the last 7 days so recovery is possible for up to one week in the past.

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 S3 Object Storage bucket in the same region your database is in. Databases in regions without IONOS S3 Object Storage will be backed up to eu-central-2.

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 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 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 normal 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 recoveryTargetTime 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. For more information, see Restore from Backup.

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.