-- -- MULTI_MOVE_MX -- ALTER SEQUENCE pg_catalog.pg_dist_shardid_seq RESTART 1550000; -- Create mx test tables SET citus.shard_count TO 4; SET citus.shard_replication_factor TO 1; CREATE TABLE mx_table_1 (a int); SELECT create_distributed_table('mx_table_1', 'a'); create_distributed_table --------------------------------------------------------------------- (1 row) CREATE TABLE mx_table_2 (a int); SELECT create_distributed_table('mx_table_2', 'a'); create_distributed_table --------------------------------------------------------------------- (1 row) CREATE TABLE mx_table_3 (a text); SELECT create_distributed_table('mx_table_3', 'a'); create_distributed_table --------------------------------------------------------------------- (1 row) -- Check that the first two tables are colocated SELECT logicalrelid, repmodel FROM pg_dist_partition WHERE logicalrelid = 'mx_table_1'::regclass OR logicalrelid = 'mx_table_2'::regclass OR logicalrelid = 'mx_table_3'::regclass ORDER BY logicalrelid; logicalrelid | repmodel --------------------------------------------------------------------- mx_table_1 | s mx_table_2 | s mx_table_3 | s (3 rows) -- Check the list of shards SELECT logicalrelid, shardid, nodename, nodeport FROM pg_dist_shard NATURAL JOIN pg_dist_shard_placement WHERE logicalrelid = 'mx_table_1'::regclass OR logicalrelid = 'mx_table_2'::regclass OR logicalrelid = 'mx_table_3'::regclass ORDER BY logicalrelid, shardid; logicalrelid | shardid | nodename | nodeport --------------------------------------------------------------------- mx_table_1 | 1550000 | localhost | 57637 mx_table_1 | 1550001 | localhost | 57638 mx_table_1 | 1550002 | localhost | 57637 mx_table_1 | 1550003 | localhost | 57638 mx_table_2 | 1550004 | localhost | 57637 mx_table_2 | 1550005 | localhost | 57638 mx_table_2 | 1550006 | localhost | 57637 mx_table_2 | 1550007 | localhost | 57638 mx_table_3 | 1550008 | localhost | 57637 mx_table_3 | 1550009 | localhost | 57638 mx_table_3 | 1550010 | localhost | 57637 mx_table_3 | 1550011 | localhost | 57638 (12 rows) -- Check the data on the worker \c - - - :worker_2_port SELECT logicalrelid, shardid, nodename, nodeport FROM pg_dist_shard NATURAL JOIN pg_dist_shard_placement WHERE logicalrelid = 'mx_table_1'::regclass OR logicalrelid = 'mx_table_2'::regclass OR logicalrelid = 'mx_table_3'::regclass ORDER BY logicalrelid, shardid; logicalrelid | shardid | nodename | nodeport --------------------------------------------------------------------- mx_table_1 | 1550000 | localhost | 57637 mx_table_1 | 1550001 | localhost | 57638 mx_table_1 | 1550002 | localhost | 57637 mx_table_1 | 1550003 | localhost | 57638 mx_table_2 | 1550004 | localhost | 57637 mx_table_2 | 1550005 | localhost | 57638 mx_table_2 | 1550006 | localhost | 57637 mx_table_2 | 1550007 | localhost | 57638 mx_table_3 | 1550008 | localhost | 57637 mx_table_3 | 1550009 | localhost | 57638 mx_table_3 | 1550010 | localhost | 57637 mx_table_3 | 1550011 | localhost | 57638 (12 rows) \c - - - :master_port -- Check that citus_copy_shard_placement cannot be run with MX tables SELECT citus_copy_shard_placement(shardid, 'localhost', :worker_1_port, 'localhost', :worker_2_port, 'force_logical') FROM pg_dist_shard NATURAL JOIN pg_dist_shard_placement WHERE logicalrelid = 'mx_table_1'::regclass AND nodeport = :worker_1_port ORDER BY shardid LIMIT 1; ERROR: Table 'mx_table_1' is streaming replicated. Shards of streaming replicated tables cannot be copied -- Move a shard from worker 1 to worker 2 SELECT master_move_shard_placement(shardid, 'localhost', :worker_1_port, 'localhost', :worker_2_port, 'force_logical') FROM pg_dist_shard NATURAL JOIN pg_dist_shard_placement WHERE logicalrelid = 'mx_table_1'::regclass AND nodeport = :worker_1_port ORDER BY shardid LIMIT 1; master_move_shard_placement --------------------------------------------------------------------- (1 row) -- Test changing citus.node_conninfo on the target node affects the -- CREATE SUBSCRIPTION command for shard move \c - - - :worker_2_port ALTER SYSTEM SET citus.node_conninfo TO 'sslrootcert=/non/existing/certificate.crt sslmode=verify-full'; SELECT pg_reload_conf(); pg_reload_conf --------------------------------------------------------------------- t (1 row) \c - - - :worker_2_port -- before reseting citus.node_conninfo, check that CREATE SUBSCRIPTION -- with citus_use_authinfo takes into account node_conninfo even when -- one of host, port, or user parameters are not specified. -- -- We need to specify host and port to not get an hba error, so we test -- only with ommitting user. CREATE SUBSCRIPTION subs_01 CONNECTION 'host=''localhost'' port=57637' PUBLICATION pub_01 WITH (citus_use_authinfo=true); ERROR: could not connect to the publisher: root certificate file "/non/existing/certificate.crt" does not exist Either provide the file, use the system's trusted roots with sslrootcert=system, or change sslmode to disable server certificate verification. ALTER SYSTEM RESET citus.node_conninfo; SELECT pg_reload_conf(); pg_reload_conf --------------------------------------------------------------------- t (1 row) \c - - - :master_port -- Check that the shard and its colocated shard is moved, but not the other shards SELECT logicalrelid, shardid, nodename, nodeport FROM pg_dist_shard NATURAL JOIN pg_dist_shard_placement WHERE (logicalrelid = 'mx_table_1'::regclass OR logicalrelid = 'mx_table_2'::regclass OR logicalrelid = 'mx_table_3'::regclass) AND shardstate != 4 ORDER BY logicalrelid, shardid; logicalrelid | shardid | nodename | nodeport --------------------------------------------------------------------- mx_table_1 | 1550000 | localhost | 57638 mx_table_1 | 1550001 | localhost | 57638 mx_table_1 | 1550002 | localhost | 57637 mx_table_1 | 1550003 | localhost | 57638 mx_table_2 | 1550004 | localhost | 57638 mx_table_2 | 1550005 | localhost | 57638 mx_table_2 | 1550006 | localhost | 57637 mx_table_2 | 1550007 | localhost | 57638 mx_table_3 | 1550008 | localhost | 57637 mx_table_3 | 1550009 | localhost | 57638 mx_table_3 | 1550010 | localhost | 57637 mx_table_3 | 1550011 | localhost | 57638 (12 rows) -- Check that the changes are made in the worker as well \c - - - :worker_2_port SELECT logicalrelid, shardid, nodename, nodeport FROM pg_dist_shard NATURAL JOIN pg_dist_shard_placement WHERE logicalrelid = 'mx_table_1'::regclass OR logicalrelid = 'mx_table_2'::regclass OR logicalrelid = 'mx_table_3'::regclass ORDER BY logicalrelid, shardid; logicalrelid | shardid | nodename | nodeport --------------------------------------------------------------------- mx_table_1 | 1550000 | localhost | 57638 mx_table_1 | 1550001 | localhost | 57638 mx_table_1 | 1550002 | localhost | 57637 mx_table_1 | 1550003 | localhost | 57638 mx_table_2 | 1550004 | localhost | 57638 mx_table_2 | 1550005 | localhost | 57638 mx_table_2 | 1550006 | localhost | 57637 mx_table_2 | 1550007 | localhost | 57638 mx_table_3 | 1550008 | localhost | 57637 mx_table_3 | 1550009 | localhost | 57638 mx_table_3 | 1550010 | localhost | 57637 mx_table_3 | 1550011 | localhost | 57638 (12 rows) -- Check that the UDFs cannot be called from the workers SELECT citus_copy_shard_placement(shardid, 'localhost', :worker_2_port, 'localhost', :worker_1_port, 'force_logical') FROM pg_dist_shard NATURAL JOIN pg_dist_shard_placement WHERE logicalrelid = 'mx_table_1'::regclass AND nodeport = :worker_2_port ORDER BY shardid LIMIT 1 OFFSET 1; ERROR: operation is not allowed on this node HINT: Connect to the coordinator and run it again. SELECT master_move_shard_placement(shardid, 'localhost', :worker_2_port, 'localhost', :worker_1_port, 'force_logical') FROM pg_dist_shard NATURAL JOIN pg_dist_shard_placement WHERE logicalrelid = 'mx_table_1'::regclass AND nodeport = :worker_2_port ORDER BY shardid LIMIT 1 OFFSET 1; ERROR: operation is not allowed on this node HINT: Connect to the coordinator and run it again. -- Check that shards of a table with GENERATED columns can be moved. \c - - - :master_port SET citus.shard_count TO 4; SET citus.shard_replication_factor TO 1; CREATE TABLE mx_table_with_generated_column (a int, b int GENERATED ALWAYS AS ( a + 3 ) STORED, c int); SELECT create_distributed_table('mx_table_with_generated_column', 'a'); create_distributed_table --------------------------------------------------------------------- (1 row) -- Check that dropped columns are handled properly in a move. ALTER TABLE mx_table_with_generated_column DROP COLUMN c; -- Move a shard from worker 1 to worker 2 SELECT citus_move_shard_placement(shardid, 'localhost', :worker_1_port, 'localhost', :worker_2_port, 'force_logical') FROM pg_dist_shard NATURAL JOIN pg_dist_shard_placement WHERE logicalrelid = 'mx_table_with_generated_column'::regclass AND nodeport = :worker_1_port ORDER BY shardid LIMIT 1; citus_move_shard_placement --------------------------------------------------------------------- (1 row) -- Cleanup \c - - - :master_port SET client_min_messages TO WARNING; CALL citus_cleanup_orphaned_resources(); DROP TABLE mx_table_with_generated_column; DROP TABLE mx_table_1; DROP TABLE mx_table_2; DROP TABLE mx_table_3;