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
|
SELECT * FROM pglogical_regress_variables()
\gset
\c :provider1_dsn
SET client_min_messages = 'warning';
GRANT ALL ON SCHEMA public TO nonsuper;
CREATE OR REPLACE FUNCTION public.pg_xlog_wait_remote_apply(i_pos pg_lsn, i_pid integer) RETURNS VOID
AS $FUNC$
BEGIN
WHILE EXISTS(SELECT true FROM pg_stat_get_wal_senders() s WHERE s.replay_location < i_pos AND (i_pid = 0 OR s.pid = i_pid)) LOOP
PERFORM pg_sleep(0.01);
END LOOP;
END;$FUNC$ LANGUAGE plpgsql;
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 VERSION '2.0.0';
ALTER EXTENSION pglogical UPDATE;
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
BEGIN;
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)
/*
* Remove the function we added in preseed because otherwise the restore of
* schema will fail. We do this in same transaction as create_subscription()
* because the subscription process will only start on commit.
*/
DROP FUNCTION IF EXISTS public.pglogical_regress_variables();
COMMIT;
DO $$
BEGIN
FOR i IN 1..100 LOOP
IF EXISTS (SELECT 1 FROM pglogical.show_subscription_status() WHERE status = 'replicating' and subscription_name = 'test_subscription1') THEN
RETURN;
END IF;
PERFORM pg_sleep(0.1);
END LOOP;
END;
$$;
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_insert_only,ddl_sql,repset_test,default} |
test_subscription1 | replicating | test_provider1 | {default,default_insert_only,ddl_sql} |
(2 rows)
DO $$
BEGIN
FOR i IN 1..300 LOOP
IF EXISTS (SELECT 1 FROM pglogical.local_sync_status WHERE sync_status = 'r') THEN
EXIT;
END IF;
PERFORM pg_sleep(0.1);
END LOOP;
END;$$;
SELECT sync_kind, sync_subid, sync_nspname, sync_relname, sync_status FROM pglogical.local_sync_status ORDER BY 2,3,4;
sync_kind | sync_subid | sync_nspname | sync_relname | sync_status
-----------+------------+--------------+---------------+-------------
f | 3102546391 | public | multi_ups_tbl | r
d | 3102546391 | | | r
d | 3848008564 | public | multi_ups_tbl | r
f | 3848008564 | | | r
(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
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
CONTEXT: during execution of queued SQL statement:
DROP TABLE public.multi_ups_tbl CASCADE;
replicate_ddl_command
-----------------------
t
(1 row)
|
