From: Mr. Dan <bitsandbytes88@hotmail.com>
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
Message-ID: <BAY116-F2283D23093F2AC9D65336AD1620@phx.gbl> (raw)
In-Reply-To: <4451.1153158045@sss.pgh.pa.us>
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" <bitsandbytes88@hotmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: bitsandbytes88@hotmail.com, tgl@sss.pgh.pa.us
Subject: Re: On-line backup
In-Reply-To: <BAY116-F2283D23093F2AC9D65336AD1620@phx.gbl>
References: <4451.1153158045@sss.pgh.pa.us> <BAY116-F2283D23093F2AC9D65336AD1620@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox