Re: postgres crash

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> mattfairley(at)netscape(dot)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 thought it would have been ok, that things would have been in sync, rather than out of sync. However, due to hard drive corruption, is it possible that the restored database is screwy? From what I can gather things went a bit wrong with the hard drive about a week before I noticed it.

>> LOG: database system was interrupted; last known up at 2011-07-15 07:48:24 BST
>> LOG: unexpected pageaddr 0/2058000 in log file 0, segment 4, offset 360448
>> LOG: invalid primary checkpoint record
>> LOG: unexpected pageaddr 0/2056000 in log file 0, segment 4, offset 352256
>> LOG: invalid secondary checkpoint record
>> PANIC: could not locate a valid checkpoint record
>
>> 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?
> The man page for pg_resetxlog is reasonably thorough, did you read it?
> http://www.postgresql.org/docs/9.0/static/app-pgresetxlog.html
>
> (Once you do get it to start again, you'll at minimum want to do a
> complete database reindex, and ideally a dump/re-initdb/reload, to try
> to make sure there's not lingering database corruption.)
>

How do I do all that?

> regards, tom lane