1SELECT * FROM pglogical_regress_variables() 2\gset 3 4\c :provider_dsn 5SELECT E'\'' || current_database() || E'\'' AS pubdb; 6\gset 7 8\c :orig_provider_dsn 9SET client_min_messages = 'warning'; 10 11GRANT ALL ON SCHEMA public TO nonsuper; 12 13SET client_min_messages = 'warning'; 14 15DO $$ 16BEGIN 17 IF (SELECT setting::integer/100 FROM pg_settings WHERE name = 'server_version_num') = 904 THEN 18 CREATE EXTENSION IF NOT EXISTS pglogical_origin; 19 END IF; 20END;$$; 21 22DO $$ 23BEGIN 24 IF version() ~ 'Postgres-XL' THEN 25 CREATE EXTENSION IF NOT EXISTS pglogical; 26 ELSE 27 CREATE EXTENSION IF NOT EXISTS pglogical VERSION '1.0.0'; 28 END IF; 29END; 30$$; 31ALTER EXTENSION pglogical UPDATE; 32 33SELECT * FROM pglogical.create_node(node_name := 'test_orig_provider', dsn := (SELECT orig_provider_dsn FROM pglogical_regress_variables()) || ' user=super'); 34 35\c :provider_dsn 36SET client_min_messages = 'warning'; 37-- test_provider pglogical node already exists here. 38 39BEGIN; 40SELECT * FROM pglogical.create_subscription( 41 subscription_name := 'test_orig_subscription', 42 provider_dsn := (SELECT orig_provider_dsn FROM pglogical_regress_variables()) || ' user=super', 43 synchronize_structure := false, 44 forward_origins := '{}'); 45COMMIT; 46 47BEGIN; 48SET LOCAL statement_timeout = '10s'; 49SELECT pglogical.wait_for_subscription_sync_complete('test_orig_subscription'); 50COMMIT; 51 52SELECT subscription_name, status, provider_node, replication_sets, forward_origins FROM pglogical.show_subscription_status(); 53 54SELECT sync_kind, sync_subid, sync_nspname, sync_relname, sync_status IN ('y', 'r') FROM pglogical.local_sync_status ORDER BY 2,3,4; 55 56-- Make sure we see the slot and active connection 57\c :orig_provider_dsn 58SELECT plugin, slot_type, active FROM pg_replication_slots; 59SELECT count(*) FROM pg_stat_replication; 60 61-- Table that replicates from top level provider to mid-level pglogical node. 62 63\c :orig_provider_dsn 64 65SELECT pglogical.replicate_ddl_command($$ 66 CREATE TABLE public.top_level_tbl ( 67 id serial primary key, 68 other integer, 69 data text, 70 something interval 71 ); 72$$); 73 74SELECT * FROM pglogical.replication_set_add_table('default', 'top_level_tbl'); 75INSERT INTO top_level_tbl(other, data, something) 76VALUES (5, 'foo', '1 minute'::interval), 77 (4, 'bar', '12 weeks'::interval), 78 (3, 'baz', '2 years 1 hour'::interval), 79 (2, 'qux', '8 months 2 days'::interval), 80 (1, NULL, NULL); 81 82SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 83 84\c :provider_dsn 85SELECT id, other, data, something FROM top_level_tbl ORDER BY id; 86 87-- Table that replicates from top level provider to mid-level pglogical node. 88SELECT pglogical.replicate_ddl_command($$ 89 CREATE TABLE public.mid_level_tbl ( 90 id serial primary key, 91 other integer, 92 data text, 93 something interval 94 ); 95$$); 96 97SELECT * FROM pglogical.replication_set_add_table('default', 'mid_level_tbl'); 98INSERT INTO mid_level_tbl(other, data, something) 99VALUES (5, 'foo', '1 minute'::interval), 100 (4, 'bar', '12 weeks'::interval), 101 (3, 'baz', '2 years 1 hour'::interval), 102 (2, 'qux', '8 months 2 days'::interval), 103 (1, NULL, NULL); 104 105SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 106 107\c :subscriber_dsn 108SELECT id, other, data, something FROM mid_level_tbl ORDER BY id; 109 110-- drop the tables 111\c :orig_provider_dsn 112\set VERBOSITY terse 113SELECT pglogical.replicate_ddl_command($$ 114 DROP TABLE public.top_level_tbl CASCADE; 115$$); 116 117\c :provider_dsn 118\set VERBOSITY terse 119SELECT pglogical.replicate_ddl_command($$ 120 DROP TABLE public.mid_level_tbl CASCADE; 121$$); 122 123\c :provider_dsn 124SELECT * FROM pglogical.drop_subscription('test_orig_subscription'); 125 126\c :orig_provider_dsn 127SELECT * FROM pglogical.drop_node(node_name := 'test_orig_provider'); 128 129SELECT plugin, slot_type, active FROM pg_replication_slots; 130SELECT count(*) FROM pg_stat_replication; 131