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

John T. Dow wrote:
> Douglas
>
> 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.

This is probably the point that lacks explanation. I may be a bit off
here but I would like to know if I have anything wrong.

The data files and the WAL files are linked together (through TXID's I
believe) that make the WAL's only good for the data files they are
generated with.
The WAL files are mainly (originally) of use when the server is stopped
during a write process that is incomplete. It allows postgresql to know
what data is needed to complete the write when it is restarted.

If you have a filesystem backup from 6 hours ago, then the WAL files as
they are now can be used to update the backup to match the db as it is now.
This makes the filesystem backup have two points of interest. First the
entire data folder. Second the WAL files. You may backup the entire data
folder maybe once a day (or week?) but you would also want to backup the
WAL files maybe every 15 mins.

> 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.
>
> John

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz