Backup with pg_dump and Restore One Database

pg_dump is a logical copy of one database. It is the right tool when you want a portable archive you can restore onto a new major, a new VPS, or a staging box. It is not a snapshot of the whole cluster, and it is not point-in-time recovery. This post dumps a single database in custom format, keeps the file off the data directory, and restores it into an empty database with pg_restore. Practice the restore. A dump you have never restored is a file, not a backup.

Use a dedicated role with CONNECT and SELECT on the schemas you care about, or a backup role with pg_read_all_data on PostgreSQL 16. Do not dump as superuser from a laptop over the public IP.

Dump one database, custom format

Custom format (-Fc) is compressed, TOC-aware, and parallel-restorable. Plain SQL is greppable and slower to restore. Directory format is for parallel dumps of large databases. Start with custom.

sudo mkdir -p /var/backups/pg
sudo chown postgres:postgres /var/backups/pg
sudo -u postgres pg_dump --format=custom --compress=9   --no-owner --no-privileges --verbose   --file=/var/backups/pg/appdb.$(date +%F).dump   appdb
sudo -u postgres pg_restore --list /var/backups/pg/appdb.$(date +%F).dump | head
ls -lh /var/backups/pg

--no-owner and --no-privileges keep the archive from replaying role names that exist only on the source. You create roles on the target first, then restore into a database those roles can own. --verbose prints object names so a silent failure is obvious.

Do not write the dump into /var/lib/postgresql. That is the data directory. A full disk there pauses the cluster. Keep dumps on a separate volume, then copy off-box. Lucknow DC01 is the live site; the copy that survives a VPS loss lives somewhere else.

A dump of postgres, template0, or template1 is usually the wrong target. Dump appdb. If you have more than one application database, loop names from pg_database where datistemplate is false and datname is not postgres.

Restore into a clean database

pg_restore does not create the database unless you pass --create, and --create needs a dump taken with --create or a plain dump that starts with CREATE DATABASE. The safer pattern is: create the database empty, restore into it, then grant.

sudo -u postgres psql -c "CREATE ROLE app_owner LOGIN;"
sudo -u postgres psql -c "CREATE DATABASE appdb_restore OWNER app_owner TEMPLATE template0;"
sudo -u postgres pg_restore --verbose --no-owner --role=app_owner   --jobs=2 --dbname=appdb_restore   /var/backups/pg/appdb.2026-05-22.dump
sudo -u postgres psql -d appdb_restore -c "SELECT count(*) FROM app.orders;"

--jobs=2 uses two workers; do not set it higher than the vCPU count you actually have. --role=app_owner makes restored objects owned by the migration role. If the dump was taken with --no-privileges, re-run your grant file after restore. That is a feature: the grant file is the source of truth, not whatever the old cluster had in PUBLIC.

If restore errors on missing types or extensions, install those on the target first. CREATE EXTENSION pgcrypto; and friends belong in the role/database bootstrap, not as a surprise in the middle of pg_restore.

Restore is the drill, not the dump

A nightly cron that writes a dump is half the pipeline. Once a week, restore onto a second database or a second VPS and run a count plus a checksum query the app already knows. Time it. That duration is your restore RTO for this method.

#!/usr/bin/env bash
set -euo pipefail
STAMP=$(date +%F)
DUMP=/var/backups/pg/appdb.$STAMP.dump
sudo -u postgres pg_dump --format=custom --compress=9   --no-owner --no-privileges --file=$DUMP appdb
sudo -u postgres pg_restore --list $DUMP >/dev/null
# off-box copy belongs here: rsync, restic, or SFTP
find /var/backups/pg -name 'appdb.*.dump' -mtime +14 -delete

Retention of 14 days on-box is a buffer, not the off-site copy. Encrypt the file before it leaves the VPS if the transport is not already a trusted channel.

pg_dump is consistent for one database at the moment it finishes its snapshot of that database. Other databases on the same cluster are not in the file. Global objects — roles, tablespaces, grants at the cluster level — are not in the file either. pg_dumpall --globals-only captures roles. Take it. A restore that cannot CREATE ROLE app_runtime because you forgot the globals dump is a restore that is not done.

When RPO is minutes, dump is not enough. That is WAL and PITR, covered next. When RPO is a day and the unit of restore is one database, pg_dump plus a practiced pg_restore is the correct default.

Takeaway

One custom-format dump per database, stored off the data disk, restored into a clean database with roles created first. Time that restore. You can spin up a second Ubuntu instance on Netbay in Lucknow DC01 in under 60 seconds and run the drill for real — netbayhosts.in.