praefect: Track database schema in Git

Given that our Praefect database schema is seeded by migrations, there
is no single place which gives an overview over the complete schema.
While this is information that can be easily obtained by connecting to a
Praefect database, this workaround doesn't really work across different
Praefect versions given that one would have to re-seed the database for
each version one is about to investigate. This makes it hard to get info
about the current database schema and to compare changes to the schema
between different versions.

Introduce a new script "generate-praefect-schema" to fix this issue. The
script connects to a Postgres server, creates a new database, seeds the
database by executing `praefect sql-migrate` and then dumps the
resulting schema. Furthermore, a new Makefile target automates this and
writes the dump into our `_support` directory.

The intent is that whenever the database changes, the author of those
changes must update the schema in our `_support` folder such that it's
easily possible to diff the schema across different versions in our Git
history.

To ensure that the schema is always up to date, a new CI job is added
which performs the dump and then checks that no changes occurred.

2 files changed, 716 insertions, 0 deletions

diff --git a/_support/generate-praefect-schema b/_support/generate-praefect-schema
new file mode 100755
index 000000000..e2a254b50
--- /dev/null
+++ b/_support/generate-praefect-schema
@@ -0,0 +1,80 @@
+#!/usr/bin/env bash
+
+set -eo pipefail
+
+declare -r SOURCE_DIR=$(dirname "$(dirname "$(realpath "$0")")")
+
+function die_usage() {
+    echo "USAGE: $0" >&2
+    if test "$#" -ge 1
+    then
+        echo
+        echo "$@" >&2
+    fi
+
+    exit 1
+}
+
+if test -z "${PGHOST}" && command -v gdk >/dev/null
+then
+    # Try to get database information from the GDK, if available.
+    eval $(gdk env)
+fi
+
+if test -z "${PGHOST}"
+then
+    die_usage "PGHOST environment variable is not set"
+fi
+
+if test -z "${PGPORT}"
+then
+    die_usage "PGPORT environment variable is not set"
+fi
+
+declare -r DUMP_PATH="$1"
+if test "$#" -gt 0
+then
+    die_usage "Script does not take any arguments"
+fi
+
+# Create a temporary database. This database will be removed when the script
+# exits. We want to fail in case createdb(1) finds the database to exist
+# already such that we do not clobber any preexisting data.
+declare -r DBNAME=praefect_database_schema
+createdb "${DBNAME}"
+trap "dropdb '${DBNAME}'" EXIT
+
+# Create a temporary file where the Praefect configuration will be written to.
+# This file is deleted when the script exits.
+declare -r PRAEFECT_CFG=$(mktemp -t "praefect-config-XXXXXXXX")
+trap "dropdb '${DBNAME}' ; rm -f '${PRAEFECT_CFG}'" EXIT
+
+# Generate a dummy configuration. We don't care about anything but the database
+# configuration, but Praefect will fail to start if we have no listen address
+# and no virtual storage configured.
+cat >"${PRAEFECT_CFG}" <<EOF
+listen_addr = "127.0.0.1:0"
+
+[[virtual_storage]]
+name = "default"
+
+[[virtual_storage.node]]
+storage = "dummy-node"
+address = "unix:/dummy"
+
+[database]
+host = "${PGHOST}"
+port = ${PGPORT}
+dbname = "${DBNAME}"
+sslmode = "disable"
+EOF
+
+# Run the migration such that all required tables are created.
+"${SOURCE_DIR}"/_build/bin/praefect -config "${PRAEFECT_CFG}" sql-migrate >/dev/null
+
+# And then finally dump the database schema. We ignore the owner because it
+# would change based on the local user name. Furthermore, we replace the
+# database version such that the output doesn't change based on the the
+# Postgres version.
+pg_dump --create --schema-only --no-owner --dbname "$DBNAME" |
+    sed -e 's/^\(-- Dumped .* version\) [0-9]\+\(.[0-9]\+\).*/\1 REPLACED/'
diff --git a/_support/praefect-schema.sql b/_support/praefect-schema.sql
new file mode 100644
index 000000000..b69a2f0c6
--- /dev/null
+++ b/_support/praefect-schema.sql
@@ -0,0 +1,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
+--
+