Operating the posts database on the VPS

Sunday, September 13, 2026

Julien Béranger

Connecting

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

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

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

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 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:

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:

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 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 instead. Everything between the delimiters is taken literally:

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:

# 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:

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

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:

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:

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:

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:

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:

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:

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 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:

\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 so new databases are covered automatically, roles and passwords included:

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:

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 to Infomaniak's object storage.

Restoring

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

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.