Logo ← PostgreSQL Blog

PostgreSQL Logical Backup

Introduction

PostgreSQL Logical Backup

Introduction

In PostgreSQL, creating regular backups is not optional — it’s essential. Whether you’re a database administrator, a developer, or managing mission-critical applications, having a solid backup strategy ensures that you can recover quickly from unexpected data loss, corruption, or system failure. This article focuses on logical backups using pg_dump, and presents a practical, production-ready Bash script that automates the backup of multiple databases, manages logs, sends email notifications, and performs cleanup of old backups.

Why Logical Backup? How Is It Different from Physical Backup?

PostgreSQL supports two primary types of backups:

Backup TypeToolDescriptionPhysicalpg_basebackupCopies the entire data directory, including WALs; ideal for full server recovery.Logicalpg_dump, pg_dumpallDumps database objects as SQL or directory files; flexible and selective.

Key Differences

FeatureLogical Backup (pg_dump)Physical Backup (pg_basebackup)GranularityPer database or schemaEntire PostgreSQL instancePortabilityCan restore to different PostgreSQL versionsMust match the exact versionRecoveryObject-level restore, flexibleFull data directory restore, rigidWAL required for restore Not needed RequiredBackup format optionsSQL, Custom, DirectoryRaw data files

Logical dumps are perfect for:

  • Migrating databases
  • Partial restores (e.g., single table)
  • Human-readable structure for audits or version control
  • Lightweight backups for development or testing environments

PostgreSQL Logical Dump Backup Script (Multi-DB)

Here is a practical Bash script to perform logical backups for multiple PostgreSQL databases using pg_dump in directory format (-Fd), which is parallelizable and efficient for large databases.

#!/bin/bash

# -------------------------------------------
# PostgreSQL Backup Script - Dump Format (Fd)
# Author: Oz
# Description: Dumps multiple databases, logs the results,
# sends email notifications, and purges old backups.
# -------------------------------------------
# Environment and Directories
export PGCLIENTENCODING="UTF8"
DATE=$(date +%y%m%d)
TIMESTAMP=$(date '+%d-%m-%Y_%H-%M-%S')
YESTERDAY=$(date -d "-1 days" '+%y%m%d')
TMPDIR="/temp/scripts/log"
LOG_DIRECTORY="/dump_log/log"
BACKUP_DIR="/BACKUP/dump"
BCK_RETENTION=28
# Email settings
BCK_EMAIL="*******@********"
BCK_EMAIL_FROM="*******@********"
# Ensure necessary directories exist
mkdir -p "$TMPDIR" "$LOG_DIRECTORY" "$BACKUP_DIR"
# Temp log files
SUCCESS_LOG="${TMPDIR}/successful_backups.log"
FAILED_LOG="${TMPDIR}/failed_backups.log"
> "$SUCCESS_LOG"
> "$FAILED_LOG"
# List of databases to back up
DATABASES=("test1db" "test2db" "test3db" "test4db" "test5db" "test6db" "test7db" "test8db" "test9db")
# Start backup process
echo "[$(date)] Starting PostgreSQL database backup process..."
for DBNAME in "${DATABASES[@]}"; do
    BCK_LOG="${LOG_DIRECTORY}/pgdump_${TIMESTAMP}_${DBNAME}.log"
    BCK_DUMP="${BACKUP_DIR}/${DBNAME}_${TIMESTAMP}.dmp"
    {
        echo "[$(date)] Starting backup for database: ${DBNAME}"
        echo "Backup file: ${BCK_DUMP}"
        echo "Log file: ${BCK_LOG}"
    } > "${BCK_LOG}"
    # Perform the dump
    pg_dump -h **.*.***.*** -p **** -Fd -v -d "${DBNAME}" -f "${BCK_DUMP}" &>> "${BCK_LOG}"
    RESULT=$?
    if [ $RESULT -ne 0 ]; then
        echo "[$(date)] FAILED backup for ${DBNAME}" | tee -a "${BCK_LOG}" >> "${FAILED_LOG}"
    else
        echo "[$(date)] SUCCESSFUL backup for ${DBNAME}" | tee -a "${BCK_LOG}" >> "${SUCCESS_LOG}"
    fi
done
# Summary & Email Notification
if [ -s "$FAILED_LOG" ]; then
    mailx -v -s "FAILED PostgreSQL Backup Processes" -r "$BCK_EMAIL_FROM" "$BCK_EMAIL" < "$FAILED_LOG"
else
    FINAL_LOG="${LOG_DIRECTORY}/pgdump_${TIMESTAMP}_final.log"
    {
        echo "[$(date)] All database backups completed successfully."
        cat "$SUCCESS_LOG"
    } > "$FINAL_LOG"
    mailx -v -s "PostgreSQL Backup Completed Successfully" -r "$BCK_EMAIL_FROM" "$BCK_EMAIL" < "$FINAL_LOG"
fi
# Cleanup temp logs
rm -f "$SUCCESS_LOG" "$FAILED_LOG"
# Remove old dumps and logs
find "$BACKUP_DIR" "$LOG_DIRECTORY" -maxdepth 1 -mindepth 1 \
    \( -name "*.dmp" -o -name "*.log" \) -mtime +$BCK_RETENTION \
    -exec echo "[$(date +"%Y-%m-%d %H:%M")] Deleting: $(basename {})" \; \
    -exec rm -rf {} \; >> "${LOG_DIRECTORY}/deleted_files.log"
echo "[$(date)] 🧹 Old backups and logs (older than $BCK_RETENTION days) have been cleaned."
echo "[$(date)] Backup script completed."

Output & Behavior Summary

  • Dumps each database in directory format (-Fd) for parallel restore.
  • Creates separate log files for each database.
  • Sends an email if:
  • Any database backup fails
  • All backups succeed
  • Keeps backups and logs for 28 days, then removes them.
  • Supports multiple databases and can easily be adapted to dynamic environments.

Final Thoughts

This script strikes a balance between simplicity and reliability. While logical backups aren’t a replacement for full physical backups in disaster recovery scenarios, they offer unmatched flexibility, especially when restoring specific objects, doing version migrations, or auditing structure.