| 1 |
\chapter{Catalog Maintenance} |
| 2 |
\label{blb:CatMaintenanceChapter} |
| 3 |
\index[general]{Maintenance!Catalog } |
| 4 |
\index[general]{Catalog Maintenance } |
| 5 |
|
| 6 |
Without proper setup and maintenance, your Catalog may continue to grow |
| 7 |
indefinitely as you run Jobs and backup Files, and/or it may become |
| 8 |
very inefficient and slow. How fast the size of your |
| 9 |
Catalog grows depends on the number of Jobs you run and how many files they |
| 10 |
backup. By deleting records within the database, you can make space available |
| 11 |
for the new records that will be added during the next Job. By constantly |
| 12 |
deleting old expired records (dates older than the Retention period), your |
| 13 |
database size will remain constant. |
| 14 |
|
| 15 |
If you started with the default configuration files, they already contain |
| 16 |
reasonable defaults for a small number of machines (less than 5), so if you |
| 17 |
fall into that case, catalog maintenance will not be urgent if you have a few |
| 18 |
hundred megabytes of disk space free. Whatever the case may be, some knowledge |
| 19 |
of retention periods will be useful. |
| 20 |
\label{blb:Retention} |
| 21 |
|
| 22 |
\section{Setting Retention Periods} |
| 23 |
\index[general]{Setting Retention Periods } |
| 24 |
\index[general]{Periods!Setting Retention } |
| 25 |
|
| 26 |
\textbf{\bacula{}} uses three Retention periods: the \textbf{File Retention} period, |
| 27 |
the \textbf{Job Retention} period, and the \textbf{Volume Retention} period. Of |
| 28 |
these three, the File Retention period is by far the most important in |
| 29 |
determining how large your database will become. |
| 30 |
|
| 31 |
The \textbf{File Retention} and the \textbf{Job Retention} are specified in each |
| 32 |
Client resource as is shown below. The \textbf{Volume Retention} period is |
| 33 |
specified in the Pool resource, and the details are given in the next chapter |
| 34 |
of this manual. |
| 35 |
|
| 36 |
\begin{bdescription} |
| 37 |
|
| 38 |
\item [File Retention = \bbracket{time-period-specification}] |
| 39 |
\index[general]{File Retention } |
| 40 |
The File Retention record defines the length of time that \bacula{} will keep |
| 41 |
File records in the Catalog database. When this time period expires, and if |
| 42 |
\textbf{\bdirectivename{AutoPrune}} is set to \textbf{yes}, \bacula{} will prune (remove) File records |
| 43 |
that are older than the specified File Retention period. The pruning will |
| 44 |
occur at the end of a backup Job for the given Client. Note that the Client |
| 45 |
database record contains a copy of the File and Job retention periods, but |
| 46 |
\bacula{} uses the current values found in the Director's Client resource to do |
| 47 |
the pruning. |
| 48 |
|
| 49 |
Since File records in the database account for probably 80 percent of the |
| 50 |
size of the database, you should carefully determine exactly what File |
| 51 |
Retention period you need. Once the File records have been removed from |
| 52 |
the database, you will no longer be able to restore individual files |
| 53 |
in a Job. However, with \bacula{} version 1.37 and later, as long as the |
| 54 |
Job record still exists, you will be able to restore all files in the |
| 55 |
job. |
| 56 |
|
| 57 |
Retention periods are specified in seconds, but as a convenience, there are |
| 58 |
a number of modifiers that permit easy specification in terms of minutes, |
| 59 |
hours, days, weeks, months, quarters, or years on the record. See the |
| 60 |
\bilink{Configuration chapter}{blb:Time} of this manual for additional details |
| 61 |
of modifier specification. |
| 62 |
|
| 63 |
The default File retention period is \bdefaultvalue{60} days. |
| 64 |
|
| 65 |
\item [Job Retention = \bbracket{time-period-specification}] |
| 66 |
\index[general]{Job Retention } |
| 67 |
The Job Retention record defines the length of time that \textbf{\bacula{}} |
| 68 |
will keep Job records in the Catalog database. When this time period |
| 69 |
expires, and if \textbf{\bdirectivename{AutoPrune}} is set to \textbf{yes} \bacula{} will prune |
| 70 |
(remove) Job records that are older than the specified Job Retention |
| 71 |
period. Note, if a Job record is selected for pruning, all associated File |
| 72 |
and JobMedia records will also be pruned regardless of the File Retention |
| 73 |
period set. As a consequence, you normally will set the File retention |
| 74 |
period to be less than the Job retention period. |
| 75 |
|
| 76 |
As mentioned above, once the File records are removed from the database, |
| 77 |
you will no longer be able to restore individual files from the Job. |
| 78 |
However, as long as the Job record remains in the database, you will be |
| 79 |
able to restore all the files backuped for the Job (on version 1.37 and |
| 80 |
later). As a consequence, it is generally a good idea to retain the Job |
| 81 |
records much longer than the File records. |
| 82 |
|
| 83 |
The retention period is specified in seconds, but as a convenience, there |
| 84 |
are a number of modifiers that permit easy specification in terms of |
| 85 |
minutes, hours, days, weeks, months, quarters, or years. See the |
| 86 |
\bilink{Configuration chapter}{blb:Time} of this manual for additional details of |
| 87 |
modifier specification. |
| 88 |
|
| 89 |
The default Job Retention period is \bdefaultvalue{180} days. |
| 90 |
|
| 91 |
\item [AutoPrune = \byesorno{}] |
| 92 |
\index[general]{AutoPrune } |
| 93 |
If AutoPrune is set to \bdefaultvalue{yes} (default), \bacula{} will automatically apply |
| 94 |
the File retention period and the Job retention period for the Client at the |
| 95 |
end of the Job. |
| 96 |
|
| 97 |
If you turn this off by setting it to \textbf{no}, your Catalog will grow each |
| 98 |
time you run a Job. |
| 99 |
\end{bdescription} |
| 100 |
|
| 101 |
\label{blb:CompactingMySQL} |
| 102 |
\section{Compacting Your \mysql{} Database} |
| 103 |
\index[general]{Database!Compacting Your \mysql{} } |
| 104 |
\index[general]{Compacting Your \mysql{} Database } |
| 105 |
|
| 106 |
Over time, as noted above, your database will tend to grow. I've noticed |
| 107 |
that even though Bacula regularly prunes files, \mysql{} does not |
| 108 |
effectively use the space, and instead continues growing. To avoid this, |
| 109 |
from time to time, you must compact your database. Normally, large |
| 110 |
commercial database such as Oracle have commands that will compact a |
| 111 |
database to reclaim wasted file space. MySQL has the \textbf{OPTIMIZE TABLE} |
| 112 |
command that you can use. We leave it to you to explore the utility of the |
| 113 |
\textbf{OPTIMIZE TABLE} command in \mysql{}. |
| 114 |
|
| 115 |
All database programs have some means of writing the database out in \acs{ASCII} |
| 116 |
format and then reloading it. Doing so will re-create the database from |
| 117 |
scratch producing a compacted result, so below, we show you how you can do |
| 118 |
this for \mysql{}, \postgresql{}. |
| 119 |
|
| 120 |
For a \textbf{\mysql{}} database, you could write the \bacula{} database as an \acs{ASCII} |
| 121 |
file (\bfilename{bacula.sql}) then reload it by doing the following: |
| 122 |
|
| 123 |
\begin{bVerbatim} |
| 124 |
mysqldump -f --opt bacula > bacula.sql |
| 125 |
mysql bacula < bacula.sql |
| 126 |
rm -f bacula.sql |
| 127 |
\end{bVerbatim} |
| 128 |
|
| 129 |
Depending on the size of your database, this will take more or less time and a |
| 130 |
fair amount of disk space. For example, if I cd to the location of the \mysql{} |
| 131 |
\bacula{} database (typically \bdirectoryname{/opt/mysql/var} or something similar) and enter: |
| 132 |
|
| 133 |
\begin{bVerbatim} |
| 134 |
du bacula |
| 135 |
\end{bVerbatim} |
| 136 |
|
| 137 |
I get \textbf{620,644} which means there are that many blocks containing 1024 |
| 138 |
bytes each or approximately 635 MB of data. After doing the \btool{mysqldump}, I |
| 139 |
had a \bfilename{bacula.sql} file that had \textbf{174,356} blocks, and after doing the \btool{mysql} |
| 140 |
command to recreate the database, I ended up with a total of \textbf{210,464} |
| 141 |
blocks rather than the original \textbf{629,644}. In other words, the |
| 142 |
compressed version of the database took approximately one third of the space |
| 143 |
of the database that had been in use for about a year. |
| 144 |
|
| 145 |
As a consequence, I suggest you monitor the size of your database and from |
| 146 |
time to time (once every six months or year), compress it. |
| 147 |
|
| 148 |
\label{blb:DatabaseRepair} |
| 149 |
\label{blb:RepairingMySQL} |
| 150 |
\section{Repairing Your \mysql{} Database} |
| 151 |
\index[general]{Database!Repairing Your \mysql{} } |
| 152 |
\index[general]{Repairing Your \mysql{} Database } |
| 153 |
|
| 154 |
If you find that you are getting errors writing to your \mysql{} database, or |
| 155 |
\bacula{} hangs each time it tries to access the database, you should consider |
| 156 |
running \mysql{}'s database check and repair routines. The program you need to |
| 157 |
run depends on the type of database indexing you are using. If you are using |
| 158 |
the default, you will probably want to use \btool{myisamchk}. For more details |
| 159 |
on how to do this, please consult the \mysql{} document at: |
| 160 |
\bref{http://www.mysql.com/doc/en/Repair.html}{www.mysql.com/doc/en/Repair.html}. |
| 161 |
|
| 162 |
If the errors you are getting are simply \acs{SQL} warnings, then you might try |
| 163 |
running dbcheck before (or possibly after) using the \mysql{} database repair |
| 164 |
program. It can clean up many of the orphaned record problems, and certain |
| 165 |
other inconsistencies in the \bacula{} database. |
| 166 |
|
| 167 |
A typical cause of \mysql{} database problems is if your partition fills. In |
| 168 |
such a case, you will need to create additional space on the partition or |
| 169 |
free up some space then repair the database probably using \btool{myisamchk}. |
| 170 |
Recently my root partition filled and the \mysql{} database was corrupted. |
| 171 |
Simply running \btool{myisamchk -r} did not fix the problem. However, |
| 172 |
the following script did the trick for me: |
| 173 |
|
| 174 |
\begin{bVerbatim} |
| 175 |
#!/bin/sh |
| 176 |
for i in *.MYD ; do |
| 177 |
mv $i x${i} |
| 178 |
t=`echo $i | cut -f 1 -d '.' -` |
| 179 |
mysql bacula <<END_OF_DATA |
| 180 |
set autocommit=1; |
| 181 |
truncate table $t; |
| 182 |
quit |
| 183 |
END_OF_DATA |
| 184 |
cp x${i} ${i} |
| 185 |
chown mysql:mysql ${i} |
| 186 |
myisamchk -r ${t} |
| 187 |
done |
| 188 |
\end{bVerbatim} |
| 189 |
|
| 190 |
I invoked it with the following commands: |
| 191 |
|
| 192 |
\begin{bVerbatim} |
| 193 |
cd /var/lib/mysql/bacula |
| 194 |
./repair |
| 195 |
\end{bVerbatim} |
| 196 |
|
| 197 |
Then after ensuring that the database was correctly fixed, I did: |
| 198 |
\begin{bVerbatim} |
| 199 |
cd /var/lib/mysql/bacula |
| 200 |
rm -f x*.MYD |
| 201 |
\end{bVerbatim} |
| 202 |
|
| 203 |
\section{\mysql{} Table is Full} |
| 204 |
\index[general]{Database!\mysql{} Table is Full} |
| 205 |
\index[general]{\mysql{} Table is Full} |
| 206 |
|
| 207 |
If you are running into the error \textbf{The table 'File' is full \ldots{}}, |
| 208 |
it is probably because on version 4.x \mysql{}, the table is limited by |
| 209 |
default to a maximum size of 4 GB and you have probably run into |
| 210 |
the limit. The solution can be found at: |
| 211 |
\bref{http://dev.mysql.com/doc/refman/5.0/en/full-table.html}{dev.mysql.com/doc/refman/5.0/en/full-table.html} |
| 212 |
|
| 213 |
You can display the maximum length of your table with: |
| 214 |
|
| 215 |
\begin{bVerbatim} |
| 216 |
mysql bacula |
| 217 |
SHOW TABLE STATUS FROM bacula like "File"; |
| 218 |
\end{bVerbatim} |
| 219 |
|
| 220 |
If the column labeled "Max\_data\_length" is around 4Gb, this is likely |
| 221 |
to be the source of your problem, and you can modify it with: |
| 222 |
|
| 223 |
\begin{bVerbatim} |
| 224 |
mysql bacula |
| 225 |
ALTER TABLE File MAX_ROWS=281474976710656; |
| 226 |
\end{bVerbatim} |
| 227 |
|
| 228 |
Alternatively you can modify your \bfilename{/etc/my.conf} file before creating the |
| 229 |
\bacula{} tables, and in the \texttt{[mysqld]} section set: |
| 230 |
|
| 231 |
\begin{bVerbatim} |
| 232 |
set-variable = myisam_data_pointer_size=6 |
| 233 |
\end{bVerbatim} |
| 234 |
|
| 235 |
The above myisam data pointer size must be made before you create your |
| 236 |
\bacula{} tables or it will have no effect. |
| 237 |
|
| 238 |
The row and pointer size changes should already be the default on \mysql{} |
| 239 |
version 5.x, so making these changes should only be necessary on \mysql{} 4.x |
| 240 |
depending on the size of your catalog database. |
| 241 |
|
| 242 |
\section{\mysql{} Server Has Gone Away} |
| 243 |
\index[general]{Database!\mysql{} Server Has Gone Away} |
| 244 |
\index[general]{\mysql{} Server Has Gone Away} |
| 245 |
If you are having problems with the \mysql{} server disconnecting or with |
| 246 |
messages saying that your \mysql{} server has gone away, then please read |
| 247 |
the \mysql{} documentation, which can be found at: |
| 248 |
|
| 249 |
\bref{http://dev.mysql.com/doc/refman/5.0/en/gone-away.html} |
| 250 |
{dev.mysql.com/doc/refman/5.0/en/gone-away.html} |
| 251 |
|
| 252 |
\section{\mysql{} Temporary Tables} |
| 253 |
When doing backups with large numbers of files, \mysql{} creates some |
| 254 |
temporary tables. When these tables are small they can be held in |
| 255 |
system memory, but as they approach some size, they |
| 256 |
spool off to disk. The default location for these temp tables is |
| 257 |
\bdirectoryname{/tmp}. Once that space fills up, \bacula{} daemons such as the Storage |
| 258 |
daemon doing spooling can get strange errors. E.g. |
| 259 |
|
| 260 |
\begin{bVerbatim} |
| 261 |
Fatal error: spool.c:402 Spool data read error. |
| 262 |
Fatal error: backup.c:892 Network send error to SD. ERR=Connection reset by peer |
| 263 |
\end{bVerbatim} |
| 264 |
|
| 265 |
What you need to do is setup \mysql{} to use a different (larger) temp |
| 266 |
directory, which can be set in the \bfilename{/etc/my.cnf} with these variables |
| 267 |
set: |
| 268 |
|
| 269 |
\begin{bVerbatim} |
| 270 |
tmpdir=/path/to/larger/tmpdir |
| 271 |
bdb_tmpdir=/path/to/larger/tmpdir |
| 272 |
\end{bVerbatim} |
| 273 |
|
| 274 |
\label{blb:RepairingPSQL} |
| 275 |
\section{Repairing Your \postgresql{} Database} |
| 276 |
\index[general]{Database!Repairing Your \postgresql{} } |
| 277 |
\index[general]{Repairing Your \postgresql{} Database } |
| 278 |
|
| 279 |
The same considerations apply that are indicated above for \mysql{}. That is, |
| 280 |
consult the \postgresql{} documents for how to repair the database, and also |
| 281 |
consider using \bacula{}'s \btool{dbcheck} program if the conditions are reasonable for |
| 282 |
using (see above). |
| 283 |
|
| 284 |
\label{blb:DatabasePerformance} |
| 285 |
\section{Database Performance Issues} |
| 286 |
\index[general]{Database Performance Issues} |
| 287 |
\index[general]{Performance!Database} |
| 288 |
|
| 289 |
There are a considerable number of ways each of the databases can be |
| 290 |
tuned to improve the performance. Going from an untuned database to one |
| 291 |
that is properly tuned can make a difference of a factor of 100 or more |
| 292 |
in the time to insert or search for records. |
| 293 |
|
| 294 |
For each of the databases, you may get significant improvements by adding |
| 295 |
additional indexes. The comments in the \bacula{} make\_xxx\_tables give some |
| 296 |
indications as to what indexes may be appropriate. Please see below |
| 297 |
for specific instructions on checking indexes. |
| 298 |
|
| 299 |
For \mysql{}, what is very important is to use the examine the |
| 300 |
\bfilename{my.cnf} file (usually in \bfilename{/etc/my.cnf}). |
| 301 |
You may obtain significant performances by switching to |
| 302 |
the \bfilename{my-large.cnf} or \bfilename{my-huge.cnf} files that come with the \mysql{} source |
| 303 |
code. |
| 304 |
|
| 305 |
For \postgresql{}, you might want to consider turning \texttt{fsync} off. Of course |
| 306 |
doing so can cause corrupted databases in the event of a machine crash. |
| 307 |
There are many different ways that you can tune \postgresql{}, the |
| 308 |
following document discusses a few of them: |
| 309 |
\bref{http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html} |
| 310 |
{www.varlena.com/varlena/GeneralBits/Tidbits/perf.html}. |
| 311 |
|
| 312 |
There is also a \postgresql{} FAQ question number 3.3 that may |
| 313 |
answer some of your questions about how to improve performance |
| 314 |
of the \postgresql{} engine: |
| 315 |
\bref{http://www.postgresql.org/docs/faqs.FAQ.html\#3.3}{www.postgresql.org/docs/faqs.FAQ.html\#3.3}. |
| 316 |
% TODO: verify above is correct. is this okay for book? |
| 317 |
|
| 318 |
Also for \postgresql{}, look at what ``\texttt{effective\_cache\_size}''. For a 2GB memory |
| 319 |
machine, you probably want to set it at 131072, but don't set it too high. |
| 320 |
In addition, for a 2GB system, \texttt{work\_mem = 256000} and |
| 321 |
\texttt{maintenance\_work\_mem = 256000} seem to be reasonable values. Make |
| 322 |
sure your \texttt{checkpoint\_segments} is set to at least 8. |
| 323 |
|
| 324 |
|
| 325 |
|
| 326 |
\section{Performance Issues Indexes} |
| 327 |
\index[general]{Database Performance Issues Indexes} |
| 328 |
\index[general]{Performance!Database} |
| 329 |
One of the most important considerations for improving performance on |
| 330 |
the \bacula{} database is to ensure that it has all the appropriate indexes. |
| 331 |
Several users have reported finding that their database did not have |
| 332 |
all the indexes in the default configuration. In addition, you may |
| 333 |
find that because of your own usage patterns, you need additional indexes. |
| 334 |
|
| 335 |
The most important indexes for performance are the two indexes on the |
| 336 |
\textbf{\btable{File}} table. The first index is on \textbf{\btable{FileId}} and is automatically |
| 337 |
made because it is the unique key used to access the table. The other |
| 338 |
one is the (JobId, PathId, Filename) index. If these Indexes |
| 339 |
are not present, your performance may suffer a lot. |
| 340 |
|
| 341 |
\subsection{\postgresql{} Indexes} |
| 342 |
On \postgresql{}, you can check to see if you have the proper indexes using |
| 343 |
the following commands: |
| 344 |
|
| 345 |
\begin{bVerbatim} |
| 346 |
psql bacula |
| 347 |
select * from pg_indexes where tablename='file'; |
| 348 |
\end{bVerbatim} |
| 349 |
|
| 350 |
If you do not see output that indicates that all three indexes |
| 351 |
are created, you can create the two additional indexes using: |
| 352 |
|
| 353 |
\begin{bVerbatim} |
| 354 |
psql bacula |
| 355 |
CREATE INDEX file_jobid_idx on file (jobid); |
| 356 |
CREATE INDEX file_jpf_idx on file (jobid, pathid, filename); |
| 357 |
\end{bVerbatim} |
| 358 |
|
| 359 |
Make sure that you doesn't have an index on File (filenameid, pathid). |
| 360 |
|
| 361 |
\subsection{\mysql{} Indexes} |
| 362 |
On \mysql{}, you can check if you have the proper indexes by: |
| 363 |
|
| 364 |
\begin{bVerbatim} |
| 365 |
mysql bacula |
| 366 |
show index from File; |
| 367 |
\end{bVerbatim} |
| 368 |
|
| 369 |
If the indexes are not present, especially the JobId index, you can |
| 370 |
create them with the following commands: |
| 371 |
|
| 372 |
\begin{bVerbatim} |
| 373 |
mysql bacula |
| 374 |
CREATE INDEX file_jobid_idx on File (JobId); |
| 375 |
CREATE INDEX file_jpf_idx on File (JobId, PathId, Filename(255)); |
| 376 |
\end{bVerbatim} |
| 377 |
|
| 378 |
Though normally not a problem, you should ensure that the indexes |
| 379 |
defined for Filename and Path are both set to 255 characters. Some users |
| 380 |
reported performance problems when their indexes were set to 50 characters. |
| 381 |
To check, do: |
| 382 |
|
| 383 |
\begin{bVerbatim} |
| 384 |
mysql bacula |
| 385 |
show index from Path; |
| 386 |
\end{bVerbatim} |
| 387 |
|
| 388 |
and what is important is that for Filename, you have an index with |
| 389 |
Key\_name ``Name'' and Sub\_part ``255''. For Path, you should have a Key\_name |
| 390 |
``Path'' and Sub\_part ``255''. If one or the other does not exist or the |
| 391 |
Sub\_part is less that 255, you can drop and recreate the appropriate |
| 392 |
index with: |
| 393 |
|
| 394 |
\begin{bVerbatim} |
| 395 |
mysql bacula |
| 396 |
DROP INDEX Path on Path; |
| 397 |
CREATE INDEX Path on Path (Path(255)); |
| 398 |
\end{bVerbatim} |
| 399 |
|
| 400 |
|
| 401 |
\label{blb:CompactingPostgres} |
| 402 |
\section{Compacting Your \postgresql{} Database} |
| 403 |
\index[general]{Database!Compacting Your \postgresql{} } |
| 404 |
\index[general]{Compacting Your \postgresql{} Database } |
| 405 |
|
| 406 |
Over time, as noted above, your database will tend to grow. I've noticed that |
| 407 |
even though \bacula{} regularly prunes files, \postgresql{} has a \bcommandname{VACUUM} |
| 408 |
command that will compact your database for you. Alternatively you may want to |
| 409 |
use the \btool{vacuumdb} command, which can be run from a cron job. |
| 410 |
|
| 411 |
All database programs have some means of writing the database out in \acs{ASCII} |
| 412 |
format and then reloading it. Doing so will re-create the database from |
| 413 |
scratch producing a compacted result, so below, we show you how you can do |
| 414 |
this for \postgresql{}. |
| 415 |
|
| 416 |
For a \textbf{\postgresql{}} database, you could write the \bacula{} database as an |
| 417 |
\acs{ASCII} file (\bfilename{bacula.sql}) then reload it by doing the following: |
| 418 |
|
| 419 |
\begin{bVerbatim} |
| 420 |
pg_dump -c bacula > bacula.sql |
| 421 |
cat bacula.sql | psql bacula |
| 422 |
rm -f bacula.sql |
| 423 |
\end{bVerbatim} |
| 424 |
|
| 425 |
Depending on the size of your database, this will take more or less time and a |
| 426 |
fair amount of disk space. For example, you can \btool{cd} to the location of |
| 427 |
the \bacula{} database (typically \bdirectoryname{/usr/local/pgsql/data} or possible |
| 428 |
\bdirectoryname{/var/lib/pgsql/data}) and check the size. |
| 429 |
|
| 430 |
There are certain \postgresql{} users who do not recommend the above |
| 431 |
procedure. They have the following to say: |
| 432 |
\postgresql{} does not |
| 433 |
need to be dumped/restored to keep the database efficient. A normal |
| 434 |
process of vacuuming will prevent the database from every getting too |
| 435 |
large. If you want to fine-tweak the database storage, commands such |
| 436 |
as \bcommandname{VACUUM FULL}, \bcommandname{REINDEX}, and \bcommandname{CLUSTER} exist specifically to keep you |
| 437 |
from having to do a dump/restore. |
| 438 |
|
| 439 |
Finally, you might want to look at the \postgresql{} documentation on |
| 440 |
this subject at |
| 441 |
\bref{http://www.postgresql.org/docs/8.1/interactive/maintenance.html}{www.postgresql.org/docs/8.1/interactive/maintenance.html}. |
| 442 |
|
| 443 |
\section{Migrating from \sqlite{} to \mysql{} or \postgresql{}} |
| 444 |
\index[general]{\mysql{}!Migrating from \sqlite{} to } |
| 445 |
\index[general]{Migrating from \sqlite{} to \mysql{} or \postgresql{}} |
| 446 |
|
| 447 |
On some older Bacula you may begun using Bacula with \sqlite{} then later find |
| 448 |
that you want to switch to MySQL or Postgres for any of a number of |
| 449 |
reasons: \sqlite{} is no longer supported by Bacula; \sqlite{} tends to use more |
| 450 |
disk than \mysql{}; when the database is corrupted it is often more |
| 451 |
catastrophic than with \mysql{} or \postgresql{}. Several users have succeeded in |
| 452 |
converting by exporting the \sqlite{} data and then processing it with Perl |
| 453 |
scripts prior to putting it into \mysql{} or \postgresql{}. This is, however, not |
| 454 |
always a simple process. Scripts are available on bacula source distribution |
| 455 |
under \texttt{examples/database}. |
| 456 |
|
| 457 |
|
| 458 |
\label{blb:BackingUpBacula} |
| 459 |
\section{Backing Up Your \bacula{} Database} |
| 460 |
\index[general]{Backing Up Your \bacula{} Database } |
| 461 |
\index[general]{Database!Backing Up Your \bacula{} } |
| 462 |
|
| 463 |
If ever the machine on which your \bacula{} database crashes, and you need to |
| 464 |
restore from backup tapes, one of your first priorities will probably be to |
| 465 |
recover the database. Although \bacula{} will happily backup your catalog |
| 466 |
database if it is specified in the FileSet, this is not a very good way to do |
| 467 |
it, because the database will be saved while \bacula{} is modifying it. Thus the |
| 468 |
database may be in an unstable state. Worse yet, you will backup the database |
| 469 |
before all the \bacula{} updates have been applied. |
| 470 |
|
| 471 |
To resolve these problems, you need to backup the database after all the backup |
| 472 |
jobs have been run. In addition, you will want to make a copy while \bacula{} is |
| 473 |
not modifying it. To do so, you can use two scripts provided in the release |
| 474 |
\bfilename{make\_catalog\_backup.pl} and \bfilename{delete\_catalog\_backup.pl}. These files |
| 475 |
will be automatically generated along with all the other \bacula{} scripts. The |
| 476 |
first script will make an \acs{ASCII} copy of your \bacula{} database into \bfilename{bacula.sql} |
| 477 |
in the working directory you specified in your configuration, and |
| 478 |
the second will delete the \bfilename{bacula.sql} file. |
| 479 |
|
| 480 |
The basic sequence of events to make this work correctly is as follows: |
| 481 |
|
| 482 |
\begin{bitemize} |
| 483 |
\item Run all your nightly backups |
| 484 |
\item After running your nightly backups, run a Catalog backup Job |
| 485 |
\item The Catalog backup job must be scheduled after your last nightly backup |
| 486 |
\item You use \textbf{RunBeforeJob} to create the \acs{ASCII} backup file and \textbf{RunAfterJob} to clean up |
| 487 |
\end{bitemize} |
| 488 |
|
| 489 |
Assuming that you start all your nightly backup jobs at 1:05 am (and that they |
| 490 |
run one after another), you can do the catalog backup with the following |
| 491 |
additional Director configuration statements: |
| 492 |
|
| 493 |
\begin{bVerbatim} |
| 494 |
# Backup the catalog database (after the nightly save) |
| 495 |
Job { |
| 496 |
Name = "BackupCatalog" |
| 497 |
Type = Backup |
| 498 |
Client=rufus-fd |
| 499 |
FileSet="Catalog" |
| 500 |
Schedule = "WeeklyCycleAfterBackup" |
| 501 |
Storage = DLTDrive |
| 502 |
Messages = Standard |
| 503 |
Pool = Default |
| 504 |
# WARNING!!! Passing the password via the command line is insecure. |
| 505 |
# see comments in make_catalog_backup.pl for details. |
| 506 |
RunBeforeJob = "/opt/bacula/scripts/make_catalog_backup.pl MyCatalog" |
| 507 |
RunAfterJob = "/opt/bacula/scripts/delete_catalog_backup" |
| 508 |
Write Bootstrap = "/opt/bacula/working/BackupCatalog.bsr" |
| 509 |
} |
| 510 |
# This schedule does the catalog. It starts after the WeeklyCycle |
| 511 |
Schedule { |
| 512 |
Name = "WeeklyCycleAfterBackup |
| 513 |
Run = Level=Full sun-sat at 1:10 |
| 514 |
} |
| 515 |
# This is the backup of the catalog |
| 516 |
FileSet { |
| 517 |
Name = "Catalog" |
| 518 |
Include { |
| 519 |
Options { |
| 520 |
signature=MD5 |
| 521 |
} |
| 522 |
File = /opt/bacula/working/bacula.sql |
| 523 |
} |
| 524 |
} |
| 525 |
\end{bVerbatim} |
| 526 |
|
| 527 |
Be sure to write a bootstrap file as in the above example. However, it is preferable |
| 528 |
to write or copy the bootstrap file to another computer. It will allow |
| 529 |
you to quickly recover the database backup should that be necessary. If |
| 530 |
you do not have a bootstrap file, it is still possible to recover your |
| 531 |
database backup, but it will be more work and take longer. |
| 532 |
|
| 533 |
|
| 534 |
\label{blb:BackingUpBaculaSecurityConsiderations} |
| 535 |
\section{Security considerations} |
| 536 |
\index[general]{Backing Up Your \bacula{} Database - Security Considerations } |
| 537 |
\index[general]{Database!Backing Up Your \bacula{} Database - Security Considerations } |
| 538 |
|
| 539 |
We provide \bfilename{make\_catalog\_backup.pl} as an example of what can be used to backup |
| 540 |
your \bacula{} database. We expect you to take security precautions relevant |
| 541 |
to your situation. \bfilename{make\_catalog\_backup.pl} is designed to take a password on |
| 542 |
the command line. This is fine on machines with only trusted users. It is |
| 543 |
not acceptable on machines without trusted users. Most database systems |
| 544 |
provide a alternative method, which does not place the password on the |
| 545 |
command line. |
| 546 |
|
| 547 |
The \bfilename{make\_catalog\_backup.pl} script contains some warnings about how to use it. Please |
| 548 |
read those tips. |
| 549 |
|
| 550 |
To help you get started, we know \postgresql{} has a password file, |
| 551 |
\bfootref{http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html}{.pgpass}, and |
| 552 |
we know \mysql{} has |
| 553 |
\bfootref{http://dev.mysql.com/doc/refman/4.1/en/password-security.html}{.my.cnf}. |
| 554 |
|
| 555 |
Only you can decide what is appropriate for your situation. We have provided |
| 556 |
you with a starting point. We hope it helps. |
| 557 |
|
| 558 |
|
| 559 |
\label{blb:BackingUPOtherDBs} |
| 560 |
\section{Backing Up Third Party Databases} |
| 561 |
\index[general]{Backing Up Third Party Databases } |
| 562 |
\index[general]{Databases!Backing Up Third Party } |
| 563 |
|
| 564 |
If you are running a database in production mode on your machine, \bacula{} will |
| 565 |
happily backup the files, but if the database is in use while \bacula{} is |
| 566 |
reading it, you may back it up in an unstable state. |
| 567 |
|
| 568 |
The best solution is to shutdown your database before backing it up, or use |
| 569 |
some tool specific to your database to make a valid live copy perhaps by |
| 570 |
dumping the database in \acs{ASCII} format. I am not a database expert, so I cannot |
| 571 |
provide you advice on how to do this, but if you are unsure about how to |
| 572 |
backup your database, you might try visiting the Backup Central site, which |
| 573 |
has been renamed Storage Mountain (\bref{http://www.backupcentral.com}{www.backupcentral.com}). In particular, |
| 574 |
their \bref{http://www.backupcentral.com/toc-free-backup-software.html}{Free Backup and Recovery Software} page has |
| 575 |
links to scripts that show you how to shutdown and backup most major |
| 576 |
databases. |
| 577 |
\label{blb:Size} |
| 578 |
|
| 579 |
\section{Database Size} |
| 580 |
\index[general]{Size!Database } |
| 581 |
\index[general]{Database Size } |
| 582 |
|
| 583 |
As mentioned above, if you do not do automatic pruning, your Catalog will grow |
| 584 |
each time you run a Job. Normally, you should decide how long you want File |
| 585 |
records to be maintained in the Catalog and set the \textbf{File Retention} |
| 586 |
period to that time. Then you can either wait and see how big your Catalog |
| 587 |
gets or make a calculation assuming approximately 154 bytes for each File |
| 588 |
saved and knowing the number of Files that are saved during each backup and |
| 589 |
the number of Clients you backup. |
| 590 |
|
| 591 |
For example, suppose you do a backup of two systems, each with 100,000 files. |
| 592 |
Suppose further that you do a Full backup weekly and an Incremental every day, |
| 593 |
and that the Incremental backup typically saves 4,000 files. The size of your |
| 594 |
database after a month can roughly be calculated as: |
| 595 |
|
| 596 |
\begin{bVerbatim} |
| 597 |
Size = 154 * No. Systems * (100,000 * 4 + 10,000 * 26) |
| 598 |
\end{bVerbatim} |
| 599 |
|
| 600 |
where we have assumed four weeks in a month and 26 incremental backups per month. |
| 601 |
This would give the following: |
| 602 |
|
| 603 |
\begin{bVerbatim} |
| 604 |
Size = 154 * 2 * (100,000 * 4 + 10,000 * 26) |
| 605 |
\end{bVerbatim} |
| 606 |
or |
| 607 |
\begin{bVerbatim} |
| 608 |
Size = 308 * (400,000 + 260,000) |
| 609 |
\end{bVerbatim} |
| 610 |
or |
| 611 |
\begin{bVerbatim} |
| 612 |
Size = 203,280,000 bytes |
| 613 |
\end{bVerbatim} |
| 614 |
|
| 615 |
So for the above two systems, we should expect to have a database size of |
| 616 |
approximately 200 Megabytes. Of course, this will vary according to how many |
| 617 |
files are actually backed up. |
| 618 |
|
| 619 |
Below are some statistics for a \mysql{} database containing Job records for five |
| 620 |
Clients beginning September 2001 through May 2002 (8.5 months) and File |
| 621 |
records for the last 80 days. (Older File records have been pruned). For these |
| 622 |
systems, only the user files and system files that change are backed up. The |
| 623 |
core part of the system is assumed to be easily reloaded from the Red Hat rpms. |
| 624 |
|
| 625 |
|
| 626 |
In the list below, the files (corresponding to \bacula{} tables) with the |
| 627 |
extension \bfilename{.MYD} contain the data records whereas files with the extension \bfilename{.MYI} |
| 628 |
contain indexes. |
| 629 |
|
| 630 |
You will note that the File records (containing the file attributes) make up |
| 631 |
the large bulk of the number of records as well as the space used (459 Mega |
| 632 |
Bytes including the indexes). As a consequence, the most important Retention |
| 633 |
period will be the \textbf{File Retention} period. A quick calculation shows that |
| 634 |
for each File that is saved, the database grows by approximately 150 bytes. |
| 635 |
|
| 636 |
\begin{bVerbatim} |
| 637 |
Size in |
| 638 |
Bytes Records File |
| 639 |
============ ========= =========== |
| 640 |
168 5 Client.MYD |
| 641 |
3,072 Client.MYI |
| 642 |
344,394,684 3,080,191 File.MYD |
| 643 |
115,280,896 File.MYI |
| 644 |
2,590,316 106,902 Filename.MYD |
| 645 |
3,026,944 Filename.MYI |
| 646 |
184 4 FileSet.MYD |
| 647 |
2,048 FileSet.MYI |
| 648 |
49,062 1,326 JobMedia.MYD |
| 649 |
30,720 JobMedia.MYI |
| 650 |
141,752 1,378 Job.MYD |
| 651 |
13,312 Job.MYI |
| 652 |
1,004 11 Media.MYD |
| 653 |
3,072 Media.MYI |
| 654 |
1,299,512 22,233 Path.MYD |
| 655 |
581,632 Path.MYI |
| 656 |
36 1 Pool.MYD |
| 657 |
3,072 Pool.MYI |
| 658 |
5 1 Version.MYD |
| 659 |
1,024 Version.MYI |
| 660 |
\end{bVerbatim} |
| 661 |
|
| 662 |
This database has a total size of approximately 450 Megabytes. |