Spaces:

LoveLogicAI
/

kanbot-backend

Paused

Jeremy Jones

Recover from corrupted PG cluster: wipe + re-init on start failure

6095970 about 1 month ago

5.68 kB

#!/bin/sh
# Kanbot backend entrypoint for the HF Space.
#
# Two modes:
#   1. DB_HOST set + reachable  → use external Postgres, skip local init.
#   2. otherwise                → init Postgres in $PGDATA, apply schema,
#                                 then run the web server.
#
# In both modes the web server (python -m web.server) is the foreground process.
#
# The container runs as the "postgres" user (UID 1000 in the Dockerfile), so
# initdb / postgres / psql are invoked directly — no `su` (which needs root).

set -e

# Locate the postgres bin dir (versioned path on Debian/Postgres images).
PG_BIN="$(ls -d /usr/lib/postgresql/*/bin 2>/dev/null | head -1)"
if [ -z "$PG_BIN" ]; then PG_BIN="/usr/bin"; fi
export PATH="$PG_BIN:$PATH"

HOST="${DB_HOST:-}"
PORT="${DB_PORT:-5432}"
export PGUSER="${PGUSER:-kanbot}"
export PGPASSWORD="${PGPASSWORD:-kanbot}"
export PGDATABASE="${PGDATABASE:-kanbot}"
WEB_PORT="${KANBOT_WEB_PORT:-7860}"
PGDATA="${PGDATA:-/data/pgdata}"

# Make sure HOME is writable (the Dockerfile sets it to /var/lib/postgresql,
# which may be read-only under the HF runtime mount).
export HOME="${PGDATA}"

# --- wait helper ----------------------------------------------------------- #
wait_for_host() {
    echo "[kanbot] waiting for ${1}:${2} ..."
    for i in $(seq 1 60); do
        if nc -z -w 2 "${1}" "${2}" 2>/dev/null; then
            echo "[kanbot] ${1}:${2} is up"
            return 0
        fi
        sleep 1
    done
    echo "[kanbot] ERROR: ${1}:${2} did not become reachable" >&2
    return 1
}

# --- mode 1: external Postgres -------------------------------------------- #
if [ -n "$HOST" ]; then
    export DATABASE_URL="postgresql://${PGUSER}:${PGPASSWORD}@${HOST}:${PORT}/${PGDATABASE}"
    wait_for_host "$HOST" "$PORT" || exit 1
    echo "[kanbot] using external Postgres at ${HOST}:${PORT}"
    exec python -m web.server
fi

# --- mode 2: local Postgres in /data -------------------------------------- #
export DATABASE_URL="postgresql://${PGUSER}:${PGPASSWORD}@127.0.0.1:${PORT}/${PGDATABASE}"

# Start Postgres directly (not via pg_ctl — pg_ctl start -w fails under the
# HF runtime with an empty server.log). Run in background, capture stderr.
start_postgres() {
    rm -f "${PGDATA}/postmaster.pid" 2>/dev/null || true
    # Postgres requires the data dir to be 0700 (owner-only). The HF bucket
    # mount may land with looser perms after a previous failed run.
    chmod 700 "${PGDATA}" 2>/dev/null || true
    postgres -D "${PGDATA}" >"${PGDATA}/server.log" 2>&1 &
    echo $!
}

# Idempotent init: PG_VERSION is the definitive marker that initdb completed.
# A previous failed run can leave a partial/corrupted cluster (Postgres then
# dies with "could not open directory ..."), so wipe and re-init when it is
# missing or the cluster is clearly broken.
init_cluster() {
    echo "[kanbot] initialising Postgres in ${PGDATA} ..."
    rm -rf "${PGDATA:?}/"* 2>/dev/null || true
    mkdir -p "${PGDATA}"
    initdb -D "${PGDATA}" -U "${PGUSER}" --auth=trust
    # Allow TCP connections from localhost with trust auth.
    echo "host all all 127.0.0.1/32 trust" >> "${PGDATA}/pg_hba.conf"
    echo "listen_addresses = 'localhost'" >> "${PGDATA}/postgresql.conf"
}

if [ ! -f "${PGDATA}/PG_VERSION" ]; then
    init_cluster
fi

# Try to start; if the persisted cluster is corrupted (a previous failed run
# left it half-initialised), wipe it and re-init before trying again.
echo "[kanbot] starting Postgres ..."
POSTGRES_PID=$(start_postgres)
echo "[kanbot] postgres pid=${POSTGRES_PID}"
if ! wait_for_host 127.0.0.1 "$PORT"; then
    echo "[kanbot] Postgres failed to start. server.log:" >&2
    cat "${PGDATA}/server.log" >&2
    echo "[kanbot] wiping corrupted cluster and re-initialising ..." >&2
    kill "${POSTGRES_PID}" 2>/dev/null || true
    wait "${POSTGRES_PID}" 2>/dev/null || true
    init_cluster
    POSTGRES_PID=$(start_postgres)
    echo "[kanbot] postgres pid=${POSTGRES_PID}"
    if ! wait_for_host 127.0.0.1 "$PORT"; then
        echo "[kanbot] Postgres failed to start after re-init. server.log:" >&2
        cat "${PGDATA}/server.log" >&2
        exit 1
    fi
