CI / check (pull_request) Failing after 28s
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.
116 lines
4.7 KiB
Bash
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"
|