PostgreSQL Logical Backups on Debian 12 with pg_dump

Why logical backups beat a rushed snapshot

PostgreSQL logical backups are the right choice when you need a clean export you can move, inspect, and restore elsewhere. On a Debian 12 VPS, that usually means a backup you can rely on during a migration, a schema change, or a storage problem.

This tutorial walks through a production-safe backup workflow with pg_dump, restore validation, retention, compression, and a rollback path. If you run a busy app on a Hostperl VPS, this is the kind of routine that keeps recovery predictable. For sizing and growth planning, a Hostperl VPS gives you enough CPU, RAM, and NVMe headroom to test restores without fighting the host.

Primary search intent: create, verify, and restore PostgreSQL logical backups on Debian 12.

Focus keyword: PostgreSQL logical backups

Semantic variants: pg_dump backups, PostgreSQL export and restore, logical dump workflow, restore testing

Relevant entities: Debian 12, PostgreSQL 15, pg_dump, pg_restore, systemd, cron, gzip, sudo, app user, backup directory

What you will build

You will set up a dedicated backup account, create compressed daily dumps, test a restore into a separate database, and add a cron job that keeps at least seven days of history. The procedure stays on Debian 12 because PostgreSQL packages, service names, and AppArmor defaults are stable there in 2026.

Logical backups do not capture everything. They do not include WAL replay, so they are not a replacement for physical backups or PITR. They do, however, give you readable, portable backups that work well for migrations, staging refreshes, and disaster recovery drills. If you are comparing hosting for this workload, the pgvector hosting on VPS guide and Hostperl’s broader database sizing advice are useful companions when your PostgreSQL server also serves search, app data, or analytics.

Prerequisites and safe starting point

• A fresh Debian 12 server with root SSH access.
• A PostgreSQL instance already installed and running.
• A database name you want to protect. In this tutorial, use appdb.
• A non-root administrative user named deploy.

Start by connecting from your local computer:

ssh root@203.0.113.10

203.0.113.10 is a documentation example. Replace it with the real public IP assigned to your server.

Confirm the operating system and PostgreSQL service

On the VPS as root, confirm the release before you change anything:

cat /etc/os-release

You should see Debian 12 fields such as ID=debian and VERSION_ID="12". Then check PostgreSQL:

systemctl status postgresql --no-pager

A running service should show active (running). If it is not installed yet, install it on Debian 12 with:

apt update
apt install -y postgresql postgresql-client gzip cron

After installation, verify the version and service again:

psql --version
systemctl status postgresql --no-pager

Create a backup user and lock down file access

Run this on the VPS as root. The backup job should not use your normal login or the PostgreSQL superuser for file handling. A separate account keeps ownership and retention tidy.

adduser --disabled-password --gecos "" pgbackup
usermod -aG sudo deploy

The first command creates a locked account for backup files. The second command only applies if your deploy account does not already have sudo access. If you use a different admin account, replace deploy with that username.

Create the backup directory and apply permissions:

mkdir -p /var/backups/postgresql
chown pgbackup:pgbackup /var/backups/postgresql
chmod 750 /var/backups/postgresql

You should end up with a directory readable only by pgbackup and root. That matters if the dump contains customer data, API tokens, or order records.

Switch to the dedicated backup identity

On the VPS as root, open a shell as the backup user:

su - pgbackup

Check where you are:

pwd

You should land in /home/pgbackup. Now create a small working directory for temporary restore files:

mkdir -p ~/restore-test

Create the logical backup script

Use a script so the command stays identical during manual runs and cron execution. On the VPS as root, create the script file:

install -o pgbackup -g pgbackup -m 750 -d /usr/local/sbin
nano /usr/local/sbin/pg-dump-appdb.sh

Add this exact content, changing only the database name if needed:

#!/bin/sh
set -eu

BACKUP_DIR=/var/backups/postgresql
DB_NAME=appdb
DATE=$(date +%F_%H-%M-%S)
OUT_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

/usr/bin/pg_dump --format=plain --no-owner --no-privileges "$DB_NAME" | /bin/gzip -9 > "$OUT_FILE"
/chmod 640 "$OUT_FILE"

find "$BACKUP_DIR" -type f -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

Save and exit Nano with Ctrl+O, Enter, then Ctrl+X. This script creates a compressed plain-text dump and removes files older than seven days.

Fix one small issue before using it: replace /chmod with chmod. Then run a syntax-safe edit:

sed -i 's#/chmod#chmod#' /usr/local/sbin/pg-dump-appdb.sh
chmod 750 /usr/local/sbin/pg-dump-appdb.sh
chown pgbackup:pgbackup /usr/local/sbin/pg-dump-appdb.sh

Verify the file contents:

sed -n '1,200p' /usr/local/sbin/pg-dump-appdb.sh

Test PostgreSQL access before you schedule anything

Backups usually fail for two reasons: the wrong database name or the wrong PostgreSQL role permissions. Test from the server before cron ever runs.

On the VPS as root, switch to the backup user again and run the script manually:

su - pgbackup
/usr/local/sbin/pg-dump-appdb.sh

If PostgreSQL local authentication blocks the dump, you will see a connection or permission error. In that case, run the command as a PostgreSQL user with dump rights, or set up a .pgpass file for the backup account.

A safer production option is to connect with a dedicated role. On Debian 12, create it as the PostgreSQL admin user:

