imkrish/remote-postgres

1#!/usr/bin/env bash2set -euo pipefail3 4# Postgres client/server binaries live in /usr/lib/postgresql/<ver>/bin on Debian5export PATH="$(ls -d /usr/lib/postgresql/*/bin 2>/dev/null | head -n1):$PATH"6 7: "${POSTGRES_USER:=demo}"8: "${POSTGRES_DB:=demo}"9: "${PGPORT:=5432}"10: "${APP_PORT:=7860}"11export PGHOST=/tmp   # local clients connect over the unix socket in /tmp (trust auth)12 13# ---- pick data + backup locations ----14# HF persistent storage (if enabled) mounts a writable /data owned by uid 1000.15# When present we keep BOTH the live cluster AND backups there so they survive restarts.16if [ -d /data ] && [ -w /data ]; then17  echo "[start] Persistent /data detected — using it for the cluster and backups."18  export PGDATA="${PGDATA:-/data/pgdata}"19  export BACKUP_DIR="${BACKUP_DIR:-/data/backups}"20else21  echo "[start] No persistent /data — cluster is EPHEMERAL; relying on backup/restore."22  export PGDATA="${PGDATA:-/home/appuser/data/pgdata}"23  export BACKUP_DIR="${BACKUP_DIR:-/home/appuser/backups}"24fi25mkdir -p "$BACKUP_DIR"26 27# Generate a strong password if the user didn't supply one as a Space secret28if [ -z "${POSTGRES_PASSWORD:-}" ]; then29  POSTGRES_PASSWORD="$(python3 -c 'import secrets;print(secrets.token_urlsafe(18))')"30  echo "[start] No POSTGRES_PASSWORD secret set — generated one for this session."31fi32export POSTGRES_PASSWORD POSTGRES_USER POSTGRES_DB PGPORT BACKUP_DIR33 34FRESH_INIT=035 36# ---- initialize the cluster (only when the data dir is empty) ----37if [ ! -s "$PGDATA/PG_VERSION" ]; then38  FRESH_INIT=139  echo "[start] Initializing PostgreSQL cluster at $PGDATA"40  mkdir -p "$PGDATA"41  chmod 700 "$PGDATA"42  PWFILE="$(mktemp)"43  printf '%s' "$POSTGRES_PASSWORD" > "$PWFILE"44  initdb -D "$PGDATA" \45         --auth-host=scram-sha-256 \46         --auth-local=trust \47         -U "$POSTGRES_USER" \48         --pwfile="$PWFILE" >/dev/null49  rm -f "$PWFILE"50 51  {52    echo "listen_addresses = '*'"53    echo "port = $PGPORT"54    echo "unix_socket_directories = '/tmp'"55  } >> "$PGDATA/postgresql.conf"56 57  {58    echo "host all all 0.0.0.0/0 scram-sha-256"59    echo "host all all ::/0      scram-sha-256"60  } >> "$PGDATA/pg_hba.conf"61fi62 63# ---- start postgres ----64echo "[start] Starting PostgreSQL on port $PGPORT"65pg_ctl -D "$PGDATA" -o "-p $PGPORT" -w -l "$PGDATA/server.log" start66 67# ensure the target database exists68if ! psql -p "$PGPORT" -U "$POSTGRES_USER" -d postgres -tAc \69       "SELECT 1 FROM pg_database WHERE datname='$POSTGRES_DB'" | grep -q 1; then70  echo "[start] Creating database '$POSTGRES_DB'"71  createdb -p "$PGPORT" -U "$POSTGRES_USER" "$POSTGRES_DB"72fi73 74# ---- restore the latest backup onto a freshly-initialized cluster ----75# On a fresh boot (ephemeral disk, or first run) pull back the most recent dump so the76# data "survives" restarts. Skipped when the cluster already had data (persistent disk).77if [ "$FRESH_INIT" = "1" ]; then78  echo "[start] Fresh cluster — checking for a backup to restore"79  python /app/backup.py restore || echo "[start] no restore performed"80fi81 82# ---- expose Postgres publicly via a TCP tunnel ----83# Default: bore (no signup). If NGROK_AUTHTOKEN is set, use ngrok instead.84# Whichever runs writes the public endpoint to /tmp/tunnel.json for the web app.85rm -f /tmp/tunnel.json86if [ -n "${NGROK_AUTHTOKEN:-}" ]; then87  echo "[start] Tunnel: ngrok (NGROK_AUTHTOKEN present)"88  ngrok config add-authtoken "$NGROK_AUTHTOKEN" >/dev/null 2>&1 || true89  ngrok tcp "$PGPORT" --log=stdout > "$HOME/ngrok.log" 2>&1 &90  ( set +e91    for _ in $(seq 1 30); do92      pub="$(curl -fsS localhost:4040/api/tunnels 2>/dev/null | grep -oE 'tcp://[^"]+' | head -1)"93      if [ -n "$pub" ]; then94        hp="${pub#tcp://}"95        printf '{"host":"%s","port":"%s","provider":"ngrok"}' "${hp%%:*}" "${hp##*:}" > /tmp/tunnel.json96        echo "[start] ngrok tunnel up at ${hp}"; break97      fi98      sleep 199    done ) &100else101  BORE_HOST="${BORE_HOST:-bore.pub}"102  echo "[start] Tunnel: bore -> ${BORE_HOST} (no token needed)"103  bore local "$PGPORT" --to "$BORE_HOST" > "$HOME/bore.log" 2>&1 &104  ( set +e105    for _ in $(seq 1 30); do106      port="$(grep -aoE 'listening at [^[:space:]]+:[0-9]+' "$HOME/bore.log" 2>/dev/null \107              | grep -oE '[0-9]+$' | tail -1)"108      if [ -n "$port" ]; then109        printf '{"host":"%s","port":"%s","provider":"bore"}' "$BORE_HOST" "$port" > /tmp/tunnel.json110        echo "[start] bore tunnel up at ${BORE_HOST}:${port}"; break111      fi112      sleep 1113    done ) &114fi115 116# ---- keep the Space awake (free Spaces sleep after 48h with NO http traffic) ----117# Postgres/ngrok traffic does NOT count — only requests to this web app do. So we hit118# the PUBLIC url (goes through HF's router => resets the idle timer). SPACE_HOST is119# injected by HF, e.g. user-space.hf.space. Each hit is logged to the Space logs.120if [ -n "${SPACE_HOST:-}" ]; then121  echo "[start] Self keep-alive enabled — pinging https://${SPACE_HOST}/keepalive every 20m"122  ( while true; do123      sleep 1200124      ts="$(date '+%Y-%m-%d %H:%M:%S')"125      code="$(curl -fsS -o /dev/null -w '%{http_code}' \126              "https://${SPACE_HOST}/keepalive?src=self" 2>/dev/null || echo 000)"127      echo "[keepalive] self ${ts} -> ${code}"128    done ) &129fi130 131# ---- start the web UI (foreground; keeps the container alive) ----132echo "[start] Starting web UI on port $APP_PORT"133cd /app134exec uvicorn main:app --host 0.0.0.0 --port "$APP_PORT"135