Hi,
I'm currently trying to solve an issue i have on a slony based replication platform (slony 1.94):

There's a master postgresql database server (on which master nodes are running) and a slave postgresql database server ( on which slave nodes are running).

On the master server I have the following databases :

DB1, DB3, DB5, DB7 ....

On the slave server I have the following database :

DB2, DB4,DB6, DB8 ....

Basically, the following replication is expected :

DB1 -> DB2
DB3 -> DB4
DB5 -> DB6
Etc ....

However I have noticed I have many processes which are not expected to be here. One of these databases is called "ferrandi" and has the same name on the slave database server.

select distinct
datname,
application_name,
client_addr,
substring(query from 1 for 30) query_text,
count(1) over (partition by datname) cnt_total,
count(1) over (partition by datname,application_name,client_addr,substring(query from 1 for 30) ) cnt
from
pg_stat_activity
order by cnt_total, datname, application_name;

   datname   |      application_name      |  client_addr  |           query_text           | cnt_total | cnt
-------------+----------------------------+---------------+--------------------------------+-----------+-----
ferrandi    | slon.local_cleanup         | 172.16.173.35 | select nspname, relname from " |       200 |   1
ferrandi    | slon.local_listen          | 172.16.173.35 | rollback transaction;          |       200 |   1
ferrandi    | slon.local_monitor         | 172.16.173.35 | commit;                        |       200 |   1
ferrandi    | slon.local_sync            | 172.16.173.35 | rollback transaction;          |       200 |   1
ferrandi    | slon.node_9_listen         | 172.16.173.35 | select con_origin, con_receive |       200 |  13
ferrandi    | slon.origin_10_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_11_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_12_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_13_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_14_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_1_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_2_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_3_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_4_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_5_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_6_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_7_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_8_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_9_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_9_provider_9   | 172.16.173.35 | select SL.seql_seqid, max(SL.s |       200 |   1
ferrandi    | slon.remoteWorkerThread_1  | 172.16.173.35 | insert into "_replication_ccir |       200 |   1
ferrandi    | slon.remoteWorkerThread_10 | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_11 | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_12 | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_13 | 172.16.173.35 | insert into "_replication_ccir |       200 |   1
ferrandi    | slon.remoteWorkerThread_14 | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_15 | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_16 | 172.16.173.35 | select "_replication_ccir".log |       200 |   1
ferrandi    | slon.remoteWorkerThread_17 | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_18 | 172.16.173.35 | select "_replication_ccir".log |       200 |   1
ferrandi    | slon.remoteWorkerThread_19 | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_2  | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_20 | 172.16.173.35 | select "_replication_ccir".log |       200 |   1
ferrandi    | slon.remoteWorkerThread_3  | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_4  | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_5  | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_6  | 172.16.173.35 | insert into "_replication_ccir |       200 |   1
ferrandi    | slon.remoteWorkerThread_7  | 172.16.173.35 | select "_replication_ccir".for |       200 |   1
ferrandi    | slon.remoteWorkerThread_8  | 172.16.173.35 | insert into "_replication_ccir |       200 |   1

The problem I have is with these multiple processes (basically 168 idel processes):

ferrandi    | slon.node_9_listen         | 172.16.173.35 | select con_origin, con_receive |       200 |  13
ferrandi    | slon.origin_10_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_11_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_12_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_13_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_14_provider_9  | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_1_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_2_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_3_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_4_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_5_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_6_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_7_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  11
ferrandi    | slon.origin_8_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_9_provider_9   | 172.16.173.35 | rollback transaction; set enab |       200 |  12
ferrandi    | slon.origin_9_provider_9   | 172.16.173.35 | select SL.seql_seqid, max(SL.s |       200 |   1

ferrandi database is node 9 while the slave is node 10. So there should be no relation between node 9 and nodes 1,2,3,4,5,6,7,8,11,12...20 which are other databases. Only node 10 should be working with node 9.

Checking the configuration in the slony schema, I can see that whatever the master database is, the configuration table contain all the nodes, while I guess only nodes 9 and 10 should be referenced in this specific replication which only involves node 9 and node 10.

Could someone confirm that is node 9 and 10 are only supposed to work together (with no other database involved) then I should not have all the nodes declared in the configuration ? If so then I guess people who have done this have applied a single same configuration file to all the master databases, and this may explain why I have all these processes.

Thanks and Best Regards,
Olivier


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20170327/62f6ea1b/attachment.htm