Part 6 of 6 Running one for other people
PostgreSQL, and when to switch
By the end of this chapter, the team's work has moved from a file into PostgreSQL, the move is recorded where the service will look for it, and both have been checked before anything is started.
SQLite is the default and it is not a toy: it is the right answer for one person, and for a small team that is not writing at the same moment. This chapter is about the day that stops being true.
The work so far
Laurence has the service account, the install and the instance that An account, an install and a first run made, with a task in it:
sudo useradd --system --no-create-home --shell /usr/sbin/nologin subroutine
sudo python3 -m venv /opt/subroutine
sudo /opt/subroutine/bin/pip install "subroutine[postgres]"
sudo install -d -o subroutine -g subroutine -m 0755 /var/lib/subroutinesudo -u subroutine env \
XDG_CONFIG_HOME=/var/lib/subroutine/config \
XDG_DATA_HOME=/var/lib/subroutine/data \
XDG_STATE_HOME=/var/lib/subroutine/state \
/opt/subroutine/bin/subroutine init --workspace metacortex --instance-name MetaCortex
sudo -u subroutine env \
XDG_CONFIG_HOME=/var/lib/subroutine/config \
XDG_DATA_HOME=/var/lib/subroutine/data \
XDG_STATE_HOME=/var/lib/subroutine/state \
/opt/subroutine/bin/subroutine add "Rewrite the home page copy"
When to switch
Any one of these is a reason, and none of them is about how much work you have:
- More than a handful of people or agents write at the same moment. SQLite takes one writer at a time.
- The database has to live on a different machine from the service. It cannot: a network filesystem cannot give SQLite the locking it needs, and
serverefuses to start on one rather than corrupting the file quietly. - You want the backups, replication and point-in-time recovery you already run to cover this too.
The [postgres] extra is not PostgreSQL. It installs the driver, and it installs perfectly happily on a machine with no database server anywhere. The server is yours to provide:
Not checked: the build does not run this.
sudo apt install -y postgresqlOn Debian or Ubuntu that makes a cluster, starts it, and starts it again at boot, so there is no initdb step by hand. PostgreSQL's own installation pages cover every other platform, and nothing below depends on which one you used:
pg_lsclusters
online is the word to look for.
A role and a database, both named for the account
sudo -u postgres createuser subroutine
sudo -u postgres createdb --owner=subroutine subroutinepostgresql+psycopg:///subroutine names no host and no user, so it connects over a Unix socket as the operating system user, which under the service is subroutine. PostgreSQL's own default maps that straight through, so there is no password to keep anywhere and nothing listening on the network.
--owner is load-bearing, and it looks decorative. Since PostgreSQL 15 the public schema no longer lets every user create tables in it, and the database owner does. Leave it off and the first migration stops on permission denied for schema public: a message about schemas, arriving a long way from the decision that caused it.
Both are far cheaper to fix now than with data in them, so check before Subroutine touches the database at all:
sudo -u subroutine psql -d subroutine -c '\conninfo'
sudo -u subroutine psql -d subroutine -tAc 'show server_encoding'
The first says the socket and the account are the ones you meant. The second wants to say UTF8: a minimal server image with its locale left at C can give you a SQL_ASCII cluster, and that is a bad day much later rather than a small one now. sudo -u postgres psql -l shows the same for every database at once.
Copy what you have across
Do not just change the setting. That gives you an empty database and leaves everything you have in a file nothing is reading. A backup will not do it either: backups are per engine, so a SQLite one cannot be restored into PostgreSQL.
Nothing is serving this instance yet, so there is nothing to stop; where something is, stop it first, so that nothing writes to the old database after the copy is taken. Then subroutine db copy takes the whole instance across:
sudo -u subroutine env \
XDG_CONFIG_HOME=/var/lib/subroutine/config \
XDG_DATA_HOME=/var/lib/subroutine/data \
XDG_STATE_HOME=/var/lib/subroutine/state \
/opt/subroutine/bin/subroutine db copy --to postgresql+psycopg:///subroutine
It is a copy, and the original is untouched, so nothing is at risk while you check it. The target has to be empty; it is migrated to the right schema for you, and every table is read back and counted before the command says it worked. Keep the SQLite file until you are sure: deleting it is the only step here that cannot be undone, and nothing does it for you.
It works in the other direction too, which is what you want for a copy of a served instance on your own laptop.
A database that is not there is refused before anything is read:
sudo -u subroutine env \
XDG_CONFIG_HOME=/var/lib/subroutine/config \
XDG_DATA_HOME=/var/lib/subroutine/data \
XDG_STATE_HOME=/var/lib/subroutine/state \
/opt/subroutine/bin/subroutine db copy --to postgresql+psycopg:///nothing_here
createdb first, and run it again.
Record the move
The copy changed nothing about which database this instance uses. That is one line, in the configuration the service reads:
sudo -u subroutine tee -a /var/lib/subroutine/config/subroutine/config.toml > /dev/null <<'SETTING'
database_url = "postgresql+psycopg:///subroutine"
SETTINGSubroutine will not write it for you, and that is deliberate. A PostgreSQL URL routinely carries a password, and a password belongs with the credentials rather than beside the settings: config.toml is 0600 and holds the signing key, and what it does not hold is anything that authenticates you to something else.
You can give it in the environment instead, as SUBROUTINE_DATABASE_URL, which is what you want when the credential comes from a secrets manager rather than a file on disk. Just be sure that whatever starts the service sets it, because the unit sets only the three directories.
Check before starting anything
subroutine db current reads the schema out of whichever database the configuration names, as the service account and with the same three directories:
sudo -u subroutine env \
XDG_CONFIG_HOME=/var/lib/subroutine/config \
XDG_DATA_HOME=/var/lib/subroutine/data \
XDG_STATE_HOME=/var/lib/subroutine/state \
/opt/subroutine/bin/subroutine db current
A schema revision means this configuration found a database and that it is at the revision this build expects. A path ending .db means the setting has not taken effect and there is nothing where it points. Neither says which database, which is what the next command is for. All three directories, even though this only reads: leave them off and the SQLite default resolves against your data directory rather than the service's, so a check written to catch exactly this can report a perfectly healthy schema from the wrong file.
subroutine config show answers the other half of the question, which is where the value came from:
sudo -u subroutine env \
XDG_CONFIG_HOME=/var/lib/subroutine/config \
XDG_DATA_HOME=/var/lib/subroutine/data \
XDG_STATE_HOME=/var/lib/subroutine/state \
/opt/subroutine/bin/subroutine config show | grep database_url
The file, rather than [default]. The service will read the same thing.
If it cannot find its database, do not run init again
It is the obvious thing to reach for, and it is the one thing that makes this worse. With no database_url set, subroutine init looks at the SQLite default, and where there is nothing there it says so at length: that it has run here before, that the configuration already holds a signing key, and that if you set an instance up earlier and it is not there then it is somewhere this configuration does not name. Its advice is the section above.
And then it makes a second, empty instance anyway. Everything after that looks healthy: db current reports a real schema revision, read out of the database it has just made, list reports an empty backlog, and the service starts and serves nothing. Your work is in the database nothing is naming, untouched and unreachable.
So when something cannot find its data, the answer is always database_url, and never another init.
What else changes
Search can use the database's own. On PostgreSQL, search_backend = "native" in config.toml gives full-text search that stems words and ranks results, where the default matches what you typed. It is a setting rather than a migration: nothing about the data changes, and you can put it back.
Nothing else about running the instance differs. The unit, the proxy, the accounts and the backups are the same commands on either engine, even where what they write differs, and the next chapter puts this one behind an address.