On Thursday, September 04, 2003 18:12, Bruce Momjian [mailto:[EMAIL PROTECTED] >Murthy Kambhampaty wrote: ... >I assume you are contrasting _any_ point-in-time recovery to recover up >to the crash point, right? > Right.
>Anyway, unfortunately, WAL doesn't contain enough information >to recover >without having the file system files in some consistent state, even if >that state is old. In fact, the files have to be consistent as of the >last checkpoint. This, I'm not so sure of. On Linux, an xfs_freeze -f <fs_to_freeze> pauses the filesystem and flushes all writes to disk; a snapshot of $PGDATA's filesystem taken while it is frozen gives a consistent copy of $PGDATA as of that instant (similar functionality is obtained by compiling the VFS locking patch into the kernel). The discussion at http://marc.theaimsgroup.com/?l=postgresql-admin&w=2&r=1&s=LVM+snapshots&q=b , includes log files from postmaster startup and shutdown on the backup $PGDATA, and AFAICT, WAL recovery does not roll back to the last checkpoint. If there is a better way to test this, let me know, and I'm happy to do it. Thanks, Murthy PS: From the man page for xfs_freeze: "The -f flag requests the specified XFS filesystem to be frozen from new modifications. When this is selected, all ongoing transactions in the filesystem are allowed to complete, new write system calls are halted, other calls which modify the filesystem are halted, and all dirty data, metadata, and log information are written to disk. Any process attempting to write to the frozen filesystem will block waiting for the filesystem to be unfrozen." When $PGDATA/pg_xlog/ is on disks different from $PGDATA's, the XFS filesystem still allows online BAR with the following sequence: 1. rysnc -avr --delete $PGDATA/ <backup server>::mirror_pgdata/ 2. xfs_freeze -f $PGDATA/pg_clog/ 3. xfs_freeze -f $PGDATA 4. create snapshots and mount 5. xfs_freeze -f $PGDATA 6. xfs_freeze -f $PGDATA/pg_clog/ 7. rysnc -avr --delete --exclude=pg_xlog/ $PGDATA/ <backup server>::mirror_pgdata/pg_xlog/ 8. rysnc -avr --delete $PGDATA/pg_xlog/ $PGDATA/ <backup server>::mirror_pgdata/pg_xlog/ 9. remove snapshots By freezing both volumes ($PGDATA/pg_clog/ and $PGDATA/) during snapshot creation, a "consistent" copy is assured. Freezing the pg_xlog first, and unfreezing it last, makes sure that no CHECKPOINT operations are missed, ensuring the consistency of the copy. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html