README.md

---
title: Remote Postgres
emoji: 🐘
colorFrom: indigo
colorTo: blue
sdk: docker
app_port: 7860
pinned: false
---

# Remote Postgres on Hugging Face Spaces

A single Docker container that runs **PostgreSQL** + a small **web UI** that hands you a
**public connection URL** to drop into any demo project that needs a Postgres database.

Because Hugging Face Spaces only exposes one HTTP port (no raw TCP), the container opens an
outbound **TCP tunnel** so Postgres is reachable from anywhere with a normal `postgresql://…`
URL and any client/ORM. The default tunnel is **[bore](https://github.com/ekzhang/bore)** —
open-source, **no signup, no token**. Set `NGROK_AUTHTOKEN` to use ngrok instead.

> ⚠️ **Throwaway by default.** The public URL changes on every restart. Data is **backed up**
> (see Persistence) but treat the cluster itself as disposable.

---

## Deploy (5 minutes)

1. **Create a Space** → New Space → **SDK: Docker** → **Visibility: Public** (so keep-alive can
   reach it; the UI is still password-locked).
2. **Push these files** to the Space repo (or upload them in the web UI):
   `Dockerfile`, `start.sh`, `README.md`, and the `app/` folder.
3. **Add Space secrets** (Settings → *Variables and secrets*):
   | Secret | Required | Purpose |
   |---|---|---|
   | `APP_PASSWORD` | recommended | Locks the web UI (Basic auth, user `admin`) so only you see the URL |
   | `HF_TOKEN` + `HF_BACKUP_REPO` | for backups | Off-Space dumps to a private HF Dataset (see Persistence) |
   | `POSTGRES_PASSWORD` | recommended | Fixed DB password (otherwise one is generated per boot) |
   | `NGROK_AUTHTOKEN` | optional | Use ngrok instead of bore for the tunnel |
   | `POSTGRES_USER` / `POSTGRES_DB` | optional | Defaults: `demo` / `demo` |
4. The Space builds and starts. Open it → the UI shows your **connection URL** + a Copy button.

## Use it

Open the Space, copy the `postgresql://…` URL, and use it anywhere:

```bash
psql "postgresql://demo:PASSWORD@bore.pub:26134/demo"
```

```python
import psycopg
conn = psycopg.connect("postgresql://demo:PASSWORD@bore.pub:26134/demo")
```

Works the same with SQLAlchemy, Prisma, Drizzle, node-postgres, etc.

## Endpoints

- `/` — **Gradio uptime-watcher dashboard**: live Postgres/tunnel status, container uptime,
  availability %, recent-checks table, keep-alive counter, and the copyable connection URL.
  Login-protected (user `admin`) when `APP_PASSWORD` is set.
- `/api/connection` — JSON with the URL + fields (also protected).
- `/keepalive?src=<label>` — open; bumps the keep-alive counter and logs the hit. Hit by the
  in-container loop (`src=self`) and the external cron (`src=github-actions`).
- `/health` — open status check: `{"status","postgres","tunnel","uptime"}`.

## Don't lose the data (persistence)

The container stacks two independent safeguards — use either or both:

1. **Persistent volume (cleanest).** Enable **persistent storage** on the Space
   (Settings → Storage). It mounts a writable `/data`; on boot the container detects it and
   stores the live Postgres cluster at `/data/pgdata`, so data survives restarts as-is. Zero
   extra config. (Paid HF add-on.)
2. **Backups + auto-restore (works on free tier).** Every `BACKUP_INTERVAL_MIN` minutes the
   DB is `pg_dump`ed to `BACKUP_DIR`, and — if you set `HF_TOKEN` + `HF_BACKUP_REPO` — mirrored
   to a **private HF Dataset repo** (durable off-Space). On a fresh boot the container
   **auto-restores the latest dump**, so even a wiped ephemeral Space comes back with your data
   (you lose at most the last interval). There's also a **Back up now** button on the dashboard.

   To enable off-Space backups, add Space secrets:
   | Secret | Example | Purpose |
   |---|---|---|
   | `HF_TOKEN` | `hf_xxx` (write) | Lets the Space push/pull dumps — [create one](https://huggingface.co/settings/tokens) |
   | `HF_BACKUP_REPO` | `your-name/pg-backups` | Private dataset repo (auto-created) holding the dumps |

   Optional: `BACKUP_INTERVAL_MIN` (default 30), `BACKUP_KEEP` (default 24 local dumps).

> Recommended combo for "I don't want to risk it" on free tier: **HF Dataset backups every
> 15–30 min** + the external hourly keep-alive below. Data is safe across restarts; only the
> ngrok URL still rotates (re-copy it from the dashboard after a restart).

## Keeping the Space awake

Free Spaces sleep after **48h with no HTTP traffic to the web app**. Your Postgres queries go
through ngrok and do **not** count — only requests to this app's web port do. Two layers keep
it awake:

1. **In-container self-ping (automatic).** A loop hits `https://$SPACE_HOST/keepalive` every
   20 min and logs each hit. Keeps the Space from ever going idle *while the container runs* —
   but it cannot wake a Space that already slept (it's asleep too).
2. **External hourly cron (recommended).** An outside request also **wakes** a slept Space.
   Pick one:
   - **GitHub Actions** — [`.github/workflows/keepalive.yml`](.github/workflows/keepalive.yml)
     is included. Push this repo to GitHub, add a repo secret **`SPACE_URL`** =
     `https://<your-space>.hf.space`, and it pings every hour. (Note: GitHub disables scheduled
     workflows after 60 days with no commits — push occasionally, or use the option below.)
   - **No-code:** [cron-job.org](https://cron-job.org) or [UptimeRobot](https://uptimerobot.com)
     → GET `https://<your-space>.hf.space/keepalive` every hour. Truly set-and-forget.

You can watch the **Keep-alive hits** counter on the dashboard to confirm pings are landing.

> A sleep/wake or any rebuild still wipes data and rotates the URL — keep-alive only keeps the
> Space *running*; it does not make the data or the URL permanent.

## Keep it secure

- Set the Space to **Private** and set **`APP_PASSWORD`** so the URL isn't world-readable.
- The DB itself is internet-exposed via the tunnel — anyone with the URL **and** password can
  connect. Use a strong `POSTGRES_PASSWORD` and treat the data as disposable.

## Local test

```bash
docker build -t remote-pg .
docker run -p 7860:7860 \
  -e NGROK_AUTHTOKEN=your_token \
  -e APP_PASSWORD=letmein \
  remote-pg
# open http://localhost:7860
```