In planning a Postgresql deployment, our team discovered that we have
different understandings of how the WAL affects database reliability,
and we have not found the Postgresql manual quite explicit enough to
distinguish between the following two theories:
a) Putting the WAL on a separate device from the database tables
not only increases efficiency, but reliability as well - because
as long as one keeps a database backup and a WAL history that
goes back to the last backup (as described in 22.3 of the 8.0.1
manual), then one is insulated from losing data from a single
disk failure:
- If the drive holding the tables fails, then take the most
recent backup and bring it up to date using the WAL.
- If the backup fails, make another backup.
And the disputed point:
- If the drive holding the WAL fails, then the database engine
will shut down cleanly by writing everything in RAM out to
the real database tables, and no data will be lost.
The counter theory:
b) Putting the WAL on a separate device from the database tables
increases efficiency but does not by itself improve reliability.
Just as, when the WAL and tables are on a single device, a
failure of that device makes you lose all data written since the
your last backup, so when the WAL is placed on a separate drive,
a failure of that drive may result in the loss of any data
committed but not yet written out to the main database tables.
Essentially, the documentation does not make clear what recovery can
take place - under what guarantees - should the WAL be destroyed in
mid-operation.
Please advise as to which understanding is the correct one.
--
Brandon Craig Rhodes brandon@rhodesmill.org http://rhodesmill.org/brandon
