1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
SELECT * FROM pglogical_regress_variables()
\gset
\c :subscriber_dsn
GRANT ALL ON SCHEMA public TO nonsuper;
SELECT E'\'' || current_database() || E'\'' AS subdb;
subdb
------------
'postgres'
(1 row)
\gset
\c :provider1_dsn
SET client_min_messages = 'warning';
GRANT ALL ON SCHEMA public TO nonsuper;
SET client_min_messages = 'warning';
DO $$
BEGIN
IF (SELECT setting::integer/100 FROM pg_settings WHERE name = 'server_version_num') = 904 THEN
CREATE EXTENSION IF NOT EXISTS pglogical_origin;
END IF;
END;$$;
CREATE EXTENSION IF NOT EXISTS pglogical;
SELECT * FROM pglogical.create_node(node_name := 'test_provider1', dsn := (SELECT provider1_dsn FROM pglogical_regress_variables()) || ' user=super');
create_node
-------------
866557357
(1 row)
\c :provider_dsn
-- add these entries to provider
SELECT pglogical.replicate_ddl_command($$
CREATE TABLE public.multi_ups_tbl(id integer primary key, key text unique not null, data text);
$$);
replicate_ddl_command
-----------------------
t
(1 row)
INSERT INTO multi_ups_tbl VALUES(1, 'key1', 'data1');
INSERT INTO multi_ups_tbl VALUES(2, 'key2', 'data2');
INSERT INTO multi_ups_tbl VALUES(3, 'key3', 'data3');
SELECT * FROM pglogical.replication_set_add_table('default', 'multi_ups_tbl', true);
replication_set_add_table
---------------------------
t
(1 row)
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
wait_slot_confirm_lsn
-----------------------
(1 row)
\c :provider1_dsn
-- add these entries to provider1
CREATE TABLE multi_ups_tbl(id integer primary key, key text unique not null, data text);
INSERT INTO multi_ups_tbl VALUES(4, 'key4', 'data4');
INSERT INTO multi_ups_tbl VALUES(5, 'key5', 'data5');
INSERT INTO multi_ups_tbl VALUES(6, 'key6', 'data6');
SELECT * FROM pglogical.replication_set_add_table('default', 'multi_ups_tbl');
replication_set_add_table
---------------------------
t
(1 row)
\c :subscriber_dsn
-- We'll use the already existing pglogical node
-- notice synchronize_structure as false when table definition already exists
SELECT * FROM pglogical.create_subscription(
subscription_name := 'test_subscription1',
provider_dsn := (SELECT provider1_dsn FROM pglogical_regress_variables()) || ' user=super',
synchronize_structure := false,
forward_origins := '{}');
create_subscription
---------------------
3102546391
(1 row)
BEGIN;
SET LOCAL statement_timeout = '180s';
SELECT pglogical.wait_for_subscription_sync_complete('test_subscription1');
wait_for_subscription_sync_complete
-------------------------------------
(1 row)
COMMIT;
SELECT subscription_name, status, provider_node, replication_sets, forward_origins FROM pglogical.show_subscription_status();
subscription_name | status | provider_node | replication_sets | forward_origins
--------------------+-------------+----------------+---------------------------------------+-----------------
test_subscription | replicating | test_provider | {default,default_insert_only,ddl_sql} |
test_subscription1 | replicating | test_provider1 | {default,default_insert_only,ddl_sql} |
(2 rows)
SELECT sync_kind, sync_subid, sync_nspname, sync_relname, sync_status IN ('y', 'r') FROM pglogical.local_sync_status ORDER BY 2,3,4;
sync_kind | sync_subid | sync_nspname | sync_relname | ?column?
-----------+------------+--------------+---------------+----------
f | 3102546391 | public | multi_ups_tbl | t
d | 3102546391 | | | t
d | 3848008564 | public | multi_ups_tbl | t
f | 3848008564 | | | t
(4 rows)
SELECT * from multi_ups_tbl ORDER BY id;
id | key | data
----+------+-------
1 | key1 | data1
2 | key2 | data2
3 | key3 | data3
4 | key4 | data4
5 | key5 | data5
6 | key6 | data6
(6 rows)
-- Make sure we see the slot and active connection
\c :provider1_dsn
SELECT plugin, slot_type, active FROM pg_replication_slots;
plugin | slot_type | active
------------------+-----------+--------
pglogical_output | logical | t
pglogical_output | logical | t
(2 rows)
SELECT count(*) FROM pg_stat_replication;
count
-------
2
(1 row)
-- cleanup
\c :provider_dsn
\set VERBOSITY terse
SELECT pglogical.replicate_ddl_command($$
DROP TABLE public.multi_ups_tbl CASCADE;
$$);
NOTICE: drop cascades to table public.multi_ups_tbl membership in replication set default
replicate_ddl_command
-----------------------
t
(1 row)
\c :provider1_dsn
SELECT * FROM pglogical.drop_node(node_name := 'test_provider1');
ERROR: cannot drop node "test_provider1" because one or more replication slots for the node are still active
\set VERBOSITY terse
DROP TABLE public.multi_ups_tbl CASCADE;
NOTICE: drop cascades to table multi_ups_tbl membership in replication set default
\c :subscriber_dsn
SELECT * FROM pglogical.drop_subscription('test_subscription1');
drop_subscription
-------------------
1
(1 row)
\c :provider1_dsn
SELECT * FROM pglogical.drop_node(node_name := 'test_provider1');
drop_node
-----------
t
(1 row)
SELECT plugin, slot_type, active FROM pg_replication_slots;
plugin | slot_type | active
------------------+-----------+--------
pglogical_output | logical | t
(1 row)
SELECT count(*) FROM pg_stat_replication;
count
-------
1
(1 row)
|
