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
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
|
--
-- PostgreSQL database dump
--
-- Dumped from database version REPLACED
-- Dumped by pg_dump version REPLACED
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: praefect_database_schema; Type: DATABASE; Schema: -; Owner: -
--
CREATE DATABASE praefect_database_schema WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';
\connect praefect_database_schema
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: replication_job_state; Type: TYPE; Schema: public; Owner: -
--
CREATE TYPE public.replication_job_state AS ENUM (
'ready',
'in_progress',
'completed',
'cancelled',
'failed',
'dead'
);
--
-- Name: notify_on_change(); Type: FUNCTION; Schema: public; Owner: -
--
CREATE FUNCTION public.notify_on_change() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
msg JSONB;
BEGIN
CASE TG_OP
WHEN 'INSERT' THEN
SELECT JSON_AGG(obj) INTO msg
FROM (
SELECT JSONB_BUILD_OBJECT('virtual_storage', virtual_storage, 'relative_paths', ARRAY_AGG(DISTINCT relative_path)) AS obj
FROM NEW
GROUP BY virtual_storage
) t;
WHEN 'UPDATE' THEN
SELECT JSON_AGG(obj) INTO msg
FROM (
SELECT JSONB_BUILD_OBJECT('virtual_storage', virtual_storage, 'relative_paths', ARRAY_AGG(DISTINCT relative_path)) AS obj
FROM NEW
FULL JOIN OLD USING (virtual_storage, relative_path)
GROUP BY virtual_storage
) t;
WHEN 'DELETE' THEN
SELECT JSON_AGG(obj) INTO msg
FROM (
SELECT JSONB_BUILD_OBJECT('virtual_storage', virtual_storage, 'relative_paths', ARRAY_AGG(DISTINCT relative_path)) AS obj
FROM OLD
GROUP BY virtual_storage
) t;
END CASE;
CASE WHEN JSONB_ARRAY_LENGTH(msg) > 0 THEN
PERFORM PG_NOTIFY(TG_ARGV[TG_NARGS-1], msg::TEXT);
ELSE END CASE;
RETURN NULL;
END;
$$;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: node_status; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.node_status (
id bigint NOT NULL,
praefect_name character varying(511) NOT NULL,
shard_name character varying(255) NOT NULL,
node_name character varying(255) NOT NULL,
last_contact_attempt_at timestamp with time zone,
last_seen_active_at timestamp with time zone
);
--
-- Name: healthy_storages; Type: VIEW; Schema: public; Owner: -
--
CREATE VIEW public.healthy_storages AS
SELECT ns.shard_name AS virtual_storage,
ns.node_name AS storage
FROM public.node_status ns
WHERE (ns.last_seen_active_at >= (now() - '00:00:10'::interval))
GROUP BY ns.shard_name, ns.node_name
HAVING ((count(ns.praefect_name))::numeric >= ( SELECT ceil(((count(DISTINCT node_status.praefect_name))::numeric / 2.0)) AS quorum_count
FROM public.node_status
WHERE (((node_status.shard_name)::text = (ns.shard_name)::text) AND (node_status.last_contact_attempt_at >= (now() - '00:01:00'::interval)))))
ORDER BY ns.shard_name, ns.node_name;
--
-- Name: hello_world; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.hello_world (
id integer
);
--
-- Name: node_status_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.node_status_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: node_status_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.node_status_id_seq OWNED BY public.node_status.id;
--
-- Name: replication_queue; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.replication_queue (
id bigint NOT NULL,
state public.replication_job_state DEFAULT 'ready'::public.replication_job_state NOT NULL,
created_at timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
updated_at timestamp without time zone,
attempt integer DEFAULT 3 NOT NULL,
lock_id text,
job jsonb,
meta jsonb
);
--
-- Name: replication_queue_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.replication_queue_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: replication_queue_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.replication_queue_id_seq OWNED BY public.replication_queue.id;
--
-- Name: replication_queue_job_lock; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.replication_queue_job_lock (
job_id bigint NOT NULL,
lock_id text NOT NULL,
triggered_at timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL
);
--
-- Name: replication_queue_lock; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.replication_queue_lock (
id text NOT NULL,
acquired boolean DEFAULT false NOT NULL
);
--
-- Name: repositories; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.repositories (
virtual_storage text NOT NULL,
relative_path text NOT NULL,
generation bigint,
"primary" text,
repository_id bigint NOT NULL,
replica_path text
);
--
-- Name: repositories_repository_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.repositories_repository_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: repositories_repository_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.repositories_repository_id_seq OWNED BY public.repositories.repository_id;
--
-- Name: repository_assignments; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.repository_assignments (
virtual_storage text NOT NULL,
relative_path text NOT NULL,
storage text NOT NULL,
repository_id bigint NOT NULL
);
--
-- Name: storage_repositories; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.storage_repositories (
virtual_storage text NOT NULL,
relative_path text NOT NULL,
storage text NOT NULL,
generation bigint NOT NULL,
repository_id bigint NOT NULL
);
--
-- Name: repository_generations; Type: VIEW; Schema: public; Owner: -
--
CREATE VIEW public.repository_generations AS
SELECT storage_repositories.virtual_storage,
storage_repositories.relative_path,
max(storage_repositories.generation) AS generation
FROM public.storage_repositories
GROUP BY storage_repositories.virtual_storage, storage_repositories.relative_path;
--
-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.schema_migrations (
id text NOT NULL,
applied_at timestamp with time zone
);
--
-- Name: shard_primaries; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.shard_primaries (
id bigint NOT NULL,
shard_name character varying(255) NOT NULL,
node_name character varying(255) NOT NULL,
elected_by_praefect character varying(255) NOT NULL,
elected_at timestamp with time zone NOT NULL,
read_only boolean DEFAULT false NOT NULL,
demoted boolean DEFAULT false NOT NULL,
previous_writable_primary text
);
--
-- Name: shard_primaries_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.shard_primaries_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: shard_primaries_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.shard_primaries_id_seq OWNED BY public.shard_primaries.id;
--
-- Name: storage_cleanups; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.storage_cleanups (
virtual_storage text NOT NULL,
storage text NOT NULL,
last_run timestamp without time zone,
triggered_at timestamp without time zone
);
--
-- Name: valid_primaries; Type: VIEW; Schema: public; Owner: -
--
CREATE VIEW public.valid_primaries AS
SELECT candidates.repository_id,
candidates.virtual_storage,
candidates.relative_path,
candidates.storage
FROM ( SELECT repositories.repository_id,
repositories.virtual_storage,
repositories.relative_path,
storage_repositories.storage,
((repository_assignments.storage IS NOT NULL) OR bool_and((repository_assignments.storage IS NULL)) OVER (PARTITION BY repositories.repository_id)) AS eligible
FROM (((public.repositories
JOIN ( SELECT storage_repositories_1.repository_id,
storage_repositories_1.storage,
storage_repositories_1.generation
FROM public.storage_repositories storage_repositories_1) storage_repositories USING (repository_id, generation))
JOIN public.healthy_storages USING (virtual_storage, storage))
LEFT JOIN public.repository_assignments USING (repository_id, storage))
WHERE (NOT (EXISTS ( SELECT
FROM public.replication_queue
WHERE ((replication_queue.state <> ALL (ARRAY['completed'::public.replication_job_state, 'dead'::public.replication_job_state, 'cancelled'::public.replication_job_state])) AND ((replication_queue.job ->> 'change'::text) = 'delete_replica'::text) AND (((replication_queue.job ->> 'repository_id'::text))::bigint = repositories.repository_id) AND ((replication_queue.job ->> 'target_node_storage'::text) = storage_repositories.storage)))))) candidates
WHERE candidates.eligible;
--
-- Name: virtual_storages; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.virtual_storages (
virtual_storage text NOT NULL,
repositories_imported boolean DEFAULT false NOT NULL
);
--
-- Name: node_status id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.node_status ALTER COLUMN id SET DEFAULT nextval('public.node_status_id_seq'::regclass);
--
-- Name: replication_queue id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.replication_queue ALTER COLUMN id SET DEFAULT nextval('public.replication_queue_id_seq'::regclass);
--
-- Name: repositories repository_id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.repositories ALTER COLUMN repository_id SET DEFAULT nextval('public.repositories_repository_id_seq'::regclass);
--
-- Name: shard_primaries id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.shard_primaries ALTER COLUMN id SET DEFAULT nextval('public.shard_primaries_id_seq'::regclass);
--
-- Name: node_status node_status_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.node_status
ADD CONSTRAINT node_status_pkey PRIMARY KEY (id);
--
-- Name: replication_queue_job_lock replication_queue_job_lock_pk; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.replication_queue_job_lock
ADD CONSTRAINT replication_queue_job_lock_pk PRIMARY KEY (job_id, lock_id);
--
-- Name: replication_queue_lock replication_queue_lock_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.replication_queue_lock
ADD CONSTRAINT replication_queue_lock_pkey PRIMARY KEY (id);
--
-- Name: replication_queue replication_queue_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.replication_queue
ADD CONSTRAINT replication_queue_pkey PRIMARY KEY (id);
--
-- Name: repositories repositories_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.repositories
ADD CONSTRAINT repositories_pkey PRIMARY KEY (repository_id);
--
-- Name: repository_assignments repository_assignments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.repository_assignments
ADD CONSTRAINT repository_assignments_pkey PRIMARY KEY (virtual_storage, relative_path, storage);
--
-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.schema_migrations
ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (id);
--
-- Name: shard_primaries shard_primaries_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.shard_primaries
ADD CONSTRAINT shard_primaries_pkey PRIMARY KEY (id);
--
-- Name: storage_cleanups storage_cleanups_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.storage_cleanups
ADD CONSTRAINT storage_cleanups_pkey PRIMARY KEY (virtual_storage, storage);
--
-- Name: storage_repositories storage_repositories_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.storage_repositories
ADD CONSTRAINT storage_repositories_pkey PRIMARY KEY (virtual_storage, relative_path, storage);
--
-- Name: virtual_storages virtual_storages_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.virtual_storages
ADD CONSTRAINT virtual_storages_pkey PRIMARY KEY (virtual_storage);
--
-- Name: delete_replica_unique_index; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX delete_replica_unique_index ON public.replication_queue USING btree (((job ->> 'virtual_storage'::text)), ((job ->> 'relative_path'::text))) WHERE ((state <> ALL (ARRAY['completed'::public.replication_job_state, 'cancelled'::public.replication_job_state, 'dead'::public.replication_job_state])) AND ((job ->> 'change'::text) = 'delete_replica'::text));
--
-- Name: replication_queue_target_index; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX replication_queue_target_index ON public.replication_queue USING btree (((job ->> 'virtual_storage'::text)), ((job ->> 'relative_path'::text)), ((job ->> 'target_node_storage'::text)), ((job ->> 'change'::text))) WHERE (state <> ALL (ARRAY['completed'::public.replication_job_state, 'cancelled'::public.replication_job_state, 'dead'::public.replication_job_state]));
--
-- Name: repository_assignments_new_pkey; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX repository_assignments_new_pkey ON public.repository_assignments USING btree (repository_id, storage);
--
-- Name: repository_lookup_index; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX repository_lookup_index ON public.repositories USING btree (virtual_storage, relative_path);
--
-- Name: repository_replica_path_index; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX repository_replica_path_index ON public.repositories USING btree (replica_path, virtual_storage);
--
-- Name: shard_name_on_node_status_idx; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX shard_name_on_node_status_idx ON public.node_status USING btree (shard_name, node_name);
--
-- Name: shard_name_on_shard_primaries_idx; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX shard_name_on_shard_primaries_idx ON public.shard_primaries USING btree (shard_name);
--
-- Name: shard_node_names_on_node_status_idx; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX shard_node_names_on_node_status_idx ON public.node_status USING btree (praefect_name, shard_name, node_name);
--
-- Name: storage_repositories_new_pkey; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX storage_repositories_new_pkey ON public.storage_repositories USING btree (repository_id, storage);
--
-- Name: virtual_target_on_replication_queue_idx; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX virtual_target_on_replication_queue_idx ON public.replication_queue USING btree (((job ->> 'virtual_storage'::text)), ((job ->> 'target_node_storage'::text)));
--
-- Name: repositories notify_on_delete; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER notify_on_delete AFTER DELETE ON public.repositories REFERENCING OLD TABLE AS old FOR EACH STATEMENT EXECUTE PROCEDURE public.notify_on_change('repositories_updates');
--
-- Name: storage_repositories notify_on_delete; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER notify_on_delete AFTER DELETE ON public.storage_repositories REFERENCING OLD TABLE AS old FOR EACH STATEMENT EXECUTE PROCEDURE public.notify_on_change('storage_repositories_updates');
--
-- Name: storage_repositories notify_on_insert; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER notify_on_insert AFTER INSERT ON public.storage_repositories REFERENCING NEW TABLE AS new FOR EACH STATEMENT EXECUTE PROCEDURE public.notify_on_change('storage_repositories_updates');
--
-- Name: storage_repositories notify_on_update; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER notify_on_update AFTER UPDATE ON public.storage_repositories REFERENCING OLD TABLE AS old NEW TABLE AS new FOR EACH STATEMENT EXECUTE PROCEDURE public.notify_on_change('storage_repositories_updates');
--
-- Name: replication_queue_job_lock replication_queue_job_lock_job_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.replication_queue_job_lock
ADD CONSTRAINT replication_queue_job_lock_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.replication_queue(id);
--
-- Name: replication_queue_job_lock replication_queue_job_lock_lock_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.replication_queue_job_lock
ADD CONSTRAINT replication_queue_job_lock_lock_id_fkey FOREIGN KEY (lock_id) REFERENCES public.replication_queue_lock(id);
--
-- Name: repository_assignments repository_assignments_repository_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.repository_assignments
ADD CONSTRAINT repository_assignments_repository_id_fkey FOREIGN KEY (repository_id) REFERENCES public.repositories(repository_id) ON DELETE CASCADE;
--
-- Name: repository_assignments repository_assignments_virtual_storage_relative_path_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.repository_assignments
ADD CONSTRAINT repository_assignments_virtual_storage_relative_path_fkey FOREIGN KEY (virtual_storage, relative_path) REFERENCES public.repositories(virtual_storage, relative_path) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: storage_repositories storage_repositories_repository_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.storage_repositories
ADD CONSTRAINT storage_repositories_repository_id_fkey FOREIGN KEY (repository_id) REFERENCES public.repositories(repository_id) ON DELETE CASCADE;
--
-- PostgreSQL database dump complete
--
|