Remove replicated tables and sequences from replication for only one slave-node.
Par david techer, samedi 18 février 2012 à 23:14 :: Réplication PostgreSQL :: #880 :: rss
A couple days ago I asked on Slony mailing-list how to do this. Christopher Browne replied to my mail.
Here is a resume of the required steps to do
- Create a empty set
- Subscribe this set to all required slave-nodes
- move all objects (tables and sequences) to this set using
set move table(id = ??, new set = ??)andset move sequence(id =??, new set =??) - Unsubscribe the set to the required slave-node using
unsubscribe(id = ??, receiver = ??)
1. Scenario
Slony - Replication
enterprisedb@monroe:~/slony_on_models$ edb-psql models
Welcome to edb-psql 8.3.0.112, the EnterpriseDB interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with edb-psql commands
\g or terminate with semicolon to execute query
\q to quit
models=# select * from _replication.sl_node;
no_id | no_active | no_comment | no_spool
-------+-----------+--------------------+----------
1 | t | master 192.168.2.6 | f
2 | t | mercury-xen05 | f
3 | t | mercury-xen06 | f
4 | t | mercury-xen07 | f
(4 lignes)
models=# select * from _replication.sl_set;
set_id | set_origin | set_locked | set_comment
--------+------------+------------+-----------------------
1 | 1 | | set for Denise Milani
2 | 1 | | set for Jana Defi
(2 lignes)
models=# select * from _replication.sl_subscribe;
sub_set | sub_provider | sub_receiver | sub_forward | sub_active
---------+--------------+--------------+-------------+------------
1 | 1 | 2 | t | t
2 | 1 | 2 | t | t
1 | 1 | 3 | t | t
2 | 1 | 3 | t | t
1 | 1 | 4 | t | t
2 | 1 | 4 | t | t
models=# select * from _replication.sl_table;
tab_id | tab_reloid | tab_relname | tab_nspname | tab_set | tab_idxname | tab_altered | tab_comment
--------+------------+------------------+---------------+---------+-----------------------+-------------+------------------------
171 | 23162 | action1 | denise_milani | 1 | action1_pkey | t | Table action1
174 | 23172 | action2 | denise_milani | 1 | action2_pkey | t | Table action2
194 | 23182 | angel | denise_milani | 1 | angel_pkey | t | Table angel
199 | 23192 | atv | denise_milani | 1 | atv_pkey | t | Table atv
202 | 23212 | balcony | denise_milani | 1 | balcony_pkey | t | Table balcony
203 | 23202 | balcony1 | denise_milani | 1 | balcony1_pkey | t | Table balcony1
208 | 23222 | basement | denise_milani | 1 | basement_pkey | t | Table basement
211 | 23232 | bathroom | denise_milani | 1 | bathroom_pkey | t | Table bathroom
214 | 23242 | bb_white_sweater | denise_milani | 1 | bb_white_sweater_pkey | t | Table bb_white_sweater
217 | 23252 | beach_silver | denise_milani | 1 | beach_silver_pkey | t | Table beach_silver
220 | 23262 | beach_zebra | denise_milani | 1 | beach_zebra_pkey | t | Table beach_zebra
223 | 23272 | bear_creek | denise_milani | 1 | bear_creek_pkey | t | Table bear_creek
226 | 23292 | big_bear_blue | denise_milani | 1 | big_bear_blue_pkey | t | Table big_bear_blue
229 | 23282 | bigbeach | denise_milani | 1 | bigbeach_pkey | t | Table bigbeach
232 | 23302 | bike | denise_milani | 1 | bike_pkey | t | Table bike
31 | 177363 | Cheerleader | jana_defi | 2 | Cheerleader_pkey | t | Table Cheerleader
34 | 177473 | Construction | jana_defi | 2 | Construction_pkey | t | Table Construction
37 | 177543 | Corsage | jana_defi | 2 | Corsage_pkey | t | Table Corsage
40 | 177533 | Curtain | jana_defi | 2 | Curtain_pkey | t | Table Curtain
43 | 177523 | Dark_forest | jana_defi | 2 | Dark_forest_pkey | t | Table Dark_forest
46 | 177583 | Daylight | jana_defi | 2 | Daylight_pkey | t | Table Daylight
49 | 177203 | Diva | jana_defi | 2 | Diva_pkey | t | Table Diva
52 | 177663 | Garden | jana_defi | 2 | Garden_pkey | t | Table Garden
55 | 177443 | Hacienda_I | jana_defi | 2 | Hacienda_I_pkey | t | Table Hacienda_I
56 | 177393 | Hacienda_II | jana_defi | 2 | Hacienda_II_pkey | t | Table Hacienda_II
61 | 177413 | Hot_hovel | jana_defi | 2 | Hot_hovel_pkey | t | Table Hot_hovel
64 | 177253 | Jeans | jana_defi | 2 | Jeans_pkey | t | Table Jeans
67 | 177513 | Lakeside | jana_defi | 2 | Lakeside_pkey | t | Table Lakeside
70 | 177683 | Limelight | jana_defi | 2 | Limelight_pkey | t | Table Limelight
73 | 177223 | Meadow | jana_defi | 2 | Meadow_pkey | t | Table Meadow
76 | 177593 | Mirror_Play | jana_defi | 2 | Mirror_Play_pkey | t | Table Mirror_Play
79 | 177623 | Mirrors | jana_defi | 2 | Mirrors_pkey | t | Table Mirrors
(32 lignes)
models=# select * from _replication.sl_sequence;
seq_id | seq_reloid | seq_relname | seq_nspname | seq_set | seq_comment
--------+------------+-------------------------+---------------+---------+----------------------------------
172 | 23167 | action1_id_seq | denise_milani | 1 | Sequence action1_id_seq
175 | 23177 | action2_id_seq | denise_milani | 1 | Sequence action2_id_seq
195 | 23187 | angel_id_seq | denise_milani | 1 | Sequence angel_id_seq
200 | 23197 | atv_id_seq | denise_milani | 1 | Sequence atv_id_seq
204 | 23207 | balcony1_id_seq | denise_milani | 1 | Sequence balcony1_id_seq
206 | 23217 | balcony_id_seq | denise_milani | 1 | Sequence balcony_id_seq
209 | 23227 | basement_id_seq | denise_milani | 1 | Sequence basement_id_seq
212 | 23237 | bathroom_id_seq | denise_milani | 1 | Sequence bathroom_id_seq
215 | 23247 | bb_white_sweater_id_seq | denise_milani | 1 | Sequence bb_white_sweater_id_seq
218 | 23257 | beach_silver_id_seq | denise_milani | 1 | Sequence beach_silver_id_seq
221 | 23267 | beach_zebra_id_seq | denise_milani | 1 | Sequence beach_zebra_id_seq
224 | 23277 | bear_creek_id_seq | denise_milani | 1 | Sequence bear_creek_id_seq
227 | 23297 | big_bear_blue_id_seq | denise_milani | 1 | Sequence big_bear_blue_id_seq
230 | 23287 | bigbeach_id_seq | denise_milani | 1 | Sequence bigbeach_id_seq
233 | 23307 | bike_id_seq | denise_milani | 1 | Sequence bike_id_seq
32 | 177361 | Cheerleader_id_seq | jana_defi | 2 | Sequence Cheerleader_id_seq
35 | 177471 | Construction_id_seq | jana_defi | 2 | Sequence Construction_id_seq
38 | 177541 | Corsage_id_seq | jana_defi | 2 | Sequence Corsage_id_seq
41 | 177531 | Curtain_id_seq | jana_defi | 2 | Sequence Curtain_id_seq
44 | 177521 | Dark_forest_id_seq | jana_defi | 2 | Sequence Dark_forest_id_seq
47 | 177581 | Daylight_id_seq | jana_defi | 2 | Sequence Daylight_id_seq
50 | 177201 | Diva_id_seq | jana_defi | 2 | Sequence Diva_id_seq
53 | 177661 | Garden_id_seq | jana_defi | 2 | Sequence Garden_id_seq
57 | 177391 | Hacienda_II_id_seq | jana_defi | 2 | Sequence Hacienda_II_id_seq
59 | 177441 | Hacienda_I_id_seq | jana_defi | 2 | Sequence Hacienda_I_id_seq
62 | 177411 | Hot_hovel_id_seq | jana_defi | 2 | Sequence Hot_hovel_id_seq
65 | 177251 | Jeans_id_seq | jana_defi | 2 | Sequence Jeans_id_seq
68 | 177511 | Lakeside_id_seq | jana_defi | 2 | Sequence Lakeside_id_seq
71 | 177681 | Limelight_id_seq | jana_defi | 2 | Sequence Limelight_id_seq
74 | 177221 | Meadow_id_seq | jana_defi | 2 | Sequence Meadow_id_seq
77 | 177591 | Mirror_Play_id_seq | jana_defi | 2 | Sequence Mirror_Play_id_seq
80 | 177621 | Mirrors_id_seq | jana_defi | 2 | Sequence Mirrors_id_seq
(32 lignes)
2. Example remove "Hacienda" objects from replication on node 3
/opt/PostgresPlus/8.3R2AS/dbserver/bin/slonik <<_EOF_ # PREAMBLE ########### include <preamble.sk>; # CREATE SET 3 ########### echo 'Create Set 3 for Hacienda'; create set (id = 3, origin = 1, comment = 'Hacienda objects'); # SUBSCRIBE SET 3 ON ALL SLAVE-NODES ############################## echo 'Subscribing set 3 on node mercury-xen05'; subscribe set (id=3, provider=1, receiver=2, forward = yes); sync(id=1); wait for event(origin=1,confirmed = all, wait on = 1, timeout=0); echo 'Subscribing set 3 on node mercury-xen06'; subscribe set (id=3, provider=1, receiver=3, forward = yes); sync(id=1); wait for event(origin=1,confirmed = all, wait on = 1, timeout=0); echo 'Subscribing set 3 on node mercury-xen07'; subscribe set (id=3, provider=1, receiver=4, forward = yes); sync(id=1); wait for event(origin=1,confirmed = all, wait on = 1, timeout=0); # MOVE HACIENDA_* OBJECTS TO SET 3 ########################### echo 'Move Hacienda I objects to set 3'; set move table (origin = 1, id = 55 ,new set = 3); set move sequence (origin = 1, id = 59 ,new set = 3); echo 'Move Hacienda II objects to set 3'; set move table (origin = 1, id = 56 ,new set = 3); set move sequence (origin = 1, id = 57 ,new set = 3); # UNSUBSCRIBING SET 3 ON NODE 3 ################################ echo 'Unsubscribing set 3 on node 3'; unsubscribe set ( id = 3, receiver = 3); sync(id=1); wait for event(origin=1,confirmed = all, wait on = 1, timeout=0); _EOF_
Commentaires
Aucun commentaire pour le moment.
Ajouter un commentaire
Les commentaires pour ce billet sont fermés.