All pages
Databases
A database on pilots is a machine running the stock image, with a volume and a snapshot schedule. That is a genuinely good place to run one, and it is not the same thing as a managed database.
Adding one
pilot add postgres
pilot add postgres --durable-volume --to web
pilot add redis --name cache
pilot add writes a compose entry, a volume, a snapshot policy
with retention, a health gate, a private address and a generated password into your own
project. The fragment lands in your file where you can read it, change it and commit it.
Nothing about it is a second system: the same rollout, the same gate, the same volume, the
same snapshots as any other service.
The password is generated on your machine and stored in your local secret store. It never travels: the compose file references it by name, and the deploy seals it on the way through. There is no route that returns a database password, which is why opening a session is a command you run rather than a call an agent makes.
An agent adds one with the add_database tool on the local MCP server
(pilot mcp), which is this same command. The password is made and stored on your
computer exactly as above, and the tool's answer names only the secret://
references, so the value never passes through the model.
How much you can lose, exactly
| Mode | Loses at most | Costs |
|---|---|---|
wal-archive, the Postgres default
| A minute of writes on a host failure. | Nothing per commit. Segments ship to the volume every minute. |
--durable-volume
| Nothing. | An object-storage round trip on every commit. |
| Every other engine | Nothing. | The data directory is on the volume, so a write is durable before it is acknowledged. |
The default is the first, because a minute of exposure on a machine that
has not crashed beats putting object-storage latency in the commit path of every write.
Choose the other one deliberately. pilot add prints which one you chose, every
time, because choosing it silently would make this paragraph a formality.
Using it
pilot db connect
pilot metrics postgres
pilot db connect opens the engine's own client over a tunnel, or
inside the machine if you have neither the client nor the password.
pilot metrics reports what the engine says about itself: connections against
the limit, cache hits, commits against rollbacks.
Two addresses, and which is which
| Variable | Port | For |
|---|---|---|
DATABASE_URL
| 6432 | The application. A pooler in transaction mode, so hundreds of client connections sit on a handful of server ones. |
DATABASE_URL_DIRECT
| 5432 |
Migrations, LISTEN and NOTIFY, session advisory locks,
temporary tables, and anything meant to outlive a transaction.
|
Recovery
A deploy replaces the database's machine, and in the default mode its data directory goes with the old machine. The new one restores itself: the old replica archives its last write-ahead log first, and the new one finds an empty data directory and a backup in the archive, restores it and replays the log to the end. Nothing committed before the deploy is lost. The old replica also takes a fresh backup before it stops, while it is still serving, so the new one replays only the last few changes and the database is down for seconds rather than for everything written since the last weekly backup.
A project whose database was added before a change to the generated scripts
picks it up with pilot add postgres --refresh, which rewrites those files and
leaves the compose file and the password alone.
pilot db restore postgres --to 2026-09-20T14:31:00Z
Point-in-time recovery replays the write-ahead log onto the newest base backup at or before the moment you name, as a NEW private service on a FORK of the archive volume. The original keeps serving throughout, so you can query both and compare before pointing anything at either. How far back you can go is bounded by the oldest base backup the archive still holds.
The release health gate is the restore gate: the new service only becomes
ready once the engine accepts connections, so a recovery that never reaches its target
never becomes a running release. The command waits until then. An agent has the same thing
as the db_restore tool, and a program as restorePostgres in the
JavaScript SDK or RestorePostgres in the Go one: three public calls, a snapshot of
the archive, a fork of it, and a service created from the database's own release on the
fork, which starts with the database's own health check and settings.
From code
A product that publishes apps for its users does the same thing through the
SDK, with no CLI and no disk. compose.database is pilot add as
values, and compose.deploy is pilot deploy: the fleet plans the
file, then every service is built, migrated, created or updated and rolled out in
dependency order, and a service whose inputs did not change keeps its release.
import { PilotsClient } from '@pilots/sdk'
const pilots = new PilotsClient(process.env.PILOT_API_KEY!)
// pilot add postgres, as values. Keep db.secrets: they are the password.
const db = await pilots.compose.database('postgres')
const compose = {
name: 'shop',
services: {
web: {
build: '.',
environment: { NODE_ENV: 'production', ...db.env },
depends_on: [db.name],
'x-pilots': {
pre_deploy: 'bun run migrate',
min_machines_running: 0, max_machines_running: 4, soft_limit: 20,
},
},
...db.services,
},
volumes: db.volumes,
}
const result = await pilots.compose.deploy({
files: { ...projectFiles, ...db.files, 'compose.yaml': JSON.stringify(compose) },
secrets: db.secrets,
onLine: (e) => console.log(e.step, e.line),
})
DATABASE_URLreaches the pooler on 6432. Migrations and anything using LISTEN/NOTIFY, session advisory locks or temporary tables useDATABASE_URL_DIRECT, which is why the migration above can run aspre_deploy: once, on a throwaway machine made from the new build, before any replica is replaced. A failing migration replaces nothing.- The secrets travel only as the services' sealed environment. Store
db.secretsand pass the same password on every later deploy (compose.database(engine, { password })), or the app is locked out of the database it already has.services.patch(id, { secret_env })sets one by hand. - A change to
min_machines_running,max_machines_running,soft_limit,hard_limitoridle_timeoutalone is applied in place, with no rebuild and no restart.
Importing a dump is a stream into psql on the database's own
machine. pipeStdin sends it in frames the far end accepts and paces itself on
the socket, so a large dump is not held in memory.
const pg = (await pilots.services.list()).find((s) => s.app === 'shop' && s.name === 'postgres')!
const replica = (await pilots.machines.list()).find((m) => m.service_id === pg.id)!
const psql = pilots.machines.execStream(replica.id,
['sh', '-c', 'PGPASSWORD=$POSTGRES_PASSWORD psql -h 127.0.0.1 -U postgres -v ON_ERROR_STOP=1'],
{ stdin: true })
await psql.pipeStdin(dump) // bytes, a string, or any stream of chunks
const exitCode = await psql.wait()
High availability, and whose it is
pilot db ha enable postgres
pilot db ha status postgres
- Only a Postgres written by
pilot addcan become a cluster. The label that allows several volumes with one writer each is written once and cannot be added to a hand-written service. - Each node gets its own volume and lands on a different host where the fleet has one. The address and the connection string do not change: every node runs a proxy on the published port that follows whichever node the cluster manager says is primary, checked every second. No leader is recorded anywhere to read, deliberately, because a replicated row would be stale exactly when it mattered.
- The smallest sensible cluster is two Postgres machines and three coordination members, each with its own volume, all of them billed.
- A failover resets connections that were open to the old primary, which is physics rather than policy: the process they were talking to is gone.
- Turning it off keeps the FIRST node and destroys the rest with their volumes, so it is refused while any other node is primary.
Which half is ours
The configuration, the volume, the snapshots, the rescue onto another host and the placement of nodes are ours. The choice of leader, the tuning, the upgrade and the three in the morning are yours. What we operate is the unabridged version, including the recovery times and what is not automatic.
If you need more than that, use a managed database and point the connection variable at it. An application here talking to a database elsewhere is an ordinary configuration rather than a workaround.