PostgreSQL
The bot stores everything in PostgreSQL 17, running in Docker. This page covers the setup, the migration from SQLite, the connect-race fix, backups, and how to query the database. The two-bot side of the story is in Two bots.
The setup
One container, one database, localhost only:
# servers/postgres/docker-compose.yml
services:
postgres:
image: postgres:17
container_name: havenly-postgres
restart: unless-stopped
env_file:
- .env.postgres
ports:
- "127.0.0.1:5432:5432" # localhost only — nothing outside the machine can reach it
volumes:
- havenly_pgdata:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U havenly_bot -d havenly"]
interval: 5s
timeout: 5s
retries: 12
volumes:
havenly_pgdata:
-
Database:
havenly(production).havenly_stagingis the staging database used for dry runs — never point the live bot at it. -
Credentials:
.env.postgresholds the database password. It is strictKEY=VALUE— no spaces around=— becausedocker composeis pickier than the bot's.env. The file is mode 600 and lives only on the server. The bot's own.envis CRLF with spaces around=; the two files have different formats on purpose, so do not copy one over the other. -
How the bot connects:
DATABASE_URLin the bot's.env:DATABASE_URL=postgresql://<user>:<password>@127.0.0.1:5432/havenlydata/DB/db.pyreads it from the bot-root.envand raises a clear error if it is missing. The code derives a sync URL (postgresql+psycopg://) and an async URL (plainpostgresql://fordatabases/asyncpg) from it. -
Drivers:
asyncpg0.32.0 (async driver fordatabases) andpsycopg3.3.9 (sync driver for the JSON store and the Logger), installed aspsycopg[binary]. Kept:databases0.9.0, SQLAlchemy 2.1.6.asyncpgandpsycopgare installed on the live server but not yet listed inrequirements.txt, so install them explicitly on a fresh setup (pip install asyncpg "psycopg[binary]").
Check the container is healthy with:
docker exec havenly-postgres pg_isready -U havenly_bot -d havenly
docker logs havenly-postgres
The migration from SQLite
On 2026-11-12 the data moved from SQLite (bot.db) to Postgres.
The route was pgloader (Docker dimitri/pgloader:latest),
loading into a schema created first by the patched code's own
metadata.create_all() plus the Logger and JSON-store schema
setup (46 tables).
Final cutover numbers (bot stopped, so final):
| Source | Rows moved | Verification |
|---|---|---|
bot.db (42 tables) | 4,969 | 0 count mismatches |
message_log.db (5 tables) | 2,865 data rows | 0 count mismatches |
Verification covered per-table row counts on all 46 tables, 14
JSONB columns, and 7 spot checks (profiles, levels blob,
command_permissions, qotd_questions, ghlog_items, events,
messages) — all clean. Notable counts: profiles 2,519;
introduction_status 1,204; command_permissions 456;
vrchat_accounts 373; qotd_questions 296; events 1,132.
The old SQLite database is frozen, not live. The live
bot.dbwas frozen at 2026-11-12 11:41. Nothing writes to SQLite any more. The file is still in the repo folder as a legacy copy — do not delete it, and do not expect it to change. A rollback to SQLite would need the original (pre-Postgres) files restored, not just flipping the URL — the Postgres code usessqlalchemy.dialects.postgresql.insertand cannot run on SQLite.
Postgres semantics worth knowing (verified during the build):
- Upserts are
INSERT ... ON CONFLICT DO UPDATE(orDO NOTHING), keyed on the primary key.ON CONFLICT DO UPDATEchecks NOT NULL on the proposed row even when a conflict exists — a partial-payload upsert that omits a NOT NULL column with no default fails. The only upsert table with a non-PK NOT NULL column isvrchat_accounts(vrc_user_id), and live callers always pass the full payload there. command_permissions.allowedis anIntegercolumn (1 = allowed, 0 = denied), not a Boolean.sqlalchemy.JSONcolumns map to JSONB. The JSON store's_rows_to_dictaccepts both a string and an already-parsed value, because Postgres JSONB returns real values — this fixed the SQLite NUMERIC-affinity quirk where a bare-number row was quarantined instead of loading.- Level awards are serialised across processes with a Postgres
advisory lock —
pg_advisory_xact_lock(hashtext("levels:<guild_id>")::bigint)inside a transaction (get_level_lock()incogs/Level_system.py). The old per-processasyncio.Locksilently lost XP when two processes awarded at once (the harness proved it: 44 XP instead of 80 on SQLite, 80 on Postgres). Never nest the lock.
The connect race (and the fix)
The first cutover attempt (2026-11-12 11:42–11:52) was rolled
back because the bot's database layer broke under its own
concurrent startup. Login and command sync worked, but every
database operation failed forever after with errors like
AssertionError('Connection is already acquired') and
InterfaceError('Pool.release() received invalid connection … is not a member of this pool'). The data move itself was correct
(0 mismatches) — the failure was in the connect path, not the
data.
Root cause: many cogs each call await database.connect() in
their before_loop hooks (QOTD, Lockdown, Timeout, and others),
and those hooks all fire at the same moment after on_ready.
On SQLite (aiosqlite) connect() is near-instant, so the
concurrent calls do not collide. On Postgres, connect() creates
the asyncpg pool (~1 s), so the concurrent calls raced inside the
databases library (0.9.0) and left the single shared connection
stuck in an "acquired" state.
The fix (three changes, in data/DB/db.py, bot.py,
cogs/admin_cog.py):
SerialisedDatabase(Database)— a subclass indb.pythat holds a singleasyncio.Lockaroundconnect(): the check-and-connect is atomic, so exactly one caller creates the pool and the rest return as safe no-ops. Per-cogconnect()calls are left in place but neutralised once connected.- A single explicit
await database.connect()at the top ofon_readyinbot.py, before the guild sanity check, the grant-rename migration, and the sync — in try/except that logs loudly but does not block startup. - A
connect()inadmin_cog's_before_board_refresh— the board-refresh loop was the only loaded loop that touched the async DB without connecting first.
The harness was extended to simulate real startup (8 concurrent connect callers plus a DB worker doing loop-first-iteration work) and gated the retry: 99/99 checks passed with the fix, and the negative control (old code) failed with the exact live error. The retry cutover (2026-11-12 ~12:11–12:21) succeeded, with 0 DB errors and 12 active Postgres connections from the bot.
Lesson for future work: a harness that only exercises the
database layer can pass while the real startup still races. A
harness pass counts as evidence only when it replays the real
failure first and starts the bot the way the real bot starts,
including on_ready and the explicit connect.
Backups
Snapshots are pg_dump-based. One dump file, bot.dump,
replaces the two old .db files; each snapshot is the dump plus
a manifest, zipped, with SHA-256 verification.
- Who takes them: the Helper. Its daily loop takes a snapshot
at 04:00, and
/backup now//backup status(Helper-only commands) take and inspect manual snapshots. The dump runs through the container (docker exec ... pg_dump --clean --if-exists --no-owner --no-privileges); inside the container local socket auth is trust, so no password is passed on the command line. - Retention: the newest 14 daily snapshots + the newest 10
manual / pre-restore snapshots; older ones are pruned
automatically. An old SQLite-era snapshot (kind
sqlite) is refused outright on restore. - Nightly copy:
nitro-backup.shruns at 2am and copies the server directories to~/backups/(14-day retention). That protects against deletes and bad edits — not against the disk dying, because everything is still on the same disk. The offsite step (an encrypted nightly upload ofbot.dumpto a private cloud folder) is recommended but not yet set up; until it is, the snapshots are a same-disk safety net, not an offsite backup. - Restore: from a snapshot with
psqlthrough the container (seeutils/backup.pyfor the exact invocation). Apg_dumprestores withpg_restore/psqlinto a fresh Postgres — no SQLite files are involved. Test a restore intohavenly_stagingbefore relying on it.
Querying the database
From the server — psql inside the container:
docker exec -it havenly-postgres psql -U havenly_bot -d havenly
Useful first checks:
SELECT count(*) FROM profiles; -- 2,519 at cutover
SELECT count(*) FROM command_permissions;
SELECT * FROM pg_stat_activity WHERE usename = 'havenly_bot';
From a PC — Postgres only listens on 127.0.0.1:5432, so a
remote tool cannot reach it directly. Use a GUI client that
supports an SSH tunnel (e.g. DBeaver): tunnel to the Nitro over
SSH, then connect to host 127.0.0.1, port 5432, database
havenly, user havenly_bot (password from .env.postgres on
the server — read it there, never paste it into chat or a shared
file).
Staging: havenly_staging holds a copy of the migrated data
for dry runs. Point a staging .env at it
(DATABASE_URL=postgresql://<user>:<password>@127.0.0.1:5432/havenly_staging)
and never point the live bot there. The staging harness creates
its own throwaway database (havenly_harness) and drops it when
it finishes, so harness runs never touch havenly_staging data.