Re: [ADMIN] [PERFORM] backup/restore - another area.
Friday, October 17, 2003 12:05, Tom Lane [mailto:[EMAIL PROTECTED] wrote: Murthy Kambhampaty [EMAIL PROTECTED] writes: ... 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. It does? How exactly can you ensure snapshot consistency between data files and XLOG if they are on different filesystem Say, you're setup looks something like this: mount -t xfs /dev/VG1/LV_data /home/pgdata mount -t xfs /dev/VG1/LV_xlog /home/pgdata/pg_xlog When you want to take the filesystem backup, you do: Step 1: xfs_freeze -f /dev/VG1/LV_xlog xfs_freeze -f /dev/VG1/LV_data This should finish any checkpoints that were in progress, and not start any new ones till you unfreeze. (writes to an xfs_frozen filesystem wait for the xfs_freeze -u, but reads proceed; see text from xfs_freeze manpage in postcript below.) Step2: create snapshots of /dev/VG1/LV_xlog and /dev/VG1/LV_xlog Step 3: xfs_freeze -u /dev/VG1/LV_data xfs_freeze -u /dev/VG1/LV_xlog Unfreezing in this order should assure that checkpoints resume where they left off, then log writes commence. Step4: mount the snapshots taken in Step2 somewhere; e.g. /mnt/snap_data and /mnt/snap_xlog. Copy (or rsync or whatever) /mnt/snap_data to /mnt/pgbackup/ and /mnt/snap_xlog to /mnt/pgbackup/pg_xlog. Upon completion, /mnt/pgbackup/ looks to the postmaster like /home/pgdata would if the server had crashed at the moment that Step1 was initiated. As I understand it, during recovery (startup) the postmaster will roll the database forward to this point, checkpoint-ing all the transactions that made it into the log before the crash. Step5: remove the snapshots created in Step2. The key is (i) xfs_freeze allows you to quiesce any filesystem at any point in time and, if I'm not mistaken, the order (LIFO) in which you freeze and unfreeze the two filesystems: freeze $PGDATA/pg_xlog then $PGDATA; unfreeze $PGDATA then $PGDATA/pg_xlog. (ii) WAL recovery assures consistency after a (file)sytem crash. Presently, the test server for my backup scripts is set-up this way, and the backup works flawlessly, AFAICT. (Note that the backup script starts a postmaster on the filesystem copy each time, so you get early warning of problems. Moreover the data in the production and backup copies are tested and found to be identical. Comments? Any suggestions for additional tests? Thanks, Murthy PS: From the xfs_freeze manpage: xfs_freeze suspends and resumes access to an XFS filesystem (see xfs(5)). xfs_freeze halts new access to the filesystem and creates a stable image on disk. xfs_freeze is intended to be used with volume managers and hardware RAID devices that support the creation of snapshots. The mount-point argument is the pathname of the directory where the filesystem is mounted. The filesystem must be mounted to be frozen (see mount(8)). 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. Note that even after freezing, the on-disk filesystem can contain information on files that are still in the process of unlinking. These files will not be unlinked until the filesystem is unfrozen or a clean mount of the snapshot is complete. The -u option is used to un-freeze the filesystem and allow operations to continue. Any filesystem modifications that were blocked by the freeze are unblocked and allowed to complete. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] backup/restore - another area.
On Tue, 14 Oct 2003, [EMAIL PROTECTED] wrote: I'm curious to what kind of testing you've done with LVM. I'm not currently trying any backup/restore stuff, but I'm running our DBT-2 workload using LVM. I've started collecting vmstat, iostat, and readprofile data, initially running disktest to gauge the performance. [added -admin to this, since this is very relevant there] I was going to post this data yesterday, but I had severe inet issues. So, I tried this out with lvm2 on 2.4.21 on a 2xp2-450 with 2 disks. (I just looked at your 14 and 52 disk data. drool.) So I have a db which is about 3.2GB on disk. All backups were done to an nfs mount, but I ran a network monitor to check bandwidth usage. I note where things were io bound. backing up: pg_dump: 18m [cpu bound] pg_dump | gzip -1: 18m [cpu bound] snapshot, then tar: 4m [io bound] snapshot, then tar | gzip: 21m [cpu bound] The times for a compressed backup are a bit slower for snapshots, but this is where the snapshot method wins tacos - restore. restore: psql: 158m snapshot:8m Yes folks, 8m. When I started PG back up it checked the WAL and got itself back online. The benefits of the pg_dump backup afaict are that the data is in a format readable to anything and is [mostly] cross-pg compatible. The downside is it seems to be quite slow and restoring it can be long and tiresome. The benefits of the snapshot are that backups are very, very quick and restore is very, very quick (It won't need to re-enable foriegn keys, no need to rebuild indexes, no need to re-vacuum analyze). The downside is this method will only work on that specific version of PG and it isn't the cleanest thing in the world since you are essentially simulating a power failure to PG. Luckly the WAL works like a champ. Also, these backups can be much larger since it has to include the indexes as well. but this is a price you have to pay. I did have some initial problems with snapshots corruption but it turned out to be user-error on my part. COOL HUH? -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] backup/restore - another area.
Jeff, The downside is this method will only work on that specific version of PG and it isn't the cleanest thing in the world since you are essentially simulating a power failure to PG. Luckly the WAL works like a champ. Also, these backups can be much larger since it has to include the indexes as well. but this is a price you have to pay. The other downside is, of course, that the database needs to be shut down. COOL HUH? Certainly very useful in the DBA's arsenal of backup tools. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] backup/restore - another area.
On Thu, 16 Oct 2003 09:49:59 -0700 Josh Berkus [EMAIL PROTECTED] wrote: Jeff, The downside is this method will only work on that specific version of PG and it isn't thecleanest thing in the world since you are essentially simulating a power failure to PG. Luckly the WAL works like a champ. Also, these backups can be much larger since it has to include the indexes as well. but this is a price you have to pay. The other downside is, of course, that the database needs to be shut down. 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) You could do pg_ctl stop; snapshot; pg_ctls tart for a clean image. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] backup/restore - another area.
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? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] backup/restore - another area.
On Thu, 16 Oct 2003 10:09:27 -0700 Josh Berkus [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] backup/restore - another area.
Jeff, I'm curious to what kind of testing you've done with LVM. I'm not currently trying any backup/restore stuff, but I'm running our DBT-2 workload using LVM. I've started collecting vmstat, iostat, and readprofile data, initially running disktest to gauge the performance. For anyone curious, I have some data on a 14-disk volume here: http://developer.osdl.org/markw/lvm/results.4/log/ and a 52-disk volume here: http://developer.osdl.org/markw/lvm/results.5/data/ Mark Jeff [EMAIL PROTECTED] writes: Idea #1: Use an LVM and take a snapshop - archive that. From the way I see it. the downside is the LVM will use a lot of space until the snapshot is removed. Also PG may be in a slightly inconsistant state - but this should appear to PG the same as if the power went out. For restore, simply unarchive this snapshot and point postgres at it. Let it recover and you are good to go. Little overhead from what I see... I'm leaning towards this method the more I think of it. ---(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
Re: [PERFORM] backup/restore - another area.
On 9 Oct 2003, Greg Stark wrote: I don't quite follow your #2 so I can only comment on the above idea of using an LVM snapshot. If you have the hardware and the LVM-fu to be able to do this properly I would recommend it. Just to be a bit clearer incase it was my wording: Method #2 is nearly identical to method #1, except that no logical volume manager is needed. We cannot just cp $PGDATA because it is (or could be) changing and we need to take data from a constitant point. So what we do is write code that understands xids and all that and simply dumps out the pages of data in a raw form that can be quickly reloaded. The key is that the data be in a somewhat consistant state. Method #2 requires a ton more work but it would be able to run on platforms without an lvm (or requiring the use of an lvm). Does that make more sense? The idea here is to backup restore as fast as possible, throwing away some things like inter-version compat and whatnot. Being able to add fast backup / restore is a good thing in the list of enterprise features. Also, I wouldn't consider this a replacement for having a pg_dump export. In a crisis when you want to restore everything *exactly* the way things were you want the complete filesystem snapshot. But if you just want to load a table the way it was the day before to compare, or if you want to load a test box to do some performance testing, or whatever, you'll need the logical export. Yeah, a pg_dump now and then would be useful (and safe). If you wanted to get fancy schmancy you could take the snapshot, archive it, transfer it and unarchive it on machine B. (We actually used to do that here until machine B no longer had the capacity to hold all our data :) -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] backup/restore - another area.
Boy, I must be getting annoying by now huh? Anyway, after the joys of Solaris being fast I'm moving onto another area - backup restore. I've been checking the archives and haven't seen any good tips for backing up big databases (and more importantly, restoring). I've noticed while doing a backup (with both -Fc and regular recipe) that my IO is no where near being stressed. According to vmstat, it sits around reading about 512kB/sec (with occasional spikes) and every 5-6 seconds it writes out a 3MB hunk. So as a test I decided to cp a 1GB file and got a constant read speed of 20MB/sec and the writes. well. were more sporatic (buffering most likely) and it would write out 60MB every 3 seconds. And. then.. on the restore I notice similar things - IO hardly being stressed at all... reading in at ~512kB/sec and every now and then writing out a few MB. So, I've been thinking of various backup/restore strategies... some I'm sure some people do, some need code written and may be controvertial.. Idea #1: Use an LVM and take a snapshop - archive that. From the way I see it. the downside is the LVM will use a lot of space until the snapshot is removed. Also PG may be in a slightly inconsistant state - but this should appear to PG the same as if the power went out. For restore, simply unarchive this snapshot and point postgres at it. Let it recover and you are good to go. Little overhead from what I see... I'm leaning towards this method the more I think of it. Idea #2: a new program/internal system. Lets call it pg_backup. It would generate a very fast backup (that restores very fast) at the expense of disk space. Pretty much what we would do is write out new copies of all the pages in the db - both indexes and tables. the pro's to this is it does not depend on an LVM and therefore is accessable to all platforms. it also has the other benfets mentioned above, except speed. For a restore PG would need something like a 'restore mode' where we can just have it pump pages into it somehow.. It would not have to build index, check constraints, and all that because by definition the backup would contain valid data. The downside for both of these are that the backup is only good for that version of PG on that architecture. Speaking in Informix world this is how it is - it has a fast backup fast restore that does essentially #2 and then it has export/import options (works like our current pg_dump and restore). and oh yeah -I've tried disabling fsync on load and while it did go faster it was only 2 minutes faster (9m vs 11m). Any thoughts on this? What do you ther folk with big db's do? -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] backup/restore - another area.
Jeff [EMAIL PROTECTED] writes: Idea #1: Use an LVM and take a snapshop - archive that. From the way I see it. the downside is the LVM will use a lot of space until the snapshot is removed. Also PG may be in a slightly inconsistant state - but this should appear to PG the same as if the power went out. For restore, simply unarchive this snapshot and point postgres at it. Let it recover and you are good to go. Little overhead from what I see... I'm leaning towards this method the more I think of it. I don't quite follow your #2 so I can only comment on the above idea of using an LVM snapshot. If you have the hardware and the LVM-fu to be able to do this properly I would recommend it. We actually used to do this with veritas even on Oracle which has full online backup support simply because it was much much faster and the snapshot could be backed up during peak times without any significant performance impact. That's partly because Veritas and Hitachi storage systems kick butt though. Depending on the systems you're considering you may or may not have nearly the same success. Note, you should *test* this backup. You're depending on some subtle semantics with this. If you do it slightly wrong or the LVM does something slightly wrong and you end up with an inconsistent snapshot or missing some critical file the whole backup could be useless. Also, I wouldn't consider this a replacement for having a pg_dump export. In a crisis when you want to restore everything *exactly* the way things were you want the complete filesystem snapshot. But if you just want to load a table the way it was the day before to compare, or if you want to load a test box to do some performance testing, or whatever, you'll need the logical export. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
