#!/bin/bash
#
# PURPOSE
# 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.
#
# DISCLAIMER
# This script is used at your own risk, without any warranty whatsoever!
#
# USE
# First save the script as /usr/local/bin/bareos-purge-old-volumes.sh
# Then make it owned by root and the group bareos and make it executable:
# chown root.bareos /usr/local/bin/bareos-purge-old-volumes.sh
# chmod 770 /usr/local/bin/bareos-purge-old-volumes.sh
#
# Then make the script run as a Bareos job:
#
#Job {
#  Name = "PurgeOldVolumes"
#  Type = "Admin"
#  Description = "Purges old volumes left behind from AI consolidations"
#  Schedule = "DailyPurgeOldVolumes"
#  JobDefs = "DefaultJob"
#  Client = bareos-fd
#  RunAfterJob = "/usr/local/bin/bareos-purge-old-volumes.sh"
#  Priority = 31                   # run after consolidation
# }
#
# Also make a new schedule for the job, which should be prioritised below the priority of the Consolidate job 
# and ran for example one hour after the Consolidate Job:
#
# Schedule {
#  Name = "DailyPurgeOldVolumes"         # Purges old volumes left behind from AI consolidations (priority 31)
#  Run = Incremental mon-sun at 13:00
# }


#
# REQUIREMENTS
# Bareos version 16.2.5 or later installed with PostgreSQL as databse
# Bash
#
# 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 AND VERSION
# Written for PostgreSQL by Thomas Hojemo. Revised 2019-05-02.
# Based on original script for MySQL from dpcushing @ https://bugs.bareos.org/view.php?id=779
#

# ---- START OF SETTINGS TO CONFIGURE ------------------------------------------------------------------------------------
#
# RUN SCRIPT AS USER
# 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'
#
# SET BAREOS STORAGE LOCATION
# From where the files on disk will be deleted. Is normally /var/lib/bareos/storage/ Change directory below if needed:
dirName='/var/lib/bareos/storage/'
#
# ---- 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 DISTINCT media.volumename FROM media WHERE media.volstatus NOT IN ('Recycle','Append','Purged') AND media.volumename NOT IN (SELECT DISTINCT volumename FROM media,jobmedia,job WHERE media.mediaid = jobmedia.mediaid AND jobmedia.jobid = job.jobid AND job.jobstatus NOT IN ('T','E','e','f','A', 'W')) AND NOT EXISTS (SELECT 1 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 DISTINCT media.volumename FROM media WHERE media.volstatus NOT IN ('Recycle','Append','Purged') AND media.volumename NOT IN (SELECT DISTINCT volumename FROM media,jobmedia,job WHERE media.mediaid = jobmedia.mediaid AND jobmedia.jobid = job.jobid AND job.jobstatus NOT IN ('T','E','e','f','A', 'W')) AND NOT EXISTS (SELECT 1 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)
fileName="$dirName$volName"

# 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

# Delete file from disk
rm $fileName

done

fi