Chapter 23. Routine Database Maintenance Tasks
Table of Contents
Postgres Pro, like any database software, requires that certain tasks be performed regularly to achieve optimum performance. The tasks discussed here arerequired, but they are repetitive in nature and can easily be automated using standard tools such ascron scripts or Windows'Task Scheduler. It is the database administrator's responsibility to set up appropriate scripts, and to check that they execute successfully.
One obvious maintenance task is the 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 inPostgres Pro are discussed at length inChapter 24.
The other main category of maintenance task is periodic“vacuuming” of the database. This activity is discussed inSection 23.1. Closely related to this is updating the statistics that will be used by the query planner, as discussed inSection 23.1.3.
Another task that might need periodic attention is log file management. This is discussed inSection 23.3.
check_postgres is available for monitoring database health and reporting unusual conditions.check_postgres integrates with Nagios and MRTG, but can be run standalone too.
Postgres Pro 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.