136 lines
4.5 KiB
Markdown
136 lines
4.5 KiB
Markdown
|
# Homelab Postgres (CT 102) — Deck Hearth
|
|||
|
|
|
|||
|
|
Move Deck Hearth off Neon onto the shared **pgvector** Postgres on CT 102
|
|||
|
|
(`192.168.68.102:5432`). No Supabase or Neon bill — storage lives on your
|
|||
|
|
`/apps` ZFS pool.
|
|||
|
|
|
|||
|
|
**Homelab reference:** `axiom-server/proxmox/AGENT-DEPLOY.md` § Shared Services (CT 102)
|
|||
|
|
|
|||
|
|
## Important: Vercel vs homelab
|
|||
|
|
|
|||
|
|
Vercel serverless **cannot reach** `192.168.68.102` on your LAN. Options:
|
|||
|
|
|
|||
|
|
| Hosting | DB on CT 102? |
|
|||
|
|
| --- | --- |
|
|||
|
|
| **Dokploy on CT 112** (recommended) | Yes — app and DB on the same network |
|
|||
|
|
| **Local dev** (Mac on LAN) | Yes |
|
|||
|
|
| **Vercel production** | No — unless you add a tunnel (not recommended for Postgres) |
|
|||
|
|
|
|||
|
|
Plan: run production at `deckhearth.stillwell.cloud` via Dokploy (CT 112),
|
|||
|
|
with `POSTGRES_URL` pointing at CT 102.
|
|||
|
|
|
|||
|
|
CI already uses CT 102 today (`deckhearth_ci` user, migrate job in `.github/workflows/ci.yml`).
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
## Phase 1 — Provision `deckhearth` on CT 102
|
|||
|
|
|
|||
|
|
Init SQL lives in the **axiom-server** repo:
|
|||
|
|
|
|||
|
|
`proxmox/ct102/init/03-deckhearth.sql`
|
|||
|
|
|
|||
|
|
On CT 102 (once), apply manually — initdb.d only runs on first Postgres boot:
|
|||
|
|
|
|||
|
|
```bash
|
|||
|
|
# From axiom-server workspace on your Mac:
|
|||
|
|
./proxmox/scripts/sync.sh push 102
|
|||
|
|
./proxmox/scripts/sync.sh exec 102 "docker exec -i postgres psql -U postgres" < proxmox/ct102/init/03-deckhearth.sql
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**Before running:** edit `03-deckhearth.sql` and set `deckhearth_ci` password to match
|
|||
|
|
GitHub secret `HOMELAB_CI_POSTGRES_PASSWORD` (or rotate both together).
|
|||
|
|
|
|||
|
|
Verify:
|
|||
|
|
|
|||
|
|
```bash
|
|||
|
|
./proxmox/scripts/sync.sh exec 102 "docker exec postgres psql -U postgres -c '\\l deckhearth'"
|
|||
|
|
./proxmox/scripts/sync.sh exec 102 "docker exec postgres psql -U deckhearth -d deckhearth -c 'CREATE EXTENSION IF NOT EXISTS vector;'"
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
## Phase 2 — `.env.local` (Deck Hearth repo)
|
|||
|
|
|
|||
|
|
Homelab uses one direct URL (no pooler):
|
|||
|
|
|
|||
|
|
```bash
|
|||
|
|
POSTGRES_URL=postgresql://deckhearth:YOUR_PASSWORD@192.168.68.102:5432/deckhearth
|
|||
|
|
POSTGRES_URL_DIRECT=postgresql://deckhearth:YOUR_PASSWORD@192.168.68.102:5432/deckhearth
|
|||
|
|
|
|||
|
|
# One-time Neon source for data copy (keep until migration done)
|
|||
|
|
NEON_DATABASE_URL=postgresql://…@…neon.tech/…?sslmode=require
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
Apply schema on the **empty** homelab database:
|
|||
|
|
|
|||
|
|
```bash
|
|||
|
|
npm run migrate up
|
|||
|
|
npm run setup-db # seeds admin@deckhearth.com if ADMIN_INITIAL_PASSWORD set
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
## Phase 3 — Copy data Neon → CT 102
|
|||
|
|
|
|||
|
|
Requires **PostgreSQL 17** `pg_dump` / `pg_restore`. Homebrew PG 14 will fail with a version mismatch — use the `postgres:17` Docker image:
|
|||
|
|
|
|||
|
|
```bash
|
|||
|
|
set -a && source .env.local && set +a
|
|||
|
|
docker run --rm \
|
|||
|
|
-e "SOURCE=${NEON_DATABASE_URL}" \
|
|||
|
|
-e "TARGET=${POSTGRES_URL_DIRECT}" \
|
|||
|
|
-v /tmp/deckhearth-pg-migrate:/dump \
|
|||
|
|
postgres:17 bash -c '
|
|||
|
|
pg_dump "$SOURCE" --format=custom --data-only --no-owner --no-acl -f /dump/neon-data.dump
|
|||
|
|
pg_restore --dbname "$TARGET" --data-only --no-owner --no-acl /dump/neon-data.dump
|
|||
|
|
'
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
Or `npm run migrate-neon-to-homelab` if local `pg_dump` is v17+.
|
|||
|
|
|
|||
|
|
Copies **data only** (schema from Phase 2 migrations). Harmless restore errors: `neon_auth.*`, legacy RBAC tables, duplicate `pgmigrations` rows.
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
## Phase 4 — Deploy app on Dokploy (CT 112)
|
|||
|
|
|
|||
|
|
1. Dokploy (CT 112) → New Application → Git repo — see [`DOKPLOY_DEPLOY.md`](./DOKPLOY_DEPLOY.md)
|
|||
|
|
2. Domain: `http://deckhearth.stillwell.cloud` (Traefik on CT 100 → CT 112)
|
|||
|
|
3. Environment variables:
|
|||
|
|
|
|||
|
|
| Variable | Value |
|
|||
|
|
| --- | --- |
|
|||
|
|
| `POSTGRES_URL` | `postgresql://deckhearth:…@192.168.68.102:5432/deckhearth` |
|
|||
|
|
| `REDIS_URL` | `redis://:…@192.168.68.102:6379/5` |
|
|||
|
|
| `S3_*` | MinIO on CT 102 — see `DOKPLOY_DEPLOY.md` |
|
|||
|
|
| `JWT_SECRET` | (openssl rand -base64 32) |
|
|||
|
|
| `AI_GATEWAY_API_KEY` | Vercel AI Gateway (embeddings + vision) |
|
|||
|
|
| `RESEND_API_KEY` | email (external until self-hosted) |
|
|||
|
|
| `CRON_SECRET` | n8n weekly catalog sync |
|
|||
|
|
|
|||
|
|
4. Deploy and smoke-test login, search, scanner.
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
## Phase 5 — Decommission Neon + Vercel
|
|||
|
|
|
|||
|
|
After Dokploy prod is healthy for 24–48 h, pause/delete the Neon project and disable the Vercel deployment.
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
## Code changes (this convoy)
|
|||
|
|
|
|||
|
|
- `lib/sql.js` — generic Postgres client (`postgres` npm package); LAN = no SSL
|
|||
|
|
- All routes import `lib/sql.js` instead of `@vercel/postgres`
|
|||
|
|
- `npm run migrate` uses `POSTGRES_URL_DIRECT`
|
|||
|
|
- `npm run migrate-neon-to-homelab` — data copy helper
|
|||
|
|
|
|||
|
|
## Troubleshooting
|
|||
|
|
|
|||
|
|
| Symptom | Fix |
|
|||
|
|
| --- | --- |
|
|||
|
|
| `connection refused` to 192.168.68.102 | Mac not on LAN / VPN; CT 102 down |
|
|||
|
|
| `SSL required` against homelab | Fixed in `lib/sql.js` for private IPs |
|
|||
|
|
| `type "vector" does not exist` | Run `CREATE EXTENSION vector` in `deckhearth` DB |
|
|||
|
|
| Vercel preview still on Neon | Retired — CI smoke targets homelab URL |
|