PostgreSQL

PostgreSQL is for a site whose database is a separate service: a managed database, a platform with no lasting disk, or more than one copy of the site. This page is for whoever sets that up. If one server with a disk is all you have, SQLite is simpler. Read Choose a database first: there is no tool to move a site between databases later.

Settings

Variable	What it does	Default
DB_PROVIDER	postgres chooses PostgreSQL. postgresql is accepted too.	sqlite
DATABASE_URL	The address of the database, with the user and password in it.	none, required
DATABASE_SSL	true encrypts the connection.	false
DB_AUTO_MIGRATE	Create missing tables at every start.	true

DB_PROVIDER=postgres
DATABASE_URL=postgres://choir:a-long-password@db.example.org:5432/choir
DATABASE_SSL=true

The address has the form postgres://USER:PASSWORD@HOST:PORT/DATABASE. If the password contains characters such as @, :, /, # or %, write them percent-encoded (@ is %40), or choose a password of letters and digits.

The address contains the password, so treat it as a secret. DATABASE_URL_FILE reads it from a file: Keep secrets in files.

Create the database and its user

The site needs one empty database and a user that owns it. It creates its own tables. As a PostgreSQL administrator:

CREATE USER choir WITH PASSWORD 'a-long-password';
CREATE DATABASE choir OWNER choir;

The user has to be able to create tables and indexes in the database, at every start and after every update, so do not give the site a user that can only read and write rows.

Keep the database's time zone at UTC, which is the default on most servers and in the official Docker image. The site stores times in plain columns and treats them as UTC; it does not set the time zone of its own connections. To be certain:

ALTER DATABASE choir SET timezone TO 'UTC';

Encryption: DATABASE_SSL

DATABASE_SSL=true encrypts the connection. Most managed databases require it.

It does not check the server's certificate. The connection is protected against someone listening, but not against someone standing in for your database server. There is no setting for a certificate authority file.

Options written in the address

The site hands DATABASE_URL to the pg driver as it is, and that driver reads options from the end of the address. Many providers give you an address ending in ?sslmode=require. With such an option the driver, not DATABASE_SSL, decides how the connection is encrypted, and current versions of it then do check the certificate. This is the driver's behaviour and was not tested for this guide. If a connection fails with a certificate error, try the address without the sslmode option and with DATABASE_SSL=true.

What you cannot set

• The number of connections. The site keeps a pool of connections and there is no setting for its size. The driver's own default applies, which is ten at most for each copy of the site.
• A schema or table prefix. The tables go in the user's default schema, normally public, under fixed names. Give the site a database of its own.

How dates and numbers come back from the database is handled inside the site, so that every database gives the same answers. There is nothing to configure.

In Docker Compose

docker-compose.yml in the source runs PostgreSQL beside the site, using the image postgres:16-alpine, and builds DATABASE_URL from three values in your .env:

POSTGRES_USER=choir
POSTGRES_PASSWORD=change-me
POSTGRES_DB=choir

POSTGRES_PASSWORD is required; Compose refuses to start without it. The data is in the db-data volume. See Docker Compose: PostgreSQL and MinIO.

Check it

Start the site and look for:

[db] schema is up to date (postgres)

Then list the tables. There should be 36:

psql "postgres://choir:a-long-password@db.example.org:5432/choir" -c '\dt'

Backups are yours

The update does not back this database up

Before a one-click update the site makes a safety copy of a SQLite database only. A PostgreSQL database is not copied, by the update or by anything else in the site. Set up pg_dump or your provider's backups before the site holds anything you would miss: Back up PostgreSQL, MySQL, S3 storage and Cloudflare.

If something goes wrong

The site applies its schema at start, so a database it cannot reach stops it. The driver's own error follows [server] failed to start: in the log.

What you see	Cause
DATABASE_URL is needed for DB_PROVIDER=postgres among the [config] lines	DATABASE_URL is not set. The driver then falls back on its own defaults and usually fails to connect to localhost.
ECONNREFUSED, ENOTFOUND or a timeout	The host or port is wrong, or a firewall is in the way.
password authentication failed for user "…"	The user or password is wrong, or a special character in the password is not percent-encoded.
no pg_hba.conf entry for host …, no encryption	The server requires an encrypted connection: set DATABASE_SSL=true.
The server does not support SSL connections	DATABASE_SSL=true against a server without encryption, such as the one in the Compose file. Remove it.
permission denied for schema public	The user may not create tables. Make it the owner of the database.