Hi,

I have to set up a replication database from a large production
database on a new server, using Slony.

As the tables I have to replicate have several million rows, I tried to
dump the entire database from the master and restore it as a slave
database before setting up Slony (in a developpement environnement to
test it first). Unfortunately, even if my two databases are equal, it
seems that Slony still execute a "copy" on the replicated tables.

In pg_stat_activity of the master database, I can see something like :

datid | datname | procpid | usesysid | usename |
current_query
--------+----------------+---------+----------+----------+------------------+
366347 | db_master | 11659 | 10 | postgres | copy
"public"."mytable" ("id","field2","field3") to stdout;


Is it a good idea to dump the master database and restore it as a slave
database before setting up Slony ? Should it prevent Slony from
replicating the whole data the first time ? And, If so, then why is
Slony doing a "copy" in my case ?

Thanks for your help,
Regards,

Nico

Nicolas.PAYART@gmail.com writes:
> I have to set up a replication database from a large production
> database on a new server, using Slony.
>
> As the tables I have to replicate have several million rows, I tried to
> dump the entire database from the master and restore it as a slave
> database before setting up Slony (in a developpement environnement to
> test it first). Unfortunately, even if my two databases are equal, it
> seems that Slony still execute a "copy" on the replicated tables.
>
> In pg_stat_activity of the master database, I can see something like :
>
> datid | datname | procpid | usesysid | usename |
> current_query
> --------+----------------+---------+----------+----------+------------------+
> 366347 | db_master | 11659 | 10 | postgres | copy
> "public"."mytable" ("id","field2","field3") to stdout;
>
>
> Is it a good idea to dump the master database and restore it as a slave
> database before setting up Slony ?

It is a fine idea to dump the schema. Dumping all the data is pretty
futile.

> Should it prevent Slony from replicating the whole data the first
> time ?

No.

> And, If so, then why is Slony doing a "copy" in my case ?

How can Slony-I be certain that the data matches if it does not copy
it over?

That you claim it to match does not mean it necessarily does.

Slony-I *guarantees* that the data will be the same because it copies
it into place itself.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/slony.html
"Linux! Guerrilla Unix Development Venimus, Vidimus, Dolavimus."
-- <mah@ka4ybr.com> Mark A. Horton KA4YBR