Databases

A Raspberry Pi cannot afford a database server per app. piway runs one shared server per engine, PostgreSQL, MariaDB and Redis, and gives each app that asks its own database and user on it.

run:
  port: 3000
  databases: [postgres, redis]

On the next deploy piway starts the engines the app asks for (the first time, it pulls the image and initialises the data, which takes a minute or two), creates the app's database and user, and sets these variables:

Engine	Variables
postgres	DATABASE_URL and POSTGRES_URL: postgres://<app>:<password>@piway-postgres:5432/<app>?sslmode=disable
mariadb	MYSQL_URL (and DATABASE_URL, unless the app also uses postgres): mysql://<app>:<password>@piway-mariadb:3306/<app>
redis	REDIS_URL: redis://<app>:<password>@piway-redis:6379/0, and REDIS_PREFIX: <app>:

These cannot also be set in run.env or run.secrets. They show on the app's page under Environment, marked set by piway; the values are credentials, so they are masked and shown only in a signed-in browser.

The dashboard's Databases page shows each engine: whether it runs, its version (and whether this piway pins a newer one), the size of its data, its memory, and the apps with a database on it with their latest backup. Back up now runs the nightly backup at once.

Isolation

• PostgreSQL and MariaDB: each app has its own database and a user that can reach only that database. Other apps' databases (and PostgreSQL's postgres database) refuse it.
• Redis: each app has an ACL user limited to keys and channels that start with <app>: (use REDIS_PREFIX). Commands that reach beyond them, such as FLUSHALL, KEYS and CONFIG, are refused. For pub/sub, note that Redis compares a PSUBSCRIBE pattern literally with the allowed channels: subscribe to <app>:* itself and filter, not to a narrower pattern such as <app>:room:* (see the chat example).
• Network: the engines are on piway-db, an internal Docker network. Apps that use a database join it; nothing on it is published on the Pi, so other machines cannot reach the engines, and the engines have no route to the internet.

Passwords are generated by piway: one admin password per engine, which no app gets, and one per app and engine, kept in ~/.config/piway/databases/ with mode 0600 and included in backups.

Resources

Engine	Image	Memory cap	Idle on a Pi 4
PostgreSQL	postgres:17-alpine	256 MiB	about 45 MiB
MariaDB	mariadb:11.4	256 MiB	about 90 MiB
Redis	redis:7.4-alpine	64 MiB (maxmemory 48mb, no eviction)	about 4 MiB

An engine starts only when an app asks for it, then keeps running. The memory caps are enforced only when the memory cgroup is on (DOCKER.md); the engines' own settings (small buffer pools, 40 connections) keep them below the caps anyway.

Major versions are pinned: piway never moves an engine to a new major version by itself (see Upgrading).

SD card or SSD

Databases write a lot, and an SD card wears out and is slow at small synchronous writes. piway looks at the disk the engine's data is on when it first starts it:

	On an SD card	On an SSD or other disk
PostgreSQL	synchronous_commit=off, checkpoint_timeout=15min	full durability
MariaDB	innodb_flush_log_at_trx_commit=2	1 (flush every commit)
Redis	RDB snapshots (after 15 min with a change, 5 min with 100, 1 min with 10 000)	append-only file, synced every second

On an SD card, a power cut can lose the last second or so of writes, but never corrupts the database. For anything that matters, put Docker's data on a USB SSD: it is faster, lasts far longer, and piway then keeps every write. Move Docker's data directory there (data-root in ~/.config/docker/daemon.json for rootless Docker), then deploy again. Engines created before the move keep the settings they started with; remove their containers to pick up the new ones (the data is in their volumes and stays).

Backups

Every night at 03:30 piway makes a logical backup of each app's database on each engine that runs, one gzipped file per app and engine, and keeps the newest 7:

Engine	How	File
PostgreSQL	pg_dump as plain SQL, without owners or grants	~/.local/state/piway/db-backups/postgres/<app>/<time>.sql.gz
MariaDB	mariadb-dump --single-transaction (a consistent snapshot, without locking the app out)	…/mariadb/<app>/<time>.sql.gz
Redis	every key under <app>: with DUMP and its time to live	…/redis/<app>/<time>.redis.gz

A logical dump is consistent while the app keeps writing, and it restores into a newer major version, which a copy of the data directory does not.

piway db backup                 # every app, now
piway db backup blog            # one app
piway db list                   # what there is
piway db restore blog           # the newest backup of blog's database
piway db restore -engine redis -from …/redis/blog/20261016-033000.redis.gz blog

piway db restore asks for the app's name before it starts (-yes skips that, for scripts). It replaces the database: it drops it, makes it again with the same user and password, and loads the dump, so the app sees errors while it runs. Restart the app afterwards. A PostgreSQL dump is loaded as the app's own role, so it owns what it had; for Redis, the app's keys are cleared first and the backed-up ones come back with their time to live.

Copy backups off the Pi. A backup on the same SD card is lost with the card. piway backup (BACKUP.md) includes the database backups along with everything else; copy its archive elsewhere, or sync ~/.local/state/piway/db-backups/ to another machine.

Upgrading

A new piway may pin a newer major version for new engines; engines that already run keep theirs until you move them. Data directories do not carry across major versions, so the move is a dump and restore:

piway db upgrade postgres                       # to the version this piway pins
piway db upgrade -to postgres:18-alpine postgres

1. Every app's database on the engine is backed up (to the usual backup directory).
2. The old server stops and is kept, renamed piway-postgres-17.
3. The new version starts on a new volume (piway-db-postgres-18), each app gets its database and user back with the same password (so its DATABASE_URL does not change), and its backup is loaded.

If anything in step 3 fails, the new server and its volume are removed and the old one comes back as it was. On success the old server is removed, and its volume stays until you remove it (docker volume rm piway-db-postgres-17) once the apps work.

Apps see errors while it runs, and should be restarted afterwards. It asks for the engine's name before it starts (-yes skips that). A deploy that needs the engine waits until the upgrade is done.

Data

Each engine keeps its data in a volume named after it and its major version, such as piway-db-postgres-17. Removing an app or its repository leaves its database, and its backups, in place. To delete a database for good:

piway db drop blog                  # every engine blog has a database on
piway db drop -engine redis blog

It deletes the database and its user, and asks for the app's name first. The backups stay, so piway db restore can bring it back. If the app still lists the engine in run.databases, its next deploy makes an empty database again.