Bacula: copying jobs from one bacula-sd to another bacula-sd on FreeBSD

Leave a Comment / Bacula, FreeBSD, Open Source / By

I’m moving some backups from one bacula-sd to another bacula-sd. If you don’t know what that means, this is not the post for you. You need more Bacula knowledge.

I’m retiring a host and moving some of the backups on that host to another host.

This post follows on from Bacula – copying the latest jobs over from one SD to another, now that the required space is installed and configured.

In this post:

  • FreeBSD 13.2-RELEASE-p2
  • Bacula 9.6.7 (yes, I am behind on my upgrades here)
  • bacula-sd-01 – the SD source of the backups to copy
  • knew – the server upon which bacula-sd-01 runs in a FreeBSD jail
  • bacula-sd-04 – the SD to which the backups are going to be copied
  • r730-03 – the server on which the bacula-sd-04 jail is hosted
  • FullFile – the pool from which the backups will be copied – this pool resides within bacula-sd-01
  • FullFile-04 – the pool into which the backups will be copied. This is hosted on bacula-sd-04

Updated SQL

I find the Selection Type and Selection directives incredibly usefule. They allow different criteria to be used:

  1. a single previous Job
  2. a Volume
  3. a Client
  4. a regular expression matching a Job, Volume, or Client name
  5. the time a Job has been on a Volume
  6. high and low water marks (usage or occupation) of a Pool
  7. Volume size

That’s so flexible. I’m using SQLQuery:

“The SQLQuery selection type, used the Selection Pattern as an SQL query to obtain the JobIds to be migrated. The Selection Pattern must be a valid SELECT SQL statement for your SQL engine, and it must return the JobId as the first field of the SELECT.”

This is my Job definition:

Job {
  Name     = "CopyToSD04"
  Type     = Copy
  Level    = Full
  Pool     = FullFile
  NextPool = FullFile-04

  FileSet   = "EmptyCopyToTape"

  Client      = crey-fd 
  Schedule    = "Never"
  Storage     = bacula-sd-04-FullFile
  Messages    = Standard

  Spool Attributes = yes

  Maximum Concurrent Jobs = 30

  Selection Type = SQL Query
  Selection Pattern = "
 SELECT J.JobId, J.SchedTime, J.name, J.jobbytes, pg_size_pretty(J.JobBytes)
   FROM Job J, Pool P
   WHERE P.Name IN ('FullFile', 'MonthlyBackups')
     AND P.PoolId = J.PoolId
     AND J.Type = 'B' 
     AND J.JobStatus IN ('T','W')
     AND J.jobBytes > 0
     AND J.endtime > current_timestamp - interval '14 months'
     AND J.schedtime::date in ('2022-09-04', '2022-10-02', '2022-11-06', '2022-12-04', '2023-01-01', '2023-02-05', 
         '2023-03-05', '2023-04-02', '2023-05-07', '2023-06-04', '2023-07-02', '2023-08-06', '2023-09-03')
     AND J.JobId NOT IN
         (SELECT J.PriorJobId 
            FROM Job J
           WHERE J.Type IN ('B','C')
             AND J.JobStatus IN ('T','W')
             AND J.PriorJobId != 0
             AND J.PoolId IN (SELECT P.poolid FROM pool P WHERE P.name = 'FullFile-04'))
ORDER BY J.JobBytes desc, lower(J.name), schedtime

limit 40
"
}

Things to note: I’ve added a bunch of dates, because I’m only interested in the Full jobs done on the first Sunday of the month.

In the select statement, I have the jobid first. This is required and is specified explicitly in the documentation. The other columns are ignored. I include them for my own convenience. I decided to do the bigger jobs first, just so when it gets to the smaller jobs, I can adjust the limit clause upwards and queue more jobs at once.

Notice that duplicate copies are avoided with the AND J.JobId NOT IN clause.

Things are going well.

How many jobs left?

I use the SQL query from the Job to find out how many more jobs are left to copy over:

