Re: [Bacula-users] Copy tape job for offsite

Josip Deanovic on Friday 2015-07-31 21:58:15  wrote:
> Here you are:
> 
> Selection Type = SQLQuery
> Selection Pattern = "SELECT DISTINCT
> JobId,StartTime,Type,Level,Name,PriorJobId FROM Job WHERE Name =
> 'jobname' AND JobBytes > 0 AND ((Level='F' AND JobStatus IN ('T', 'W')
> AND StartTime >= (SELECT DISTINCT StartTime FROM Job WHERE Name =
> 'jobname' AND Level='F' AND JobStatus IN ('T', 'W') ORDER BY StartTime
> DESC LIMIT 1)) OR (Level='D' AND JobStatus IN ('T', 'W') AND StartTime
> >= (SELECT DISTINCT StartTime FROM Job WHERE Name = 'jobname' AND
> Level='F' AND JobStatus IN ('T', 'W') ORDER BY StartTime DESC LIMIT 1))
> OR (Level='I' AND JobStatus IN ('T', 'W') AND StartTime >= (SELECT
> DISTINCT StartTime FROM Job WHERE Name = 'jobname' AND Level='D' AND
> JobStatus IN ('T', 'W') ORDER BY StartTime DESC LIMIT 1) OR StartTime
> >= (SELECT DISTINCT StartTime FROM Job WHERE Name = 'jobname' AND
> Level='F' AND JobStatus IN ('T', 'W') ORDER BY StartTime DESC LIMIT
> 1))) AND NOT JobId = ANY (select PriorJobId FROM Job WHERE Name =
> 'jobname' AND PriorJobId <> 0) AND Type<>'C' ORDER BY JobId;"
> 
> 
> I am using it for more than a year and it selects only the latest full
> job along with its differential and all the incremental jobs that
> haven't been copied yet.
> If someone finds a way to improve it, it would be nice to hear about it.
> 
> The string "jobname" should be replaced with the actual job name. The
> string appears six times in the query.
> 
> I recommend tests directly in the database before using the query in
> production.
> I am using it with MySQL database.

Recently I have noticed that if storage loses its connection during
the copy process it would lead to a problem which requires the query
above to be modified to get around this.

When a job which is being copied fails it will have its JobStatus
correctly set (depending on whether it has failed or it has been
canceled) as well as its PriorJobId which will show the JobId of the
original job.

Furthermore until a copy of the original job is successfully finished
it will have its Type set to "B" instead of "C" which defeats the logic
of the above query.

The modified query below should solve all of this:

Selection Type = SQLQuery
Selection Pattern = "SELECT DISTINCT 
JobId,StartTime,Type,Level,JobStatus,Name,PriorJobId FROM Job WHERE Name = 
'jobname' AND JobBytes > 0 AND ((Level='F' AND JobStatus IN ('T', 'W') AND 
StartTime >= (SELECT DISTINCT StartTime FROM Job WHERE Name = 'jobname' AND 
Level='F' AND JobStatus IN ('T', 'W') ORDER BY StartTime DESC LIMIT 1)) OR 
(Level='D' AND JobStatus IN ('T', 'W') AND StartTime >= (SELECT DISTINCT 
StartTime FROM Job WHERE Name = 'jobname' AND Level='F' AND JobStatus IN ('T', 
'W') ORDER BY StartTime DESC LIMIT 1)) OR (Level='I' AND JobStatus IN ('T', 
'W') AND StartTime >= (SELECT DISTINCT StartTime FROM Job WHERE Name = 
'jobname' AND Level='D' AND JobStatus IN ('T', 'W') ORDER BY StartTime DESC 
LIMIT 1) OR StartTime >= (SELECT DISTINCT StartTime FROM Job WHERE Name = 
'jobname' AND Level='F' AND JobStatus IN ('T', 'W') ORDER BY StartTime DESC 
LIMIT 1))) AND Type = 'B' AND PriorJobId = 0 AND NOT JobId = ANY (select 
PriorJobId FROM Job WHERE Name = 'jobname' AND PriorJobId <> 0 AND JobStatus = 
'T') ORDER BY JobId;"

The string "jobname" should be replaced with the actual job name. The
string appears six times in the query.

It should be tested directly in the database before using the query in
production.
I am using it with MySQL database and I haven't tested it with PostgreSQL
but I think it should work without problems.


-- 
Josip Deanovic

------------------------------------------------------------------------------
Monitor Your Dynamic Infrastructure at Any Scale With Datadog!
Get real-time metrics from all of your servers, apps and tools
in one place.
SourceForge users - Click here to start your Free Trial of Datadog now!
http://pubads.g.doubleclick.net/gampad/clk?id=241902991&iu=/4140
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Copy tape job for offsite - best practices
