Chapter 26. Backup and Restore

Table of Contents

26.1. SQL Dump

26.1.1. Restoring the Dump

26.1.2. Using pg_dumpall

26.1.3. Handling Large Databases

26.2. File System Level Backup

26.3. Continuous Archiving and Point-in-Time Recovery (PITR)

26.3.1. Setting Up WAL Archiving

26.3.2. Making a Base Backup

26.3.3. Making a Base Backup Using the Low Level API

26.3.4. Recovering Using a Continuous Archive Backup

26.3.5. Timelines

26.3.6. Tips and Examples

26.3.7. Caveats

As with everything that contains valuable data, PostgreSQL databases should be backed up regularly. While the procedure is essentially simple, it is important to have a clear understanding of the underlying techniques and assumptions.

There are three fundamentally different approaches to backing up PostgreSQL data:

• SQL dump

• File system level backup

• Continuous archiving

Each has its own strengths and weaknesses; each is discussed in turn in the following sections.