Ok, I see Tom has diagnosed your problem. Here are more hints
On Fri, Oct 27, 2006 at 10:20:25AM +0100, Gavin Hamill wrote:
> > table bloat in various slony-related tables.
> I know it takes longer, I know it blocks. It's never been a problem
The problem from a VACUUM FULL is that its taking longer causes the
vacuums on (especially) pg_listen and sl_log_[n] to be unable to
recover as many rows (because there's an older transaction around).
This is a significant area of vulnerability in Slony. You really
have to readjust your vacuum assumptions when using Slony.
> > 3. Your backups "from the slave" aren't done with pg_dump,
> > right?
> Em, they are indeed. I assumed that MVCC would ensure I got a
> consistent snapshot from the instant when pg_dump began. Am I wrong?
That's not the problem. The problem is that when you restore the
dump of the slave, you'll have garbage. Slony fools with the
catalogs on the replicas. This is documented in the Slony docs, but
probably not in sufficiently large-type bold italics in red with the
<blink> tag set as would be appropriate for such a huge gotcha.
Anyway, don't use pg_dump on a replica. There's a tool that comes
with slony that will allow you to take consistent, restorable dumps
from replicas if you like. (And you might as well throw away the
dumpfiles from the replicas that you have. They won't work when you
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
"The year's penultimate month" is not in truth a good way of saying
In response to
pgsql-performance by date
|Next:||From: Gavin Hamill||Date: 2006-10-29 15:08:26|
|Subject: Re: VACUUMs take twice as long across all nodes|
|Previous:||From: Andreas Kostyrka||Date: 2006-10-28 22:28:05|
|Subject: partitioned table performance|