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)