Re: [Bacula-users] Volumes are not being recycled?



Hello Kern​,​
I noticed that LastWritten value is stored in UTC. Therefore the results of both queries are in UTC. These values can be converted to​ ​the​ ​database session​ ​local times. Unfortunately, the local​ ​time for a user issuing this query through bconsole can be different from the database session local time (if the bconsole host is located in a different time zone than the bacula database​ ​server).

For a PostgreSQL bacula database:

# 21
:List Media for a selected Pool
*Enter Pool name:
SELECT MediaId,VolumeName,Media.VolRetention,Media.LastWritten​ ​at time zone current_setting('TIMEZONE'),to_timestamp(extract('epoch' from LastWritten)+Media.VolRetention) at time​ ​zone​ ​current_setting('TIMEZONE') as ExpiresAt FROM Media,Pool
 WHERE Name='%1' AND Media.PoolId=Pool.PoolId;

For a MySQL bacula database:

# 21
:List Media for a selected Pool
*Enter Pool name:
SELECT​ ​MediaId,VolumeName,Media.VolRetention,CONVERT_TZ(Media.LastWritten,'+00:00',@@session.time_zone),CONVERT_TZ(FROM_UNIXTIME(UNIX_TIMESTAMP(Media.LastWritten)+Media.VolRetention),'+00:00',@@session.time_zone) AS ExpiresAt
 FROM Media,Pool
 WHERE Name='%1' AND Media.PoolId=Pool.PoolId;

I removed the "+1" second from both queries since ​the result​ makes more sense to me. I run tests in a "+01:00" (CET)​ ​time zone offset.

Best regards,
Ana
On Thu, Feb 18, 2016 at 8:44 AM, Kern Sibbald <kern@xxxxxxxxxxx> wrote:
Hello Ana,

It may be possible that your code would work.  However, please be aware that it was *very* complicated to get the current code to produce the right results.  It is very easy to get an answer that is wrong by 1 hour or something like that.

Thus anything that one would submit would need to be *really* well tested.

Best regards,
Kern

On 02/18/2016 10:45 AM, Ana Emília M. Arruda wrote:
Hello Kern,

Thank you for your clear explanation. I was thinking about an
"ExpiresAt" field instead of an "ExpiresIn". The meaning of an ExpiresIn
field is perfectly clear for me expressed in seconds. Also, I agree that
the results from bconsole commands should be less human-readable since
we have GUI interfaces for this purpose. IMHO these values should be in
a kind of raw format that can make their use easier with/in other
languages/frameworks.

Maybe the bellow PostgreSQL/MySQL queries (included in query.sql) could
help with an "ExpiresAt" field:

     21: List Media for a selected Pool
Choose a query (1-21): 21
Enter Pool name: File
+---------+------------+--------------+---------------------+---------------------+
| mediaid | volumename | volretention | lastwritten         |
expiresat           |
+---------+------------+--------------+---------------------+---------------------+
|       1 | Vol-0001   |   31,536,000 | 2016-01-21 23:10:03 | 2017-01-20
23:10:04 |
+---------+------------+--------------+---------------------+---------------------+

For a PostgreSQL bacula database:

# 21
:List Media for a selected Pool
*Enter Pool name:
SELECT
MediaId,VolumeName,Media.VolRetention,Media.LastWritten,to_timestamp(extract('epoch'
from LastWritten)+Media.VolRetention+1) at time zone 'UTC' as ExpiresAt
  FROM Media,Pool
  WHERE Name='%1' AND Media.PoolId=Pool.PoolId;


For a MySQL bacula database:

# 21
:List Media for a selected Pool
*Enter Pool name:
SELECT
MediaId,VolumeName,Media.VolRetention,Media.LastWritten,FROM_UNIXTIME(UNIX_TIMESTAMP(LastWritten)+Media.VolRetention+1)
AS ExpiresAt
  FROM Media,Pool
  WHERE Name='%1' AND Media.PoolId=Pool.PoolId;

I'm not sure about adding one second to this value...

Am I misunderstanding something here?

Thank you.

Best regards,
Ana



