Removing a corrupt database by hand

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

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

I get this:

C:\Program Files\PostgreSQL\8.3\data>..\bin\postgres.exe -d 5 -D .
2009-02-19 06:44:30 GMT DEBUG: postgres: PostmasterMain: initial
environ dump:
2009-02-19 06:44:30 GMT DEBUG: -----------------------------------------
2009-02-19 06:44:30 GMT DEBUG: ALLUSERSPROFILE=C:\Documents and
Setting
s\All Users
2009-02-19 06:44:30 GMT DEBUG:
ClusterLog=C:\WINDOWS\Cluster\cluster.lo
g
2009-02-19 06:44:30 GMT DEBUG: CommonProgramFiles=C:\Program
Files\Comm
on Files
2009-02-19 06:44:30 GMT DEBUG: COMPUTERNAME=KITCHENPC
2009-02-19 06:44:30 GMT DEBUG: ComSpec=C:\WINDOWS\system32\cmd.exe
2009-02-19 06:44:31 GMT DEBUG: CYGWIN=tty
2009-02-19 06:44:31 GMT DEBUG: FP_NO_HOST_CHECK=NO
2009-02-19 06:44:31 GMT DEBUG: lib=C:\Program Files\SQLXML 4.0\bin\
2009-02-19 06:44:31 GMT DEBUG: NUMBER_OF_PROCESSORS=4
2009-02-19 06:44:31 GMT DEBUG: OS=Windows_NT
2009-02-19 06:44:31 GMT DEBUG:
Path=c:\Perl\bin;C:\Perl\site\bin;C:\Per
l\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program
Files\M
icrosoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft Windows
Small Busi
ness Server\Networking\;C:\Program Files\Microsoft SQL
Server\90\Tools\binn\;C:\
Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program
Files\Microsoft SQL S
erver\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program
Files\Perforce\C:\Program Fi
les\Perforce
2009-02-19 06:44:31 GMT DEBUG:
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.J
S;.JSE;.WSF;.WSH
2009-02-19 06:44:31 GMT DEBUG: PROCESSOR_ARCHITECTURE=x86
2009-02-19 06:44:31 GMT DEBUG: PROCESSOR_IDENTIFIER=x86 Family
6 Model
15 Stepping 11, GenuineIntel
2009-02-19 06:44:31 GMT DEBUG: PROCESSOR_LEVEL=6
2009-02-19 06:44:31 GMT DEBUG: PROCESSOR_REVISION=0f0b
2009-02-19 06:44:31 GMT DEBUG: ProgramFiles=C:\Program Files
2009-02-19 06:44:31 GMT DEBUG: PROMPT=$P$G
2009-02-19 06:44:31 GMT DEBUG: SBSProgramDir=C:\Program
Files\Microsoft
Windows Small Business Server
2009-02-19 06:44:31 GMT DEBUG: SystemDrive=C:
2009-02-19 06:44:31 GMT DEBUG: SystemRoot=C:\WINDOWS
2009-02-19 06:44:31 GMT DEBUG:
TEMP=C:\DOCUME~1\postgres\LOCALS~1\Temp
2009-02-19 06:44:31 GMT DEBUG:
TMP=C:\DOCUME~1\postgres\LOCALS~1\Temp
2009-02-19 06:44:31 GMT DEBUG: USERDNSDOMAIN=KITCHENPC.LOCAL
2009-02-19 06:44:31 GMT DEBUG: USERDOMAIN=HOME
2009-02-19 06:44:31 GMT DEBUG: USERNAME=postgres
2009-02-19 06:44:31 GMT DEBUG: USERPROFILE=C:\Documents and
Settings\po
stgres
2009-02-19 06:44:31 GMT DEBUG: VISUALSVN_SERVER=C:\Program
Files\Visual
SVN Server\
2009-02-19 06:44:31 GMT DEBUG: windir=C:\WINDOWS
2009-02-19 06:44:31 GMT DEBUG: winsbprogramdir=C:\Program
Files\Windows
for Small Business Server
2009-02-19 06:44:31 GMT DEBUG: PGLOCALEDIR=C:/Program
Files/PostgreSQL/
8.3/share/locale
2009-02-19 06:44:31 GMT DEBUG: PGSYSCONFDIR=C:/Program
Files/PostgreSQL
/8.3/etc
2009-02-19 06:44:31 GMT DEBUG: LC_COLLATE=English_United
States.1252
2009-02-19 06:44:31 GMT DEBUG: LC_CTYPE=English_United States.1252
2009-02-19 06:44:31 GMT DEBUG: LC_MONETARY=C
2009-02-19 06:44:31 GMT DEBUG: LC_NUMERIC=C
2009-02-19 06:44:31 GMT DEBUG: LC_TIME=C
2009-02-19 06:44:31 GMT DEBUG: LC_MESSAGES=English_United
States.1252
2009-02-19 06:44:31 GMT DEBUG: -----------------------------------------
2009-02-19 06:44:31 GMT DEBUG: TZ "US/Pacific" matches Windows timezone
"Pacifi
c Standard Time"
2009-02-18 22:44:31 PST LOG: loaded library
"$libdir/plugins/plugin_debugger.dl
l"
2009-02-18 22:44:31 PST DEBUG: invoking IpcMemoryCreate(size=38395904)
2009-02-18 22:44:31 PST DEBUG: max_safe_fds = 987, usable_fds = 1000,
already_o
pen = 3

And it stays there forever.. I'm mucking around with stuff in the
debugger right now and it's looking like it's crashing due to bad file
handles.. In fact, this line might be the culprit:

SysLoggerPID = SysLogger_Start();

If I skip over it, it continues but then gets other errors due to bad
file handles.. Heh, whatever I did totally wrecked the DB.. We really
need some sort of dbrepair tool one of these days..

Mike

Joshua D. Drake 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:

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: