storcon: squash all migrations into one

Problem

Neon and Hadron deployments have the same database schema, but different
migration histories. Some transactions have different identifiers too.
If we don't do anything about it, then the storage controller would fail
to apply the merged set of transactions.

Summary of Changes

We squash all migrations into a single one. If the schema already
matches, then the new transaction gets applie without doing anything.
For new regions, this migration will bootstrap the database schema.

This should be merged in both neon and hadron codebases.

Note that after deploying this change, the `__diesel_schema_migrations`
table will still contain entries for the old pre-squash transactions.
This is fine because diesel only considers the tranasactions embedded
in the repo for application. Once we are certain that we are not going
to roll back, we can clean up the `__diesel_schema_migrations` tables in
prod, but this is manual and error prone, so I'd skip it.

Rolling back

Rolling back to a previous deployment which embeds the non-squashed
transactions is safe. The old transactions are still present in
`__diesel_schema_migrations` (i.e. considered applied), so no migrations
will be run, so no migrations will be run.

Note that this assumes that all transactions are applied and squashed into
the new migration before deployment.

storage_controller/migrations/2024-01-07-211257_create_tenant_shards/down.sql

@@ -1 +0,0 @@
-DROP TABLE tenant_shards;

storage_controller/migrations/2024-01-07-211257_create_tenant_shards/up.sql

@@ -1,13 +0,0 @@
-CREATE TABLE tenant_shards (
-  tenant_id VARCHAR NOT NULL,
-  shard_number INTEGER NOT NULL,
-  shard_count INTEGER NOT NULL,
-  PRIMARY KEY(tenant_id, shard_number, shard_count),
-  shard_stripe_size INTEGER NOT NULL,
-  generation INTEGER NOT NULL,
-  generation_pageserver BIGINT NOT NULL,
-  placement_policy VARCHAR NOT NULL,
-  splitting SMALLINT NOT NULL,
-  -- config is JSON encoded, opaque to the database.
-  config TEXT NOT NULL
-);

storage_controller/migrations/2024-01-07-212945_create_nodes/down.sql

@@ -1 +0,0 @@
-DROP TABLE nodes;

storage_controller/migrations/2024-01-07-212945_create_nodes/up.sql

@@ -1,10 +0,0 @@
-CREATE TABLE nodes (
-  node_id BIGINT PRIMARY KEY NOT NULL,
-
-  scheduling_policy VARCHAR NOT NULL,
-
-  listen_http_addr VARCHAR NOT NULL,
-  listen_http_port INTEGER NOT NULL,
-  listen_pg_addr VARCHAR NOT NULL,
-  listen_pg_port INTEGER NOT NULL
-);

storage_controller/migrations/2024-02-29-094122_generations_null/down.sql

@@ -1,2 +0,0 @@
-ALTER TABLE tenant_shards ALTER generation SET NOT NULL;
-ALTER TABLE tenant_shards ALTER generation_pageserver SET NOT NULL;

storage_controller/migrations/2024-02-29-094122_generations_null/up.sql

@@ -1,4 +0,0 @@
-
-
-ALTER TABLE tenant_shards ALTER generation DROP NOT NULL;
-ALTER TABLE tenant_shards ALTER generation_pageserver DROP NOT NULL;

storage_controller/migrations/2024-03-18-184429_rename_policy/down.sql

@@ -1,3 +0,0 @@
-
-UPDATE tenant_shards set placement_policy='{"Double": 1}' where placement_policy='{"Attached": 1}';
-UPDATE tenant_shards set placement_policy='"Single"' where placement_policy='{"Attached": 0}';

storage_controller/migrations/2024-03-18-184429_rename_policy/up.sql

@@ -1,3 +0,0 @@
-
-UPDATE tenant_shards set placement_policy='{"Attached": 1}' where placement_policy='{"Double": 1}';
-UPDATE tenant_shards set placement_policy='{"Attached": 0}' where placement_policy='"Single"';

storage_controller/migrations/2024-03-27-133204_tenant_policies/down.sql

@@ -1,3 +0,0 @@
--- This file should undo anything in `up.sql`
-
-ALTER TABLE tenant_shards drop scheduling_policy;

storage_controller/migrations/2024-03-27-133204_tenant_policies/up.sql

