Thread: Postgres will not start due to corrupt index

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

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

Robert Sosinski <rsosinski@ticketevolution.com> writes:
> We are running Postgres 9.1.3, and after stopping it by physically shutting off the machine, we rebooted and now get
thiserror whenever we try to start it.  
> 2012-10-02 13:54:30 PDT PANIC:  GIN metapage disappeared

This looks like an issue that was fixed in 9.1.4.  Update and it should
start successfully (assuming your machine does fsyncs correctly;
otherwise you might have some actual data corruption there ...)

            regards, tom lane

From: Robert Sosinski [mailto:rsosinski@ticketevolution.com] 
Sent: Wednesday, October 03, 2012 10:54 AM
To: Merlin Moncure
Cc: pgsql-general@postgresql.org; Spike Grobstein
Subject: 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
futurethough.
 

Thanks again for the help,

-- 
Robert Sosinski


I wonder if there is a column in pg catalog, that indicates the type of the index.  I couldn't find one.
So, I ran the following sql trying to find system indexes of gin or gist type:

select * from pg_indexes where schemaname = 'pg_catalog' and (indexdef like '%USING gist%' OR indexdef like '%USING
gin%');

and it returned zero rows.

Regards,
Igor Neyman

Igor Neyman <ineyman@perceptron.com> writes:
> I wonder if there is a column in pg catalog, that indicates the type of the index.  I couldn't find one.

join relam to pg_am.oid

> So, I ran the following sql trying to find system indexes of gin or gist type:

There aren't any.

            regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, October 03, 2012 2:47 PM
> To: Igor Neyman
> Cc: Robert Sosinski; Merlin Moncure; pgsql-general@postgresql.org;
> Spike Grobstein
> Subject: Re: [GENERAL] Postgres will not start due to corrupt index
>
> Igor Neyman <ineyman@perceptron.com> writes:
> > I wonder if there is a column in pg catalog, that indicates the type
> of the index.  I couldn't find one.
>
> join relam to pg_am.oid
>
> > So, I ran the following sql trying to find system indexes of gin or
> gist type:
>
> There aren't any.
>
>             regards, tom lane

Tom, thank you.

In this case:

select i.indexname, a.amname, i.tablename from pg_indexes i JOIN
(pg_class c join pg_am a ON (c.relam = a.oid) ) ON (i.indexname = c.relname)
WHERE i.schemaname = 'pg_catalog';

Regards,
Igor Neyman

In case corruption of SQL server, you can try any third party application to
repair sql database. when you search on the internet you will get lots of
option but i would suggest you "RecoveryFix for SQL Database" software
because this company offer the free trial version of software to get the
results in preview items.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Postgres-will-not-start-due-to-corrupt-index-tp5726462p5727130.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.