Use your own PostgreSQL

tofa ships with its own database built in, and that is the setup we recommend and support. It needs no setup, backs itself up, and updates with tofa. If you already run PostgreSQL and would rather keep everything in one place, you can point tofa at it instead.

What you need#

• PostgreSQL 18 is what we test against, because it is the version we bundle. Anything from PostgreSQL 13 up should work, but older versions are not tested.
• The pg_trgm extension. It ships with PostgreSQL (in the standard contrib package, which most installs and the official Docker image include). tofa uses it for fast title search.
• A database of its own. tofa creates and manages its own tables and expects nothing else to live in that database.
• Room for around 50 connections. tofa keeps three connection pools, up to 48 connections combined. PostgreSQL's default limit of 100 is plenty unless other apps share the server.

01Create the database#

Connect to your server as an admin (for example with psql -U postgres) and create a login and a database it owns:

CREATE ROLE tofa LOGIN PASSWORD 'choose-a-strong-password';
CREATE DATABASE tofa OWNER tofa;

Making tofa's role the owner of the database matters: it lets tofa create the pg_trgm extension itself on first start. If tofa connects as a role that does not own the database, create the extension once as an admin instead (\c tofa then CREATE EXTENSION IF NOT EXISTS pg_trgm;), or the first start fails with a permission error.

02Point tofa at it#

Set DATABASE_URL and SESSION_SIGNING_KEY in the server's environment. In Docker Compose they go under environment::

environment:
  DATABASE_URL: postgres://tofa:[email protected]:5432/tofa
  SESSION_SIGNING_KEY: paste-a-random-string-of-at-least-32-characters

For the native Linux service, use NAME=value syntax in /etc/tofa/tofa.env and restart the service:

DATABASE_URL=postgres://tofa:[email protected]:5432/tofa
SESSION_SIGNING_KEY=paste-a-random-string-of-at-least-32-characters

sudo systemctl restart tofa

SESSION_SIGNING_KEY is required with your own database. The bundled setup creates one for you, but here tofa will not start without it. Use a random string of at least 32 characters, for example the output of openssl rand -hex 32. Keep it the same from then on: changing it interrupts any playback that is already running.

When DATABASE_URL is set, the bundled database stays off and tofa uses yours. If the password contains characters such as @, :, /, or #, percent-encode them in the URL (@ becomes %40), or pick a password without them.

Encrypted connections. Add ?sslmode=require to the end of the URL to require TLS. If your server uses a certificate from your own certificate authority, also add &sslrootcert=/path/to/ca.crt pointing at a file the container can read.

03Start tofa#

Start or restart the server. On first start it creates its tables, and on every later start it applies any changes a new version needs, so there is nothing to run by hand when you update. The server log mentions "using external database mode" when it picked up your database.

Then claim the server and add your libraries as usual.

Moving an existing server to your database#

Setting DATABASE_URL on a server that has been running on the bundled database does not copy anything across. tofa starts on an empty database, as if freshly installed. To bring your library, users, and settings with you:

1. On the old setup, take a backup: Admin, then Settings, then Database, then Backup Now. Backups are gzip-compressed SQL files (tofa-embedded-….sql.gz) in the backups folder of the data directory.
2. Create the empty database as in step 1.
3. Load the backup into it before starting tofa against it: gunzip -c tofa-embedded-….sql.gz | psql -U tofa -d tofa -v ON_ERROR_STOP=1
4. Set DATABASE_URL and SESSION_SIGNING_KEY, then start tofa.

To script this, for example in an Ansible playbook, stop tofa and take the backup from the command line instead. With the server stopped, nothing can change after the snapshot, and a path ending in .sql gives you an uncompressed file:

docker stop tofa
docker run --rm -v /path/to/tofa-data:/data <tofa-image> ./tofa backup --output /data/backups/migrate.sql
psql -U tofa -d tofa -v ON_ERROR_STOP=1 -f /path/to/tofa-data/backups/migrate.sql

Keep the rest of the data directory, especially config.toml and identity/. The identity folder is what ties the server to your account, so a server moved without it has to be claimed again.

What changes with your own database#

• Backups are yours. The built-in backup schedule, verification, and automatic restore do not apply; the Database page says so. Use your usual pg_dump routine, and still copy config.toml and identity/ from the data directory. See Back up your server.
• In-place updates are off. The one-click update relies on the bundled database, so the update card tells you to update through your install method instead (for Docker, pull the new image). See Keep your server up to date.
• Connection limits are adjustable. If your PostgreSQL is short on connections, lower TOFA_DB_MAX_CONNECTIONS (default 32), TOFA_DB_BG_MAX_CONNECTIONS (default 12), and TOFA_DB_AUX_MAX_CONNECTIONS (default 4). Going much lower than the defaults can slow tofa down while scans and playback run at the same time.

If something is off#

• tofa will not start, and the log mentions pg_trgm or "permission denied to create extension". tofa's role does not own the database. Create the extension as an admin, as in step 1, then start tofa again.
• "password authentication failed". Check the user and password in the URL, and that your pg_hba.conf allows that user to connect from the machine or container tofa runs in.
• "connection refused" from Docker. Inside a container, localhost is the container itself. Use the database container's name when both share a Docker network, or the host's address otherwise.

Still stuck? Getting help & feedback covers what to send us.