Back up and restore PostgreSQL with pg_dump

A database backup you've never restored is a guess, not a backup. PostgreSQL ships with everything you need to do this right: pg_dump to take backups, pg_restore to bring them back, and cron to make it automatic. In this guide you'll set up a nightly custom-format backup of a Postgres database, learn when plain SQL dumps are the better choice, and rehearse an actual restore so you know the whole thing works before you need it.

Prerequisites

• A running PostgreSQL server (version 13 or newer is what we'll assume, but these commands have worked the same way for years).
• Shell access to a machine that can reach the database, with the Postgres client tools installed. pg_dump --version should print something.
• A database user with read access to the database you're backing up. The database owner or a superuser works.
• Somewhere to put the backup files with enough free disk space. Rough rule: budget at least the size of the database, though dumps usually compress well below that.

Step 1: Take your first dump in custom format

pg_dump connects to a live database and writes out its contents. It doesn't block normal traffic; it takes a consistent snapshot from the moment it starts. The flag that matters most is -F, the output format. Use c for custom format:

pg_dump -Fc -h localhost -U appuser -d appdb -f appdb.dump

Breaking that down: -Fc selects custom format, -h and -U are the host and user, -d is the database, and -f is the output file. You'll be prompted for a password unless you've set one up in ~/.pgpass (more on that in the cron step).

Custom format is compressed by default and, more importantly, it's selective on restore: you can pull out a single table from it later without touching the rest. That combination is why it's our default.

Step 2: Know when plain SQL is the better format

The other common format is plain SQL, which is just a text file of the commands needed to rebuild the database:

pg_dump -h localhost -U appuser -d appdb > appdb.sql

Here's how we choose between them:

• Custom format (-Fc): compressed, restores with pg_restore, lets you restore selected tables, and supports parallel restore with -j. Use it for routine scheduled backups. This is the workhorse.
• Plain SQL: human-readable, greppable, editable, and restores with plain psql anywhere. Use it when you want to inspect or hand-edit what's inside, or when moving data between very different Postgres versions and you want to see exactly what will run.

One more tool worth knowing: pg_dumpall captures cluster-wide objects like roles and passwords that pg_dump skips. If you ever rebuild a server from scratch, you'll want a pg_dumpall --globals-only dump alongside your database dumps.

Step 3: Restore with pg_restore

To restore a custom-format dump into a fresh database:

createdb -h localhost -U appuser appdb_restored
pg_restore -h localhost -U appuser -d appdb_restored appdb.dump

Useful variations:

# Restore only one table
pg_restore -h localhost -U appuser -d appdb_restored -t customers appdb.dump

# Use 4 parallel jobs for a faster restore on big databases
pg_restore -h localhost -U appuser -d appdb_restored -j 4 appdb.dump

# See what's inside a dump without restoring anything
pg_restore -l appdb.dump

For a plain SQL dump, there's no pg_restore involved. Feed it to psql:

psql -h localhost -U appuser -d appdb_restored -f appdb.sql

Step 4: Schedule it with cron

First, set up password-less authentication so cron can run unattended. Create a ~/.pgpass file for the user the job will run as:

echo "localhost:5432:appdb:appuser:YOUR_PASSWORD" >> ~/.pgpass
chmod 600 ~/.pgpass

The chmod 600 matters: Postgres refuses to use the file if it's readable by other users. Next, a small backup script at /usr/local/bin/backup-appdb.sh:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgres"
STAMP=$(date +%Y-%m-%d)

pg_dump -Fc -h localhost -U appuser -d appdb \
  -f "$BACKUP_DIR/appdb-$STAMP.dump"

# Keep 14 days of backups
find "$BACKUP_DIR" -name "appdb-*.dump" -mtime +14 -delete

Make it executable with chmod +x, then add a cron entry with crontab -e:

# Nightly backup at 2:30 AM
30 2 * * * /usr/local/bin/backup-appdb.sh

Two upgrades worth making once this works: ship the files off the machine (a nightly sync to S3, Backblaze B2, or even another server), because a backup sitting next to the database dies with the database. And make failures loud: have the script send an email or a Slack webhook when it exits non-zero, or use a check-in service that alerts when the job doesn't run.

Step 5: Rehearse the restore

This is the step everyone skips, and it's the one that separates having backups from being protected. Once a quarter, do a fire drill:

1. Grab the most recent dump file, ideally from your offsite copy, not the local one.
2. Restore it into a scratch database (a spare server, a laptop, a Docker container, anywhere).
3. Connect and check it: row counts on your biggest tables, a few recent records you know should exist, and whether the application can actually start against it.
4. Time the whole thing. That number is your real recovery time, and it's worth knowing before an outage, not during one.

The failure modes this catches are all real ones we've seen: the cron job that stopped after a password change, dumps that are 0 bytes because the disk filled, and backups that restore fine but are missing a schema nobody remembered adding. Twenty minutes a quarter buys you the right to sleep through the night.

Wrap-up

You now have nightly compressed backups, retention that cleans up after itself, and a tested path back from disaster. For most business databases this setup is genuinely enough. When you outgrow it (multi-terabyte data, or a need to restore to any point in time rather than last night), the next step is continuous archiving with WAL, but don't reach for that complexity until nightly dumps stop being sufficient.