Re: Seeking information about backup/recovery

On Thursday, September 04, 2003 18:12, Bruce Momjian
[mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
>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.