Thread: Corrupted index, what do i do?
Hello, today i restarted my postgresql 9.0 instance and it won't start. The logs are 2012-01-12 14:01:59.976 MSK,,,5644,,4f0eaf97.160c,2,,2012-01-12 14:01:59 MSK,,0,LOG,00000,"database system was not properly shut down; automatic recovery in progress",,,,,,,,,"" 2012-01-12 14:01:59.976 MSK,,,5644,,4f0eaf97.160c,3,,2012-01-12 14:01:59 MSK,,0,LOG,00000,"consistent recovery state reached at 2A/7FA53250",,,,,,,,,"" 2012-01-12 14:01:59.976 MSK,,,5644,,4f0eaf97.160c,4,,2012-01-12 14:01:59 MSK,,0,LOG,00000,"redo starts at 2A/7FA53250",,,,,,,,,"" 2012-01-12 14:01:59.999 MSK,,,5644,,4f0eaf97.160c,5,,2012-01-12 14:01:59 MSK,,0,LOG,00000,"unexpected pageaddr 2A/8CE6000 in log file 42, segment 127, offset 13524992",,,,,,,,,"" 2012-01-12 14:01:59.999 MSK,,,5644,,4f0eaf97.160c,6,,2012-01-12 14:01:59 MSK,,0,LOG,00000,"redo done at 2A/7FCE5E20",,,,,,,,,"" 2012-01-12 14:01:59.999 MSK,,,5644,,4f0eaf97.160c,7,,2012-01-12 14:01:59 MSK,,0,LOG,00000,"last completed transaction was at log time 2012-01-12 13:37:40.810534+04",,,,,,,,,"" 2012-01-12 14:02:00.040 MSK,,,5644,,4f0eaf97.160c,8,,2012-01-12 14:01:59 MSK,,0,FATAL,XX002,"index ""1339592"" contains unexpected zero page at block 328134",,"Please REINDEX it.",,,,,,,"" 2012-01-12 14:02:00.042 MSK,,,5642,,4f0eaf97.160a,1,,2012-01-12 14:01:59 MSK,,0,LOG,00000,"startup process (PID 5644) exited with exit code 1",,,,,,,,,"" 2012-01-12 14:02:00.042 MSK,,,5642,,4f0eaf97.160a,2,,2012-01-12 14:01:59 MSK,,0,LOG,00000,"aborting startup due to startup process failure",,,,,,,,,"" My question is corrupted index, not transaction rollbacks. How can i rebuild index if db aint start? I was running with fsync off, how postgresql behave about index consistency with fsync on? -- Evgeny
On 12/01/2012 9:08 PM, eshishki wrote: > 14:01:59 MSK,,0,FATAL,XX002,"index ""1339592"" contains unexpected > zero page at block 328134",,"Please REINDEX it.",,,,,,,"" > 2012-01-12 14:02:00.042 MSK,,,5642,,4f0eaf97.160a,1,,2012-01-12 > 14:01:59 MSK,,0,LOG,00000,"startup process (PID 5644) exited with exit > code 1",,,,,,,,,"" > 2012-01-12 14:02:00.042 MSK,,,5642,,4f0eaf97.160a,2,,2012-01-12 > 14:01:59 MSK,,0,LOG,00000,"aborting startup due to startup process > failure",,,,,,,,,"" > > My question is corrupted index, not transaction rollbacks. > How can i rebuild index if db aint start? Since you were running with fsync off, you must have had good backups or replication configured, because the documentation warns you that running with fsync=off will probably destroy your data and is only for expert users. In case you don't have a current replica or backup: first, before you do ANYTHING ELSE follow the instructions here: http://wiki.postgresql.org/wiki/Corruption Now - **AFTER** following the instructions above - start the database in single user recovery mode. See: http://www.postgresql.org/docs/current/static/app-postgres.html where the "--single" command line argument for the "postgres" executable is documented. Once you have a single user mode backend running you can REINDEX then shut the backend down, start back up in multi-user mode, and see if you have any luck. > I was running with fsync off, how postgresql behave about index > consistency with fsync on? Indexes will always be consistent when fsync is on. You were running in a mode that says "be fast even if you eat my data". It ate your data. If you don't tell it to, it won't. -- Craig Ringer
> Since you were running with fsync off, you must have had good backups or > replication configured, because the documentation warns you that running > with fsync=off will probably destroy your data and is only for expert users. Yes, i know, i have a replica and slow disk. > In case you don't have a current replica or backup: first, before you do > ANYTHING ELSE follow the instructions here: > > http://wiki.postgresql.org/wiki/Corruption > > Now - **AFTER** following the instructions above - start the database in > single user recovery mode. See: > > http://www.postgresql.org/docs/current/static/app-postgres.html > > where the "--single" command line argument for the "postgres" executable is > documented. > > Once you have a single user mode backend running you can REINDEX then shut > the backend down, start back up in multi-user mode, and see if you have any > luck. Good, may be said wiki page about corruption can be expanded to include this steps? -- Evgeny