Thread: does database shut down cleanly when WAL device fails?

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

Brandon Craig Rhodes <brandon@rhodesmill.org> writes:
>       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.

Whoever claimed that has no familiarity with the code at all, and no
understanding of the basic WAL rule: write to the log BEFORE you write
data.

In point of fact, loss of the WAL drive will mean a database PANIC stop
and probably a corrupt data area afterwards, since there'd be no
guarantee that related page updates had all made it to disk.

            regards, tom lane

On Tue, 2005-05-03 at 10:30 -0400, Brandon Craig Rhodes wrote:
> 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.

I assume the WAL history you mention are the WAL segments archived
for PITR. they are usually shipped to a safe place, as soon as they
are archived, but not kept on the same device as the WAL itself, if
at all possible.(often near the database backup)
Then, if either of the devices holding the database or WAL fails,
the database can be rebuilt using backup+archived WALs upto the point
of the last WAL archive.

gnari

Tom Lane wrote:
> Brandon Craig Rhodes <brandon@rhodesmill.org> writes:
> >       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.
>
> Whoever claimed that has no familiarity with the code at all, and no
> understanding of the basic WAL rule: write to the log BEFORE you write
> data.
>
> In point of fact, loss of the WAL drive will mean a database PANIC stop
> and probably a corrupt data area afterwards, since there'd be no
> guarantee that related page updates had all made it to disk.

Also the WAL files might be recyled at each checkpoint, which is at
least every five minutes, so pg_xlog will not contain all the WAL files
from the backup, unless you are using point-in-time recovery --- this
might be where you got confused.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073