Back up and restore PostgreSQL

PostgreSQL provides two tools for protecting data. pg_dump saves one database to a single file, and pg_restore rebuilds it from that file. This guide also saves role definitions, which a database dump alone leaves out.

Before you begin

• PostgreSQL installed, with a role named alex and a database named appdb. The install guide covers this.
• SSH access as a sudo user, such as alex.
• Enough free disk space for the dump. Check with df -h ~.
• Somewhere off the server to keep copies. Your computer or a second machine both work.

Step 1: Create a backup folder

Keep the backup files together so they are easy to find and copy.

mkdir -p ~/backups/postgresql

Step 2: Save the roles

pg_dump saves the contents of a database, but not the roles that own or access it. pg_dumpall --roles-only saves every role definition, run as the postgres superuser. The file contains password hashes, so restrict it immediately.

sudo -u postgres pg_dumpall --roles-only > ~/backups/postgresql/roles.sql
chmod 600 ~/backups/postgresql/roles.sql

Step 3: Dump the database in custom format

The -Fc option writes PostgreSQL's compressed custom format. -h localhost forces password login, and -f names the output file. You will be asked for the password of the alex role.

pg_dump -h localhost -U alex -d appdb -Fc -f ~/backups/postgresql/appdb-2026-10-31.dump
ls -lh ~/backups/postgresql

-rw-rw-r-- 1 alex alex 48K Oct 31 09:30 appdb-2026-10-31.dump

To see what a dump contains without restoring it, list its table of contents:

pg_restore --list ~/backups/postgresql/appdb-2026-10-31.dump | head -n 20

The output begins with a short header and then lists each object, such as tables and indexes.

Step 4: Restore into a new database first

Practice restoring into a new database with a different name. That way the live data is never touched while you check the backup. Create the target, then restore into it. --no-owner skips ownership statements, so the restored objects belong to the role you connect as.

sudo -u postgres createdb --owner=alex appdb_restore
pg_restore -h localhost -U alex -d appdb_restore --no-owner ~/backups/postgresql/appdb-2026-10-31.dump

Check that the tables arrived:

psql -h localhost -U alex -d appdb_restore -c "\dt"

             List of relations
 Schema |   Name    | Type  | Owner
--------+-----------+-------+-------
 public | customers | table | alex

Step 5: Restore over the original (only when needed)

To put the data back in place, restore into appdb with --clean --if-exists. Those options drop each object before recreating it.

pg_restore -h localhost -U alex -d appdb --clean --if-exists ~/backups/postgresql/appdb-2026-10-31.dump

Step 6: Restore roles on a rebuilt server

When you rebuild a server, load the saved roles before restoring databases. Errors about roles that already exist, such as postgres, are normal and can be ignored.

sudo -u postgres psql -f ~/backups/postgresql/roles.sql

Troubleshooting

• password authentication failed: retype the password, or confirm the role exists with sudo -u postgres psql -c "\du".
• server version mismatch: your pg_dump is older than the server. Install a matching client with sudo apt install -y postgresql-client-16 on Ubuntu 24.04.
• database "appdb_restore" already exists: drop the test database with sudo -u postgres dropdb appdb_restore, or choose a new name.
• permission denied for table: restore as the owning role, or add --no-owner so ownership is not copied.

Next steps

Schedule the dump with the cron guide so it runs every night, and use the restore testing guide to check that the files actually restore. Copy the dump folder off the server with the rsync guide.