bacula=#  SELECT J.JobId, J.SchedTime, J.name, J.jobbytes, pg_size_pretty(J.JobBytes)
   FROM Job J, Pool P
   WHERE P.Name IN ('FullFile', 'MonthlyBackups')
     AND P.PoolId = J.PoolId
     AND J.Type = 'B' 
     AND J.JobStatus IN ('T','W')
     AND J.jobBytes > 0
     AND J.endtime > current_timestamp - interval '14 months'
     AND J.schedtime::date in ('2022-09-04', '2022-10-02', '2022-11-06', '2022-12-04', '2023-01-01', '2023-02-05', 
         '2023-03-05', '2023-04-02', '2023-05-07', '2023-06-04', '2023-07-02', '2023-08-06', '2023-09-03')
     AND J.JobId NOT IN
         (SELECT J.PriorJobId 
            FROM Job J
           WHERE J.Type IN ('B','C')
             AND J.JobStatus IN ('T','W')
             AND J.PriorJobId != 0
             AND J.PoolId IN (SELECT P.poolid FROM pool P WHERE P.name = 'FullFile-04'))
ORDER BY J.JobBytes desc, lower(J.name), schedtime
;
 jobid  |      schedtime      |                     name                     |   jobbytes   | pg_size_pretty 
--------+---------------------+----------------------------------------------+--------------+----------------
 344209 | 2022-09-04 03:05:03 | zuul jail snapshots                          | 166026165330 | 155 GB
 344185 | 2022-09-04 03:05:00 | BackupCatalog                                | 142943800427 | 133 GB
 344180 | 2022-09-04 03:04:00 | knew jail snapshots                          |  86400750482 | 80 GB
 344197 | 2022-09-04 03:05:01 | slocum dev data snapshots                    |  69504879474 | 65 GB
 344181 | 2022-09-04 03:04:00 | slocum jail snapshots                        |  39082891398 | 36 GB
 344196 | 2022-09-04 03:05:01 | slocum home                                  |  34592623302 | 32 GB
 344194 | 2022-09-04 03:05:01 | r720-01 jail snapshots                       |  18368105457 | 17 GB
 344207 | 2022-09-04 03:05:02 | x8dtu jail snapshots                         |  16345637138 | 15 GB
 344182 | 2022-09-04 03:04:00 | repo-svn-snapshots                           |   8004432236 | 7634 MB
 344200 | 2022-09-04 03:05:01 | svn everything                               |   7181328623 | 6849 MB
 344203 | 2022-09-04 03:05:02 | tallboy jail snapshots                       |   6382494719 | 6087 MB
 344204 | 2022-09-04 03:05:02 | tallboy Papers Jail                          |   4277274677 | 4079 MB
 344192 | 2022-09-04 03:05:01 | mydev home dir                               |   1719139432 | 1639 MB
 344198 | 2022-09-04 03:05:01 | unifi                                        |   1268986023 | 1210 MB
 344188 | 2022-09-04 03:05:00 | gelt                                         |    759420173 | 724 MB
 344189 | 2022-09-04 03:05:00 | knew basic                                   |    726947688 | 693 MB
 344195 | 2022-09-04 03:05:01 | slocum basic                                 |    526462315 | 502 MB
 344187 | 2022-09-04 03:05:00 | gelt basic                                   |    493498352 | 471 MB
 344208 | 2022-09-04 03:05:03 | zuul basic                                   |    488862000 | 466 MB
 346501 | 2022-11-06 03:04:00 | repo-git-snapshots                           |    415786548 | 397 MB
 346519 | 2022-11-06 03:05:04 | tallboy basic                                |    411254557 | 392 MB
 344201 | 2022-09-04 03:05:02 | tallboy basic                                |    406886243 | 388 MB
 345160 | 2022-10-02 03:05:03 | tallboy basic                                |    405601196 | 387 MB
 353152 | 2023-04-02 03:05:00 | tallboy basic                                |    382205735 | 364 MB
 348948 | 2022-12-04 03:05:02 | tallboy basic                                |    376990676 | 360 MB
 351178 | 2023-02-05 03:05:03 | tallboy basic                                |    376489477 | 359 MB
 349955 | 2023-01-01 03:05:04 | tallboy basic                                |    375742993 | 358 MB
 352246 | 2023-03-05 03:05:00 | tallboy basic                                |    375460487 | 358 MB
 346511 | 2022-11-06 03:05:02 | r720-01 basic                                |    374909544 | 358 MB
 345152 | 2022-10-02 03:05:01 | r720-01 basic                                |    368592347 | 352 MB
 344193 | 2022-09-04 03:05:01 | r720-01 basic                                |    368089985 | 351 MB
 348940 | 2022-12-04 03:05:01 | r720-01 basic                                |    339847050 | 324 MB
 351170 | 2023-02-05 03:05:01 | r720-01 basic                                |    339367021 | 324 MB
 349947 | 2023-01-01 03:05:02 | r720-01 basic                                |    339101238 | 323 MB
 355140 | 2023-06-04 03:05:00 | fileserver basic                             |    314492565 | 300 MB
 352229 | 2023-03-05 03:05:00 | fileserver basic                             |    308281509 | 294 MB
 351163 | 2023-02-05 03:05:00 | fileserver basic                             |    304235761 | 290 MB
 357119 | 2023-08-06 03:05:00 | fileserver basic                             |    302943799 | 289 MB
 353140 | 2023-04-02 03:05:00 | fileserver basic                             |    302700627 | 289 MB
 348933 | 2022-12-04 03:05:00 | fileserver basic                             |    301934671 | 288 MB
 357136 | 2023-08-06 03:05:01 | x8dtu basic                                  |    301593185 | 288 MB
 349940 | 2023-01-01 03:05:00 | fileserver basic                             |    301006245 | 287 MB
 344186 | 2022-09-04 03:05:00 | fileserver basic                             |    300733808 | 287 MB
 345145 | 2022-10-02 03:05:00 | fileserver basic                             |    300599865 | 287 MB
 356041 | 2023-07-02 03:05:00 | fileserver basic                             |    300287994 | 286 MB
 354225 | 2023-05-07 03:05:00 | fileserver basic                             |    299684165 | 286 MB
 346504 | 2022-11-06 03:05:00 | fileserver basic                             |    299421169 | 286 MB
 348930 | 2022-12-04 03:04:00 | repo-git-snapshots                           |    293421222 | 280 MB
 344206 | 2022-09-04 03:05:02 | x8dtu basic                                  |    292987236 | 279 MB
 346524 | 2022-11-06 03:05:05 | x8dtu basic                                  |    292719581 | 279 MB
 345165 | 2022-10-02 03:05:03 | x8dtu basic                                  |    291735557 | 278 MB
 355157 | 2023-06-04 03:05:00 | x8dtu basic                                  |    269481743 | 257 MB
 354242 | 2023-05-07 03:05:00 | x8dtu basic                                  |    269319381 | 257 MB
 352251 | 2023-03-05 03:05:00 | x8dtu basic                                  |    268682096 | 256 MB
 353157 | 2023-04-02 03:05:00 | x8dtu basic                                  |    268487457 | 256 MB
 356048 | 2023-07-02 03:05:00 | r730-01 basic                                |    263617368 | 251 MB
 357126 | 2023-08-06 03:05:01 | r730-01 basic                                |    263610998 | 251 MB
 348953 | 2022-12-04 03:05:03 | x8dtu basic                                  |    263124199 | 251 MB
 353138 | 2023-04-02 03:04:01 | repo-git-snapshots                           |    263062721 | 251 MB
 351160 | 2023-02-05 03:04:00 | repo-git-snapshots                           |    263048028 | 251 MB
 349937 | 2023-01-01 03:04:00 | repo-git-snapshots                           |    263038952 | 251 MB
 345142 | 2022-10-02 03:04:01 | repo-git-snapshots                           |    262619890 | 250 MB
 344183 | 2022-09-04 03:04:00 | repo-git-snapshots                           |    262617851 | 250 MB
 351183 | 2023-02-05 03:05:03 | x8dtu basic                                  |    262411292 | 250 MB
 349960 | 2023-01-01 03:05:04 | x8dtu basic                                  |    261809240 | 250 MB
 354232 | 2023-05-07 03:05:00 | r730-01 basic                                |    261217394 | 249 MB
 355147 | 2023-06-04 03:05:00 | r730-01 basic                                |    261154172 | 249 MB
 353147 | 2023-04-02 03:05:00 | r730-01 basic                                |    260258340 | 248 MB
 352238 | 2023-03-05 03:05:00 | r730-01 basic                                |    256828666 | 245 MB
 357117 | 2023-08-06 03:04:00 | repo-git-snapshots                           |    203508338 | 194 MB
 356039 | 2023-07-02 03:04:00 | repo-git-snapshots                           |    203479382 | 194 MB
 355138 | 2023-06-04 03:04:00 | repo-git-snapshots                           |    203478370 | 194 MB
 354223 | 2023-05-07 03:04:00 | repo-git-snapshots                           |    203473882 | 194 MB
 351176 | 2023-02-05 03:05:02 | svn basic                                    |    134874441 | 129 MB
 349953 | 2023-01-01 03:05:03 | svn basic                                    |    133717529 | 128 MB
 348946 | 2022-12-04 03:05:02 | svn basic                                    |    132755557 | 127 MB
 346517 | 2022-11-06 03:05:04 | svn basic                                    |    131920297 | 126 MB
 345158 | 2022-10-02 03:05:02 | svn basic                                    |    130781908 | 125 MB
 357129 | 2023-08-06 03:05:01 | svn basic                                    |    130720665 | 125 MB
 344199 | 2022-09-04 03:05:01 | svn basic                                    |    129812652 | 124 MB
 356051 | 2023-07-02 03:05:01 | svn basic                                    |    129214954 | 123 MB
 355150 | 2023-06-04 03:05:00 | svn basic                                    |    127793421 | 122 MB
 354235 | 2023-05-07 03:05:00 | svn basic                                    |    126741011 | 121 MB
 353150 | 2023-04-02 03:05:00 | svn basic                                    |    125501741 | 120 MB
 352244 | 2023-03-05 03:05:00 | svn basic                                    |    125363224 | 120 MB
 357132 | 2023-08-06 03:05:01 | tallboy home                                 |     82493261 | 79 MB
 356054 | 2023-07-02 03:05:01 | tallboy home                                 |     82491260 | 79 MB
 355153 | 2023-06-04 03:05:00 | tallboy home                                 |     82486698 | 79 MB
 354238 | 2023-05-07 03:05:00 | tallboy home                                 |     82486548 | 79 MB
 357124 | 2023-08-06 03:05:01 | mydev basic                                  |     20202304 | 19 MB
 356046 | 2023-07-02 03:05:00 | mydev basic                                  |     19548099 | 19 MB
 351168 | 2023-02-05 03:05:00 | mydev basic                                  |     19397437 | 18 MB
 346509 | 2022-11-06 03:05:01 | mydev basic                                  |     19391351 | 18 MB
 345150 | 2022-10-02 03:05:01 | mydev basic                                  |     19363807 | 18 MB
 348938 | 2022-12-04 03:05:01 | mydev basic                                  |     19329842 | 18 MB
 344191 | 2022-09-04 03:05:00 | mydev basic                                  |     19328048 | 18 MB
 349945 | 2023-01-01 03:05:02 | mydev basic                                  |     19314788 | 18 MB
 355145 | 2023-06-04 03:05:00 | mydev basic                                  |     19125773 | 18 MB
 354230 | 2023-05-07 03:05:00 | mydev basic                                  |     19098945 | 18 MB
 352234 | 2023-03-05 03:05:00 | mydev basic                                  |     19088738 | 18 MB
 353145 | 2023-04-02 03:05:00 | mydev basic                                  |     19069736 | 18 MB
 344190 | 2022-09-04 03:05:00 | ansible                                      |     15475593 | 15 MB
 353153 | 2023-04-02 03:05:00 | tallboy home                                 |      2410248 | 2354 kB
 352247 | 2023-03-05 03:05:00 | tallboy home                                 |      2409062 | 2353 kB
 351179 | 2023-02-05 03:05:03 | tallboy home                                 |      2408479 | 2352 kB
 349956 | 2023-01-01 03:05:04 | tallboy home                                 |      2407493 | 2351 kB
 348949 | 2022-12-04 03:05:02 | tallboy home                                 |      2407143 | 2351 kB
 345161 | 2022-10-02 03:05:03 | tallboy home                                 |      2406633 | 2350 kB
 346520 | 2022-11-06 03:05:05 | tallboy home                                 |      2406621 | 2350 kB
 344202 | 2022-09-04 03:05:02 | tallboy home                                 |      2405619 | 2349 kB
 344184 | 2022-09-04 03:05:00 | bast pfsense config.xml                      |       358341 | 350 kB
 344205 | 2022-09-04 03:05:02 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 345164 | 2022-10-02 03:05:03 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 346523 | 2022-11-06 03:05:05 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 348952 | 2022-12-04 03:05:03 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 349959 | 2023-01-01 03:05:04 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 351182 | 2023-02-05 03:05:03 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 352250 | 2023-03-05 03:05:00 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 353156 | 2023-04-02 03:05:00 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 354241 | 2023-05-07 03:05:00 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 355156 | 2023-06-04 03:05:00 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 356057 | 2023-07-02 03:05:01 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
 357135 | 2023-08-06 03:05:01 | tallboy Papers Jail PostgreSQL Configuration |        23909 | 23 kB
(123 rows)

bacula=#

Now it’s just a matter of running:

*run job=CopyToSD04 yes
Using Catalog "MyCatalog"
Job queued. JobId=358358
You have messages.

It is important to note that this job launches other jobs. Here is an excerpt from status dir today:

Running Jobs:
Console connected at 10-Sep-23 20:31
 JobId  Type Level     Files     Bytes  Name              Status
======================================================================
358365  Copy Full          0         0  CopyToSD04        is waiting on max Job jobs
358366  Copy Full          0         0  CopyToSD04        is running
358367  Back Full          0         0  ansible           is running
358368  Copy Full          0         0  CopyToSD04        is running
358369  Back Full          0         0  ansible           is running
358370  Copy Full          0         0  CopyToSD04        is running
358371  Back Full          0         0  ansible           is running
358372  Copy Full          0         0  CopyToSD04        is waiting on max Job jobs
358373  Back Full          0         0  ansible           is waiting execution
358374  Copy Full          0         0  CopyToSD04        is waiting on max Job jobs
358375  Back Full          0         0  ansible           is waiting execution
358376  Copy Full          0         0  CopyToSD04        is waiting on max Job jobs
358377  Back Full          0         0  ansible           is waiting execution
358378  Copy Full          0         0  CopyToSD04        is waiting on max Job jobs
358379  Back Full          0         0  ansible           is waiting execution
358380  Copy Full          0         0  CopyToSD04        is waiting on max Job jobs
358381  Back Full          0         0  ansible           is waiting execution
358382  Copy Full          0         0  CopyToSD04        is waiting on max Job jobs
358383  Back Full          0         0  ansible           is waiting execution
358384  Back Full          0         0  ansible           is waiting execution
====

I’m waiting for all the jobs to finish before issuing that command again. The SQL query is run when you issue the command. I’d get duplicates if I didn’t wait.

Endless copying

One issue to keep in mind: retention period. If you copy a 13-month old job into a pool with a 12-month retention, it will quickly disappear. The next time any recycling is done, it’s gone.

You can see how I hit that problem above: J.endtime > current_timestamp – interval ’14 months’.

However, the FullFile-04 pool has a retention period of 1 year. I’d run the copy job, it would copy a big job. I’d run the job again, that job was still there to be copied over, because it no longer existed within the FullFile-04 pool – as specified in the SQL query:

SELECT P.poolid FROM pool P WHERE P.name = ‘FullFile-04’

I hope you avoid this issue. From what I see, I copied this job 8 times.

Edit 2023-09-12

The backup copies have finished. Next step: Turn off that host.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

Leave a Comment

Scroll to Top