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 than 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 Since File records in the database account for probably 80 percent of the
54 size of the database, you should carefully determine exactly what File
55 Retention period you need. Once the File records have been removed from
56 the database, you will no longer be able to restore individual files
57 in a Job. However, with Bacula version 1.37 and later, as long as the
58 Job record still exists, you will be able to restore all files in the
61 Retention periods are specified in seconds, but as a convenience, there are
62 a number of modifiers that permit easy specification in terms of minutes,
63 hours, days, weeks, months, quarters, or years on the record. See the
64 \ilink{ Configuration chapter}{Time} of this manual for additional details
65 of modifier specification.
67 The default File retention period is 60 days.
69 \item [Job Retention = \lt{}time-period-specification\gt{}]
70 \index[dir]{Job Retention }
71 The Job Retention record defines the length of time that {\bf Bacula}
72 will keep Job records in the Catalog database. When this time period
73 expires, and if {\bf AutoPrune} is set to {\bf yes} Bacula will prune
74 (remove) Job records that are older than the specified Job Retention
75 period. Note, if a Job record is selected for pruning, all associated File
76 and JobMedia records will also be pruned regardless of the File Retention
77 period set. As a consequence, you normally will set the File retention
78 period to be less than the Job retention period.
80 As mentioned above, once the File records are removed from the database,
81 you will no longer be able to restore individual files from the Job.
82 However, as long as the Job record remains in the database, you will be
83 able to restore all the files backuped for the Job (on version 1.37 and
84 later). As a consequence, it is generally a good idea to retain the Job
85 records much longer than the File records.
87 The retention period is specified in seconds, but as a convenience, there
88 are a number of modifiers that permit easy specification in terms of
89 minutes, hours, days, weeks, months, quarters, or years. See the \ilink{
90 Configuration chapter}{Time} of this manual for additional details of
91 modifier specification.
93 The default Job Retention period is 180 days.
95 \item [AutoPrune = \lt{}yes/no\gt{}]
96 \index[dir]{AutoPrune }
97 If AutoPrune is set to {\bf yes} (default), Bacula will automatically apply
98 the File retention period and the Job retention period for the Client at the
101 If you turn this off by setting it to {\bf no}, your Catalog will grow each
105 \label{CompactingMySQL}
107 \subsection*{Compacting Your MySQL Database}
108 \index[general]{Database!Compacting Your MySQL }
109 \index[general]{Compacting Your MySQL Database }
110 \addcontentsline{toc}{subsection}{Compacting Your MySQL Database}
112 Over time, as noted above, your database will tend to grow. I've noticed that
113 even though Bacula regularly prunes files, {\bf MySQL} does not effectively
114 use the space, and instead continues growing. To avoid this, from time to
115 time, you must compact your database. Normally, large commercial database such
116 as Oracle have commands that will compact a database to reclaim wasted file
117 space. MySQL has the {\bf OPTIMIZE TABLE} command that you can use, and SQLite
118 version 2.8.4 and greater has the {\bf VACUUM} command. We leave it to you to
119 explore the utility of the {\bf OPTIMIZE TABLE} command in MySQL.
121 All database programs have some means of writing the database out in ASCII
122 format and then reloading it. Doing so will re-create the database from
123 scratch producing a compacted result, so below, we show you how you can do
124 this for MySQL, PostgreSQL and SQLite.
126 For a {\bf MySQL} database, you could write the Bacula database as an ASCII
127 file (bacula.sql) then reload it by doing the following:
131 mysqldump -f --opt bacula > bacula.sql
132 mysql bacula < bacula.sql
137 Depending on the size of your database, this will take more or less time and a
138 fair amount of disk space. For example, if I cd to the location of the MySQL
139 Bacula database (typically /opt/mysql/var or something similar) and enter:
147 I get {\bf 620,644} which means there are that many blocks containing 1024
148 bytes each or approximately 635 MB of data. After doing the {\bf msqldump}, I
149 had a bacula.sql file that had {\bf 174,356} blocks, and after doing the {\bf
150 mysql} command to recreate the database, I ended up with a total of {\bf
151 210,464} blocks rather than the original {\bf 629,644}. In other words, the
152 compressed version of the database took approximately one third of the space
153 of the database that had been in use for about a year.
155 As a consequence, I suggest you monitor the size of your database and from
156 time to time (once every 6 months or year), compress it.
157 \label{RepairingMySQL}
159 \subsection*{Repairing Your MySQL Database}
160 \index[general]{Database!Repairing Your MySQL }
161 \index[general]{Repairing Your MySQL Database }
162 \addcontentsline{toc}{subsection}{Repairing Your MySQL Database}
164 If you find that you are getting errors writing to your MySQL database, or
165 Bacula hangs each time it tries to access the database, you should consider
166 running MySQL's database check and repair routines. The program you need to
167 run depends on the type of database indexing you are using. If you are using
168 the default, you will probably want to use {\bf myisamchk}. For more details
169 on how to do this, please consult the MySQL document at:
171 http://www.mysql.com/doc/en/Repair.html}
172 {http://www.mysql.com/doc/en/Repair.html}.
174 If the errors you are getting are simply SQL warnings, then you might try
175 running dbcheck before (or possibly after) using the MySQL database repair
176 program. It can clean up many of the orphaned record problems, and certain
177 other inconsistencies in the Bacula database.
178 \label{RepairingPSQL}
180 \subsection*{Repairing Your PostgreSQL Database}
181 \index[general]{Database!Repairing Your PostgreSQL }
182 \index[general]{Repairing Your PostgreSQL Database }
183 \addcontentsline{toc}{subsection}{Repairing Your PostgreSQL Database}
185 The same considerations apply that are indicated above for MySQL. That is,
186 consult the PostgreSQL documents for how to repair the database, and also
187 consider using Bacula's dbcheck program if the conditions are reasonable for
189 \label{CompactingPostgres}
191 \subsection*{Performance Issues}
192 \index[general]{Database Performance Issues}
193 \index[general]{Performance!Database}
194 \addcontentsline{toc}{subsection}{Database Performance Issues}
196 There are a considerable number of ways each of the databases can be
197 tuned to improve the performance. Going from an untuned database to one
198 that is properly tuned can make a difference of a factor of 100 or more
199 in the time to insert or search for records.
201 For each of the databases, you may get significant improvements by adding
202 additional indexes. The comments in the Bacula make\_xxx\_tables give some
203 indications as to what indexes may be appropriate. Please see below
204 for specific instructions on checking indexes.
206 For MySQL, what seems to be very important is to use the examine the
207 my.cnf file. You may obtain significant performances by switching to
208 the my-large.cnf or my-huge.cnf files that come with the MySQL source
211 For SQLite3, one significant factor in improving the performance is
212 to ensure that there is a "PRAGMA synchronous = NORMAL;" statement.
213 This reduces the number of times that the database flushes the in memory
214 cache to disk. There are other settings for this PRAGMA that can
215 give even further performance improvements at the risk of a database
216 corruption if your system crashes.
218 For PostgreSQL, you might want to consider turning fsync off. Of course
219 doing so can cause corrupted databases in the even of a machine crash.
220 There are many different ways that you can tune PostgreSQL, the
221 following document discusses a few of them:
223 http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html}
224 {http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html}.
226 There is also a PostgreSQL FAQ question number 3.3 that may
227 answer some of your questions about how to improve performance
228 of the PostgreSQL engine:
230 http://www.postgresql.org/docs/faqs.FAQ.html\#3.3}
231 {http://www.postgresql.org/docs/faqs.FAQ.html\#3.3}.
234 \subsection*{Performance Issues Indexes}
235 \index[general]{Database Performance Issues Indexes}
236 \index[general]{Performance!Database}
237 \addcontentsline{toc}{subsection}{Database Performance Issues Indexes}
238 One of the most important considerations for improving performance on
239 the Bacula database is to ensure that it has all the appropriate indexes.
240 Several users have reported finding that their database did not have
241 all the indexes in the default configuration. In addition, you may
242 find that because of your own usage patterns, you need additional indexes.
244 The most important indexes for performance are the three indexes on the
245 {\bf File} table. The first index is on {\bf FileId} and is automatically
246 made because it is the unique key used to access the table. The other
247 two are the JobId index and the (Filename, PathId) index. If these Indexes
248 are not present, your peformance may suffer a lot.
250 \subsubsection*{PostgreSQL Indexes}
251 On PostgreSQL, you can check to see if you have the proper indexes using
252 the following commands:
257 select * from pg_indexes where tablename='file';
261 If you do not see output that indicates that all three indexes
262 are created, you can create the two additional indexes using:
267 CREATE INDEX file_jobid_idx on file (jobid);
268 CREATE INDEX file_fp_idx on file (filenameid, pathid);
272 \subsubsection*{MySQL Indexes}
273 On MySQL, you can check if you have the proper indexes by:
278 show index from File;
282 If the indexes are not present, especially the JobId index, you can
283 create them with the following commands:
288 CREATE INDEX file_jobid_idx on File (JobId);
289 CREATE INDEX file_jpf_idx on File (Job, FilenameId, PathId);
293 \subsubsection*{SQLit Indexes}
294 On SQLite, you can check if you have the proper indexes by:
298 sqlite <path>bacula.db
299 select * from sqlite_master where type='index' and tbl_name='File';
303 If the indexes are not present, especially the JobId index, you can
304 create them with the following commands:
309 CREATE INDEX file_jobid_idx on File (JobId);
310 CREATE INDEX file_jpf_idx on File (Job, FilenameId, PathId);
316 \subsection*{Compacting Your PostgreSQL Database}
317 \index[general]{Database!Compacting Your PostgreSQL }
318 \index[general]{Compacting Your PostgreSQL Database }
319 \addcontentsline{toc}{subsection}{Compacting Your PostgreSQL Database}
321 Over time, as noted above, your database will tend to grow. I've noticed that
322 even though Bacula regularly prunes files, PostgreSQL has a {\bf VACUUM}
323 command that will compact your database for you. Alternatively you may want to
324 use the {\bf vacuumdb} command, which can be run from a cron job.
326 All database programs have some means of writing the database out in ASCII
327 format and then reloading it. Doing so will re-create the database from
328 scratch producing a compacted result, so below, we show you how you can do
331 For a {\bf PostgreSQL} database, you could write the Bacula database as an
332 ASCII file (bacula.sql) then reload it by doing the following:
336 pg_dump bacula > bacula.sql
337 cat bacula.sql | psql bacula
342 Depending on the size of your database, this will take more or less time and a
343 fair amount of disk space. For example, you can {\bf cd} to the location of
344 the Bacula database (typically /usr/local/pgsql/data or possible
345 /var/lib/pgsql/data) and check the size.
347 \subsection*{Compacting Your SQLite Database}
348 \index[general]{Compacting Your SQLite Database }
349 \index[general]{Database!Compacting Your SQLite }
350 \addcontentsline{toc}{subsection}{Compacting Your SQLite Database}
352 First please read the previous section that explains why it is necessary to
353 compress a database. SQLite version 2.8.4 and greater have the {\bf Vacuum}
354 command for compacting the database.
358 cd {\bf working-directory}
359 echo 'vacuum;' | sqlite bacula.db
363 As an alternative, you can use the following commands, adapted to your system:
368 cd {\bf working-directory}
369 echo '.dump' | sqlite bacula.db > bacula.sql
371 sqlite bacula.db < bacula.sql
376 Where {\bf working-directory} is the directory that you specified in the
377 Director's configuration file. Note, in the case of SQLite, it is necessary to
378 completely delete (rm) the old database before creating a new compressed
381 \subsection*{Migrating from SQLite to MySQL}
382 \index[general]{MySQL!Migrating from SQLite to }
383 \index[general]{Migrating from SQLite to MySQL }
384 \addcontentsline{toc}{subsection}{Migrating from SQLite to MySQL}
386 You may begin using Bacula with SQLite then later find that you want to switch
387 to MySQL for any of a number of reasons: SQLite tends to use more disk than
388 MySQL; when the database is corrupted it is often more catastrophic than
389 with MySQL or PostgreSQL.
390 Several users have succeeded in converting from SQLite to MySQL by
391 exporting the MySQL data and then processing it with Perl scripts
392 prior to putting it into MySQL. This is, however, not a simple
395 \label{BackingUpBacula}
396 \subsection*{Backing Up Your Bacula Database}
397 \index[general]{Backing Up Your Bacula Database }
398 \index[general]{Database!Backing Up Your Bacula }
399 \addcontentsline{toc}{subsection}{Backing Up Your Bacula Database}
401 If ever the machine on which your Bacula database crashes, and you need to
402 restore from backup tapes, one of your first priorities will probably be to
403 recover the database. Although Bacula will happily backup your catalog
404 database if it is specified in the FileSet, this is not a very good way to do
405 it, because the database will be saved while Bacula is modifying it. Thus the
406 database may be in an instable state. Worse yet, you will backup the database
407 before all the Bacula updates have been applied.
409 To resolve these problems, you need to backup the database after all the backup
410 jobs have been run. In addition, you will want to make a copy while Bacula is
411 not modifying it. To do so, you can use two scripts provided in the release
412 {\bf make\_catalog\_backup} and {\bf delete\_catalog\_backup}. These files
413 will be automatically generated along with all the other Bacula scripts. The
414 first script will make an ASCII copy of your Bacula database into {\bf
415 bacula.sql} in the working directory you specified in your configuration, and
416 the second will delete the {\bf bacula.sql} file.
418 The basic sequence of events to make this work correctly is as follows:
421 \item Run all your nightly backups
422 \item After running your nightly backups, run a Catalog backup Job
423 \item The Catalog backup job must be scheduled after your last nightly backup
425 \item You use {\bf RunBeforeJob} to create the ASCII backup file and {\bf
426 RunAfterJob} to clean up
429 Assuming that you start all your nightly backup jobs at 1:05 am (and that they
430 run one after another), you can do the catalog backup with the following
431 additional Director configuration statements:
435 # Backup the catalog database (after the nightly save)
437 Name = "BackupCatalog"
441 Schedule = "WeeklyCycleAfterBackup"
445 RunBeforeJob = "/home/kern/bacula/bin/make_catalog_backup"
446 RunAfterJob = "/home/kern/bacula/bin/delete_catalog_backup"
447 Write Bootstrap = "/home/kern/bacula/working/BackupCatalog.bsr"
449 # This schedule does the catalog. It starts after the WeeklyCycle
451 Name = "WeeklyCycleAfterBackup
452 Run = Full sun-sat at 1:10
454 # This is the backup of the catalog
461 File = \lt{}working_directory\gt{}/bacula.sql
467 Be sure to write a bootstrap file as in the above example. However, it is preferable
468 to write or copy the bootstrap file to another computer. It will allow
469 you to quickly recover the database backup should that be necessary. If
470 you do not have a bootstrap file, it is still possible to recover your
471 database backup, but it will be more work and take longer.
473 \label{BackingUPOtherDBs}
474 \subsection*{Backing Up Third Party Databases}
475 \index[general]{Backing Up Third Party Databases }
476 \index[general]{Databases!Backing Up Third Party }
477 \addcontentsline{toc}{subsection}{Backing Up Third Party Databases}
479 If you are running a database in production mode on your machine, Bacula will
480 happily backup the files, but if the database is in use while Bacula is
481 reading it, you may back it up in an unstable state.
483 The best solution is to shutdown your database before backing it up, or use
484 some tool specific to your database to make a valid live copy perhaps by
485 dumping the database in ASCII format. I am not a database expert, so I cannot
486 provide you advice on how to do this, but if you are unsure about how to
487 backup your database, you might try visiting the Backup Central site, which
488 has been renamed Storage Mountain (www.backupcentral.com). In particular,
490 \elink{ Free Backup and Recovery
491 Software}{http://www.backupcentral.com/toc-free-backup-software.html} page has
492 links to scripts that show you how to shutdown and backup most major
496 \subsection*{Database Size}
497 \index[general]{Size!Database }
498 \index[general]{Database Size }
499 \addcontentsline{toc}{subsection}{Database Size}
501 As mentioned above, if you do not do automatic pruning, your Catalog will grow
502 each time you run a Job. Normally, you should decide how long you want File
503 records to be maintained in the Catalog and set the {\bf File Retention}
504 period to that time. Then you can either wait and see how big your Catalog
505 gets or make a calculation assuming approximately 154 bytes for each File
506 saved and knowing the number of Files that are saved during each backup and
507 the number of Clients you backup.
509 For example, suppose you do a backup of two systems, each with 100,000 files.
510 Suppose further that you do a Full backup weekly and an Incremental every day,
511 and that the Incremental backup typically saves 4,000 files. The size of your
512 database after a month can roughly be calculated as:
516 Size = 154 * No. Systems * (100,000 * 4 + 10,000 * 26)
520 where we have assumed 4 weeks in a month and 26 incremental backups per month.
521 This would give the following:
525 Size = 154 * 2 * (100,000 * 4 + 10,000 * 26)
527 Size = 308 * (400,000 + 260,000)
529 Size = 203,280,000 bytes
533 So for the above two systems, we should expect to have a database size of
534 approximately 200 Megabytes. Of course, this will vary according to how many
535 files are actually backed up.
537 Below are some statistics for a MySQL database containing Job records for five
538 Clients beginning September 2001 through May 2002 (8.5 months) and File
539 records for the last 80 days. (Older File records have been pruned). For these
540 systems, only the user files and system files that change are backed up. The
541 core part of the system is assumed to be easily reloaded from the RedHat rpms.
544 In the list below, the files (corresponding to Bacula Tables) with the
545 extension .MYD contain the data records whereas files with the extension .MYI
548 You will note that the File records (containing the file attributes) make up
549 the large bulk of the number of records as well as the space used (459 Mega
550 Bytes including the indexes). As a consequence, the most important Retention
551 period will be the {\bf File Retention} period. A quick calculation shows that
552 for each File that is saved, the database grows by approximately 150 bytes.
558 ============ ========= ===========
561 344,394,684 3,080,191 File.MYD
563 2,590,316 106,902 Filename.MYD
564 3,026,944 Filename.MYI
567 49,062 1,326 JobMedia.MYD
569 141,752 1,378 Job.MYD
573 1,299,512 22,233 Path.MYD
582 This database has a total size of approximately 450 Megabytes.
584 If we were using SQLite, the determination of the total database size would be
585 much easier since it is a single file, but we would have less insight to the
586 size of the individual tables as we have in this case.
588 Note, SQLite databases may be as much as 50\% larger than MySQL databases due
589 to the fact that all data is stored as ASCII strings. That is even binary
590 integers are stored as ASCII strings, and this seems to increase the space