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 to REDIS.
  • MongoDB: mongo-<db>, 27017 read/write, 27018 read-only. The prefix defaults to MONGODB, and _URI repeats _URL. The URLs include directConnection=true; keep it. The read-only one adds readPreference=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 @admin commands, FLUSHALL or SWAPDB. This doesn't isolate apps: one can still SELECT another's number. Redis has maxmemory at half the server's RAM and noeviction: when it's full, writes fail rather than drop keys.
  • MongoDB: a link's user gets readWrite and dbAdmin on 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's DB_*) 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}/pitr with {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 of at, 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, with sslmode=verify-full and the database's CA. Direct TLS works too.
  • Redis: e.g. redis-cli --tls --cacert <db>-ca.pem, as admin.
  • 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-cluster on a survivor.
  • A single server can't grow into a cluster. Make a new database and restore a backup into it.