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