mirror of
https://github.com/MBombeck/HealthLog.git
synced 2026-11-07 04:12:04 +00:00
Marc-André Bombeck
41f2f5ac6c
Going back to Postgres 16 after a move that stopped half-way left the moving marker, and the next start emptied the live 16 database and started a second server on its data directory. db-upgrade now waits for the database service and does nothing unless it runs Postgres 18. Using 16 again after a finished move and then updating started the app on the move-day copy without a word. The marker now records the old database's last checkpoint, and a later start refuses when it moved on; the guide says how to move the current database instead. The disk check leaves room for the first pre-migration dump, and the move check seeds the old database with v1.45's own migrations.
215 lines
10 KiB
Bash
Executable File
215 lines
10 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
# Plays the Postgres 16 -> 18 move in docker-compose.yml end to end, against
|
|
# real containers, the way an install from v1.45 meets it on its first update.
|
|
#
|
|
# 1. Start the database exactly as the v1.45 compose file had it, apply every
|
|
# migration and add rows in a second schema.
|
|
# 2. Start the current compose file's `db-upgrade` and check the move: exit 0,
|
|
# Postgres 18, the same rows and the same migration history.
|
|
# 3. Start it again: nothing to do.
|
|
# 4. Pretend the move was cut short after the restore: the next start must
|
|
# empty the new database and run the move again.
|
|
# 5. Empty the new volume: it must refuse to import the old state again,
|
|
# and move it once more after the marker is removed as the guide says.
|
|
# 6. Going back to 16 after a move that did not finish: db-upgrade leaves
|
|
# the live 16 database alone.
|
|
# 7. Using 16 again after a move, then updating: refused, not the old copy.
|
|
# 8. Updating again as the guide says: the current 16 database moves.
|
|
# 9. A new install with no old volume: nothing to do.
|
|
#
|
|
# Needs Docker and the repo's node_modules (for the migrations). Run it from
|
|
# anywhere in the repo: `bash scripts/compose-db-upgrade-check.sh`.
|
|
set -euo pipefail
|
|
|
|
ROOT=$(git rev-parse --show-toplevel)
|
|
# The last release whose compose file ran Postgres 16.
|
|
OLD_REF=${OLD_REF:-v1.45.0}
|
|
PROJECT=hl-db-upgrade-check
|
|
# A free port unless CHECK_DB_PORT names one, so the check never collides with
|
|
# another stack on the same machine.
|
|
PORT=${CHECK_DB_PORT:-$(node -e "const s=require('node:net').createServer().listen(0,'127.0.0.1',()=>{console.log(s.address().port);s.close()})")}
|
|
WORK=$(mktemp -d)
|
|
|
|
export PRISMA_HIDE_UPDATE_MESSAGE=1
|
|
export POSTGRES_PASSWORD
|
|
POSTGRES_PASSWORD=$(openssl rand -hex 16)
|
|
# The app service is never started here, but compose reads its environment.
|
|
export ENCRYPTION_KEY API_TOKEN_HMAC_KEY
|
|
ENCRYPTION_KEY=$(openssl rand -hex 32)
|
|
API_TOKEN_HMAC_KEY=$(openssl rand -hex 32)
|
|
|
|
git -C "$ROOT" show "$OLD_REF:docker-compose.yml" >"$WORK/old.yml"
|
|
cat >"$WORK/old-port.yml" <<EOF
|
|
services:
|
|
db:
|
|
ports:
|
|
- "127.0.0.1:$PORT:5432"
|
|
EOF
|
|
|
|
old() { docker compose -p "$PROJECT" -f "$WORK/old.yml" -f "$WORK/old-port.yml" "$@"; }
|
|
new() { docker compose -p "$PROJECT" -f "$ROOT/docker-compose.yml" "$@"; }
|
|
down_all() {
|
|
new down -v --remove-orphans >/dev/null 2>&1 || true
|
|
old down -v --remove-orphans >/dev/null 2>&1 || true
|
|
}
|
|
trap 'down_all; rm -rf "$WORK"' EXIT
|
|
|
|
fail() {
|
|
echo "FAIL: $*" >&2
|
|
docker logs healthlog-db-upgrade 2>&1 | tail -40 >&2 || true
|
|
exit 1
|
|
}
|
|
step() { echo "== $*"; }
|
|
sql() { docker exec healthlog-db psql -U healthlog -d healthlog -v ON_ERROR_STOP=1 -Atqc "$1"; }
|
|
# Starts db-upgrade (and the database it waits for), waits for it to exit and
|
|
# prints its exit code.
|
|
run_upgrade() {
|
|
new up -d db-upgrade >/dev/null 2>&1 || true
|
|
docker wait healthlog-db-upgrade
|
|
}
|
|
upgrade_log() { docker logs healthlog-db-upgrade 2>&1 | grep '^db-upgrade:' || true; }
|
|
|
|
# The container can report healthy before its published port forwards (Docker
|
|
# Desktop and OrbStack set the forward up a moment later), and a migrate
|
|
# started in that gap failed with "Can't reach database server". So wait
|
|
# until the port answers, for at most thirty seconds, and retry the migrate
|
|
# within the same bound.
|
|
wait_for_port() {
|
|
local i=0
|
|
until node -e "
|
|
const s = require('node:net').connect(Number(process.argv[1]), '127.0.0.1');
|
|
s.on('connect', () => { s.end(); process.exit(0); });
|
|
s.on('error', () => process.exit(1));
|
|
" "$PORT" 2>/dev/null; do
|
|
i=$((i + 1))
|
|
[ "$i" -lt 30 ] || return 1
|
|
sleep 1
|
|
done
|
|
}
|
|
migrate_old() {
|
|
local started=$SECONDS
|
|
until DATABASE_URL="postgresql://healthlog:$POSTGRES_PASSWORD@127.0.0.1:$PORT/healthlog?schema=public" \
|
|
node "$ROOT/node_modules/prisma/build/index.js" migrate deploy --schema "$WORK/old-prisma/prisma/schema.prisma" >"$WORK/migrate.log" 2>&1; do
|
|
if [ $((SECONDS - started)) -ge 30 ] || ! grep -q "P1001" "$WORK/migrate.log"; then
|
|
cat "$WORK/migrate.log" >&2
|
|
return 1
|
|
fi
|
|
sleep 1
|
|
done
|
|
}
|
|
|
|
# The v1.45 schema, migrated with v1.45's own migrations.
|
|
mkdir -p "$WORK/old-prisma"
|
|
git -C "$ROOT" archive "$OLD_REF" prisma | tar -x -C "$WORK/old-prisma"
|
|
# The current compose file with its database pointed back at Postgres 16, the
|
|
# way docs/ops/postgres-major-upgrade.md "Going back" describes it.
|
|
sed -e 's|image: postgres:18\.[0-9]*-alpine|image: postgres:16.15-alpine|' \
|
|
-e 's|- postgres-data:/var/lib/postgresql$|- pgdata:/var/lib/postgresql/data|' \
|
|
"$ROOT/docker-compose.yml" >"$WORK/back.yml"
|
|
grep -q 'image: postgres:16.15-alpine' "$WORK/back.yml" && grep -q -- '- pgdata:/var/lib/postgresql/data' "$WORK/back.yml" ||
|
|
{ echo "FAIL: could not derive the going-back compose file" >&2; exit 1; }
|
|
back() { docker compose -p "$PROJECT" -f "$WORK/back.yml" "$@"; }
|
|
|
|
down_all
|
|
step "a v1.45 database on Postgres 16"
|
|
old up -d --wait db >"$WORK/up.log" 2>&1 || { cat "$WORK/up.log" >&2; fail "the v1.45 database did not start"; }
|
|
wait_for_port || fail "port $PORT did not answer within thirty seconds"
|
|
migrate_old || fail "the migrations did not apply to the v1.45 database"
|
|
old exec -T db psql -U healthlog -d healthlog -v ON_ERROR_STOP=1 -q <<'SQL'
|
|
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
|
|
CREATE SCHEMA check_probe;
|
|
CREATE TABLE check_probe.rows AS
|
|
SELECT g AS id, md5(g::text) AS value FROM generate_series(1, 50000) AS g;
|
|
SQL
|
|
probe() {
|
|
"$@" psql -U healthlog -d healthlog -Atqc \
|
|
"SELECT md5(string_agg(value, ',' ORDER BY id)) FROM check_probe.rows"
|
|
}
|
|
migrations() {
|
|
"$@" psql -U healthlog -d healthlog -Atqc "SELECT count(*) FROM _prisma_migrations"
|
|
}
|
|
PROBE=$(probe old exec -T db)
|
|
MIGRATIONS=$(migrations old exec -T db)
|
|
[ "$MIGRATIONS" -gt 0 ] || fail "the migrations did not apply to the old database"
|
|
old stop db >/dev/null 2>&1
|
|
old rm -f db >/dev/null 2>&1
|
|
|
|
step "first start of the current compose file moves it"
|
|
CODE=$(run_upgrade)
|
|
upgrade_log
|
|
[ "$CODE" = 0 ] || fail "db-upgrade exited $CODE on the move"
|
|
upgrade_log | grep -q 'tables arrived with the same row counts' || fail "the move did not report its row-count check"
|
|
VERSION=$(sql "SHOW server_version_num")
|
|
[ "$VERSION" -ge 180000 ] || fail "the database runs $VERSION, expected Postgres 18"
|
|
[ "$(probe docker exec healthlog-db)" = "$PROBE" ] || fail "the probe rows differ after the move"
|
|
[ "$(migrations docker exec healthlog-db)" = "$MIGRATIONS" ] || fail "the migration history differs after the move"
|
|
[ "$(sql "SELECT count(*) FROM pg_stats WHERE schemaname = 'check_probe'")" -gt 0 ] ||
|
|
fail "the moved tables have no planner statistics"
|
|
|
|
step "second start: nothing to do"
|
|
CODE=$(run_upgrade)
|
|
[ "$CODE" = 0 ] || fail "db-upgrade exited $CODE on a start after the move"
|
|
upgrade_log | tail -1 | grep -q 'nothing to do' || fail "the second start did not say there was nothing to do"
|
|
|
|
step "a move cut short after the restore runs again from scratch"
|
|
new down >/dev/null 2>&1
|
|
docker run --rm -v "${PROJECT}_pgdata:/old" postgres:16.15-alpine \
|
|
mv /old/HEALTHLOG_MOVED_TO_POSTGRES_18 /old/HEALTHLOG_MOVING_TO_POSTGRES_18
|
|
CODE=$(run_upgrade)
|
|
[ "$CODE" = 0 ] || fail "db-upgrade exited $CODE when resuming a cut-short move"
|
|
upgrade_log | grep -q 'did not finish' || fail "the resumed move did not say it starts again"
|
|
[ "$(probe docker exec healthlog-db)" = "$PROBE" ] || fail "the probe rows differ after the resumed move"
|
|
|
|
step "an emptied Postgres 18 volume is not refilled from the old state"
|
|
new down >/dev/null 2>&1
|
|
docker volume rm "${PROJECT}_postgres-data" >/dev/null
|
|
CODE=$(run_upgrade)
|
|
[ "$CODE" = 1 ] || fail "db-upgrade exited $CODE with the moved marker and an empty database, expected 1"
|
|
upgrade_log | grep -q 'Not importing the old state again' || fail "the refusal did not explain itself"
|
|
|
|
step "starting over as the guide says brings the moved state back"
|
|
new run --rm --entrypoint rm db-upgrade /var/lib/postgresql/data/HEALTHLOG_MOVED_TO_POSTGRES_18 >/dev/null 2>&1
|
|
CODE=$(run_upgrade)
|
|
[ "$CODE" = 0 ] || fail "db-upgrade exited $CODE after the marker was removed"
|
|
[ "$(probe docker exec healthlog-db)" = "$PROBE" ] || fail "the probe rows differ after starting over"
|
|
|
|
step "going back to 16 after a move that did not finish leaves 16 alone"
|
|
new down >/dev/null 2>&1
|
|
docker volume rm "${PROJECT}_postgres-data" >/dev/null
|
|
docker run --rm -v "${PROJECT}_pgdata:/old" postgres:16.15-alpine \
|
|
mv /old/HEALTHLOG_MOVED_TO_POSTGRES_18 /old/HEALTHLOG_MOVING_TO_POSTGRES_18
|
|
back up -d db-upgrade >/dev/null 2>&1 || true
|
|
CODE=$(docker wait healthlog-db-upgrade)
|
|
upgrade_log
|
|
[ "$CODE" = 0 ] || fail "db-upgrade exited $CODE beside a Postgres 16 database service"
|
|
upgrade_log | grep -q 'not 18: nothing to move' || fail "db-upgrade did not recognise the Postgres 16 service"
|
|
[ "$(probe docker exec healthlog-db)" = "$PROBE" ] || fail "the live Postgres 16 data changed beside db-upgrade"
|
|
|
|
step "using 16 again after a move, then updating, is refused instead of serving the old copy"
|
|
back down >/dev/null 2>&1
|
|
CODE=$(run_upgrade)
|
|
[ "$CODE" = 0 ] || fail "the move after going back exited $CODE"
|
|
back up -d db >/dev/null 2>&1
|
|
back exec -T db psql -U healthlog -d healthlog -v ON_ERROR_STOP=1 -qc \
|
|
"INSERT INTO check_probe.rows VALUES (50001, 'written on 16 after the move')"
|
|
back down >/dev/null 2>&1
|
|
CODE=$(run_upgrade)
|
|
[ "$CODE" = 1 ] || fail "db-upgrade exited $CODE after 16 was used again, expected 1"
|
|
upgrade_log | grep -q 'changed after it was moved' || fail "the refusal did not say that 16 changed"
|
|
|
|
step "updating again as the guide says moves the current 16 database"
|
|
new down >/dev/null 2>&1
|
|
docker volume rm "${PROJECT}_postgres-data" >/dev/null
|
|
new run --rm --entrypoint rm db-upgrade /var/lib/postgresql/data/HEALTHLOG_MOVED_TO_POSTGRES_18 >/dev/null 2>&1
|
|
CODE=$(run_upgrade)
|
|
[ "$CODE" = 0 ] || fail "db-upgrade exited $CODE when moving the current 16 database"
|
|
[ "$(sql "SELECT count(*) FROM check_probe.rows")" = 50001 ] || fail "the row written on 16 did not arrive"
|
|
|
|
step "a new install: nothing to do"
|
|
new down -v >/dev/null 2>&1
|
|
CODE=$(run_upgrade)
|
|
[ "$CODE" = 0 ] || fail "db-upgrade exited $CODE on a new install"
|
|
upgrade_log | tail -1 | grep -q 'no Postgres 16 database to move' || fail "the new install did not say there was nothing to move"
|
|
|
|
echo "OK: the Postgres 16 -> 18 move behaves in all nine cases"
|