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

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

On Thu, 28 Aug 2008 11:21:24 -0400, Douglas McNaught wrote:

>On Thu, Aug 28, 2008 at 10:57 AM, John T. Dow <john(at)johntdow(dot)com> wrote:
>> BACKGROUND INFO BEGINS
>>
>> Recently I had some questions about doing backups and received very helpful replies. I have now put together a BAT file to do a routine backup, using pg_dumpall with the -g option to get the roles, and pg_dump with the custom format to get all the data.
>>
>> I am now testing this process to make sure it is possible to recover data up to the last minute in the event of a catastrophic server failure.
>
>You have a fundamental misunderstanding of how this works. You can't
>apply saved WAL files to a database restored from a dump. To use
>PITR, you need to do a physical backup of the actual database files
>(after calling the pg_start_backup() function), then when that is
>done, call pg_stop_backup(). You then archive WAL files as the
>database runs normally.
>
>To recover, you do a physical restore of the database files, then run
>recovery using the archived WAL files.
>
>Read the documentation on PITR again--it's reasonably complete.
>
>-Doug
>
>--
>Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general