Now frustration sets in :(  I just rebuilt everything, and am still 
getting the error reported in the last email ... so obviously I'm doing 
*something* wrong with my setting up ...

So, let's do this in a nice long email ...

I have one central server (db.hub.org) from which I want to replicate a 
dns_records table to multiple subscribers (at this point in time, just 2) 
... one is saturn.hub.org (a US based server), and the other is 
neptune.hub.org (a server sitting in the same machine room as db.hub.org)

Now, here are the steps I'm going through to "rebuild" ...

1. shutdown slon on all three servers (it was already down on
    saturn/neptune)

2. drop node/path for both node 2 and node 3

3. uninstall node 1 (which probably negates the need for 2, right?)


there, that should remove all traces of slony ... right?


4. drop/create database on subscriber postgresql servers

5. reload database schema on subscriber databases


at this point in time, still no slony installed/running


6. setup the master server for slony, using:

slonik <<_EOF_
     cluster name = $CLUSTER;

     node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER';

     init cluster ( id = 1, comment = 'Node 1' );

     table add key ( node id = 1, fully qualified name = 'public.dns_records' );

     create set ( id = 1, origin = 1, comment = 'Distributed DNS Database' );
     set add table ( set id = 1, origin = 1,
         id = 1, fully qualified name = 'public.dns_records',
         key = serial,
         comment = 'public.dns_records' );

_EOF_

7. setup neptune/saturn using the following (note node #s are changed
    appropriately):

saturn:

slonik <<_EOF_
     cluster name = $CLUSTER;

     node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER';
     node 2 admin conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER';

     store node ( id = 2, comment = 'Node 2' );
     store path ( server = 1, client = 2,
         conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER');
     store path ( server = 2, client = 1,
         conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER');
     store listen ( origin = 1, provider = 1, receiver = 2 );
     store listen ( origin = 2, provider = 2, receiver = 1 );

     subscribe set ( id = 1, provider = 1, receiver = 2, forward = no);
_EOF_

neptune:

slonik <<_EOF_
     cluster name = $CLUSTER;

     node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER';
     node 3 admin conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER';

     store node ( id = 3, comment = 'Node 3' );
     store path ( server = 1, client = 3,
         conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER');
     store path ( server = 3, client = 1,
         conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER');
     store listen ( origin = 1, provider = 1, receiver = 3 );
     store listen ( origin = 3, provider = 3, receiver = 1 );

     subscribe set ( id = 1, provider = 1, receiver = 3, forward = no);
_EOF_

8. restart slon on master, which appears to start up fine:

Dec  4 15:04:46 db slony_dns: CONFIG main: slon version 1.0.5 starting up
Dec  4 15:04:46 db slony_dns: CONFIG main: local node id = 1
Dec  4 15:04:46 db slony_dns: CONFIG main: loading current cluster configuration
Dec  4 15:04:46 db slony_dns: CONFIG storeNode: no_id=2 no_comment='Node 2'
Dec  4 15:04:46 db slony_dns: CONFIG storeNode: no_id=3 no_comment='Node 3'
Dec  4 15:04:46 db slony_dns: CONFIG storePath: pa_server=2 pa_client=1 pa_conninfo="dbname=ams host=saturn.hub.org user=pgsql" pa_connretry=10
Dec  4 15:04:46 db slony_dns: CONFIG storePath: pa_server=3 pa_client=1 pa_conninfo="dbname=ams host=neptune.hub.org user=pgsql" pa_connretry=10
Dec  4 15:04:46 db slony_dns: CONFIG storeListen: li_origin=2 li_receiver=1 li_provider=2
Dec  4 15:04:46 db slony_dns: CONFIG storeListen: li_origin=3 li_receiver=1 li_provider=3
Dec  4 15:04:46 db slony_dns: CONFIG storeSet: set_id=1 set_origin=1 set_comment='Distributed DNS Database'
Dec  4 15:04:46 db slony_dns: CONFIG main: configuration complete - starting threads
Dec  4 15:04:46 db slony_dns: CONFIG enableNode: no_id=2
Dec  4 15:04:46 db slony_dns: CONFIG enableNode: no_id=3


9. restart slon on neptune, and this is where it first fails:

Dec  4 15:05:51 neptune slony_dns: CONFIG main: local node id = 3
Dec  4 15:05:51 neptune slony_dns: CONFIG main: loading current cluster configuration
Dec  4 15:05:51 neptune slony_dns: CONFIG storeNode: no_id=1 no_comment='Node 1'
Dec  4 15:05:51 neptune slony_dns: CONFIG storeNode: no_id=2 no_comment='Node 2'
Dec  4 15:05:51 neptune slony_dns: CONFIG storePath: pa_server=1 pa_client=3 pa_conninfo="dbname=ams host=db.hub.org user=pgsql" pa_connretry=10
Dec  4 15:05:51 neptune slony_dns: CONFIG storeListen: li_origin=1 li_receiver=3 li_provider=1
Dec  4 15:05:52 neptune slony_dns: CONFIG storeSet: set_id=1 set_origin=1 set_comment='Distributed DNS Database'
Dec  4 15:05:52 neptune slony_dns: WARN   remoteWorker_wakeup: node 1 - no worker thread
Dec  4 15:05:52 neptune slony_dns: CONFIG storeSubscribe: sub_set=1 sub_provider=1 sub_forward='f'
Dec  4 15:05:52 neptune slony_dns: WARN   remoteWorker_wakeup: node 1 - no worker thread
Dec  4 15:05:52 neptune slony_dns: CONFIG main: configuration complete - starting threads
Dec  4 15:05:52 neptune slony_dns: CONFIG enableNode: no_id=1
Dec  4 15:05:52 neptune slony_dns: CONFIG enableNode: no_id=2
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "dns_records__Slony-I_distributed_dns_rowID_key" for table "dns_records"
CONTEXT:  PL/pgSQL function "determineattkindserial" line 52 at execute statement
Dec  4 15:05:58 neptune slony_dns: CONFIG enableSubscription: sub_set=1
Dec  4 15:05:58 neptune slony_dns: ERROR  remoteWorkerThread_1: "begin transaction; set transaction isolation level serializable; lock table "_distributed_dns".sl_config_lock; select "_distributed_dns".enableSubscription(1, 1, 2); notify "_distributed_dns_Event"; notify "_distributed_dns_Confirm"; insert into "_distributed_dns".sl_event     (ev_origin, ev_seqno, ev_timestamp,      ev_minxid, ev_maxxid, ev_xip, ev_type , ev_data1, ev_data2, ev_data3, ev_data4    ) values ('1', '13', '2004-12-04 15:04:48.854993', '84718467', '84718468', '', 'ENABLE_SUBSCRIPTION', '1', '1', '2', 'f'); insert into "_distributed_dns".sl_confirm       (con_origin, con_received, con_seqno, con_timestamp)    values (1, 3, '13', CURRENT_TIMESTAMP); commit transaction;" PGRES_FATAL_ERROR ERROR:  insert or update on table "sl_subscribe" violates foreign key constraint "sl_subscribe-sl_path-ref"
Dec  4 15:05:58 neptune slony_dns: DETAIL:  Key (sub_provider,sub_receiver)=(1,2) is not present in table "sl_path".
Dec  4 15:05:58 neptune slony_dns: INFO   remoteListenThread_1: disconnecting from 'dbname=ams host=db.hub.org user=pgsql'


My 'startup script' looks like:

============
#!/bin/sh

CLUSTER=distributed_dns

DBNAME1=ams
HOST1=db.hub.org
PASS1=vacUlefot

DBNAME2=ams
HOST2=saturn.hub.org
PASS1=vacUlefot

SLONY_USER=pgsql

/usr/local/bin/slon $CLUSTER "dbname=$DBNAME2 user=$SLONY_USER" | /usr/bin/logger -p local0.notice -t slony_dns

===========

So, I'm obviously setting up my subscribers wrong ...


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy at hub.org           Yahoo!: yscrappy              ICQ: 7615664