postgres sql backup

create a simple script for Linux  that will create backup for PostgreSQL

USER='postgres' PASSWORD='B!XCO@2o26' and location is /usr/src/postgresql-backup/all-databases

where all user database is created and one on another location /usr/src/postgresql-backup/bacula where each data base will be created 

#!/bin/bash

# PostgreSQL Backup Script

# This script creates backups of all PostgreSQL databases

# Configuration

PGUSER="postgres"

PGPASSWORD="B!XCO@2o26"

BACKUP_DIR_ALL="/usr/src/postgresql-backup/all-databases"

BACKUP_DIR_INDIVIDUAL="/usr/src/postgresql-backup/bacula"

DATE=$(date +%Y%m%d_%H%M%S)

RETENTION_DAYS=7

# Export password for pg_dump and pg_dumpall

export PGPASSWORD="$PGPASSWORD"

# Create backup directories if they don't exist

mkdir -p "$BACKUP_DIR_ALL"

mkdir -p "$BACKUP_DIR_INDIVIDUAL"

echo "=== PostgreSQL Backup Started at $(date) ==="

# Backup all databases (global backup)

echo "Creating backup of all databases..."

ALL_BACKUP_FILE="$BACKUP_DIR_ALL/all-databases-$DATE.sql"

pg_dumpall -U "$PGUSER" > "$ALL_BACKUP_FILE"

if [ $? -eq 0 ]; then

    echo "All databases backup created successfully: $ALL_BACKUP_FILE"

    # Compress the backup

    gzip "$ALL_BACKUP_FILE"

    echo "Backup compressed: $ALL_BACKUP_FILE.gz"

else

    echo "ERROR: All databases backup failed!"

fi

# Backup individual databases

echo "Creating individual database backups..."

DATABASES=$(psql -U "$PGUSER" -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';")

for DB in $DATABASES; do

    # Skip empty lines

    if [ -z "$DB" ]; then

        continue

    fi

    

    echo "Backing up database: $DB"

    INDIVIDUAL_BACKUP_FILE="$BACKUP_DIR_INDIVIDUAL/${DB}-$DATE.sql"

    

    pg_dump -U "$PGUSER" "$DB" > "$INDIVIDUAL_BACKUP_FILE"

    

    if [ $? -eq 0 ]; then

        echo "Database $DB backed up successfully: $INDIVIDUAL_BACKUP_FILE"

        # Compress the backup

        gzip "$INDIVIDUAL_BACKUP_FILE"

        echo "Backup compressed: $INDIVIDUAL_BACKUP_FILE.gz"

    else

        echo "ERROR: Backup failed for database $DB"

    fi

done

# Clean up old backups (older than RETENTION_DAYS)

echo "Cleaning up old backups (older than $RETENTION_DAYS days)..."

find "$BACKUP_DIR_ALL" -name "*.sql.gz" -type f -mtime +$RETENTION_DAYS -delete

find "$BACKUP_DIR_INDIVIDUAL" -name "*.sql.gz" -type f -mtime +$RETENTION_DAYS -delete

echo "Old backups cleaned up."

# Unset password

unset PGPASSWORD

echo "=== PostgreSQL Backup Completed at $(date) ==="

echo ""