\chapter{Catalog Maintenance} \label{blb:CatMaintenanceChapter} \index[general]{Maintenance!Catalog } \index[general]{Catalog Maintenance } Without proper setup and maintenance, your Catalog may continue to grow indefinitely as you run Jobs and backup Files, and/or it may become very inefficient and slow. How fast the size of your Catalog grows depends on the number of Jobs you run and how many files they backup. By deleting records within the database, you can make space available for the new records that will be added during the next Job. By constantly deleting old expired records (dates older than the Retention period), your database size will remain constant. If you started with the default configuration files, they already contain reasonable defaults for a small number of machines (less than 5), so if you fall into that case, catalog maintenance will not be urgent if you have a few hundred megabytes of disk space free. Whatever the case may be, some knowledge of retention periods will be useful. \label{blb:Retention} \section{Setting Retention Periods} \index[general]{Setting Retention Periods } \index[general]{Periods!Setting Retention } \textbf{\bacula{}} uses three Retention periods: the \textbf{File Retention} period, the \textbf{Job Retention} period, and the \textbf{Volume Retention} period. Of these three, the File Retention period is by far the most important in determining how large your database will become. The \textbf{File Retention} and the \textbf{Job Retention} are specified in each Client resource as is shown below. The \textbf{Volume Retention} period is specified in the Pool resource, and the details are given in the next chapter of this manual. \begin{bdescription} \item [File Retention = \bbracket{time-period-specification}] \index[general]{File Retention } The File Retention record defines the length of time that \bacula{} will keep File records in the Catalog database. When this time period expires, and if \textbf{\bdirectivename{AutoPrune}} is set to \textbf{yes}, \bacula{} will prune (remove) File records that are older than the specified File Retention period. The pruning will occur at the end of a backup Job for the given Client. Note that the Client database record contains a copy of the File and Job retention periods, but \bacula{} uses the current values found in the Director's Client resource to do the pruning. Since File records in the database account for probably 80 percent of the size of the database, you should carefully determine exactly what File Retention period you need. Once the File records have been removed from the database, you will no longer be able to restore individual files in a Job. However, with \bacula{} version 1.37 and later, as long as the Job record still exists, you will be able to restore all files in the job. Retention periods are specified in seconds, but as a convenience, there are a number of modifiers that permit easy specification in terms of minutes, hours, days, weeks, months, quarters, or years on the record. See the \bilink{Configuration chapter}{blb:Time} of this manual for additional details of modifier specification. The default File retention period is \bdefaultvalue{60} days. \item [Job Retention = \bbracket{time-period-specification}] \index[general]{Job Retention } The Job Retention record defines the length of time that \textbf{\bacula{}} will keep Job records in the Catalog database. When this time period expires, and if \textbf{\bdirectivename{AutoPrune}} is set to \textbf{yes} \bacula{} will prune (remove) Job records that are older than the specified Job Retention period. Note, if a Job record is selected for pruning, all associated File and JobMedia records will also be pruned regardless of the File Retention period set. As a consequence, you normally will set the File retention period to be less than the Job retention period. As mentioned above, once the File records are removed from the database, you will no longer be able to restore individual files from the Job. However, as long as the Job record remains in the database, you will be able to restore all the files backuped for the Job (on version 1.37 and later). As a consequence, it is generally a good idea to retain the Job records much longer than the File records. The retention period is specified in seconds, but as a convenience, there are a number of modifiers that permit easy specification in terms of minutes, hours, days, weeks, months, quarters, or years. See the \bilink{Configuration chapter}{blb:Time} of this manual for additional details of modifier specification. The default Job Retention period is \bdefaultvalue{180} days. \item [AutoPrune = \byesorno{}] \index[general]{AutoPrune } If AutoPrune is set to \bdefaultvalue{yes} (default), \bacula{} will automatically apply the File retention period and the Job retention period for the Client at the end of the Job. If you turn this off by setting it to \textbf{no}, your Catalog will grow each time you run a Job. \end{bdescription} \label{blb:CompactingMySQL} \section{Compacting Your \mysql{} Database} \index[general]{Database!Compacting Your \mysql{} } \index[general]{Compacting Your \mysql{} Database } Over time, as noted above, your database will tend to grow. I've noticed that even though Bacula regularly prunes files, \mysql{} does not effectively use the space, and instead continues growing. To avoid this, from time to time, you must compact your database. Normally, large commercial database such as Oracle have commands that will compact a database to reclaim wasted file space. MySQL has the \textbf{OPTIMIZE TABLE} command that you can use. We leave it to you to explore the utility of the \textbf{OPTIMIZE TABLE} command in \mysql{}. All database programs have some means of writing the database out in \acs{ASCII} format and then reloading it. Doing so will re-create the database from scratch producing a compacted result, so below, we show you how you can do this for \mysql{}, \postgresql{}. For a \textbf{\mysql{}} database, you could write the \bacula{} database as an \acs{ASCII} file (\bfilename{bacula.sql}) then reload it by doing the following: \begin{bVerbatim} mysqldump -f --opt bacula > bacula.sql mysql bacula < bacula.sql rm -f bacula.sql \end{bVerbatim} Depending on the size of your database, this will take more or less time and a fair amount of disk space. For example, if I cd to the location of the \mysql{} \bacula{} database (typically \bdirectoryname{/opt/mysql/var} or something similar) and enter: \begin{bVerbatim} du bacula \end{bVerbatim} I get \textbf{620,644} which means there are that many blocks containing 1024 bytes each or approximately 635 MB of data. After doing the \btool{mysqldump}, I had a \bfilename{bacula.sql} file that had \textbf{174,356} blocks, and after doing the \btool{mysql} command to recreate the database, I ended up with a total of \textbf{210,464} blocks rather than the original \textbf{629,644}. In other words, the compressed version of the database took approximately one third of the space of the database that had been in use for about a year. As a consequence, I suggest you monitor the size of your database and from time to time (once every six months or year), compress it. \label{blb:DatabaseRepair} \label{blb:RepairingMySQL} \section{Repairing Your \mysql{} Database} \index[general]{Database!Repairing Your \mysql{} } \index[general]{Repairing Your \mysql{} Database } If you find that you are getting errors writing to your \mysql{} database, or \bacula{} hangs each time it tries to access the database, you should consider running \mysql{}'s database check and repair routines. The program you need to run depends on the type of database indexing you are using. If you are using the default, you will probably want to use \btool{myisamchk}. For more details on how to do this, please consult the \mysql{} document at: \bref{http://www.mysql.com/doc/en/Repair.html}{www.mysql.com/doc/en/Repair.html}. If the errors you are getting are simply \acs{SQL} warnings, then you might try running dbcheck before (or possibly after) using the \mysql{} database repair program. It can clean up many of the orphaned record problems, and certain other inconsistencies in the \bacula{} database. A typical cause of \mysql{} database problems is if your partition fills. In such a case, you will need to create additional space on the partition or free up some space then repair the database probably using \btool{myisamchk}. Recently my root partition filled and the \mysql{} database was corrupted. Simply running \btool{myisamchk -r} did not fix the problem. However, the following script did the trick for me: \begin{bVerbatim} #!/bin/sh for i in *.MYD ; do mv $i x${i} t=`echo $i | cut -f 1 -d '.' -` mysql bacula < bacula.sql cat bacula.sql | psql bacula rm -f bacula.sql \end{bVerbatim} Depending on the size of your database, this will take more or less time and a fair amount of disk space. For example, you can \btool{cd} to the location of the \bacula{} database (typically \bdirectoryname{/usr/local/pgsql/data} or possible \bdirectoryname{/var/lib/pgsql/data}) and check the size. There are certain \postgresql{} users who do not recommend the above procedure. They have the following to say: \postgresql{} does not need to be dumped/restored to keep the database efficient. A normal process of vacuuming will prevent the database from every getting too large. If you want to fine-tweak the database storage, commands such as \bcommandname{VACUUM FULL}, \bcommandname{REINDEX}, and \bcommandname{CLUSTER} exist specifically to keep you from having to do a dump/restore. Finally, you might want to look at the \postgresql{} documentation on this subject at \bref{http://www.postgresql.org/docs/8.1/interactive/maintenance.html}{www.postgresql.org/docs/8.1/interactive/maintenance.html}. \section{Migrating from \sqlite{} to \mysql{} or \postgresql{}} \index[general]{\mysql{}!Migrating from \sqlite{} to } \index[general]{Migrating from \sqlite{} to \mysql{} or \postgresql{}} On some older Bacula you may begun using Bacula with \sqlite{} then later find that you want to switch to MySQL or Postgres for any of a number of reasons: \sqlite{} is no longer supported by Bacula; \sqlite{} tends to use more disk than \mysql{}; when the database is corrupted it is often more catastrophic than with \mysql{} or \postgresql{}. Several users have succeeded in converting by exporting the \sqlite{} data and then processing it with Perl scripts prior to putting it into \mysql{} or \postgresql{}. This is, however, not always a simple process. Scripts are available on bacula source distribution under \texttt{examples/database}. \label{blb:BackingUpBacula} \section{Backing Up Your \bacula{} Database} \index[general]{Backing Up Your \bacula{} Database } \index[general]{Database!Backing Up Your \bacula{} } If ever the machine on which your \bacula{} database crashes, and you need to restore from backup tapes, one of your first priorities will probably be to recover the database. Although \bacula{} will happily backup your catalog database if it is specified in the FileSet, this is not a very good way to do it, because the database will be saved while \bacula{} is modifying it. Thus the database may be in an unstable state. Worse yet, you will backup the database before all the \bacula{} updates have been applied. To resolve these problems, you need to backup the database after all the backup jobs have been run. In addition, you will want to make a copy while \bacula{} is not modifying it. To do so, you can use two scripts provided in the release \bfilename{make\_catalog\_backup.pl} and \bfilename{delete\_catalog\_backup.pl}. These files will be automatically generated along with all the other \bacula{} scripts. The first script will make an \acs{ASCII} copy of your \bacula{} database into \bfilename{bacula.sql} in the working directory you specified in your configuration, and the second will delete the \bfilename{bacula.sql} file. The basic sequence of events to make this work correctly is as follows: \begin{bitemize} \item Run all your nightly backups \item After running your nightly backups, run a Catalog backup Job \item The Catalog backup job must be scheduled after your last nightly backup \item You use \textbf{RunBeforeJob} to create the \acs{ASCII} backup file and \textbf{RunAfterJob} to clean up \end{bitemize} Assuming that you start all your nightly backup jobs at 1:05 am (and that they run one after another), you can do the catalog backup with the following additional Director configuration statements: \begin{bVerbatim} # Backup the catalog database (after the nightly save) Job { Name = "BackupCatalog" Type = Backup Client=rufus-fd FileSet="Catalog" Schedule = "WeeklyCycleAfterBackup" Storage = DLTDrive Messages = Standard Pool = Default # WARNING!!! Passing the password via the command line is insecure. # see comments in make_catalog_backup.pl for details. RunBeforeJob = "/opt/bacula/scripts/make_catalog_backup.pl MyCatalog" RunAfterJob = "/opt/bacula/scripts/delete_catalog_backup" Write Bootstrap = "/opt/bacula/working/BackupCatalog.bsr" } # This schedule does the catalog. It starts after the WeeklyCycle Schedule { Name = "WeeklyCycleAfterBackup Run = Level=Full sun-sat at 1:10 } # This is the backup of the catalog FileSet { Name = "Catalog" Include { Options { signature=MD5 } File = /opt/bacula/working/bacula.sql } } \end{bVerbatim} Be sure to write a bootstrap file as in the above example. However, it is preferable to write or copy the bootstrap file to another computer. It will allow you to quickly recover the database backup should that be necessary. If you do not have a bootstrap file, it is still possible to recover your database backup, but it will be more work and take longer. \label{blb:BackingUpBaculaSecurityConsiderations} \section{Security considerations} \index[general]{Backing Up Your \bacula{} Database - Security Considerations } \index[general]{Database!Backing Up Your \bacula{} Database - Security Considerations } We provide \bfilename{make\_catalog\_backup.pl} as an example of what can be used to backup your \bacula{} database. We expect you to take security precautions relevant to your situation. \bfilename{make\_catalog\_backup.pl} is designed to take a password on the command line. This is fine on machines with only trusted users. It is not acceptable on machines without trusted users. Most database systems provide a alternative method, which does not place the password on the command line. The \bfilename{make\_catalog\_backup.pl} script contains some warnings about how to use it. Please read those tips. To help you get started, we know \postgresql{} has a password file, \bfootref{http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html}{.pgpass}, and we know \mysql{} has \bfootref{http://dev.mysql.com/doc/refman/4.1/en/password-security.html}{.my.cnf}. Only you can decide what is appropriate for your situation. We have provided you with a starting point. We hope it helps. \label{blb:BackingUPOtherDBs} \section{Backing Up Third Party Databases} \index[general]{Backing Up Third Party Databases } \index[general]{Databases!Backing Up Third Party } If you are running a database in production mode on your machine, \bacula{} will happily backup the files, but if the database is in use while \bacula{} is reading it, you may back it up in an unstable state. The best solution is to shutdown your database before backing it up, or use some tool specific to your database to make a valid live copy perhaps by dumping the database in \acs{ASCII} format. I am not a database expert, so I cannot provide you advice on how to do this, but if you are unsure about how to backup your database, you might try visiting the Backup Central site, which has been renamed Storage Mountain (\bref{http://www.backupcentral.com}{www.backupcentral.com}). In particular, their \bref{http://www.backupcentral.com/toc-free-backup-software.html}{Free Backup and Recovery Software} page has links to scripts that show you how to shutdown and backup most major databases. \label{blb:Size} \section{Database Size} \index[general]{Size!Database } \index[general]{Database Size } As mentioned above, if you do not do automatic pruning, your Catalog will grow each time you run a Job. Normally, you should decide how long you want File records to be maintained in the Catalog and set the \textbf{File Retention} period to that time. Then you can either wait and see how big your Catalog gets or make a calculation assuming approximately 154 bytes for each File saved and knowing the number of Files that are saved during each backup and the number of Clients you backup. For example, suppose you do a backup of two systems, each with 100,000 files. Suppose further that you do a Full backup weekly and an Incremental every day, and that the Incremental backup typically saves 4,000 files. The size of your database after a month can roughly be calculated as: \begin{bVerbatim} Size = 154 * No. Systems * (100,000 * 4 + 10,000 * 26) \end{bVerbatim} where we have assumed four weeks in a month and 26 incremental backups per month. This would give the following: \begin{bVerbatim} Size = 154 * 2 * (100,000 * 4 + 10,000 * 26) \end{bVerbatim} or \begin{bVerbatim} Size = 308 * (400,000 + 260,000) \end{bVerbatim} or \begin{bVerbatim} Size = 203,280,000 bytes \end{bVerbatim} So for the above two systems, we should expect to have a database size of approximately 200 Megabytes. Of course, this will vary according to how many files are actually backed up. Below are some statistics for a \mysql{} database containing Job records for five Clients beginning September 2001 through May 2002 (8.5 months) and File records for the last 80 days. (Older File records have been pruned). For these systems, only the user files and system files that change are backed up. The core part of the system is assumed to be easily reloaded from the Red Hat rpms. In the list below, the files (corresponding to \bacula{} tables) with the extension \bfilename{.MYD} contain the data records whereas files with the extension \bfilename{.MYI} contain indexes. You will note that the File records (containing the file attributes) make up the large bulk of the number of records as well as the space used (459 Mega Bytes including the indexes). As a consequence, the most important Retention period will be the \textbf{File Retention} period. A quick calculation shows that for each File that is saved, the database grows by approximately 150 bytes. \begin{bVerbatim} Size in Bytes Records File ============ ========= =========== 168 5 Client.MYD 3,072 Client.MYI 344,394,684 3,080,191 File.MYD 115,280,896 File.MYI 2,590,316 106,902 Filename.MYD 3,026,944 Filename.MYI 184 4 FileSet.MYD 2,048 FileSet.MYI 49,062 1,326 JobMedia.MYD 30,720 JobMedia.MYI 141,752 1,378 Job.MYD 13,312 Job.MYI 1,004 11 Media.MYD 3,072 Media.MYI 1,299,512 22,233 Path.MYD 581,632 Path.MYI 36 1 Pool.MYD 3,072 Pool.MYI 5 1 Version.MYD 1,024 Version.MYI \end{bVerbatim} This database has a total size of approximately 450 Megabytes.