@@ -1,2 +0,0 @@
-
-ALTER TABLE tenant_shards add scheduling_policy VARCHAR NOT NULL DEFAULT '"Active"';

storage_controller/migrations/2024-07-23-191537_create_metadata_health/down.sql

@@ -1 +0,0 @@
-DROP TABLE metadata_health;

storage_controller/migrations/2024-07-23-191537_create_metadata_health/up.sql

@@ -1,14 +0,0 @@
-CREATE TABLE metadata_health (
-  tenant_id VARCHAR NOT NULL,
-  shard_number INTEGER NOT NULL,
-  shard_count INTEGER NOT NULL,
-  PRIMARY KEY(tenant_id, shard_number, shard_count),
-  -- Rely on cascade behavior for delete
-  FOREIGN KEY(tenant_id, shard_number, shard_count) REFERENCES tenant_shards ON DELETE CASCADE,
-  healthy BOOLEAN NOT NULL DEFAULT TRUE,
-  last_scrubbed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
-);
-
-
-INSERT INTO metadata_health(tenant_id, shard_number, shard_count)
-SELECT tenant_id, shard_number, shard_count FROM tenant_shards;

storage_controller/migrations/2024-07-26-140924_create_leader/down.sql

@@ -1 +0,0 @@
-DROP TABLE controllers;

storage_controller/migrations/2024-07-26-140924_create_leader/up.sql

@@ -1,5 +0,0 @@
-CREATE TABLE controllers (
-  address VARCHAR NOT NULL,
-  started_at TIMESTAMPTZ NOT NULL,
-  PRIMARY KEY(address, started_at)
-);

storage_controller/migrations/2024-08-23-102952_safekeepers/down.sql

@@ -1,2 +0,0 @@
--- This file should undo anything in `up.sql`
-DROP TABLE safekeepers;

storage_controller/migrations/2024-08-23-102952_safekeepers/up.sql

@@ -1,15 +0,0 @@
--- started out as a copy of cplane schema, removed the unnecessary columns.
-CREATE TABLE safekeepers (
-	-- the surrogate identifier defined by control plane database sequence
-	id BIGINT PRIMARY KEY,
-	region_id TEXT NOT NULL,
-	version BIGINT NOT NULL,
-	-- the natural id on whatever cloud platform, not needed in storage controller
-	-- instance_id TEXT UNIQUE NOT NULL,
-	host TEXT NOT NULL,
-	port INTEGER NOT NULL,
-	active BOOLEAN NOT NULL DEFAULT false,
-	-- projects_count INTEGER NOT NULL DEFAULT 0,
-	http_port INTEGER NOT NULL,
-	availability_zone_id TEXT NOT NULL
-);

storage_controller/migrations/2024-08-23-170149_tenant_id_index/down.sql

@@ -1,2 +0,0 @@
--- This file should undo anything in `up.sql`
-DROP INDEX tenant_shards_tenant_id;

storage_controller/migrations/2024-08-23-170149_tenant_id_index/up.sql

@@ -1,2 +0,0 @@
--- Your SQL goes here
-CREATE INDEX tenant_shards_tenant_id ON tenant_shards (tenant_id);

storage_controller/migrations/2024-08-27-184400_pageserver_az/down.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes DROP availability_zone_id;

storage_controller/migrations/2024-08-27-184400_pageserver_az/up.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes ADD availability_zone_id VARCHAR;

storage_controller/migrations/2024-08-28-150530_pageserver_az_not_null/down.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes ALTER availability_zone_id DROP NOT NULL;

storage_controller/migrations/2024-08-28-150530_pageserver_az_not_null/up.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes ALTER availability_zone_id SET NOT NULL;

storage_controller/migrations/2024-09-05-104500_tenant_shard_preferred_az/down.sql

@@ -1 +0,0 @@
-ALTER TABLE tenant_shards DROP preferred_az_id;

storage_controller/migrations/2024-09-05-104500_tenant_shard_preferred_az/up.sql

@@ -1 +0,0 @@
-ALTER TABLE tenant_shards ADD preferred_az_id VARCHAR;

storage_controller/migrations/2024-12-12-212515_safekeepers_scheduling_policy/down.sql

