Back up and restore PostgreSQL with pg_dump and pg_restore

pg_dump is the standard tool for backing up a single PostgreSQL database. It takes a consistent snapshot while the database stays online, so you do not need to stop your application. This guide covers the two formats worth knowing, how to back up roles that pg_dump leaves out, and how to restore everything on the same server or a new one.

Before you start

• A Linux VPS with PostgreSQL running. See PostgreSQL basics if you are new to it.
• SSH access as root or a sudo user.
• Enough free disk space for the dump. Check with df -h. A compressed custom-format dump is usually much smaller than the database itself.
• The examples back up the database appdb into /var/backups/postgres. Create that folder and give it to the postgres user:

sudo mkdir -p /var/backups/postgres
sudo chown postgres:postgres /var/backups/postgres
sudo chmod 700 /var/backups/postgres

Running the tools as the postgres system user means they log in through peer authentication, so no password is needed.

Plain or custom format?

• Plain (.sql): a readable SQL script. You restore it with psql. Easy to inspect or edit, but you must restore all of it, in order, with one connection.
• Custom (-Fc, .dump): a compressed archive. You restore it with pg_restore, which can restore single tables, skip ownership, and run several jobs at once.
• Directory (-Fd): like custom, but written as a folder of files. It is the only format that pg_dump itself can write in parallel.

For regular backups, the custom format is the best default.

Back up one database

Custom format, compressed:

sudo -u postgres pg_dump -Fc -f /var/backups/postgres/appdb-$(date +%F).dump appdb

Plain SQL, when you want a readable file:

sudo -u postgres pg_dump -f /var/backups/postgres/appdb-$(date +%F).sql appdb

Directory format with four parallel jobs, for large databases:

sudo -u postgres pg_dump -Fd -j 4 -f /var/backups/postgres/appdb-$(date +%F) appdb

Pick a job count no higher than your vCPU count. Each job opens its own database connection.

Back up roles and other globals

pg_dump saves one database. It does not save roles, their passwords or tablespaces, because those belong to the whole server. Save them separately with pg_dumpall:

sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgres/globals-$(date +%F).sql

pg_dumpall without --globals-only dumps every database plus globals into one plain SQL file. That is handy for small servers but slow to restore selectively.

Restore a backup

Restore globals first

On a new server, recreate the roles before restoring a database so ownership and grants apply correctly:

sudo -u postgres psql -f /var/backups/postgres/globals-2026-11-09.sql

You will see errors for roles that already exist, such as postgres. Those are harmless.

Restore a custom or directory dump

Create an empty database, then restore into it:

sudo -u postgres createdb -O appuser appdb
sudo -u postgres pg_restore -d appdb -j 4 /var/backups/postgres/appdb-2026-11-09.dump

Useful options:

• --clean --if-exists drops existing objects before recreating them, to restore over a database that already has data.
• --no-owner skips ownership commands, so the restoring user owns everything. Use it when the original roles do not exist on the target.
• -t orders restores just one table.
• -l lists the archive contents without restoring anything.

Restore a plain SQL dump

sudo -u postgres createdb -O appuser appdb
sudo -u postgres psql -v ON_ERROR_STOP=1 -d appdb -f /var/backups/postgres/appdb-2026-11-09.sql

ON_ERROR_STOP=1 makes psql stop at the first error instead of carrying on and leaving a half-restored database.

Automate nightly dumps

A simple cron job for the postgres user keeps a dated dump each night. Open its crontab:

sudo crontab -u postgres -e

Add a line like this (it runs at 02:30):

30 2 * * * pg_dump -Fc -f /var/backups/postgres/appdb-$(date +\%F).dump appdb

The % signs must be escaped as \% inside crontab. Delete old files with a second job, for example find /var/backups/postgres -name '*.dump' -mtime +14 -delete, and copy the folder off the server. A backup that lives only on the same VPS does not protect you if the VPS is lost.

Check it works

• List the contents of a custom dump. If it prints a table of contents, the file is readable:

  sudo -u postgres pg_restore -l /var/backups/postgres/appdb-2026-11-09.dump | head

• Do a test restore into a scratch database, count rows in a key table, then drop it:

  sudo -u postgres createdb restore_test
  sudo -u postgres pg_restore -d restore_test /var/backups/postgres/appdb-2026-11-09.dump
  sudo -u postgres psql -d restore_test -c "SELECT count(*) FROM orders;"
  sudo -u postgres dropdb restore_test

Troubleshooting

• "input file appears to be a text format dump. Please use psql": you gave a plain .sql file to pg_restore. Restore it with psql -f instead.
• "role appuser does not exist" during restore: restore the globals file first, create the role, or add --no-owner --no-privileges.
• "server version mismatch" from pg_dump: your pg_dump is older than the server. Use the client from the same or a newer major version, for example /usr/lib/postgresql/<version>/bin/pg_dump on Debian and Ubuntu.
• "Permission denied" writing the file: the postgres user cannot write to that folder. Use the backup folder created above.
• Restore is slow: use custom or directory format with -j, and restore onto a server with free CPU and disk I/O.

Next steps

• Set up a role and database before restoring: Create PostgreSQL users, roles and databases.
• Copy your dumps off the server: Back up a Linux VPS with rsync and restic.
• Understand where dumps fit next to snapshots: Snapshot vs backup.