Обсуждение: Postgres will not start due to corrupt index
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?
Thanks,
--
Robert Sosinski
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
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@ticketevolution.com> wrote:We are running Postgres 9.1.3, and after stopping it by physically shuttingoff the machine, we rebooted and now get this error whenever we try to startit.2012-10-02 13:54:30 PDT LOG: database system was interrupted; last known upat 2012-10-02 13:46:20 PDT2012-10-02 13:54:30 PDT LOG: database system was not properly shut down;automatic recovery in progress2012-10-02 13:54:30 PDT LOG: redo starts at A/764C4C02012-10-02 13:54:30 PDT PANIC: GIN metapage disappeared2012-10-02 13:54:30 PDT CONTEXT: xlog redo Update metapage, node:1663/899422/21182896 blkno: 22012-10-02 13:54:30 PDT LOG: startup process (PID 940) was terminated bysignal 6: Aborted2012-10-02 13:54:30 PDT LOG: aborting startup due to startup processfailure2012-10-02 14:42:49 PDT LOG: database system was interrupted while inrecovery at 2012-10-02 13:54:30 PDT2012-10-02 14:42:49 PDT HINT: This probably means that some data iscorrupted 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 progress2012-10-02 14:42:49 PDT LOG: redo starts at A/764C4C02012-10-02 14:42:49 PDT PANIC: GIN metapage disappeared2012-10-02 14:42:49 PDT CONTEXT: xlog redo Update metapage, node:1663/899422/21182896 blkno: 22012-10-02 14:42:49 PDT LOG: startup process (PID 954) was terminated bysignal 6: Aborted2012-10-02 14:42:49 PDT LOG: aborting startup due to startup processfailureI guess it is a problem with an index, because it is saying that there in aGIN metapage missing. Any idea how to get postgres to boot up after it getsinto this condition without having to recover from a backup? Wouldupgrading 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-userPostgreSQL server with the -P option included on its command line.Then, REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or REINDEXINDEX can be issued, depending on how much you want to reconstruct. Ifin doubt, use REINDEX SYSTEM to select reconstruction of all systemindexes in the database. Then quit the single-user server session andrestart the regular server. See the postgres reference page for moreinformation about how to interact with the single-user serverinterface."Not sure if things have been improved in 9.2 -- historically gist/ginhaven'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.