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 AT sibbald DOT com> 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 AT sibbald DOT com
<mailto:kern AT sibbald DOT com>> 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 AT sibbald DOT com
<mailto:kern AT sibbald DOT com>
<mailto:kern AT sibbald DOT com <mailto:kern AT sibbald DOT com>>> 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 AT bacula.com DOT br <mailto:heitor AT bacula.com DOT br>
<mailto:heitor AT bacula.com DOT br

<mailto:heitor AT bacula.com DOT br>>> 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 AT lists.sourceforge DOT net
<mailto:Bacula-users AT lists.sourceforge DOT net>
<mailto:Bacula-users AT lists.sourceforge DOT net
<mailto:Bacula-users AT lists.sourceforge DOT net>>
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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users