PostgreSQL Backup and Restore on a VPS: pg_dump and pg_restore

Published on

Back up PostgreSQL on a VPS with pg_dump and pg_dumpall, restore with pg_restore, run nightly dumps, copy them off the server and test restores, Docker too.

Overview page of a VPS control panel with disk usage, bandwidth, CPU and network graphs

Your app keeps its data in PostgreSQL on one VPS: a shop, an n8n instance or a Discord bot. After a failed migration, a wrong DELETE or a dead disk, you need a copy you can put back. That copy has to live off the server, and you have to know it restores. This guide sets that up on Ubuntu 24.04 and Debian 12, for PostgreSQL installed with apt and for the official Docker image.

Quick answer: Dump each database with pg_dump -Fc (compressed, restored with pg_restore) and your roles with pg_dumpall --globals-only, because pg_dump leaves roles out. Replace a database with pg_restore --clean --if-exists --create -d postgres, or restore beside it into a new database made from template0. In Docker, run the same tools through docker exec, never with -t. Run the dumps nightly from a systemd timer, copy them to another machine, and restore one into a scratch database every month. Moving to a newer major version? Restore with the newer version's tools.

Which Dump Format to Use

pg_dump exports one database, consistently, even while your app uses it (manual). It has four formats:

FormatFlagRestore withCompressedParallel
Plain SQL-Fp (default)psqlNo, unless you add -ZNo
Custom-Fcpg_restoreYes, gzip by defaultRestore only
Directory-Fdpg_restoreYes, gzip by defaultDump and restore
Tar-Ftpg_restoreNoNo

Use custom: one file per database, and pg_restore can restore all of it, one table, or a list you edit. Pick directory for a large database you want to dump with -j.

pg_dump does not save roles or tablespaces, because they belong to the whole cluster. The SQL dump chapter calls pg_dumpall --globals-only "necessary to fully backup the cluster" when you dump databases one by one. That file holds your roles' passwords, so guard it like the dumps.

Step 1: Take a Dump Now

Steps 1 and 2 assume PostgreSQL from apt; the Docker section translates them. sudo -u postgres runs a tool as the postgres system user, which logs in as the database superuser through peer authentication, the default for local connections on Ubuntu and Debian. The > writes the file into your current folder.

pg_lsclusters                     # version, cluster name, status
sudo -u postgres psql -l          # your databases
sudo -u postgres pg_dump -Fc appdb > appdb.dump
sudo -u postgres pg_dumpall --globals-only > globals.sql
pg_restore -l appdb.dump | head -15

Replace appdb with your database. Ubuntu 24.04 installs PostgreSQL 16 and Debian 12 installs 15, unless you added the PostgreSQL apt repository. The last line reads the archive's table of contents: if it shows Dumped from database version and Dumped by pg_dump version, the file is a readable archive.

Step 2: Restore It

Every restore below reads the file through <, so the postgres user needs no access to your folder.

Replace a damaged database. Stop the app first: DROP DATABASE fails while anyone else is connected.

sudo -u postgres pg_restore --clean --if-exists --create -d postgres < appdb.dump

With --clean and --create, pg_restore drops appdb, creates it again and restores into it. -d postgres is only where it connects first (use -d template1 when you restore the postgres database itself), and --if-exists avoids an error when appdb is already gone.

Restore beside the original, to compare or copy rows back:

sudo -u postgres createdb -T template0 appdb_old
sudo -u postgres pg_restore -d appdb_old < appdb.dump

The pg_restore manual uses template0 so the new database starts empty.

On a new server, install the same major version or newer, then restore roles, databases and statistics, in that order:

sudo -u postgres psql -X -d postgres < globals.sql
sudo -u postgres pg_restore --clean --if-exists --create -d postgres < appdb.dump
sudo -u postgres vacuumdb --all --analyze-only

The first command prints role "postgres" already exists, an error the pg_dumpall manual calls harmless. pg_dump leaves optimizer statistics out by default, so the last line rebuilds them. Moving a whole site to a new VPS? Our website move guide covers the files, DNS and a short freeze. A Space-Node VPS gives you full root access, and you pick Ubuntu 24.04 LTS or Debian when you order.

Skipped the roles, or want one user to own everything? Add --no-owner --no-privileges: pg_restore then skips ownership and GRANT commands, and the user running the restore owns all objects. A plain .sql dump goes into a database created from template0 with psql instead: sudo -u postgres psql -X --set ON_ERROR_STOP=on -d appdb < appdb.sql stops at the first error with exit status 3.

