Skip site navigation (1) Skip section navigation (2)

Re: Please help - corrupt database

From: "Ertel, Steve" <Steve(dot)Ertel(at)infimatic(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Please help - corrupt database
Date: 2008-12-16 15:32:06
Message-ID: 863ECFDF04C8804ABDF3559D87F157C601472D6C@MAGPTCPEXC02.na.mag-ias.net (view raw or flat)
Thread:
Lists: pgsql-novice
Thanks Tom.  We recently added REINDEX DATABASE to satisfy a customer that swears that databases need to be routinely reindexed.  In the customer's mind, this is especially true because we are not running sql server nor windows.  I'll take the command out of our backup script and pass on the info that you provided.


Best regards,
 
Steve Ertel
859-534-4568
steve(dot)ertel(at)infimatic(dot)com
 

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
Sent: Tuesday, December 16, 2008 10:24 AM
To: Ertel, Steve
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Please help - corrupt database 

"Ertel, Steve" <Steve(dot)Ertel(at)infimatic(dot)com> writes:
> From the log files you can see that the backup does a REINDEX DATABASE 
> db_name While this reindex is running, several other queries are running against the database wrapped in a BEGIN/COMMIT transaction.
> After the transaction query was issued, the log file provides the 
> message NOTICE:  table "pg_class" was reindexed A few more queries run 
> and then the log file provides the error message: ERROR:  could not 
> find pg_class tuple for index 2663
 
> Is there anything that we can do to recover from this issue?

REINDEX DATABASE during active database operations is just asking for trouble in 8.1 :-(.  There are race conditions that can be triggered if another backend opens a reindexed index at the wrong time.

My advice is to stop doing the REINDEX DATABASE; I don't think that's necessary or appropriate as a routine maintenance operation.  If you insist on doing it, update to a recent 8.2.x or 8.3.x release, where it should be reasonably safe.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: 丰上Date: 2008-12-17 04:36:14
Subject: How to setup client auth?
Previous:From: Tom LaneDate: 2008-12-16 15:23:41
Subject: Re: Please help - corrupt database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group