Re: Postgres will not start due to corrupt index

Hey Merlin,

Thanks. Starting postgres with -P was something that I did not try. Does postgres have any GIN or GIST system indexes though?

I would love to try it out, but the database has already been restored. Will definitely keep this in mind for the future though.

Thanks again for the help,

--
Robert Sosinski

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

> On Wed, Oct 3, 2012 at 9:33 AM, Robert Sosinski
> <rsosinski(at)ticketevolution(dot)com (mailto: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