PostgreSQL Backup Script

postgresbackup.sh

# From http://www.cyberciti.biz/tips/howto-backup-postgresql-databases.html
# and from
# http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux
#!/bin/bash

# License: Eclipse Public License - v 2.0 (https://www.eclipse.org/org/documents/epl-2.0/EPL-2.0.html)
# Updated on November 7, 2022

# Example of how to run in cron
#   0 2 * * * su - postgres /usr/local/sbin/postgresbackup.sh

OUTPUTDIR="/backup/current/postgresql"
USERNAME="postgres"
ENCRYPT_PWD="<password>"

# By the way, the folder needs to already exist. The user postgres will
# not be able to create a folder as it doesn't have the rights.
if [ ! -d "$OUTPUTDIR" ]
then
  echo -e "\n$OUTPUTDIR must already exist before running this script.\n\n"
  exit
fi

lcOwner=`stat -c '%U' $OUTPUTDIR`
lcGroup=`stat -c '%G' $OUTPUTDIR`

if [ "$lcOwner" != "$USERNAME" ] ||  [ "$lcGroup" != "$USERNAME" ]
then
  echo -e "\n$OUTPUTDIR must have owner:group set to $USERNAME.\n\n"
  exit
fi

FULL_BACKUP_QUERY="select datname from pg_database where not datistemplate and datallowconn order by datname;"

echo -e "\n\nPerforming full backups"
echo -e "--------------------------------------------\n"

for DATABASE in `psql -U "$USERNAME" -At -c "$FULL_BACKUP_QUERY"`
do
  # I'm now naming database backups in the following specific format
  #   sql.<database type>.<database name>.gz
  #   For example:
  #     sql.postgresql.Database1.gz
  #     sql.mysql.Database2.gz
  fileoutput="$OUTPUTDIR/sql.postgresql.$DATABASE.txt"
  echo "$fileoutput"

  pg_dump -U "$USERNAME" $DATABASE > "$fileoutput"

done

echo -e "\nEncrypting. . . .\n"
lcZipFile="$OUTPUTDIR/files-in-postgresql.7z"

# If you don't remove the zip file, then 7zip with just refresh.
# And if you have changed the password, then the operation
# will fail.
if [ -f "$lcZipFile" ]
then
  rm "$lcZipFile"
fi

# You need to install 7-zip for this to work.
#   apt-get install p7zip-full p7zip-rar
7z a "$lcZipFile" "$OUTPUTDIR/*.txt" -r -p"$ENCRYPT_PWD" -mhe -mx9 -t7z -y
7z t "$lcZipFile" -p"$ENCRYPT_PWD" -y

pushd "$OUTPUTDIR"
# Ensure that you are in the correct folder.
# By the way, for some reason, using su - postgres, I have to 
# change into the folder before removing files. Otherwise, if I use 
# rm "$OUTPUTDIR/*.txt", I get the following error message:
# rm: cannot remove '/backup/current/postgresql/*.txt': No such file or directory
if [ $PWD == "$OUTPUTDIR" ]
then
  echo -e "\nRemoving files in $PWD matching *.txt.\n"
  rm *.txt
else
  echo -e "\nUnable to remove text files in $OUTPUTDIR.\n"
fi

echo -e "Returning to previous directory.\n"
popd