Re: Backup routine

There's an extensive discussion of doing file system level backups off an
LVM snapshot taken on a frozen XFS filesystem (XFS provides xfs_freeze
command which allows the sysadmin to freeze and unfreeze a given file system
at will). See
http://marc.theaimsgroup.com/?l=postgresql-admin&w=2&r=1&s=LVM+snapshots&q=b
(as Mike Castle points out in this thread, this gives you a "consistent
state" but not a know one). If your hardware gives you the ability to freeze
I/O, you might get similar capabilities with other filesystems/OSes than XFS
on Linux.

In that thread, I also discussed a strategy for getting consistent backups
when the $PGDATA/pg_xlog/ folder is on a different filesystem:
freeze $PGDATA/pg_xlog/
freeze $PGDATA
create snapshot $PGDATA/pg_xlog/ and mount that to $PGDATA.snap/pg_xlog\
create snapshot $PGDATA and mount that to $PGDATA.snap
unfreeze $PGDATA
unfreeze $PGDATA/pg_xlog/
(notice that since you only need to freeze the filesystem during snapshot
creation, which is fast, you don't hold the postmaster up very long)
rsync $PGDATA.snap/ to a backup volume
umount and lvremove the snapshot of $PGDATA/pg_xlog/
umount and lvremove the snapshot of $PGDATA/

Filesystem performance is adversely affected as long as the snapshots exist;
so I do a "preliminary rsync" (giving an inconsistent backup) at the
beginning of the sequence to minimize the amount of data to be backed up
from the snapshot(s).

An example bash script which handles $PGDATA/pg_xlog/ being on a different
filesystem (and mounted, not symlinked, to $PGDATA/pg_xlog) is attached. It
has been tested under a fairly heavy load and seems to handle the job. I'd
be interested in comments on the strategy/implementation from the postgresql
gurus on this list (the script is rather messy, sorry).

Thanks,
    Murthy


>-----Original Message-----
>From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
>Sent: Sunday, August 10, 2003 23:13
>To: Christopher Browne
>Cc: pgsql-admin@postgresql.org
>Subject: Re: [ADMIN] Backup routine
>
>
>Christopher Browne wrote:
>> The world rejoiced as peterandsarah@blueyonder.co.uk (Peter
>and Sarah Childs) wrote:
>> >     However there is a third way. That should be safe but some
>> > people may disagree with me! If you can "freeze" the disk while you
>> > take the backup. The backup can be used as if the computer had
>> > crashed with no hard disk failure at all. Ie WAL will be consistant
>> > and database may take longer but once it is up it will be
>safe (like
>> > paragaph 1). Now freezeing a disk for backup is not that
>> > difficult. You should be doing it anyway for user file
>> > consistancy. (You don't want the first 30 pages of you document to
>> > disagree with the end because somone was saving it during the
>> > backup!
>>
>> I heard D'Arcy Cain indicate that some SAN systems (I think he
>> mentioned NetApp) support this sort of thing, too.  Digital's AdvFS
>> also supports it.
>>
>> Of course, if you take this approach, you have to make _certain_ that
>> when you "freeze" a replica of a filesystem, that _ALL_ of the
>> database is contained in that one filesystem.  If you move WAL to a
>> different filesystem, bets would be off again...
>
>Also, I assume you have to stop the server just for a moment while you
>do the freeze, right?
>
>--
>  Bruce Momjian                        |  http://candle.pha.pa.us
>  pgman@candle.pha.pa.us               |  (610) 359-1001
>  +  If your life is a hard drive,     |  13 Roberts Road
>  +  Christ can be your backup.        |  Newtown Square,
>Pennsylvania 19073
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to
>majordomo@postgresql.org)
>