# PostgreSQL (/docs/databases/postgresql)

backupd backs up PostgreSQL 9.2 through 18 with `pg_dump` in its custom format, from a single consistent snapshot. It works with managed Postgres services as long as the database is reachable from the internet over TLS.

## Connection string [#connection-string]

```txt
postgresql://user:password@db.example.com:5432/app?sslmode=require
```

- The scheme is `postgresql://` or `postgres://`.
- The port defaults to `5432`.
- The path is the database to back up. Without one, backupd uses the database named after the user, then `postgres`.
- Percent-encode special characters in the user or password, for example `@` as `%40`.

## TLS [#tls]

backupd always connects with TLS.

| `sslmode` | Result |
| --- | --- |
| not set | `require` is used. |
| `require`, `verify-ca`, `verify-full` | Used as given. |
| `allow`, `prefer` | Upgraded to `require`. |
| `disable` | Rejected: _backupd connects over TLS; remove sslmode=disable_ |

## Permissions [#permissions]

A read-only user is enough. It needs to read every table, sequence and schema you want in the backup. On PostgreSQL 14 and later the simplest option is the built-in `pg_read_all_data` role:

```sql
CREATE ROLE backupd LOGIN PASSWORD 'choose-a-strong-password';
GRANT pg_read_all_data TO backupd;
```

On older versions, grant `CONNECT` on the database, `USAGE` on each schema, and `SELECT` on all tables and sequences in those schemas.

To restore from the app, the target needs a user that can create and replace the objects in the backup.

## What is backed up [#what-is-backed-up]

- One database per connection: every schema, table, view, function, sequence and its data, as `pg_dump` sees it.
- Cluster-wide objects such as roles and tablespaces are not part of a `pg_dump` backup.
- On a primary, backupd opens a read-only transaction and exports its snapshot, so the dump and the row counts see the same moment.

Files are named with the `.pgdump.zst.age` extension: a `pg_dump` custom-format archive, compressed with zstd and encrypted with age.

## Row counts and restore tests [#row-counts-and-restore-tests]

backupd counts each table's rows while it backs up, and the restore test compares them with what comes back. See [Integrity checks and restore tests](/docs/backups/verification).

- Counts are exact when the database has up to 25 million rows in total and a snapshot can be exported, which needs a primary.
- On a standby, or above 25 million rows, counts are estimates and those tables are not compared.
- Counts cover the 1,000 largest tables.

## Restoring [#restoring]

In-app restores use `pg_restore` without owners or privileges, in a single transaction: if anything fails, the target is left as it was. backupd uses `pg_dump` 18, which can dump every server from 9.2 to 18. Restores into servers older than 17 are handled automatically.

To restore yourself, see [Restore without backupd](/docs/restoring/without-backupd).

## Limitations [#limitations]

- No point-in-time recovery or incremental backups. Every backup is a full copy.
- No private networks or SSH tunnels.
- A dump that sends no data for 5 minutes is stopped, so a long lock doesn't keep a snapshot open on your database.