---
title: Operating the posts database on the VPS
description: Day-to-day handling of the self-hosted Postgres behind personal-website — editing post content by hand, the pnpm posts CLI, and adding databases for the other apps on the box.
date: 2026-09-13
lang: en-US
author: Julien Béranger
model: Claude Opus 5
conversation: https://claude.ai/chat/3c4d352f-4ad6-4cc5-b5ff-89c0a1e81595
source: https://julienberanger.com/operating-posts-database-vps
---

# Operating the posts database on the VPS

## Connecting

Every command below assumes the connection string from your laptop. With the
password in `~/.pgpass`, it stays out of your shell history:

```bash
psql "postgresql://website@db.w3hc.org:5432/website?sslmode=verify-full&sslrootcert=system"
```

Worth setting an alias, since you will type this a lot:

```bash
alias pgweb='psql "postgresql://website@db.w3hc.org:5432/website?sslmode=verify-full&sslrootcert=system"'
```

One thing to hold onto: there is no staging environment. Every statement you run
hits the database serving julienberanger.com, and there is no undo. The
[transaction](https://www.postgresql.org/docs/current/tutorial-transactions.html)
pattern in the next section is not ceremony.

## Changing a post by hand

### The safe path: edit the markdown, re-run the CLI

`pnpm posts add` is an upsert on `slug`, so re-adding an edited file replaces the
row. This is the right default because the markdown file stays the source of
truth:

```bash
vim path/to/tap-double-tap-tap-long.md
pnpm posts add path/to/tap-double-tap-tap-long.md
```

The filename determines the slug, so renaming the file creates a *second* post
rather than renaming the existing one. Delete the old slug if that was not the
intent.

### The direct path: UPDATE in a transaction

For a typo in a title or a missing `description`, going through the file is
overkill. Open a transaction, change one row, look at it, then decide:

```sql
BEGIN;

UPDATE posts
SET description = 'Trois gestes, trois intentions.'
WHERE slug = 'tap-double-tap-tap-long';

SELECT slug, title, description FROM posts WHERE slug = 'tap-double-tap-tap-long';
```

If the output is right, `COMMIT;`. If not, `ROLLBACK;` and nothing happened.

Always include the `WHERE` clause. An
[`UPDATE`](https://www.postgresql.org/docs/current/sql-update.html) without one
rewrites all 21 rows, and the only reason that is recoverable is the nightly
dump.

### Editing content, where quoting gets awkward

The `content` column holds raw markdown: apostrophes, backslashes, newlines,
code fences. Standard single-quoted SQL strings are miserable for this — every
`'` in `L'IA à l'école` has to be doubled.

Use [dollar
quoting](https://www.postgresql.org/docs/current/sql-syntax-lexical.html)
instead. Everything between the delimiters is taken literally:

```sql
UPDATE posts SET content = $md$
## Première section

C'est l'exemple qui contient des apostrophes, des "guillemets" et du `code`.
$md$ WHERE slug = 'gagner-sans-jouer';
```

The tag between the dollar signs is arbitrary — `$md$` reads well for markdown.
Only pick a different tag if the content itself contains `$md$`, which it will
not.

For anything longer than a paragraph, round-trip through a file rather than
pasting into a terminal:

```bash
# export the body
pgweb -At -c "SELECT content FROM posts WHERE slug = 'rukh-roadmap'" > /tmp/rukh-roadmap.md

# edit it
vim /tmp/rukh-roadmap.md

# write it back
pgweb -v content="$(cat /tmp/rukh-roadmap.md)" \
  -c "UPDATE posts SET content = :'content' WHERE slug = 'rukh-roadmap'"
```

`-A` turns off column alignment and `-t` drops the header row, so the export is
the raw markdown and nothing else. The `:'content'` syntax on the way back is
psql's variable interpolation with proper quoting, which is what saves you from
escaping by hand.

Note that `content` holds the body *without* frontmatter — `pnpm posts add`
parses those fields into their own columns and strips the leading `#` heading.
An exported file is not a valid input to `pnpm posts add` for that reason.

### Two columns that deserve care

`date` is `TEXT`, not `DATE`. Post ordering is
`ORDER BY COALESCE(date, created_at::text) DESC`, which is a *string* sort. It
works because every value is `YYYY-MM-DD`. Write `2026-9-8` instead of
`2026-09-08` and that post sorts in the wrong place, silently.

`locale` drives `formatPostDate`, which converts `fr_FR` to the BCP 47 tag
`fr-FR`. It expects the OpenGraph underscore form. A value of `fr-FR` or `fr`
will not throw, it will just format dates in the wrong language.

### When the change appears

Reads are wrapped in `unstable_cache` with a 60-second window, and the post pages
carry `revalidate = 60` on top of that. Worst case is roughly two minutes before
an edit is visible. If it has been longer, the problem is not caching.

## The posts CLI

Four commands, in `scripts/posts.ts`, all running against the VPS via the
`DATABASE_URL` in your local `.env`.

| Command | What it does |
| --- | --- |
| `pnpm posts init` | creates the `posts` table if absent, adds missing columns |
| `pnpm posts add <file.md>` | inserts or updates one post from a markdown file |
| `pnpm posts delete <slug>` | removes one post |
| `pnpm posts list` | prints date, slug and title for every post |

**`init`** is idempotent. It uses `CREATE TABLE IF NOT EXISTS` followed by
`ALTER TABLE ... ADD COLUMN IF NOT EXISTS` for `author`, `model` and
`conversation`. That pattern is the project's migration story: when you add a
column, add another `ADD COLUMN IF NOT EXISTS` line and re-run `init`. It is
crude but it never destroys anything, which is the property that matters at this
scale.

**`add`** derives the slug from the filename, validates it against
lowercase-letters-numbers-hyphens, parses the frontmatter into columns, and
upserts on `ON CONFLICT (slug) DO UPDATE`. Re-running it on the same file is
always safe.

**`delete`** takes effect immediately with no confirmation prompt. There is no
soft delete and no trash. Check the slug first with `pnpm posts list`.

**`list`** is the quickest sanity check that the database is reachable and
populated — useful on its own when something looks wrong on the live site.

A note on failure modes: since step 13 of the migration, every command closes the
connection pool in a `finally` block. If a command ever prints its output and
then hangs instead of returning to the prompt, that `sql.end()` has been lost in
a refactor.

## Adding a database for another app

The VPS already serves `rukh.w3hc.org`, `shebam.w3hc.org`, `avventura.fun` and
`api.avventura.fun`. When one of those needs Postgres, resist the temptation to
add a table to `website`.

Give each app its own database and its own role. A leaked credential then reaches
one app's data rather than all of it, and `DROP TABLE` typed in the wrong window
destroys one thing rather than everything.

### Create the role and database

On the VPS:

```bash
openssl rand -hex 24     # generate, then paste at the prompt below
sudo -u postgres psql
```

```sql
CREATE ROLE shebam LOGIN;
\password shebam
CREATE DATABASE shebam OWNER shebam;
```

Splitting `CREATE ROLE` from `\password` keeps the secret out of your shell
history and out of the Postgres logs.

### Close the default access

By default, every role can connect to every database. On a box hosting four
projects that is worth fixing:

```sql
REVOKE CONNECT ON DATABASE shebam FROM PUBLIC;
GRANT CONNECT ON DATABASE shebam TO shebam;
```

Run the same pair against `website` if you have not already. PostgreSQL 15 and
later no longer let arbitrary roles create objects in the `public` schema, so
that half is handled by your 18.6 install.

A connection limit stops one misbehaving app from starving the others:

```sql
ALTER ROLE shebam CONNECTION LIMIT 20;
```

### Add the pg_hba rule

The rules from the migration name the `website` role explicitly, so a new role
gets no access until you add its line:

```bash
sudo tee -a /etc/postgresql/18/main/pg_hba.conf > /dev/null <<'EOF'
hostssl  shebam  shebam  0.0.0.0/0  scram-sha-256
hostssl  shebam  shebam  ::/0       scram-sha-256
EOF
sudo systemctl reload postgresql
```

Verify it parsed before trusting it:

```bash
sudo -u postgres psql -c 'SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;'
```

Zero rows. A reload with a broken `pg_hba.conf` leaves the old rules in place and
only complains in the log, so this check is the difference between knowing and
assuming.

Then confirm from your laptop, including the negative case:

```bash
psql "postgresql://shebam@db.w3hc.org:5432/shebam?sslmode=verify-full&sslrootcert=system" -c '\conninfo'
psql "postgresql://shebam@db.w3hc.org:5432/website?sslmode=verify-full&sslrootcert=system" -c 'SELECT 1;'
```

The first succeeds. The second must fail — the `shebam` role has no business
reading posts.

### Create tables

Connect as the app's own role, not as `postgres`, so the tables end up owned by
the role that will use them:

```sql
CREATE TABLE IF NOT EXISTS sessions (
  id          TEXT PRIMARY KEY,
  user_id     TEXT NOT NULL,
  payload     JSONB NOT NULL DEFAULT '{}'::jsonb,
  expires_at  TIMESTAMPTZ NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS sessions_user_id_idx ON sessions (user_id);
```

Two habits worth carrying over from the `posts` table. Use `TIMESTAMPTZ` rather
than `TIMESTAMP`, so values carry a zone and
[postgres.js](https://github.com/porsager/postgres) hands you a correct `Date`.
And index any column you filter or join on — `posts` gets away with none beyond
its primary key because it holds 21 rows, which will not generalise.

Inspect what you built:

```sql
\dt
\d sessions
```

### Add it to the backup

This is the step that gets forgotten, and the consequence only shows up on the
day you need it. `/usr/local/bin/backup-website.sh` dumps exactly one database.
Switch it to
[`pg_dumpall`](https://www.postgresql.org/docs/current/app-pg-dumpall.html) so new
databases are covered automatically, roles and passwords included:

```bash
sudo tee /usr/local/bin/backup-postgres.sh > /dev/null <<'EOF'
#!/bin/sh
set -e
DEST=/var/lib/postgresql/backups
pg_dumpall --clean | gzip > "$DEST/all-$(date +%F).sql.gz"
find "$DEST" -name 'all-*.sql.gz' -mtime +14 -delete
EOF
sudo chmod +x /usr/local/bin/backup-postgres.sh
echo '15 3 * * * postgres /usr/local/bin/backup-postgres.sh' | sudo tee /etc/cron.d/backup-postgres
sudo rm -f /etc/cron.d/backup-website
```

Verify, and actually look at the size rather than just the filename:

```bash
sudo -u postgres /usr/local/bin/backup-postgres.sh
sudo ls -lh /var/lib/postgresql/backups/
```

A dump on the same disk as the database still is not a backup. Pull it off the
box nightly, or push it with [restic](https://restic.net/) to Infomaniak's object
storage.

## Restoring

Worth rehearsing once while nothing is broken. From a `pg_dumpall` archive:

```bash
gunzip -c /var/lib/postgresql/backups/all-2026-09-13.sql.gz | sudo -u postgres psql
```

For a single table when you have only lost the posts, restore into a scratch
database first and copy the rows across rather than running `--clean` against
production. The slow, boring version is the one you want at the moment you need
it.

## Further reading

- [psql reference](https://www.postgresql.org/docs/current/app-psql.html) — `\d`, `\e`, `-A`, `-t` and variable interpolation
- [CREATE TABLE](https://www.postgresql.org/docs/current/sql-createtable.html)
- [CREATE ROLE](https://www.postgresql.org/docs/current/sql-createrole.html) and [GRANT](https://www.postgresql.org/docs/current/sql-grant.html)
- [Dollar-quoted string constants](https://www.postgresql.org/docs/current/sql-syntax-lexical.html)
- [julienbrg/personal-website](https://github.com/julienbrg/personal-website) — `scripts/posts.ts` is the whole CLI, about 120 lines