Files
deploy/remote/database.sh
Christian Manivong 6e3ecb8680
CI / check (pull_request) Failing after 28s
feat: the bundled database on every deploy, and --infra-db to move it (NetOrk/netork#1308)
remote/database.sh runs on the host (over ssh's stdin) after .env and before
broker and engine:

- no bundled database: nothing to do;
- the volume it already uses: plain `up -d --wait`, recreated only on a changed
  image or definition, so a new PostgreSQL minor release arrives with the next
  deploy;
- another volume (a new major version): a normal deploy stops there, nothing
  changed; with --infra-db the engine stops, the old database is counted and
  dumped (kept in ~/netork/backups), the new container comes up on the new
  volume, restore, ANALYZE, counts and Alembic revision must match. The old
  volume stays, and deploying the previous version with --infra-db switches
  back onto it without a restore.

Checked on .50 in reconcile mode: the running postgres:16 container stayed as
it was. README describes the steps and the way back.
2026-10-20 23:15:48 +02:00

116 lines
4.7 KiB
Bash

#!/bin/bash
# database.sh <reconcile|move> <engine service...>: netOrk's bundled PostgreSQL
# on a deploy (NetOrk/netork#1308). deploy.sh runs it on the host, in ~/netork,
# after the compose files and .env are in place and before broker and engine.
#
# - No bundled database (external PostgreSQL): nothing to do.
# - Compose names the volume the container uses: plain `up -d --wait`, which
# recreates only on a changed image or definition (a new minor release).
# - Compose names another volume (a new major version):
# - reconcile: stop here, exit 3, nothing changed; that needs --infra-db.
# - move, volume absent: stop the engine, count and dump the old database
# (the dump stays in ~/netork/backups), bring the new container up on the new
# volume, restore, ANALYZE, compare counts and Alembic revision.
# - move, volume present: the way back; stop the engine and switch onto it.
# The old volume always stays.
#
# Env: REGISTRY_HOST, NETORK_VERSION (for the compose files' substitutions).
set -euo pipefail
mode=$1
shift
engine=("$@")
data=/var/lib/postgresql/data
cd ~/netork
compose() {
docker compose --profile local-db -f docker-compose.yml -f docker-compose.registry.yml "$@"
}
db_container() {
docker ps -aq --filter label=com.docker.compose.project=netork \
--filter label=com.docker.compose.service=postgres | head -1
}
up() { compose up -d --no-deps --wait --wait-timeout 300 postgres; }
cid=$(db_container)
if [ -z "$cid" ]; then
echo "no bundled database (external PostgreSQL): nothing to do"
exit 0
fi
current=$(docker inspect "$cid" \
--format "{{range .Mounts}}{{if eq .Destination \"$data\"}}{{.Name}}{{end}}{{end}}")
config=$(compose config --format json | tr -d ' \n\t')
source=$(grep -o "\"source\":\"[^\"]*\",\"target\":\"$data\"" <<<"$config" \
| sed 's/^"source":"\([^"]*\)".*/\1/')
target=$(grep -o "\"$source\":{\"name\":\"[^\"]*\"" <<<"$config" | sed 's/.*"name":"\([^"]*\)"$/\1/' || true)
target=${target:-netork_$source}
if [ "$current" = "$target" ]; then
echo "database stays on $current"
up
exit 0
fi
if [ "$mode" != move ]; then
echo "The database would move from volume $current to $target: a new PostgreSQL major" >&2
echo "version, which only happens by dump and restore. Run deploy.sh --infra-db for" >&2
echo "this host at an announced time. Nothing was changed." >&2
exit 3
fi
if docker volume inspect "$target" >/dev/null 2>&1; then
echo "switching the database from $current onto the existing volume $target;"
echo "its data is as it was when that volume was last in use"
compose stop "${engine[@]}"
up
echo "database now on $target; $current stays"
exit 0
fi
user=$(docker exec "$cid" printenv POSTGRES_USER || echo postgres)
db=$(docker exec "$cid" printenv POSTGRES_DB || echo "$user")
psql_in() { local c=$1; shift; docker exec "$c" psql -U "$user" -d "$db" -XAtq -v ON_ERROR_STOP=1 -c "$@"; }
counts() { # counts <container>: "schema.table=rows" for every table, sorted
local stmt
stmt=$(psql_in "$1" "SELECT string_agg(format('SELECT %L AS t, count(*) AS n FROM %I.%I', schemaname||'.'||relname, schemaname, relname), ' UNION ALL ') FROM pg_stat_user_tables")
psql_in "$1" "SELECT t||'='||n FROM ($stmt) x ORDER BY t"
}
docker start "$cid" >/dev/null
for _ in $(seq 1 60); do docker exec "$cid" pg_isready -U "$user" -q && break; sleep 1; done
echo "stopping the engine: ${engine[*]}"
compose stop "${engine[@]}"
mkdir -p backups
stamp=$(date -u +%Y%m%dT%H%M%SZ)
dump="backups/db-$current-$stamp.dump"
counts "$cid" > "backups/db-$stamp.before"
revision=$(psql_in "$cid" "SELECT version_num FROM alembic_version")
echo "dumping $db from $current to ~/netork/$dump"
docker exec "$cid" pg_dump -U "$user" -Fc "$db" > "$dump"
echo "bringing the database up on $target"
up
new=$(db_container)
echo "restoring into $target"
docker exec -i "$new" pg_restore -U "$user" -d "$db" --exit-on-error --no-owner --role="$user" < "$dump"
psql_in "$new" ANALYZE
counts "$new" > "backups/db-$stamp.after"
back="To go back: deploy the previous netOrk version with deploy.sh --infra-db; it switches onto $current, which holds the data as it was before the move."
if ! cmp -s "backups/db-$stamp.before" "backups/db-$stamp.after"; then
echo "The row counts differ after the restore (backups/db-$stamp.before, .after):" >&2
diff "backups/db-$stamp.before" "backups/db-$stamp.after" | head -20 >&2 || true
echo "$back" >&2
exit 1
fi
if [ "$(psql_in "$new" "SELECT version_num FROM alembic_version")" != "$revision" ]; then
echo "The Alembic revision differs after the restore." >&2
echo "$back" >&2
exit 1
fi
echo "database moved from $current to $target: $(wc -l < "backups/db-$stamp.after") tables, counts equal, revision $revision"
echo "the dump stays at ~/netork/$dump; $current stays as the way back"