Обсуждение: risks of backup using filesystem snapshot/dump ?
hi, i'm running pgsql 9.1.3 (soon to be upgraded to 9.2.x) on FreeBSD 8-STABLE. i've got a db of some 550GB that i want to backup on a daily basis, as we have had some nasty hardware/power/other issues of late. with dump on FreeBSD, you can specify -L, which does a filesystem level snapshot before the dump starts, and then clears it afterwards. i did a backup using dump on the filesystems, except for $PGDATA, followed by a pg_dumpall. this took 4 hours, and ate some 30GB (after compression) i'm currently doing a dump of the filesystems, including $PGDATA. this is looking like it will take 6-7 hours, and looks like it will take some 40-50GB (after compression) if i were to attempt a restore by replaying the 'pg_dumpall', i suspect that i would be looking at many hours, and possibly hiccups requiring re-runs, etc. i'm alot more comfortable with the idea of just restoring the filesystem dump. so, the question becomes, if i restore a filesystem snapshot backup, the DB will be in a 'crashed' state. what, if any, risks are there with regards to corruption, or the inability to actually use the restored DB? -- Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633 "He who dies with the most toys is nonetheless dead"
Jim Mercer wrote: > i'm running pgsql 9.1.3 (soon to be upgraded to 9.2.x) on FreeBSD 8-STABLE. > > i've got a db of some 550GB that i want to backup on a daily basis, as we > have had some nasty hardware/power/other issues of late. > > with dump on FreeBSD, you can specify -L, which does a filesystem level > snapshot before the dump starts, and then clears it afterwards. > > i did a backup using dump on the filesystems, except for $PGDATA, followed > by a pg_dumpall. > this took 4 hours, and ate some 30GB (after compression) > > i'm currently doing a dump of the filesystems, including $PGDATA. > this is looking like it will take 6-7 hours, and looks like it will take > some 40-50GB (after compression) > > if i were to attempt a restore by replaying the 'pg_dumpall', i suspect that > i would be looking at many hours, and possibly hiccups requiring re-runs, etc. > > i'm alot more comfortable with the idea of just restoring the filesystem > dump. > > so, the question becomes, if i restore a filesystem snapshot backup, the > DB will be in a 'crashed' state. > > what, if any, risks are there with regards to corruption, or the inability to > actually use the restored DB? If the filesystem snapshot is truly atomic, that should work fine: http://www.postgresql.org/docs/current/static/backup-file.html If you do a pg_start_backup before and a pg_stop_backup after the snapshot, you'd gain the additional advantage of point-in-time-recovery (if you archive WAL files). Yours, Laurenz Albe