On 12/4/2004 2:15 PM, Marc G. Fournier wrote:

> 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 );

Here you are missing:

     store listen (origin = 3, provider = 1, receiver = 2);
     store listen (origin = 2, provider = 1, receiver = 3);

Even though the two don't seem to have much to do with each other, they 
do need to listen for each others events.

This of course requires to put "node 2 admin conninfo ..." into the 
script as well.

And you might want to add

     wait for event (origin = 2, confirmed = 3);
     wait for event (origin = 3, confirmed = 2);

here before the subscribe as well, so that the whole adding of node 3 
has completely propagated before you subscribe.


Jan

> 
>      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
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #