Run Postgres Backups With pg_dump and Verify Restores

Before you begin

• A Postgres database you can access with a user that has backup privileges.
• Enough free disk space to store the dump file.
• A plan for where the backup will live after creation, ideally somewhere other than the same VPS.
• A willingness to test a restore, not just create a file.

Logical backups with pg_dump are one of the simplest good habits for Postgres. They are easy to automate, portable between many setups, and much safer for beginners than copying live database files directly. The important part is treating restore verification as part of the backup job, not as a separate someday task.

Step 1: Choose the right dump format for the job

For many small self-hosted apps, the custom format is the most practical choice:

pg_dump -U myapp_user -d myapp_db -Fc -f /backups/myapp_$(date +%F_%H%M).dump

Why -Fc is useful:

• It compresses well.
• It works cleanly with pg_restore.
• It gives you more restore flexibility than a plain SQL file.

A plain SQL dump is still valid when you want a human-readable file:

pg_dump -U myapp_user -d myapp_db > /backups/myapp_$(date +%F_%H%M).sql

For this guide, we will use the custom format because it is a better default for restore practice.

Step 2: Create the backup safely

Create a backup directory with restricted permissions:

mkdir -p /backups
chmod 700 /backups

Run the dump:

pg_dump -U myapp_user -d myapp_db -Fc -f /backups/myapp_$(date +%F_%H%M).dump

If the database is inside Docker, run the command from the container or pipe it to the host:

docker exec -t postgres pg_dump -U myapp_user -d myapp_db -Fc > /backups/myapp_$(date +%F_%H%M).dump

Check that the file exists and is not suspiciously tiny:

ls -lh /backups
file /backups/*.dump

You can also inspect the dump metadata:

pg_restore -l /backups/myapp_2026-04-28_1600.dump | head

If this step fails, do not move on and pretend the backup job is complete.

Step 3: Store the backup somewhere safer and rotate old files

A dump sitting on the same VPS helps with operator mistakes, but it does not protect you from full server loss. Copy the file to another system or object storage as soon as practical.

scp /backups/myapp_2026-04-28_1600.dump backup-user@backup-host:/srv/postgres-backups/

Or use a tool like rclone for offsite storage.

Set a simple retention habit so backup storage does not grow forever. For example, keep recent daily backups and prune older ones with a script you understand.

find /backups -name '*.dump' -mtime +14 -delete

If you automate deletion, do it only after you are confident newer backups are succeeding and offsite copies exist.

Step 4: Verify the restore in a safe test database

This is the step many people skip. It is also the step that turns a backup file into a trustworthy backup.

Create a test database:

createdb -U postgres myapp_restore_test

Restore the dump into it:

pg_restore -U postgres -d myapp_restore_test --clean --if-exists /backups/myapp_2026-04-28_1600.dump

If the database is empty before restore, you may omit --clean --if-exists, but including them is good practice when you are testing repeatably.

Now verify the restored data:

psql -U postgres -d myapp_restore_test -c '\dt'
psql -U postgres -d myapp_restore_test -c 'SELECT COUNT(*) FROM users;'

Use checks that matter to your app. Examples include:

• Confirm key tables exist
• Check row counts on important tables
• Verify schema migrations appear current
• Run a quick app connection test against the restored database if you have a safe staging app

When you finish, remove the test database if you do not need to keep it:

dropdb -U postgres myapp_restore_test

Rollback and recovery notes

Backup creation is usually low-risk, but restore testing can become dangerous if you point at the wrong database.

• Never restore into the live production database unless that is the explicit recovery event you are handling.
• Use a clearly named test database.
• Double-check your connection target before running pg_restore --clean.

If you accidentally restore into the wrong place, stop application writes immediately and assess whether you can recover from another backup. That mistake is survivable only if you catch it fast.

Step 5: Verify the backup routine is operationally real

By the end of this process, you should have:

• A recent dump file in a deliberate format
• Evidence that the file can be listed by pg_restore
• A successful restore into a test database
• A basic retention and offsite copy plan

This is what separates “we have backups” from “we can recover.”

Troubleshooting common pg_dump and restore problems

Authentication failed.
Check the username, password source, socket or host target, and whether the backup user has the needed rights.

The dump file is tiny or empty.
The command may have failed, or shell redirection may have written an error message instead of a dump. Inspect the file and rerun carefully.

pg_restore complains about existing objects.
Use a fresh test database or include --clean --if-exists when appropriate.

The restore succeeds, but the app still would not work.
A database dump may not include everything the app needs, such as uploaded files or external secrets. Recovery planning should cover the whole app, not just Postgres.

What to do next

Once your database backups are restore-tested, the next strong habit is scheduling them cleanly and rotating them without guesswork. Continue with How to Schedule Backups and Maintenance With systemd Timers.