imkrish/remote-postgres

1---2title: Remote Postgres3emoji: 🐘4colorFrom: indigo5colorTo: blue6sdk: docker7app_port: 78608pinned: false9---10 11# Remote Postgres on Hugging Face Spaces12 13A single Docker container that runs **PostgreSQL** + a small **web UI** that hands you a14**public connection URL** to drop into any demo project that needs a Postgres database.15 16Because Hugging Face Spaces only exposes one HTTP port (no raw TCP), the container opens an17outbound **TCP tunnel** so Postgres is reachable from anywhere with a normal `postgresql://…`18URL and any client/ORM. The default tunnel is **[bore](https://github.com/ekzhang/bore)** —19open-source, **no signup, no token**. Set `NGROK_AUTHTOKEN` to use ngrok instead.20 21> ⚠️ **Throwaway by default.** The public URL changes on every restart. Data is **backed up**22> (see Persistence) but treat the cluster itself as disposable.23 24---25 26## Deploy (5 minutes)27 281. **Create a Space** → New Space → **SDK: Docker** → **Visibility: Public** (so keep-alive can29   reach it; the UI is still password-locked).302. **Push these files** to the Space repo (or upload them in the web UI):31   `Dockerfile`, `start.sh`, `README.md`, and the `app/` folder.323. **Add Space secrets** (Settings → *Variables and secrets*):33   | Secret | Required | Purpose |34   |---|---|---|35   | `APP_PASSWORD` | recommended | Locks the web UI (Basic auth, user `admin`) so only you see the URL |36   | `HF_TOKEN` + `HF_BACKUP_REPO` | for backups | Off-Space dumps to a private HF Dataset (see Persistence) |37   | `POSTGRES_PASSWORD` | recommended | Fixed DB password (otherwise one is generated per boot) |38   | `NGROK_AUTHTOKEN` | optional | Use ngrok instead of bore for the tunnel |39   | `POSTGRES_USER` / `POSTGRES_DB` | optional | Defaults: `demo` / `demo` |404. The Space builds and starts. Open it → the UI shows your **connection URL** + a Copy button.41 42## Use it43 44Open the Space, copy the `postgresql://…` URL, and use it anywhere:45 46```bash47psql "postgresql://demo:PASSWORD@bore.pub:26134/demo"48```49 50```python51import psycopg52conn = psycopg.connect("postgresql://demo:PASSWORD@bore.pub:26134/demo")53```54 55Works the same with SQLAlchemy, Prisma, Drizzle, node-postgres, etc.56 57## Endpoints58 59- `/` — **Gradio uptime-watcher dashboard**: live Postgres/tunnel status, container uptime,60  availability %, recent-checks table, keep-alive counter, and the copyable connection URL.61  Login-protected (user `admin`) when `APP_PASSWORD` is set.62- `/api/connection` — JSON with the URL + fields (also protected).63- `/keepalive?src=<label>` — open; bumps the keep-alive counter and logs the hit. Hit by the64  in-container loop (`src=self`) and the external cron (`src=github-actions`).65- `/health` — open status check: `{"status","postgres","tunnel","uptime"}`.66 67## Don't lose the data (persistence)68 69The container stacks two independent safeguards — use either or both:70 711. **Persistent volume (cleanest).** Enable **persistent storage** on the Space72   (Settings → Storage). It mounts a writable `/data`; on boot the container detects it and73   stores the live Postgres cluster at `/data/pgdata`, so data survives restarts as-is. Zero74   extra config. (Paid HF add-on.)752. **Backups + auto-restore (works on free tier).** Every `BACKUP_INTERVAL_MIN` minutes the76   DB is `pg_dump`ed to `BACKUP_DIR`, and — if you set `HF_TOKEN` + `HF_BACKUP_REPO` — mirrored77   to a **private HF Dataset repo** (durable off-Space). On a fresh boot the container78   **auto-restores the latest dump**, so even a wiped ephemeral Space comes back with your data79   (you lose at most the last interval). There's also a **Back up now** button on the dashboard.80 81   To enable off-Space backups, add Space secrets:82   | Secret | Example | Purpose |83   |---|---|---|84   | `HF_TOKEN` | `hf_xxx` (write) | Lets the Space push/pull dumps — [create one](https://huggingface.co/settings/tokens) |85   | `HF_BACKUP_REPO` | `your-name/pg-backups` | Private dataset repo (auto-created) holding the dumps |86 87   Optional: `BACKUP_INTERVAL_MIN` (default 30), `BACKUP_KEEP` (default 24 local dumps).88 89> Recommended combo for "I don't want to risk it" on free tier: **HF Dataset backups every90> 15–30 min** + the external hourly keep-alive below. Data is safe across restarts; only the91> ngrok URL still rotates (re-copy it from the dashboard after a restart).92 93## Keeping the Space awake94 95Free Spaces sleep after **48h with no HTTP traffic to the web app**. Your Postgres queries go96through ngrok and do **not** count — only requests to this app's web port do. Two layers keep97it awake:98 991. **In-container self-ping (automatic).** A loop hits `https://$SPACE_HOST/keepalive` every100   20 min and logs each hit. Keeps the Space from ever going idle *while the container runs* —101   but it cannot wake a Space that already slept (it's asleep too).1022. **External hourly cron (recommended).** An outside request also **wakes** a slept Space.103   Pick one:104   - **GitHub Actions** — [`.github/workflows/keepalive.yml`](.github/workflows/keepalive.yml)105     is included. Push this repo to GitHub, add a repo secret **`SPACE_URL`** =106     `https://<your-space>.hf.space`, and it pings every hour. (Note: GitHub disables scheduled107     workflows after 60 days with no commits — push occasionally, or use the option below.)108   - **No-code:** [cron-job.org](https://cron-job.org) or [UptimeRobot](https://uptimerobot.com)109     → GET `https://<your-space>.hf.space/keepalive` every hour. Truly set-and-forget.110 111You can watch the **Keep-alive hits** counter on the dashboard to confirm pings are landing.112 113> A sleep/wake or any rebuild still wipes data and rotates the URL — keep-alive only keeps the114> Space *running*; it does not make the data or the URL permanent.115 116## Keep it secure117 118- Set the Space to **Private** and set **`APP_PASSWORD`** so the URL isn't world-readable.119- The DB itself is internet-exposed via the tunnel — anyone with the URL **and** password can120  connect. Use a strong `POSTGRES_PASSWORD` and treat the data as disposable.121 122## Local test123 124```bash125docker build -t remote-pg .126docker run -p 7860:7860 \127  -e NGROK_AUTHTOKEN=your_token \128  -e APP_PASSWORD=letmein \129  remote-pg130# open http://localhost:7860131```132