Re: postgres crash

Michael Wood <esiotrot(at)gmail(dot)com> writes:
> On 3 August 2011 22:15, Matthew Fairley <mattfairley(at)netscape(dot)net> wrote:
>> To clarify - the whole hard drive was restored from the Time Machine back up, not just the db, so I would have thought it would have been ok, that things would have been in sync, rather than out of sync.

> That is most likely not good enough. If Time Machine creates a
> snapshot of the whole hard drive before copying the files to the
> backup location then it would be almost OK (as if you pulled the power
> cable out the back of the machine while it was running.) I don't
> think Time Machine does that, though. So while it's backing up one of
> Postgres' files, the others could still be modified. Then while it
> backs up the next one, again others might be modified. So they can be
> out of sync with each other as Tom says.

If you've ever watched Time Machine do its thing, you'll notice that it
actually makes two passes over your drive per backup session --- the
second pass copies any files that changed during the first pass. So
it's not even trying to deliver an exact single-instant filesystem
snapshot. (I doubt OS X has support for such a thing anyway.)

>> From memory, I ran:
>> pg_resetxlog -f /usr/local/pgsql/data
>> Was that right?

> You will need to use some of the other options mentioned in the
> documentation. Have a look at that and then ask again if you don't
> understand the documentation.

He may or may not need any other options. -f might not have been a good
idea though ... it would have been nice to see the output from
pg_resetxlog before that. For that matter, we still haven't seen what
happened after trying pg_resetxlog.

regards, tom lane