Chris Isaacson wrote:

> Hello all,
>  
> I've been testing the duration of slony 1.0.5's initial copy of a
> database with 59 tables.  58 of the tables have less than 10K rows
> while the other table has ~24mil rows.  In both cases the slave host
> database was an empty schema with no data.
>  
> As a baseline, I did an ordinary pg_dump from the masterhost, piping
> the result over a 1GB LAN to psql on the slavehost. 
>  
> <slavehost>$ pg_dump --host=<masterhost> --username=<xxx> <dbname> |
> psql <dbname> <username>
>  
> This dump/restore took 2h22m14s.
>  
> After deleting all the data from slave host, I installed slony 1.0.5
> on both hosts and subscribed to all the tables.  The slon log on the
> slavehost reports the initial subscribe set(s) copy for the same
> database taking 7h7m.  Any idea why slony's initial snapshot of the
> database takes 3x as long as an ordinary dump/restore?  Is there any
> way to use dump/restore and bypass slony's initial snapshot operation?
>  
> Any guidance would be greatly appreciated.

The reason for this is [sigh] well-understood, and there is a plan to
resolve it in version 1.2.

The reason for it to take longer to do COPY_SET than it takes for
dump/restore is that Slony-I leaves all of your indices in place as it
loads the data, and that ~4.5 hours is the difference in time between:

 a) Loading the data en masse, without indices, and creating the indices
"ex nihilo", versus

 b) Loading the data into the tables with indices, so the indices are
built incrementally as the data is loaded in.

I put a patch into one of the 1.1 betas that tried to drop the indices
and recreate them; it ran afoul of issues surrounding foreign keys and
other index-related constraints such that I had to drop the patch for now.

Your best answer, for now, is to drop any indices that you can on the
"slave" system while the COPY_SET event runs.