Backing Up & Restoring PostgreSQL

A backup is only useful if you can actually restore it. PostgreSQL ships with a small family of command-line tools — pg_dump, pg_dumpall, pg_restore, and psql — that turn a live database into a file you can store safely and bring back later. In this page you will learn the different backup formats, when to pick each one, how to restore them, and how to schedule backups with cron so they run while you sleep. Most importantly, you will learn to test your restores, because a backup you have never restored is just a hopeful guess.

PostgreSQL is the popular open-source relational database (a database that stores data in tables with rows and columns). These tools are installed automatically with the postgresql-client package on Ubuntu, so on a server running PostgreSQL they are already available.

Logical vs physical backups — which one this page covers

There are two broad ways to back up PostgreSQL.

Type	What it captures	Tools	When to use
Logical	The SQL needed to recreate your data (a “dump”)	pg_dump, pg_dumpall, pg_restore, psql	Single databases, moving between servers/versions, most small-to-medium setups
Physical	A byte-for-byte copy of the data files plus WAL logs	pg_basebackup, point-in-time recovery	Very large databases, high-frequency recovery points, replication

This page covers logical backups, which are the right default for almost everyone learning DevOps. They are portable (you can restore a dump from PostgreSQL 16 into PostgreSQL 17), human-readable in plain format, and simple to automate. When NOT to use them: terabyte-scale databases where dumping takes hours — reach for physical backups and point-in-time recovery there.

pg_dump in plain SQL format

pg_dump backs up a single database. The simplest format is plain SQL — a text file full of CREATE TABLE and INSERT statements.

Run the dump as the postgres system user (the admin account created when PostgreSQL is installed):

sudo -u postgres pg_dump shopdb > /tmp/shopdb.sql

Use sudo -u postgres so you connect with the operating-system postgres account, which has peer authentication and needs no password. Never put a database password directly on the command line — it shows up in your shell history and in ps output for any other user to see.

Look at what you produced:

ls -lh /tmp/shopdb.sql
head -n 20 /tmp/shopdb.sql

Output:

-rw-rw-r-- 1 postgres postgres 48K Jun 15 09:14 /tmp/shopdb.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 16.3 (Ubuntu 16.3-0ubuntu0.24.04.1)
-- Dumped by pg_dump version 16.3
SET statement_timeout = 0;
SET lock_timeout = 0;
...

When to use plain SQL: small databases, when you want to read or edit the dump, or when restoring into a different database engine. It is the most portable format but offers no parallelism and no selective restore.

pg_dump in custom format

The custom format is compressed and, crucially, lets you restore parts of a dump and restore in parallel. Use the -Fc flag (format = custom):

sudo -u postgres pg_dump -Fc shopdb > /var/backups/shopdb.dump

The .dump file is binary, so you cannot read it with cat — you inspect it with pg_restore --list:

sudo -u postgres pg_restore --list /var/backups/shopdb.dump | head

Output:

;
; Archive created at 2026-06-15 09:20:11 UTC
;     dbname: shopdb
;     TOC Entries: 41
;     Compression: -1
;     Format: CUSTOM
;
215; 1259 16456 TABLE public orders postgres
216; 1259 16461 TABLE public customers postgres

When to use custom format: this is the recommended default for backups you intend to restore with tooling. It is smaller, faster to restore (with -j for parallel jobs), and lets you restore a single table if disaster strikes only one.

Restoring a backup

How you restore depends on the format you dumped.

Restore a plain SQL dump with psql

A plain .sql file is just SQL, so you feed it to psql. First create an empty target database, then load the file into it:

sudo -u postgres createdb shopdb_restored
sudo -u postgres psql shopdb_restored < /tmp/shopdb.sql

Output:

SET
SET
CREATE TABLE
ALTER TABLE
COPY 1284
CREATE INDEX

Restore a custom-format dump with pg_restore

For a .dump file, use pg_restore. The -d flag names the target database, and -j 4 runs four parallel restore jobs to speed things up:

sudo -u postgres createdb shopdb_restored
sudo -u postgres pg_restore -d shopdb_restored -j 4 /var/backups/shopdb.dump

