Re: Removing a corrupt database by hand

[Mike Christensen <imaudi@xxxxxxxxxxx>]

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