Re: [Bacula-users] Which tapes to put into the tape library?

On Thu, January 21, 2010 11:36 am, John Jorgensen wrote:
>>>>>> "dan" == Dan Langille <dan AT langille DOT org> writes:
>
>     dan> I have about 100 tapes.  My tape library holds only 10 tapes.
>     dan> How do you decide which tapes to load into the library?  What
> strategy do
>     dan> you use?
>
> I have a variant of your query that orders the volumes by the
> date when they should be eligible for reuse.  (Our mechanism for
> keeping the first backups of each month for a year is to adjust
> their volretention above the default, so the volretention is not
> uniform.)
>
>  SELECT VolumeName AS Volume,
>         CASE WHEN InChanger != 0
>           THEN text(Slot)
>           ELSE ''
>         END AS SLOT,
>         LabelDate, FirstWritten, LastWritten,
>         CASE LastWritten IS NULL
>           WHEN TRUE THEN now()
>           ELSE LastWritten + CAST(CAST(Media.VolRetention AS text) AS
> interval)
>         END AS Eligible,
>       Media.VolRetention/3600/24 AS Ret,
>         VolJobs AS Jobs, VolBlocks,
>       VolBytes/1024/1024/1024 AS GB,
>         VolStatus AS STATUS, Media.Recycle
>  FROM Media LEFT JOIN Pool USING (PoolId)
>  WHERE Pool.name = 'Generic Full ML6000 Pool'
>  ORDER BY Eligible,volumename
>
> So when it's time to rotate tapes out of the library, I run the
> query and load the volumes at the top of the list back into the
> library (I also store the tapes on the shelf in more or less the
> same order, so there is a unautomated fallback mechanism :-).:

That's nice.  I altered the query slightly, to remove non-candidates based
upon unsuitable status.  Compare my original output with your query:

Your [amended] query:

 SELECT VolumeName AS Volume,
        CASE WHEN InChanger != 0
          THEN text(Slot)
          ELSE ''
        END AS SLOT,
        CASE LastWritten IS NULL
          WHEN TRUE THEN now()
          ELSE LastWritten + CAST(CAST(Media.VolRetention AS text) AS
interval)
        END AS Eligible,
        VolStatus AS STATUS, Media.Recycle
 FROM Media LEFT JOIN Pool USING (PoolId)
 WHERE Pool.name = 'Default'
   AND volstatus NOT IN ('Disabled', 'Error', 'Read-Only', 'Used')
 ORDER BY Eligible,volumename
 LIMIT 10;

     volume     | slot |           eligible            | status | recycle
----------------+------+-------------------------------+--------+---------
 DLT7000-001111 | 0    | 2009-12-07 14:03:20+00        | Full   |       1
 DLT7000-JYN220 | 0    | 2009-12-17 08:16:41+00        | Full   |       1
 DLT7000-JYN221 | 0    | 2010-01-04 07:12:47+00        | Full   |       1
 DLT7000-JYN231 | 0    | 2010-01-10 07:22:38+00        | Full   |       1
 DGG441         | 8    | 2010-01-21 17:27:27.572582+00 | Append |       1
 DGG442         | 6    | 2010-01-21 17:27:27.572582+00 | Append |       1
 DGG443         | 3    | 2010-01-21 17:27:27.572582+00 | Append |       1
 DGG457         | 7    | 2010-01-21 17:27:27.572582+00 | Append |       1
 DGG465         | 10   | 2010-01-21 17:27:27.572582+00 | Append |       1
 DGG467         | 4    | 2010-01-21 17:27:27.572582+00 | Append |       1
(10 rows)

We need to sort by status, to get the Append tapes in there first:

 SELECT VolumeName AS Volume,
        CASE WHEN InChanger != 0
          THEN text(Slot)
          ELSE ''
        END AS SLOT,
        CASE LastWritten IS NULL
          WHEN TRUE THEN now()
          ELSE LastWritten + CAST(CAST(Media.VolRetention AS text) AS
interval)
        END AS Eligible,
        VolStatus AS STATUS, Media.Recycle
 FROM Media LEFT JOIN Pool USING (PoolId)
 WHERE Pool.name = 'Default'
   AND volstatus NOT IN ('Disabled', 'Error', 'Read-Only', 'Used')
 ORDER BY status, Eligible,volumename
 LIMIT 10;


     volume     | slot |           eligible           | status | recycle
----------------+------+------------------------------+--------+---------
 DGG441         | 8    | 2010-01-21 17:30:24.18334+00 | Append |       1
 DGG442         | 6    | 2010-01-21 17:30:24.18334+00 | Append |       1
 DGG443         | 3    | 2010-01-21 17:30:24.18334+00 | Append |       1
 DGG457         | 7    | 2010-01-21 17:30:24.18334+00 | Append |       1
 DGG465         | 10   | 2010-01-21 17:30:24.18334+00 | Append |       1
 DGG467         | 4    | 2010-01-21 17:30:24.18334+00 | Append |       1
 DGG469         | 5    | 2010-01-21 17:30:24.18334+00 | Append |       1
 DGG471         | 9    | 2010-01-21 17:30:24.18334+00 | Append |       1
 DGG472         | 1    | 2010-01-21 17:30:24.18334+00 | Append |       1
 DLT7000-001111 | 0    | 2009-12-07 14:03:20+00       | Full   |       1
(10 rows)

Thank you.


-- 
Dan Langille -- http://langille.org/


------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users