To restore into a database that already exists and start fresh, add --clean --if-exists so existing objects are dropped first:

sudo -u postgres pg_restore -d shopdb --clean --if-exists /var/backups/shopdb.dump

Backing up everything with pg_dumpall

pg_dump skips global objects — roles (database users) and tablespaces — because those live at the server level, not inside one database. To capture them, use pg_dumpall. A common pattern is to dump just the globals, then dump each database separately:

sudo -u postgres pg_dumpall --globals-only > /var/backups/globals.sql

Or dump the entire cluster (all databases plus globals) in one plain-SQL file:

sudo -u postgres pg_dumpall > /var/backups/all-databases.sql

When to use pg_dumpall --globals-only: always pair it with per-database custom dumps. That way you keep the fast, selective custom format for your data and still capture the roles and permissions needed to restore cleanly onto a brand-new server.

A cron-scheduled backup script

Manual backups get forgotten. Cron (the Linux job scheduler) runs them automatically. Create a script that dumps each database, keeps the last 7 days, and logs what it did:

sudo install -d -o postgres -g postgres /var/backups/postgres
sudo nano /usr/local/bin/pg-backup.sh

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgres"
KEEP_DAYS=7
STAMP=$(date +%Y-%m-%d_%H%M%S)

# Capture roles/tablespaces once per run
pg_dumpall --globals-only > "$BACKUP_DIR/globals_${STAMP}.sql"

# Dump every non-template database in custom format
for db in $(psql -At -c \
  "SELECT datname FROM pg_database WHERE datistemplate = false AND datname <> 'postgres';"); do
  pg_dump -Fc "$db" > "$BACKUP_DIR/${db}_${STAMP}.dump"
done

# Delete backups older than KEEP_DAYS
find "$BACKUP_DIR" -type f -mtime +"$KEEP_DAYS" -delete

echo "$(date): backup complete" >> "$BACKUP_DIR/backup.log"

Make it executable and run it once by hand to confirm it works:

sudo chmod +x /usr/local/bin/pg-backup.sh
sudo -u postgres /usr/local/bin/pg-backup.sh
ls /var/backups/postgres

Output:

backup.log  globals_2026-06-15_021500.sql  shopdb_2026-06-15_021500.dump

Now schedule it. Edit the postgres user’s crontab so the job runs as postgres (peer auth, no password):

sudo crontab -u postgres -e

Add this line to run every day at 02:15:

15 2 * * * /usr/local/bin/pg-backup.sh

Test your restores — do not skip this

A backup file that has never been restored is unproven. Schedule a regular restore drill: take last night’s dump and load it into a throwaway database.

sudo -u postgres createdb restore_test
sudo -u postgres pg_restore -d restore_test /var/backups/postgres/shopdb_2026-06-15_021500.dump
sudo -u postgres psql restore_test -c "SELECT count(*) FROM orders;"
sudo -u postgres dropdb restore_test

Output:

 count
-------
  1284
(1 row)

If that row count matches production, your backup is genuinely recoverable. If pg_restore errors or the count is wrong, you have found the problem before a real outage — which is exactly the point.

Best Practices

• Use custom format (-Fc) for routine backups and pair it with pg_dumpall --globals-only to capture roles and permissions.
• Never pass passwords on the command line. Use sudo -u postgres for local peer auth or a ~/.pgpass file for remote connections.
• Store backups off the database server — copy them to object storage or another host so a disk failure does not take your backups with it.
• Keep a retention policy (e.g. 7 daily, 4 weekly) and prune old files automatically with find -mtime.
• Run a restore drill on a schedule and check row counts, not just that the command exited cleanly.
• Monitor the backup log and alert if a nightly run is missing — silent backup failure is the most common cause of data loss.
• Match or exceed the source PostgreSQL major version when restoring; dumps restore forward across versions, not backward.

Related Topics

• What is PostgreSQL
• Installing PostgreSQL
• PostgreSQL Users & Databases
• MySQL Backup & Restore
• Automating Database Backups