Csaba,

Thanks, very helpful. I have removed the foreign keys from my replica
schema.

All - as regards the UTF-8 problem, I am replicating from a DB which is
SQL-ASCII to a db which is UTF-8. I want to do this, while my initial
purpose is backup it will become, in due course, an upgrade, and I want
the upgraded version to be unicode. Is this possible - are there any
configuration options I need to set in postgres / slony to enable it -
it seems at the moment that I may not be able to do an upgrade to
PostgreSQL 8.1 via Slony - I may be forced down the dump/restore route
in order to correct the encoding in the dump file?

Thanks,

Steve


Csaba Nagy wrote:
> 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.
>
>
>   

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://gborg.postgresql.org/pipermail/slony1-general/attachments/20060801/f92238c1/attachment.html