PostgreSQL Backup and Restore with pg_dump

By Kokil Thapa | Last reviewed: September 2026

Production data loss rarely starts with a dramatic server crash. It starts with a bad migration, a mistaken DROP TABLE, or a restore plan nobody tested. PostgreSQL backup and restore with pg_dump is the first tool I reach for on small and mid-size servers because it ships with PostgreSQL, needs no extra daemons, and produces portable dumps you can inspect before disaster strikes. If you run PostgreSQL 18 alongside a Laravel application on PostgreSQL, this workflow belongs in your deployment checklist—not as an afterthought on launch day.

What is pg_dump and when should you use it?

pg_dump creates a logical backup of one database. It reads tables, indexes, constraints, sequences, and permissions, then writes SQL or a custom archive file. Unlike file-level copies of /var/lib/postgresql, logical dumps survive version upgrades and cross-platform moves more easily.

On real client projects I treat pg_dump as the baseline layer. It does not replace PostgreSQL point-in-time recovery with WAL archiving when you need minute-level RPO. For many Laravel booking apps, legal portals, and eCommerce backends, nightly pg_dump plus off-site storage is enough.

Choose pg_dump when you need portability, selective restore, or a simple cron job on Ubuntu. Skip it as your only strategy when you require continuous replication or sub-hour recovery windows. Pair it with monitoring and documented runbooks instead.

How do you create a PostgreSQL backup with pg_dump?

Install client tools matching your server major version. On Ubuntu 24.04 with PostgreSQL 18, the package is typically postgresql-client-18. Always dump from a user with sufficient privileges—often a dedicated backup role, not the application user.

Plain SQL dump (best for small databases)

Plain SQL is human-readable. You can grep it, edit it, and restore with psql. It works well for schemas under a few hundred megabytes.

export PGHOST=127.0.0.1
export PGPORT=5432
export PGUSER=backup_user
export PGPASSWORD='your-secure-password'

pg_dump \
  --dbname=myapp_production \
  --format=plain \
  --no-owner \
  --no-acl \
  --file=/var/backups/postgresql/myapp_$(date +%F).sql

gzip -9 /var/backups/postgresql/myapp_$(date +%F).sql

The --no-owner and --no-acl flags avoid restore failures when target roles differ. That matters when you restore to staging with different usernames.

Custom format dump (best for production)

Custom format (-Fc) compresses data and supports parallel restore. This is my default on production Laravel apps backed by PostgreSQL.

pg_dump \
  --dbname=myapp_production \
  --format=custom \
  --compress=6 \
  --file=/var/backups/postgresql/myapp_$(date +%F_%H%M).dump

pg_dump \
  --dbname=myapp_production \
  --schema-only \
  --file=/var/backups/postgresql/myapp_schema_$(date +%F).sql

Keep a separate schema-only dump. After a bad migration, restoring structure without data is often faster than rolling back application code alone.

Globals, roles, and pg_dumpall

pg_dump does not export cluster-wide objects. Use pg_dumpall for roles, tablespaces, and database-level grants.

pg_dumpall \
  --globals-only \
  --file=/var/backups/postgresql/globals_$(date +%F).sql

Store globals alongside per-database dumps. Restoring a database without matching roles produces permission errors that look like application bugs.

Which pg_dump format should you choose: plain SQL vs custom vs directory?

Format choice affects restore speed, storage size, and operational flexibility. The table below summarises what I use in practice on Linux-managed PostgreSQL servers.

For databases above 10 GB, directory format plus parallel dump can cut backup windows significantly. Below that threshold, custom format keeps scripts simple without sacrificing restore options.

How do you restore a PostgreSQL database from pg_dump?

Restore is where untested backups die. Read the dump header first. Never pipe an unknown file straight into production.

Restore plain SQL with psql

createdb -O app_user myapp_restore_test

gunzip -c /var/backups/postgresql/myapp_2026-09-09.sql.gz | \
  psql --dbname=myapp_restore_test --set ON_ERROR_STOP=on