Docker: The Same Tools Through docker exec

The official postgres image ships pg_dump, pg_restore and psql in the server's own version, so run them inside the container. Find its name with sudo docker ps:

sudo docker exec my-postgres pg_dump -U postgres -Fc appdb > appdb.dump
sudo docker exec my-postgres pg_dumpall -U postgres --globals-only > globals.sql
sudo docker exec -i my-postgres pg_restore -U postgres --clean --if-exists --create -d postgres < appdb.dump
  • -U is your POSTGRES_USER, or postgres if you never set one. The image trusts connections made inside the container, so nothing asks for a password.
  • Never add -t when you redirect the output. A pseudo-TTY turns each newline into a carriage return plus newline (moby #8513), which corrupts a custom dump (docker-library/postgres #219). docker compose exec allocates a TTY by default, so add -T there: sudo docker compose exec -T db pg_dump -U postgres -Fc appdb > appdb.dump, with your service name for db.
  • Add -i when a file goes in with <: it keeps standard input open (docker exec).

Step 3: Nightly Dumps With a systemd Timer

Installed PostgreSQL with apt? postgresql-common ships a ready timer: sudo systemctl enable --now pg_dump@16-main.timer (your version and cluster from pg_lsclusters) dumps the roles and every database weekly into /var/backups/postgresql/16-main and keeps three. If a week of lost data is too much, or you use Docker, use this script. It runs as root and writes to a folder only you and root can open:

sudo install -d -m 700 -o "$USER" /var/backups/pgdump

Save this as /usr/local/bin/pg-backup:

#!/bin/sh
set -eu
DIR=/var/backups/pgdump
KEEP_DAYS=14
RUN="runuser -u postgres --"    # Docker: RUN="docker exec my-postgres"
DBUSER=postgres                 # Docker: your POSTGRES_USER
DAY=$(date +%F)

DBS=$($RUN psql -U "$DBUSER" -d postgres -XAtc \
  "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate")
for DB in $DBS; do
  $RUN pg_dump -U "$DBUSER" -Fc "$DB" > "$DIR/$DB-$DAY.dump.part"
  mv "$DIR/$DB-$DAY.dump.part" "$DIR/$DB-$DAY.dump"
done
$RUN pg_dumpall -U "$DBUSER" --globals-only > "$DIR/globals-$DAY.sql"
find "$DIR" -type f -mtime +"$KEEP_DAYS" -delete

set -e stops the script at the first failure, so a broken night never deletes older dumps, and a dump only gets its final name once pg_dump has finished. find ignores the fraction of a day, so -mtime +14 removes files at least 15 days old.

Then the two units:

# /etc/systemd/system/pg-backup.service
[Unit]
Description=PostgreSQL dumps

[Service]
Type=oneshot
ExecStart=/usr/local/bin/pg-backup

# /etc/systemd/system/pg-backup.timer
[Unit]
Description=Nightly PostgreSQL dumps

[Timer]
OnCalendar=*-*-* 03:30:00
Persistent=true

[Install]
WantedBy=timers.target

sudo chmod 700 /usr/local/bin/pg-backup
sudo systemctl daemon-reload
sudo systemctl start pg-backup.service
ls -l /var/backups/pgdump
sudo systemctl enable --now pg-backup.timer
systemctl list-timers pg-backup.timer

Persistent=true catches up on a night the server was off (systemd.timer), and a failed run shows in systemctl status pg-backup.service. Prefer cron? Add 30 3 * * * /usr/local/bin/pg-backup with sudo crontab -e. If you ever put date +%F in a crontab line itself, write \%: cron turns a bare % into a newline (crontab(5)).

Step 4: Copy the Dumps off the Server

A dump on the VPS's own disk dies with that disk, and anyone who breaks in can delete it along with the database. Pull a copy from another machine every day, so the VPS holds no login to your copies:

rsync -a youruser@VPS_IP:/var/backups/pgdump/ ~/pgdump-vps/

Schedule it on that machine for after the nightly run. rsync starts a copy of itself on the other end, so install it on both machines (sudo apt install rsync). Without --delete it never removes files at the destination (rsync), so prune old copies there with a find line of your own.

Step 5: Test a Restore Every Month

A dump you have never restored is a guess. Restore last night's file into a scratch database, compare a row count with the live one, then drop it:

F=/var/backups/pgdump/appdb-2026-10-30.dump
sudo -u postgres createdb -T template0 restore_test
sudo -u postgres pg_restore --exit-on-error -d restore_test < "$F" && echo RESTORE OK
sudo -u postgres psql -d restore_test -c 'SELECT count(*) FROM orders;'
sudo -u postgres psql -d appdb -c 'SELECT count(*) FROM orders;'
sudo -u postgres dropdb restore_test

Use one of your own tables instead of orders. pg_restore exits with status 1 when anything failed, and --exit-on-error stops at the first error. In Docker, swap sudo -u postgres for sudo docker exec -i my-postgres and add -U postgres. Now and then, run it on the other machine too, after loading globals.sql.

Restoring Into a Newer Major Version

Dump and restore is the classic way up a major version, because pg_dump's output loads into newer servers. PostgreSQL 14 gets its final release on 12 November 2026, so if you're on 14, move now.

  • pg_dump can't dump a newer server. It stops with aborting because of server version mismatch (source), for example Ubuntu 24.04's pg_dump 16 against a postgres:17 container. Run pg_dump inside the container, or install postgresql-client-17 from the PostgreSQL apt repository.
  • Restore with the newer tools. pg_restore 16 can't read a custom dump from pg_dump 17 or 18: unsupported version (1.16) in file header. Plain dumps from pg_dump releases since 14 August 2025 contain a \restrict line near the top, which an older psql rejects with invalid command \restrict.
  • Cross-version dumps. The upgrade chapter recommends the newer version's pg_dump and pg_dumpall when you can, and the pg_dump manual recommends --quote-all-identifiers across versions.

On an apt install, postgresql-common does the work. With a fresh dump in hand, install the new version from the PostgreSQL apt repository, drop the empty cluster it creates (README.Debian) and upgrade; on Debian 12, use 15 instead of 16:

sudo apt install postgresql-18
sudo pg_dropcluster 18 main --stop
sudo pg_upgradecluster 16 main

pg_upgradecluster dumps and restores by default, gives the new cluster the old port and keeps the old cluster, stopped, until you remove it with sudo pg_dropcluster 16 main.

In Docker, don't just change postgres:17 to postgres:18 on the old volume. From 18 the image keeps its data in /var/lib/postgresql/18/docker, with the volume at /var/lib/postgresql, and refuses the old layout with Error: in 18+, these Docker images are configured to store database data in a format which is compatible with "pg_ctlcluster". Dump with the old container, start the new one on a new volume at /var/lib/postgresql, restore as in Step 2, and keep the old volume until the app runs on the new one.

If a Restore Fails

  • role "appuser" does not exist: the roles weren't restored. Load globals.sql first, or restore with --no-owner --no-privileges.
  • database "appdb" is being accessed by other users: the app or a psql session is still connected. Stop it and run the restore again.
  • input file appears to be a text format dump. Please use psql.: it's a plain SQL dump; load it with psql.
  • errors ignored on restore: 3: pg_restore carried on past errors. Fix the first one shown above it and restore again.
  • could not read from input file: end of file: the file is cut short or damaged. A custom dump made through docker exec -t can fail like this; it can't be repaired, so make a new one without -t.

FAQ

Do I have to stop my app to make a dump? No. pg_dump "makes consistent exports even if the database is being used concurrently" and doesn't block reads or writes; only operations that need an exclusive lock, such as most forms of ALTER TABLE, wait for it. Stop the app when you move servers, so no writes land after the last dump.

Can I copy /var/lib/postgresql or the Docker volume instead? Only with PostgreSQL stopped, and only as the whole cluster into the same major version: the manual says the server "must be shut down in order to get a usable backup". A consistent disk snapshot also works; our snapshot guide covers that for PostgreSQL. A dump works across versions and machines.

How do I lose less than a day of data? Dumps are points in time. Continuous archiving, a base backup plus archived WAL, restores to any moment since that base backup (PITR). postgresql-common ships pg_basebackup@ and pg_receivewal@ units for it; its README.systemd calls that the preferred method and advises pgbackrest or barman for large or busy databases.

Are backups included with a Space-Node VPS? Not in the plan price; you can arrange them through a support ticket. Your own off-server dumps are still worth keeping: you decide how often they run and how long they stay.

Sources

All opened on 30 October 2026: