Automating database backups with pg_dump

pg_dump mydatabase > /backups/mydatabase.sql

This produces an SQL script that fully rebuilds the database if you run it later with `psql`. The result is readable, checkable, and you can search through it with `grep`.

For small databases, this is fine. For larger databases, it gets stuck: the script is unstructured, you can't restore individual tables, and recovery takes a long time because everything happens sequentially.

Step 1: Choose the right format

For production environments, you don't use the plain SQL dump but the custom format:

pg_dump -Fc mydatabase > /backups/mydatabase.dump

`-Fc` stands for custom format. The file is compressed, making it smaller than the SQL variant. More importantly: you can later restore individual tables from it and run parallel recovery with `pg_restore`. The file is not readable as text, so you check it afterward with `pg_restore -l`, not with `less`.

The directory format (`-Fd`) splits the backup into multiple files within a folder and enables parallel dumps:

pg_dump -Fd -j 4 -Z0 mydatabase -f /backups/mydatabase_dir

`-j 4` dumps four tables at once, `-Z0` disables compression. This is faster with databases that have many tables, but you pay in storage space. Azure Document Database for PostgreSQL Flexible Server uses this format in its examples, but for a standalone server, `-Fc` is usually the better choice: one file, compressed, flexibly restorable.

Step 2: Automate with cron

Running backups manually works until you forget. A cron job that runs daily at 02:00 goes in the crontab file of the postgres user:

crontab -e -u postgres

Add this line:

0 2   * /usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump

The backup gets the date in the filename, so you know afterward which dump is from when. The time 02:00 is a choice, not a law: pick a moment when the database has little load, otherwise you'll notice the dump in your query times.

Note the escape `\%` in the cron line. Without that backslash, cron interprets the `%` character as a newline and the job doesn't run as intended.

Step 3: Clean up old backups

A backup every day, kept indefinitely, fills your disk. Set a retention policy in the same cron line:

0 2    /usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump && find /backups -name "mydatabase_.dump" -mtime +7 -delete

The `find` command deletes all dumps older than seven days. Seven days is an initial estimate: decide for yourself how far back you want to go. Each extra day costs storage space; each fewer day costs recovery capability. A company that has to show auditors it keeps backups for a year gets nothing from seven days.

Step 4: Automate with systemd instead of cron

Modern Linux distributions run on systemd, and systemd timers offer an alternative to cron with better logging and dependency management. A timer consists of two files.

Create `/etc/systemd/system/pg_backup.service`:

[Unit]
Description=PostgreSQL backup with pg_dump
[Service]
User=postgres
ExecStart=/usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump
ExecStartPost=/usr/bin/find /backups -name "mydatabase_*.dump" -mtime +7 -delete

Then create `/etc/systemd/system/pg_backup.timer`:

[Unit]
Description=Run PostgreSQL backup daily at 02:00
[Timer]
OnCalendar=--* 02:00:00
Persistent=true
[Install]
WantedBy=timers.target

Activate the timer:

systemctl daemon-reload
systemctl enable --now pg_backup.timer

The difference from cron is in the control: `systemctl status pg_backup.timer` shows when the timer last ran and when it will run next. `journalctl -u pg_backup.service` shows the output of the dump. With cron, you have to dig through log files that often don't even exist.

The `Persistent=true` line is the advantage that cron doesn't know: if the server was off at 02:00, the backup runs immediately after startup. Cron just skips that day.

Step 5: Secure the backups

Backup files contain the same sensitive data as your database. A dump of a customer database in a world-readable location is a data leak waiting for a `chmod`.

Make sure the backup folder is only accessible to the user running the backup:

chown postgres:postgres /backups
chmod 700 /backups

`pg_dump` does not support encryption itself. For encryption, combine it with `gpg`:

/usr/bin/pg_dump -Fc mydatabase | /usr/bin/gpg --encrypt --recipient back-up@example.com > /backups/mydatabase_$(date +\%Y\%m\%d).dump.gpg

This costs you an extra step when restoring — you must decrypt first — but it makes a stolen backup file worthless. For a database with personal data, this is not a consideration but an obligation under the GDPR.

Step 6: Test the restore

A backup you never restore is not a backup but a file. The moment you need it, the dump turns out to be corrupt or incomplete.

Test regularly whether your dumps are usable. Restore the backup into a separate database:

/usr/bin/createdb -U postgres restore_test
/usr/bin/pg_restore -U postgres -d restore_test /backups/mydatabase_20260825.dump

If this runs without errors, the dump is intact. Then remove the test database again:

/usr/bin/dropdb -U postgres restore_test

Schedule this test, just like the backup itself. A monthly restore test catches most problems: a disk silently producing corrupt blocks, a `pg_dump` version that no longer matches the server, a script writing an empty file because the database user's password has changed.

Step 7: Back up all databases with pg_dumpall

`pg_dump` backs up a single database. If you manage multiple databases on one cluster, separate dumps miss the global objects: roles, users, tablespaces. These are not in an individual database but at the cluster level.

`pg_dumpall` covers that:

/usr/bin/pg_dumpall > /backups/cluster_$(date +\%Y\%m\%d).sql

This command dumps all databases and the global objects into one SQL script. The downside: the format is always plain SQL, so no custom format, no parallel restore. For a full cluster backup before an upgrade or migration, it is fine. For daily backups of individual databases, stick with `pg_dump -Fc` and run `pg_dumpall` separately, for example weekly.

Step 8: Limit the backup user's permissions

The user running the backup has access to all data in the database. A compromised backup account is therefore a compromised database.

Create a separate user that can only dump, not write:

CREATE USER backup_user WITH PASSWORD 'een_lang_wachtwoord';
GRANT CONNECT ON DATABASE mydatabase TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

This user cannot change anything, only read. For `pg_dumpall` you need more permissions — it must read all databases and roles — so use that only where necessary.

Verification

Check that the backup was actually created and is readable:

ls -lh /backups/
pg_restore -l /backups/mydatabase_$(date +\%Y\%m\%d).dump | head -20

The first command shows the files with their sizes. The second shows the contents of the dump: if you see tables and data here, the file is not an empty script.

For a systemd timer:

systemctl status pg_backup.timer

This shows the next scheduled run and the last execution.

Frequently asked problems

The cron job runs, but no file is created. The likelihood is high that the postgres user has no write permissions on the target folder, or that the database user's password is incorrect. Check the output by running the cron rule manually as the postgres user and look at the error message. Cron itself often logs nothing useful.

`pg_dump` warns about a version difference. If you use a different `pg_dump` version than the server, it sometimes works but not always. Install the client tools that match your server version, or use `pg_dump` from the server itself.

The backup is much smaller than expected. A dump of an empty database is a few kilobytes. Check whether you are using the correct database name and whether the database is not empty: `psql -c "\dt" mydatabase` shows the tables.

Self-hosting means you are the helpdesk. A backup strategy you do not automate and do not test is a strategy you only discover at the moment it fails. And that moment always comes on a Sunday evening.