PostgreSQL Backup and Restore in Docker¶

A comprehensive guide to backing up and restoring PostgreSQL databases running in Docker containers using bash scripts.

Backup Command¶

if docker exec "$DBContainerName" /usr/bin/pg_dump --dbname=$Database --username=$DBUSER --role=$DBUSER > "$TempDir/backup.sql" 2>/dev/null; then
  echo -e "${GREEN}✓${NC} Database backed up successfully"
else
  echo -e "${RED}✗${NC} Database backup failed!"
  echo -e "${RED}Cleaning up and exiting...${NC}"
  rm -rf "$TempDir"
  exit 1
fi

Key Points¶

Uses pg_dump to create a plain SQL backup
Redirects stdout (>) to save the backup file
Suppresses error messages with 2>/dev/null
Default format is plain text SQL

Restore Command¶

if docker exec -i "$DBContainerName" /usr/bin/psql --dbname=$Database --username=$DBUSER < "$TempDir/backup.sql" 2>/dev/null; then
  echo -e "${GREEN}✓${NC} Database restored successfully"
else
  echo -e "${RED}✗${NC} Database restoration failed!"
  echo -e "${RED}Cleaning up and exiting...${NC}"
  rm -rf "$TempDir"
  exit 1
fi

Key Differences from Backup¶

-i flag: Required for docker exec when piping input into the container
psql instead of pg_dump: Uses PostgreSQL client to execute SQL commands
< instead of >: Redirects backup file into the command

Why `psql` and not `pg_restore`?¶

psql is for plain-text SQL dumps (default pg_dump format)
pg_restore is only for custom/compressed formats: -Fc (custom), -Fd (directory), -Ft (tar)

Database Creation Before Restore¶

Since psql requires the database to exist, you have three options:

Option 1: Include CREATE DATABASE in Backup (Recommended)¶

# Backup with --create flag
docker exec "$DBContainerName" /usr/bin/pg_dump --create --dbname=$Database --username=$DBUSER --role=$DBUSER > "$TempDir/backup.sql"

# Restore to postgres database (which always exists)
docker exec -i "$DBContainerName" /usr/bin/psql --dbname=postgres --username=$DBUSER < "$TempDir/backup.sql"

Option 2: Create Database Manually Before Restore¶

# Create database if it doesn't exist
docker exec "$DBContainerName" /usr/bin/psql --username=$DBUSER --dbname=postgres -c "CREATE DATABASE $Database OWNER $DBUSER;" 2>/dev/null || true

# Then restore
docker exec -i "$DBContainerName" /usr/bin/psql --dbname=$Database --username=$DBUSER < "$TempDir/backup.sql"

Option 3: Drop and Recreate for Clean Restore¶

# Drop existing database and create fresh
docker exec "$DBContainerName" /usr/bin/psql --username=$DBUSER --dbname=postgres -c "DROP DATABASE IF EXISTS $Database;"
docker exec "$DBContainerName" /usr/bin/psql --username=$DBUSER --dbname=postgres -c "CREATE DATABASE $Database OWNER $DBUSER;"

# Then restore
docker exec -i "$DBContainerName" /usr/bin/psql --dbname=$Database --username=$DBUSER < "$TempDir/backup.sql"

pg_dump Formats Comparison¶

Plain SQL Format (default)¶

Good for: - Small to medium databases (< 1-10 GB) - Databases you might need to manually inspect or edit - Maximum compatibility

Limitations: - Not compressed - can be very large - Slow restore - sequential, line by line - No selective restore - all or nothing - No parallelization - single-threaded only

Custom Format (`-Fc`) - Most Popular¶

pg_dump -Fc --dbname=$Database > backup.dump
pg_restore --dbname=$Database backup.dump

Advantages: - Compressed by default (5-10x smaller) - Selective restore - specific tables/schemas - Parallel restore with -j flag - Best flexibility

Use when: Database > 1 GB or you want faster restores

