Backing up and moving a database

Backing up and moving a database

stado database backup [<name>]
stado database migrate <name> --to fleet [--port <port>] [--host <host>] [--discard-pending]

backup copies a declared postgres database whole into the storage of the machine that runs it, whichever provider hosts the database. It reads the credential item from the owner vault (from any host, through the host channel), reaches the server on the item's session_url with its db_password in libpq's environment (never on a command line), verifies it against the item's ca_certificate, and runs pg_dump of the server's own major version. The backup is a directory ~/.stado/database-backups/<name>/<UTC time>/, readable by its owner only:

file	what it holds
database.dump	the custom-format archive, read back with pg_restore --list before the backup is kept
manifest.json	the source address and provider, the PostgreSQL version, every table whose data the archive holds with its exact row count counted from the archive itself (tables, rows), the source's own counts taken just before the dump (source_tables_before_dump, source_rows_before_dump), the roles and extensions the database uses, the schemas and tables the item's user may not read (left out of the archive and named here), the archive's size and SHA-256
source-server-ca.pem	the certificate authority the source was verified against; migrate adds target-server-ca.pem, the fleet server's

Without a name every declared postgres database is backed up; one that could not be is a row with its cause and the command exits 1.

A database Stado runs on the fleet has no provider taking backups of it, so its backups are a schedule the operator declares, with the retention stated as --keep (Stado assumes none: without it nothing is removed). The schedule runs on whichever host stado schedule places it on, and the backups are kept on that host:

stado schedule create --cron '<cron>' 'stado database backup --keep <n>'
stado database backups

stado database backups [<name>] lists every backup the machine keeps, newest first, from each manifest: when it was taken, the source, its tables, rows, size and SHA-256. A directory whose manifest cannot be read, a backup that stopped part way, is listed as incomplete with why and is never removed by --keep.

migrate moves a declared postgres database that another provider hosts (a Supabase project, an external server) onto a fleet host, with no vendor and no bill:

1. a backup is taken, as above, and kept;
2. the credential item as it stood is kept as <name>-database-previous, its context carrying kept_as {role: "previous", of: <name>-database, at}; Skarbiec refuses a second item holding exactly another's payload, and the marker is what tells the kept copy from the item it was copied from;
3. a fleet PostgreSQL of the source's major version is placed (place --version), on the vault owner unless --host names another, which writes <name>-database with the fleet coordinates;
4. the roles the source has are created (NOLOGIN), the archive is restored without owners but with its grants (the REST API's anon, authenticated and service_role read a table only through them, under its row policies) and creates each extension in the schema the source kept it in; each object the archive defines is dropped first when it already stands (pg_restore --clean --if-exists), so a second run into a pending server replaces the partial copy instead of adding its rows again; the source's extensions the target does not have afterwards, and every line pg_restore printed, are in the report;
5. every table the manifest counted is counted again on the target. A table the archive defines no TABLE for was made by an extension on the source (a hosted provider's net.http_request_queue, vault.secrets); where the target lacks that extension it lacks the table too. One that held no rows loses nothing and is listed under the report's not_carried with why; one that held rows is a mismatch like any other, so no row is left behind unnamed. A table whose target count is not the manifest's but is exactly the rows the archive holds (counted from it) was written on the source between a backup's count and its dump, by a backup made before manifests were counted from the archive: it is listed under counted_outside_the_dump, since no restore brings back rows no backup holds. A move that refuses keeps its report, every pg_restore line included, as restore-report.json beside the backup and names it.

Equal counts end the move: <name>-database holds the fleet coordinates, the declaration and its consumers are unchanged, and a consumer reads the new coordinates on its next connection through stado_database::connect. A table that differs or is missing gives <name>-database the source's coordinates back, keeps the fleet ones as <name>-database-fleet-pending (marked kept_as {role: "fleet-pending", …} the same way), and refuses naming each table; so does a placement that stored the fleet coordinates and then failed to install its unit. A second run restores into the server the pending item names instead of placing another, and deletes the pending item once it succeeds; that holds only for a run that names no --host or --port, or names the pending item's own. A run that names another host or port while a pending item stands is refused naming it, before anything is taken or written: the pending server may stand on a host that cannot run it. --discard-pending retires that server once the backup is in hand (taken, or the kept one accepted), removing its unit <name>-database on the host the item names (or saying it is already absent) and deleting the item, then places anew where the run says; a backup that is refused or fails leaves the pending item for the next run. The report's discarded_pending names both steps, and the data directory under ~/.stado/databases/<name>/ on that host is left in place, as destroy leaves it. An item with no context object is refused before anything is written, since it could not be written back. The source is never changed: deleting a Supabase project afterwards is stado database destroy <name> --delete-project, a separate decision.

A database moved before migrate carried the source's grants has its rows on the fleet but none of the grants, and its REST API answers every request permission denied for table … (42501). Its kept backup's grants alone are carried onto it with

stado database migrate <name> --to fleet --from-backup <directory> --grants-only

which reads the archive's ACL and DEFAULT ACL entries, writes them as grants.list beside the backup and restores only them into the fleet copy the item names, reporting how many entries there were (grants), how many were given (given) and how many failed (failed, the count of errors pg_restore says it ignored), and every line pg_restore printed. When every entry failed no grant was given, and it is refused naming the first errors: create on the fleet server the roles the grants name and restore the objects they grant on (migrate without --grants-only), then run it again. It is also refused for a database that is not on the fleet and for a backup that is not the database's newest kept one or no longer hashes to its manifest.

A move made before migrate restored and counted every table could also record the fleet server on the item and leave it empty: its consumers then read nothing, while every listing reported the database moved. The same command without --grants-only restores the kept backup whole into such a server:

stado database migrate <name> --to fleet --from-backup <directory>

On a database whose item already names a fleet server it installs the extensions the backup names that Stado pins, restores the archive, counts every table against the manifest and answers restored_into_fleet; the item is not rewritten, since it already names that server. A server that holds rows in any of the backup's tables is refused naming them, since a restore would replace what was written there since the backup; a count that differs after the restore is refused with every count and pg_restore's lines kept as restore-report.json beside the backup. stado database list names the hosts that run each fleet database (server=); one listed server=none has no unit, and stado database tls reissue <name> installs it again on its data directory before the restore.

Writes the source takes after the backup are not in the copy, and a consumer that connects while the restore runs reaches the fleet server before its rows are all there (a write it makes there makes a count differ): stop the consumers and the source's writers before migrate. A consumer that talks to Supabase's own HTTP APIs (PostgREST, Auth, Realtime, Storage) with the project's API keys rather than to Postgres keeps reaching the Supabase project; the keys and project URL stay on <name>-database-previous, and the report's rest_api names the rest place that serves the REST API from the fleet (next section).