On Thu, Aug 28, 2008 at 12:35 PM, John T. Dow <john@xxxxxxxxxxxx> 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
