Hi,

I've three nodes configured using Slony 2.2.0B/PG 9.3beta with One master
two slaves and one table.


        Node1 (Master - 5555)
                      |
        --------------------------------
        |                          |
Slave1-5556          Slave2- 5557

As per doc, RESUBSCRIBE NODE its says we could change the provider. So,
planned to move Provider to Slave1-5556 (node 2).

"The RESUBSCRIBE NODE command will change the provider used to receive data
from for all of the replication sets originating on a given node. If a
subscriber node is receiving a number of replication sets with a particular
node as an origin then the RESUBSCRIBE NODE command will change the
provider node used by that subscriber node for all of the replication sets
originating on the given origin."

Hence, I have created a script to perform RESUBSCRIBE NODE:

Resub.sh script:

cluster name = rep220;
node 1 admin conninfo='host=localhost dbname=postgres user=postgres
port=5555';
node 2 admin conninfo='host=localhost dbname=postgres user=postgres
port=5556';
node 3 admin conninfo='host=localhost dbname=postgres user=postgres
port=5557';
resubscribe node ( origin = 1, provider = 2 , receiver = 3);

While executing it gives me error as:

-bash-4.1$ slonik resub_set.sh
resub_set.sh:8: PGRES_FATAL_ERROR lock table "_rep220".sl_event_lock,
"_rep220".sl_config_lock;select "_rep220".resubscribeNode(1, 2, 2);  -
ERROR:  insert or update on table "sl_subscribe" violates foreign key
constraint "sl_subscribe-sl_path-ref"
DETAIL:  Key (sub_provider, sub_receiver)=(2, 2) is not present in table
"sl_path".
CONTEXT:  SQL statement "update "_rep220".sl_subscribe
                        set sub_provider = p_sub_provider,
                                sub_forward = p_sub_forward
                        where sub_set = p_sub_set
                        and sub_receiver = p_sub_receiver"
PL/pgSQL function
_rep220.subscribeset_int(integer,integer,integer,boolean,boolean) line 35
at SQL statement
SQL statement "SELECT "_rep220".subscribeSet_int(v_record.sub_set,
                                p_provider,
                                p_receiver, v_record.sub_forward, false)"
PL/pgSQL function _rep220.resubscribenode(integer,integer,integer) line 75
at PERFORM
-bash-4.1$

Here's sl_path output from three nodes:

sl_path ouput from 5555 port

postgres=# select * from _rep220.sl_path ;
 pa_server | pa_client |                      pa_conninfo
    | pa_connretry
-----------+-----------+--------------------------------------------------------+--------------
         2 |         1 | host=localhost dbname=postgres user=postgres
port=5556 |           10
         1 |         2 | host=localhost dbname=postgres user=postgres
port=5555 |           10
         3 |         1 | host=localhost dbname=postgres user=postgres
port=5557 |           10
         1 |         3 | host=localhost dbname=postgres user=postgres
port=5555 |           10
(4 rows)

sl_path ouput from 5556 port

-bash-4.1$ psql -p 5556
psql (9.3beta1)
Type "help" for help.

postgres=# select * from _rep220.sl_path ;
 pa_server | pa_client |                      pa_conninfo
    | pa_connretry
-----------+-----------+--------------------------------------------------------+--------------
         1 |         2 | host=localhost dbname=postgres user=postgres
port=5555 |           10
         2 |         1 | host=localhost dbname=postgres user=postgres
port=5556 |           10
         3 |         1 | host=localhost dbname=postgres user=postgres
port=5557 |           10
         1 |         3 | host=localhost dbname=postgres user=postgres
port=5555 |           10
(4 rows)

sl_path ouput from 5557 port

-bash-4.1$ psql -p 5557
psql (9.3beta1)
Type "help" for help.

postgres=# select * from _rep220.sl_path ;
 pa_server | pa_client |                      pa_conninfo
    | pa_connretry
-----------+-----------+--------------------------------------------------------+--------------
         2 |         1 | host=localhost dbname=postgres user=postgres
port=5556 |           10
         1 |         2 | host=localhost dbname=postgres user=postgres
port=5555 |           10
         1 |         3 | host=localhost dbname=postgres user=postgres
port=5555 |           10
         3 |         1 | host=localhost dbname=postgres user=postgres
port=5557 |           10
(4 rows)

I may be doing something surely wrong here, would you please indicate me
what mix-up am doing here.

Thanks in advance.

-- 
Regards
Raghav
Blog: htt://raghavt.blogspot.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20130704/aec9f605/attachment.htm