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, online physical full backups have been available since version 8.0. 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.

PostgreSQL version 17 introduced the incremental backup feature.

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?