How to Use pg_dump and pg_restore for Backups

How to Use pg_dump and pg_restore for Backups

Regular database backups are essential for any application running on a Breeze instance. PostgreSQL provides pg_dump for creating logical backups and pg_restore for restoring them. These tools support flexible backup formats, selective table dumps, and parallel operations for large databases.

Basic pg_dump Usage

Create a plain-text SQL dump of your database:

pg_dump -U postgres -h localhost myapp > /backup/myapp_$(date +%Y%m%d_%H%M%S).sql

For a compressed custom-format dump (recommended for production):

pg_dump -U postgres -h localhost -Fc myapp > /backup/myapp_$(date +%Y%m%d_%H%M%S).dump

The custom format (-Fc) supports parallel restore, selective table restoration, and is significantly smaller than plain-text dumps.

Dump Formats Compared

• Plain (-Fp) — human-readable SQL text. Can be restored with psql. Largest file size.
• Custom (-Fc) — compressed binary format. Restored with pg_restore. Supports parallel restore and selective object restore.
• Directory (-Fd) — dumps each table to a separate file in a directory. Best for parallel dump and restore of very large databases.
• Tar (-Ft) — tar archive format. Compatible with standard archive tools but does not support parallel operations.

Parallel Dumps for Large Databases

Speed up backups on your Breeze instance by dumping tables in parallel:

pg_dump -U postgres -h localhost -Fd -j 4 -f /backup/myapp_dir/ myapp

The -j 4 flag uses 4 parallel workers, significantly reducing dump time for databases with many tables.

Selective Backups

Dump specific tables or schemas:

# Dump only the orders and users tables
pg_dump -U postgres -t orders -t users myapp > /backup/partial.sql

# Dump only the public schema
pg_dump -U postgres -n public myapp > /backup/public_schema.sql

# Dump schema only (no data)
pg_dump -U postgres --schema-only myapp > /backup/schema.sql

# Dump data only (no schema)
pg_dump -U postgres --data-only myapp > /backup/data.sql

Restoring from Backups

Restore a plain-text dump:

psql -U postgres -h localhost -d myapp < /backup/myapp_20260301.sql

Restore a custom-format dump:

pg_restore -U postgres -h localhost -d myapp /backup/myapp_20260301.dump

Restore with parallel workers for faster recovery:

pg_restore -U postgres -h localhost -d myapp -j 4 /backup/myapp_dir/

Restoring to a New Database

Create a fresh database and restore into it:

createdb -U postgres myapp_restored
pg_restore -U postgres -d myapp_restored --clean --if-exists /backup/myapp.dump

The --clean flag drops existing objects before recreating them, and --if-exists prevents errors if objects do not exist yet.

Automated Backup Script

Create a cron-based backup script on your Breeze instance:

#!/bin/bash
BACKUP_DIR="/backup/postgres"
RETENTION_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"
pg_dump -U postgres -Fc myapp > "$BACKUP_DIR/myapp_$TIMESTAMP.dump"

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: myapp_$TIMESTAMP.dump"

Add this to cron to run nightly:

0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Always test your restore procedure periodically to ensure your backups are valid and your recovery process works smoothly on your Breeze instances.