Re: backup/restore - another area.

Jeff <threshar(at)torgo(dot)978(dot)org> 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