Re: Removing a corrupt database by hand

Hey all!

After working non-stop for 7 hours straight, I managed to fix my
problem.. It boiled down to a massive backlog of transactions, and I
found a little utility called pg_resetxlog.exe that did the /exact/
thing I needed. I forced a reset of the transaction log and got the
server to startup in user mode at which point I immediately made a noise
that probably woke the neighbors, and I feverishly clicked the "Backup"
button. The bad DB has been nuked and now everything is running along
nicely.

Had I not been able to recover my good DBs, that would have been about
five months of work gone. I have a little tool that dumps my DB to an
XML file (the only file format I really trust), and I'm gonna configure
that to run nightly. Thanks for all the help guys!

Mike

Mike Christensen wrote:
> After looking at it more, it seems the problem is it's trying to
> rebuild a corrupt DB and not really getting anywhere. The DB is about
> 900 megs, and I've given it 6-7hrs to try to repair and it just seems
> to be stuck.
>
> If I delete the DB directory, I get:
>
> 2009-02-19 00:02:05 PST FATAL: index "41330" contains unexpected zero
> page at b
> lock 0
> 2009-02-19 00:02:05 PST HINT: Please REINDEX it.
>
> The process just quits if it can't repair the DB. How can I tell it
> not to try to repair this database and just let me access the other
> DBs? Thanks!
>
> Mike
>
> Joshua D. Drake wrote:
>> On Wed, 2009-02-18 at 19:58 -0800, Mike Christensen wrote:
>>
>>> I have two questions actually..
>>>
>>> First off, is there a way to remove a database if the postgres.exe
>>> service won't start? It seems if I just delete the data\base\xxxxx
>>> directory, then postgres will crash. Is there a way to drop a DB that
>>> prevents postgres.exe from starting?
>>>
>>>
>>
>> What happens if you start postgresql in single user mode?
>>
>>
>>> Second question, if I just re-install postgres and create a fresh new
>>> instance, is there a way to import my old data files back into the
>>> database? Thanks!
>>>
>>> Mike
>>>
>>>