@@ -1 +0,0 @@
-ALTER TABLE safekeepers DROP scheduling_policy;

storage_controller/migrations/2024-12-12-212515_safekeepers_scheduling_policy/up.sql

@@ -1 +0,0 @@
-ALTER TABLE safekeepers ADD scheduling_policy VARCHAR NOT NULL DEFAULT 'disabled';

storage_controller/migrations/2025-01-09-160454_safekeepers_remove_active/down.sql

@@ -1,4 +0,0 @@
--- this sadly isn't a "true" revert of the migration, as the column is now at the end of the table.
--- But preserving order is not a trivial operation.
--- https://wiki.postgresql.org/wiki/Alter_column_position
-ALTER TABLE safekeepers ADD active BOOLEAN NOT NULL DEFAULT false;

storage_controller/migrations/2025-01-09-160454_safekeepers_remove_active/up.sql

@@ -1 +0,0 @@
-ALTER TABLE safekeepers DROP active;

storage_controller/migrations/2025-01-15-181207_safekeepers_disabled_to_pause/down.sql

@@ -1,2 +0,0 @@
-ALTER TABLE safekeepers ALTER COLUMN scheduling_policy SET DEFAULT 'disabled';
-UPDATE safekeepers SET scheduling_policy = 'disabled' WHERE scheduling_policy = 'pause';

storage_controller/migrations/2025-01-15-181207_safekeepers_disabled_to_pause/up.sql

@@ -1,2 +0,0 @@
-ALTER TABLE safekeepers ALTER COLUMN scheduling_policy SET DEFAULT 'pause';
-UPDATE safekeepers SET scheduling_policy = 'pause' WHERE scheduling_policy = 'disabled';

storage_controller/migrations/2025-02-11-144848_pageserver_use_https/down.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes DROP listen_https_port;

storage_controller/migrations/2025-02-11-144848_pageserver_use_https/up.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes ADD listen_https_port INTEGER;

storage_controller/migrations/2025-02-14-160526_safekeeper_timelines/down.sql

@@ -1,2 +0,0 @@
-DROP TABLE timelines;
-DROP TABLE safekeeper_timeline_pending_ops;

storage_controller/migrations/2025-02-14-160526_safekeeper_timelines/up.sql

@@ -1,19 +0,0 @@
-CREATE TABLE timelines (
-  tenant_id VARCHAR NOT NULL,
-  timeline_id VARCHAR NOT NULL,
-  start_lsn pg_lsn NOT NULL,
-  generation INTEGER NOT NULL,
-  sk_set BIGINT[] NOT NULL,
-  new_sk_set BIGINT[],
-  cplane_notified_generation INTEGER NOT NULL,
-  deleted_at timestamptz,
-  PRIMARY KEY(tenant_id, timeline_id)
-);
-CREATE TABLE safekeeper_timeline_pending_ops (
-  sk_id BIGINT NOT NULL,
-  tenant_id VARCHAR NOT NULL,
-  timeline_id VARCHAR NOT NULL,
-  generation INTEGER NOT NULL,
-  op_kind VARCHAR NOT NULL,
-  PRIMARY KEY(tenant_id, timeline_id, sk_id)
-);

storage_controller/migrations/2025-02-28-141741_safekeeper_use_https/down.sql

@@ -1 +0,0 @@
-ALTER TABLE safekeepers DROP https_port;

storage_controller/migrations/2025-02-28-141741_safekeeper_use_https/up.sql

@@ -1 +0,0 @@
-ALTER TABLE safekeepers ADD https_port INTEGER;

storage_controller/migrations/2025-03-18-103700_timeline_imports/down.sql

@@ -1 +0,0 @@
-DROP TABLE timeline_imports;

storage_controller/migrations/2025-03-18-103700_timeline_imports/up.sql

@@ -1,6 +0,0 @@
-CREATE TABLE timeline_imports (
-  tenant_id VARCHAR NOT NULL,
-  timeline_id VARCHAR NOT NULL,
-  shard_statuses JSONB NOT NULL,
-  PRIMARY KEY(tenant_id, timeline_id)
-);

storage_controller/migrations/2025-06-01-201442_add_lifecycle_to_nodes/down.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes DROP COLUMN lifecycle;

