forked from 2ndQuadrant/pglogical
-
Notifications
You must be signed in to change notification settings - Fork 0
/
pglogical--2.3.3.sql
249 lines (207 loc) · 12.2 KB
/
pglogical--2.3.3.sql
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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
\echo Use "CREATE EXTENSION pglogical" to load this file. \quit
CREATE TABLE pglogical.node (
node_id oid NOT NULL PRIMARY KEY,
node_name name NOT NULL UNIQUE
) WITH (user_catalog_table=true);
CREATE TABLE pglogical.node_interface (
if_id oid NOT NULL PRIMARY KEY,
if_name name NOT NULL, -- default same as node name
if_nodeid oid REFERENCES node(node_id),
if_dsn text NOT NULL,
UNIQUE (if_nodeid, if_name)
);
CREATE TABLE pglogical.local_node (
node_id oid PRIMARY KEY REFERENCES node(node_id),
node_local_interface oid NOT NULL REFERENCES node_interface(if_id)
);
CREATE TABLE pglogical.subscription (
sub_id oid NOT NULL PRIMARY KEY,
sub_name name NOT NULL UNIQUE,
sub_origin oid NOT NULL REFERENCES node(node_id),
sub_target oid NOT NULL REFERENCES node(node_id),
sub_origin_if oid NOT NULL REFERENCES node_interface(if_id),
sub_target_if oid NOT NULL REFERENCES node_interface(if_id),
sub_enabled boolean NOT NULL DEFAULT true,
sub_slot_name name NOT NULL,
sub_replication_sets text[],
sub_forward_origins text[],
sub_apply_delay interval NOT NULL DEFAULT '0',
sub_force_text_transfer boolean NOT NULL DEFAULT 'f'
);
CREATE TABLE pglogical.local_sync_status (
sync_kind "char" NOT NULL CHECK (sync_kind IN ('i', 's', 'd', 'f')),
sync_subid oid NOT NULL REFERENCES pglogical.subscription(sub_id),
sync_nspname name,
sync_relname name,
sync_status "char" NOT NULL,
sync_statuslsn pg_lsn NOT NULL,
UNIQUE (sync_subid, sync_nspname, sync_relname)
);
CREATE FUNCTION pglogical.create_node(node_name name, dsn text)
RETURNS oid STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_create_node';
CREATE FUNCTION pglogical.drop_node(node_name name, ifexists boolean DEFAULT false)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_drop_node';
CREATE FUNCTION pglogical.alter_node_add_interface(node_name name, interface_name name, dsn text)
RETURNS oid STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_node_add_interface';
CREATE FUNCTION pglogical.alter_node_drop_interface(node_name name, interface_name name)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_node_drop_interface';
CREATE FUNCTION pglogical.create_subscription(subscription_name name, provider_dsn text,
replication_sets text[] = '{default,default_insert_only,ddl_sql}', synchronize_structure boolean = false,
synchronize_data boolean = true, forward_origins text[] = '{all}', apply_delay interval DEFAULT '0',
force_text_transfer boolean = false)
RETURNS oid STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_create_subscription';
CREATE FUNCTION pglogical.drop_subscription(subscription_name name, ifexists boolean DEFAULT false)
RETURNS oid STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_drop_subscription';
CREATE FUNCTION pglogical.alter_subscription_interface(subscription_name name, interface_name name)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_subscription_interface';
CREATE FUNCTION pglogical.alter_subscription_disable(subscription_name name, immediate boolean DEFAULT false)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_subscription_disable';
CREATE FUNCTION pglogical.alter_subscription_enable(subscription_name name, immediate boolean DEFAULT false)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_subscription_enable';
CREATE FUNCTION pglogical.alter_subscription_add_replication_set(subscription_name name, replication_set name)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_subscription_add_replication_set';
CREATE FUNCTION pglogical.alter_subscription_remove_replication_set(subscription_name name, replication_set name)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_subscription_remove_replication_set';
CREATE FUNCTION pglogical.show_subscription_status(subscription_name name DEFAULT NULL,
OUT subscription_name text, OUT status text, OUT provider_node text,
OUT provider_dsn text, OUT slot_name text, OUT replication_sets text[],
OUT forward_origins text[])
RETURNS SETOF record STABLE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_show_subscription_status';
CREATE TABLE pglogical.replication_set (
set_id oid NOT NULL PRIMARY KEY,
set_nodeid oid NOT NULL,
set_name name NOT NULL,
replicate_insert boolean NOT NULL DEFAULT true,
replicate_update boolean NOT NULL DEFAULT true,
replicate_delete boolean NOT NULL DEFAULT true,
replicate_truncate boolean NOT NULL DEFAULT true,
UNIQUE (set_nodeid, set_name)
) WITH (user_catalog_table=true);
CREATE TABLE pglogical.replication_set_table (
set_id oid NOT NULL,
set_reloid regclass NOT NULL,
set_att_list text[],
set_row_filter pg_node_tree,
PRIMARY KEY(set_id, set_reloid)
) WITH (user_catalog_table=true);
CREATE TABLE pglogical.replication_set_seq (
set_id oid NOT NULL,
set_seqoid regclass NOT NULL,
PRIMARY KEY(set_id, set_seqoid)
) WITH (user_catalog_table=true);
CREATE TABLE pglogical.sequence_state (
seqoid oid NOT NULL PRIMARY KEY,
cache_size integer NOT NULL,
last_value bigint NOT NULL
) WITH (user_catalog_table=true);
CREATE TABLE pglogical.depend (
classid oid NOT NULL,
objid oid NOT NULL,
objsubid integer NOT NULL,
refclassid oid NOT NULL,
refobjid oid NOT NULL,
refobjsubid integer NOT NULL,
deptype "char" NOT NULL
) WITH (user_catalog_table=true);
CREATE VIEW pglogical.TABLES AS
WITH set_relations AS (
SELECT s.set_name, r.set_reloid
FROM pglogical.replication_set_table r,
pglogical.replication_set s,
pglogical.local_node n
WHERE s.set_nodeid = n.node_id
AND s.set_id = r.set_id
),
user_tables AS (
SELECT r.oid, n.nspname, r.relname, r.relreplident
FROM pg_catalog.pg_class r,
pg_catalog.pg_namespace n
WHERE r.relkind = 'r'
AND r.relpersistence = 'p'
AND n.oid = r.relnamespace
AND n.nspname !~ '^pg_'
AND n.nspname != 'information_schema'
AND n.nspname != 'pglogical'
)
SELECT r.oid AS relid, n.nspname, r.relname, s.set_name
FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class r,
set_relations s
WHERE r.relkind = 'r'
AND n.oid = r.relnamespace
AND r.oid = s.set_reloid
UNION
SELECT t.oid AS relid, t.nspname, t.relname, NULL
FROM user_tables t
WHERE t.oid NOT IN (SELECT set_reloid FROM set_relations);
CREATE FUNCTION pglogical.create_replication_set(set_name name,
replicate_insert boolean = true, replicate_update boolean = true,
replicate_delete boolean = true, replicate_truncate boolean = true)
RETURNS oid STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_create_replication_set';
CREATE FUNCTION pglogical.alter_replication_set(set_name name,
replicate_insert boolean DEFAULT NULL, replicate_update boolean DEFAULT NULL,
replicate_delete boolean DEFAULT NULL, replicate_truncate boolean DEFAULT NULL)
RETURNS oid CALLED ON NULL INPUT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_replication_set';
CREATE FUNCTION pglogical.drop_replication_set(set_name name, ifexists boolean DEFAULT false)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_drop_replication_set';
CREATE FUNCTION pglogical.replication_set_add_table(set_name name, relation regclass, synchronize_data boolean DEFAULT false,
columns text[] DEFAULT NULL, row_filter text DEFAULT NULL)
RETURNS boolean CALLED ON NULL INPUT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_replication_set_add_table';
CREATE FUNCTION pglogical.replication_set_add_all_tables(set_name name, schema_names text[], synchronize_data boolean DEFAULT false)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_replication_set_add_all_tables';
CREATE FUNCTION pglogical.replication_set_remove_table(set_name name, relation regclass)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_replication_set_remove_table';
CREATE FUNCTION pglogical.replication_set_add_sequence(set_name name, relation regclass, synchronize_data boolean DEFAULT false)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_replication_set_add_sequence';
CREATE FUNCTION pglogical.replication_set_add_all_sequences(set_name name, schema_names text[], synchronize_data boolean DEFAULT false)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_replication_set_add_all_sequences';
CREATE FUNCTION pglogical.replication_set_remove_sequence(set_name name, relation regclass)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_replication_set_remove_sequence';
CREATE FUNCTION pglogical.alter_subscription_synchronize(subscription_name name, truncate boolean DEFAULT false)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_subscription_synchronize';
CREATE FUNCTION pglogical.alter_subscription_resynchronize_table(subscription_name name, relation regclass,
truncate boolean DEFAULT true)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_alter_subscription_resynchronize_table';
CREATE FUNCTION pglogical.synchronize_sequence(relation regclass)
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_synchronize_sequence';
CREATE FUNCTION pglogical.table_data_filtered(reltyp anyelement, relation regclass, repsets text[])
RETURNS SETOF anyelement CALLED ON NULL INPUT STABLE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_table_data_filtered';
CREATE FUNCTION pglogical.show_repset_table_info(relation regclass, repsets text[], OUT relid oid, OUT nspname text,
OUT relname text, OUT att_list text[], OUT has_row_filter boolean)
RETURNS record STRICT STABLE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_show_repset_table_info';
CREATE FUNCTION pglogical.show_subscription_table(subscription_name name, relation regclass, OUT nspname text, OUT relname text, OUT status text)
RETURNS record STRICT STABLE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_show_subscription_table';
CREATE TABLE pglogical.queue (
queued_at timestamp with time zone NOT NULL,
role name NOT NULL,
replication_sets text[],
message_type "char" NOT NULL,
message json NOT NULL
);
CREATE FUNCTION pglogical.replicate_ddl_command(command text, replication_sets text[] DEFAULT '{ddl_sql}')
RETURNS boolean STRICT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_replicate_ddl_command';
CREATE OR REPLACE FUNCTION pglogical.queue_truncate()
RETURNS trigger LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_queue_truncate';
CREATE FUNCTION pglogical.pglogical_node_info(OUT node_id oid, OUT node_name text, OUT sysid text, OUT dbname text, OUT replication_sets text)
RETURNS record
STABLE STRICT LANGUAGE c AS 'MODULE_PATHNAME';
CREATE FUNCTION pglogical.pglogical_gen_slot_name(name, name, name)
RETURNS name
IMMUTABLE STRICT LANGUAGE c AS 'MODULE_PATHNAME';
CREATE FUNCTION pglogical_version() RETURNS text
LANGUAGE c AS 'MODULE_PATHNAME';
CREATE FUNCTION pglogical_version_num() RETURNS integer
LANGUAGE c AS 'MODULE_PATHNAME';
CREATE FUNCTION pglogical_max_proto_version() RETURNS integer
LANGUAGE c AS 'MODULE_PATHNAME';
CREATE FUNCTION pglogical_min_proto_version() RETURNS integer
LANGUAGE c AS 'MODULE_PATHNAME';
CREATE FUNCTION
pglogical.wait_slot_confirm_lsn(slotname name, target pg_lsn)
RETURNS void LANGUAGE c AS 'pglogical','pglogical_wait_slot_confirm_lsn';
CREATE FUNCTION pglogical.wait_for_subscription_sync_complete(subscription_name name)
RETURNS void RETURNS NULL ON NULL INPUT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_wait_for_subscription_sync_complete';
CREATE FUNCTION pglogical.wait_for_table_sync_complete(subscription_name name, relation regclass)
RETURNS void RETURNS NULL ON NULL INPUT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_wait_for_table_sync_complete';
CREATE FUNCTION pglogical.xact_commit_timestamp_origin("xid" xid, OUT "timestamp" timestamptz, OUT "roident" oid)
RETURNS record RETURNS NULL ON NULL INPUT VOLATILE LANGUAGE c AS 'MODULE_PATHNAME', 'pglogical_xact_commit_timestamp_origin';