Directory Format (`-Fd`)¶

pg_dump -Fd --dbname=$Database -f backup_dir/
pg_restore --dbname=$Database backup_dir/

Advantages: - Same benefits as custom format - Better for very large databases (splits into multiple files) - Parallel dump AND restore possible

Use when: Database is very large (100+ GB)

Tar Format (`-Ft`)¶

pg_dump -Ft --dbname=$Database > backup.tar
pg_restore --dbname=$Database backup.tar

Advantages: - Packaged as single tar file - Selective restore possible - Not compressed by default (can pipe through gzip)

Use when: Need portability but custom format isn't suitable

Practical Size Guidelines¶

< 100 MB: Plain SQL is fine
100 MB - 10 GB: Custom format recommended
10 GB+: Custom or directory format highly recommended
100 GB+: Directory format with parallel operations

Performance Example¶

5 GB database: - Plain SQL: ~5 GB file, 20-30 min restore - Custom format (-Fc): ~500 MB file, 5-10 min restore (with -j 4)

Output Redirection Explained¶

`2>/dev/null` - Suppress Errors¶

2> redirects stderr (standard error, file descriptor 2)
/dev/null discards everything (black hole)
Hides error messages while preserving exit code for if statements

When to Use¶

Clean output - Don't clutter terminal
Custom error handling - Show user-friendly messages
Hide expected warnings - Suppress harmless warnings

When NOT to Use (Debugging)¶

Remove 2>/dev/null temporarily to see actual errors:

docker exec "$DBContainerName" /usr/bin/pg_dump ... > "$TempDir/backup.sql"

Suppress Both stdout and stderr¶

# Shorthand (most common)
command &>/dev/null

# Equivalent longer form
command >/dev/null 2>&1

# Explicit both
command 1>/dev/null 2>/dev/null

Breaking down syntax: - &>/dev/null - redirects both stdout and stderr (bash shorthand) - >/dev/null 2>&1 - stdout to /dev/null, then stderr follows stdout - 1>/dev/null - explicitly redirects stdout (file descriptor 1) - 2>/dev/null - explicitly redirects stderr (file descriptor 2)

Important: Don't Suppress stdout in pg_dump¶

# CORRECT - stdout contains your backup data
pg_dump ... > "$TempDir/backup.sql" 2>/dev/null

# WRONG - would create empty backup file
pg_dump ... > "$TempDir/backup.sql" &>/dev/null

When to Suppress Both¶

For commands where you don't care about any output:

# Creating database - don't need to see output
docker exec "$DBContainerName" psql -c "CREATE DATABASE ..." &>/dev/null

# Restore without messages
docker exec -i "$DBContainerName" psql ... < "$TempDir/backup.sql" &>/dev/null

Complete Restore Script Example¶

# Create database if needed
if docker exec "$DBContainerName" /usr/bin/psql --username=$DBUSER --dbname=postgres -c "CREATE DATABASE $Database OWNER $DBUSER;" 2>/dev/null; then
  echo -e "${GREEN}✓${NC} Database created"
else
  echo -e "${YELLOW}!${NC} Database already exists or creation failed"
fi

# Restore the backup
if docker exec -i "$DBContainerName" /usr/bin/psql --dbname=$Database --username=$DBUSER < "$TempDir/backup.sql" 2>/dev/null; then
  echo -e "${GREEN}✓${NC} Database restored successfully"
else
  echo -e "${RED}✗${NC} Database restoration failed!"
  exit 1
fi

Best Practices¶

For production databases > 1GB: Use custom format (-Fc) for compression and faster restores
Always include --create in backups for easier restoration
Use --clean in backups to include DROP statements for clean restores
Remove 2>/dev/null when debugging to see actual error messages
Keep stdout in pg_dump commands (it's your data!)
Test your restore process regularly to ensure backups are valid

Tags: #postgresql #docker #backup #bash #devops