4 \chapter{Catalog Services}
5 \label{TheChapterStart30}
6 \index[general]{Services!Catalog }
7 \index[general]{Catalog Services }
10 \index[general]{General }
11 \addcontentsline{toc}{subsection}{General}
13 This chapter is intended to be a technical discussion of the Catalog services
14 and as such is not targeted at end users but rather at developers and system
15 administrators that want or need to know more of the working details of {\bf
18 The {\bf Bacula Catalog} services consist of the programs that provide the SQL
19 database engine for storage and retrieval of all information concerning files
20 that were backed up and their locations on the storage media.
22 We have investigated the possibility of using the following SQL engines for
23 Bacula: Beagle, mSQL, GNU SQL, PostgreSQL, SQLite, Oracle, and MySQL. Each
24 presents certain problems with either licensing or maturity. At present, we
25 have chosen for development purposes to use MySQL, PostgreSQL and SQLite.
26 MySQL was chosen because it is fast, proven to be reliable, widely used, and
27 actively being developed. MySQL is released under the GNU GPL license.
28 PostgreSQL was chosen because it is a full-featured, very mature database, and
29 because Dan Langille did the Bacula driver for it. PostgreSQL is distributed
30 under the BSD license. SQLite was chosen because it is small, efficient, and
31 can be directly embedded in {\bf Bacula} thus requiring much less effort from
32 the system administrator or person building {\bf Bacula}. In our testing
33 SQLite has performed very well, and for the functions that we use, it has
34 never encountered any errors except that it does not appear to handle
35 databases larger than 2GBytes. That said, we would not recommend it for
36 serious production use.
38 The Bacula SQL code has been written in a manner that will allow it to be
39 easily modified to support any of the current SQL database systems on the
40 market (for example: mSQL, iODBC, unixODBC, Solid, OpenLink ODBC, EasySoft
41 ODBC, InterBase, Oracle8, Oracle7, and DB2).
43 If you do not specify either {\bf \verb{--{with-mysql} or {\bf \verb{--{with-postgresql} or
44 {\bf \verb{--{with-sqlite} on the ./configure line, Bacula will use its minimalist
45 internal database. This database is kept for build reasons but is no longer
46 supported. Bacula {\bf requires} one of the three databases (MySQL,
47 PostgreSQL, or SQLite) to run.
49 \subsection{Filenames and Maximum Filename Length}
50 \index[general]{Filenames and Maximum Filename Length }
51 \index[general]{Length!Filenames and Maximum Filename }
52 \addcontentsline{toc}{subsubsection}{Filenames and Maximum Filename Length}
54 In general, either MySQL, PostgreSQL or SQLite permit storing arbitrary long
55 path names and file names in the catalog database. In practice, there still
56 may be one or two places in the Catalog interface code that restrict the
57 maximum path length to 512 characters and the maximum file name length to 512
58 characters. These restrictions are believed to have been removed. Please note,
59 these restrictions apply only to the Catalog database and thus to your ability
60 to list online the files saved during any job. All information received and
61 stored by the Storage daemon (normally on tape) allows and handles arbitrarily
62 long path and filenames.
64 \subsection{Installing and Configuring MySQL}
65 \index[general]{MySQL!Installing and Configuring }
66 \index[general]{Installing and Configuring MySQL }
67 \addcontentsline{toc}{subsubsection}{Installing and Configuring MySQL}
69 For the details of installing and configuring MySQL, please see the
70 \borgxrlink{Installing and Configuring MySQL}{MySqlChapter}{main}{chapter} of
73 \subsection{Installing and Configuring PostgreSQL}
74 \index[general]{PostgreSQL!Installing and Configuring }
75 \index[general]{Installing and Configuring PostgreSQL }
76 \addcontentsline{toc}{subsubsection}{Installing and Configuring PostgreSQL}
78 For the details of installing and configuring PostgreSQL, please see the
79 \borgxrlink{Installing and Configuring PostgreSQL}{PostgreSqlChapter}{main}{chapter}
82 \subsection{Installing and Configuring SQLite}
83 \index[general]{Installing and Configuring SQLite }
84 \index[general]{SQLite!Installing and Configuring }
85 \addcontentsline{toc}{subsubsection}{Installing and Configuring SQLite}
87 For the details of installing and configuring SQLite, please see the
88 \borgxrlink{Installing and Configuring SQLite}{SqlLiteChapter}{main}{chapter} of
91 \subsection{Internal Bacula Catalog}
92 \index[general]{Catalog!Internal Bacula }
93 \index[general]{Internal Bacula Catalog }
94 \addcontentsline{toc}{subsubsection}{Internal Bacula Catalog}
96 Please see the \borgxrlink{Internal Bacula Database}{sec:InternalBaculaDatabase}{misc}{section} of the \miscman{} for more details.
98 \subsection{Database Table Design}
99 \index[general]{Design!Database Table }
100 \index[general]{Database Table Design }
101 \addcontentsline{toc}{subsubsection}{Database Table Design}
103 All discussions that follow pertain to the MySQL database. The details for the
104 PostgreSQL and SQLite databases are essentially identical except for that all
105 fields in the SQLite database are stored as ASCII text and some of the
106 database creation statements are a bit different. The details of the internal
107 Bacula catalog are not discussed here.
109 Because the Catalog database may contain very large amounts of data for large
110 sites, we have made a modest attempt to normalize the data tables to reduce
111 redundant information. While reducing the size of the database significantly,
112 it does, unfortunately, add some complications to the structures.
114 In simple terms, the Catalog database must contain a record of all Jobs run by
115 Bacula, and for each Job, it must maintain a list of all files saved, with
116 their File Attributes (permissions, create date, ...), and the location and
117 Media on which the file is stored. This is seemingly a simple task, but it
118 represents a huge amount interlinked data. Note: the list of files and their
119 attributes is not maintained when using the internal Bacula database. The data
120 stored in the File records, which allows the user or administrator to obtain a
121 list of all files backed up during a job, is by far the largest volume of
122 information put into the Catalog database.
124 Although the Catalog database has been designed to handle backup data for
125 multiple clients, some users may want to maintain multiple databases, one for
126 each machine to be backed up. This reduces the risk of confusion of accidental
127 restoring a file to the wrong machine as well as reducing the amount of data
128 in a single database, thus increasing efficiency and reducing the impact of a
129 lost or damaged database.
131 \section{Sequence of Creation of Records for a Save Job}
132 \index[general]{Sequence of Creation of Records for a Save Job }
133 \index[general]{Job!Sequence of Creation of Records for a Save }
134 \addcontentsline{toc}{subsection}{Sequence of Creation of Records for a Save
137 Start with StartDate, ClientName, Filename, Path, Attributes, MediaName,
138 MediaCoordinates. (PartNumber, NumParts). In the steps below, ``Create new''
139 means to create a new record whether or not it is unique. ``Create unique''
140 means each record in the database should be unique. Thus, one must first
141 search to see if the record exists, and only if not should a new one be
142 created, otherwise the existing RecordId should be used.
145 \item Create new Job record with StartDate; save JobId
146 \item Create unique Media record; save MediaId
147 \item Create unique Client record; save ClientId
148 \item Create unique Filename record; save FilenameId
149 \item Create unique Path record; save PathId
150 \item Create unique Attribute record; save AttributeId
151 store ClientId, FilenameId, PathId, and Attributes
152 \item Create new File record
153 store JobId, AttributeId, MediaCoordinates, etc
154 \item Repeat steps 4 through 8 for each file
155 \item Create a JobMedia record; save MediaId
156 \item Update Job record filling in EndDate and other Job statistics
159 \section{Database Tables}
160 \index[general]{Database Tables }
161 \index[general]{Tables!Database }
162 \addcontentsline{toc}{subsection}{Database Tables}
164 \addcontentsline{lot}{table}{Filename Table Layout}
165 \begin{longtable}{|l|l|l|}
167 \multicolumn{3}{|l| }{\bf Filename } \\
169 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{l| }{\bf Data Type }
170 & \multicolumn{1}{l| }{\bf Remark } \\
172 {FilenameId } & {integer } & {Primary Key } \\
174 {Name } & {Blob } & {Filename }
179 The {\bf Filename} table shown above contains the name of each file backed up
180 with the path removed. If different directories or machines contain the same
181 filename, only one copy will be saved in this table.
185 \addcontentsline{lot}{table}{Path Table Layout}
186 \begin{longtable}{|l|l|l|}
188 \multicolumn{3}{|l| }{\bf Path } \\
190 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
191 } & \multicolumn{1}{c| }{\bf Remark } \\
193 {PathId } & {integer } & {Primary Key } \\
195 {Path } & {Blob } & {Full Path }
200 The {\bf Path} table contains shown above the path or directory names of all
201 directories on the system or systems. The filename and any MSDOS disk name are
202 stripped off. As with the filename, only one copy of each directory name is
203 kept regardless of how many machines or drives have the same directory. These
204 path names should be stored in Unix path name format.
206 Some simple testing on a Linux file system indicates that separating the
207 filename and the path may be more complication than is warranted by the space
208 savings. For example, this system has a total of 89,097 files, 60,467 of which
209 have unique filenames, and there are 4,374 unique paths.
211 Finding all those files and doing two stats() per file takes an average wall
212 clock time of 1 min 35 seconds on a 400MHz machine running RedHat 6.1 Linux.
214 Finding all those files and putting them directly into a MySQL database with
215 the path and filename defined as TEXT, which is variable length up to 65,535
216 characters takes 19 mins 31 seconds and creates a 27.6 MByte database.
218 Doing the same thing, but inserting them into Blob fields with the filename
219 indexed on the first 30 characters and the path name indexed on the 255 (max)
220 characters takes 5 mins 18 seconds and creates a 5.24 MB database. Rerunning
221 the job (with the database already created) takes about 2 mins 50 seconds.
223 Running the same as the last one (Path and Filename Blob), but Filename
224 indexed on the first 30 characters and the Path on the first 50 characters
225 (linear search done there after) takes 5 mins on the average and creates a 3.4
226 MB database. Rerunning with the data already in the DB takes 3 mins 35
229 Finally, saving only the full path name rather than splitting the path and the
230 file, and indexing it on the first 50 characters takes 6 mins 43 seconds and
231 creates a 7.35 MB database.
235 \addcontentsline{lot}{table}{File Table Layout}
236 \begin{longtable}{|l|l|l|}
238 \multicolumn{3}{|l| }{\bf File } \\
240 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
241 } & \multicolumn{1}{c| }{\bf Remark } \\
243 {FileId } & {integer } & {Primary Key } \\
245 {FileIndex } & {integer } & {The sequential file number in the Job } \\
247 {JobId } & {integer } & {Link to Job Record } \\
249 {PathId } & {integer } & {Link to Path Record } \\
251 {FilenameId } & {integer } & {Link to Filename Record } \\
253 {MarkId } & {integer } & {Used to mark files during Verify Jobs } \\
255 {LStat } & {tinyblob } & {File attributes in base64 encoding } \\
257 {MD5 } & {tinyblob } & {MD5/SHA1 signature in base64 encoding }
262 The {\bf File} table shown above contains one entry for each file backed up by
263 Bacula. Thus a file that is backed up multiple times (as is normal) will have
264 multiple entries in the File table. This will probably be the table with the
265 most number of records. Consequently, it is essential to keep the size of this
266 record to an absolute minimum. At the same time, this table must contain all
267 the information (or pointers to the information) about the file and where it
268 is backed up. Since a file may be backed up many times without having changed,
269 the path and filename are stored in separate tables.
271 This table contains by far the largest amount of information in the Catalog
272 database, both from the stand point of number of records, and the stand point
273 of total database size. As a consequence, the user must take care to
274 periodically reduce the number of File records using the {\bf retention}
275 command in the Console program.
279 \addcontentsline{lot}{table}{Job Table Layout}
280 \begin{longtable}{|l|l|p{2.5in}|}
282 \multicolumn{3}{|l| }{\bf Job } \\
284 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
285 } & \multicolumn{1}{c| }{\bf Remark } \\
287 {JobId } & {integer } & {Primary Key } \\
289 {Job } & {tinyblob } & {Unique Job Name } \\
291 {Name } & {tinyblob } & {Job Name } \\
293 {PurgedFiles } & {tinyint } & {Used by Bacula for purging/retention periods
296 {Type } & {binary(1) } & {Job Type: Backup, Copy, Clone, Archive, Migration
299 {Level } & {binary(1) } & {Job Level } \\
301 {ClientId } & {integer } & {Client index } \\
303 {JobStatus } & {binary(1) } & {Job Termination Status } \\
305 {SchedTime } & {datetime } & {Time/date when Job scheduled } \\
307 {StartTime } & {datetime } & {Time/date when Job started } \\
309 {EndTime } & {datetime } & {Time/date when Job ended } \\
311 {RealEndTime } & {datetime } & {Time/date when original Job ended } \\
313 {JobTDate } & {bigint } & {Start day in Unix format but 64 bits; used for
314 Retention period. } \\
316 {VolSessionId } & {integer } & {Unique Volume Session ID } \\
318 {VolSessionTime } & {integer } & {Unique Volume Session Time } \\
320 {JobFiles } & {integer } & {Number of files saved in Job } \\
322 {JobBytes } & {bigint } & {Number of bytes saved in Job } \\
324 {JobErrors } & {integer } & {Number of errors during Job } \\
326 {JobMissingFiles } & {integer } & {Number of files not saved (not yet used) }
329 {PoolId } & {integer } & {Link to Pool Record } \\
331 {FileSetId } & {integer } & {Link to FileSet Record } \\
333 {PrioJobId } & {integer } & {Link to prior Job Record when migrated } \\
335 {PurgedFiles } & {tiny integer } & {Set when all File records purged } \\
337 {HasBase } & {tiny integer } & {Set when Base Job run }
342 The {\bf Job} table contains one record for each Job run by Bacula. Thus
343 normally, there will be one per day per machine added to the database. Note,
344 the JobId is used to index Job records in the database, and it often is shown
345 to the user in the Console program. However, care must be taken with its use
346 as it is not unique from database to database. For example, the user may have
347 a database for Client data saved on machine Rufus and another database for
348 Client data saved on machine Roxie. In this case, the two database will each
349 have JobIds that match those in another database. For a unique reference to a
352 The Name field of the Job record corresponds to the Name resource record given
353 in the Director's configuration file. Thus it is a generic name, and it will
354 be normal to find many Jobs (or even all Jobs) with the same Name.
356 The Job field contains a combination of the Name and the schedule time of the
357 Job by the Director. Thus for a given Director, even with multiple Catalog
358 databases, the Job will contain a unique name that represents the Job.
360 For a given Storage daemon, the VolSessionId and VolSessionTime form a unique
361 identification of the Job. This will be the case even if multiple Directors
362 are using the same Storage daemon.
364 The Job Type (or simply Type) can have one of the following values:
366 \addcontentsline{lot}{table}{Job Types}
367 \begin{longtable}{|l|l|}
369 \multicolumn{1}{|c| }{\bf Value } & \multicolumn{1}{c| }{\bf Meaning } \\
371 {B } & {Backup Job } \\
373 {M } & {Migrated Job } \\
375 {V } & {Verify Job } \\
377 {R } & {Restore Job } \\
379 {C } & {Console program (not in database) } \\
381 {I } & {Internal or system Job } \\
383 {D } & {Admin Job } \\
385 {A } & {Archive Job (not implemented) }
387 {C } & {Copy Job } \\
389 {g } & {Migration Job } \\
393 Note, the Job Type values noted above are not kept in an SQL table.
396 The JobStatus field specifies how the job terminated, and can be one of the
399 \addcontentsline{lot}{table}{Job Statuses}
400 \begin{longtable}{|l|l|}
402 \multicolumn{1}{|c| }{\bf Value } & \multicolumn{1}{c| }{\bf Meaning } \\
404 {C } & {Created but not yet running } \\
410 {T } & {Terminated normally } \\
412 {W } & {Terminated normally with warnings }
414 {E } & {Terminated in Error } \\
416 {e } & {Non-fatal error } \\
418 {f } & {Fatal error } \\
420 {D } & {Verify Differences } \\
422 {A } & {Canceled by the user } \\
424 {I } & {Incomplete Job }
426 {F } & {Waiting on the File daemon } \\
428 {S } & {Waiting on the Storage daemon } \\
430 {m } & {Waiting for a new Volume to be mounted } \\
432 {M } & {Waiting for a Mount } \\
434 {s } & {Waiting for Storage resource } \\
436 {j } & {Waiting for Job resource } \\
438 {c } & {Waiting for Client resource } \\
440 {d } & {Wating for Maximum jobs } \\
442 {t } & {Waiting for Start Time } \\
444 {p } & {Waiting for higher priority job to finish }
446 {i } & {Doing batch insert file records }
448 {a } & {SD despooling attributes }
450 {l } & {Doing data despooling }
452 {L } & {Committing data (last despool) }
459 \addcontentsline{lot}{table}{File Sets Table Layout}
460 \begin{longtable}{|l|l|l|}
462 \multicolumn{3}{|l| }{\bf FileSet } \\
464 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type\
465 \ \ } & \multicolumn{1}{c| }{\bf Remark } \\
467 {FileSetId } & {integer } & {Primary Key } \\
469 {FileSet } & {tinyblob } & {FileSet name } \\
471 {MD5 } & {tinyblob } & {MD5 checksum of FileSet } \\
473 {CreateTime } & {datetime } & {Time and date Fileset created }
478 The {\bf FileSet} table contains one entry for each FileSet that is used. The
479 MD5 signature is kept to ensure that if the user changes anything inside the
480 FileSet, it will be detected and the new FileSet will be used. This is
481 particularly important when doing an incremental update. If the user deletes a
482 file or adds a file, we need to ensure that a Full backup is done prior to the
486 \addcontentsline{lot}{table}{JobMedia Table Layout}
487 \begin{longtable}{|l|l|p{2.5in}|}
489 \multicolumn{3}{|l| }{\bf JobMedia } \\
491 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type\
492 \ \ } & \multicolumn{1}{c| }{\bf Remark } \\
494 {JobMediaId } & {integer } & {Primary Key } \\
496 {JobId } & {integer } & {Link to Job Record } \\
498 {MediaId } & {integer } & {Link to Media Record } \\
500 {FirstIndex } & {integer } & {The index (sequence number) of the first file
501 written for this Job to the Media } \\
503 {LastIndex } & {integer } & {The index of the last file written for this
504 Job to the Media } \\
506 {StartFile } & {integer } & {The physical media (tape) file number of the
507 first block written for this Job } \\
509 {EndFile } & {integer } & {The physical media (tape) file number of the
510 last block written for this Job } \\
512 {StartBlock } & {integer } & {The number of the first block written for
515 {EndBlock } & {integer } & {The number of the last block written for this
518 {VolIndex } & {integer } & {The Volume use sequence number within the Job }
523 The {\bf JobMedia} table contains one entry at the following: start of
524 the job, start of each new tape file, start of each new tape, end of the
525 job. Since by default, a new tape file is written every 2GB, in general,
526 you will have more than 2 JobMedia records per Job. The number can be
527 varied by changing the "Maximum File Size" specified in the Device
528 resource. This record allows Bacula to efficiently position close to
529 (within 2GB) any given file in a backup. For restoring a full Job,
530 these records are not very important, but if you want to retrieve
531 a single file that was written near the end of a 100GB backup, the
532 JobMedia records can speed it up by orders of magnitude by permitting
533 forward spacing files and blocks rather than reading the whole 100GB
539 \addcontentsline{lot}{table}{Media Table Layout}
540 \begin{longtable}{|l|l|p{2.4in}|}
542 \multicolumn{3}{|l| }{\bf Media } \\
544 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type\
545 \ \ } & \multicolumn{1}{c| }{\bf Remark } \\
547 {MediaId } & {integer } & {Primary Key } \\
549 {VolumeName } & {tinyblob } & {Volume name } \\
551 {Slot } & {integer } & {Autochanger Slot number or zero } \\
553 {PoolId } & {integer } & {Link to Pool Record } \\
555 {MediaType } & {tinyblob } & {The MediaType supplied by the user } \\
557 {MediaTypeId } & {integer } & {The MediaTypeId } \\
559 {LabelType } & {tinyint } & {The type of label on the Volume } \\
561 {FirstWritten } & {datetime } & {Time/date when first written } \\
563 {LastWritten } & {datetime } & {Time/date when last written } \\
565 {LabelDate } & {datetime } & {Time/date when tape labeled } \\
567 {VolJobs } & {integer } & {Number of jobs written to this media } \\
569 {VolFiles } & {integer } & {Number of files written to this media } \\
571 {VolBlocks } & {integer } & {Number of blocks written to this media } \\
573 {VolMounts } & {integer } & {Number of time media mounted } \\
575 {VolBytes } & {bigint } & {Number of bytes saved in Job } \\
577 {VolParts } & {integer } & {The number of parts for a Volume (DVD) } \\
579 {VolErrors } & {integer } & {Number of errors during Job } \\
581 {VolWrites } & {integer } & {Number of writes to media } \\
583 {MaxVolBytes } & {bigint } & {Maximum bytes to put on this media } \\
585 {VolCapacityBytes } & {bigint } & {Capacity estimate for this volume } \\
587 {VolStatus } & {enum } & {Status of media: Full, Archive, Append, Recycle,
588 Read-Only, Disabled, Error, Busy } \\
590 {Enabled } {tinyint } & {Whether or not Volume can be written } \\
592 {Recycle } & {tinyint } & {Whether or not Bacula can recycle the Volumes:
595 {ActionOnPurge } & {tinyint } & {What happens to a Volume after purging } \\
597 {VolRetention } & {bigint } & {64 bit seconds until expiration } \\
599 {VolUseDuration } & {bigint } & {64 bit seconds volume can be used } \\
601 {MaxVolJobs } & {integer } & {maximum jobs to put on Volume } \\
603 {MaxVolFiles } & {integer } & {maximume EOF marks to put on Volume }
605 {InChanger } & {tinyint } & {Whether or not Volume in autochanger } \\
607 {StorageId } & {integer } & {Storage record ID } \\
609 {DeviceId } & {integer } & {Device record ID } \\
611 {MediaAddressing } & {integer } & {Method of addressing media } \\
613 {VolReadTime } & {bigint } & {Time Reading Volume } \\
615 {VolWriteTime } & {bigint } & {Time Writing Volume } \\
617 {EndFile } & {integer } & {End File number of Volume } \\
619 {EndBlock } & {integer } & {End block number of Volume } \\
621 {LocationId } & {integer } & {Location record ID } \\
623 {RecycleCount } & {integer } & {Number of times recycled } \\
625 {InitialWrite } & {datetime } & {When Volume first written } \\
627 {ScratchPoolId } & {integer } & {Id of Scratch Pool } \\
629 {RecyclePoolId } & {integer } & {Pool ID where to recycle Volume } \\
631 {Comment } & {blob } & {User text field } \\
637 The {\bf Volume} table (internally referred to as the Media table) contains
638 one entry for each volume, that is each tape, cassette (8mm, DLT, DAT, ...),
639 or file on which information is or was backed up. There is one Volume record
640 created for each of the NumVols specified in the Pool resource record.
644 \addcontentsline{lot}{table}{Pool Table Layout}
645 \begin{longtable}{|l|l|p{2.4in}|}
647 \multicolumn{3}{|l| }{\bf Pool } \\
649 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
650 } & \multicolumn{1}{c| }{\bf Remark } \\
652 {PoolId } & {integer } & {Primary Key } \\
654 {Name } & {Tinyblob } & {Pool Name } \\
656 {NumVols } & {Integer } & {Number of Volumes in the Pool } \\
658 {MaxVols } & {Integer } & {Maximum Volumes in the Pool } \\
660 {UseOnce } & {tinyint } & {Use volume once } \\
662 {UseCatalog } & {tinyint } & {Set to use catalog } \\
664 {AcceptAnyVolume } & {tinyint } & {Accept any volume from Pool } \\
666 {VolRetention } & {bigint } & {64 bit seconds to retain volume } \\
668 {VolUseDuration } & {bigint } & {64 bit seconds volume can be used } \\
670 {MaxVolJobs } & {integer } & {max jobs on volume } \\
672 {MaxVolFiles } & {integer } & {max EOF marks to put on Volume } \\
674 {MaxVolBytes } & {bigint } & {max bytes to write on Volume } \\
676 {AutoPrune } & {tinyint } & {yes|no for autopruning } \\
678 {Recycle } & {tinyint } & {yes|no for allowing auto recycling of Volume } \\
680 {ActionOnPurge } & {tinyint } & {Default Volume ActionOnPurge } \\
682 {PoolType } & {enum } & {Backup, Copy, Cloned, Archive, Migration } \\
684 {LabelType } & {tinyint } & {Type of label ANSI/Bacula } \\
686 {LabelFormat } & {Tinyblob } & {Label format }
688 {Enabled } {tinyint } & {Whether or not Volume can be written } \\
690 {ScratchPoolId } & {integer } & {Id of Scratch Pool } \\
692 {RecyclePoolId } & {integer } & {Pool ID where to recycle Volume } \\
694 {NextPoolId } & {integer } & {Pool ID of next Pool } \\
696 {MigrationHighBytes } & {bigint } & {High water mark for migration } \\
698 {MigrationLowBytes } & {bigint } & {Low water mark for migration } \\
700 {MigrationTime } & {bigint } & {Time before migration } \\
707 The {\bf Pool} table contains one entry for each media pool controlled by
708 Bacula in this database. One media record exists for each of the NumVols
709 contained in the Pool. The PoolType is a Bacula defined keyword. The MediaType
710 is defined by the administrator, and corresponds to the MediaType specified in
711 the Director's Storage definition record. The CurrentVol is the sequence
712 number of the Media record for the current volume.
716 \addcontentsline{lot}{table}{Client Table Layout}
717 \begin{longtable}{|l|l|l|}
719 \multicolumn{3}{|l| }{\bf Client } \\
721 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
722 } & \multicolumn{1}{c| }{\bf Remark } \\
724 {ClientId } & {integer } & {Primary Key } \\
726 {Name } & {TinyBlob } & {File Services Name } \\
728 {UName } & {TinyBlob } & {uname -a from Client (not yet used) } \\
730 {AutoPrune } & {tinyint } & {yes|no for autopruning } \\
732 {FileRetention } & {bigint } & {64 bit seconds to retain Files } \\
734 {JobRetention } & {bigint } & {64 bit seconds to retain Job }
739 The {\bf Client} table contains one entry for each machine backed up by Bacula
740 in this database. Normally the Name is a fully qualified domain name.
743 \addcontentsline{lot}{table}{Storage Table Layout}
744 \begin{longtable}{|l|l|l|}
746 \multicolumn{3}{|l| }{\bf Storage } \\
748 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
749 } & \multicolumn{1}{c| }{\bf Remark } \\
751 {StorageId } & {integer } & {Unique Id } \\
753 {Name } & {tinyblob } & {Resource name of Storage device } \\
755 {AutoChanger } & {tinyint } & {Set if it is an autochanger } \\
760 The {\bf Storage} table contains one entry for each Storage used.
763 \addcontentsline{lot}{table}{Counter Table Layout}
764 \begin{longtable}{|l|l|l|}
766 \multicolumn{3}{|l| }{\bf Counter } \\
768 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
769 } & \multicolumn{1}{c| }{\bf Remark } \\
771 {Counter } & {tinyblob } & {Counter name } \\
773 {MinValue } & {integer } & {Start/Min value for counter } \\
775 {MaxValue } & {integer } & {Max value for counter } \\
777 {CurrentValue } & {integer } & {Current counter value } \\
779 {WrapCounter } & {tinyblob } & {Name of another counter }
784 The {\bf Counter} table contains one entry for each permanent counter defined
787 \addcontentsline{lot}{table}{Job History Table Layout}
788 \begin{longtable}{|l|l|p{2.5in}|}
790 \multicolumn{3}{|l| }{\bf JobHisto } \\
792 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
793 } & \multicolumn{1}{c| }{\bf Remark } \\
795 {JobId } & {integer } & {Primary Key } \\
797 {Job } & {tinyblob } & {Unique Job Name } \\
799 {Name } & {tinyblob } & {Job Name } \\
801 {Type } & {binary(1) } & {Job Type: Backup, Copy, Clone, Archive, Migration
804 {Level } & {binary(1) } & {Job Level } \\
806 {ClientId } & {integer } & {Client index } \\
808 {JobStatus } & {binary(1) } & {Job Termination Status } \\
810 {SchedTime } & {datetime } & {Time/date when Job scheduled } \\
812 {StartTime } & {datetime } & {Time/date when Job started } \\
814 {EndTime } & {datetime } & {Time/date when Job ended } \\
816 {RealEndTime } & {datetime } & {Time/date when original Job ended } \\
818 {JobTDate } & {bigint } & {Start day in Unix format but 64 bits; used for
819 Retention period. } \\
821 {VolSessionId } & {integer } & {Unique Volume Session ID } \\
823 {VolSessionTime } & {integer } & {Unique Volume Session Time } \\
825 {JobFiles } & {integer } & {Number of files saved in Job } \\
827 {JobBytes } & {bigint } & {Number of bytes saved in Job } \\
829 {JobErrors } & {integer } & {Number of errors during Job } \\
831 {JobMissingFiles } & {integer } & {Number of files not saved (not yet used) }
834 {PoolId } & {integer } & {Link to Pool Record } \\
836 {FileSetId } & {integer } & {Link to FileSet Record } \\
838 {PrioJobId } & {integer } & {Link to prior Job Record when migrated } \\
840 {PurgedFiles } & {tiny integer } & {Set when all File records purged } \\
842 {HasBase } & {tiny integer } & {Set when Base Job run }
847 The {bf JobHisto} table is the same as the Job table, but it keeps
848 long term statistics (i.e. it is not pruned with the Job).
851 \addcontentsline{lot}{table}{Log Table Layout}
852 \begin{longtable}{|l|l|l|}
854 \multicolumn{3}{|l| }{\bf Version } \\
856 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
857 } & \multicolumn{1}{c| }{\bf Remark } \\
859 {LogIdId } & {integer } & {Primary Key }
861 {JobId } & {integer } & {Points to Job record }
863 {Time } & {datetime } & {Time/date log record created }
865 {LogText } & {blob } & {Log text }
870 The {\bf Log} table contains a log of all Job output.
872 \addcontentsline{lot}{table}{Location Table Layout}
873 \begin{longtable}{|l|l|l|}
875 \multicolumn{3}{|l| }{\bf Location } \\
877 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
878 } & \multicolumn{1}{c| }{\bf Remark } \\
880 {LocationId } & {integer } & {Primary Key }
882 {Location } & {tinyblob } & {Text defining location }
884 {Cost } & {integer } & {Relative cost of obtaining Volume }
886 {Enabled } & {tinyint } & {Whether or not Volume is enabled }
891 The {\bf Location} table defines where a Volume is physically.
894 \addcontentsline{lot}{table}{Location Log Table Layout}
895 \begin{longtable}{|l|l|l|}
897 \multicolumn{3}{|l| }{\bf LocationLog } \\
899 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
900 } & \multicolumn{1}{c| }{\bf Remark } \\
902 {locLogIdId } & {integer } & {Primary Key }
904 {Date } & {datetime } & {Time/date log record created }
906 {MediaId } & {integer } & {Points to Media record }
908 {LocationId } & {integer } & {Points to Location record }
910 {NewVolStatus } & {integer } & {enum: Full, Archive, Append, Recycle, Purged
911 Read-only, Disabled, Error, Busy, Used, Cleaning }
913 {Enabled } & {tinyint } & {Whether or not Volume is enabled }
919 The {\bf Log} table contains a log of all Job output.
922 \addcontentsline{lot}{table}{Version Table Layout}
923 \begin{longtable}{|l|l|l|}
925 \multicolumn{3}{|l| }{\bf Version } \\
927 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
928 } & \multicolumn{1}{c| }{\bf Remark } \\
930 {VersionId } & {integer } & {Primary Key }
935 The {\bf Version} table defines the Bacula database version number. Bacula
936 checks this number before reading the database to ensure that it is compatible
937 with the Bacula binary file.
940 \addcontentsline{lot}{table}{Base Files Table Layout}
941 \begin{longtable}{|l|l|l|}
943 \multicolumn{3}{|l| }{\bf BaseFiles } \\
945 \multicolumn{1}{|c| }{\bf Column Name } & \multicolumn{1}{c| }{\bf Data Type
946 } & \multicolumn{1}{c| }{\bf Remark } \\
948 {BaseId } & {integer } & {Primary Key } \\
950 {BaseJobId } & {integer } & {JobId of Base Job } \\
952 {JobId } & {integer } & {Reference to Job } \\
954 {FileId } & {integer } & {Reference to File } \\
956 {FileIndex } & {integer } & {File Index number }
961 The {\bf BaseFiles} table contains all the File references for a particular
962 JobId that point to a Base file -- i.e. they were previously saved and hence
963 were not saved in the current JobId but in BaseJobId under FileId. FileIndex
964 is the index of the file, and is used for optimization of Restore jobs to
965 prevent the need to read the FileId record when creating the in memory tree.
966 This record is not yet implemented.
970 \subsection{MySQL Table Definition}
971 \index[general]{MySQL Table Definition }
972 \index[general]{Definition!MySQL Table }
973 \addcontentsline{toc}{subsubsection}{MySQL Table Definition}
975 The commands used to create the MySQL tables are as follows:
980 CREATE TABLE Filename (
981 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
983 PRIMARY KEY(FilenameId),
987 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
993 FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
994 FileIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
995 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
996 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
997 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
998 MarkId INTEGER UNSIGNED NOT NULL DEFAULT 0,
999 LStat TINYBLOB NOT NULL,
1000 MD5 TINYBLOB NOT NULL,
1001 PRIMARY KEY(FileId),
1007 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1008 Job TINYBLOB NOT NULL,
1009 Name TINYBLOB NOT NULL,
1010 Type BINARY(1) NOT NULL,
1011 Level BINARY(1) NOT NULL,
1012 ClientId INTEGER NOT NULL REFERENCES Client,
1013 JobStatus BINARY(1) NOT NULL,
1014 SchedTime DATETIME NOT NULL,
1015 StartTime DATETIME NOT NULL,
1016 EndTime DATETIME NOT NULL,
1017 JobTDate BIGINT UNSIGNED NOT NULL,
1018 VolSessionId INTEGER UNSIGNED NOT NULL DEFAULT 0,
1019 VolSessionTime INTEGER UNSIGNED NOT NULL DEFAULT 0,
1020 JobFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
1021 JobBytes BIGINT UNSIGNED NOT NULL,
1022 JobErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
1023 JobMissingFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
1024 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
1025 FileSetId INTEGER UNSIGNED NOT NULL REFERENCES FileSet,
1026 PurgedFiles TINYINT NOT NULL DEFAULT 0,
1027 HasBase TINYINT NOT NULL DEFAULT 0,
1031 CREATE TABLE FileSet (
1032 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1033 FileSet TINYBLOB NOT NULL,
1034 MD5 TINYBLOB NOT NULL,
1035 CreateTime DATETIME NOT NULL,
1036 PRIMARY KEY(FileSetId)
1038 CREATE TABLE JobMedia (
1039 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1040 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
1041 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
1042 FirstIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
1043 LastIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
1044 StartFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
1045 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
1046 StartBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
1047 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
1048 VolIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
1049 PRIMARY KEY(JobMediaId),
1050 INDEX (JobId, MediaId)
1052 CREATE TABLE Media (
1053 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1054 VolumeName TINYBLOB NOT NULL,
1055 Slot INTEGER NOT NULL DEFAULT 0,
1056 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
1057 MediaType TINYBLOB NOT NULL,
1058 FirstWritten DATETIME NOT NULL,
1059 LastWritten DATETIME NOT NULL,
1060 LabelDate DATETIME NOT NULL,
1061 VolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
1062 VolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
1063 VolBlocks INTEGER UNSIGNED NOT NULL DEFAULT 0,
1064 VolMounts INTEGER UNSIGNED NOT NULL DEFAULT 0,
1065 VolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
1066 VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
1067 VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0,
1068 VolCapacityBytes BIGINT UNSIGNED NOT NULL,
1069 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
1070 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
1071 Recycle TINYINT NOT NULL DEFAULT 0,
1072 VolRetention BIGINT UNSIGNED NOT NULL DEFAULT 0,
1073 VolUseDuration BIGINT UNSIGNED NOT NULL DEFAULT 0,
1074 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
1075 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
1076 MaxVolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
1077 InChanger TINYINT NOT NULL DEFAULT 0,
1078 MediaAddressing TINYINT NOT NULL DEFAULT 0,
1079 VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
1080 VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
1081 PRIMARY KEY(MediaId),
1085 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1086 Name TINYBLOB NOT NULL,
1087 NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
1088 MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
1089 UseOnce TINYINT NOT NULL,
1090 UseCatalog TINYINT NOT NULL,
1091 AcceptAnyVolume TINYINT DEFAULT 0,
1092 VolRetention BIGINT UNSIGNED NOT NULL,
1093 VolUseDuration BIGINT UNSIGNED NOT NULL,
1094 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
1095 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
1096 MaxVolBytes BIGINT UNSIGNED NOT NULL,
1097 AutoPrune TINYINT DEFAULT 0,
1098 Recycle TINYINT DEFAULT 0,
1099 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
1100 LabelFormat TINYBLOB,
1101 Enabled TINYINT DEFAULT 1,
1102 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
1103 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
1105 PRIMARY KEY (PoolId)
1107 CREATE TABLE Client (
1108 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1109 Name TINYBLOB NOT NULL,
1110 Uname TINYBLOB NOT NULL, /* full uname -a of client */
1111 AutoPrune TINYINT DEFAULT 0,
1112 FileRetention BIGINT UNSIGNED NOT NULL,
1113 JobRetention BIGINT UNSIGNED NOT NULL,
1115 PRIMARY KEY(ClientId)
1117 CREATE TABLE BaseFiles (
1118 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
1119 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
1120 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
1121 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
1122 FileIndex INTEGER UNSIGNED,
1125 CREATE TABLE UnsavedFiles (
1126 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
1127 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
1128 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
1129 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
1130 PRIMARY KEY (UnsavedId)
1132 CREATE TABLE Version (
1133 VersionId INTEGER UNSIGNED NOT NULL
1135 -- Initialize Version
1136 INSERT INTO Version (VersionId) VALUES (7);
1137 CREATE TABLE Counters (
1138 Counter TINYBLOB NOT NULL,
1141 CurrentValue INTEGER,
1142 WrapCounter TINYBLOB NOT NULL,
1143 PRIMARY KEY (Counter(128))