Re: Postgres will not start due to corrupt index

On Wednesday, October 3, 2012 at 10:44 AM, Merlin Moncure wrote:

On Wed, Oct 3, 2012 at 9:33 AM, Robert Sosinski

<rsosinski@ticketevolution.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