pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrisks of backup using filesystem snapshot/dump ?
2+ messages / 2 participants
[nested] [flat]
* risks of backup using filesystem snapshot/dump ?
@ 2013-04-06 16:40 Jim Mercer <jim@reptiles.org>
0 siblings, 1 reply; 2+ messages in thread
From: Jim Mercer @ 2013-04-06 16:40 UTC (permalink / raw)
To: pgsql-admin
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"
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: risks of backup using filesystem snapshot/dump ?
@ 2013-04-08 07:20 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Jim Mercer <jim@reptiles.org>
0 siblings, 0 replies; 2+ messages in thread
From: Albe Laurenz @ 2013-04-08 07:20 UTC (permalink / raw)
To: Jim Mercer *EXTERN* <jim@reptiles.org>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-04-08 07:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-06 16:40 risks of backup using filesystem snapshot/dump ? Jim Mercer <jim@reptiles.org>
2013-04-08 07:20 ` Albe Laurenz <laurenz.albe@wien.gv.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox