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
|
SELECT * FROM pglogical_regress_variables()
\gset
\c :provider_dsn
SET client_min_messages = 'warning';
DROP ROLE IF EXISTS nonreplica;
CREATE USER nonreplica;
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;
GRANT ALL ON SCHEMA pglogical TO nonreplica;
GRANT ALL ON ALL TABLES IN SCHEMA pglogical TO nonreplica;
\c :subscriber_dsn
SET client_min_messages = 'warning';
\set VERBOSITY terse
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;
-- fail (local node not existing)
SELECT * FROM pglogical.create_subscription(
subscription_name := 'test_subscription',
provider_dsn := (SELECT provider_dsn FROM pglogical_regress_variables()) || ' user=nonreplica',
forward_origins := '{}');
ERROR: local pglogical node not found
-- succeed
SELECT * FROM pglogical.create_node(node_name := 'test_subscriber', dsn := (SELECT subscriber_dsn FROM pglogical_regress_variables()) || ' user=nonreplica');
create_node
-------------
1755434425
(1 row)
-- fail (can't connect to remote)
DO $$
BEGIN
SELECT * FROM pglogical.create_subscription(
subscription_name := 'test_subscription',
provider_dsn := (SELECT provider_dsn FROM pglogical_regress_variables()) || ' user=nonexisting',
forward_origins := '{}');
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION '%:%', split_part(SQLERRM, ':', 1), (regexp_matches(SQLERRM, '^.*( FATAL:.*role.*)$'))[1];
END;
$$;
ERROR: could not connect to the postgresql server: FATAL: role "nonexisting" does not exist
-- fail (remote node not existing)
SELECT * FROM pglogical.create_subscription(
subscription_name := 'test_subscription',
provider_dsn := (SELECT provider_dsn FROM pglogical_regress_variables()) || ' user=nonreplica',
forward_origins := '{}');
ERROR: could not fetch remote node info: ERROR: local pglogical node not found
\c :provider_dsn
-- succeed
SELECT * FROM pglogical.create_node(node_name := 'test_provider', dsn := (SELECT provider_dsn FROM pglogical_regress_variables()) || ' user=nonreplica');
create_node
-------------
2689511696
(1 row)
\c :subscriber_dsn
\set VERBOSITY terse
-- fail (can't connect with replication connection to remote)
DO $$
BEGIN
SELECT * FROM pglogical.create_subscription(
subscription_name := 'test_subscription',
provider_dsn := (SELECT provider_dsn FROM pglogical_regress_variables()) || ' user=nonreplica',
forward_origins := '{}');
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION '%', split_part(SQLERRM, ':', 1);
END;
$$;
ERROR: could not connect to the postgresql server in replication mode
-- cleanup
SELECT * FROM pglogical.drop_node('test_subscriber');
drop_node
-----------
t
(1 row)
DROP EXTENSION pglogical;
\c :provider_dsn
SELECT * FROM pglogical.drop_node('test_provider');
drop_node
-----------
t
(1 row)
SET client_min_messages = 'warning';
DROP OWNED BY nonreplica;
DROP ROLE IF EXISTS nonreplica;
DROP EXTENSION pglogical;
|
