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
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
|
/* First test whether a table's replication set can be properly manipulated */
SELECT * FROM pglogical_regress_variables()
\gset
\c :provider_dsn
SELECT pglogical.replicate_ddl_command($$
CREATE SCHEMA normalschema;
CREATE SCHEMA "strange.schema-IS";
CREATE TABLE public.test_publicschema(id serial primary key, data text);
CREATE TABLE normalschema.test_normalschema(id serial primary key);
CREATE TABLE "strange.schema-IS".test_strangeschema(id serial primary key);
CREATE TABLE public.test_nopkey(id int);
CREATE UNLOGGED TABLE public.test_unlogged(id int primary key);
$$);
replicate_ddl_command
-----------------------
t
(1 row)
SELECT nspname, relname, set_name FROM pglogical.tables
WHERE relname IN ('test_publicschema', 'test_normalschema', 'test_strangeschema', 'test_nopkey') ORDER BY 1,2,3;
nspname | relname | set_name
-------------------+--------------------+----------
normalschema | test_normalschema |
public | test_nopkey |
public | test_publicschema |
strange.schema-IS | test_strangeschema |
(4 rows)
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
wait_slot_confirm_lsn
-----------------------
(1 row)
-- show initial replication sets
SELECT nspname, relname, set_name FROM pglogical.tables
WHERE relname IN ('test_publicschema', 'test_normalschema', 'test_strangeschema', 'test_nopkey') ORDER BY 1,2,3;
nspname | relname | set_name
-------------------+--------------------+----------
normalschema | test_normalschema |
public | test_nopkey |
public | test_publicschema |
strange.schema-IS | test_strangeschema |
(4 rows)
-- not existing replication set
SELECT * FROM pglogical.replication_set_add_table('nonexisting', 'test_publicschema');
ERROR: replication set nonexisting not found
-- create some replication sets
SELECT * FROM pglogical.create_replication_set('repset_replicate_all');
create_replication_set
------------------------
1767380104
(1 row)
SELECT * FROM pglogical.create_replication_set('repset_replicate_instrunc', replicate_update := false, replicate_delete := false);
create_replication_set
------------------------
348382733
(1 row)
SELECT * FROM pglogical.create_replication_set('repset_replicate_insupd', replicate_delete := false, replicate_truncate := false);
create_replication_set
------------------------
128878480
(1 row)
-- add tables
SELECT * FROM pglogical.replication_set_add_table('repset_replicate_all', 'test_publicschema');
replication_set_add_table
---------------------------
t
(1 row)
SELECT * FROM pglogical.replication_set_add_table('repset_replicate_instrunc', 'normalschema.test_normalschema');
replication_set_add_table
---------------------------
t
(1 row)
SELECT * FROM pglogical.replication_set_add_table('repset_replicate_insupd', 'normalschema.test_normalschema');
replication_set_add_table
---------------------------
t
(1 row)
SELECT * FROM pglogical.replication_set_add_table('repset_replicate_insupd', '"strange.schema-IS".test_strangeschema');
replication_set_add_table
---------------------------
t
(1 row)
-- should fail
SELECT * FROM pglogical.replication_set_add_table('repset_replicate_all', 'test_unlogged');
ERROR: UNLOGGED and TEMP tables cannot be replicated
SELECT * FROM pglogical.replication_set_add_table('repset_replicate_all', 'test_nopkey');
ERROR: table test_nopkey cannot be added to replication set repset_replicate_all
DETAIL: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs
HINT: Add a PRIMARY KEY to the table
-- success
SELECT * FROM pglogical.replication_set_add_table('repset_replicate_instrunc', 'test_nopkey');
replication_set_add_table
---------------------------
t
(1 row)
SELECT * FROM pglogical.alter_replication_set('repset_replicate_insupd', replicate_truncate := true);
alter_replication_set
-----------------------
128878480
(1 row)
-- fail again
SELECT * FROM pglogical.replication_set_add_table('repset_replicate_insupd', 'test_nopkey');
ERROR: table test_nopkey cannot be added to replication set repset_replicate_insupd
DETAIL: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs
HINT: Add a PRIMARY KEY to the table
SELECT * FROM pglogical.replication_set_add_all_tables('default', '{public}');
ERROR: table test_nopkey cannot be added to replication set default
DETAIL: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs
HINT: Add a PRIMARY KEY to the table
SELECT * FROM pglogical.alter_replication_set('repset_replicate_instrunc', replicate_update := true);
ERROR: replication set repset_replicate_instrunc cannot be altered to replicate UPDATEs or DELETEs because it contains tables without PRIMARY KEY
SELECT * FROM pglogical.alter_replication_set('repset_replicate_instrunc', replicate_delete := true);
ERROR: replication set repset_replicate_instrunc cannot be altered to replicate UPDATEs or DELETEs because it contains tables without PRIMARY KEY
-- Adding already-added fails
\set VERBOSITY terse
SELECT * FROM pglogical.replication_set_add_table('repset_replicate_all', 'public.test_publicschema');
ERROR: duplicate key value violates unique constraint "replication_set_table_pkey"
\set VERBOSITY default
-- check the replication sets
SELECT nspname, relname, set_name FROM pglogical.tables
WHERE relname IN ('test_publicschema', 'test_normalschema', 'test_strangeschema', 'test_nopkey') ORDER BY 1,2,3;
nspname | relname | set_name
-------------------+--------------------+---------------------------
normalschema | test_normalschema | repset_replicate_instrunc
normalschema | test_normalschema | repset_replicate_insupd
public | test_nopkey | repset_replicate_instrunc
public | test_publicschema | repset_replicate_all
strange.schema-IS | test_strangeschema | repset_replicate_insupd
(5 rows)
SELECT * FROM pglogical.replication_set_add_all_tables('default_insert_only', '{public}');
replication_set_add_all_tables
--------------------------------
t
(1 row)
SELECT nspname, relname, set_name FROM pglogical.tables
WHERE relname IN ('test_publicschema', 'test_normalschema', 'test_strangeschema', 'test_nopkey') ORDER BY 1,2,3;
nspname | relname | set_name
-------------------+--------------------+---------------------------
normalschema | test_normalschema | repset_replicate_instrunc
normalschema | test_normalschema | repset_replicate_insupd
public | test_nopkey | default_insert_only
public | test_nopkey | repset_replicate_instrunc
public | test_publicschema | default_insert_only
public | test_publicschema | repset_replicate_all
strange.schema-IS | test_strangeschema | repset_replicate_insupd
(7 rows)
--too short
SELECT pglogical.create_replication_set('');
ERROR: replication set name cannot be empty
-- Can't drop table while it's in a repset
DROP TABLE public.test_publicschema;
ERROR: cannot drop table test_publicschema because other objects depend on it
DETAIL: table test_publicschema membership in replication set default_insert_only depends on table test_publicschema
table test_publicschema membership in replication set repset_replicate_all depends on table test_publicschema
HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- Can't drop table while it's in a repset
BEGIN;
SELECT pglogical.replicate_ddl_command($$
DROP TABLE public.test_publicschema;
$$);
ERROR: cannot drop table public.test_publicschema because other objects depend on it
DETAIL: table public.test_publicschema membership in replication set default_insert_only depends on table public.test_publicschema
table public.test_publicschema membership in replication set repset_replicate_all depends on table public.test_publicschema
HINT: Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT: during execution of queued SQL statement:
DROP TABLE public.test_publicschema;
ROLLBACK;
-- Can CASCADE though, even outside ddlrep
BEGIN;
DROP TABLE public.test_publicschema CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table test_publicschema membership in replication set default_insert_only
drop cascades to table test_publicschema membership in replication set repset_replicate_all
ROLLBACK;
-- ... and can drop after repset removal
SELECT pglogical.replication_set_remove_table('repset_replicate_all', 'public.test_publicschema');
replication_set_remove_table
------------------------------
t
(1 row)
SELECT pglogical.replication_set_remove_table('default_insert_only', 'public.test_publicschema');
replication_set_remove_table
------------------------------
t
(1 row)
BEGIN;
DROP TABLE public.test_publicschema;
ROLLBACK;
\set VERBOSITY terse
SELECT pglogical.replicate_ddl_command($$
DROP TABLE public.test_publicschema CASCADE;
DROP SCHEMA normalschema CASCADE;
DROP SCHEMA "strange.schema-IS" CASCADE;
DROP TABLE public.test_nopkey CASCADE;
DROP TABLE public.test_unlogged CASCADE;
$$);
NOTICE: drop cascades to table normalschema.test_normalschema
NOTICE: drop cascades to 2 other objects
NOTICE: drop cascades to table "strange.schema-IS".test_strangeschema
NOTICE: drop cascades to table "strange.schema-IS".test_strangeschema membership in replication set repset_replicate_insupd
NOTICE: drop cascades to 2 other objects
replicate_ddl_command
-----------------------
t
(1 row)
\c :subscriber_dsn
SELECT * FROM pglogical.replication_set;
set_id | set_nodeid | set_name | replicate_insert | replicate_update | replicate_delete | replicate_truncate
------------+------------+---------------------+------------------+------------------+------------------+--------------------
828867312 | 1755434425 | default | t | t | t | t
3318003856 | 1755434425 | default_insert_only | t | f | f | t
2796587818 | 1755434425 | ddl_sql | t | f | f | f
(3 rows)
|
