Backups POSTGRES Databases¶

#!/usr/bin/env bash
# gunzip -c file.sql.gzip > dump.sql
# gpg --decrypt-files /tmp/file.gpg

pgHost="${1:-localhost}"
pgUser="${2:-postgres}"
backupsOwner="${3:-root}"
pgDumpsDir="${4:-/backups/psql}"
oldDays=${5:-30}
excludedBases="${6}"

dateTime=$(date +%Y-%m-%d-%H-%M-%S)
excludedBaseBases="template0|template1|postgres"

psql=$(which psql)
pg_dump=$(which pg_dump)
pg_dumpall=$(which pg_dumpall)
tar=$(which tar)

export PGHOST="${pgHost}"
export PGUSER="${pgUser}"

if [ -n "${excludedBases}" ]; then
    excludedBases=$(echo "${excludedBases}" | sed 's/ /|/g' | sed 's/,/|/g' | sed 's/;/|/g')
    excludedBases="${excludedBaseBases}|${excludedBases}"
else
    excludedBases="${excludedBaseBases}"
fi

databasesString=$(${psql} -l --csv | tail -n +2 | cut -d ',' -f1 |  egrep -v "${excludedBases}")
databases=(${databasesString})

if [ ! -d "${pgDumpsDir}" ]; then
    mkdir "${pgDumpsDir}"
    chown "${backupsOwner}" "${pgDumpsDir}" 
    chmod ug+rw "${pgDumpsDir}"
    chmod o-rwx "${pgDumpsDir}"
fi

for base in "${databases[@]}"
do
    cd "${pgDumpsDir}"

    name="${base}-${pgHost}-${dateTime}"
    echo "Backup database: ${base}"

    if [ ! -d "${base}" ]; then
        echo "Create directory: ${base}"
        mkdir "${base}"
        chown "${backupsOwner}" "${base}"
        chmod ug+rw "${base}"
        chmod o-rwx "${base}"
    fi

    echo "Change directory: ${base}"
    cd ${base}

    mkdir "${name}"
    chown "${backupsOwner}" "${name}"
    cd "${name}"
    log="${name}.log"

    echo "Create log: ${log}"
    touch "${log}"
    echo "Dump database: ${base}"
    ${pg_dump} "${base}" > "${name}.sql" 2> ${log}

    #if [ "$(date +%d)" -eq "01" ]; then
    #   echo "Vacuum database: ${base}"
    #   ${psql} -c 'VACUUM FULL VERBOSE ANALYZE;' "${base}" >> "${log}" 2>&1
    #   echo "Reindex base: ${base}"
    #   psql -c "REINDEX DATABASE ${base};" "${base}" >> "${log}" 2>&1
    #fi

    cd ../
    echo "Create archive: ${name}.tgz"
    ${tar} -cjvf "${name}.tgz" "${name}"
    chmod ug+rw "${name}.tgz"
    chmod o-rwx "${name}.tgz"

    echo "Remove temp files: ${name}"
    rm -Rf "${name}"

    countFiles=$(ls "./" | wc -l)
    if [ ${countFiles} -ge ${oldDays} ]; then
        echo "Delete old backups: count (${countFiles})"
        find ./ -name "*.tgz" -mdateTime +${oldDays} -type f -delete > /dev/null 2>&1
    else
        echo "No old dumps files"
    fi

done

echo "Create dump All Roles"
cd "${pgDumpsDir}"

if [ ! -d "roles" ]; then
    mkdir "roles"
    chown "${backupsOwner}" "roles"
    chmod ug+rw "roles"
    chmod o-rwx "roles"
fi

cd "roles"
name="roles-${pgHost}-${dateTime}"
${pg_dumpall} --roles-only > "${name}.sql"
${tar} -cjvf "${name}.tgz" "${name}.sql"
chmod ug+rw "${name}.tgz"
chmod o-rwx "${name}.tgz"
rm "${name}.sql"

countFiles=$(ls "./" | wc -l)
if [ ${countFiles} -ge ${oldDays} ]; then
    echo "Delete old backups: count (${countFiles})"
    find ./ -name "*.tgz" -mdateTime +${oldDays} -type f -delete > /dev/null 2>&1
else
    echo "No old dumps files"
fi

exit 0