Re: postgres crash - Mailing list pgsql-novice

Hi

On 3 August 2011 22:15, Matthew Fairley <mattfairley@netscape.net> wrote:
> Thanks for the reply Tom - I'll give the man page a read. A couple of comments below, just to clarify though.
>
> Regards,
>
> Matt
>
> Sent from my iPhone
>
> On 3 Aug 2011, at 19:27, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> mattfairley@netscape.net writes:
>>> I am a complete newb with postgres. In fact, I don't even use it as
>>> such but have a problem with a crashed postgresql db associated with
>>> using Calendar Server on Mac OS X 10.6 on a Mac Mini. To cut a long
>>> story short, I had to reformat my hard drive and restore form a Time
>>> Machine backup.
>>
>> Yeah, that's not exactly the approved way to back up a Postgres
>> instance; you're likely to get a collection of files that are somewhat
>> out-of-sync with each other, which seems to be exactly what this is
>> about:
>
> To clarify - the whole hard drive was restored from the Time Machine back up, not just the db, so I would have
thoughtit 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.

> However, due to hard drive corruption, is it possible that the restored database is screwy?

Yes, but as mentioned above this could also just be as a result of the
way Time Machine does the backups.

> From what I can gather things went a bit wrong with the hard drive about a week before I noticed it.

[...]
>>> I've tried using pg_resetxlog with the -f switch but I don't really know what I'm doing.
>>
>> pg_resetxlog is pretty much the only way out, given that you don't have
>> any other form of backup.  But you haven't shown us exactly what you did
>> or exactly how it failed.
>>
> 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.

--
Michael Wood <esiotrot@gmail.com>