Aiven PostgreSQL databases are automatically backed-up, with full backups daily, and write-ahead log (WAL) recorded continuously. All backups are encrypted. We use pghoard for this. Number of stored backups and backup retention time depends on the selected service plan. Full backups are version specific binary backups, which when combined with WAL allow consistent recovery to a point in time (PITR). This is different from logical backups typically taken with pg_dump  and similar command line tools.

To restore a backup, see this help article.

What are differences between logical and binary backups?

Size of logical backups, and Aiven backup size shown on Aiven web console differ, in some cases significantly. Backup sizes shown in Aiven web console are for daily backups, before encryption and compression. Below is listed some differences between backup types.

Binary backups contain

  • all data from indexes,
  • uncommitted transactions,
  • deleted (and updated) rows which has not been cleaned up by PostgreSQL VACUUM process, and
  • all databases, including templates.

pg_dump takes logical backups, which

  • do not contain index data (only queries used to recreate indexes from other data),
  • do not contain any data already deleted, and
  • depending on the options given, output might be compressed.

To see size of each database, including space consumed by indexes, you can use \l+ command on psql  prompt.

Did this answer your question?