---
title: "Postgres"
description: "Logical dumps of a Postgres database, local or cloud."
url: "https://saved.sh/docs/backups/sources/postgres"
---

Runs `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 [#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.

<Callout type="warn">
  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.
</Callout>

## Fields [#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`                                           |

```yaml title="config.yaml"
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`                                                                      |

```yaml title="saved.yaml"
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 [#a-least-privileged-role]

`pg_dump` needs to read everything it dumps. The smallest role that reliably works:

```sql
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.

<Callout>
  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.
</Callout>

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 [#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 [#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 [#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 [#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.

<Callout type="warn" title="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.
</Callout>

### `pg_dump` on a hot standby can be cancelled mid-run [#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 [#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 [#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](/docs/backups/compression) off
for this source costs you almost nothing.

## Restoring [#restoring]

```bash
sctl restore postgres <artifact-id> --host localhost --database scratch --user postgres
```

See [Recover a Postgres artifact](/docs/recover/restore-postgres) for restoring by hand,
selectively, and without our CLI.