View Issue Details

IDProjectCategoryView StatusLast Update
0000779bareos-core[All Projects] directorpublic2019-07-10 17:37
ReporterdpcushingAssigned To 
PrioritynormalSeverityminorReproducibilityalways
Status acknowledgedResolutionopen 
PlatformLinuxOSCentOSOS Version7
Product Version16.2.4 
Target VersionFixed in Version 
Summary0000779: Always Incremental Consolidate job does not prune the storage volumes
DescriptionParagraph 23.3.3 in the Bareos manual has a note in the storage pool that says 'Don't prune jobs from the volume. Let this be done by Consolidate.' The consolidate job does purge the jobs from the database, but it does not prune the storage pool volume(s). Volumes with no reference to existing jobs are left in the in the Full or Used state until they are manually pruned.
Steps To ReproduceConfigure daily backups with Always Incremental and configure a daily consolidation job. None of the volumes from the AI related pools will be recycled.
Additional InformationI first discover this when my pool fill it unexpectedly to it's Maximum Volumes. You can query the database with the below and any volume that is returned and does not have a status of 'Purged' is a problem.

SELECT m.MediaId, m.VolumeName, m.VolStatus, m.Recycle
FROM bareos.Media m
WHERE NOT EXISTS (SELECT 1 FROM JobMedia jm
 WHERE jm.MediaId = m.MediaId)
;
TagsNo tags attached.
bareos-master: impact
bareos-master: action
bareos-18.2: impact
bareos-18.2: action
bareos-17.2: impact
bareos-17.2: action
bareos-16.2: impact
bareos-16.2: action
bareos-15.2: impact
bareos-15.2: action
bareos-14.2: impact
bareos-14.2: action
bareos-13.2: impact
bareos-13.2: action
bareos-12.4: impact
bareos-12.4: action

Activities

dpcushing

dpcushing

2017-03-14 01:27

reporter   ~0002606

For anybody else that may be facing this issue, I wrote a shell script that I run in an admin job after running consolidate the prunes the unused volumes. Here's the content ...

#!/bin/bash
# grab the database credentials from existing configuration files
catalogFile=`find /etc/bareos/bareos-dir.d/catalog/ -type f`
dbUser=`grep dbuser $catalogFile | grep -o '".*"' | sed 's/"//g'`
dbPwd=`grep dbpassword $catalogFile | grep -o '".*"' | sed 's/"//g'`

# Get a list of volumes no longer in use and submit them to the console for pruning.
# Query for a list of volumes
emptyVols=$(mysql bareos -u $dbUser -p$dbPwd -se "SELECT m.VolumeName FROM bareos.Media m where m.VolStatus not in ('Append','Purged') and not exists (select 1 from bareos.JobMedia jm where jm.MediaId=m.MediaId);")
# Submit volumes to bconsole for pruning
for volName in $emptyVols
do
/bin/bconsole << EOD
prune volume=$volName yes
quit
EOD
done
morfair

morfair

2018-01-31 12:18

reporter   ~0002894

Does this mean that unnecessary old deleted data will remain in consolidated forever?
dpcushing

dpcushing

2018-01-31 15:08

reporter   ~0002895

@morfair - As noted, the jobs and associated contents are correctly purged from the database. So the database does not grow out of control and does not contain the 'old deleted data' forever.

Because the volumes are no pruned, they can never be recycled. So the problem is that your storage pools, both the incremental and the consolidated, will grow until you run out of space. The 'old deleted data' will remain in those volumes forever or until they are manually pruned. The script I provided resolves this issue by finding and pruning those non referenced volumes after the consolidation job is completed.
morfair

morfair

2018-01-31 15:12

reporter   ~0002896

@dpcushing - Sorry for some offtopic, but I do not understand how to remove jobs that was consolidated. "prune jobs" has now affect and I have many backuped files that no presend in FD older 1 month. I dont want keep this data and don't know how to set retention period for AI jobs.
dpcushing

dpcushing

2018-01-31 15:48

reporter   ~0002897

@morfair - This probably isn't appropriate for discussion under this bug report. I'd recommend that you search the bareos-users group to see if your question is answered there. If not, post a new question and there are lots of helpful users who can provide you with recommended solutions.

https://groups.google.com/forum/?fromgroups#!forum/bareos-users
hojemo

hojemo

2019-05-01 21:48

reporter  

bareos-purge-old-volumes.txt (5,259 bytes)
#!/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
hojemo

hojemo

2019-05-01 21:48

reporter   ~0003350

Hi!
I have made a bash script for purging these volumes left by Bareos. The script is adapted for PostgreSQL and senses if it is run as root or another user with direct access to PostgreSQL and adapts accordingly. It is adapted from the earlier scripts posted for MySQL, thanks to these authors for providing a start. It works for me but you use it on your own risk - I am new to bash programming. Please see below. I have also attached the script as a text file in case line/word wrap would be a problem.

Cheers,
Thomas

#!/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
hojemo

hojemo

2019-05-02 16:47

reporter   ~0003351

Hello again!
Unfortunately the script I sent yesterday did not detect a backup that just had been started. I have added an extra SQL AND clause to check this. Please use the corrected script below instead. It works for me, but you of course test it on your risk.

Best wishes,
Thomas

#!/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

bareos-purge-old-volumes-2.txt (6,454 bytes)
#!/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

Issue History

Date Modified Username Field Change
2017-02-08 22:33 dpcushing New Issue
2017-03-14 01:27 dpcushing Note Added: 0002606
2018-01-31 12:18 morfair Note Added: 0002894
2018-01-31 15:08 dpcushing Note Added: 0002895
2018-01-31 15:12 morfair Note Added: 0002896
2018-01-31 15:48 dpcushing Note Added: 0002897
2019-05-01 21:48 hojemo File Added: bareos-purge-old-volumes.txt
2019-05-01 21:48 hojemo Note Added: 0003350
2019-05-02 16:47 hojemo File Added: bareos-purge-old-volumes-2.txt
2019-05-02 16:47 hojemo Note Added: 0003351
2019-07-10 17:37 arogge Status new => acknowledged