ON_ERROR_STOP=on halts on the first error. Without it, psql keeps going and you get a half-restored database that passes smoke tests but fails on edge cases.

Restore custom format with pg_restore

createdb -O app_user myapp_restore_test

pg_restore \
  --dbname=myapp_restore_test \
  --jobs=4 \
  --verbose \
  --no-owner \
  --no-acl \
  /var/backups/postgresql/myapp_2026-09-09.dump

Restore into an empty database. If objects already exist, you will see duplicate-key and relation-exists errors that waste hours.

Selective restore of one table

Custom format shines here. List contents, then restore a single table.

pg_restore --list /var/backups/postgresql/myapp_2026-09-09.dump | grep orders

pg_restore \
  --dbname=myapp_production \
  --table=orders \
  --data-only \
  /var/backups/postgresql/myapp_2026-09-09.dump

Use selective restore carefully on production. Foreign keys and sequences can desynchronise if you restore data without matching related tables.

Restore globals and roles

psql -f /var/backups/postgresql/globals_2026-09-09.sql postgres

Run globals before database restore on a fresh cluster. On an existing server, review the SQL file first—blindly re-applying roles can reset passwords.

How do you automate pg_dump backups on a production server?

Manual dumps fail the first busy week. Automate with cron, log every run, and push copies off the server the same night. A backup on the same disk as PostgreSQL data is not a backup.

Example cron script

Save this as /usr/local/bin/pg_backup_myapp.sh. Make it executable and restrict permissions to root or a backup user.

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgresql/myapp"
RETAIN_DAYS=14
DBNAME="myapp_production"
TIMESTAMP="$(date +%F_%H%M%S)"
LOG="/var/log/postgresql/backup-myapp.log"

mkdir -p "$BACKUP_DIR"
export PGHOST=127.0.0.1 PGPORT=5432 PGUSER=backup_user
export PGPASSWORD="$(cat /etc/postgresql/backup-myapp.password)"

pg_dump --dbname="$DBNAME" --format=custom --compress=6 \
  --file="$BACKUP_DIR/${DBNAME}_${TIMESTAMP}.dump"

pg_dumpall --globals-only \
  --file="$BACKUP_DIR/globals_${TIMESTAMP}.sql"

find "$BACKUP_DIR" -type f -mtime +"$RETAIN_DAYS" -delete

rsync -az "$BACKUP_DIR/" backup@offsite:/srv/backups/postgresql/myapp/

echo "$(date -Is) OK ${DBNAME}_${TIMESTAMP}.dump" >> "$LOG"

Schedule it during low-traffic hours. On Nepal-hosted apps, 02:30 NPT often works well.

30 2 * * * /usr/local/bin/pg_backup_myapp.sh

For Laravel apps, also review Laravel Spatie backup automation. Spatie wraps pg_dump cleanly and can push to S3. I still keep a shell script on the database host as a fallback when the app layer is down.

Off-site options include rsync over SSH, restic, or S3-compatible storage. See automated server backup setup and Ubuntu server backup strategies for wider context beyond PostgreSQL alone.

Locking, consistency, and replication

By default, pg_dump takes an MVCC snapshot. Reads stay consistent without long exclusive locks on InnoDB-style tables. Heavy write workloads may see slightly longer dump duration, not write blocking.

On a replica, run pg_dump against a standby node to keep load off the primary. Confirm the replica is caught up first. Stale replicas produce stale backups.

For zero-downtime requirements beyond logical dumps, add PostgreSQL replication and high availability. Logical backup plus streaming replication covers most SMB budgets I see in Nepal—roughly Rs 3,000–8,000/month (~USD 22–60) for a modest VPS plus storage.

What restore testing should you actually perform after pg_dump?

A backup file that never restores is expensive disk usage. Monthly restore drills catch permission drift, missing extensions, and broken cron paths before an incident.

