Data and mail
Managed databases
stackd runs MySQL, PostgreSQL, Redis and MongoDB for your apps, each as a single server or highly available, and links them to apps with their connection details in the environment. Sharded databases have their own page, Sharded databases, and so do object storage, volumes and mail.
Kinds
A database has an engine (mysql, postgres, redis or mongo) and a
topology (single, ha, or sharded for MySQL and PostgreSQL):
| Kind | Members | Failover |
|---|---|---|
| MySQL single | 1 | none |
| MySQL cluster | 3–9, odd | automatic |
| PostgreSQL single | 1 | none |
| PostgreSQL ha | 3–9, odd | automatic |
| Redis single | 1 | none |
| Redis ha | 3–9, odd | automatic |
| MongoDB single | 1 | none |
| MongoDB ha | 3, 5 or 7 | automatic |
| MySQL sharded (Vitess) | shards × 1 or 3, plus 1 or 3 control | automatic, per shard |
| PostgreSQL sharded (Citus) | (workers + 1) × 1 or 3 | automatic, per group |
A highly available database needs a majority of its members up to take writes.
Linking apps
Linking an app gives it its own user, plus a schema (MySQL) or a database
it owns (PostgreSQL), and these variables: <PREFIX>_URL, _RO_URL,
_HOST, _PORT, _RO_PORT, _USER, _PASSWORD and _NAME. They point
at a router on the app's own server:
- MySQL:
mysql-<db>, 6446 read/write, 6447 read-only. - PostgreSQL:
postgres-<db>, 5432 read/write, 5433 read-only. - Redis:
redis-<db>, 6379 read/write, 6380 read-only. Redis links get_DB(the app's database number) instead of_NAME, and the prefix defaults toREDIS. - MongoDB:
mongo-<db>, 27017 read/write, 27018 read-only. The prefix defaults toMONGODB, and_URIrepeats_URL. The URLs includedirectConnection=true; keep it. The read-only one addsreadPreference=secondaryPreferred.
On a single server the read-only port reaches the same server, so an app
written against _RO_URL keeps working if the database later becomes a
cluster.
On a highly available database, reads on the read-only port go to the
member on the app's own server when it can serve them, so on a mesh across
regions they cost no round trip. Writes go to the primary, wherever it is.
A replica's reads can trail the primary by milliseconds: apps reading back
what they just wrote should read from _URL.
Users and schemas can be named when linking (username, database_name)
on MySQL and PostgreSQL that aren't sharded; see Templates.
The tenant-schema option (create_schemas) is MySQL only: a PostgreSQL app
already owns its database and can create schemas in it.
- PostgreSQL: app roles can't connect to other apps' databases, or to
postgres. Unlinking drops the role; the data stays. - Redis: each link gets its own user and the lowest free database
number (0–255), so apps' keys don't collide. Apps can't run
@admincommands, FLUSHALL or SWAPDB. This doesn't isolate apps: one can still SELECT another's number. Redis hasmaxmemoryat half the server's RAM andnoeviction: when it's full, writes fail rather than drop keys. - MongoDB: a link's user gets
readWriteanddbAdminon the app's own database. Unlinking drops it; the data stays. Transactions and change streams work on a single server too.
The owner's admin user, with full rights, can be revealed in the panel.
Renaming
Databases, object storage and volumes can be renamed (Settings in the
panel, or PATCH /api/databases/{id} with display_name). The internal
host follows the new name (mysql-<new name>), and linked apps get it in
their variables on their next deploy. The old host keeps working in
between, so apps don't restart. A volume's stores are shown under its new
name (<name>-data, <name>-meta) and can't be renamed on their own. A
new database may take a name another was renamed away from; its host then
gets a short suffix (orders-3f9a).
Databases and users from the panel
Besides links, admins can make databases and users on a database's page, for tools and people rather than apps: MySQL schemas and users, PostgreSQL databases and login roles, object storage buckets and access keys. Not on sharded databases or Redis.
- Passwords are shown once. stackd doesn't store them: a new user's password, or a key's secret, is shown when it's made or reset. The lists come from the database itself; what a link made is marked with its app.
- One database per user, read/write or read-only. MySQL: every
privilege on the schema, or SELECT and SHOW VIEW. PostgreSQL: read/write
users of a database made here can all use what any of them creates;
read-only users get SELECT on the public schema's tables and sequences,
now and later ones. Users can't connect to
postgres. Buckets: a key that can write, or only read; the admin key reaches every bucket. - Dropping a database drops everything in it on every member (a bucket only once it's empty). Keys can't change their secret: make another.
- What an app's link made can only be dropped with
?unlink=true(the panel asks first): the app is unlinked, which drops its user, takes away the variables its template made from the link (a Laravel app'sDB_*) and redeploys it; then its schema is dropped too, if that's what was asked. Its tenants' schemas (<schema>_*) drop like any other, the app staying linked. stackd's own users can't be dropped.
Backups
Backups go to an S3-compatible destination (endpoint, region, bucket,
prefix and keys). One destination can serve any number of databases: each
writes under <prefix>/<database>/ and keeps its own schedule, retention
and pause switch. Setting up backups offers the destinations other
databases use (GET /api/backup-targets, never with secrets), or a new
one. Rotating a destination's keys from any database updates them for all.
A destination is deleted, with its keys, once no database is backed up to
it and no backup lives in it.
A backup always restores into a new database of the same engine; the source keeps running. A failed backup or restore is shown as failed.
- PostgreSQL: a dump of every database, taken from a replica when there is one. Each database's dump is consistent on its own, not across databases. After a restore, an app linked to the new database under the same name takes over its old role and data.
- Redis: a snapshot of every database number, from a replica when there is one. Taking one needs free disk space on the member about the size of the snapshot; a restore waits up to the backup timeout (6h).
- MongoDB: a dump of every database. Each collection is as it was when it was dumped, so writes made while a dump ran may be in some collections and not others. For a consistent moment, use point-in-time recovery.
Point-in-time recovery
Single and highly available PostgreSQL and MongoDB databases can be restored to any moment, to the second. Not sharded ones, or Redis.
- Turning it on. The database's Backups tab, or
PUT /api/databases/{id}/pitrwith{enabled}. It needs the database's backups set up: the archive goes to the same destination, under<prefix>/<db>/pitr. No restart either way. - What you can lose. Changes are archived every minute, so at most about the last minute of writes is only on the database.
- Base backups are taken when it's turned on, then every day, and kept for the backup policy's retention. A failure is shown on the card and retried.
- The window.
GET /api/databases/{id}/pitr(or the card) shows the earliest and latest restorable moments, the base backups and the last error. The window starts again after recovery is turned back on, the backup policy is resumed, or the destination changes. - Restoring. Backups → Point-in-time recovery, or
POST /api/databases/{id}/pitr/restore({at, name, server_ids, project_id}, the source's project by default). It makes a new database of the same topology with everything as ofat, users included (apps' users come back unlinked). The source keeps running. Moments before the first base backup, or in the future, are refused with the window. A PostgreSQL moment newer than the latest restorable one waits up to two minutes for the archive to catch up, or is refused with the latest moment that is restorable. - The new database doesn't archive until recovery is turned on for it. A restored PostgreSQL database has the source's superuser and replication passwords.
- Older PostgreSQL members. Members started before point-in-time recovery was available say "started before point-in-time recovery" until they're replaced: a single server's when it's next deployed; a highly available one's by adding a member and removing an old one.
- MongoDB: if archiving falls behind for longer than the oplog holds, the archive starts afresh with a new base backup, and the database says so.
External access
External access lets clients outside the mesh reach a database over TLS.
It's turned on the same way as for MySQL clusters (PUT /external-access),
with ports from 23306, a per-server firewall allowlist, a per-database CA
to download, and the admin user. TLS is required: plaintext connections,
and addresses outside the allowlist, are refused. Apps inside the mesh
connect as before, without TLS.
- PostgreSQL: any server's endpoint, as
admin, withsslmode=verify-fulland the database's CA. Direct TLS works too. - Redis: e.g.
redis-cli --tls --cacert <db>-ca.pem, asadmin. - MongoDB: connect over TLS with the database's CA and
directConnection=true. Turning it on restarts each member once, one at a time, primary last.
Certificates name the servers' public addresses and are reissued automatically when servers come or go.
High availability
- Failover is automatic. Apps keep their variables; the routers send writes to the new primary. A server that comes back rejoins as a replica by itself.
- Switchover moves the primary to another member on purpose.
- Members can be added and removed. A MongoDB member must be a secondary to be removed (switch the primary first). Adding or removing members restarts the routers, dropping app connections once on every server.
- Redis: a primary refuses writes while no replica is connected, so an old primary can't take writes after a failover.
- Reboots: a server holding a MongoDB primary switches it away first, and waits until every member is in sync.
Measured on three servers:
| Redis | MongoDB | PostgreSQL | |
|---|---|---|---|
| Failover | about 8s of failed writes (primary unreachable for 5s, then failed over) | within seconds | about 16–29s of failed writes |
| Switchover | about 3s, with about 6s of refused writes | about 13s | about 6s |
A PostgreSQL cluster of 3 members forms in about 40s, and comes back by itself if every member restarts together.
Replication policy
A PostgreSQL cluster's replication policy decides whether a commit waits
for a replica. It's shown under Overview and editable by administrators
under Databases → the cluster → Settings → Replication policy. It
applies to every database and app in the cluster, including
stackd-panel.
- Prefer availability: asynchronous replication. A failover can lose the last acknowledged commits.
- Balanced (default for new clusters): a commit waits for one replica while one is available; with none, the primary carries on alone. Each commit costs a round trip to the nearest replica.
- Require replicated writes: writes are blocked without a streaming
replica, and one is needed to enable it. For
stackd-panel, losing all replicas can make the panel unavailable, including session renewal, and recovery may need a Patroni command on the server; the UI asks for the panel database's name to confirm.
Changing it needs no restart and may take a few seconds to become active.
Clusters formed by 0.12.17 or earlier stay asynchronous until changed here.
Custom replica counts are shown as custom; selecting a preset replaces
them. Sessions that turn off synchronous_commit don't wait for replicas.
API: GET /api/databases/{id}/replication and administrator-only
PUT /api/databases/{id}/replication with {"policy":"availability"},
{"policy":"balanced"} or {"policy":"strict"}.
etcd TLS for older databases
PostgreSQL ha, Citus and Vitess databases created before etcd's TLS have
no authentication on etcd, whose port is reachable from the mesh and app
containers. Turn it on with POST /api/databases/{id}/etcd-tls. It
restarts every member once, one at a time, switching primaries away
first; expect a few failed writes at the switchovers. A member that
doesn't come back holds the change up (its message says which), and other
changes wait meanwhile.
Requirements
- MySQL: at least 1 GB of RAM, and swap under 2 GB. A cluster's members must all share one architecture (x86_64 or aarch64).
- PostgreSQL: at least 512 MB. A member idles at about 100 MiB.
- Single servers can't be drained: a drain of a server holding one is refused (409) and names the database.
Limitations
- Redis has no point-in-time recovery, and apps on one Redis can read each other's database numbers.
- Redis on small servers may log warnings about background saves
without
vm.overcommit_memory = 1, which stackd doesn't set on the host. - PostgreSQL databases created by 0.5.0 can't be backed up. Replace each member (add one, remove the old one); a single server would need re-creating from a dump.
- No external access for single MySQL servers.
- Citus replicates asynchronously within each group.
- Losing etcd's majority for good (two of three servers gone) needs
manual recovery:
etcd --force-new-clusteron a survivor. - A single server can't grow into a cluster. Make a new database and restore a backup into it.