Chapter 22. Routine Database Maintenance Tasks

Table of Contents
22.1. Routine Vacuuming
22.1.1. Recovering disk space
22.1.2. Updating planner statistics
22.1.3. Preventing transaction ID wraparound failures
22.1.4. The auto-vacuum daemon
22.2. Routine Reindexing
22.3. Log File Maintenance

There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. The tasks discussed here are repetitive in nature and can easily be automated using standard Unix tools such as cron scripts. But it is the database administrator's responsibility to set up appropriate scripts, and to check that they execute successfully.

One obvious maintenance task is creation of backup copies of the data on a regular schedule. Without a recent backup, you have no chance of recovery after a catastrophe (disk failure, fire, mistakenly dropping a critical table, etc.). The backup and recovery mechanisms available in PostgreSQL are discussed at length in Chapter 23.

The other main category of maintenance task is periodic "vacuuming" of the database. This activity is discussed in Section 22.1.

Something else that might need periodic attention is log file management. This is discussed in Section 22.3.

PostgreSQL is low-maintenance compared to some other database management systems. Nonetheless, appropriate attention to these tasks will go far towards ensuring a pleasant and productive experience with the system.