Postgres
Logical dumps of a Postgres database, local or cloud.
View as MarkdownRuns pg_dump in custom format (-Fc) and stores the result. Available as both a local
and a cloud backup.
Custom format is the right default: it is compressed, it restores with pg_restore, and it
lets you restore selectively rather than all-or-nothing.
What is taken
A logical dump of one database: schema and data, as SQL objects rather than as data files.
| Included | Not included |
|---|---|
| Schemas, tables, data, indexes, constraints | Other databases in the cluster |
| Views, functions, triggers, sequences | Roles and users (they are cluster-wide) |
| Extensions the database declares | Tablespaces and physical layout |
| WAL, so no point-in-time recovery between runs |
A logical dump restores into any Postgres of an equal or newer major version, on any platform. That portability is the reason to prefer it over a filesystem snapshot: the backup is not tied to the machine that made it.
Roles are not in the dump. A restore into a fresh cluster needs the roles to exist first, or
--no-owner set so ownership is not assigned at all. Set no_owner and no_privileges if
you expect to restore somewhere the original roles do not exist.
Fields
Local, in the worker's config.yaml under source::
| Key | Required | Notes |
|---|---|---|
database | Yes | |
host, port, user | No | Omitted flags fall back to pg_dump's defaults |
password | No | Passed as PGPASSWORD, never on the command line |
ssl_mode | No | disable, require, verify-ca, verify-full. Passed as PGSSLMODE |
schema | No | Restrict to one schema, as -n |
exclude_tables | No | A list of table names, one -T each |
no_owner | No | true adds --no-owner |
no_privileges | No | true adds --no-privileges |
backups:
"<backup-uuid>":
source_type: postgres
source:
database: app
host: db.internal
port: 5432
user: backup
password: "<password>"
ssl_mode: require
exclude_tables: [audit_log, sessions]
no_owner: true
no_privileges: true--no-password is always passed, so pg_dump fails with a readable error instead of blocking
on a prompt no one will answer.
Cloud, on the backup definition. The same fields, split by sensitivity:
| Half | Fields |
|---|---|
| Public, stored in our database | host, port, database, user, schema, exclude_tables, no_owner, no_privileges |
| Secret, stored in our vault | password, ssl_mode |
backups:
- name: prod-db
kind: cloud
source_type: postgres
schedule: "0 2 * * *"
source:
host: db.example.com
port: 5432
database: app
no_owner: true
ssl_mode: verify-full
credentials:
user: backup
password: "<password>"A least-privileged role
pg_dump needs to read everything it dumps. The smallest role that reliably works:
CREATE ROLE backup WITH LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO backup;
GRANT USAGE ON SCHEMA public TO backup;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO backup;The ALTER DEFAULT PRIVILEGES line is the one people forget. Without it, a table created
next month is invisible to the backup role, and the dump silently omits it.
On Postgres 15 and newer, pg_read_all_data does the same job in one grant:
GRANT pg_read_all_data TO backup;. It is broader, and simpler to keep correct.
The role does not need SUPERUSER, and giving it one to make an error go away usually means
ALTER DEFAULT PRIVILEGES was the real fix.
Consistency
pg_dump takes a consistent snapshot in a single transaction. The dump reflects the database
as of the moment it started, whatever happened during the hours it ran.
It does hold a lock that conflicts with ALTER TABLE, DROP TABLE and other exclusive DDL.
Dump the replica, not the primary
If you have a read replica, point the backup at it. A dump is a long read that holds a
snapshot open for its whole duration, and on the primary that means competing with production
for I/O, blocking exclusive DDL, and holding back VACUUM so dead tuples accumulate for
however many hours the dump runs.
None of that is hypothetical on a large database, and none of it costs you anything to avoid when a replica already exists.
Three things change when you do, and all three are worth setting up deliberately.
Your RPO now includes replication lag
The dump reflects the replica, so it is as old as the lag was when the dump started. Usually that is milliseconds and nobody cares. During the incident where the primary is under load, it is exactly when lag is highest.
A stalled replica backs up stale data, quietly
This is the failure that costs people real data. Replication stops, nothing alerts, and every nightly backup afterwards is a faithful copy of a database frozen at the moment replication broke. The dumps succeed. They are the right size. They are worthless.
Check freshness, not just success
A stalled replica does not produce small backups, it produces identical ones. Watch for a
dump whose size stops changing, not just one that shrinks. On the standby,
pg_last_xact_replay_timestamp() tells you how current it really is, and
pg_is_in_recovery() confirms you are talking to a standby at all.
pg_dump on a hot standby can be cancelled mid-run
A long read on a standby conflicts with WAL replay, and Postgres resolves that by cancelling
your query: canceling statement due to conflict with recovery. On a big database this can
happen hours in, repeatedly.
Two settings fix it, and each has a price:
| Setting | Effect | Price |
|---|---|---|
hot_standby_feedback = on | The standby tells the primary which rows it still needs | Bloat on the primary, which is the thing you moved the dump away from |
max_standby_streaming_delay = -1 | Replay waits indefinitely rather than cancelling | The standby falls arbitrarily far behind while the dump runs |
For a replica that exists to serve backups, max_standby_streaming_delay = -1 is usually the
right trade: lag on that node during the dump window costs nothing, and it keeps the primary
clean. For a replica also serving read traffic, neither answer is free and the dump window
becomes a scheduling decision.
Versions
Use a pg_dump at least as new as the server. A newer pg_dump reads an older server; the
other way round fails, sometimes with a confusing message about unsupported syntax.
For local backups this means keeping the client on your worker current when you upgrade a
database. The worker logs which pg_dump it resolved at startup.
Sizing
The artifact is usually much smaller than the database. Indexes are dumped as definitions rather than data, and custom format compresses.
A rough starting point is a quarter of the on-disk size for an index-heavy OLTP database, though the real number depends entirely on your data. Measure once with a manual trigger rather than guessing, then plan retention against what you actually see.
Custom format is already compressed, so leaving compression off for this source costs you almost nothing.
Restoring
sctl restore postgres <artifact-id> --host localhost --database scratch --user postgresSee Recover a Postgres artifact for restoring by hand, selectively, and without our CLI.