slony add tables to existing set

Steps to add new tables to slony replication

In order to add a new table to existing set in slony, we need to create a new set with the table and merge it with the existing set.

  • Before adding the table to slony, verify below things
    • Table exists on the target node.
    • Table has a primary key. Slony doesn’t work without a primary key.
    • It is better to truncate the table on the target node before adding it to slony replication.
  • Verify existing slony configuration. We need to capture below details.
    • Cluster name
    • Source/target node connection info
    • Existing Set ids, Table ids

Cluster name is same as slony schema name without the underscore at the beginning, it is ‘testslony’ in our case.

Source and target node details from below query

testslon=# select * from _testslony.sl_path;
 pa_server | pa_client | pa_conninfo | pa_connretry
-----------+-----------+------------------------------------------------------------------------+--------------
 2 | 1 | host=db02.lax.qa2 dbname=testslon user=postgres port=5432 | 10
 1 | 2 | host=db01.lax.qa dbname=testslon user=postgres port=5432 | 10
(2 rows)

Table and set details from below query

testslon=# select * from _testslony.sl_table ;
 tab_id | tab_reloid | tab_relname | tab_nspname | tab_set | tab_idxname | tab_altered | tab_comment
--------+------------+----------------+-------------+---------+---------------------+-------------+----------------------------------------------
 1 | 8596498 | tab1 | public | 1 | tab1_pkey | t | Table public.tab1 with primary key
 2 | 8596500 | tab2 | public | 1 | tab2_pkey | t | Table public.tab2 with primary key
 3 | 8603350 | tab3 | public | 1 | tab3_pkey | t | Table public.tab3 with primary key
 5 | 8611554 | h_queue | public | 1 | h_queue_pkey | t | Table public.h_queue with primary key
 6 | 8622655 | fs_keystore | test_data | 1 | fs_keystore_pkey | t | Table test_data.fs_keystore with primary key
 7 | 13064971 | price_increase | public | 1 | price_increase_pkey | t | Table public.price_increase
(6 rows)
  • Configure a new set using below slony script. We are creating a new set with id 2 and adding a new table(testslon) to it.
# File : slon_add_table.slonik
cluster name=testslony;
node 1 admin conninfo='host=db01.lax.qa dbname=testslon user=postgres port=5432';
node 2 admin conninfo='host=db02.lax.qa2 dbname=testslon user=postgres port=5432';
create set (id=2, origin=1, comment='Set 2');
set add table (id=8, set id=2, origin=1, fully qualified name='public.testslon', comment='Table public.testslon');
subscribe set(id=3, provider=1,receiver=2);

Save the above script to slon_add_table.slonik file and run it.

slonik < slon_add_table.slonik

Verify that slony log shows below output on target

DEBUG2 remoteWorkerThread_1: syncing set 2 with 1 table(s) from provider 1
DEBUG2 remoteWorkerThread_1: syncing set 1 with 6 table(s) from provider 1

Now the table is added to slony replication, we can merge it to the existing set using below slony script

# slon_merge_set.slonik
cluster name=testslony;
node 1 admin conninfo='host=db01.lax.qa dbname=testslon user=postgres port=5432';
node 2 admin conninfo='host=db02.lax.qa2 dbname=testslon user=postgres port=5432';
merge set(id=1, add id=2,origin=1);

Save the above script to slon_merge_set.slonik file and run it.

slonik < slon_merge_set.slonik

Verify the slony configs

 

testslon=# select * from _testslony.sl_set;
 set_id | set_origin | set_locked | set_comment
--------+------------+------------+------------------------
 1 | 1 | | Set 1
(1 row)

testslon=# select * from _testslon_ben.sl_table;
 tab_id | tab_reloid | tab_relname | tab_nspname | tab_set | tab_idxname | tab_altered | tab_comment
--------+------------+----------------+-------------+---------+---------------------+-------------+----------------------------------------------
 1 | 8596498 | tab1 | public | 1 | tab1_pkey | t | Table public.tab1 with primary key
 2 | 8596500 | tab2 | public | 1 | tab2_pkey | t | Table public.tab2 with primary key
 3 | 8603350 | tab3 | public | 1 | tab3_pkey | t | Table public.tab3 with primary key
 5 | 8611554 | h_queue | public | 1 | h_queue_pkey | t | Table public.h_queue with primary key
 6 | 8622655 | fs_keystore | test_data | 1 | fs_keystore_pkey | t | Table test_data.fs_keystore with primary key
 7 | 13064971 | price_increase | public | 1 | price_increase_pkey | t | Table public.price_increase
 8 | 13064979 | testslon | public | 1 | testslon_pkey | t | Table public.testslon
(7 rows)

One thought to “slony add tables to existing set”

Leave a Reply

Your email address will not be published. Required fields are marked *