8000 feat: new flake app runs migrations before starting (#1056) · smilexs4/supabase-postgres@74e3390 · GitHub
[go: up one dir, main page]
Skip to content

Commit 74e3390

Browse files
samrosesoedirgo
samrose
and
soedirgo
authored
feat: new flake app runs migrations before starting (supabase#1056)
* feat: new flake app runs migrations before starting * feat: also include pgbouncer/pg_stat_statements migrations --------- Co-authored-by: Sam Rose <samuel@supabase.io> Co-authored-by: Bobbie Soedirgo <bobbie@soedirgo.dev>
1 parent 04d24e8 commit 74e3390

File tree

4 files changed

+92
-3
lines changed

4 files changed

+92
-3
lines changed

‎flake.nix

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -432,6 +432,27 @@
432432
chmod +x $out/bin/start-postgres-client
433433
'';
434434

435+
# Start a version of the client and runs migrations script on server.
436+
start-client-and-migrate =
437+
let
438+
migrationsDir = ./migrations/db;
439+
postgresqlSchemaSql = ./nix/tools/postgresql_schema.sql;
440+
pgbouncerAuthSchemaSql = ./ansible/files/pgbouncer_config/pgbouncer_auth_schema.sql;
441+
statExtensionSql = ./ansible/files/stat_extension.sql;
442+
in
443+
pkgs.runCommand "start-postgres-client-migrate" { } ''
444+
mkdir -p $out/bin
445+
substitute ${./nix/tools/run-client-migrate.sh.in} $out/bin/start-postgres-client-migrate \
446+
--subst-var-by 'PGSQL_DEFAULT_PORT' '${pgsqlDefaultPort}' \
447+
--subst-var-by 'PGSQL_SUPERUSER' '${pgsqlSuperuser}' \
448+
--subst-var-by 'PSQL15_BINDIR' '${basePackages.psql_15.bin}' \
449+
--subst-var-by 'MIGRATIONS_DIR' '${migrationsDir}' \
450+
--subst-var-by 'POSTGRESQL_SCHEMA_SQL' '${postgresqlSchemaSql}' \
451+
--subst-var-by 'PGBOUNCER_AUTH_SCHEMA_SQL' '${pgbouncerAuthSchemaSql}' \
452+
--subst-var-by 'STAT_EXTENSION_SQL' '${statExtensionSql}'
453+
chmod +x $out/bin/start-postgres-client-migrate
454+
'';
455+
435456
# Migrate between two data directories.
436457
migrate-tool =
437458
let
@@ -535,6 +556,7 @@
535556
{
536557
start-server = mkApp "start-server" "start-postgres-server";
537558
start-client = mkApp "start-client" "start-postgres-client";
559+
start-client-and-migrate = mkApp "start-client-and-migrate" "start-postgres-client-migrate";
538560
start-replica = mkApp "start-replica" "start-postgres-replica";
539561
migration-test = mkApp "migrate-tool" "migrate-postgres";
540562
sync-exts-versions = mkApp "sync-exts-versions" "sync-exts-versions";

‎nix/tests/util/pgsodium_getkey.sh

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,10 @@
1-
# NOTE (aseipp): just use some random key for testing, no need to query
2-
# /dev/urandom. also helps ferrit out other random flukes, perhaps?
1+
#!/bin/bash
32

4-
echo -n 8359dafbba5c05568799c1c24eb6c2fbff497654bc6aa5e9a791c666768875a1
3+
set -euo pipefail
4+
5+
KEY_FILE="${1:-/tmp/pgsodium.key}"
6+
7+
if [[ ! -f "${KEY_FILE}" ]]; then
8+
head -c 32 /dev/urandom | od -A n -t x1 | tr -d ' \n' > "${KEY_FILE}"
9+
fi
10+
cat $KEY_FILE

‎nix/tools/postgresql_schema.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
ALTER DATABASE postgres SET "app.settings.jwt_secret" TO 'my_jwt_secret_which_is_not_so_secret';
2+
ALTER DATABASE postgres SET "app.settings.jwt_exp" TO 3600;
3+
ALTER USER supabase_admin WITH PASSWORD 'postgres';
4+
ALTER USER postgres WITH PASSWORD 'postgres';
5+
ALTER USER authenticator WITH PASSWORD 'postgres';
6+
ALTER USER pgbouncer WITH PASSWORD 'postgres';
7+
ALTER USER supabase_auth_admin WITH PASSWORD 'postgres';
8+
ALTER USER supabase_storage_admin WITH PASSWORD 'postgres';
9+
ALTER USER supabase_replication_admin WITH PASSWORD 'postgres';
10+
ALTER ROLE supabase_read_only_user WITH PASSWORD 'postgres';
11+
ALTER ROLE supabase_admin SET search_path TO "$user",public,auth,extensions;

‎nix/tools/run-client-migrate.sh.in

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
#!/usr/bin/env bash
2+
# shellcheck shell D7AE =bash
3+
4+
[ ! -z "$DEBUG" ] && set -x
5+
6+
# first argument should be '15' or '16' for the version
7+
if [ "$1" == "15" ]; then
8+
echo "Starting client for PSQL 15"
9+
PSQL15=@PSQL15_BINDIR@
10+
BINDIR="$PSQL15"
11+
elif [ "$1" == "16" ]; then
12+
echo "Starting client for PSQL 16"
13+
PSQL16=@PSQL16_BINDIR@
14+
BINDIR="$PSQL16"
15+
elif [ "$1" == "orioledb-16" ]; then
16+
echo "Starting client for PSQL ORIOLEDB 16"
17+
PSQLORIOLEDB16=@PSQLORIOLEDB16_BINDIR@
18+
BINDIR="$PSQLORIOLEDB16"
19+
else
20+
echo "Please provide a valid Postgres version (15, 16, or orioledb-16)"
21+
exit 1
22+
fi
23+
#vars for migration.sh
24+
export PATH=$BINDIR/bin:$PATH
25+
export POSTGRES_DB=postgres
26+
export POSTGRES_HOST=localhost
27+
export POSTGRES_PORT=@PGSQL_DEFAULT_PORT@
28+
PORTNO="${2:-@PGSQL_DEFAULT_PORT@}"
29+
PGSQL_SUPERUSER=@PGSQL_SUPERUSER@
30+
MIGRATIONS_DIR=@MIGRATIONS_DIR@
31+
POSTGRESQL_SCHEMA_SQL=@POSTGRESQL_SCHEMA_SQL@
32+
PGBOUNCER_AUTH_SCHEMA_SQL=@PGBOUNCER_AUTH_SCHEMA_SQL@
33+
STAT_EXTENSION_SQL=@STAT_EXTENSION_SQL@
34+
psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U "$PGSQL_SUPERUSER" -p "$PORTNO" -h localhost -d postgres -f "$PGBOUNCER_AUTH_SCHEMA_SQL"
35+
psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U "$PGSQL_SUPERUSER" -p "$PORTNO" -h localhost -d postgres -f "$STAT_EXTENSION_SQL"
36+
for sql in "$MIGRATIONS_DIR"/init-scripts/*.sql; do
37+
echo "$0: running $sql"
38+
psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U postgres -p "$PORTNO" -h localhost -f "$sql" postgres
39+
done
40+
psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U postgres -p "$PORTNO" -h localhost -c "ALTER USER supabase_admin WITH PASSWORD '$PGPASSWORD'"
41+
# run migrations as super user - postgres user demoted in post-setup
42+
for sql in "$MIGRATIONS_DIR"/migrations/*.sql; do
43+
echo "$0: running $sql"
44+
psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U supabase_admin -p "$PORTNO" -h localhost -f "$sql" postgres
45+
done
46+
psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U supabase_admin -p "$PORTNO" -h localhost -f "$POSTGRESQL_SCHEMA_SQL" postgres
47+
# TODO Do we need to reset stats when running migrations locally?
48+
#psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U supabase_admin -p "$PORTNO" -h localhost -c 'SELECT extensions.pg_stat_statements_reset(); SELECT pg_stat_reset();' postgres || true
49+
50+
exec psql -U "$PGSQL_SUPERUSER" -p "$PORTNO" -h localhost postgres

0 commit comments

Comments
 (0)
0