Backup and Recovery

PostgreSQL Backups: A cluster can have multiple backups. They are created
  1. 1.
    When a cluster is created
  2. 2.
    When the PostgreSQL version is changed to a higher major version
  3. 3.
    When a Point-In-Time-Recovery operation is conducted.
At any time, Postgres only ships to one backup. We use base backups combined with continuous WAL archiving. A base backup is done via pg_basebackup regularly, and then WAL records are continuously added to the backup. Thus, a backup doesn't 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 S3 buckets in the same region your database is in. Databases in regions without S3 Object Storage will be backed up to eu-central-2.
Warning: When a database is stopped all transactions since the last WAL segment are written to a (partial) WAL file and shipped to S3. 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 it could happen that this last WAL Segment is not written to S3 (e.g. due to errors in the communication with S3) and these transactions 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, see above) which is normal if there is no new data added.

We provide Point-in-Time-Recovery (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.
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.
Export as PDF
Copy link
On this page