Change MySQL backup script to use on PostgreSQL

Question

On a few MySQL databases I have I backup them up ny first; mysql_config_editor set -h localhost -G dbbkup -P 3306 -u root -p

Then running a wee script;

#!/bin/bash

BACKUPDIR="/home/deploy/backup-db"
BKUPSSH="[email protected]"
BKUPSERVDIR="/home/deploy/backups/databases"

# Remove files older than 30 days
find $BACKUPDIR/ -mtime +31 -exec rm {} \;

# DO NOT BACKUP these databases
IGNOREDB="
information_schema
mysql
test"

#* MySQL binaries *#
MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)
GZIP=$(which gzip)

# assuming that BACKUPDIR exists
if [ ! -d $BACKUPDIR ]; then
  mkdir -p $BACKUPDIR
else
  :
fi

# get all database listing
DBS="$(mysql --login-path=dbbkup -Bse 'show databases')"

# Set date and time for the file
NOW=$(date +"%Y-%m-%d_%H.%M"); # year-month-day_hour.minute format

# start to dump database one by one
for db in $DBS
do
        DUMP="yes";
        if [ "$IGNOREDB" != "" ]; then
                for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                do
                        if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                DUMP="NO";         # SET value of DUMP to "no"
                                #echo "$i database is being ignored!";
                        fi
                done
        fi

        if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                FILE="$BACKUPDIR/$NOW-$db.sql.gz";
                echo "BACKING UP $db";
        $MYSQLDUMP --login-path=dbbkup --add-drop-database --opt --lock-all-tables --set-gtid-purged=OFF $db | $GZIP > $FILE
        fi
done

# change permissions on files
chmod -R 755 $BACKUPDIR

# rsync backup to 'Larry' the backup server and append the log file
rsync -azv $BACKUPDIR -e ssh $BKUPSSH:$BKUPSERVDIR >> /home/deploy/db_rsync.log 2>&1
RESULT="$?"

# check result of rsync
if [ "$RESULT" != "0" ]; then
    echo -e "rsync exit Code:" $RESULT "\nFailed to rsync databases" >> /home/deploy/db_rsync.log 2>&1
else
    echo "succesfully rsynced databases" >> /home/deploy/db_rsync.log 2>&1
fi

As I'm not a DBA to trade, does anyone know how I can achieve the same by slightly tweaking the script to use for PostgreSQL?

Answer 1

have a look at pg_dump it's very similar to the dump utility by MySQL See: pg_dump documentation

I think most of the change is just changing the flags to match the new utility and replacing the call for mysql with a similar call to psql -l to get the db list (to filter out dbs you want to ignore)