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
|
-- 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;
\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;
SELECT * FROM pglogical.create_node(node_name := 'test_provider', dsn := (SELECT provider_dsn FROM pglogical_regress_variables()) || ' user=super');
\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');
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 := '{}');
/*
* 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');
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;
-- Make sure we see the slot and active connection
\c :provider_dsn
SELECT plugin, slot_type, active FROM pg_replication_slots;
SELECT count(*) FROM pg_stat_replication;
|
