---
name: shipholm-postgres-app
description: "Deploy an app with its own Postgres database on Shipholm (database=true). Use when the app needs many concurrent writers, several replicas, extensions such as pgvector, or already has Postgres SQL and migrations, e.g. from Supabase."
---

# App with Postgres
Its own Postgres database for apps with many concurrent writers, several replicas or SQL you already have.

## When this fits
Several replicas, heavy concurrent writes, extensions (pgvector, PostGIS), or a schema from
Supabase you want to keep nearly as is. Otherwise `shipholm-sqlite-app` is lighter: a Postgres
instance runs all the time, also while the app sleeps.

## Steps
1. `create_app(app=..., source_repo=..., database=true)`, or later `update_app(database=true)`.
   The database gets 2 GiB by default (`db_size`). With `previews=true`, set `database=true` and
   the secrets on the app before the first pull request is built: a preview copies them when it
   is built.
2. The app reads `DATABASE_URL`. The platform sets user, password and host, and the database is
   called `app`. Never put the URL in the repository or a log.
3. Migrations at start: keep a `schema_migrations` table and apply the missing files in order, each
   in a transaction. Then a restart, a second replica and a preview all end up on the same schema.
4. Health check: `health_path="/healthz"`, answering 200 after `SELECT 1`. The app then waits
   for its database instead of taking traffic with a broken connection. When you move an
   existing app to a server that has `/healthz`, switch only once that server runs.
5. `previews=true` gives every pull request its own empty database (at most three previews per
   app). Seed it at start, or import into it (below).

## Data in and out
- Import through an admin endpoint protected by a secret key (skill `shipholm-env-and-secrets`),
  never through git. Upsert in one transaction so a second run is safe, and answer with counts
  per table. That works the same for the preview and production.
- From Supabase: keep the tables and ids, drop `auth.*`, `storage.*`, RLS policies that call
  `auth.uid()` and the realtime publication. Their checks move into the server (skill
  `shipholm-migrate-from-lovable`).
- Export what you cannot lose: an admin endpoint that streams JSON, or `COPY … TO STDOUT` from
  the app. There is no shell into the database.

## What to know
- One instance by default, on one node. When that node reboots for OS updates (03:00–05:00
  Stockholm time, one node at a time), the database is away for a minute or two. Let the app
  retry its connection instead of exiting. `db_instances` up to 3 adds standbys on other nodes.
- Keep the connection pool small (5–10 per replica). Several replicas multiply it.
- The database cannot be removed by an update, since that would delete the data. Delete the app
  to drop it.
- Backups to object storage are on the roadmap. Until then, export what you cannot lose.