fi

# Create the kanbot user password + database (idempotent). initdb -U kanbot
# makes 'kanbot' the superuser, so we connect as ${PGUSER} on the default
# template1 database (the 'postgres' database does not exist in this cluster).
echo "[kanbot] ensuring user '${PGUSER}' and database '${PGDATABASE}' ..."
psql -d template1 -c "ALTER USER ${PGUSER} WITH PASSWORD '${PGPASSWORD}';" || true
if ! psql -d template1 -tc "SELECT 1 FROM pg_database WHERE datname='${PGDATABASE}'" | grep -q 1; then
    psql -d template1 -c "CREATE DATABASE ${PGDATABASE} OWNER ${PGUSER};"
fi

# Apply the schema (idempotent — IF NOT EXISTS / DROP TRIGGER IF EXISTS).
echo "[kanbot] applying schema ..."
SCHEMA_FILE="${KANBOT_SCHEMA_SQL:-/app/sql/001_schema_quoted.sql}"
psql "${DATABASE_URL}" -f "${SCHEMA_FILE}"

# Seed the board from the Mac Mini snapshot on first boot (when the table is
# empty). The seed file ships in the repo; it is idempotent (ON CONFLICT DO
# NOTHING) so re-runs are safe and a manually-seeded board is never clobbered.
SEED_FILE="${KANBOT_SEED_SQL:-/app/sql/002_seed.sql}"
if [ -f "${SEED_FILE}" ]; then
    N=$(psql "${DATABASE_URL}" -tAc "SELECT count(*) FROM cards" 2>/dev/null || echo 0)
    if [ "${N}" = "0" ]; then
        echo "[kanbot] seeding board from ${SEED_FILE} ..."
        psql "${DATABASE_URL}" -f "${SEED_FILE}"
    else
        echo "[kanbot] board already has ${N} cards; skipping seed"
    fi
fi

