pg_dump and pg_restore Workflow

Beginner10 minTrending

Create reliable database backups with pg_dump and restore them with pg_restore for disaster recovery and environment cloning.

Prerequisites

-PostgreSQL installed
-psql and pg_dump available in PATH
-Database connection credentials

Steps

Create a plain SQL backup

Dump the entire database as a SQL script file for portability.

$ pg_dump -U postgres -d mydb -F plain -f /backups/mydb_$(date +%Y%m%d).sql

Plain format is human-readable and can be restored with psql. Use -F custom for large databases that benefit from compression.

Create a compressed custom-format backup

Dump the database in PostgreSQL custom format with built-in compression.

$ pg_dump -U postgres -d mydb -F custom -Z 6 -f /backups/mydb_$(date +%Y%m%d).dump

-Z 6 sets gzip compression level (0-9). Custom format also supports parallel restore.

Backup a single table

Dump only a specific table for targeted backup or migration.

$ pg_dump -U postgres -d mydb -t public.users -F custom -f /backups/users_table.dump

Restore from a custom-format backup

Restore a database from a custom-format dump file.

$ pg_restore -U postgres -d mydb_restored -F custom -c /backups/mydb_$(date +%Y%m%d).dump

The -c flag drops existing objects before recreating them. Omit it if restoring into an empty database.

Restore from a plain SQL backup

Execute a plain SQL dump file to restore the database.

$ psql -U postgres -d mydb_restored -f /backups/mydb_$(date +%Y%m%d).sql

Parallel restore for large databases

Speed up restoration by using multiple parallel jobs.

$ pg_restore -U postgres -d mydb_restored -F custom -j 4 /backups/mydb_$(date +%Y%m%d).dump

-j 4 uses 4 parallel workers. Match this to your available CPU cores for optimal speed.

Full Script

full-script.sh

#!/bin/bash
# PostgreSQL Backup and Restore
set -euo pipefail

DB_NAME="mydb"
DB_USER="postgres"
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
mkdir -p "$BACKUP_DIR"

echo "=== Creating custom-format backup ==="
pg_dump -U "$DB_USER" -d "$DB_NAME" -F custom -Z 6 -f "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"
echo "Backup: $BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"
ls -lh "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

echo ""
echo "=== Listing backup contents ==="
pg_restore -l "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" | head -20

echo ""
echo "=== Backup verification ==="
pg_restore -l "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" | wc -l
echo "objects in backup"

echo ""
echo "=== To restore, run: ==="
echo "createdb -U $DB_USER ${DB_NAME}_restored"
echo "pg_restore -U $DB_USER -d ${DB_NAME}_restored -F custom -j 4 $BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

echo ""
echo "=== Cleanup old backups (30+ days) ==="
find "$BACKUP_DIR" -name "*.dump" -mtime +30 -delete -print

FAQ

Discussion

Loading comments...

#!/bin/bash # PostgreSQL Backup and Restore set -euo pipefail DB_NAME="mydb" DB_USER="postgres" BACKUP_DIR="/backups/postgresql" TIMESTAMP=$(date +%Y%m%d-%H%M%S) mkdir -p "$BACKUP_DIR" echo "=== Creating custom-format backup ===" pg_dump -U "$DB_USER" -d "$DB_NAME" -F custom -Z 6 -f "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" echo "Backup: $BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" ls -lh "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" echo "" echo "=== Listing backup contents ===" pg_restore -l "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" | head -20 echo "" echo "=== Backup verification ===" pg_restore -l "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" | wc -l echo "objects in backup" echo "" echo "=== To restore, run: ===" echo "createdb -U $DB_USER ${DB_NAME}_restored" echo "pg_restore -U $DB_USER -d ${DB_NAME}_restored -F custom -j 4 $BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" echo "" echo "=== Cleanup old backups (30+ days) ===" find "$BACKUP_DIR" -name "*.dump" -mtime +30 -delete -print