X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 17E57D1B4E3; Fri, 17 Oct 2003 14:46:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 55246-10; Fri, 17 Oct 2003 11:45:58 -0300 (ADT) Received: from THOR.goeci.com (thor.goeci.com [66.28.220.99]) by svr1.postgresql.org (Postfix) with ESMTP id 083C0D1B53D; Fri, 17 Oct 2003 11:45:46 -0300 (ADT) Received: by THOR.goeci.com with Internet Mail Service (5.5.2653.19) id <47P8D02P>; Fri, 17 Oct 2003 10:45:42 -0400 Message-ID: <2D92FEBFD3BE1346A6C397223A8DD3FC09243E@THOR.goeci.com> From: Murthy Kambhampaty To: 'Jeff' , Josh Berkus Cc: markw@osdl.org, pgsql-performance@postgresql.org, linux-lvm@sistina.com, pgsql-admin@postgresql.org Subject: Re: [PERFORM] backup/restore - another area. Date: Fri, 17 Oct 2003 10:45:37 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/mixed; boundary="----_=_NextPart_000_01C394BD.540875DA" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/201 X-Sequence-Number: 10762 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_000_01C394BD.540875DA Content-Type: text/plain; charset="iso-8859-1" There's been a lot of discussion on the ADMIN list about postgresql backups from LVM snapshots: http://marc.theaimsgroup.com/?l=postgresql-admin&w=2&r=1&s=LVM+snapshot&q=b Note that the existence of the snapshot slows the original filesystem down, so you want to minimize the duration for which the snapshot exists. A two phase rsync -- first off the "live" filesystem, second off the snapshot -- to a backup filesystem accomplishes this. If you don't have the capacity to duplicate your $PGDATA folder, you'll want to consider doing incremental backups with xfsdump. Since xfs and xfsdump has been around forever, so you can bet your data on them; of course, xfsdump requires that pgdata be hosted on an xfs volume (XFS is a fast filesystem with metadata journaling, giving fast crash recovery, so this is a good idea anyway). For other filesystems, incremental backup and restores are being developed in star: http://freshmeat.net/projects/star/. star also claims to be faster than gnu tar, so you might use it even if you aren't doing incremental backups. The latest version of my backup script is attached. It demonstrates the implementation of filesystem level backup of the postgresql data cluster (the $PGDATA folder) using two-phase rsync. The $PGDATA folder is on an xfs formatted LVM volume; various checks are done before and after backup, and errors are e-mailed to a specified account. The script handles situations where (i) the XFS filesystem containing $PGDATA has an external log and (ii) the postmaster log ($PGDATA/pg_xlog) is written to a filesystem different than the one containing the $PGDATA folder. These configurations enhance database performance, though an external XFS log is not a big win for postgresql, which creates relatively few new files and deletes relatively few files (relative to an e-mail server). It should be possible, using this script, to keep backup times below 10 minutes even for very high loads - just increase the frequency at which you do backups (it has been tested to run hourly, and runs every three hours on the production server). Cheers, Murthy (Note: I have experienced filesystem hangs within 2 days to a week, from running this script frequently with XFS versions including XFS 1.3. The XFS CVS kernel from Sep 30th 2003 seems not to have this problem - I have been running this script hourly for two weeks without problems. So you might either use a CVS kernel or wait for an XFS release based on linux 2.4.22 or later; and you will be testing your own setup, right?!) >-----Original Message----- >From: Jeff [mailto:threshar@torgo.978.org] >Sent: Thursday, October 16, 2003 13:37 >To: Josh Berkus >Cc: markw@osdl.org; pgsql-performance@postgresql.org; >linux-lvm@sistina.com; pgsql-admin@postgresql.org >Subject: Re: [ADMIN] [PERFORM] backup/restore - another area. > > >On Thu, 16 Oct 2003 10:09:27 -0700 >Josh Berkus wrote: > >> Jeff, >> >> > I left the DB up while doing this. >> > >> > Even had a program sitting around committing data to try >and corrupt >> > things. (Which is how I discovered I was doing the snapshot wrong) >> >> Really? I'm unclear on the method you're using to take the >snapshot, >> then; I seem to have missed a couple posts on this thread. Want to >> refresh me? >> > >I have a 2 disk stripe LVM on /dev/postgres/pgdata/ > >lvcreate -L4000M -s -n pg_backup /dev/postgres/pgdata >mount /dev/postgres/pg_backup /pg_backup >tar cf - /pg_backup | gzip -1 > /squeegit/mb.backup >umount /pg_backup; >lvremove -f /dev/postgres/pg_backup; > >In a nutshell an LVM snapshot is an atomic operation that >takes, well, a >snapshot of hte FS as it was at that instant. It does not make a 2nd >copy of the data. This way you can simply tar up the pgdata directory >and be happy as the snapshot will not be changing due to db activity. > >-- >Jeff Trout >http://www.jefftrout.com/ >http://www.stuarthamm.net/ > >---------------------------(end of >broadcast)--------------------------- >TIP 9: the planner will ignore your desire to choose an index >scan if your > joining column's datatypes do not match > ------_=_NextPart_000_01C394BD.540875DA Content-Type: application/octet-stream; name="pgSnapBack3.generic" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="pgSnapBack3.generic" #!/bin/bash # FN: pgSnapBack3.generic # AB: creates and mounts snapshot of $PGDATA; # rsyncs to backup host; # unmounts and removes snapshot; # reinitializes database on backup server # DT: 2003-03-05 # AU: S. Murthy Kambhampaty PATH=3D/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin # What is this script called and where is it running: ThisCMD=3D$(echo -e "$(basename $0)" | awk 'BEGIN{FS=3D"."}{print $1}') pgHost=3D$(echo -e "$(hostname)" | awk 'BEGIN{FS=3D"."}{print $1}') ThisSession=3D$$ # The postgresql superuser # (if you want to mail error logs somewhere else, either # set your mail server to forward pgSuper's mail, or add a pgMonitor # and change the two sendmail command lines below: pgSuper=3Dpostgres BackupHost=3Dbkhost DataFS=3D/home/db pgData=3D/home/db/pgsql/pgdata RsyncDM=3D"mirrors_$pgHost" # What is the rsync module for this backup call= ed # # As long as you have installed postgresql in # "/usr/bin/pgsql-M.m/, where M.m=3D$(echo -n $(cat $PGDATA/PG_VERSION)), # on both the production server and the backup server, # you shouldn;t have to edit beyond this point # # If $pgData is a symlink, redefine pgData [[ -L "$pgData" ]] && pgData=3D$(ls -l "$pgData" | awk '{FS=3D" "}{print $N= F}') pgMinor=3D$(echo -n "$(cat "$pgData/PG_VERSION")") # A function to execute commands on the backup server: # Requires passwordless logins to $pgSuper@$BackupHost using SSH # See, for example, # http://www.onlamp.com/pub/a/bsd/2002/11/14/FreeBSD_Basics.html RMTExec () { ssh -i "/home/$pgSuper/.ssh/id_rsa" $pgSuper@$BackupHost "$1" } # A function to do the rsync # /etc/rsync.d on the backup server must be configured # A module named mirrors_$pgHost must point to a directory # on the backup server. Within that folder a subdirectory # named pgdata must exist, and will contain the backup # copy of the database RsyncLockFile=3D"/home/$pgSuper/$ThisCMD.lock" # rsync options: # The -B option to increase block size on large files: # 1. Reduces the chance of changes being missed # 2. Uses more bandwidth # If you are really paranoid about missing changes, or if most # tables have very little common data from backup to backup, add the # -W option to rsync whole files, rather than changes only. # NOTE: give the -n option to rsync until sure that it # is configured properly. rsync is not forgiviging. RsyncCMD () { rsync -avW --stats \ --delete --force --exclude=3D'postmaster.pid' \ $1/ \ $BackupHost::$RsyncDM/pgdata/$2 # If you don't give a second arguement, $2 is null; # so it is optional } # When the postmaster is started on the backup server, # the data directory is: pgDataBK=3D"$(RMTExec "cat /etc/rsyncd.conf" | sed -n /$RsyncDM/,/^$/p | gr= ep path | awk 'BEGIN{FS=3D" =3D "}{print $2}')""/pgdata" # A function to print a formatted timestamp in the log DateCMD () { # Two trailing spaces are intentional echo -n "$(date +%Y-%m-%d) $(date +%X) " return 0 } # A function to call when exiting this script with errors ExitOnError () { # This function takes two arguments an "exit code" and and "error message" # in order rm -f "$RsyncLockFile" RMTExec "rm -f ~/RsyncLockFile" lvremove -f "$DataVG/$SnapNM" lvremove -f "$DataVG/$xlSnapNM" # &> /dev/null find /mnt/ -maxdepth 1 -type d -name "*snap_$ThisSession" -exec rm -rf {} = \; echo $'\n\n'"$(DateCMD) $2"$'\n\t'\ "Notified superuser"\ | tee -a "$STASFile" >&2 exec >&6 6>&- exec 2>&7 7>&- cat <(echo "Subject: ATTN: $2"\ $'\n'"X-Priority: 2 (high)"\ $'\n'"!"\ $'\n\t'"Review $(echo -n "$LogFile"), the command log"\ $'\n\t\t'"Error report listed below:" $'\n')\ "$LogFile.err"\ | sendmail "$pgSuper" exit $1 return $1 # Superfluous: this function does not return } # A function for LVM snapshot creation and mounting # There must exist a directory named /mnt/$SnapNM # to which the snapshot is mounted DataLV=3D"$(mount | grep "/home/db\>[^/]" | awk '{print $1}')" DataVG=3D"$(dirname "$DataLV")" # Please keep the xlogLV and DataLV in the = same VG SnapNM=3D"snap_$ThisSession" xlogLV=3D"$(mount | grep "$pgData/pg_xlog\>[^/]" | awk 'BEGIN{FS=3D" "}{pri= nt $1}')" [[ -n "$xlogLV" ]] && { xlSnapMB=3D512; xlSnapNM=3D"xlsnap_$ThisSession"; } DoSnap () { AvailMB=3D"$(echo -n $(vgdisplay --colon "$DataVG") | awk 'BEGIN{FS=3D":"}= {print $16 *$13/2048}')" # 2048 512-byte blocks per MB if [[ -n "$(lvscan | grep Snapshot | grep "$DataLV$")" || \ -n "$(lvscan | grep Snapshot | grep "$xlogLV$")" ]]; then echo $'\n\t'"FATAL: Snapshot of $DataLV or $xlogLV already exists." >&2 lvscan | grep Snapshot echo $'\n\t'"Terminating ..." >&2 SnapTest=3D32 # lvcreate error: "32 snapshot already exists"; sort of ... elif ((-xlSnapMB +AvailMB <=3D0)); then # Variables need not be preceeded = by '$' in arithmetic expansion; # the above syntax does not work if you test ((-$xlSnapMB +$AvailMB)), s= o you'll # have to put up with the confusion over referencing variables without t= he preceding # $-sign sometimes echo $'\n\t'"FATAL: No space left in $DataVG for snapshot creation. Termi= nating ..." >&2 SnapTest=3D19 # lvcreate error: "19 not enoungh space available to create= logical volume" else # Use all the free space available in $DataVG for the snapshot! [[ -n "$xlogLV" ]] && xfs_freeze -f "$pgData/pg_xlog" xfs_freeze -f "$DataFS" [[ -n "$xlogLV" ]] && { lvcreate -s -L "$xlSnapMB"M -n "$xlSnapNM" "$xlog= LV"; xlSnapTest=3D$?; } lvcreate -s -L "$((-xlSnapMB +AvailMB))"M -n "$SnapNM" "$DataLV" SnapTest=3D$? xfs_freeze -u "$DataFS" [[ -n "$xlogLV" ]] && xfs_freeze -u "$pgData/pg_xlog" fi # If snapshot creation failed bail; else mount the snapshot volume if ((+$xlSnapTest +$SnapTest >0)); then ExitOnError 991$SnapTextXL$SnapTest "pgBackup: Snapshot creation failed" else echo $'\n'"$(DateCMD) Successfully created snapshot(s):" lvscan | grep "snap_$ThisSession" # Report snapshot volumes list # If /mnt/ subfolders named "*snap_$ThisSession" exist, # delete them for SDir in $(find /mnt/ -maxdepth 1 -type d -name "*snap_$ThisSession");= do rm -rf $Sdir done mkdir "/mnt/$SnapNM" if [[ -n "$xlogLV" ]]; then mkdir "/mnt/$xlSnapNM" mount -t xfs "$DataVG/$xlSnapNM" "/mnt/$xlSnapNM" -o nouuid,ro xlMountTest=3D$? fi LogDev=3D"$(cat "/etc/mtab" | grep "$DataFS\>[^/]" | awk 'BEGIN{FS=3D"log= dev=3D"}{print $2}' | awk 'BEGIN{FS=3D","}{print $1}')" if [[ -n "$LogDev" ]]; then mount -t xfs "$DataVG/$SnapNM" "/mnt/$SnapNM" -o nouuid,ro,logdev=3D"$L= ogDev" MountTest=3D$? else mount -t xfs "$DataVG/$SnapNM" "/mnt/$SnapNM" -o nouuid,ro MountTest=3D$? fi fi # If snapshot mount failed, then bail; # else print mount report and return if ((+xlMountTest +MountTest >0)); then if [[ -n "$xlogLV" ]]; then umount "$DataVG/$xlSnapNM" &> /dev/null lvremove -f "$DataVG/$xlSnapNM" fi umount "$DataVG/$SnapNM" &> /dev/null lvremove -f "$DataVG/$SnapNM" ExitOnError 992 "pgBackup; Snapshot mount failed" else echo $'\n'"$(DateCMD) Successfully mounted snapshot(s):" mount | grep "snap_$ThisSession" # Report the mounts fi return 0 # Only returns on success } # Setup logging, and get going: STASFile=3D"$pgData/pgSnapBack.status" LogFile=3D"/home/$pgSuper/$ThisCMD-$(date +%Y%m%d-%H%M)" exec 6>&1; exec > "$LogFile" exec 7>&2; exec 2> "$LogFile.err" echo "$(DateCMD) Backup initiated"\ | tee "$STASFile" # Phase 1 rsync # If the previous backup is still running, wait a bit: while [[ -f "$RsyncLockFile" ]]; do echo "$(DateCMD) previous Rsync still running"$'\n'\ "Waiting for completion" >&2 sleep 300 done # and now that it's not, proceed echo $'\n\n'\ "$(DateCMD) Phase 1 - rsync from pgdata to mirror on $BackupHost"\ | tee -a "$STASFile" # Before you start the backup, make sure that no postmaster is running on t= he backup folder: # (test cribbed from the pg_ctl script) while [[ -n $(RMTExec "sed -n 1p $pgDataBK/postmaster.pid 2>/dev/null") ]];= do echo "$(DateCMD) A postmaster is running at the remote \ location; waiting 5 minutes ..." sleep 300 done # You don't want to start simultaneous rsyncs, and you don't want to # start the postmaster on the "reflection" while an rsync is running; # so put down some lock files # (there was something on linux-xfs # about xfs' not recovering inodes allocated to empty files that # are deleted, so don't just "touch" a lockfile, put something in it). echo "$(DateCMD) Phase 1 - rsync" > "$RsyncLockFile" tail -n1 "$RsyncLockFile" | RMTExec "cat - > ~/RsyncLockFile" RsyncCMD "$pgData/" && echo $'\n'"$(DateCMD) Rsync complete"$'\n\n' # Now create and mount the snapshot volume echo $'\n\n'"$(DateCMD) Snapshot creation and mounting"\ | tee -a "$STASFile" DoSnap # Phase 2 rsync echo $'\n\n'"$(DateCMD) Phase 2 - rsync from snapshot to mirror on $BackupH= ost" echo "$(DateCMD) Phase 2 rsync" > "$RsyncLockFile" tail -n1 "$RsyncLockFile" | RMTExec "cat - > ~/RsyncLockFile" ( if [[ -n "$xlogLV" ]]; then RsyncCMD "--exclude=3Dpg_xlog/ $(echo $pgData | sed -e "s|$DataFS|/mnt/$Sn= apNM|")/" && RsyncCMD "/mnt/$xlSnapNM/" "pg_xlog/" else RsyncCMD "$(echo $pgData | sed -e "s|$DataFS|/mnt/$SnapNM|")/" fi ) && echo $'\n\t'"$(DateCMD) Phase 2 rsync complete"$'\n'\ | tee -a "$STASFile" # Now remove the snapshot rm -f "$RsyncLockFile" RMTExec "rm -f ~/RsyncLockFile" if [[ -n "$xlogLV" ]]; then umount "$DataVG/$xlSnapNM" lvremove -f "$DataVG/$xlSnapNM" fi umount "$DataVG/$SnapNM" lvremove -f "$DataVG/$SnapNM" find /mnt/ -maxdepth 1 -type d -name "*snap_$ThisSession" -exec rm -rf {} \; # "Reinitialize" the snapshot of the database on the backup server echo $'\n\n'"$(DateCMD) Start postmaster on $BackupHost, capture output, an= d shutdown" RMTExec "/usr/local/pgsql-$pgMinor/bin/postmaster -B4096 -p55432\ -D $pgDataBK 2>&1" & sleep 300 # give the postmaster time for recovery RMTExec "/usr/local/pgsql-$pgMinor/bin/pg_ctl stop -D $pgDataBK -m immediat= e" && echo $'\n\n'"$(DateCMD) Attempted to reinitialize backup; check for errors= above" # Mop up if [[ ! -s "$LogFile.err" ]]; then echo "Completed, no errors" > "$LogFile.err" rm -f "$LogFile.err" echo $'\n\n'"$(DateCMD) Backup completed."\ | tee -a "$STASFile" echo "$(DateCMD) Verify postmaster-messages, listed above, for success." exec >&6 6>&- exec 2>&7 7>&- exit 0 else ExitOnError 999 "pgBackup; Backup encountered errors" fi true # Superflous ------_=_NextPart_000_01C394BD.540875DA--