Re: Postgres will not start due to corrupt index

On Wed, Oct 3, 2012 at 9:33 AM, Robert Sosinski
<rsosinski(at)ticketevolution(dot)com> wrote:
> We are running Postgres 9.1.3, and after stopping it by physically shutting
> off the machine, we rebooted and now get this error whenever we try to start
> it.
>
> 2012-10-02 13:54:30 PDT LOG: database system was interrupted; last known up
> at 2012-10-02 13:46:20 PDT
> 2012-10-02 13:54:30 PDT LOG: database system was not properly shut down;
> automatic recovery in progress
> 2012-10-02 13:54:30 PDT LOG: redo starts at A/764C4C0
> 2012-10-02 13:54:30 PDT PANIC: GIN metapage disappeared
> 2012-10-02 13:54:30 PDT CONTEXT: xlog redo Update metapage, node:
> 1663/899422/21182896 blkno: 2
> 2012-10-02 13:54:30 PDT LOG: startup process (PID 940) was terminated by
> signal 6: Aborted
> 2012-10-02 13:54:30 PDT LOG: aborting startup due to startup process
> failure
> 2012-10-02 14:42:49 PDT LOG: database system was interrupted while in
> recovery at 2012-10-02 13:54:30 PDT
> 2012-10-02 14:42:49 PDT HINT: This probably means that some data is
> corrupted and you will have to use the last backup for recovery.
> 2012-10-02 14:42:49 PDT LOG: database system was not properly shut down;
> automatic recovery in progress
> 2012-10-02 14:42:49 PDT LOG: redo starts at A/764C4C0
> 2012-10-02 14:42:49 PDT PANIC: GIN metapage disappeared
> 2012-10-02 14:42:49 PDT CONTEXT: xlog redo Update metapage, node:
> 1663/899422/21182896 blkno: 2
> 2012-10-02 14:42:49 PDT LOG: startup process (PID 954) was terminated by
> signal 6: Aborted
> 2012-10-02 14:42:49 PDT LOG: aborting startup due to startup process
> failure
>
> I guess it is a problem with an index, because it is saying that there in a
> GIN metapage missing. Any idea how to get postgres to boot up after it gets
> into this condition without having to recover from a backup? Would
> upgrading to 9.2 prevent this issue from happening again?

You an boot it up in single user mode and force a reindex:
"One way to do this is to shut down the server and start a single-user
PostgreSQL server with the -P option included on its command line.
Then, REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or REINDEX
INDEX can be issued, depending on how much you want to reconstruct. If
in doubt, use REINDEX SYSTEM to select reconstruction of all system
indexes in the database. Then quit the single-user server session and
restart the regular server. See the postgres reference page for more
information about how to interact with the single-user server
interface."

(via http://www.postgresql.org/docs/9.2/static/sql-reindex.html)

Not sure if things have been improved in 9.2 -- historically gist/gin
haven't been as robust in terms of WAL/crash recovery IIRC.

merlin