13 KiB
Database Operations
psql recipes for inspecting and maintaining the chess database as the
PostgreSQL administrator. Installation is in deployment.md
(FreeBSD jail) and deployment-linux.md (Linux with
systemd).
Typical Layout
The server expects one database with its tables in one schema of the same name, owned by a role that exists only for it:
| Object | Name | Notes |
|---|---|---|
| Database | chess |
Owned by postgres; only the chess role may connect |
| Schema | chess |
All tables; public is locked down and stays empty |
| Login role | chess |
No password, no superuser, create-database, or create-role rights; 20 connections |
| Owner role (split mode only) | chess_owner |
Cannot log in; owns the schema while chess only reads and writes rows |
The service runs as an OS account named chess and connects over the local
Unix socket with peer authentication, which maps the OS account to the
role of the same name, so no password exists anywhere. The chess role has
its search_path set to chess in this database, so the service never names
the schema. deploy/postgresql/setup.sql
creates all of this; the deployment scripts run it once.
Administration uses the postgres superuser account, which keeps the
distribution's defaults. Its search_path ("$user", public) does not
include chess, so every recipe names the schema explicitly (chess.games).
This keeps other databases and tools on the same server unaffected, and a
statement typed into the wrong database fails instead of acting on whatever
it finds.
Connecting
# Linux
sudo -u postgres psql -X -d chess
# FreeBSD jail
su -m postgres -c 'psql -X -d chess'
Working with the chess schema as postgres:
| Goal | How |
|---|---|
| List the tables | \dt chess.* (a plain \dt shows public, which is empty) |
| Describe a table | \d chess.games |
| List schemas and owners | \dn+ |
| Query | SELECT ... FROM chess.games |
| Unqualified names for one session | SET search_path = chess; (lasts until you disconnect) |
| Unqualified names for one invocation | sudo -u postgres env PGOPTIONS='-c search_path=chess' psql -X -d chess |
Neither of the last two changes anything stored: the next session starts with
the defaults again. Avoid ALTER ROLE postgres ... SET search_path, which
would persist.
The tables:
| Table | Contents |
|---|---|
chess.schema_version |
One row: the applied migration |
chess.users |
Accounts: lowercase username and email, Argon2id password hash |
chess.sessions |
One active session per user; deleting it signs the user out |
chess.games |
Players, claims (*_claimed_by), name snapshots, result, termination, concession_*, times |
chess.moves |
Ply move_number 1..n, UCI move, FEN after the move |
result is white_wins, black_wins, stalemate, or draw, and
termination says how: checkmate, resignation, stalemate,
insufficient_material, threefold_repetition, fifty_move_rule, or
agreement. Both are empty while a game is unfinished, and an undo empties
them again. concession_result, concession_termination, and
concession_ply keep the first resignation or agreed draw even when an undo
against the computer continued play:
SELECT game_id, concession_result, concession_ply,
coalesce(result, 'unfinished') AS now
FROM chess.games
WHERE concession_result IS NOT NULL
AND (result IS DISTINCT FROM concession_result
OR termination IS DISTINCT FROM concession_termination); -- continued after conceding
\set g '<game-id>' stores a game ID for the :'g' references below.
Overview
SELECT version, updated_at FROM chess.schema_version;
SELECT (SELECT count(*) FROM chess.users) AS users,
(SELECT count(*) FROM chess.games) AS games,
(SELECT count(*) FROM chess.games WHERE result IS NULL) AS unfinished,
(SELECT count(*) FROM chess.moves) AS plies,
(SELECT count(*) FROM chess.sessions WHERE expires_at > now()) AS signed_in;
SELECT coalesce(termination, 'unfinished') AS termination, count(*)
FROM chess.games GROUP BY 1 ORDER BY 2 DESC;
Users
SELECT user_id, username, email, created_at, last_login_at
FROM chess.users ORDER BY created_at;
Passwords are not stored. password_hash is an Argon2id string in PHC
format, $argon2id$v=19$m=<KiB>,t=<passes>,p=<lanes>$<salt>$<hash>, from
which the password cannot be recovered. Show the parameters without the
secret parts:
SELECT username, split_part(password_hash, '$', 2) AS algorithm,
split_part(password_hash, '$', 4) AS parameters
FROM chess.users;
Create, reset, and rename accounts with the server's CLI, not SQL: it
normalizes names, enforces uniqueness rules, and hashes passwords. Without
-password it prompts, which keeps the password out of the shell history and
the process list.
# Linux: chess-db runs `chess-server db ...` as the chess account
sudo chess-db user add -username <name> [-email <addr>]
sudo chess-db user set-password -username <name>
sudo chess-db user list
# FreeBSD jail
su -m chess -c '/home/chess/bin/chess-server db user add -username <name> -dsn "postgres:///chess?host=/tmp"'
Other user subcommands: delete, set-email, set-username, set-hash
(an existing PHC string), and reset-link -url <web client>, which prints a
one-time password-reset link (valid an hour) to hand to someone who cannot
receive the recovery email. CLI-created accounts are identical to site
registrations, and the CLI ignores the registration cap.
A game imported from PGN (POST /api/games/import, db game import) has
imported_by set to its owner, no claims, the PGN's event, site and
round, and both sides human with the tag names as snapshots. It is listed
for its owner, never purged as anonymous, and deleted with its moves when the
owner removes it (DELETE /api/users/me/games/{id}) or by db game delete -gameId <id|prefix> -confirm. A player removes a game they share with
another registered player by releasing their own claim instead.
Pending password resets live in password_resets: the SHA-256 of the token
in the emailed link, one per account, deleted when used, expired, or by the
hourly cleanup. The token itself is never stored.
Sign a user out everywhere (takes effect on their next request):
DELETE FROM chess.sessions
WHERE user_id = (SELECT user_id FROM chess.users WHERE username = 'alice');
Deleting a user removes their session; their games keep the claim and the name snapshot. Games left with no remaining registered player are removed by the optional integrity sweep (below) once idle for a day.
Games
Recent games with players, outcome, and length:
SELECT g.game_id, g.start_time_utc,
coalesce(g.white_name, CASE g.white_type WHEN 2 THEN 'Stockfish L' || g.white_level ELSE 'anonymous' END) AS white,
coalesce(g.black_name, CASE g.black_type WHEN 2 THEN 'Stockfish L' || g.black_level ELSE 'anonymous' END) AS black,
coalesce(g.result || ' by ' || g.termination, 'unfinished') AS outcome,
(SELECT count(*) FROM chess.moves m WHERE m.game_id = g.game_id) AS plies
FROM chess.games g ORDER BY g.start_time_utc DESC LIMIT 20;
A user's games: add
WHERE (SELECT user_id FROM chess.users WHERE username = 'alice') IN (g.white_claimed_by, g.black_claimed_by).
Find a game from the 8-digit prefix that db query prints:
SELECT game_id FROM chess.games WHERE game_id::text LIKE '68007fd1%';.
One game, header and every ply with its FEN:
\set g '68007fd1-8970-4d84-950d-2f6ec6375c0b'
SELECT * FROM chess.games WHERE game_id = :'g' \gx
SELECT move_number AS ply, player_color, move_uci, fen_after_move, move_time_utc
FROM chess.moves WHERE game_id = :'g' ORDER BY move_number;
Final position:
SELECT coalesce(
(SELECT fen_after_move FROM chess.moves WHERE game_id = :'g' ORDER BY move_number DESC LIMIT 1),
(SELECT initial_fen FROM chess.games WHERE game_id = :'g')) AS final_fen;
The move line in UCI with move numbers (numbering follows the initial FEN, so
a game that starts with Black to move begins N...):
WITH s AS (SELECT split_part(initial_fen, ' ', 2) AS side,
split_part(initial_fen, ' ', 6)::int AS fullmove
FROM chess.games WHERE game_id = :'g')
SELECT string_agg(
CASE WHEN m.player_color = 'w'
THEN (s.fullmove + (m.move_number - CASE s.side WHEN 'w' THEN 1 ELSE 0 END) / 2) || '. '
WHEN m.move_number = 1 THEN s.fullmove || '... '
ELSE '' END || m.move_uci,
' ' ORDER BY m.move_number) AS uci_line
FROM chess.moves m, s WHERE m.game_id = :'g';
SQL has no SAN; the database stores UCI and notation is derived on read. For
real PGN use the CLI (a full ID or 8-digit prefix; -ply N stops after N
plies) or the API:
sudo chess-db pgn -gameId 68007fd1 # Linux
su -m chess -c '/home/chess/bin/chess-server db pgn -gameId 68007fd1 -dsn "postgres:///chess?host=/tmp"' # FreeBSD
curl -s https://<site>/chess/api/games/<game-id>/pgn
db verify checks every stored game against the rules (move numbering, each
move legal from the stored position before it, the stored position after it
reproduced, the result and its termination consistent with the final
position) and exits non-zero on any problem:
sudo chess-db verify
Deleting games
Delete finished or unfinished games at any time:
BEGIN;
DELETE FROM chess.games WHERE game_id = :'g'; -- its moves are removed by cascade
COMMIT;
All games of one user (both colors):
DELETE FROM chess.games
WHERE (SELECT user_id FROM chess.users WHERE username = 'alice') IN (white_claimed_by, black_claimed_by);
A game the server is still playing is unloaded when its next write finds the
row gone (that last move is not stored), or earlier by the integrity sweep;
the server stays healthy. Deleting individual chess.moves rows instead leaves a broken line
that replay cannot use: delete the whole game, or let the integrity sweep do
it.
Games without a registered player are already deleted by the server 24 hours
after their last activity (-anonymous-game-ttl).
Integrity sweep
With the server flag -db-cleanup report (log only) or -db-cleanup delete,
an hourly sweep looks for data the server cannot use:
- games whose rows were deleted while the server held them (unloaded);
- games with missing plies, i.e. deleted move rows;
- games that fail
db verify, 200 per run in ID order; - games whose registered players were all deleted, once idle for the anonymous-game retention;
- accounts whose password hash is not a usable Argon2id string (nobody can sign in to them).
Each finding is logged at warning level with its reason; delete also
removes them. Start with report, read the log, then switch. Set it through
CHESSD_FLAGS in /etc/chessd/chessd.env (Linux) or chessd_flags in
rc.conf (FreeBSD), and restart the service.
Starting Fresh
Every option below loses data: take a dump first if any of it matters.
sudo -u postgres pg_dump -Fc -d chess -f /var/lib/postgresql/chess-$(date +%F).dump # Linux (Debian/Ubuntu path)
su -m postgres -c 'pg_dump -Fc -d chess -f /var/db/postgres/chess-$(date +%F).dump' # FreeBSD
The service commands are systemctl stop|start chessd on Linux and
service chessd stop|start on FreeBSD.
Clear the data, keep the schema (accounts, sessions, and games), with the service stopped:
TRUNCATE chess.moves, chess.games, chess.sessions, chess.users;
Recreate the tables (drops and re-runs the migrations), with the service stopped:
sudo chess-db delete -confirm && sudo chess-db init # Linux
su -m chess -c '/home/chess/bin/chess-server db delete -confirm -dsn "postgres:///chess?host=/tmp"' # FreeBSD
su -m chess -c '/home/chess/bin/chess-server db init -dsn "postgres:///chess?host=/tmp"'
db init prints Database schema ready (version 3). In split mode the
chess role owns no tables and cannot drop or create them; run both commands
as postgres acting as the owner role instead, e.g. on Linux:
sudo -u postgres /usr/local/bin/chess-server db delete -confirm \
-dsn "dbname=chess user=postgres options='-c role=chess_owner -c search_path=chess'"
Rebuild the database and role (as on a new host), with the service stopped:
DROP DATABASE IF EXISTS chess WITH (FORCE);
DROP ROLE IF EXISTS chess_owner; -- exists only in split mode
DROP ROLE IF EXISTS chess;
Then rerun the deployment script (deploy/linux/setup.sh or
deploy/freebsd/setup-jail.sh) with the same inputs as before. With neither
role nor database present it runs setup.sql, creates the tables, starts the
service, and checks /health. The JWT key is kept; accounts are gone, so
create them again.
Verify any of them:
\dt chess.* -- games, moves, schema_version, sessions, users
SELECT version FROM chess.schema_version; -- 3
\drds -- chess: search_path and timeouts in database chess