Skip to main content

Databases · Intermediate · 14 min

How to Back Up and Restore a PostgreSQL Database with pg_dump

By Padding · Updated 15 Nov 2026
Ubuntu 26.04.1 LTS · PostgreSQL 18

You will back up a PostgreSQL 18 database in three formats, restore it into a fresh database, check the copy matches, and schedule a nightly backup that deletes old files.

On this page

A backup you have never restored is a guess. This tutorial covers both halves: you will dump a database with pg_dump, restore it into a second database, and compare the two, so you know the file works before you need it.

pg_dump makes a logical backup of one database. It reads the data through a normal connection while the database stays online, and it writes either SQL text or an archive that pg_restore can read. It does not copy the server's files, and it does not include roles or settings that belong to the whole cluster, so you will back those up separately.

The tutorial assumes PostgreSQL 18 is installed as in /tutorials/install-postgresql-18-on-ubuntu-26-04.

Step 1 · Create a database to back up

You need something worth backing up. Create a database called shop with two small tables and a few rows. Run the commands as the postgres superuser:

Bash
sudo -u postgres createdb shop
sudo -u postgres psql -d shop <<'SQL'
CREATE TABLE customers (id serial PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (
  id serial PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers (id),
  total_cents integer NOT NULL
);
INSERT INTO customers (name) VALUES ('Ada'), ('Grace'), ('Linus');
INSERT INTO orders (customer_id, total_cents)
  SELECT (g % 3) + 1, 1000 + g FROM generate_series(1, 500) AS g;
SQL

Check it worked

Bash
sudo -u postgres psql -d shop -Atc "SELECT count(*) FROM customers;" -c "SELECT count(*) FROM orders;"

The output is 3 then 500. -A and -t strip the table formatting so the numbers are easy to compare later.

Step 2 · Take a plain SQL backup

The default pg_dump output is a text file of SQL statements. Make a folder for backups and dump into it.

Backups hold all your data, so make the folder private first. The postgres user cannot read your home folder, so start from a folder it can reach, which avoids a "could not change directory" warning:

Bash
mkdir -m 700 ~/backups
cd /tmp
sudo -u postgres pg_dump shop > ~/backups/shop.sql

The redirect is run by your shell, so the file belongs to you and the postgres user does not need write access to your home folder. Running from /tmp keeps postgres from trying to enter your home folder, which it cannot read. Look at the start of the file:

Bash
head -n 30 ~/backups/shop.sql

You see comments with the server and pg_dump versions, some SET lines, then CREATE TABLE statements. The data follows as COPY ... FROM stdin; blocks. Plain files are easy to read and to edit, but you can only restore them whole, with psql, and they are larger than the other formats.

Step 3 · Use the custom format for real backups

The custom format (-Fc) is compressed and lets pg_restore pick tables, restore in parallel and list what is inside. For most backups this is the format to use.

Bash
sudo -u postgres pg_dump -Fc shop > ~/backups/shop.dump
ls -lh ~/backups

The .dump file is a binary archive, so do not open it in an editor. List what is in it:

Bash
pg_restore --list ~/backups/shop.dump | head -n 30

pg_restore --list prints a table of contents: one line per table, sequence, constraint and data block. Nothing is restored, and it needs no database connection.

Two other formats exist. The directory format (-Fd) writes one file per table and is the only format that pg_dump can write using several parallel jobs:

Bash
sudo -u postgres pg_dump -Fd -j 2 --file=/tmp/shop_dir shop
ls /tmp/shop_dir

The --file option names a folder that must not exist yet. Because postgres creates it, the path has to be somewhere that user can write, such as /tmp. Use -Fd -j 4 on a large database to cut the dump time. The tar format (-Ft) also exists, but it has no compression and no advantage over the others.

Step 4 · Back up roles with pg_dumpall

pg_dump saves one database. Users (roles) belong to the whole cluster, so a restore onto a new server will fail on OWNER TO appuser lines unless the roles exist first. Save them with pg_dumpall:

The file will contain password hashes, so create it private from the start. umask 077 makes every file written in that subshell readable by you alone:

Bash
(umask 077; sudo -u postgres pg_dumpall --globals-only > ~/backups/globals.sql)
ls -l ~/backups/globals.sql

--globals-only writes roles and tablespaces and leaves the databases out. The listing shows mode -rw-------.

On a new server, run globals.sql with psql first, then restore each database.

Step 5 · Restore into a fresh database

Never restore over the live database to test a backup. Create an empty database with another name and restore into that.

Bash
sudo -u postgres createdb shop_restored
sudo -u postgres pg_restore --dbname=shop_restored --no-owner --exit-on-error < ~/backups/shop.dump

Here --no-owner skips the ALTER ... OWNER TO commands, so the objects belong to whoever runs the restore. Drop it if the original owners exist on this server and you want them kept. --exit-on-error stops at the first failure instead of printing errors and carrying on, which makes a broken restore obvious.

Restoring the plain SQL file uses psql with -v ON_ERROR_STOP=1 for the same reason:

Bash
sudo -u postgres createdb shop_from_sql
sudo -u postgres psql -v ON_ERROR_STOP=1 -q -d shop_from_sql < ~/backups/shop.sql

Check it worked

Compare row counts in the three databases:

Bash
for db in shop shop_restored shop_from_sql; do
  echo "$db:"
  sudo -u postgres psql -d "$db" -Atc 'SELECT count(*) FROM customers;' -c 'SELECT count(*) FROM orders;'
done

Each database prints 3 and then 500. For a tighter check on a real database, compare a checksum of a query's output in both databases, for example the sum of total_cents.

Bash
sudo -u postgres psql -d shop -Atc 'SELECT sum(total_cents) FROM orders;'
sudo -u postgres psql -d shop_restored -Atc 'SELECT sum(total_cents) FROM orders;'

The two numbers are the same.

Troubleshooting

  • pg_dump: error: aborting because of server version mismatch: the pg_dump you ran is older than the server. Install the client for the server's version, for example postgresql-client-18, and run it from /usr/lib/postgresql/18/bin/.
  • pg_restore: error: ... role "appuser" does not exist: restore globals.sql first, or use --no-owner.
  • createdb: error: database creation failed: ERROR: database "shop_restored" already exists: pick another name, or drop the old one with dropdb once you are sure it holds nothing you need. To restore over an existing database on purpose, add --clean --if-exists. That drops each object before recreating it, so any rows added since the backup are lost. Use it only on a database you are willing to overwrite.
  • pg_dump: error: connection ... Peer authentication failed: run the command as the postgres system user with sudo -u postgres, or connect over TCP with -h 127.0.0.1 -U <role> and a password.

Step 6 · Back up every night and delete old files

Put the dump in a script so a schedule can run it. The script writes a dated file, writes to a temporary name first so a half-written dump is never mistaken for a good one, and removes backups, and any leftover .partial files, older than 14 days.

Bash
sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo tee /usr/local/bin/pg-backup.sh > /dev/null <<'SCRIPT'
#!/bin/bash
set -euo pipefail

DB="${1:?usage: pg-backup.sh <database>}"
DIR=/var/backups/postgresql
STAMP="$(date +%F-%H%M)"

pg_dump -Fc "$DB" > "$DIR/$DB-$STAMP.dump.partial"
mv "$DIR/$DB-$STAMP.dump.partial" "$DIR/$DB-$STAMP.dump"

find "$DIR" \( -name "$DB-*.dump" -o -name "$DB-*.dump.partial" \) -mtime +14 -delete
SCRIPT
sudo chmod 755 /usr/local/bin/pg-backup.sh

Run it once by hand, as the postgres user, so you see it work before cron does it for you:

Bash
sudo -u postgres /usr/local/bin/pg-backup.sh shop
ls -l /var/backups/postgresql

One dump named shop-<date>-<time>.dump is listed. Now schedule it. A file in /etc/cron.d names the user to run as, and adding one does not touch anyone's crontab:

Bash
echo '15 2 * * * postgres /usr/local/bin/pg-backup.sh shop' | sudo tee /etc/cron.d/pg-backup

The line runs at 02:15 every day. Cron ignores files in /etc/cron.d whose names contain a dot, so keep pg-backup without an extension. Cron mails or drops any output, so check the folder the next morning and look at the file dates.

Backups on the same disk as the database die with the server. Copy the folder to another machine or to object storage with rsync or rclone, and test a restore from that copy now and then.

Next steps

  • Find out why a query is slow: /tutorials/speed-up-slow-postgres-queries-with-explain.
  • Lock the server down before it holds real data: /tutorials/harden-ssh-on-ubuntu-26-04.
  • The reference for every flag is the PostgreSQL manual pages for pg_dump, pg_restore and pg_dumpall, at postgresql.org/docs/18.

Need this done for you?

Padding does this work for growing companies, from the first diagnosis to a system that keeps working.

Platform Engineering

Get new tutorials

New tutorials, about twice a month.