SafeGrd / PostgreSQL backup

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 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

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 14 and later
CREATE ROLE backup LOGIN PASSWORD 'a long random password';
GRANT pg_read_all_data TO backup;

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:

# PostgreSQL 13: once per schema
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;

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:

pg_dump --format=custom --no-owner --file=app.dump "postgres://backup@db.internal:5432/app"

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

FormatWhat it isWhen
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

FlagWhy
--no-ownerLeave out ALTER ... OWNER TO. The restore target may not have the same roles, and without this flag each missing role is an error.
--no-privilegesLeave out GRANT statements, for the same reason. Keep them when you restore to a server with the same roles.
--schema=publicOne schema. A managed provider's own schemas (Supabase's auth, storage, realtime) often cannot be restored into another project; see Supabase backup.
--table=public.ordersOne 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=4Dump four tables at once. Directory format only. Each job is a connection and a snapshot, so the role needs that many connections.
--snapshot=NAMEDump 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-deferrableWait 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:

# roles, memberships and passwords only, as SQL
pg_dumpall --globals-only --file=globals.sql "postgres://postgres@db.internal:5432/postgres"

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:

age-keygen -o backup-key.txt # on your laptop; prints the public key
pg_dump -Fc --no-owner "$DB_URL" | age -r age1... | aws s3 cp - "s3://bucket/app/$(date -u +%F).dump.age"

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

# /etc/cron.d/pg-backup: 02:15 UTC every night
15 2 * * * backup /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

Three things a cron line does not give you, and a script has to:

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.

# custom or directory format
createdb app_restored
pg_restore --exit-on-error --no-owner --dbname=app_restored app.dump
# plain format: psql, one transaction, stop on error
psql -v ON_ERROR_STOP=1 --single-transaction --dbname=app_restored --file=app.sql

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:

SELECT schemaname, relname, n_live_tup FROM pg_stat_user_tables ORDER BY 1, 2;

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.

ProviderConnect withWhat its own backup does not give you
SupabaseThe session pooler on port 5432 with sslmode=require; the direct host is IPv6 onlyA copy outside the project: daily backups are kept 7 to 30 days on paid plans and go when the project does
NeonThe direct endpoint, not the -pooler oneA copy outside Neon, or a restore past the plan's history window
RailwayThe private URL from a service in the project, or the TCP proxyA copy outside the project: volume backups restore only into it
RenderThe external URL with sslmode=requireRecovery on the Free plan, or beyond 3 or 7 days
Fly.ioA Machine on the organization's private networkA file you hold, for Fly Postgres or Managed Postgres
DigitalOceanThe public or VPC host with sslmode=require, from a trusted sourceMore than 7 days, or a download
Amazon RDS and AuroraA read replica, with sslrootcert=global-bundle.pem, from inside the VPCA copy outside the AWS account

Mistakes that only show up at restore time

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:

createdb recovered
safegrd restore --snapshot SNAPSHOT_ID --target "postgres://user:pass@host:5432/recovered"

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.

Adding a database with Back up on SafeGrd. The first backup runs at once, and a Fire Drill loads it into another PostgreSQL database and checks every table's row count. A Full backup can be downloaded from its snapshot. The waits are cut.

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