Skip to main content

Aiven for PostgreSQL® backups

About backups in Aiven for PostgreSQL®

Aiven for PostgreSQL® databases are automatically backed up, with full backups made daily, and write-ahead logs (WAL) copied at 5 minute intervals, or for every new file generated. All backups are encrypted using pghoard, an open source tool developed and maintained by Aiven, that you can find on GitHub.

The time of day when the daily backups are made is initially randomly selected, but can be customised by setting the backup_hour and backup_minute advanced parameters, see Advanced parameters for Aiven for PostgreSQL®.

note

The size of backups and the Aiven backup size shown on the Aiven web console differ, in some cases significantly. The backup sizes shown in the Aiven web console are for daily backups, before encryption and compression.

Backup retention time by plan

The number of stored backups and the backup retention time depends on the service plan that you have selected.

Plan TypeBackup Retention Time
HobbyistNone
Startup2 days
Business14 days
Premium30 days

Differences between logical and full backups

Full backups are version-specific binary backups that, when combined with WAL, allow consistent recovery to a point in time (PITR). Logical backups contain the SQL statements used to create the database schema and fill it with data, and are not tied to a specific version.

FeatureFull backupLogical backup
Version-specificFull backups can be restored with the same PostgreSQL version as the backup was created fromCross-version compatibility
Database selectionThe entire PostgreSQL instance is backed up. No option to backup (or restore) as single databaseSingle database objects can be backed up. Easy to backup and restore any item down to a single table
DataContains uncommitted transactions and deleted and updated rows that have not been cleaned up by the PostgreSQL VACUUM processContains only the current committed content of the tables
IndexesContains all data from indexesContains the queries needed to recreate indexes
PITR capabilitiesPrevious database status can be restored using the WALOnly "as-of-backup" status can be restored
Restore timeAlmost instantaneous, restore backup and replay delta WAL filesLong restoration process, replay of all SQL statements is needed to generate schema object and insert data

Delta base backups

Aiven for PostgreSQL uses delta base backups, which allows to store data files that have been changed since the last backup and leave out the unchanged files. It's particularly beneficial for databases that include considerable portions of static data. Compared to regular base backups, delta base backups are more efficient, bringing improved performance and speeding up backup operations (unless all the data is updated constantly).

Since delta base backups don't take all the data files, they are faster and easier to perform on large databases with huge volumes of data. Because performing a delta base backup doesn't last long, Aiven can back up data more frequently if required and applicable to specific datasets. With the increased backup frequency, service restoration and node replacement potentially can be faster for highly updated services because fewer WAL files need to be restored since the last backup (WAL restoration in PostgreSQL is single-threaded and, therefore, slow).

To restore a backup, see Advanced parameters for Aiven for PostgreSQL®.