On Wed, 2006-03-22 at 10:40 -0500, Vivek Khera wrote:
> On Mar 21, 2006, at 10:05 PM, Rod Taylor wrote:
> 
> > 400GB at 2B transactions/year already requires nearly double hardware
> > (CPU & RAM) to get through the initial data copy as would otherwise be
> > required for standard DB operation. Lack of VACUUM during this time  
> > is a
> > killer. Emptying sl_log_1 with everything being in a single  
> > transaction
> > at the beginning is also painful.
> 
> For something this big, I'd want to do one table in a set at a time,  
> and merge the sets as it completes each table.

That's all fine and dandy if you have less than a billion tuples per
structure. Otherwise the COPY stage still takes forever and will be
quite painful. I have several structures that are individually 100GB in
size. They take a couple of days just to rebuild the indexes after COPY
is done. PostgreSQL throws fits with transactions that large.


Anyway you still get bit when you put up the second slave. You can merge
sets, but not split them. Even if you could split them you are still
interrupting regular service on the source and other slaves to
constantly reconfigure them.

Using the first subscriber as the source to the second doesn't help very
much either since the first subscriber would bloat significantly and
require serious vacuuming (VACUUM FULL) which in turn requires greater
hardware.

Instead of the single master requiring additional hardware to power its
way through the initial copy step one of the slaves requires it to be
useful.


At some point the hardware required for initializing a new subscriber is
far more significant at the origin than what is required for standard
operation after the fact.


The best bet is for Slony to allow the use of PITR to build a second
slave (no good for mixed hardware environments or partial copies).
Between a PITR restore and the information obtained from pg_controldata,
it should be enough to allow creation of a subscriber with minimal
disruption to the origin. Probably would not take as long either.

A PITR restore for me takes between 6 to 8 hours. Setting up a new
subscriber the standard way takes between 10 to 14 days.












--