storage_controller/migrations/2025-06-01-201442_add_lifecycle_to_nodes/up.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes ADD COLUMN lifecycle VARCHAR NOT NULL DEFAULT 'active';

storage_controller/migrations/2025-06-17-082247_pageserver_grpc_addr/down.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes DROP listen_grpc_addr, listen_grpc_port;

storage_controller/migrations/2025-06-17-082247_pageserver_grpc_addr/up.sql

@@ -1 +0,0 @@
-ALTER TABLE nodes ADD listen_grpc_addr VARCHAR NULL, ADD listen_grpc_port INTEGER NULL;

storage_controller/migrations/2025-07-02-170751_safekeeper_default_no_pause/down.sql

@@ -1 +0,0 @@
-ALTER TABLE safekeepers ALTER COLUMN scheduling_policy SET DEFAULT 'pause';

storage_controller/migrations/2025-07-02-170751_safekeeper_default_no_pause/up.sql

@@ -1 +0,0 @@
-ALTER TABLE safekeepers ALTER COLUMN scheduling_policy SET DEFAULT 'activating';

storage_controller/migrations/2025-07-08-114340_sk_set_notified_generation/down.sql

@@ -1 +0,0 @@
-ALTER TABLE timelines DROP sk_set_notified_generation;

storage_controller/migrations/2025-07-08-114340_sk_set_notified_generation/up.sql

@@ -1 +0,0 @@
-ALTER TABLE timelines ADD sk_set_notified_generation INTEGER NOT NULL DEFAULT 1;

storage_controller/migrations/2025-07-17-000001_hadron_safekeepers/down.sql

@@ -1,2 +0,0 @@
-DROP TABLE hadron_safekeepers;
-DROP TABLE hadron_timeline_safekeepers;

storage_controller/migrations/2025-07-17-000001_hadron_safekeepers/up.sql

@@ -1,17 +0,0 @@
--- hadron_safekeepers keep track of all Safe Keeper nodes that exist in the system.
--- Upon startup, each Safe Keeper reaches out to the hadron cluster coordinator to register its node ID and listen addresses.
-
-CREATE TABLE hadron_safekeepers (
-  sk_node_id BIGINT PRIMARY KEY NOT NULL,
-  listen_http_addr VARCHAR NOT NULL,
-  listen_http_port INTEGER NOT NULL,
-  listen_pg_addr VARCHAR NOT NULL,
-  listen_pg_port INTEGER NOT NULL
-);
-
-CREATE TABLE hadron_timeline_safekeepers (
-  timeline_id VARCHAR NOT NULL,
-  sk_node_id BIGINT NOT NULL,
-  legacy_endpoint_id UUID DEFAULT NULL,
-  PRIMARY KEY(timeline_id, sk_node_id)
-);

storage_controller/migrations/2025-07-31-115100_squash_migrations/down.sql

@@ -0,0 +1,10 @@
+DROP TABLE IF EXISTS timelines CASCADE;
+DROP TABLE IF EXISTS timeline_imports CASCADE;
+DROP TABLE IF EXISTS tenant_shards CASCADE;
+DROP TABLE IF EXISTS safekeepers CASCADE;
+DROP TABLE IF EXISTS safekeeper_timeline_pending_ops CASCADE;
+DROP TABLE IF EXISTS nodes CASCADE;
+DROP TABLE IF EXISTS metadata_health CASCADE;
+DROP TABLE IF EXISTS hadron_timeline_safekeepers CASCADE;
+DROP TABLE IF EXISTS hadron_safekeepers CASCADE;
+DROP TABLE IF EXISTS controllers CASCADE;

storage_controller/migrations/2025-07-31-115100_squash_migrations/up.sql