1. Restore the latest dump to an isolated database name such as myapp_restore_drill.
2. Compare row counts on critical tables: orders, users, payments.
3. Run three application queries or report queries that finance or ops rely on.
4. Check sequences: SELECT last_value FROM orders_id_seq; versus MAX(id).
5. Document elapsed time. If restore exceeds your RTO, adjust format, parallel jobs, or hardware.
6. Delete the drill database after sign-off to free space.

This mirrors the checklist in database restore testing you should actually do. On a production Laravel booking platform I maintain, a missed extension restore once broke full-text search silently. Row counts looked fine. Search returned nothing.

Validate JSON exports with a JSON formatter when debugging API snapshot tables. Corrupted JSON in a dump usually means the source row was already bad—not a pg_dump bug.

Compare pg_dump against MySQL workflows if you are migrating stacks. PostgreSQL vs MySQL for production and database migration from MySQL to PostgreSQL both assume you can prove data integrity with restorable dumps.

For application-level context, read PostgreSQL administration essentials and database backup strategies for small servers. Small teams often skip WAL archiving initially. That is acceptable when pg_dump restores are tested and RPO of 24 hours is agreed with the business.

Official references remain the source of truth. The PostgreSQL pg_dump documentation and PostgreSQL backup dump chapter describe every flag. The pg_restore manual covers parallel jobs and selective restore in detail.

On hosted legal-tech and booking projects such as Adventure Third Pole Trek, PostgreSQL holds reservations, payments, and supplier data. Losing a night of bookings hurts operations immediately. Tested pg_dump automation plus off-site copies is the minimum viable safety net before budget allows full HA.

If you want hands-off monitoring, pair dumps with ongoing server support and maintenance. Alerts on missing backup files catch cron regressions after OS upgrades—a pattern I have seen repeatedly when PHP or PostgreSQL packages shift paths.

Key Takeaways

• Use custom format (-Fc) for production PostgreSQL backup and restore with pg_dump; keep plain SQL for small schemas and schema-only snapshots.
• Always dump globals with pg_dumpall --globals-only and store them beside per-database archives.
• Restore to an empty database with ON_ERROR_STOP=on for SQL dumps; use pg_restore -j for faster custom-format recovery.
• Automate via cron, log every run, retain 7–30 days locally, and copy off-site the same night.
• Run monthly restore drills with row-count checks, sequence validation, and timed RTO measurements.
• Layer replication or PITR when RPO requirements drop below one backup interval—pg_dump alone is not continuous protection.

People Also Ask

Does pg_dump lock tables in PostgreSQL?

pg_dump uses MVCC snapshots for consistent reads. It does not require exclusive locks that block normal writes on ordinary tables. Very long dumps can increase bloat if vacuum cannot reclaim dead tuples quickly, so monitor disk and autovacuum on busy systems.

Can you restore a pg_dump backup to a newer PostgreSQL version?

Yes, within reason. Logical dumps restore cleanly to the same or newer major versions— for example PostgreSQL 17 to 18. Restoring to an older major version often fails. Test on staging before production cutovers.

What is the difference between pg_dump and pg_dumpall?

pg_dump backs up one database. pg_dumpall exports all databases in a cluster plus global objects like roles and tablespaces. Use both: pg_dumpall for globals, pg_dump per application database.

How often should you run pg_dump backups?

Nightly pg_dump suits most web apps with acceptable RPO of 24 hours. High-transaction systems may need twice-daily dumps or WAL archiving. Match frequency to business tolerance and always verify off-site copies.

Build a backup workflow you can trust under pressure

PostgreSQL backup and restore with pg_dump is boring engineering—and that is the point. Custom-format nightly dumps, globals export, off-site sync, and monthly restore drills cover most production apps I run on Ubuntu without enterprise budgets. Start tonight with one database, one cron job, and one test restore. Then expand to replicas, monitoring, and PITR when the business needs tighter recovery windows. Need help auditing an existing PostgreSQL server or wiring backups into a Laravel deploy pipeline? Contact us to review your setup before the first real incident.