/
catmaintenance.tex
1498 lines (1237 loc) · 60.4 KB
/
catmaintenance.tex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
%%
%%
\chapter{Catalog Maintenance}
\label{CatMaintenanceChapter}
\index[general]{Maintenance!Catalog}
\index[general]{Catalog Maintenance}
\section{Catalog Database}
Bareos stores its catalog in a database.
Different database backends are offered:
\begin{itemize}
\item PostgreSQL (recommended)
\item MariaDB/MySQL (see \nameref{sec:MysqlSupport})
\item Sqlite (only for testing)
\end{itemize}
What database will be used, can be configured in the \bareosDir configuration, see the \nameref{DirectorResourceCatalog}.
The database often runs on the same server as the \bareosDir.
However, it is also possible to run it on a different system.
This might require some more manual configuration, a PostgreSQL example can be found in \nameref{catalog-maintenance-remote-psql}.
\subsection{dbconfig-common (Debian)}
\index[general]{Platform!Debian!dbconfig-common}
\index[general]{Platform!Ubuntu!dbconfig-common}
\label{sec:dbconfig}
Since Bareos \sinceVersion{dir}{dbconfig-common (Debian)}{14.2.0} the Debian (and Ubuntu) based packages support the \package{dbconfig-common} mechanism to create and update the Bareos database, according to the user choices.
The first choice is, if \package{dbconfig-common} should be used at all.
If you decide against it, the database must be configured manually, see \nameref{CatMaintenanceManualConfiguration}.
If you decided to use \package{dbconfig-common}, the next question will only be asked, if more than one
Bareos database backend (\package{bareos-database-*}) is installed.
If this is the case, select the database backend you want to use.
\begin{center}
\includegraphics[width=0.45\textwidth]{\idir dbconfig-1-enable}
\includegraphics[width=0.45\textwidth]{\idir dbconfig-2-select-database-type}
\end{center}
Depending on the selected database backend,
more questions about how to access the database will be asked.
Often, the default values are suitable.
The \package{dbconfig-common} configuration (and credentials) is done by the \package{bareos-database-common} package.
Settings are stored in the file \file{/etc/dbconfig-common/bareos-database-common.conf}.
The Bareos database backend will get automatically configured in \file{/etc/bareos/bareos-dir.d/catalog/MyCatalog.conf}.
If the Server is not running locally you need to specify \linkResourceDirective{Dir}{Catalog}{DB Address} in the catalog ressource.
A later reconfiguration might require manual adapt changes.
\warning{When using the PostgreSQL backend and updating to Bareos $<$ 14.2.3, it is necessary to manually grant database permissions (\command{grant_bareos_privileges}), normally by}
\begin{commands}{}
<command> </command><parameter>su - postgres -c /usr/lib/bareos/scripts/grant_bareos_privileges</parameter>
\end{commands}
For details see chapter \nameref{CatMaintenanceManualConfiguration}.
\subsection{Manual Configuration}
\label{CatMaintenanceManualConfiguration}
Bareos comes with a number of scripts to prepare and update the databases. All these scripts are located in the Bareos script directory, normally at \scriptPathUnix.
\begin{center}
\begin{tabular}{| l | l | l |}
\hline
\textbf{Script} & \textbf{Stage} & \textbf{Description} \\
\hline
\hline
\file{create_bareos_database} & installation & create Bareos database \\
\file{make_bareos_tables} & installation & create Bareos tables \\
\file{grant_bareos_privileges} & installation & grant database access privileges \\
\hline
\file{update_bareos_tables} [-f] & update & update the database schema \\
\hline
\file{drop_bareos_tables} & deinstallation & remove Bareos database tables \\
\file{drop_bareos_database} & deinstallation & remove Bareos database \\
\hline
\file{make_catalog_backup.pl} & backup & backup the Bareos database, default on Linux \\
\file{make_catalog_backup} & backup & backup the Bareos database for systems without Perl \\
\file{delete_catalog_backup} & backup helper & remove the temporary Bareos database backup file \\
\hline
\end{tabular}
\end{center}
The database preparation scripts have following configuration options:
% \begin{center}
% \begin{tabular}{| l | l | l |}
% \hline
% \textbf{Parameter} & \textbf{Values} & \textbf{Default} \\
% \hline
% \hline
% \parameter{db_type} & parameter, dbdriver from \configFileDirUnix, installed database backends & \\
% \parameter{db_name} & environment variable \variable{db_name}, dbdriver from \configFileDirUnix & bareos \\
% \parameter{db_user} & environment variable \variable{db_user} & bareos \\
% \parameter{db_password} & environment variable \variable{db_password} & \textit{none} \\
% \hline
% \end{tabular}
% \end{center}
\begin{description}
\item[db\_type] \hfill
\begin{itemize}
\item command line parameter \$1
\item \linkResourceDirective{Dir}{Catalog}{DB Driver} from the configuration
\item installed database backends
\item fallback: postgresql
\end{itemize}
\item[db\_name] \hfill
\begin{itemize}
\item environment variable \variable{db_name}
\item \linkResourceDirective{Dir}{Catalog}{DB Name} from the configuration
\item default: bareos
\end{itemize}
\item[db\_user] \hfill
\begin{itemize}
\item environment variable \variable{db_user}
\item \linkResourceDirective{Dir}{Catalog}{DB User} from the configuration
\item default: bareos
\end{itemize}
\item[db\_password] \hfill
\begin{itemize}
\item environment variable \variable{db_password}
\item \linkResourceDirective{Dir}{Catalog}{DB Password} from the configuration
\item default: \textit{none}
\end{itemize}
\end{description}
Reading the settings from the configuration require read permission for the current user.
The normal PostgreSQL administrator user (\user{postgres}) don't have these permissions.
So if you plan to use non-default database settings, you might add the user \user{postgres} to the group \group{bareos}.
The database preparation scripts need to have password-less administrator access to the database.
Depending on the distribution you're using, this require additional configuration.
See the following section about howto achieve this for the different database systems.
To view and test the currently configured settings, use following commands:
\begin{commands}{Show current database configuration}
<command> </command><parameter>/usr/sbin/bareos-dbcheck -B</parameter>
catalog=MyCatalog
db_name=bareos
db_driver=mysql
db_user=bareos
db_password=YourPassword
db_address=
db_port=0
db_socket=
db_type=MySQL
working_dir=/var/lib/bareos
\end{commands}
\begin{commands}{Test the database connection. Example: wrong password}
<command> </command><parameter>/usr/sbin/bareos-dir -t -f -d 500</parameter>
[...]
bareos-dir: mysql.c:204-0 Error 1045 (28000): Access denied for user 'bareos'@'localhost' (using password: YES)
bareos-dir: dird.c:1114-0 Could not open Catalog "MyCatalog", database "bareos".
bareos-dir: dird.c:1119-0 mysql.c:200 Unable to connect to MySQL server.
Database=bareos User=bareos
MySQL connect failed either server not running or your authorization is incorrect.
bareos-dir: mysql.c:239-0 closedb ref=0 connected=0 db=0
25-Apr 16:25 bareos-dir ERROR TERMINATION
Please correct the configuration in /etc/bareos/bareos-dir.d/*/*.conf
\end{commands}
\subsubsection{PostgreSQL}
On most distributions, PostgreSQL uses ident to allow access to the database system.
The database administrator account is the Unix user \user{postgres}.
Normally, this user can access the database without password, as the ident mechanism is used to identify the user.
If this works on your system can be verified by
\begin{commands}{Access the local PostgreSQL database}
su - postgres
psql
\end{commands}
If your database is configured to require a password, this must be definied in the file
\elink{\file{~/.pgpass}}{http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html}
in the following syntax: \parameter{HOST:PORT:DATABASE:USER:PASSWORD}, e.g.
\begin{config}{PostgreSQL access credentials}
localhost:*:bareos:bareos:secret
\end{config}
The permission of this file must be 0600 (\command{chmod 0600 ~/.pgpass}).
Again, verify that you have specified the correct settings by calling the \command{psql} command.
If this connects you to the database, your credentials are good.
Exit the PostgreSQL client and run the Bareos database preparation scripts:
\begin{commands}{Setup Bareos catalog database}
su - postgres
/usr/lib/bareos/scripts/create_bareos_database
/usr/lib/bareos/scripts/make_bareos_tables
/usr/lib/bareos/scripts/grant_bareos_privileges
\end{commands}
% \warning{The user \user{postgres} normally can't read the Bareos director config file \configFileDirUnix. If you're using non-standard settings (like different database name, ...), make sure, it got read access to it.}
The encoding of the bareos database must be \parameter{SQL_ASCII}.
The command \command{create_bareos_database} automatically creates the database with this encoding.
This can be verified by the command \command{psql -l}, which shows information about existing databases:
\begin{commands}{List existing databases}
<command>psql</command><parameter> -l</parameter>
List of databases
Name | Owner | Encoding
-----------+----------+-----------
bareos | postgres | SQL_ASCII
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(4 rows)
\end{commands}
The owner of the database may vary. The Bareos database maintance scripts don't change the default owner of the Bareos database, so it stays at the PostgreSQL administration user. The \command{grant_bareos_privileges} script grant the required permissions to the Bareos database user. In contrast, when installing (not updating) using \ilink{dbconfig}{sec:dbconfig}, the database owner will be identical with the Bareos database user.
% \warning{When updating from Bareos $<$ 14.2 to Bareos $<$ 14.2.3, it is necessary to manually grant database permissions, using the \command{grant_bareos_privileges} command.}
% When updating from Bareos $<$ 14.2 to a version using dbconfig (Debian based distribution),
% you should change the database owner to the Bareos database user:
% \begin{commands}{Change the database owner to the Bareos database user (Debian)}
% root@linux:~# <input>su - postgres</input>
% postgres@linux:~# <input>psql</input>
% psql (9.1.14)
% Type "help" for help.
%
% postgres=> <input>\l</input>
% List of databases
% Name | Owner | Encoding | Collate | Ctype | Access privileges
% -----------+----------+-----------+---------+-------+-----------------------
% bareos | postgres | SQL_ASCII | C | C |
% postgres | postgres | SQL_ASCII | C | C |
% template0 | postgres | SQL_ASCII | C | C | =c/postgres +
% | | | | | postgres=CTc/postgres
% template1 | postgres | SQL_ASCII | C | C | =c/postgres +
% | | | | | postgres=CTc/postgres
% (4 rows)
%
% postgres=> <input>ALTER DATABASE bareos OWNER TO bareos;</input>
% ALTER DATABASE
% postgres=> <input>\l</input>
% List of databases
% Name | Owner | Encoding | Collate | Ctype | Access privileges
% -----------+----------+-----------+---------+-------+-----------------------
% bareos | bareos | SQL_ASCII | C | C |
% postgres | postgres | SQL_ASCII | C | C |
% template0 | postgres | SQL_ASCII | C | C | =c/postgres +
% | | | | | postgres=CTc/postgres
% template1 | postgres | SQL_ASCII | C | C | =c/postgres +
% | | | | | postgres=CTc/postgres
% (4 rows)
% \end{commands}
By default, using PostgreSQL ident, a Unix user can access a database of the same name.
Therefore the user \user{bareos} can access the database \path|bareos|.
\begin{commands}{Verify Bareos database on PostgreSQL as Unix user bareos (bareos-13.2.3)}
root@linux:~# <input>su - bareos -s /bin/sh</input>
bareos@linux:~# <input>psql</input>
Welcome to psql 8.3.23, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
bareos=> <input>\dt</input>
List of relations
Schema | Name | Type | Owner
--------+------------------------+-------+----------
public | basefiles | table | postgres
public | cdimages | table | postgres
public | client | table | postgres
public | counters | table | postgres
public | device | table | postgres
public | devicestats | table | postgres
public | file | table | postgres
public | filename | table | postgres
public | fileset | table | postgres
public | job | table | postgres
public | jobhisto | table | postgres
public | jobmedia | table | postgres
public | jobstats | table | postgres
public | location | table | postgres
public | locationlog | table | postgres
public | log | table | postgres
public | media | table | postgres
public | mediatype | table | postgres
public | ndmpjobenvironment | table | postgres
public | ndmplevelmap | table | postgres
public | path | table | postgres
public | pathhierarchy | table | postgres
public | pathvisibility | table | postgres
public | pool | table | postgres
public | quota | table | postgres
public | restoreobject | table | postgres
public | status | table | postgres
public | storage | table | postgres
public | unsavedfiles | table | postgres
public | version | table | postgres
(30 rows)
bareos=> <input>select * from Version;</input>
versionid
-----------
2002
(1 row)
bareos=> <input>\du</input>
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
---------------+-----------+-------------+-----------+-------------+-----------
bareos | no | no | no | no limit | {}
postgres | yes | yes | yes | no limit | {}
(2 rows)
bareos=> <input>\dp</input>
Access privileges for database "bareos"
Schema | Name | Type | Access privileges
--------+-----------------------------------+----------+--------------------------------------
public | basefiles | table | {root=arwdxt/root,bareos=arwdxt/root}
public | basefiles_baseid_seq | sequence | {root=rwU/root,bareos=rw/root}
...
bareos=>
\end{commands}
\subsubsection{Remote PostgreSQL Database}
\label{catalog-maintenance-remote-psql}
When configuring bareos with a remote database, your first step is to check the connection from the \bareosDir host into the database.
A functional connection can be verified by
\begin{commands}{Access the remote PostgreSQL database}
su - postgres
psql --host bareos-database.example.com
\end{commands}
With a correct configuration you can access the database, if it fails you need to correct the PostgreSQL servers configuration files.
One way to manually create the database would be calling the bareos database preparation scripts with the \parameter{--host} option, explained later.
How ever, it is advised to use the \package{dbconfig-common}.
Both methods require you to add the database hostname/address as \linkResourceDirective{Dir}{Catalog}{DB Address}.
If you're using \package{dbconfig-common} you should choose \parameter{New Host}, enter the hostname or the local address followed by the password.
As \package{dbconfig-common} uses the \parameter{ident} authentication by default the first try to connect will fail. Don't be bothered by that. Choose \parameter{Retry} when prompted. From there, read carefully and configure the database to your needs.
The authentication should be set to password, as the ident method will not work with a remote server.
Set the user and administrator according to your PostgreSQL servers settings.
Set the PostgreSQL server IP as \linkResourceDirective{Dir}{Catalog}{DB Address} in \nameref{DirectorResourceCatalog}.
You can also customize other parameters or use the defaults. A quick check should display your recent changes:
\begin{commands}{Show current database configuration}
<command> </command><parameter>/usr/sbin/bareos-dbcheck -B</parameter>
catalog=MyCatalog
db_name=bareos
db_driver=postgresql
db_user=bareos
db_password=secret
db_address=bareos-database.example.com
db_port=0
db_socket=
db_type=PostgreSQL
working_dir=/var/lib/bareos
\end{commands}
If \package{dbconfig-common} did not succeed or you choosed not to use it, run the Bareos database preparation scripts with:
\begin{commands}{Setup Bareos catalog database}
su - postgres
/usr/lib/bareos/scripts/create_bareos_database --host=bareos-database.example.com
/usr/lib/bareos/scripts/make_bareos_tables --host=bareos-database.example.com
/usr/lib/bareos/scripts/grant_bareos_privileges --host=bareos-database.example.com
\end{commands}
\subsubsection{MySQL}
\label{catalog-maintenance-mysql}
MySQL user authentication is username, password and host-based.
The database administrator is the user \user{root}.
On some distributions access to the MySQL database is allowed password-less as database user \user{root},
on other distributions, a password is required.
On productive systems you normally want to have password secured access.
The bareos database preparation scripts require password-less access to the database.
To guarantee this, create a MySQL credentials file \elink{\file{~/.my.cnf}}{http://dev.mysql.com/doc/refman/4.1/en/password-security.html} with the credentials of the database administrator:
\begin{config}{MySQL credentials file .my.cnf}
[client]
host=localhost
user=root
password=<input>YourPasswordForAccessingMysqlAsRoot</input>
\end{config}
Alternatively you can specifiy your database password by adding it to the file \file{/etc/my.cnf}.
Verify that you have specified the correct settings by calling the \command{mysql} command.
If this connects you to the database, your credentials are good.
Exit the MySQL client.
For the Bareos database connection, you should specify a database password.
Otherwise the Bareos database user gets the permission to connect without password.
This is not recommended.
Choose a database password and add it into the Bareos Director configuration file \configFileDirUnix:
\begin{bconfig}{Bareos catalog configuration}
...
#
# Generic catalog service
#
Catalog {
Name = MyCatalog
dbdriver = "mysql"
dbname = "bareos"
dbuser = "bareos"
dbpassword = "YourSecretPassword"
}
...
\end{bconfig}
After this, run the Bareos database preparation scripts.
For Bareos $<=$ 13.2.2, the database password must be specified as environment variable \variable{db_password}.
From \sinceVersion{dir}{MySQL password from configuration file}{13.2.3}
the database password is read from the configuration, if no environment variable is given.
\begin{commands}{Setup Bareos catalog database}
export db_password=<input>YourSecretPassword</input>
/usr/lib/bareos/scripts/create_bareos_database
/usr/lib/bareos/scripts/make_bareos_tables
/usr/lib/bareos/scripts/grant_bareos_privileges
\end{commands}
After this, you can use the \command{mysql} command to verify that your database setup is okay
and works with your the Bareos database user.
The result should look similar as this (here Bareos 13.2 is used on SLES11):
\begin{commands}{Verify Bareos database on MySQL}
root@linux:~# <input>mysql --user=bareos --password=YourSecretPassword bareos</input>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 162
Server version: 5.5.32 SUSE MySQL package
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> <input>show tables;</input>
+--------------------+
| Tables_in_bareos |
+--------------------+
| BaseFiles |
| CDImages |
| Client |
| Counters |
| Device |
| DeviceStats |
| File |
| FileSet |
| Filename |
| Job |
| JobHisto |
| JobMedia |
| JobStats |
| Location |
| LocationLog |
| Log |
| Media |
| MediaType |
| NDMPJobEnvironment |
| NDMPLevelMap |
| Path |
| PathHierarchy |
| PathVisibility |
| Pool |
| Quota |
| RestoreObject |
| Status |
| Storage |
| UnsavedFiles |
| Version |
+--------------------+
30 rows in set (0.00 sec)
mysql> <input>describe Job;</input>
+-----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+----------------+
| JobId | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Job | tinyblob | NO | | NULL | |
| Name | tinyblob | NO | MUL | NULL | |
| Type | binary(1) | NO | | NULL | |
| Level | binary(1) | NO | | NULL | |
| ClientId | int(11) | YES | | 0 | |
| JobStatus | binary(1) | NO | | NULL | |
| SchedTime | datetime | YES | | NULL | |
| StartTime | datetime | YES | | NULL | |
| EndTime | datetime | YES | | NULL | |
| RealEndTime | datetime | YES | | NULL | |
| JobTDate | bigint(20) unsigned | YES | | 0 | |
| VolSessionId | int(10) unsigned | YES | | 0 | |
| VolSessionTime | int(10) unsigned | YES | | 0 | |
| JobFiles | int(10) unsigned | YES | | 0 | |
| JobBytes | bigint(20) unsigned | YES | | 0 | |
| ReadBytes | bigint(20) unsigned | YES | | 0 | |
| JobErrors | int(10) unsigned | YES | | 0 | |
| JobMissingFiles | int(10) unsigned | YES | | 0 | |
| PoolId | int(10) unsigned | YES | | 0 | |
| FileSetId | int(10) unsigned | YES | | 0 | |
| PriorJobId | int(10) unsigned | YES | | 0 | |
| PurgedFiles | tinyint(4) | YES | | 0 | |
| HasBase | tinyint(4) | YES | | 0 | |
| HasCache | tinyint(4) | YES | | 0 | |
| Reviewed | tinyint(4) | YES | | 0 | |
| Comment | blob | YES | | NULL | |
+-----------------+---------------------+------+-----+---------+----------------+
27 rows in set (0,00 sec)
mysql> <input>select * from Version;</input>
+-----------+
| VersionId |
+-----------+
| 2002 |
+-----------+
1 row in set (0.00 sec)
mysql> <input>exit</input>
Bye
\end{commands}
\paragraph{Modify database credentials}
If you want to change the Bareos database credentials, do the following:
\begin{itemize}
\item stop the Bareos director
\item modify the configuration
\item rerun the grant script \command{grant_bareos_privileges} (or modify database user directly)
\item start the Bareos director
\end{itemize}
Modify the configuration, set a new password:
\begin{bareosConfigResource}{bareos-dir}{Catalog}{MyCatalog}
Catalog {
Name = MyCatalog
dbdriver = "mysql"
dbname = "bareos"
dbuser = "bareos"
dbpassword = "MyNewSecretPassword"
}
\end{bareosConfigResource}
Rerun the Bareos grant script \command{grant_bareos_privileges} ...
\begin{commands}{Modify database privileges}
export db_password=<input>MyNewSecretPassword</input>
/usr/lib/bareos/scripts/grant_bareos_privileges
\end{commands}
\hide{
% table and commands have changed in MySQL 5.7.6.
% As things now get more complicated (different command for different version)
% we skip this topic here.
... or modify the database users directly:
\begin{commands}{Show Bareos database users}
<command>mysql</command>
mysql> <input>SELECT user,host,password FROM mysql.user WHERE user='bareos';</input>
+--------+-----------+-------------------------------------------+
| user | host | password |
+--------+-----------+-------------------------------------------+
| bareos | 127.0.0.1 | *CD8C42695AC221807E2BA599FC392C650155C16C |
| bareos | localhost | *CD8C42695AC221807E2BA599FC392C650155C16C |
| bareos | ::1 | *CD8C42695AC221807E2BA599FC392C650155C16C |
+--------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> <input>UPDATE mysql.user SET Password=PASSWORD('MyNewSecretPassword') where User='bareos';</input>
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> <input>FLUSH PRIVILEGES;</input>
Query OK, 0 rows affected (0.00 sec)
mysql> <input>SELECT user,host,password FROM mysql.user WHERE user='bareos';</input>
+--------+-----------+-------------------------------------------+
| user | host | password |
+--------+-----------+-------------------------------------------+
| bareos | 127.0.0.1 | *2119D34B0C0F7452E952EE3A73A7CAA30C1B1852 |
| bareos | localhost | *2119D34B0C0F7452E952EE3A73A7CAA30C1B1852 |
| bareos | ::1 | *2119D34B0C0F7452E952EE3A73A7CAA30C1B1852 |
+--------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql>
\end{commands}
}
\subsubsection{Sqlite}
There are different versions of Sqlite available. When we use the term Sqlite, we will always refer to Sqlite3.
Sqlite is a file based database. Access via network connection is not supported.
Because its setup is easy, it is a good database for testing.
However please don't use it in a production environment.
Sqlite stores a database in a single file.
Bareos creates this file at \file{/var/lib/bareos/bareos.db}.
Sqlite does not offer access permissions. The only permissions that do apply are the Unix file permissions.
The database is accessable by following command:
\begin{commands}{Verify Bareos database on Sqlite3 (bareos-13.2.3)}
<command>sqlite3</command><input> /var/lib/bareos/bareos.db</input>
SQLite version 3.7.6.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> <input>.tables</input>
BaseFiles Filename Media Pool
CDImages Job MediaType Quota
Client JobHisto NDMPJobEnvironment RestoreObject
Counters JobMedia NDMPLevelMap Status
Device JobStats NextId Storage
DeviceStats Location Path UnsavedFiles
File LocationLog PathHierarchy Version
FileSet Log PathVisibility
sqlite> <input>select * from Version;</input>
2002
sqlite>
\end{commands}
\section{Retention Periods}
\subsection{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 {\bf 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:
\footnotesize
\begin{verbatim}
Size = 154 * No. Systems * (100,000 * 4 + 10,000 * 26)
\end{verbatim}
\normalsize
where we have assumed four weeks in a month and 26 incremental backups per month.
This would give the following:
\footnotesize
\begin{verbatim}
Size = 154 * 2 * (100,000 * 4 + 10,000 * 26) = 203,280,000 bytes
\end{verbatim}
\normalsize
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.
You will note that the File table (containing the file attributes) make up
the large bulk of the number of records as well as the space used.
As a consequence, the most important Retention
period will be the {\bf File Retention} period.
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.
\subsection{Setting Retention Periods}
\label{Retention}
\index[general]{Setting Retention Periods}
\index[general]{Periods!Setting Retention}
Bareos uses three Retention periods: the {\bf File Retention} period,
the {\bf Job Retention} period, and the {\bf 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 {\bf File Retention} and the {\bf Job Retention} are specified in each
Client resource as is shown below. The {\bf Volume Retention} period is
specified in the Pool resource, and the details are given in the next chapter
of this manual.
\begin{description}
\item [File Retention = {\textless}time-period-specification{\textgreater}]
\index[general]{File Retention}
\index[general]{Retention!File}
The File Retention record defines the length of time that Bareos will keep
File records in the Catalog database. When this time period expires, and if
{\bf AutoPrune} is set to {\bf yes}, Bareos 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
Bareos 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, 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
\ilink{Configuration chapter}{Time} of this manual for additional details
of modifier specification.
The default File retention period is 60 days.
\item [Job Retention = {\textless}time-period-specification{\textgreater}]
\index[general]{Job!Retention}
\index[general]{Retention!Job}
The Job Retention record defines the length of time that {\bf Bareos}
will keep Job records in the Catalog database. When this time period
expires, and if {\bf AutoPrune} is set to {\bf yes} Bareos 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.
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 \ilink{Configuration chapter}{Time} of this manual for additional details of
modifier specification.
The default Job Retention period is 180 days.
\item [\linkResourceDirective{Dir}{Client}{Auto Prune}]
\index[general]{AutoPrune}
\index[general]{Job!Retention!AutoPrune}
If set to {\bf yes},
Bareos 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 {\bf no}, your Catalog will grow each
time you run a Job.
\end{description}
\subsubsection{Job Statistics}
\label{sec:JobStatistics}
\index[general]{Statistics}
\index[general]{Job!Statistics}
Bareos catalog contains lot of information about your IT infrastructure, how
many files, their size, the number of video or music files etc. Using Bareos
catalog during the day to get them permit to save resources on your servers.
In this chapter, you will find tips and information to measure Bareos
efficiency and report statistics.
If you want to have statistics on your backups to
provide some Service Level Agreement indicators, you could use a few
SQL queries on the Job table to report how many:
\begin{itemize}
\item jobs have run
\item jobs have been successful
\item files have been backed up
\item ...
\end{itemize}
However, these statistics are accurate only if your job retention is greater
than your statistics period. Ie, if jobs are purged from the catalog, you won't
be able to use them.
Now, you can use the \bcommand{update}{stats [days=num]} console command to fill
the JobHistory table with new Job records. If you want to be sure to take in
account only good jobs, ie if one of your important job has failed but
you have fixed the problem and restarted it on time, you probably want to
delete the first bad job record and keep only the successful one. For
that simply let your staff do the job, and update JobHistory table after two or
three days depending on your organization using the \parameter{[days=num]} option.
These statistics records aren't used for restoring, but mainly for
capacity planning, billings, etc.
The \linkResourceDirective{Dir}{Director}{Statistics Retention} defines
the length of time that Bareos will keep statistics job records in the Catalog
database after the Job End time. This information is stored in the \texttt{JobHistory} table.
When this time
period expires, and if user runs \bcommand{prune}{stats} command, Bareos will
prune (remove) Job records that are older than the specified period.
You can use the following Job resource in your nightly \job{BackupCatalog}
job to maintain statistics.
\begin{bareosConfigResource}{bareos-dir}{Job}{BackupCatalog}
Job {
Name = BackupCatalog
...
RunScript {
Console = "update stats days=3"
Console = "prune stats yes"
RunsWhen = After
RunsOnClient = no
}
}
\end{bareosConfigResource}
\section{PostgreSQL}
\index[general]{Database!PostgreSQL}
\index[general]{PostgreSQL}
\subsection{Compacting Your PostgreSQL Database}
\index[general]{Database!PostgreSQL!Compacting}
\label{CompactingPostgres}
Over time, as noted above, your database will tend to grow until Bareos starts
deleting old expired records based on retention periods. After that starts,
it is expected that the database size remains constant, provided that the amount
of clients and files being backed up is constant.
Note that PostgreSQL uses multiversion concurrency control (MVCC), so that
an UPDATE or DELETE of a row does not immediately remove the old version of the
row. Space occupied by outdated or deleted row versions is only reclaimed for
reuse by new rows when running \textbf{VACUUM}. Such outdated or deleted row versions
are also referred to as \emph{dead tuples}.
Since PostgreSQL Version 8.3, autovacuum is enabled by default, so that setting
up a cron job to run VACUUM is not necesary in most of the cases. Note that
there are two variants of VACUUM: standard VACUUM and VACUUM FULL. Standard
VACUUM only marks old row versions for reuse, it does not free any allocated
disk space to the operating system. Only VACUUM FULL can free up disk space,
but it requires exclusive table locks so that it can not be used in parallel
with production database operations and temporarily requires up to as much
additional disk space that the table being processed occupies.
All database programs have some means of writing the database out in 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 PostgreSQL.
For a PostgreSQL database, you could write the Bareos database as an
ASCII file (\file{bareos.sql}) then reload it by doing the following:
\begin{commands}{}
pg_dump -c bareos > bareos.sql
cat bareos.sql | psql bareos
rm -f bareos.sql
\end{commands}
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 \command{cd} to the location of
the Bareos database (typically \directory{/var/lib/pgsql/data} or possible
\directory{/usr/local/pgsql/data}) and check the size.
Except from special cases PostgreSQL does not
need to be dumped/restored to keep the database efficient. A normal
process of vacuuming will prevent the database from getting too
large. If you want to fine-tweak the database storage, commands such
as VACUUM, VACUUM FULL, REINDEX, and CLUSTER exist specifically to keep you
from having to do a dump/restore.
More details on this subject can be found in the PostgreSQL documentation.
The page \url{http://www.postgresql.org/docs/} contains links to the documentation
for all PostgreSQL versions. The section \emph{Routine Vacuuming} explains
how VACUUM works and why it is required, see
\url{http://www.postgresql.org/docs/current/static/routine-vacuuming.html}
for the current PostgreSQL version.
\subsubsection{What To Do When The Database Keeps Growing}
\label{PostgresSize}
Especially when a high number of files are beeing backed up or when working with
high retention periods, it is probable that autovacuuming will not work.
When starting to use Bareos with an empty Database, it is normal that the file
table and other tables grow, but the growth rate should drop as soon as jobs are deleted by
retention or pruning. The file table is usually the largest table in Bareos.
The reason for autovacuuming not beeing triggered is then probably the default
setting of \texttt{autovacuum\_vacuum\_scale\_factor = 0.2}, the current value can
be shown with the following query or looked up in \texttt{postgresql.conf}:
\begin{commands}{SQL statement to show the autovacuum\_vacuum\_scale\_factor parameter}
bareos=# show autovacuum_vacuum_scale_factor;
autovacuum_vacuum_scale_factor
--------------------------------
0.2
(1 row)
\end{commands}
In essence, this means that a VACUUM is only triggered when 20\% of table size
are obsolete. Consequently, the larger the table is, the less frequently VACUUM
will be triggered by autovacuum. This make sense because vacuuming has a
performance impact. While it is possible to override the autovacuum parameters
on a table-by-table basis, it can then still be triggered at any time.
To learn more details about autovacuum see
\url{http://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM}
The following example shows how to configure running VACUUM on the file table by
using an admin-job in Bareos. The job will be scheduled to run at a time that should
not run in parallel with normal backup jobs, here by scheduling it to run after
the BackupCatalog job.
First step is to check the amount of dead tuples and if autovacuum triggers VACUUM:
\begin{commands}{Check dead tuples and vacuuming on PostgreSQL}
bareos=# SELECT relname, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE n_dead_tup > 0 ORDER BY n_dead_tup DESC;
-[ RECORD 1 ]----+------------------------------
relname | file
n_dead_tup | 2955116
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
relname | log
n_dead_tup | 111298
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
-[ RECORD 3 ]----+------------------------------
relname | job
n_dead_tup | 1785
last_vacuum |
last_autovacuum | 2015-01-08 01:13:20.70894+01
last_analyze |
last_autoanalyze | 2014-12-27 18:00:58.639319+01
...
\end{commands}
In the above example, the file table has a high number of dead tuples and it
has not been vacuumed. Same for the log table, but the dead tuple count is not
very high. On the job table autovacuum has been triggered.
Note that the statistics views in PostgreSQL are not persistent, their values
are reset on restart of the PostgreSQL service.
To setup a scheduled admin job for vacuuming the file table, the following must be done:
\begin{enumerate}
\item Create a file with the SQL statements for example\\
\texttt{/usr/local/lib/bareos/scripts/postgresql\_file\_table\_maintenance.sql}\\
with the following content:
\begin{commands}{SQL Script for vacuuming the file table on PostgreSQL}
\t \x
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE relname='file';
VACUUM VERBOSE ANALYZE file;
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE relname='file';
\t \x
SELECT table_name,
pg_size_pretty(pg_total_relation_size(table_name)) AS total_sz,
pg_size_pretty(pg_total_relation_size(table_name) - pg_relation_size(table_name)) AS idx_sz
FROM ( SELECT ('"' || relname || '"' ) AS table_name
FROM pg_stat_user_tables WHERE relname != 'batch' ) AS all_tables
ORDER BY pg_total_relation_size(table_name) DESC LIMIT 5;