Hi! I have made a bash script adapted for PostgreSQL that senses if it is run as root or another user with direct access to PostgreSQL and adapts accordingly. The script 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 -- You received this message because you are subscribed to the Google Groups "bareos-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. For more options, visit https://groups.google.com/d/optout.
#!/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
