Database Reference
In-Depth Information
The next recipe is all about planning. That's really the best place to start before you go
charging ahead to do backups.
The physical backup mechanisms here were initially written by me (
Simon
Riggs)
for
PostgreSQL in release 8.0 in 2004, and have been supported by him ever since then,
now with increasing help from the community as its popularity grows. 2ndQuadrant has also
been providing database recovery services since 2004 and regrettably many people have
needed them as a result of missing or damaged backups.
Understanding and controlling crash
recovery
Crash recovery is the PostgreSQL subsystem that saves us if the server should crash, or fail as
a part of a system crash.
It's good to understand a little about it, and to do what we can to control it in our favor.
How to do it...
If PostgreSQL crashes there will be a message in the server log with severity-level
PANIC
.
PostgreSQL will immediately restart and attempt to recover using the transaction log or Write
Ahead Log (WAL).
The WAL consists of a series of files written to the
pg_xlog
subdirectory of the PostgreSQL
data directory. Each change made to the database is recorded first in WAL, hence the name
"write-ahead" log. When a transaction commits, the default and safe behavior is to force the
WAL records to disk. If PostgreSQL should crash, the WAL will be replayed, which returns the
database to the point of the last committed transaction, and thus ensures the durability of
any database changes.
Note that the database changes themselves aren't written to disk at transaction commit. Those
changes are written to disk sometime later by the "background writer" on a well-tuned server.
Crash recovery replays the WAL, though from what point does it start to recover? Recovery
starts from points in the WAL known as "checkpoints". The duration of crash recovery depends
upon the number of changes in the transaction log since the last checkpoint. A checkpoint is
a known safe starting point for recovery, since at that time we write all currently outstanding
database changes to disk. A checkpoint can become a performance bottleneck on busy
database servers because of the number of writes required. There are a number of ways of
tuning that, though please also understand the effect on crash recovery that those tuning
options may cause. Two parameters control the amount of WAL that can be written before
the next checkpoint. The first is checkpoint_segments, which controls the number of 16 MB
files that will be written before a checkpoint is triggered. The second is time-based, known as
checkpoint_timeout, and is the number of seconds until the next checkpoint. A checkpoint is
called whenever either of those two limits is reached.
