10. Online Backup and Point-In-Time Recovery (PITR)

Online database backups are classified into two categories: logical and physical backups.

While both have advantages and disadvantages, logical backups can be very time-consuming. In particular, backing up a large database takes a long time, and restoring from that backup data takes even longer. In contrast, physical backups allow for much faster creation and restoration, making them essential in practical systems.

In PostgreSQL, logical backups have been supported by the pg_dump utility since the beginning, while online physical full backups have been available since version 8.0 (2005). A snapshot of an entire running database cluster is known as a base backup.

Point-in-Time Recovery (PITR), also available since version 8.0, restores a database cluster to any point in time using a base backup and archive logs generated by the continuous archiving feature. For example, if a critical mistake occurs (such as truncating all tables), PITR can restore the database to the point just before the error.

For a long time, PostgreSQL supported only full physical backups. This required large amounts of storage because each backup contained the entire database cluster. To address this limitation, version 17 (2024) introduced incremental backups to save only the file blocks modified since the previous backup.

This chapter describes the following topics:

• What is a base backup?
• How does PITR work?
• What is a timelineId?
• What is a timeline history file?
• How does incremental backup in PostgreSQL work?