I'm not an expert on slony, but I'll comment on the experience I just
had replicating an ~ 100G data base.

On Tue, 2006-08-01 at 11:05, Steve Burrows wrote:
> I have a 300GB db of 13 tables (7 small, 6 large) which I am trying to
> replicate for backup purposes. I have installed Slony 1.1.5 on my master
> (PostgreSQL 7.4.4) and slave (8.1.3).

You can expect the copy process to copy one table at a time, and you'll
see the growth of that the best by using something like "du -sh
/data/base/dir" if you're on linux. The slony logs will also show you
what is currently going on, but that will be lost in between the syncs,
so you might "grep -v SYNC" the log to see a better picture of the
process. If you enable command strings then you will also be able to see
what command is currently executing on the DB.

One thing to look for is if the subscription's log shows something else
than "NOTICE:  truncate of "public"."sometable" succeeded", which can
mean that you have foreign keys pointing to that table, in which case
the table cannot be truncated. Unfortunately the current stable version
of slony will not drop/recreate the indexes in this case, which makes
the COPY a very slow operation. So make sure you don't have foreign keys
on the slave, or use the latest release candidate of slony which I think
fixes this problem.

If the indexes are properly dropped, then I think the COPY will be as
fast as a copy of the files would be between the machines, with the same
bottlenecks (disk speed, network speed), so you can estimate that.

Be aware that if you have indexes, those won't be copied, only the
tables themselves. The indexes will be recreated on the slave (except
the case mentioned above where they stay there during the whole process,
that's to be avoided).

> I have set everything up - all looks good to me, I have a subscribe set
> etc., started my slons on both sides, I get appropriate looking debug
> messages etc. But no data appears in my slave.
> I can see a postgres COPY process on the master being executed against
> the ip address of the slave, so something is happening. Should I expect
> to see my initial slave data population to be gradual - e.g. a bundle of
> records at a time, or a table at a time, or the whole database in one go?
> 

The data will first appear when the copy + recreate indexes finishes. Or
it might be that the whole subscribe process must finish, I'm not sure,
the subscription takes some locks on the tables but I didn't check how
long and what kind of... for me the best way to observe the process was
anyway to observe the file system growth to give you feedback...

> If the whole db, any guesses as to how long my 300GB might take ;)
> 
For me, subscribing the 100G data base takes around 5 hours, but that
depends a lot on your hardware/network. If you have fast network and
fast disks, I guess it will be fast... and my data ended up being 50G on
the slave, so the bloat from the master is not transferred, which is a
good thing ;-)

> Question numero duo:
> 
> After approx 16 hours of running this appeared in my slave log last night:
> 
> 2006-08-01 01:26:11 BST DEBUG1 cleanupThread:    0.001 seconds for
> cleanupEvent()
> 2006-08-01 01:26:11 BST DEBUG1 cleanupThread:    0.016 seconds for
> delete logs
> 2006-08-01 01:37:26 BST ERROR  remoteWorkerThread_1: copy from stdin on
> local node - PGRES_FATAL_ERROR ERROR:  invalid UTF-8 byte sequence
> detected near byte 0x92
> CONTEXT:  COPY messageblks, line 13, column messageblk: "This is a
> multi-part message in MIME format.

This is probably due to the fact you're master is some pre 8.1 version
and your slave is 8.1 ... right ? Postgres 8.1 is stricter regarding
malformed UTF-8 codes than the previous versions. You will need to fix
your data on the master first I guess... or search the archives on
postgres-general mailing list for the same error, there were some posts
about this.

Cheers,
Csaba.