X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6EB4E9FA4E3 for ; Mon, 17 Jul 2006 15:21:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 43674-06 for ; Mon, 17 Jul 2006 15:21:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from bay0-omc1-s6.bay0.hotmail.com (bay0-omc1-s6.bay0.hotmail.com [65.54.246.78]) by postgresql.org (Postfix) with ESMTP id C6D409FA418 for ; Mon, 17 Jul 2006 15:21:34 -0300 (ADT) Received: from hotmail.com ([64.4.38.32]) by bay0-omc1-s6.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 17 Jul 2006 11:21:32 -0700 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Mon, 17 Jul 2006 11:21:32 -0700 Message-ID: Received: from 64.4.38.200 by by116fd.bay116.hotmail.msn.com with HTTP; Mon, 17 Jul 2006 18:21:27 GMT X-Originating-IP: [66.160.12.211] X-Originating-Email: [bitsandbytes88@hotmail.com] X-Sender: bitsandbytes88@hotmail.com In-Reply-To: <4451.1153158045@sss.pgh.pa.us> From: "Mr. Dan" To: tgl@sss.pgh.pa.us Cc: pgsql-admin@postgresql.org Subject: Re: On-line backup Date: Mon, 17 Jul 2006 14:21:27 -0400 Mime-Version: 1.0 Content-Type: text/plain; format=flowed X-OriginalArrivalTime: 17 Jul 2006 18:21:32.0744 (UTC) FILETIME=[D4A26480:01C6A9CD] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/147 X-Sequence-Number: 22393 Hi Tom, We do a complete re-index(reindexcb) to the cluster on the weekend. The index corruption is characterized by incorrect result sets returned from a query. What happens is that we have a 'hot' table (one with many many transactions) that gets inserted and deleted often. About once a month now when we do a select from that table the results of the select do not match the where clause, ex. select * from recent_projects where user_id = 139 sometimes produces these results: user_id project_id 139 3 139 1 139 17 754 11 The last record does not belong in that result set. The solution to this problem has been to rebuild the indexes, which makes the query return the correct results. But given that we have customers who run Squish at 2am EST, we wouldn't be able to rebuild the indexes if they have a problem and this could result in 4-5 hours worth of down time for them, which is completely unacceptable. I've always agreed with staying current with minor releases, and forcing everyone to move along with the new minor releases, but that's just me. ###################################################### old ###################################################### > >"Mr. Dan" writes: > > Is this 2003 advice still relevant with postgresql 8.1.0? Our b-tree > > indexes corrupt pretty often on our production server running 8.1.0 and >we > > are grasping for a solution. > >Corrupt how --- what's the exact symptoms? > >The *first* bit of advice I'd give you is that you shouldn't be on 8.1.0 >anymore. We don't make update releases just for amusement. However, >whether this represents an already-fixed problem is impossible to tell >with no details. > >(As for that 2003 discussion, that predates the availability of PITR. >Now, you can use a plain tar backup ... as long as you've got WAL logs >to go with it.) > > regards, tom lane