Chapter 22. Routine Database Maintenance Tasks
PostgreSQL, like any database software, requires that certain tasks
be performed regularly to achieve optimum performance. The tasks
discussed here are required, but they
are repetitive in nature and can easily be automated using standard
Unix tools such as cron scripts or
Windows' Task Scheduler. 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. Closely related to this is updating
the statistics that will be used by the query planner, as discussed in
Section 22.1.2.
Another task 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.