Re: On-line backup

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(at)hotmail(dot)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