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

I understand that WAL files can only be used with the database files in use at the time the WAL was written, therefore they are of no use to a database reconstructed from a pg_dump file.

Let me see if I have this right.

A - To protect against temporary server failure (such as a loss of power), just restart the server. The WAL files will ensure that the database is consistent and current as of the last transaction.

B - To protect against permanent server failure (such as physical destruction of the server's hard drives), do a pg_dump backup regularly. The only data loss is data inserted or updated since the last pg_dump. Use pg_dumpall with the -g option to get the global information, use pg_dump with the custom output file format to get the data.

C - To protect against permanent server failure with minimal loss of data, use the PITR strategy.

D - To transfer a database to another server, use B, because the files are much smaller than an archive of the data directory.

E - To upgrade the server software, you must use B.

F - To allow selectively restoring data to a previous point in time (such as a table that was dropped by mistake), use PITR.

Summary: Permanent loss of the server's hard drives is extremely unlikely, especially with raid, so option B is adequate for most applications. It's easier than PITR to set up and use, it's fast, and the backup files are small. It also serves several other purposes. PITR has disadvantages and costs that probably means it isn't worth the effort except for those that really need to guarantee every possible transaction or need the flexibility of going back in time.

However, it would really be nice if the WAL files could be used to make the restored data more current, even if not everything can be restored. Are we certain that useful information can't be gleaned from them to apply changes made since the last pg_dump?

John