4 \section*{Catalog Maintenance}
5 \label{_ChapterStart12}
6 \index[general]{Maintenance!Catalog }
7 \index[general]{Catalog Maintenance }
8 \addcontentsline{toc}{section}{Catalog Maintenance}
10 Without proper setup and maintenance, your Catalog may continue to grow
11 indefinitely as you run Jobs and backup Files. How fast the size of your
12 Catalog grows depends on the number of Jobs you run and how many files they
13 backup. By deleting records within the database, you can make space available
14 for the new records that will be added during the next Job. By constantly
15 deleting old expired records (dates older than the Retention period), your
16 database size will remain constant.
18 If you started with the default configuration files, they already contain
19 reasonable defaults for a small number of machines (less that 5), so if you
20 fall into that case, catalog maintenance will not be urgent if you have a few
21 hundred megabytes of disk space free. Whatever the case may be, some knowledge
22 of retention periods will be useful.
25 \subsection*{Setting Retention Periods}
26 \index[general]{Setting Retention Periods }
27 \index[general]{Periods!Setting Retention }
28 \addcontentsline{toc}{subsection}{Setting Retention Periods}
30 {\bf Bacula} uses three Retention periods: the {\bf File Retention} period,
31 the {\bf Job Retention} period, and the {\bf Volume Retention} period. Of
32 these three, the File Retention period is by far the most important in
33 determining how large your database will become.
35 The {\bf File Retention} and the {\bf Job Retention} are specified in each
36 Client resource as is shown below. The {\bf Volume Retention} period is
37 specified in the Pool resource, and the details are given in the next chapter
42 \item [File Retention = \lt{}time-period-specification\gt{}]
43 \index[dir]{File Retention }
44 The File Retention record defines the length of time that Bacula will keep
45 File records in the Catalog database. When this time period expires, and if
46 {\bf AutoPrune} is set to {\bf yes}, Bacula will prune (remove) File records
47 that are older than the specified File Retention period. The pruning will
48 occur at the end of a backup Job for the given Client. Note that the Client
49 database record contains a copy of the File and Job retention periods, but
50 Bacula uses the current values found in the Director's Client resource to do
53 Retention periods are specified in seconds, but as a convenience, there are a
54 number of modifiers that permit easy specification in terms of minutes,
55 hours, days, weeks, months, quarters, or years on the record. See the
56 \ilink{ Configuration chapter}{Time} of this manual for
57 additional details of modifier specification.
59 The default is 60 days.
61 \item [Job Retention = \lt{}time-period-specification\gt{}]
62 \index[dir]{Job Retention }
63 The Job Retention record defines the length of time that {\bf Bacula} will
64 keep Job records in the Catalog database. When this time period expires, and
65 if {\bf AutoPrune} is set to {\bf yes} Bacula will prune (remove) Job records
66 that are older than the specified File Retention period. Note, if a Job
67 record is selected for pruning, all associated File and JobMedia records
68 will also be pruned regardless of the File Retention period set. As a
69 consequence, you normally will set the File retention period to be less than
70 the Job retention period.
72 The retention period is specified in seconds, but as a convenience, there are
73 a number of modifiers that permit easy specification in terms of minutes,
74 hours, days, weeks, months, quarters, or years. See the
75 \ilink{ Configuration chapter}{Time} of this manual for
76 additional details of modifier specification.
78 The default is 180 days.
80 \item [AutoPrune = \lt{}yes|no\gt{}]
81 \index[dir]{AutoPrune }
82 If AutoPrune is set to {\bf yes} (default), Bacula will automatically apply
83 the File retention period and the Job retention period for the Client at the
86 If you turn this off by setting it to {\bf no}, your Catalog will grow each
90 \label{CompactingMySQL}
92 \subsection*{Compacting Your MySQL Database}
93 \index[general]{Database!Compacting Your MySQL }
94 \index[general]{Compacting Your MySQL Database }
95 \addcontentsline{toc}{subsection}{Compacting Your MySQL Database}
97 Over time, as noted above, your database will tend to grow. I've noticed that
98 even though Bacula regularly prunes files, {\bf MySQL} does not effectively
99 use the space, and instead continues growing. To avoid this, from time to
100 time, you must compact your database. Normally, large commercial database such
101 as Oracle have commands that will compact a database to reclaim wasted file
102 space. MySQL has the {\bf OPTIMIZE TABLE} command that you can use, and SQLite
103 version 2.8.4 and greater has the {\bf VACUUM} command. We leave it to you to
104 explore the utility of the {\bf OPTIMIZE TABLE} command in MySQL.
106 All database programs have some means of writing the database out in ASCII
107 format and then reloading it. Doing so will re-create the database from
108 scratch producing a compacted result, so below, we show you how you can do
109 this for both MySQL and SQLite.
111 For a {\bf MySQL} database, you could write the Bacula database as an ASCII
112 file (bacula.sql) then reload it by doing the following:
116 mysqldump -f --opt bacula > bacula.sql
117 mysql bacula < bacula.sql
122 Depending on the size of your database, this will take more or less time and a
123 fair amount of disk space. For example, if I cd to the location of the MySQL
124 Bacula database (typically /opt/mysql/var or something similar) and enter:
132 I get {\bf 620,644} which means there are that many blocks containing 1024
133 bytes each or approximately 635 MB of data. After doing the {\bf msqldump}, I
134 had a bacula.sql file that had {\bf 174,356} blocks, and after doing the {\bf
135 mysql} command to recreate the database, I ended up with a total of {\bf
136 210,464} blocks rather than the original {\bf 629,644}. In other words, the
137 compressed version of the database took approximately one third of the space
138 of the database that had been in use for about a year.
140 As a consequence, I suggest you monitor the size of your database and from
141 time to time (once every 6 months or year), compress it.
142 \label{RepairingMySQL}
144 \subsection*{Repairing Your MySQL Database}
145 \index[general]{Database!Repairing Your MySQL }
146 \index[general]{Repairing Your MySQL Database }
147 \addcontentsline{toc}{subsection}{Repairing Your MySQL Database}
149 If you find that you are getting errors writing to your MySQL database, or
150 Bacula hangs each time it tries to access the database, you should consider
151 running MySQL's database check and repair routines. The program you need to
152 run depends on the type of database indexing you are using. If you are using
153 the default, you will probably want to use {\bf myisamchk}. For more details
154 on how to do this, please consult the MySQL document at:
156 http://www.mysql.com/doc/en/Repair.html}
157 {http://www.mysql.com/doc/en/Repair.html}.
159 If the errors you are getting are simply SQL warnings, then you might try
160 running dbcheck before (or possibly after) using the MySQL database repair
161 program. It can clean up many of the orphanned record problems, and certain
162 other inconsistencies in the Bacula database.
165 \subsection*{Repairing Your PostgreSQL Database}
166 \index[general]{Database!Repairing Your PostgreSQL }
167 \index[general]{Repairing Your PostgreSQL Database }
168 \addcontentsline{toc}{subsection}{Repairing Your PostgreSQL Database}
170 The same considerations apply that are indicated above for MySQL. That is,
171 consult the PostgreSQL documents for how to repair the database, and also
172 consider using Bacula's dbcheck program if the conditions are reasonable for
174 \label{CompactingPostgres}
176 \subsection*{Compacting Your PostgreSQL Database}
177 \index[general]{Database!Compacting Your PostgreSQL }
178 \index[general]{Compacting Your PostgreSQL Database }
179 \addcontentsline{toc}{subsection}{Compacting Your PostgreSQL Database}
181 Over time, as noted above, your database will tend to grow. I've noticed that
182 even though Bacula regularly prunes files, PostgreSQL has a {\bf VACUUM}
183 command that will compact your database for you. Alternatively you may want to
184 use the {\bf vacuumdb} command, which can be run from a cron job.
186 All database programs have some means of writing the database out in ASCII
187 format and then reloading it. Doing so will re-create the database from
188 scratch producing a compacted result, so below, we show you how you can do
191 For a {\bf PostgreSQL} database, you could write the Bacula database as an
192 ASCII file (bacula.sql) then reload it by doing the following:
196 pg_dump bacula > bacula.sql
197 cat bacula.sql | psql bacula
202 Depending on the size of your database, this will take more or less time and a
203 fair amount of disk space. For example, you can {\bf cd} to the location of
204 the Bacula database (typically /usr/local/pgsql/data or possible
205 /var/lib/pgsql/data) and check the size.
207 \subsection*{Compacting Your SQLite Database}
208 \index[general]{Compacting Your SQLite Database }
209 \index[general]{Database!Compacting Your SQLite }
210 \addcontentsline{toc}{subsection}{Compacting Your SQLite Database}
212 First please read the previous section that explains why it is necessary to
213 compress a database. SQLite version 2.8.4 and greater have the {\bf Vacuum}
214 command for compacting the database.
218 cd {\bf working-directory}
219 echo 'vacuum' | sqlite bacula.db
223 As an alternative, you can use the following commands, adapted to your system:
228 cd {\bf working-directory}
229 echo '.dump' | sqlite bacula.db > bacula.sql
231 sqlite bacula.db < bacula.sql
236 Where {\bf working-directory} is the directory that you specified in the
237 Director's configuration file. Note, in the case of SQLite, it is necessary to
238 completely delete (rm) the old database before creating a new compressed
241 \subsection*{Migrating from SQLite to MySQL}
242 \index[general]{MySQL!Migrating from SQLite to }
243 \index[general]{Migrating from SQLite to MySQL }
244 \addcontentsline{toc}{subsection}{Migrating from SQLite to MySQL}
246 You may begin using Bacula with SQLite then later find that you want to switch
247 to MySQL for any of a number of reasons: SQLite tends to use more disk than
248 MySQL, SQLite apparently does not handle database sizes greater than 2GBytes,
249 ... Several users have done so by first producing an ASCII ``dump'' of the
250 SQLite database, then creating the MySQL tables with the {\bf
251 create\_mysql\_tables} script that comes with Bacula, and finally feeding the
252 SQLite dump into MySQL using the {\bf -f} command line option to continue past
253 the errors that are generated by the DDL statements that SQLite's dump
254 creates. Of course, you could edit the dump and remove the offending
255 statements. Otherwise, MySQL accepts the SQL produced by SQLite.
256 \label{BackingUpBacula}
258 \subsection*{Backing Up Your Bacula Database}
259 \index[general]{Backing Up Your Bacula Database }
260 \index[general]{Database!Backing Up Your Bacula }
261 \addcontentsline{toc}{subsection}{Backing Up Your Bacula Database}
263 If ever the machine on which you Bacula database crashes, and you need to
264 restore from backup tapes, one of your first priorities will probably be to
265 recover the database. Although Bacula will happily backup your catalog
266 database if it is specified in the FileSet, this is not a very good way to do
267 it because the database will be saved while Bacula is modifying it. Thus the
268 database may be in and instable state. Worse yet, you will backup the database
269 before all the Bacula updates have been applied.
271 To resolve these problems, you need backup the database after all the backup
272 jobs have been run. In addition, you will want to make a copy while Bacula is
273 not modifying it. To do so, you can use two scripts provided in the release
274 {\bf make\_catalog\_backup} and {\bf delete\_catalog\_backup}. These files
275 will be automatically generated along with all the other Bacula scripts. The
276 first script will make an ASCII copy of your Bacula database into {\bf
277 bacula.sql} in the working directory you specified on your configuration, and
278 the second will delete the {\bf bacula.sql} file.
280 The basic sequence of events to make this work correctly is as follows:
283 \item Run all your nightly backups
284 \item After running your nightly backups, run a Catalog backup Job
285 \item The Catalog backup job must be scheduled after your last nightly backup
287 \item You use {\bf RunBeforeJob} to create the ASCII backup file and {\bf
288 RunAfterJob} to clean up
291 Assuming that you start all your nightly backup jobs at 1:05 am (and that they
292 run one after another), you can do the catalog backup with the following
293 additional Director configuration statements:
297 # Backup the catalog database (after the nightly save)
299 Name = "BackupCatalog"
303 Schedule = "WeeklyCycleAfterBackup"
307 RunBeforeJob = "/home/kern/bacula/bin/make_catalog_backup"
308 RunAfterJob = "/home/kern/bacula/bin/delete_catalog_backup"
310 # This schedule does the catalog. It starts after the WeeklyCycle
312 Name = "WeeklyCycleAfterBackup
313 Run = Full sun-sat at 1:10
315 # This is the backup of the catalog
318 Include = signature=MD5 {
319 @working_directory@/bacula.sql
325 \label{BackingUPOtherDBs}
327 \subsection*{Backing Up Third Party Databases}
328 \index[general]{Backing Up Third Party Databases }
329 \index[general]{Databases!Backing Up Third Party }
330 \addcontentsline{toc}{subsection}{Backing Up Third Party Databases}
332 If you are running a database in production mode on your machine, Bacula will
333 happily backup the files, but if the database is in use while Bacula is
334 reading it, you may back it up in an unstable state.
336 The best solution is to shutdown your database before backing it up, or use
337 some tool specific to your database to make a valid live copy perhaps by
338 dumping the database in ASCII format. I am not a database expert, so I cannot
339 provide you advice on how to do this, but if you are unsure about how to
340 backup your database, you might try visiting the Backup Central site, which
341 has been renamed Storage Mountain (www.backupcentral.com). In particular,
343 \elink{ Free Backup and Recovery
344 Software}{http://www.backupcentral.com/toc-free-backup-software.html} page has
345 links to scripts that show you how to shutdown and backup most major
349 \subsection*{Database Size}
350 \index[general]{Size!Database }
351 \index[general]{Database Size }
352 \addcontentsline{toc}{subsection}{Database Size}
354 As mentioned above, if you do not do automatic pruning, your Catalog will grow
355 each time you run a Job. Normally, you should decide how long you want File
356 records to be maintained in the Catalog and set the {\bf File Retention}
357 period to that time. Then you can either wait and see how big your Catalog
358 gets or make a calculation assuming approximately 154 bytes for each File
359 saved and knowing the number of Files that are saved during each backup and
360 the number of Clients you backup.
362 For example, suppose you do a backup of two systems, each with 100,000 files.
363 Suppose further that you do a Full backup weekly and an Incremental every day,
364 and that the Incremental backup typically saves 4,000 files. The size of your
365 database after a month can roughly be calculated as:
369 Size = 154 * No. Systems * (100,000 * 4 + 10,000 * 26)
373 where we have assumed 4 weeks in a month and 26 incremental backups per month.
374 This would give the following:
378 Size = 154 * 2 * (100,000 * 4 + 10,000 * 26)
380 Size = 308 * (400,000 + 260,000)
382 Size = 203,280,000 bytes
386 So for the above two systems, we should expect to have a database size of
387 approximately 200 Megabytes. Of course, this will vary according to how many
388 files are actually backed up.
390 Below are some statistics for a MySQL database containing Job records for five
391 Clients beginning September 2001 through May 2002 (8.5 months) and File
392 records for the last 80 days. (Older File records have been pruned). For these
393 systems, only the user files and system files that change are backed up. The
394 core part of the system is assumed to be easily reloaded from the RedHat rpms.
397 In the list below, the files (corresponding to Bacula Tables) with the
398 extension .MYD contain the data records whereas files with the extension .MYI
401 You will note that the File records (containing the file attributes) make up
402 the large bulk of the number of records as well as the space used (459 Mega
403 Bytes including the indexes). As a consequence, the most important Retention
404 period will be the {\bf File Retention} period. A quick calculation shows that
405 for each File that is saved, the database grows by approximately 150 bytes.
411 ============ ========= ===========
414 344,394,684 3,080,191 File.MYD
416 2,590,316 106,902 Filename.MYD
417 3,026,944 Filename.MYI
420 49,062 1,326 JobMedia.MYD
422 141,752 1,378 Job.MYD
426 1,299,512 22,233 Path.MYD
435 This database has a total size of approximately 450 Megabytes.
437 If we were using SQLite, the determination of the total database size would be
438 much easier since it is a single file, but we would have less insight to the
439 size of the individual tables as we have in this case.
441 Note, SQLite databases may be as much as 50\% larger than MySQL databases due
442 to the fact that all data is stored as ASCII strings. That is even binary
443 integers are stored as ASCII strings, and this seems to increase the space