Re: WAL file questions - how to relocate on Windows, how to replay after total loss, etc

On Thu, Aug 28, 2008 at 12:35 PM, John T. Dow <john(at)johntdow(dot)com> wrote:
> You can't blame me for being confused. Here's from section 23.3 of the 8.2 manual.
>
> "At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster's
> data directory. The log describes every change made to the database's data files. This log exists primarily
> for crash-safety purposes: if the system crashes, the database can be restored to consistency by "replaying"
> the log entries made since the last checkpoint. However, the existence of the log makes it possible to use
> a third strategy for backing up databases: we can combine a file-system-level backup with backup of the
> WAL files."
>
> That says that the database can be restored using the WAL files, and then it says that their existence makes another strategy for backing updatabases possoble. To me, that means that WAL files are useful even if not doing a physical backup of the actual database files.

You are partly right.

They are useful, and used, in the case of system or database crash--if
the physical database files survive, the WAL log entries (everything
since the last checkpoint) will be applied during normal recovery as
the database comes back up.

They are useful in the "third strategy" mentioned above, which
involves the *physical* (i.e. using 'tar' or whatever rather than
pg_dump) database file backup in conjunction with pg_start_backup()
and pg_stop_backup(), along with archiving the WAL logs as they are
created.

They are *not* useful if you are just using pg_dump--there is no way
to apply WAL files to a database restored from a dump file. If you
lose your database directory and are not doing physical backups and
archiving WAL logs as documented for PITR, you can only get back to
the point of your last pg_dump after doing an 'initdb'.

Hopefully this helps clear things up.

-Doug