Guest User

Slony with PostgreSQL Partitions

a guest
May 27th, 2011
849
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. slony details
  2. --------------
  3. cluster name:mmcluster
  4.  
  5. Hosts:
  6. 1. 192.168.75.10 - Global HQ
  7. 2. 192.168.45.10 - US Master
  8. 3. 192.168.55.10 - EU Master
  9. 4. 192.168.15.10 - AU Master
  10.  
  11.  
  12. Tables in Replication:
  13. 1. sales_us
  14. 2. sales_eu
  15. 3. sales_au
  16.  
  17.  
  18. /opt/dbserver/slony206/bin/slonik <<_EOF_
  19. cluster name = mmcluster;
  20. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  21. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  22. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  23. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  24.  
  25. init cluster(id=1, comment = 'Master Node');
  26. _EOF_
  27.  
  28. /opt/dbserver/slony206/bin/slonik <<_EOF_
  29. cluster name = mmcluster;
  30. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  31. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  32. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  33. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  34.  
  35. store node (id=2,COMMENT = 'Node US', EVENT NODE = 1 );
  36. _EOF_
  37.  
  38. /opt/dbserver/slony206/bin/slonik <<_EOF_
  39. cluster name = mmcluster;
  40. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  41. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  42. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  43. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  44.  
  45. store node (id=3,COMMENT = 'Node EU', EVENT NODE = 1 );
  46. _EOF_
  47.  
  48. /opt/dbserver/slony206/bin/slonik <<_EOF_
  49. cluster name = mmcluster;
  50. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  51. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  52. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  53. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  54.  
  55. store node (id=4,COMMENT = 'Node AU', EVENT NODE = 1 );
  56. _EOF_
  57.  
  58.  
  59. /opt/dbserver/slony206/bin/slonik <<_EOF_
  60. cluster name = mmcluster;
  61. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  62. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  63. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  64. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  65.  
  66. store path (server=1, client=2, conninfo='dbname=mm_demo host=192.168.75.10 port=5432 user=postgres');
  67. store path (server=2, client=1, conninfo='dbname=mm_demo host=192.168.45.10 port=5432 user=postgres');
  68.  
  69. store path (server=1, client=3, conninfo='dbname=mm_demo host=192.168.75.10 port=5432 user=postgres');
  70. store path (server=3, client=1, conninfo='dbname=mm_demo host=192.168.55.10 port=5432 user=postgres');
  71.  
  72. store path (server=1, client=4, conninfo='dbname=mm_demo host=192.168.75.10 port=5432 user=postgres');
  73. store path (server=4, client=1, conninfo='dbname=mm_demo host=192.168.15.10 port=5432 user=postgres');
  74. _EOF_
  75.  
  76.  
  77. start slon process on all nodes
  78. --------------------------------
  79. /opt/dbserver/slony206/bin/slon mmcluster 'dbname=mm_demo port=5432 user=postgres'
  80.  
  81.  
  82. /opt/dbserver/slony206/bin/slonik <<_EOF_
  83. cluster name = mmcluster;
  84. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  85. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  86. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  87. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  88.  
  89. create set (id=1, origin=2, comment='sales_data_us_set');
  90. set add table (set id=1, origin=2, id=7, fully qualified name = 'public.sales_us', comment='sales_us_table');
  91. _EOF_
  92.  
  93.  
  94. /opt/dbserver/slony206/bin/slonik <<_EOF_
  95. cluster name = mmcluster;
  96. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  97. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  98. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  99. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  100.  
  101. create set (id=2, origin=3, comment='sales_data_eu_set');
  102. set add table (set id=2, origin=3, id=8, fully qualified name = 'public.sales_eu', comment='sales_eu_table');
  103. _EOF_
  104.  
  105. /opt/dbserver/slony206/bin/slonik <<_EOF_
  106. cluster name = mmcluster;
  107. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  108. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  109. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  110. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  111.  
  112. create set (id=3, origin=4, comment='sales_data_au_set');
  113. set add table (set id=3, origin=4, id=9, fully qualified name = 'public.sales_au', comment='sales_au_table');
  114. _EOF_
  115.  
  116.  
  117.  
  118. /opt/dbserver/slony206/bin/slonik <<_EOF_
  119. cluster name = mmcluster;
  120. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  121. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  122. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  123. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  124.  
  125. subscribe set (id=1, provider=2,receiver=1,forward=no);
  126. _EOF_
  127.  
  128. /opt/dbserver/slony206/bin/slonik <<_EOF_
  129. cluster name = mmcluster;
  130. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  131. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  132. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  133. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  134.  
  135. subscribe set (id=2, provider=3,receiver=1,forward=no);
  136. _EOF_
  137.  
  138. /opt/dbserver/slony206/bin/slonik <<_EOF_
  139. cluster name = mmcluster;
  140. node 1 admin conninfo = 'dbname=mm_demo host=192.168.75.10 port=5432 user=postgres';
  141. node 2 admin conninfo = 'dbname=mm_demo host=192.168.45.10 port=5432 user=postgres';
  142. node 3 admin conninfo = 'dbname=mm_demo host=192.168.55.10 port=5432 user=postgres';
  143. node 4 admin conninfo = 'dbname=mm_demo host=192.168.15.10 port=5432 user=postgres';
  144.  
  145. subscribe set (id=3, provider=4,receiver=1,forward=no);
  146. _EOF_
RAW Paste Data