On Thu, Feb 18, 2016 at 12:58 AM, Kern Sibbald <kern@xxxxxxxxxxx
<mailto:kern@xxxxxxxxxxx>> wrote:

    Hello Heitor,

    One of the basic design conditions for Bacula is that as much as
    possible that goes into it officially should be understandable and
    maintainable by an average C/C++ programmer.  Once things start
    getting complicated, I slow down ...

    Best regards,
    Kern

    On 02/18/2016 08:25 AM, Heitor Faria wrote:

        Kern,

        Sorry for the dumb question but could not bconsole make the values
        conversion in similar way to this shell script bytes conversion
        snippet
        before printing?

        xargs -i echo 'scale=2; {}/1073741824' | bc

        May it would be a more database independent approach.

        Regards,
        ===========================================================================
        Heitor Medrado de Faria - LPIC-III | ITIL-F |  Bacula Systems
        Certified
        Administrator II
        Do you need Bacula training?
        <http://bacula.us/video-classes>http://bacula.us/video-classes/
        +55 61 8268-4220 <tel:%2B55%2061%208268-4220>
        Site: <http://bacula.us>http://bacula.us FB: heitor.faria
        ===========================================================================

        Enviado por TypeApp <http://www.typeapp.com/r>

        Em 17 de fev de 2016, em 20:05, Kern Sibbald <kern@xxxxxxxxxxx
        <mailto:kern@xxxxxxxxxxx>
        <mailto:kern@xxxxxxxxxxx <mailto:kern@xxxxxxxxxxx>>> escreveu:


             Hello Ana (and Heitor),

             Please note that at the current time, the list command is
        generic, and
             thus it only knows how to print character strings that have
        been
             returned by the SQL engine.  Though there may be some way
        to tell SQL
             that the result we get for expiresin is a "duration" (note:
        very
             different from a date such as LastWritten), I do not know
        how to do it.

             Thus for the moment, we are limited to displaying SQL generated
             character strings in the form that SQL gives them to us.

             The main point of the new expiresin field is that it is not
        zero, the
             retention period has not expired.  If it is a big positive
        number
             (number of seconds remaining before the volume expires),
        then the Volume
             will not be recycled.

             Many people forget that the retention period *begins* from the
             LastWritten time, which means that as long as you are
        writing on the
             Volume, nothing will be expired.  Maybe devoting a bit of
        thought to
             that particular point, and what would happen if we changed
        it, would
             make retention periods easier to understand.

             Best regards,
             Kern

             On 02/16/2016 07:19 PM, Ana Emília M. Arruda wrote:

                 Hello Kern and Heitor,

                 I can see the ExpiresIn field in Bacula 7.4.0 version.
                 Maybe the ExpiresIn value could be more useful if
        displayed in
                 the same
                 format as the LastWritten field.

                 Best regards,
                 Ana

                 On Tue, Feb 16, 2016 at 2:46 AM, Heitor Faria
        <heitor@xxxxxxxxxxxxx <mailto:heitor@xxxxxxxxxxxxx>
                 <mailto:heitor@xxxxxxxxxxxxx
        <mailto:heitor@xxxxxxxxxxxxx>>> wrote:

                             Recently I got tired of doing the mental
        gymnastics
                             to see when Volumes
                             will expire and with Eric's SQL help, we
        modified
                             the list (and llist)
                             media output to eliminate one or two of the
        columns
                             in the case of list
                             media, but to add an "expiresin" field,
        which makes
                             it much easier to
                             see when a volume will expire.

                             This code has been in the public git
        repository in
                             Branch-7.4 along with
                             a number of bug fixes since the 7.4.0
        release. If
                             you are interested in
                             simplifying the Volume expiration mind
        gymnastics
                             you might checkout and
                             try the new code.

                             By the way, I thought that the above
        feature was
                             added after the 7.4.0
                             release, but according to what I see in the
        repo,
                             much to my surprise it
                             should also be in the released 7.4.0 version.

                             I would be interested in any feedback.


                         Hello, Kern: this feature is great and I'm
        thankful for
                         it, but is there a way
                         to make information human readable?


                     What do you mean?

                     Please show me what you currently see and what you
        prefer to
                     see.


                 Hello, Kern: sorry for being laconic.
                 When I mean "human readable" is in the sense of ls, df
        and other
                 Linux commands:

                 "-h, --human-readable
                 print sizes in human readable format (e.g., 1K 234M 2G)"

                 *What I see today:*

                 *list media pool=File

        +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
                 | MediaId | VolumeName | VolStatus | Enabled | VolBytes |
                 VolFiles | VolRetention | Recycle | Slot | InChanger |
        MediaType |
                 LastWritten | ExpiresIn |

        +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
                 | 1 | Vol-0001 | Error | 1 | 1,286,119,412 |
                 0 | 31,536,000 | 1 | 0 | 0 | File1 |
                 2015-10-21 23:57:35 | 21,428,702 |

        +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+

                 *What I think would make the user life easier:*

                 *list media pool=File human

        +---------+------------+-----------+---------+----------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
                 | MediaId | VolumeName | VolStatus | Enabled | VolBytes
        | VolFiles |
                 VolRetention | Recycle | Slot | InChanger | MediaType |
        LastWritten
                 | ExpiresIn |

        +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
                 | 1 | Vol-0001 | Error | 1 | 1,29 GB | 0
                 | 31,536,000 | 1 | 0 | 0 | File1 | 2015-10-21
                 23:57:35 | 248 days |

        +---------+------------+-----------+---------+----------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+

                     Best regards,
                     Kern


                 Regards,
                 --

        ------------------------------------------------------------------------

                 Heitor Medrado de Faria - LPIC-III | ITIL-F | Bacula
        Systems
                 Certified Administrator II
                 Do you need Bacula training?
        http://bacula.us/video-classes/
        +55 61 8268-4220 <tel:%2B55%2061%208268-4220>
        <tel:%2B55%2061%208268-4220>
                 Site: http://bacula.us FB: heitor.faria

        ------------------------------------------------------------------------



        ------------------------------------------------------------------------

                 Site24x7 APM Insight: Get Deep Visibility into Application
                 Performance
                 APM + Mobile APM + RUM: Monitor 3 App instances at just
        $35/Month
                 Monitor end-to-end web transactions and take corrective
        actions now
                 Troubleshoot faster and improve end-user experience.
        Signup Now!
        http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140

        ------------------------------------------------------------------------

                 Bacula-users mailing list
        Bacula-users@xxxxxxxxxxxxxxxxxxxxx
        <mailto:Bacula-users@xxxxxxxxxxxxxxxxxxxxx>
                 <mailto:Bacula-users@xxxxxxxxxxxxxxxxxxxxx
        <mailto:Bacula-users@xxxxxxxxxxxxxxxxxxxxx>>
        https://lists.sourceforge.net/lists/listinfo/bacula-users





------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140_______________________________________________
Bacula-users mailing list
Bacula-users@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-users



Privacy