Re: [OT] Slony (initial) Replication - Slow

ajs(at)crankycanuck(dot)ca (Andrew Sullivan) writes:
> On Thu, Jan 03, 2008 at 11:15:23AM +0800, Ow Mun Heng wrote:
>> I'm just wetting my hands with slony and during the setup of the slave,
>> I did and dump and restore of the master DB to the Slave DB.
>
> Nope, you don't need to do that. You need a copy of the _schema_ on the
> target machine. But slony will remove all the contents and build the
> replica anew.

Right. The argument for doing so is that this approach (TRUNCATE +
COPY on the subscriber) is the only way that Slony-I can be certain
that it has all data on the subscriber that was on the provider.

That way, it doesn't need to trust any dodgy claims that "oh, I copied
all the data - honest!"

>> can someone confirm this? It _is_ taking long time (for slony) to do the
>> \copy (~60GB in multiple tables being replicated, including (on the fly)
>> index creation)
>
> It takes approximately the same time as it would to do a psql -h
> [remotehost] -f dumpfile.sql restore (i.e. copying the entire data
> contents across the network).

In 1.2.x, it should be a little bit quicker than the "pg_dump | psql"
approach as all index generation takes place together for each table.

When you do a restore of a pg_dump, the indexes are generated in a
somewhat arbitrary order, where there may be a separation in time
between when different indexes on a given table get created.

In contrast, Slony-I regenerates all the indexes on a given table in a
"one swell foop" fashion, which might be expected to allow cacheing to
provide a bit better performance than you could get with "pg_dump |
psql".
--
"cbbrowne","@","cbbrowne.com"
http://linuxdatabases.info/info/emacs.html
Microsoft Outlook: Deploying Viruses Has Never Been This Easy!