How to back up a PostgreSQL database, and restore it
This page is the whole job: which kind of backup to take, the role to take it with,
the pg_dump command and its flags, where to put the file, how to schedule it,
how to restore it, and how to check that the restore gave you back what you had. The
commands run on PostgreSQL 13 to 18 and work for a database on your own server or on a
managed provider. The last section is how SafeGrd does the same steps on a schedule and
signs the result.
Logical or physical
PostgreSQL has two kinds of backup, and most teams need the first before the second.
- A logical backup is the SQL to recreate the database:
pg_dumpwrites one database,pg_dumpallwrites every database on the server plus the roles. It restores to any server of the same or a newer major version, on any platform, one table at a time if you want. It restores to the moment the dump started and nothing between dumps. - A physical backup is a copy of the data directory plus the write-ahead log
from then on:
pg_basebackuptakes the copy, and a tool such as pgBackRest or WAL-G archives the log, which is what gives you point-in-time recovery. It restores only to the same major version and architecture, all or nothing, and needs the server'sreplicationprivilege and file-system access.
A logical backup is the one to take first. It needs only a database connection, so it works on a managed database where you cannot touch the file system, and it is the one you can restore into a scratch database to check it. Add a physical backup when you need to recover to a point between dumps, for a database written to all day where losing a day is too much.
What you need
pg_dumpat the server's major version or newer. An older one refuses a newer server with server version mismatch. Check both withpg_dump --versionandpsql -c "SELECT version()". On Debian and Ubuntu the client ispostgresql-client-17; on macOS,brew install libpqand put itsbinon yourPATH.- A role that can read every table, made below. Not a superuser, and not the application's role.
- Somewhere the file goes that is not the database server. A backup on the disk the database is on goes with the disk. An S3 bucket in another account is the usual answer; How to back up PostgreSQL to S3 sets one up.
1. Make a backup role
Back up as a role of its own that can read and cannot write. If the credential leaks, nobody can change the data with it. From PostgreSQL 14, one grant covers every table, including tables made later:
PostgreSQL 13 has no pg_read_all_data, so grant each schema and set the default
for tables made later. Without the last line, a table created next month is left out of
every backup after it:
Two things pg_read_all_data does not give you. Row-level security still applies
to it: a table with policies dumps only the rows the policies show the role, which can be
none, with no error. Give the role BYPASSRLS (ALTER ROLE backup
BYPASSRLS) or dump as the tables' owner. And it cannot read password hashes in
pg_authid, so pg_dumpall run as it writes roles without their
passwords.
2. Run pg_dump
One command, run from any machine that can reach the database:
Put the password in ~/.pgpass (a line of host:port:db:user:password,
mode 0600) or in PGPASSWORD. On the command line it is visible to every process
on the machine.
Which format
| Format | What it is | When |
|---|---|---|
plain (the default) | A SQL script. Restores with psql. Readable, and the only one you can edit by hand. | Small databases, migrations between tools, a dump you want to read. |
custom (-Fc) | One compressed archive with a table of contents. Restores with pg_restore, selectively and in parallel. | The default choice for a backup. |
directory (-Fd) | One file per table in a directory. The only format pg_dump can write in parallel (-j). | Databases where the dump itself takes too long single-threaded. |
tar (-Ft) | The directory format in one tar file, uncompressed. | Rarely. Custom does the same, compressed. |
Custom format is compressed as it is written. PostgreSQL 16 and later can use
--compress=zstd or lz4 instead of gzip, which is several times
faster for the same size; older versions take -Z 6 for gzip level 6.
The flags that matter
| Flag | Why |
|---|---|
--no-owner | Leave out ALTER ... OWNER TO. The restore target may not have the same roles, and without this flag each missing role is an error. |
--no-privileges | Leave out GRANT statements, for the same reason. Keep them when you restore to a server with the same roles. |
--schema=public | One schema. A managed provider's own schemas (Supabase's auth, storage, realtime) often cannot be restored into another project; see Supabase backup. |
--table=public.orders | One table. Patterns work: --table='public.events_*'. |
--exclude-table-data='public.audit_log' | Keep the table's definition and skip its rows. For a log table you can afford to lose and cannot afford to dump nightly. |
--jobs=4 | Dump four tables at once. Directory format only. Each job is a connection and a snapshot, so the role needs that many connections. |
--snapshot=NAME | Dump from a snapshot another session exported with pg_export_snapshot(), so a row count taken in that session matches the dump exactly. The restore test guide uses it. |
--serializable-deferrable | Wait for a moment with no serialization anomalies before starting. For a database using SERIALIZABLE transactions. |
Whatever the flags, pg_dump reads the whole database from one snapshot, so a
backup taken while the application writes is still consistent: every table as it was at the
moment the dump began. It takes no locks that block reads or writes, only ones that block
ALTER TABLE and DROP on the tables it is reading.
Roles and other databases: pg_dumpall
pg_dump writes one database and nothing outside it. Roles, their passwords and
their memberships, tablespaces and server settings are cluster-wide, and
pg_dumpall writes them. Dump them once a night beside the database; it is small,
and a restore to a fresh server needs the roles before the tables:
This one needs a superuser to read the password hashes; run as any other role it writes
the roles without them, which is still worth having for the memberships. A dump of
every database (pg_dumpall with no flags) is plain SQL only and cannot restore
one database on its own, so for the data use pg_dump per database.
3. Encrypt it and move it off the server
The dump holds every row in the clear, so encrypt it before it leaves the machine, and
before it sits on the machine's disk if you can. age is a small tool for this
with a public key the server can hold and a private key it never sees:
The pipe means no unencrypted copy touches disk. Keep backup-key.txt in two
places that do not depend on the server or the bucket: it is the key that opens the backups.
The S3 guide has the full script, the bucket's
policy, and a key for the server that can write and never delete.
Object Lock is how the bucket refuses
to delete a backup before its date, for everyone, including whoever holds the root keys.
4. Schedule it
Three things a cron line does not give you, and a script has to:
- Fail when any stage fails. A shell pipeline's exit status is the last
command's, so
pg_dump | age | aws s3 cpexits 0 whenpg_dumpdies halfway and the upload of the truncated stream succeeds.set -o pipefailat the top of the script fixes that. - Say so when it fails. Cron mails root, which nobody reads. Post to a heartbeat URL on success and alert when the heartbeat stops, or run a daily job that lists the bucket and checks the newest object's date and size.
- Notice when it stops running. A server rebuilt from an image without the cron line backs up nothing and reports nothing. The heartbeat catches this too.
How often: the gap between backups is the most data you can lose. Nightly is the common default; a database written to all day may want every few hours, which is where the size of each dump starts to matter and an incremental format or physical backup earns its setup.
5. Restore
Restore into an empty database, never over the live one, and stop at the first error, so a half-loaded database is not reported as a success.
--jobs=4restores four tables at once from a custom or directory archive, and is the single biggest speedup on a large database. It cannot be combined with--single-transaction, and it needs a file it can seek in, not a pipe.- One table:
pg_restore --table=orders --data-onlyinto a database that already has the table's definition.pg_restore --listprints the archive's table of contents, and--use-listwith an edited copy restores exactly the objects you leave in it. - Versions: the target is the same major version as the source or a newer one. A dump from 17 does not load into 15.
- Extensions: the dump says
CREATE EXTENSIONbut does not carry the extension's code. The target has to have it installed, and some need a superuser to create. - Roles first: on a fresh server, load
globals.sqlbefore the database so owners and grants resolve, or restore with--no-owner --no-privilegesand grant afterwards. - Swap the databases: once
app_restoredchecks out, rename the databases (ALTER DATABASE app RENAME TO app_broken, then the restored one toapp) and point the application at it. Nothing is deleted until you choose to.
6. Check what came back
A restore that loads without error can still be missing data: an empty table loads perfectly. The check that catches it is a row count per table, taken from the same snapshot the dump read, compared with the restored database:
n_live_tup is an estimate and good enough to spot a missing table or an empty one.
For an exact count, How to test that a backup
restores has the query that counts every table inside one exported snapshot and the
pg_dump --snapshot flag that dumps from the same one, so the two numbers can be
equal. Do this on a schedule, not once: the backup that fails is the one taken after the
schema change nobody told the backup script about.
Managed PostgreSQL
Every provider below is PostgreSQL, so pg_dump works against it. Each has one or
two details that break a naive command, and each has a backup of its own that is not a
substitute for a copy you hold.
| Provider | Connect with | What its own backup does not give you |
|---|---|---|
| Supabase | The session pooler on port 5432 with sslmode=require; the direct host is IPv6 only | A copy outside the project: daily backups are kept 7 to 30 days on paid plans and go when the project does |
| Neon | The direct endpoint, not the -pooler one | A copy outside Neon, or a restore past the plan's history window |
| Railway | The private URL from a service in the project, or the TCP proxy | A copy outside the project: volume backups restore only into it |
| Render | The external URL with sslmode=require | Recovery on the Free plan, or beyond 3 or 7 days |
| Fly.io | A Machine on the organization's private network | A file you hold, for Fly Postgres or Managed Postgres |
| DigitalOcean | The public or VPC host with sslmode=require, from a trusted source | More than 7 days, or a download |
| Amazon RDS and Aurora | A read replica, with sslrootcert=global-bundle.pem, from inside the VPC | A copy outside the AWS account |
Mistakes that only show up at restore time
- The role could not see every table. A new schema left out of a
--schemalist, or a table with row-level security dumped with--enable-row-security. The dump exits 0 with the table missing or empty. - The file is truncated. No
pipefail, a full disk, a cut upload.pg_restore --listcatches a file cut off before its table of contents;pg_restore -f /dev/null app.dumpreads every block and catches the rest, with no database. - The wrong database. A connection string pointing at staging, or at a replica that stopped replicating last month.
- The key is on the server you lost. An encrypted backup is as good as your copy of the key.
- The
pg_dumpon the backup host fell behind the server. A managed provider upgrades the server; the host's client stays, and every backup from then on fails with a version mismatch that nobody reads. - Nobody has restored one. The steps are in one person's head.
How SafeGrd does it
SafeGrd's daemon runs the steps above on a schedule and keeps the record. It takes the schema
with pg_dump and the rows over binary COPY from one snapshot,
compresses with zstd and encrypts with age on your host, and writes to your bucket under
Object Lock with a key that can write and never delete. Each backup uploads only the chunks
that changed since the last one. It alerts when a backup fails or does not run. On a
schedule it restores the newest backup into a throwaway database and compares every table's
row count with the manifest written at backup time, then signs the result, so the check in
step 6 happens without anyone remembering to.
Run your first Fire Drill Read the install guide
curl -fsSL https://safegrd.dev/install.sh | sh
safegrd backup --database-url "$DATABASE_URL" --retention-days 14
safegrd verify --snapshot <newest> --sandbox-target "$SANDBOX_URL"
The daemon runs on a server beside the database, in a Docker container, on Kubernetes
through the Helm chart, or in a scheduled GitHub Actions job for a database with no server
beside it. It needs a network path to PostgreSQL and a pg_dump at least as new
as the server; the ghcr.io/safegrd/cli image carries pg_dump 18. A
restore is one command on any host that has the CLI and the key:
The target has to be empty: the restore refuses a database that already holds tables. To put
one schema back into a database that has others, add --schema NAME; one table,
--table public.orders. The whole restore is one transaction, and every table's
row count is checked against the manifest before it commits
(Restore and Fire Drills).
A database with no server beside it, such as one on Supabase, Neon or RDS, can be backed up by SafeGrd itself: add its connection URL with Back up on SafeGrd in the console. Each backup and each drill runs on a machine SafeGrd starts for it, and the first backup starts as soon as the database is added.
Questions
Does it need a superuser?
A read-only role is enough. On PostgreSQL 14 and later, grant it
pg_read_all_data, which covers tables created later as well. Row-level
security still applies to that role, so give it BYPASSRLS or back up as the
tables' owner to capture every row. The backup lists each table RLS applied to
(Databases).
Does it replace pgBackRest or WAL archiving?
SafeGrd takes logical backups, so it restores to the moment of a backup, not to any minute in between. If you need point-in-time recovery, keep pgBackRest or WAL-G for that and run SafeGrd beside it for a tested, encrypted copy in a separate account (SafeGrd vs pgBackRest).
How big a database?
Rows stream over COPY in chunks, so a table larger than the host's memory backs
up without staging it on disk, and an incremental backup uploads only what changed. The
limit in practice is how long the first full read of the database takes against your
schedule. safegrd estimate measures a database and prints what each schedule
would move a month (estimate).
Related
- How to back up PostgreSQL to S3 with pg_dump: the full script, the bucket policy and cron
- How to test that a backup restores: four levels of checking, with the queries
- pg_dump command builder: pick the flags and get the matching restore
- SafeGrd vs pgBackRest: logical backups tested by restoring, or physical ones with point-in-time recovery