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:
- a single previous Job
- a Volume
- a Client
- a regular expression matching a Job, Volume, or Client name
- the time a Job has been on a Volume
- high and low water marks (usage or occupation) of a Pool
- 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.
