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
|
-- This should be done with pg_regress's --create-role option
-- but it's blocked by bug 37906
SELECT * FROM pglogical_regress_variables()
\gset
\c :provider_dsn
SET client_min_messages = 'warning';
DROP USER IF EXISTS nonsuper;
DROP USER IF EXISTS super;
CREATE USER nonsuper WITH replication;
CREATE USER super SUPERUSER;
\c :subscriber_dsn
SET client_min_messages = 'warning';
DROP USER IF EXISTS nonsuper;
DROP USER IF EXISTS super;
CREATE USER nonsuper WITH replication;
CREATE USER super SUPERUSER;
-- Can't because of bug 37906
--GRANT ALL ON DATABASE regress TO nonsuper;
--GRANT ALL ON DATABASE regress TO nonsuper;
\c :provider_dsn
GRANT ALL ON SCHEMA public TO nonsuper;
DO $$
BEGIN
IF (SELECT setting::integer/100 FROM pg_settings WHERE name = 'server_version_num') >= 1000 THEN
CREATE OR REPLACE FUNCTION public.pg_current_xlog_location() RETURNS pg_lsn
LANGUAGE SQL AS 'SELECT pg_current_wal_lsn()';
ALTER FUNCTION public.pg_current_xlog_location() OWNER TO super;
END IF;
END; $$;
\c :subscriber_dsn
GRANT ALL ON SCHEMA public TO nonsuper;
SELECT E'\'' || current_database() || E'\'' AS subdb;
subdb
------------
'postgres'
(1 row)
\gset
\c :provider_dsn
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;$$;
DO $$
BEGIN
IF version() ~ 'Postgres-XL' THEN
CREATE EXTENSION IF NOT EXISTS pglogical;
ELSE
CREATE EXTENSION IF NOT EXISTS pglogical VERSION '1.0.0';
END IF;
END;
$$;
ALTER EXTENSION pglogical UPDATE;
-- v18 \dx pglogical (v18 added column "Default version")
SELECT
e.extname AS "Name",
e.extversion AS "Version",
ae.default_version AS "Default version",
n.nspname AS "Schema",
d.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description d ON
d.objoid = e.oid AND
d.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
LEFT JOIN pg_catalog.pg_available_extensions()
ae(name, default_version, comment) ON ae.name = e.extname
WHERE
e.extname = 'pglogical' COLLATE pg_catalog.default
ORDER BY 1;
Name | Version | Default version | Schema | Description
-----------+---------+-----------------+-----------+--------------------------------
pglogical | 2.4.6 | 2.4.6 | pglogical | PostgreSQL Logical Replication
(1 row)
SELECT * FROM pglogical.create_node(node_name := 'test_provider', dsn := (SELECT provider_dsn FROM pglogical_regress_variables()) || ' user=super');
create_node
-------------
2689511696
(1 row)
\c :subscriber_dsn
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_subscriber', dsn := (SELECT subscriber_dsn FROM pglogical_regress_variables()) || ' user=super');
create_node
-------------
1755434425
(1 row)
BEGIN;
SELECT * FROM pglogical.create_subscription(
subscription_name := 'test_subscription',
provider_dsn := (SELECT provider_dsn FROM pglogical_regress_variables()) || ' user=super',
synchronize_structure := true,
forward_origins := '{}');
create_subscription
---------------------
3848008564
(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;
BEGIN;
SET LOCAL statement_timeout = '180s';
SELECT pglogical.wait_for_subscription_sync_complete('test_subscription');
wait_for_subscription_sync_complete
-------------------------------------
(1 row)
COMMIT;
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 | 3848008564 | | | t
(1 row)
-- Make sure we see the slot and active connection
\c :provider_dsn
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)
|