sudo -u postgres createuser --no-createdb --no-createrole --no-superuser backuprole
sudo -u postgres psql -c "ALTER USER backuprole WITH PASSWORD 'change-this-now'"

Then store credentials for the backup user only:

cat > /home/pgbackup/.pgpass <<'EOF'
127.0.0.1:5432:appdb:backuprole:change-this-now
EOF
chown pgbackup:pgbackup /home/pgbackup/.pgpass
chmod 600 /home/pgbackup/.pgpass

Update the dump script to use that role:

sed -i 's#/usr/bin/pg_dump#/usr/bin/pg_dump --username=backuprole#' /usr/local/sbin/pg-dump-appdb.sh

Run it again. A successful result creates a .sql.gz file in /var/backups/postgresql:

ls -lh /var/backups/postgresql

Restore the backup into a test database

This is the part many teams skip. Do not. A backup you never restored is only a guess.

On the VPS as root, create a scratch database and restore the latest dump into it. First find the newest file:

latest=$(ls -1t /var/backups/postgresql/appdb_*.sql.gz | head -n 1)
echo "$latest"

Now create a test database and restore:

sudo -u postgres createdb appdb_restore_test
gunzip -c "$latest" | sudo -u postgres psql appdb_restore_test

If the restore finishes without errors, validate the database list and table count:

sudo -u postgres psql -d appdb_restore_test -c "\dt"
sudo -u postgres psql -d appdb_restore_test -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';"

When the test is complete, remove the scratch database:

sudo -u postgres dropdb appdb_restore_test

That cleanup matters on small VPS plans, where test restores can fill storage faster than the production workload.

Schedule the backup with cron

On the VPS as the backup user, open the crontab:

crontab -e

Add this line to run at 02:15 every day:

15 2 * * * /usr/local/sbin/pg-dump-appdb.sh > /var/backups/postgresql/backup.log 2>&1

Save and exit. Then confirm the cron entry is present:

crontab -l

Because the script already deletes files older than seven days, the directory stays bounded without extra housekeeping.

Check logs, permissions, and filesystem usage

On the VPS as root, verify the result from three angles: service, logs, and disk space.

systemctl status cron --no-pager
journalctl -u cron -n 20 --no-pager
ls -lh /var/backups/postgresql
du -sh /var/backups/postgresql

You want cron active, a recent job line in the journal, and file sizes that fit your retention policy. If the directory grows too fast, increase compression, shorten retention, or move backups to object storage.

For database-level troubleshooting, the most useful PostgreSQL log check on Debian 12 is:

sudo -u postgres journalctl -u postgresql -n 50 --no-pager

Look for authentication failures, missing database names, or statement errors during restore.

Rollback and recovery path

If a backup run produces a bad dump file, remove it immediately and re-run the job after fixing the root cause:

rm -f /var/backups/postgresql/appdb_*.sql.gz
/usr/local/sbin/pg-dump-appdb.sh

If a restore test changes the wrong database, stop and inspect your command history before you continue. The safest recovery path is to restore into a separate database name first, compare row counts, and only then plan a cutover.

If you need to roll back a recent migration, keep the old server online until the new one passes both app login and write tests. For a hosted move, Hostperl’s migration checklist and zero-downtime cutover guide cover the operational side of validation and fallback timing.

Verification from the server and a client machine

On the VPS as root, confirm the backup file is recent and readable only by the intended owner:

ls -l /var/backups/postgresql
stat /var/backups/postgresql/appdb_*.sql.gz | tail -n 20

On your local computer, copy one backup file down for an off-server copy test:

scp root@203.0.113.10:/var/backups/postgresql/appdb_2026-01-01_02-15-00.sql.gz .

Replace the filename with the real one shown on your server. A successful transfer confirms your SSH access and gives you an extra offline copy, which is useful during incident response.

Troubleshooting the most likely failures

1. pg_dump says the database does not exist

Run:

sudo -u postgres psql -lqt

You should see the exact database name. If it is different, edit DB_NAME in the script and run it again.

2. The backup file is empty or tiny

Check:

gzip -dc /var/backups/postgresql/appdb_*.sql.gz | head

If the output is missing SQL headers, the dump likely failed before writing. Re-run the script without redirection to see the raw error.

3. Cron never runs the job

Check the cron journal:

journalctl -u cron -n 100 --no-pager

If the job is absent, verify the crontab belongs to pgbackup and that /usr/local/sbin/pg-dump-appdb.sh is executable.

4. Restore fails with permission denied

Run:

sudo -u postgres psql -d appdb_restore_test -c "\dt"

If the restored schema exists but the app still fails, the issue is usually role grants or missing extensions, not the dump itself.

FAQ

Can I use pg_dump for large production databases?
Yes, but watch runtime and file size. For very large databases, schedule the dump during quiet hours and consider complementing it with physical backups.

Should I compress SQL dumps?
Yes. Plain SQL compresses well and saves storage and transfer time. gzip -9 is fine for most VPS workloads.

How often should I test a restore?
Test at least weekly for active production systems. Monthly is the bare minimum if the data changes slowly.

Is this a replacement for snapshots?
No. Logical backups and storage snapshots solve different problems. Use both if your budget and host plan allow it.

Can I restore the dump to a newer PostgreSQL version?
Often yes, but extension behavior and SQL compatibility need checking. Test the restore on the target version before a migration window.