> > If you have more than about 500GB of data on a heavily used OLTP
> > database, I strongly advise posting a detailed plan of attack or hiring
> > a consultant. There are lots of gotchas at that level of the game just
> > due to the impact of the time involved.
> 
> Some of the frontline consultants are also in this mess...

I've replicated a moderately sized DB (around the 400GB mark last time
not including indexes) through a few DB upgrades using Slony with the
same physical machine as the source and destination.

It's not difficult but it does require quite a bit of thought on how to
keep the Slony transactions as short as possible AND keep the number of
sets required as low as possible.

My main issue was actually scheduling regular system vacuums on DB hot
spots to run between Slony transactions to ensure that tables that bloat
quickly didn't grow more than 100 times their normal size. COPY and
building the PRIMARY KEY for the large tables was the difficulty.

This required all kinds of lead in work that included changing primary
keys to a SERIAL instead of several varchar's and that type of thing.
Having a parallel index builder within PostgreSQL would pretty much
eliminate that type of problem.

> > 4) Stampeding Slony. If for any reason Slony is unable to complete the
> > replication work available within the timeframe allotted (5 minutes I
> > think), Slony will abandon the first connection and establish a new one
> > to retry.
> 
> Exactly what "replication work" do you mean?  One table? All tables being copied?
> In my situation I have 6500*5 + 100 tables to copy.  No way is that going to be
> completed in 5 minutes no matter that the tables are small.  (And no
> I did not design the schema :)

The standard sync process that happens in the background on a day to day
basis. I don't know if it is because pg_listener passes a threshold and
locks get in the way or if sl_event or sl_log_1 grow too large, but at
some point it can take Slony significant amounts of time to replicate a
'sync' event.

The queries that scoop data from sl_log_1 start to take a long time and
can pass the timeout. After that it seems to run away with itself.

I haven't sent in a bug report yet because I really have no idea why it
happens. I do know that it happens and has been an issue since 1.0.2 on
several DBs I run. It's always with a slon daemon syncing up to another
DB.

Node 4 subscribes to Node 1, but Node 1 will sometimes get hundreds of
connections to Node 4 all listening for NOTIFY events. Obviously it
cannot recover from this (pg_listener activity is too significant).

I've upgraded almost everything to 8.1 and have been limiting slony
connections.

> > The problem is the database doesn't necessarily get rid of the old
> > connection. I've seen Slony with several hundred connections to the
> > database from a single backend.
> > 
> > With PostgreSQL 8.1, apply a CONNECTION LIMIT to the slony user to
> > ensure it doesn't eat up all of the DB connections preventing real work
> > from happening.
> > 
> > I believe that fixing pg_listener issues (Slony 1.2 when released) may
> > solve this problem as well.
> > 
> > 5) Large DBs will require some patches not in Slony 1.1.5 but they are
> > in CVS, otherwise they will simply never create an up to date node.
> > Probably not an issues unless you have > 100GB of data to replicate.
> > Maximum group size is also something to look at here.
> 
> Are you saying slony won't handle databases of >100GB?  Or tables? 
> If the database is larger than that, exactly what patches should be added 
> for exactly what result?  For the most part I am doing production work and never apply 
> patches not in the main release for obvious reasons.  If there are crucial 
> ones, though, I need more details.

Yes. It can be very difficult to get through the initial replication and
catchup period without using a large group size. Slony 1.1.5 is
restricted out of the box (coding error -- not by intent).

I believe 1.0.6 through 1.1.2 were okay though.


--