echo "[kanbot] local Postgres ready; starting web server on :${WEB_PORT}"
exec python -m web.server

	#!/bin/sh
	# Kanbot backend entrypoint for the HF Space.
	#
	# Two modes:
	# 1. DB_HOST set + reachable → use external Postgres, skip local init.
	# 2. otherwise → init Postgres in $PGDATA, apply schema,
	# then run the web server.
	#
	# In both modes the web server (python -m web.server) is the foreground process.
	#
	# The container runs as the "postgres" user (UID 1000 in the Dockerfile), so
	# initdb / postgres / psql are invoked directly — no `su` (which needs root).

	set -e

	# Locate the postgres bin dir (versioned path on Debian/Postgres images).
	PG_BIN="$(ls -d /usr/lib/postgresql/*/bin 2>/dev/null \| head -1)"
	if [ -z "$PG_BIN" ]; then PG_BIN="/usr/bin"; fi
	export PATH="$PG_BIN:$PATH"

	HOST="${DB_HOST:-}"
	PORT="${DB_PORT:-5432}"
	export PGUSER="${PGUSER:-kanbot}"
	export PGPASSWORD="${PGPASSWORD:-kanbot}"
	export PGDATABASE="${PGDATABASE:-kanbot}"
	WEB_PORT="${KANBOT_WEB_PORT:-7860}"
	PGDATA="${PGDATA:-/data/pgdata}"

	# Make sure HOME is writable (the Dockerfile sets it to /var/lib/postgresql,
	# which may be read-only under the HF runtime mount).
	export HOME="${PGDATA}"

	# --- wait helper ----------------------------------------------------------- #
	wait_for_host() {
	echo "[kanbot] waiting for ${1}:${2} ..."
	for i in $(seq 1 60); do
	if nc -z -w 2 "${1}" "${2}" 2>/dev/null; then
	echo "[kanbot] ${1}:${2} is up"
	return 0
	fi
	sleep 1
	done
	echo "[kanbot] ERROR: ${1}:${2} did not become reachable" >&2
	return 1
	}

	# --- mode 1: external Postgres -------------------------------------------- #
	if [ -n "$HOST" ]; then
	export DATABASE_URL="postgresql://${PGUSER}:${PGPASSWORD}@${HOST}:${PORT}/${PGDATABASE}"
	wait_for_host "$HOST" "$PORT" \|\| exit 1
	echo "[kanbot] using external Postgres at ${HOST}:${PORT}"
	exec python -m web.server
	fi

	# --- mode 2: local Postgres in /data -------------------------------------- #
	export DATABASE_URL="postgresql://${PGUSER}:${PGPASSWORD}@127.0.0.1:${PORT}/${PGDATABASE}"

	# Start Postgres directly (not via pg_ctl — pg_ctl start -w fails under the
	# HF runtime with an empty server.log). Run in background, capture stderr.
	start_postgres() {
	rm -f "${PGDATA}/postmaster.pid" 2>/dev/null \|\| true
	# Postgres requires the data dir to be 0700 (owner-only). The HF bucket
	# mount may land with looser perms after a previous failed run.
	chmod 700 "${PGDATA}" 2>/dev/null \|\| true
	postgres -D "${PGDATA}" >"${PGDATA}/server.log" 2>&1 &
	echo $!
	}

	# Idempotent init: PG_VERSION is the definitive marker that initdb completed.
	# A previous failed run can leave a partial/corrupted cluster (Postgres then
	# dies with "could not open directory ..."), so wipe and re-init when it is
	# missing or the cluster is clearly broken.
	init_cluster() {
	echo "[kanbot] initialising Postgres in ${PGDATA} ..."
	rm -rf "${PGDATA:?}/"* 2>/dev/null \|\| true
	mkdir -p "${PGDATA}"
	initdb -D "${PGDATA}" -U "${PGUSER}" --auth=trust
	# Allow TCP connections from localhost with trust auth.
	echo "host all all 127.0.0.1/32 trust" >> "${PGDATA}/pg_hba.conf"
	echo "listen_addresses = 'localhost'" >> "${PGDATA}/postgresql.conf"
	}

	if [ ! -f "${PGDATA}/PG_VERSION" ]; then
	init_cluster
	fi

	# Try to start; if the persisted cluster is corrupted (a previous failed run
	# left it half-initialised), wipe it and re-init before trying again.
	echo "[kanbot] starting Postgres ..."
	POSTGRES_PID=$(start_postgres)
	echo "[kanbot] postgres pid=${POSTGRES_PID}"
	if ! wait_for_host 127.0.0.1 "$PORT"; then
	echo "[kanbot] Postgres failed to start. server.log:" >&2
	cat "${PGDATA}/server.log" >&2
	echo "[kanbot] wiping corrupted cluster and re-initialising ..." >&2
	kill "${POSTGRES_PID}" 2>/dev/null \|\| true
	wait "${POSTGRES_PID}" 2>/dev/null \|\| true
	init_cluster
	POSTGRES_PID=$(start_postgres)
	echo "[kanbot] postgres pid=${POSTGRES_PID}"
	if ! wait_for_host 127.0.0.1 "$PORT"; then
	echo "[kanbot] Postgres failed to start after re-init. server.log:" >&2
	cat "${PGDATA}/server.log" >&2
	exit 1
	fi
	fi

	# Create the kanbot user password + database (idempotent). initdb -U kanbot
	# makes 'kanbot' the superuser, so we connect as ${PGUSER} on the default
	# template1 database (the 'postgres' database does not exist in this cluster).
	echo "[kanbot] ensuring user '${PGUSER}' and database '${PGDATABASE}' ..."
	psql -d template1 -c "ALTER USER ${PGUSER} WITH PASSWORD '${PGPASSWORD}';" \|\| true
	if ! psql -d template1 -tc "SELECT 1 FROM pg_database WHERE datname='${PGDATABASE}'" \| grep -q 1; then
	psql -d template1 -c "CREATE DATABASE ${PGDATABASE} OWNER ${PGUSER};"
	fi

	# Apply the schema (idempotent — IF NOT EXISTS / DROP TRIGGER IF EXISTS).
	echo "[kanbot] applying schema ..."
	SCHEMA_FILE="${KANBOT_SCHEMA_SQL:-/app/sql/001_schema_quoted.sql}"
	psql "${DATABASE_URL}" -f "${SCHEMA_FILE}"

	# Seed the board from the Mac Mini snapshot on first boot (when the table is
	# empty). The seed file ships in the repo; it is idempotent (ON CONFLICT DO
	# NOTHING) so re-runs are safe and a manually-seeded board is never clobbered.
	SEED_FILE="${KANBOT_SEED_SQL:-/app/sql/002_seed.sql}"
	if [ -f "${SEED_FILE}" ]; then
	N=$(psql "${DATABASE_URL}" -tAc "SELECT count(*) FROM cards" 2>/dev/null \|\| echo 0)
	if [ "${N}" = "0" ]; then
	echo "[kanbot] seeding board from ${SEED_FILE} ..."
	psql "${DATABASE_URL}" -f "${SEED_FILE}"
	else
	echo "[kanbot] board already has ${N} cards; skipping seed"
	fi
	fi

	echo "[kanbot] local Postgres ready; starting web server on :${WEB_PORT}"
	exec python -m web.server