Skip to content

Commit 509d79d

Browse files
PJMODOSPJMODOS
committed
Don't failback to 'default' repset when table is without repsets
This means that by default no tables are replicated until they are configured to be replicated. This might seem like inconvenience but it's smaller problem than deciding what will and what won't be replicated behind users back. And since we can't replicate UPDATEs and DELETEs for tables without PRIMARY KEY, that's exactly what we've been doing up until now. Add pglogical.replication_set_add_all_tables(set_name, schema_names) interface for easier adding of multiple tables to the replication set. Add another precreated replication set called default_insert_only which only replicates inserts and truncates. Update all the tests and README accordingly.
1 parent 912e1cf commit 509d79d

17 files changed

+666
-147
lines changed

‎README.md

Lines changed: 41 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -81,21 +81,29 @@ Now create the provider node:
8181
dsn := 'host=providerhost port=5432 dbname=db'
8282
);
8383

84-
Optionally you can also create replication sets and add tables to them (see
85-
[Replication sets](#replication-sets)). It's usually better to create replication sets beforehand.
84+
Add all tables in `public` schema to the `default` replication set.
8685

87-
Once the provider node is setup, subscribers can be subscribed to it:
86+
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
87+
88+
Optionally you can also create additional replication sets and add tables to
89+
them (see [Replication sets](#replication-sets)). It's usually better to create
90+
replication sets beforehand.
91+
92+
Once the provider node is setup, subscribers can be subscribed to it. First the
93+
subscriber node must be created:
8894

8995
SELECT pglogical.create_node(
9096
node_name := 'subscriber1',
9197
dsn := 'host=thishost port=5432 dbname=db'
9298
);
9399

100+
And finally on the subscriber node you can create the subscription which will
101+
start synchronization and replication process in the background:
102+
94103
SELECT pglogical.create_subscription(
95104
subscription_name := 'subscription1',
96105
provider_dsn := 'host=providerhost port=5432 dbname=db'
97106
);
98-
(run this on the subscriber node)
99107

100108
### Node management
101109

@@ -223,11 +231,14 @@ Each replicated set can specify individually if `INSERTs`, `UPDATEs`,
223231
`DELETEs` and `TRUNCATEs` on the set are replicated. Every table can be in
224232
multiple replication sets and every subscriber can subscribe to multiple
225233
replication sets as well. The resulting set of tables and actions replicated
226-
is the union of the sets the table is in.
234+
is the union of the sets the table is in. The tables are not replicated until
235+
they are added into a replication set.
227236

228-
There are two preexisting replication sets named "all" and "default". The "all"
229-
replication set contains every user table in the database and every table that
230-
has not been added to specific replication set will be in the "default" set.
237+
There are two preexisting replication sets named "default" and
238+
"default_insert_only". The "default" replication set is defined to replicate
239+
all changes to tables in in. The "default_insert_only" only replicates INSERTs
240+
and is meant for tables that don't have primary key (see
241+
[Limitations](#primary-key-or-replica-identity-required) section for details).
231242

232243
The following functions are provided for managing the replication sets:
233244

@@ -266,6 +277,19 @@ The following functions are provided for managing the replication sets:
266277
- `synchronize` - if true, the table data is synchronized on all subscribers
267278
which are subscribed to given replication set, default false
268279

280+
- `pglogical.replication_set_add_all_tables(set_name name, schema_names text[], synchronize boolean)`
281+
Adds all tables in given schemas. Only existing tables are added, table that
282+
will be created in future will not be added automatically. For how to ensure
283+
that tables created in future are added to correct replication set, see
284+
[Automatic assignment of replication sets for new tables](#automatic-assignment-of-replication-sets-for-new-tables).
285+
286+
Parameters:
287+
- `set_name` - name of the existing replication set
288+
- `schema_names` - array of names name of existing schemas from which tables
289+
should be added
290+
- `synchronize` - if true, the table data is synchronized on all subscribers
291+
which are subscribed to given replication set, default false
292+
269293
- `pglogical.replication_set_remove_table(set_name name, table_name regclass)`
270294
Remove a table from replication set.
271295

@@ -289,8 +313,12 @@ Example:
289313
BEGIN
290314
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
291315
LOOP
292-
IF obj.object_type = 'table' AND obj.schema_name = 'config' THEN
293-
PERFORM pglogical.replication_set_add_table('configuration', obj.objid);
316+
IF obj.object_type = 'table' THEN
317+
IF obj.schema_name = 'config' THEN
318+
PERFORM pglogical.replication_set_add_table('configuration', obj.objid);
319+
ELSIF NOT obj.in_extension THEN
320+
PERFORM pglogical.replication_set_add_table('default', obj.objid);
321+
END IF;
294322
END IF;
295323
END LOOP;
296324
END;
@@ -302,7 +330,8 @@ Example:
302330
EXECUTE PROCEDURE pglogical_assign_repset();
303331

304332
The above example will put all new tables created in schema `config` into
305-
replication set `configuration`.
333+
replication set `configuration` and all other new tables which are not created
334+
by extensions will go to `default` replication set.
306335

307336
## Conflicts
308337

@@ -424,7 +453,7 @@ To replicate multiple databases you must set up individual provider/subscriber
424453
relationships for each. There is no way to configure replication for all databases
425454
in a PostgreSQL install at once.
426455

427-
#### `PRIMARY KEY` or `REPLICA IDENTITY` required
456+
### PRIMARY KEY or REPLICA IDENTITY required
428457

429458
`UPDATE`s and `DELETE`s cannot be replicated for tables that lack a `PRIMARY
430459
KEY` or other valid replica identity such as a `UNIQUE` constraint. Replication

‎expected/basic.out

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,12 @@ $$);
1313
t
1414
(1 row)
1515

16+
SELECT * FROM pglogical.replication_set_add_table('default', 'basic_dml');
17+
replication_set_add_table
18+
---------------------------
19+
t
20+
(1 row)
21+
1622
SELECT pg_xlog_wait_remote_apply(pg_current_xlog_location(), 0);
1723
pg_xlog_wait_remote_apply
1824
---------------------------
@@ -178,8 +184,13 @@ SELECT id, other, data, something FROM basic_dml ORDER BY id;
178184

179185
\c regression
180186
SELECT pglogical.replicate_ddl_command($$
181-
DROP TABLE public.basic_dml;
187+
DROP TABLE public.basic_dml CASCADE;
182188
$$);
189+
NOTICE: drop cascades to 1 other object
190+
DETAIL: table public.basic_dml in replication set default
191+
CONTEXT: during execution of queued SQL statement:
192+
DROP TABLE public.basic_dml CASCADE;
193+
183194
replicate_ddl_command
184195
-----------------------
185196
t

‎expected/bidirectional.out

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -33,13 +33,25 @@ $$);
3333
t
3434
(1 row)
3535

36+
SELECT * FROM pglogical.replication_set_add_table('default', 'basic_dml');
37+
replication_set_add_table
38+
---------------------------
39+
t
40+
(1 row)
41+
3642
SELECT pg_xlog_wait_remote_apply(pg_current_xlog_location(), 0);
3743
pg_xlog_wait_remote_apply
3844
---------------------------
3945

4046
(1 row)
4147

4248
\c regression
49+
SELECT * FROM pglogical.replication_set_add_table('default', 'basic_dml');
50+
replication_set_add_table
51+
---------------------------
52+
t
53+
(1 row)
54+
4355
-- check basic insert replication
4456
INSERT INTO basic_dml(other, data, something)
4557
VALUES (5, 'foo', '1 minute'::interval),
@@ -85,8 +97,13 @@ SELECT id, other, data, something FROM basic_dml ORDER BY id;
8597

8698
\c regression
8799
SELECT pglogical.replicate_ddl_command($$
88-
DROP TABLE public.basic_dml;
100+
DROP TABLE public.basic_dml CASCADE;
89101
$$);
102+
NOTICE: drop cascades to 1 other object
103+
DETAIL: table public.basic_dml in replication set default
104+
CONTEXT: during execution of queued SQL statement:
105+
DROP TABLE public.basic_dml CASCADE;
106+
90107
replicate_ddl_command
91108
-----------------------
92109
t

0 commit comments

Comments
 (0)