0

I have a bash script to backup MySQL databases. I have pasted the relevant part below (ignore the variables).

The 2 lines I think I need to change to make this work in PostgreSQL are;

DBS="$(mysql --login-path=dbbkup -Bse 'show databases')"

and

$MYSQLDUMP --login-path=dbbkup --add-drop-database --single-transaction --triggers --routines --events --set-gtid-purged=OFF $db | $GZIP > $FILE

Can anyone advise as to how I can get the same functionality with PostgreSQL?

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

# 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 --single-transaction --triggers --routines --events --set-gtid-purged=OFF $db | $GZIP > $FILE
        fi
done
0

I think I've cracked it...with a LOT of Googling and chopping of code!

DBS="$(sudo -u postgres psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d')"

and;

sudo -u postgres $PGDUMP -Fc --verbose $db | $GZIP > $FILE

# get all database listing
DBS="$(sudo -u postgres psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d')"

# 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";
                      sudo -u postgres $PGDUMP -Fc --verbose $db | $GZIP > $FILE
        fi
done

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.