\c - - - :master_port -- do some setup SELECT 1 FROM master_add_node('localhost', :worker_1_port); ?column? --------------------------------------------------------------------- 1 (1 row) SELECT 1 FROM master_add_node('localhost', :worker_2_port); ?column? --------------------------------------------------------------------- 1 (1 row) CREATE TABLE the_table (a int, b int); SELECT create_distributed_table('the_table', 'a'); create_distributed_table --------------------------------------------------------------------- (1 row) INSERT INTO the_table (a, b) VALUES (1, 1); INSERT INTO the_table (a, b) VALUES (1, 2); CREATE TABLE stock ( s_w_id int NOT NULL, s_i_id int NOT NULL, s_order_cnt int NOT NULL ); SELECT create_distributed_table('stock','s_w_id'); create_distributed_table --------------------------------------------------------------------- (1 row) INSERT INTO stock SELECT c, c, c FROM generate_series(1, 5) as c; SET citus.enable_repartition_joins TO ON; SELECT count(*) FROM the_table t1 JOIN the_table t2 USING(b); count --------------------------------------------------------------------- 2 (1 row) SET citus.enable_single_hash_repartition_joins TO ON; SELECT count(*) FROM the_table t1 , the_table t2 WHERE t1.a = t2.b; count --------------------------------------------------------------------- 2 (1 row) RESET citus.enable_repartition_joins; -- connect to the follower and check that a simple select query works, the follower -- is still in the default cluster and will send queries to the primary nodes \c - - - :follower_master_port SELECT * FROM the_table; a | b --------------------------------------------------------------------- 1 | 1 1 | 2 (2 rows) select s_i_id, sum(s_order_cnt) as ordercount from stock group by s_i_id having sum(s_order_cnt) > (select max(s_order_cnt) - 3 as having_query from stock) order by s_i_id; s_i_id | ordercount --------------------------------------------------------------------- 3 | 3 4 | 4 5 | 5 (3 rows) -- now, connect to the follower but tell it to use secondary nodes. There are no -- secondary nodes so this should fail. -- (this is :follower_master_port but substitution doesn't work here) \c "port=9070 dbname=regression options='-c\ citus.use_secondary_nodes=always'" SELECT * FROM the_table; ERROR: node group does not have a secondary node -- add the secondary nodes and try again, the SELECT statement should work this time \c -reuse-previous=off regression - - :master_port SELECT 1 FROM master_add_node('localhost', :follower_worker_1_port, groupid => (SELECT groupid FROM pg_dist_node WHERE nodeport = :worker_1_port), noderole => 'secondary'); ?column? --------------------------------------------------------------------- 1 (1 row) SELECT 1 FROM master_add_node('localhost', :follower_worker_2_port, groupid => (SELECT groupid FROM pg_dist_node WHERE nodeport = :worker_2_port), noderole => 'secondary'); ?column? --------------------------------------------------------------------- 1 (1 row) \c "port=9070 dbname=regression options='-c\ citus.use_secondary_nodes=always'" -- now that we've added secondaries this should work SELECT * FROM the_table; a | b --------------------------------------------------------------------- 1 | 1 1 | 2 (2 rows) select s_i_id, sum(s_order_cnt) as ordercount from stock group by s_i_id having sum(s_order_cnt) > (select max(s_order_cnt) - 3 as having_query from stock) order by s_i_id; s_i_id | ordercount --------------------------------------------------------------------- 3 | 3 4 | 4 5 | 5 (3 rows) SET citus.enable_repartition_joins TO ON; SELECT count(*) FROM the_table t1 JOIN the_table t2 USING(b); count --------------------------------------------------------------------- 2 (1 row) SET citus.enable_single_hash_repartition_joins TO ON; SELECT count(*) FROM the_table t1 , the_table t2 WHERE t1.a = t2.b; count --------------------------------------------------------------------- 2 (1 row) SELECT node_name, node_port FROM master_get_active_worker_nodes() ORDER BY node_name, node_port; node_name | node_port --------------------------------------------------------------------- localhost | 9071 localhost | 9072 (2 rows) -- basic helper utilities should work fine SELECT citus_is_coordinator(); citus_is_coordinator --------------------------------------------------------------------- t (1 row) SELECT count(*) FROM citus_lock_waits; count --------------------------------------------------------------------- 0 (1 row) SELECT count(*) FROM citus_dist_stat_activity WHERE global_pid = citus_backend_gpid(); count --------------------------------------------------------------------- 1 (1 row) -- okay, now let's play with nodecluster. If we change the cluster of our follower node -- queries should stat failing again, since there are no worker nodes in the new cluster \c "port=9070 dbname=regression options='-c\ citus.use_secondary_nodes=always\ -c\ citus.cluster_name=second-cluster'" -- there are no secondary nodes in this cluster, so this should fail! SELECT * FROM the_table; ERROR: there is a shard placement in node group but there are no nodes in that group select s_i_id, sum(s_order_cnt) as ordercount from stock group by s_i_id having sum(s_order_cnt) > (select max(s_order_cnt) - 3 as having_query from stock) order by s_i_id; ERROR: there is a shard placement in node group but there are no nodes in that group -- now move the secondary nodes into the new cluster and see that the follower, finally -- correctly configured, can run select queries involving them \c -reuse-previous=off regression - - :master_port UPDATE pg_dist_node SET nodecluster = 'second-cluster' WHERE noderole = 'secondary'; \c "port=9070 dbname=regression options='-c\ citus.use_secondary_nodes=always\ -c\ citus.cluster_name=second-cluster'" SELECT * FROM the_table; a | b --------------------------------------------------------------------- 1 | 1 1 | 2 (2 rows) -- Check for connectivity in the cluster SELECT * FROM citus_check_cluster_node_health(); from_nodename | from_nodeport | to_nodename | to_nodeport | result --------------------------------------------------------------------- localhost | 9071 | localhost | 9071 | t localhost | 9071 | localhost | 9072 | t localhost | 9072 | localhost | 9071 | t localhost | 9072 | localhost | 9072 | t (4 rows) -- clean up after ourselves \c -reuse-previous=off regression - - :master_port DROP TABLE the_table; DROP TABLE stock;