Re: Need Help Recovering from Botched Upgrade Attempt

Alan Hodgson wrote:
> On Wednesday 18 June 2008, Craig Ringer <craig@postnewspapers.com.au> wrote:
>>>   Every file from /var/lib/pgsql/ before I started this is on the
>>> weekly backup tape from last Friday night. If need be I can restore
>>> from that and start over.
>> Well, no worries then. I'm sure you can understand that for many people
>> - way TOO many people - that is not the case, so it's well worth
>> stressing the point.
>
> If the database was in use when _that_ backup was taken, it may also not be
> usable.
>
> You can't just backup a live database from the filesystem level and expect
> it to work ...

It should be OK, if less than ideal, if:

- You have fsync enabled (which you do if you care about your data); and
- Your filesystem supports consistent snapshots

If you can take a point-in-time snapshot at the filesystem level and
copy that, it should be OK. Pg will still have to do a bunch of work
when started up off the restored data, though.

It makes much more sense to just warn Pg about the copy about to be
taken, or use pg_dump . Any decent backup system will provide hooks to
run pre- and post- scripts to do this sort of thing.

--
Craig Ringer