#!/bin/bash
#
# USE
# Bash script to purge old Bareos volumes both from the Catalog of Bareos as well as from physical disk,
# as Bareos unfortunately does not delete old AlwaysIncremental volumes of type VirtualFull automatically.
# Remaining there will be files on disk around 1 kB in size.
# DISCLAIMER
# This script is used at your own risk, without any warranty whatsoever!
#
# SCRIPT CONFIGURATION
# Save the script as /usr/local/bin/bareos-purge-old-volumes.sh
# Then make it owned by postgres and the group bareos and make it executable:
# chown postgres.bareos /usr/local/bin/bareos-purge-old-volumes.sh
# chmod 770 /usr/local/bin/bareos-purge-old-volumes.sh
#
# REQUIREMENT
# Bareos version 16.2.5 or later, as the truncate command is available from this version onwards.
#
# PLEASE NOTE
# In order for this script to delete old volumes from disk you may need to set these 3 directives in your Bareos Pool directive(s):
#  Recycle = yes                       # Bareos can automatically recycle Volumes
#  Auto Prune = yes                    # Prune expired volumes
#  Action on Purge = Truncate          # Delete old backups
# Please read more about these directives in the Bareos manual at https://docs.bareos.org/ to understand them before making these changes.
#
# If you choose to set these 3 directives, then update old volumes with the new settings from the Pool(s) through running bconsole:
# bconsole <Enter>
# update volume <Enter>
# 14 <Enter> to choose 14: All Volumes from all Pools
#
# AUTHOR
# Written for PostgreSQL by Thomas Hojemo.
# Based on original bash script for MySQL from dpcushing @ https://bugs.bareos.org/view.php?id=779


# ---- START OF SETTINGS TO CONFIGURE ------------------------------------------------------------------------------------
#
# RUN SCRIPT AS USER
# Run this script from terminal or /etc/crontab as root or from Bareos (could be a RunAfterJob in a Job directive).
# In case this script is run as root you need to set the username root should su into below - should normally be postgres:
suUser='postgres'
#
# SET DATABASE NAME
# The Catalog normally resides in the database bareos. Change below if you use another database name:
database='bareos'
#
# ---- END OF SETTINGS TO CONFIGURE --------------------------------------------------------------------------------------


# DEBUGGING
# Should normally be turned off, i.e. commented away:
#set -x

# SCRIPT USER
# Check which user we are now (does not need to be changed).
actualUser="$LOGNAME"

# SET WORKING DIRECTORY
# Change to /tmp directory in order to not have problems with directory permissions.
cd /tmp

# QUERY FOR VOLUMES THAT ARE NOT IN USE ANYMORE
#
if [ "$actualUser" == "root" ] # If we are root we need to su to access PostgreSQL
then # Run this SQL query in PostgreSQL via su to the PostgreSQL user
  emptyVols=$(su $suUser -c "psql -d $database -t -c \"SELECT media.volumename FROM media WHERE media.volstatus NOT IN ('Recycle','Append','Purged') AND NOT EXISTS (SELECT jobmedia.jobid FROM jobmedia WHERE jobmedia.mediaid=media.mediaid)\"")
else # Else - in case we are a user with direct PostgreSQL access - we can run the SQL query directly
  emptyVols=$(psql -d $database -t -c "SELECT media.volumename FROM media WHERE media.volstatus NOT IN ('Recycle','Append','Purged') AND NOT EXISTS (SELECT jobmedia.jobid FROM jobmedia WHERE jobmedia.mediaid=media.mediaid)")
fi

# Trim any whitespace before and after string
emptyVols=$(echo $emptyVols | xargs)

# GIVE CHANCE TO ABORT SCRIPT
# If there are volumes to purge and delete give chance to abort script
if [ -n "$emptyVols" ]
then
echo "WARNING: These volumes will be purged from Bareos and deleted from disk:"
echo $emptyVols
echo "Press Ctrl+C within 10 seconds to abort."
sleep 10

# PURGE AND DELETE VOLUMES
# Get pool name and storage name for each volume
for volName in $emptyVols # Loop through each volume name in the list we extracted via the SQL query above
do
if [ "$actualUser" == "root" ] # If we are root we need to su to access PostgreSQL
then # Run this SQL query in PostgreSQL via su to the PostgreSQL user
poolName=$(su $suUser -c "psql -d $database -t -c \"SELECT pool.name FROM pool WHERE pool.poolid = (SELECT media.poolid FROM media where media.volumename='$volName');\"")
storageName=$(su $suUser -c "psql -d $database -t -c \"SELECT storage.name FROM storage where storage.storageid = (SELECT media.storageid FROM media where media.volumename='$volName');\"")
else # Else - in case we are a user with direct PostgreSQL access - we can run the SQL query directly
poolName=$(psql -d $database -t -c "SELECT pool.name FROM pool WHERE pool.poolid = (SELECT media.poolid FROM media where media.volumename='$volName');")
storageName=$(psql -d $database -t -c "SELECT storage.name FROM storage where storage.storageid = (SELECT media.storageid FROM media where media.volumename='$volName');")
fi

# Trim any whitespace before and after string
poolName=$(echo $poolName | xargs)
storageName=$(echo $storageName | xargs)

# Run bconsole command to purge, truncate and delete volumes
bconsole << EOD
purge volume=$volName pool=$poolName storage=$storageName yes
truncate volstatus=Purged volume=$volName pool=$poolName storage=$storageName yes
quit
EOD

done

fi