@@ -0,0 +1,112 @@
+/*
+ * This migration squashes all previous migrations into a single one.
+ * It will be applied in two cases:
+ * 1. Spinning up a new region. There's no previous migrations apart from the diesel setup in this case
+ * 2. For existing regions.
+ *
+ * Hence, this migration does nothing if the schema is already up to date.
+ */
+
+CREATE TABLE IF NOT EXISTS controllers (
+    address character varying NOT NULL,
+    started_at timestamp with time zone NOT NULL,
+    PRIMARY KEY(address, started_at)
+);
+
+CREATE TABLE IF NOT EXISTS hadron_safekeepers (
+    sk_node_id bigint PRIMARY KEY NOT NULL,
+    listen_http_addr character varying NOT NULL,
+    listen_http_port integer NOT NULL,
+    listen_pg_addr character varying NOT NULL,
+    listen_pg_port integer NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS hadron_timeline_safekeepers (
+    timeline_id character varying NOT NULL,
+    sk_node_id bigint NOT NULL,
+    legacy_endpoint_id uuid,
+    PRIMARY KEY(timeline_id, sk_node_id)
+);
+
+CREATE TABLE IF NOT EXISTS tenant_shards (
+    tenant_id character varying NOT NULL,
+    shard_number integer NOT NULL,
+    shard_count integer NOT NULL,
+    PRIMARY KEY(tenant_id, shard_number, shard_count),
+    shard_stripe_size integer NOT NULL,
+    generation integer,
+    generation_pageserver bigint,
+    placement_policy character varying NOT NULL,
+    splitting smallint NOT NULL,
+    config text NOT NULL,
+    scheduling_policy character varying DEFAULT '"Active"'::character varying NOT NULL,
+    preferred_az_id character varying
+);
+
+CREATE INDEX IF NOT EXISTS tenant_shards_tenant_id ON tenant_shards USING btree (tenant_id);
+
+CREATE TABLE IF NOT EXISTS metadata_health (
+    tenant_id character varying NOT NULL,
+    shard_number integer NOT NULL,
+    shard_count integer NOT NULL,
+    PRIMARY KEY(tenant_id, shard_number, shard_count),
+    -- Rely on cascade behavior for delete
+    FOREIGN KEY(tenant_id, shard_number, shard_count) REFERENCES tenant_shards ON DELETE CASCADE,
+    healthy boolean DEFAULT true NOT NULL,
+    last_scrubbed_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS nodes (
+    node_id bigint PRIMARY KEY NOT NULL,
+    scheduling_policy character varying NOT NULL,
+    listen_http_addr character varying NOT NULL,
+    listen_http_port integer NOT NULL,
+    listen_pg_addr character varying NOT NULL,
+    listen_pg_port integer NOT NULL,
+    availability_zone_id character varying NOT NULL,
+    listen_https_port integer,
+    lifecycle character varying DEFAULT 'active'::character varying NOT NULL,
+    listen_grpc_addr character varying,
+    listen_grpc_port integer
+);
+
+CREATE TABLE IF NOT EXISTS safekeeper_timeline_pending_ops (
+    sk_id bigint NOT NULL,
+    tenant_id character varying NOT NULL,
+    timeline_id character varying NOT NULL,
+    generation integer NOT NULL,
+    op_kind character varying NOT NULL,
+    PRIMARY KEY(tenant_id, timeline_id, sk_id)
+);
+
+CREATE TABLE IF NOT EXISTS safekeepers (
+    id bigint PRIMARY KEY NOT NULL,
+    region_id text NOT NULL,
+    version bigint NOT NULL,
+    host text NOT NULL,
+    port integer NOT NULL,
+    http_port integer NOT NULL,
+    availability_zone_id text NOT NULL,
+    scheduling_policy character varying DEFAULT 'activating'::character varying NOT NULL,
+    https_port integer
+);
+
+CREATE TABLE IF NOT EXISTS timeline_imports (
+    tenant_id character varying NOT NULL,
+    timeline_id character varying NOT NULL,
+    shard_statuses jsonb NOT NULL,
+    PRIMARY KEY(tenant_id, timeline_id)
+);
+
+CREATE TABLE IF NOT EXISTS timelines (
+    tenant_id character varying NOT NULL,
+    timeline_id character varying NOT NULL,
+    start_lsn pg_lsn NOT NULL,
+    generation integer NOT NULL,
+    sk_set bigint[] NOT NULL,
+    new_sk_set bigint[],
+    cplane_notified_generation integer NOT NULL,
+    deleted_at timestamp with time zone,
+    sk_set_notified_generation integer DEFAULT 1 NOT NULL,
+    PRIMARY KEY(tenant_id, timeline_id)
+);