Обсуждение: Removing a corrupt database by hand
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? 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
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:
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:
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
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:
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
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 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