1Howtos 2====== 3 4.. _dummydevice: 5 6Use a dummy device to test the backup 7------------------------------------- 8 9 10 11.. _TestUsingFifoDevice: 12 13 If your are testing your configuration, but don’t want to store the backup data, it is possible to use a dummy FIFO device to test your configuration, see :ref:`Stored configuration <SetupFifo>`. 14 15Obviously, it can not be used to do a restore. 16 17.. code-block:: bareosconfig 18 :caption: FIFO Storage Device Configuration 19 20 Device { 21 Name = NULL 22 Media Type = NULL 23 Device Type = Fifo 24 Archive Device = /dev/null 25 LabelMedia = yes 26 Random Access = no 27 AutomaticMount = no 28 RemovableMedia = no 29 MaximumOpenWait = 60 30 AlwaysOpen = no 31 } 32 33.. _BackupOtherDBs: 34 35Backup Of Third Party Databases 36------------------------------- 37 38:index:`\ <single: Backup; of Third Party Databases>` 39:index:`\ <single: Database; Backup Of Third Party>` 40 41If you are running a database in production mode on your machine, Bareos will happily backup the files, but if the database is in use while Bareos is reading it, you may back it up in an unstable state. 42 43The 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 ASCII format. 44 45 46.. _MSSQL: 47 48Backup of MSSQL Databases with Bareos Plugin 49~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 50 51.. index:: 52 single: MSSQL Backup 53 single: Database; MSSQL 54 single: Plugin; MSSQL backup 55 56:sinceVersion:`13.2.0: MSSQL` 57 58Preparation 59^^^^^^^^^^^ 60 61If you like to use the MSSQL-Plugin to backing up your Databases you need to consider some things: 62 63- | Database Mode 64 | The database need to run in Full Recovery Mode. Otherwise you are not able to use differential and incremental backups or to use point in time recovery. 65 66 .. warning:: 67 68 If you set the databases into the mentionend mode you have to consider some maintance facts. The database doesn't shrink or delete the logs unanttended, so you have to shrink them manual once a week and you have to truncate the logs once in a month. 69 70- | Security and Access 71 | For connections you can use a SQL-User or a integrated systemaccount (Windows NT user). Both connection types are supported. 72 73 - | Standard Security 74 | You have to provide user credentials within your options which do belong to user with the sufficent right performing restores and backups from the database. This way stands for an extra backup/restore user. 75 76 - | Trusted Security 77 | You use a systemaccount which have the sufficent rights to performing backups and restores on a database. This systemaccount have to be same account like the bareos-filedeamon runs on. 78 79- Permissions and Roles 80 81 - | Server-Role 82 | The user should be in the groups sysadmin and dbcreator. 83 84 - | Database permissions 85 | The user have to be a backupoperator and dbowner of the database which you like to backup. 86 87There is no difference for the rights and roles between using a systemaccount (trusted security method) or a extra backup user (standard security method). Please keep in mind if you use the trusted security method you have to use the same system account like the bareos-filedeamon runs on. 88 89.. _MssqlPluginInstallation: 90 91MSSQL Plugin Installation 92^^^^^^^^^^^^^^^^^^^^^^^^^ 93 94For Bareos < 14.2, install the Bareos MSSQL plugin onto the MSSQL server you want to backup. Bareos >= 14.2 also allows to backup remote MSSQL servers (option :strong:`serveraddress`). 95 96Bareos Windows-Installer 97'''''''''''''''''''''''' 98 99Install the Bareos filedaemon including the component "Bareos FileDameon Plugins". Make sure, that you install the file daemon without the "compatible" option. 100 101Manual install 102'''''''''''''' 103 104After downloading the plugin you need to copy it into :file:`C:\Program Files\Bareos\Plugins`. Then you need to define the plugin directory and which plugin the bareos-filedaemon should use. You have to edit the bareos-filedaemon resource in :file:`C:\Program Data\bareos-fd.conf` as follows: 105 106.. code-block:: bareosconfig 107 :caption: MSSQL plugin configuration 108 109 FileDaemon { 110 Name = mssqlserver-fd 111 Maximum Concurrent Jobs = 20 112 113 # remove comment in next line to load plugins from specified directory 114 Plugin Directory = "C:/Program Files/Bareos/Plugins" 115 116 Plugin Names = "mssqlvdi" 117 compatible = no # this is the default since bareos 15 118 } 119 120Plugin Test 121^^^^^^^^^^^ 122 123.. code-block:: bconsole 124 :caption: status client=mssqlserver-fd 125 126 *<input>status client=mssqlserver-fd</input> 127 Connecting to Client mssqlserver-fd at 192.168.10.101:9102 128 129 mssqlserver-fd Version: 13.2.2 (12 November 2013) VSS Linux Cross-compile Win64 130 Daemon started 18-Nov-13 11:51. Jobs: run=0 running=0. 131 Microsoft Windows Server 2012 Standard Edition (build 9200), 64-bit 132 Heap: heap=0 smbytes=20,320 max_bytes=20,522 bufs=71 max_bufs=73 133 Sizeof: boffset_t=8 size_t=8 debug=0 trace=1 bwlimit=0kB/s 134 Plugin Info: 135 Plugin : mssqlvdi-fd.dll 136 Description: Bareos MSSQL VDI Windows File Daemon Plugin 137 Version : 1, Date: July 2013 138 Author : Zilvinas Krapavickas 139 License : Bareos AGPLv3 140 Usage : 141 mssqlvdi: 142 serveraddress=<hostname>: 143 instance=<instance name>: 144 database=<database name>: 145 username=<database username>: 146 password=<database password>: 147 norecovery=<yes|no>: 148 replace=<yes|no>: 149 recoverafterrestore=<yes|no>: 150 stopbeforemark=<log sequence number specification>: 151 stopatmark=<log sequence number specification>: 152 stopat=<timestamp> 153 getconfigurationtimeout=<timeout-seconds> 154 155 examples: 156 timestamp: 'Apr 15, 2020 12:00 AM' 157 log sequence number: 'lsn:15000000040000037' 158 159Configure the FileSet 160^^^^^^^^^^^^^^^^^^^^^ 161 162To use the plugin you need to configure it in the fileset as a plugin resource. For each database instance you need to define a exclusive backup job and fileset. 163 164.. code-block:: bareosconfig 165 :caption: MSSQL FileSet 166 167 Fileset { 168 Name = "Mssql" 169 Enable VSS = no 170 Include { 171 Options { 172 Signature = MD5 173 } 174 Plugin = "mssqlvdi:instance=default:database=myDatabase:username=bareos:password=bareos" 175 } 176 } 177 178In this example we use the standard security method for the connection. 179 180Used options in the plugin string are: 181 182mssqlvdi 183 This is the reference to the MSSQL plugin. 184 185serveraddress 186 (:sinceVersion:`14.2.2: MSSQL: serveraddress`) Defines the server address to connect to (if empty defaults to localhost). 187 188instance 189 Defines the instance within the database server. 190 191database 192 Defines the database that should get backed up. 193 194username and password 195 Username and Password are required, when the connection is done using a MSSQL user. If the systemaccount the bareos-fd runs with has succifient permissions, this is not required. 196 197It is recommend to define an additional restore job. 198 199For every database separate job and FileSet are required. 200 201Run Backups 202^^^^^^^^^^^ 203 204Here you can see an example for a backup: 205 206.. code-block:: bconsole 207 :caption: run MSSQL backup job 208 209 *<input>run job=MSSQLBak</input> 210 Using Catalog "MyCatalog" 211 Run Backup job 212 JobName: MSSQLBak 213 Level: Full 214 Client: mssqlserver-fd 215 Format: Native 216 FileSet: Mssql 217 Pool: File (From Job resource) 218 Storage: File (From Job resource) 219 When: 2013-11-21 09:48:27 220 Priority: 10 221 OK to run? (yes/mod/no): <input>yes</input> 222 Job queued. JobId=7 223 You have no messages. 224 *<input>mess</input> 225 21-Nov 09:48 bareos-dir JobId 7: Start Backup JobId 7, Job=MSSQLBak.2013-11-21_09.48.30_04 226 21-Nov 09:48 bareos-dir JobId 7: Using Device "FileStorage" to write. 227 21-Nov 09:49 bareos-sd JobId 7: Volume "test1" previously written, moving to end of data. 228 21-Nov 09:49 bareos-sd JobId 7: Ready to append to end of Volume "test1" size=2300114868 229 21-Nov 09:49 bareos-sd JobId 7: Elapsed time=00:00:27, Transfer rate=7.364 M Bytes/second 230 231 21-Nov 09:49 bareos-dir JobId 7: Bareos bareos-dir 13.4.0 (01Oct13): 232 Build OS: x86_64-pc-linux-gnu debian Debian GNU/Linux 7.0 (wheezy) 233 JobId: 7 234 Job: MSSQLBak.2013-11-21_09.48.30_04 235 Backup Level: Full 236 Client: "mssqlserver-fd" 13.2.2 (12Nov13) Microsoft Windows Server 2012 Standard Edition (build 9200), 64-bit,Cross-compile,Win64 237 FileSet: "Mssql" 2013-11-04 23:00:01 238 Pool: "File" (From Job resource) 239 Catalog: "MyCatalog" (From Client resource) 240 Storage: "File" (From Job resource) 241 Scheduled time: 21-Nov-2013 09:48:27 242 Start time: 21-Nov-2013 09:49:13 243 End time: 21-Nov-2013 09:49:41 244 Elapsed time: 28 secs 245 Priority: 10 246 FD Files Written: 1 247 SD Files Written: 1 248 FD Bytes Written: 198,836,224 (198.8 MB) 249 SD Bytes Written: 198,836,435 (198.8 MB) 250 Rate: 7101.3 KB/s 251 Software Compression: None 252 VSS: no 253 Encryption: no 254 Accurate: no 255 Volume name(s): test1 256 Volume Session Id: 1 257 Volume Session Time: 1384961357 258 Last Volume Bytes: 2,499,099,145 (2.499 GB) 259 Non-fatal FD errors: 0 260 SD Errors: 0 261 FD termination status: OK 262 SD termination status: OK 263 Termination: Backup OK 264 265At least you gain a full backup which contains the follow: 266 267 268 269:: 270 271 @MSSQL/ 272 @MSSQL/default/ 273 @MSSQL/default/myDatabase/ 274 @MSSQL/default/myDatabase/db-full 275 276 277 278After your first full backup you will be able to also run differential and incremental backups. 279Differntial FileSet example: 280 281 282 283:: 284 285 /@MSSQL/ 286 /@MSSQL/default/ 287 /@MSSQL/default/myDatabase/ 288 /@MSSQL/default/myDatabase/db-full 289 /@MSSQL/default/myDatabase/db-diff 290 291 292 293Incremental FileSet example: 294 295 296 297:: 298 299 *@MSSQL/ 300 *default/ 301 *myDatabase/ 302 *db-diff 303 *db-full 304 *log-2013-11-21 17:32:20 305 306 307 308Restores 309^^^^^^^^ 310 311If you want to perform a restore of a full backup without differentials or incrementals you have some options which helps you to restore even the corrupted database still exist. But you have to specifiy the options like plugin, instance and database during every backup. 312 313replace=<yes|no> 314 With this option you can replace the database if it still exist. 315 316instance 317 Defines the server instance whithin the database is running. 318 319database 320 Defines the database you want to backup. 321 322If you want to restore the actual backup to a set of backup files which you can use to restore a database under an new name or perform any kind of special operations using for example the sql management studio, you can use a where setting for the restore other then ’/’. When the where is set to ’/’ it will restore to the Virtual Device Interface (VDI). 323 324When you specify for restore a where path which is lets say ’c:/temp’ the plugin will restore the selected backup files under a relocated path under c:/temp/@MSSQL@/... 325 326Example for a full restore: 327 328.. code-block:: bconsole 329 :caption: restore MSSQL database 330 331 *<input>restore client=mssqlserver-fd</input> 332 Using Catalog "MyCatalog" 333 334 First you select one or more JobIds that contain files 335 to be restored. You will be presented several methods 336 of specifying the JobIds. Then you will be allowed to 337 select which files from those JobIds are to be restored. 338 339 To select the JobIds, you have the following choices: 340 1: List last 20 Jobs run 341 2: List Jobs where a given File is saved 342 3: Enter list of comma separated JobIds to select 343 4: Enter SQL list command 344 5: Select the most recent backup for a client 345 6: Select backup for a client before a specified time 346 7: Enter a list of files to restore 347 8: Enter a list of files to restore before a specified time 348 9: Find the JobIds of the most recent backup for a client 349 10: Find the JobIds for a backup for a client before a specified time 350 11: Enter a list of directories to restore for found JobIds 351 12: Select full restore to a specified Job date 352 13: Cancel 353 Select item: (1-13): <input>5</input> 354 Automatically selected FileSet: Mssql 355 +-------+-------+----------+-------------+---------------------+------------+ 356 | JobId | Level | JobFiles | JobBytes | StartTime | VolumeName | 357 +-------+-------+----------+-------------+---------------------+------------+ 358 | 8 | F | 1 | 198,836,224 | 2013-11-21 09:52:28 | test1 | 359 +-------+-------+----------+-------------+---------------------+------------+ 360 You have selected the following JobId: 8 361 362 Building directory tree for JobId(s) 8 ... 363 1 files inserted into the tree. 364 365 You are now entering file selection mode where you add (mark) and 366 remove (unmark) files to be restored. No files are initially added, unless 367 you used the "all" keyword on the command line. 368 Enter "done" to leave this mode. 369 370 cwd is: / 371 $ <input>mark *</input> 372 1 file marked. 373 $ <input>done</input> 374 Bootstrap records written to /var/lib/bareos/bareos-dir.restore.4.bsr 375 376 The job will require the following 377 Volume(s) Storage(s) SD Device(s) 378 =========================================================================== 379 380 test1 File FileStorage 381 382 Volumes marked with "*" are online. 383 384 385 1 file selected to be restored. 386 387 The defined Restore Job resources are: 388 1: RestoreMSSQL 389 2: RestoreFiles 390 Select Restore Job (1-2): <input>1</input> 391 Using Catalog "MyCatalog" 392 Run Restore job 393 JobName: RestoreMSSQL 394 Bootstrap: /var/lib/bareos/bareos-dir.restore.4.bsr 395 Where: / 396 Replace: Always 397 FileSet: Mssql 398 Backup Client: mssqlserver-fd 399 Restore Client: mssqlserver-fd 400 Format: Native 401 Storage: File 402 When: 2013-11-21 17:12:05 403 Catalog: MyCatalog 404 Priority: 10 405 Plugin Options: *None* 406 OK to run? (yes/mod/no): <input>mod</input> 407 Parameters to modify: 408 1: Level 409 2: Storage 410 3: Job 411 4: FileSet 412 5: Restore Client 413 6: Backup Format 414 7: When 415 8: Priority 416 9: Bootstrap 417 10: Where 418 11: File Relocation 419 12: Replace 420 13: JobId 421 14: Plugin Options 422 Select parameter to modify (1-14): <input>14</input> 423 Please enter Plugin Options string: <input>mssqlvdi:instance=default:database=myDatabase:replace=yes</input> 424 Run Restore job 425 JobName: RestoreMSSQL 426 Bootstrap: /var/lib/bareos/bareos-dir.restore.4.bsr 427 Where: / 428 Replace: Always 429 FileSet: Mssql 430 Backup Client: mssqlserver-fd 431 Restore Client: mssqlserver-fd 432 Format: Native 433 Storage: File 434 When: 2013-11-21 17:12:05 435 Catalog: MyCatalog 436 Priority: 10 437 Plugin Options: mssqlvdi:instance=default:database=myDatabase:replace=yes 438 OK to run? (yes/mod/no): <input>yes</input> 439 Job queued. JobId=10 440 You have messages. 441 *<input>mess</input> 442 21-Nov 17:12 bareos-dir JobId 10: Start Restore Job RestoreMSSQL.2013-11-21_17.12.26_11 443 21-Nov 17:12 bareos-dir JobId 10: Using Device "FileStorage" to read. 444 21-Nov 17:13 damorgan-sd JobId 10: Ready to read from volume "test1" on device "FileStorage" (/storage). 445 21-Nov 17:13 damorgan-sd JobId 10: Forward spacing Volume "test1" to file:block 0:2499099145. 446 21-Nov 17:13 damorgan-sd JobId 10: End of Volume at file 0 on device "FileStorage" (/storage), Volume "test1" 447 21-Nov 17:13 damorgan-sd JobId 10: End of all volumes. 448 21-Nov 17:13 bareos-dir JobId 10: Bareos bareos-dir 13.4.0 (01Oct13): 449 Build OS: x86_64-pc-linux-gnu debian Debian GNU/Linux 7.0 (wheezy) 450 JobId: 10 451 Job: RestoreMSSQL.2013-11-21_17.12.26_11 452 Restore Client: mssqlserver-fd 453 Start time: 21-Nov-2013 17:12:28 454 End time: 21-Nov-2013 17:13:21 455 Files Expected: 1 456 Files Restored: 1 457 Bytes Restored: 198,836,224 458 Rate: 3751.6 KB/s 459 FD Errors: 0 460 FD termination status: OK 461 SD termination status: OK 462 Termination: Restore OK 463 464Restore a Backup Chain 465'''''''''''''''''''''' 466 467If you like to restore a specific state or a whole chain consists of full, incremental and differential backups you need to use the "norecovery=yes" option. After this the database is in "recovery mode". You can also use a option which put the database right after the restore back into the right mode. If you like to restore certains point with protocols or "LSN" it it not recommend to work with this option. 468 469norecovery=<yes|no> 470 This option must be set to yes, if the database server should not do a automatic recovery after the backup. Instead, additional manual maintenace operations are possible. 471 472recoverafterrestore=<yes|no> 473 With this command the database is right after backup in the correct mode. If you not use this you have to use the followed tsql statement: 474 475 :: 476 477 Restore DATABASE yourDatabase WITH RECOVERY 478 GO 479 480 481 482stopbeforemark=<log sequence number specification> 483 used for point in time recovery. 484 485stopatmark=<log sequence number specification> 486 used for point in time recovery. 487 488stopat=<timestamp> 489 used for point in time recovery. 490 491Followed is a example for a restore of full, differential and incremental backup with a replace of the original database: 492 493.. code-block:: bconsole 494 :caption: restore MSSQL database chain 495 496 *<input>restore client=mssqlserver-fd</input> 497 498 First you select one or more JobIds that contain files 499 to be restored. You will be presented several methods 500 of specifying the JobIds. Then you will be allowed to 501 select which files from those JobIds are to be restored. 502 503 To select the JobIds, you have the following choices: 504 1: List last 20 Jobs run 505 2: List Jobs where a given File is saved 506 3: Enter list of comma separated JobIds to select 507 4: Enter SQL list command 508 5: Select the most recent backup for a client 509 6: Select backup for a client before a specified time 510 7: Enter a list of files to restore 511 8: Enter a list of files to restore before a specified time 512 9: Find the JobIds of the most recent backup for a client 513 10: Find the JobIds for a backup for a client before a specified time 514 11: Enter a list of directories to restore for found JobIds 515 12: Select full restore to a specified Job date 516 13: Cancel 517 Select item: (1-13): <input>5</input> 518 Automatically selected FileSet: Mssql 519 +-------+-------+----------+-------------+---------------------+------------+ 520 | JobId | Level | JobFiles | JobBytes | StartTime | VolumeName | 521 +-------+-------+----------+-------------+---------------------+------------+ 522 | 8 | F | 1 | 198,836,224 | 2013-11-21 09:52:28 | test1 | 523 | 11 | D | 1 | 2,555,904 | 2013-11-21 17:19:45 | test1 | 524 | 12 | I | 1 | 720,896 | 2013-11-21 17:29:39 | test1 | 525 +-------+-------+----------+-------------+---------------------+------------+ 526 You have selected the following JobIds: 8,11,12 527 528 Building directory tree for JobId(s) 8,11,12 ... 529 3 files inserted into the tree. 530 531 You are now entering file selection mode where you add (mark) and 532 remove (unmark) files to be restored. No files are initially added, unless 533 you used the "all" keyword on the command line. 534 Enter "done" to leave this mode. 535 536 cwd is: / 537 $ <input>mark *</input> 538 3 files marked. 539 $ <input>lsmark</input> 540 *@MSSQL/ 541 *default/ 542 *myDatabase/ 543 *db-diff 544 *db-full 545 *log-2013-11-21 17:32:20 546 $ <input>done</input> 547 Bootstrap records written to /var/lib/bareos/bareos-dir.restore.6.bsr 548 549 The job will require the following 550 Volume(s) Storage(s) SD Device(s) 551 =========================================================================== 552 553 test1 File FileStorage 554 555 Volumes marked with "*" are online. 556 557 558 1 file selected to be restored. 559 560 The defined Restore Job resources are: 561 1: RestoreMSSQL 562 2: RestoreFiles 563 Select Restore Job (1-2): <input>1</input> 564 Run Restore job 565 JobName: RestoreMSSQL 566 Bootstrap: /var/lib/bareos/bareos-dir.restore.6.bsr 567 Where: / 568 Replace: Always 569 FileSet: Mssql 570 Backup Client: mssqlserver-fd 571 Restore Client: mssqlserver-fd 572 Format: Native 573 Storage: File 574 When: 2013-11-21 17:34:23 575 Catalog: MyCatalog 576 Priority: 10 577 Plugin Options: *None* 578 OK to run? (yes/mod/no): <input>mod</input> 579 Parameters to modify: 580 1: Level 581 2: Storage 582 3: Job 583 4: FileSet 584 5: Restore Client 585 6: Backup Format 586 7: When 587 8: Priority 588 9: Bootstrap 589 10: Where 590 11: File Relocation 591 12: Replace 592 13: JobId 593 14: Plugin Options 594 Select parameter to modify (1-14): <input>14</input> 595 Please enter Plugin Options string: <input>mssqlvdi:instance=default:database=myDatabase:replace=yes:norecovery=yes</input> 596 Run Restore job 597 JobName: RestoreMSSQL 598 Bootstrap: /var/lib/bareos/bareos-dir.restore.6.bsr 599 Where: / 600 Replace: Always 601 FileSet: Mssql 602 Backup Client: mssqlserver-fd 603 Restore Client: mssqlserver-fd 604 Format: Native 605 Storage: File 606 When: 2013-11-21 17:34:23 607 Catalog: MyCatalog 608 Priority: 10 609 Plugin Options: mssqlvdi:instance=default:database=myDatabase:replace=yes:norecovery=yes 610 OK to run? (yes/mod/no): <input>yes</input> 611 Job queued. JobId=14 612 21-Nov 17:34 bareos-dir JobId 14: Start Restore Job RestoreMSSQL.2013-11-21_17.34.40_16 613 21-Nov 17:34 bareos-dir JobId 14: Using Device "FileStorage" to read. 614 21-Nov 17:35 damorgan-sd JobId 14: Ready to read from volume "test1" on device "FileStorage" (/storage). 615 21-Nov 17:35 damorgan-sd JobId 14: Forward spacing Volume "test1" to file:block 0:2499099145. 616 21-Nov 17:35 damorgan-sd JobId 14: End of Volume at file 0 on device "FileStorage" (/storage), Volume "test1" 617 21-Nov 17:35 damorgan-sd JobId 14: End of all volumes. 618 21-Nov 17:35 bareos-dir JobId 14: Bareos bareos-dir 13.4.0 (01Oct13): 619 Build OS: x86_64-pc-linux-gnu debian Debian GNU/Linux 7.0 (wheezy) 620 JobId: 14 621 Job: RestoreMSSQL.2013-11-21_17.34.40_16 622 Restore Client: mssqlserver-fd 623 Start time: 21-Nov-2013 17:34:42 624 End time: 21-Nov-2013 17:35:36 625 Files Expected: 1 626 Files Restored: 3 627 Bytes Restored: 202,113,024 628 Rate: 3742.8 KB/s 629 FD Errors: 0 630 FD termination status: OK 631 SD termination status: OK 632 Termination: Restore OK 633 634 635.. _backup-postgresql: 636 637Backup of a PostgreSQL Database 638~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 639 640.. index:: 641 single: PostgreSQL; Backup 642 single: Database; PostgreSQL; Backup 643 644In this section, we describe different methods how to do backups of the PostgreSQL databases. 645 646Backup of a PostgreSQL Database by using the RunScript directive 647^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 648 649.. index:: 650 single: RunScript; Example 651 652One method to backup a PostgreSQL database is to use the :command:`pg_dumpall` tool to dump the database into a file and then backup it as a normal file. After the backup, the file can be removed. It may also be an option not to remove it, so that the latest version is always available immediately. On the next job run it will be overwritten anyway. 653 654This can be done by using :config:option:`dir/job/RunScript`\ directives inside a Job Resource, for example: 655 656.. code-block:: bareosconfig 657 :caption: RunScript job resource for a PostgreSQL backup 658 659 Job { 660 Name = "backup-postgres" 661 JobDefs = "DefaultJob" 662 Client = dbserver-fd 663 Level = Full 664 FileSet="postgres" 665 666 # This creates a dump of our database in the local filesystem on the client 667 RunScript { 668 FailJobOnError = Yes 669 RunsOnClient = Yes 670 RunsWhen = Before 671 Command = "su postgres -c 'pg_dumpall > /var/tmp/postgresql_dump.sql'" 672 } 673 674 # This deletes the dump in our local filesystem on the client 675 RunScript { 676 RunsOnSuccess = Yes 677 RunsOnClient = Yes 678 RunsWhen = After 679 Command = "rm /var/tmp/postgresql_dump.sql" 680 } 681 } 682 683 FileSet { 684 Name = "postgres" 685 Include { 686 Options { 687 signature = MD5 688 compression = gzip 689 } 690 # database dump file 691 File = "/var/tmp/postgresql_dump.sql" 692 } 693 } 694 695 696Note that redirecting the :command:`pg_dumpall` output to a file requires to run the whole command line through a shell, otherwise the :command:`pg_dumpall` would not know what do with the :command:`>` character and the job would fail. As no shell features like redirection or piping are used for the :command:`rm`, the :command:`sh -c` is not needed there. See :config:option:`dir/job/RunScript` for more details. 697 698Backup of a PostgreSQL Databases by using the bpipe plugin 699^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 700 701.. index:: 702 single: bpipe; PostgreSQL backup 703 704Instead of creating a temporary database dump file, the bpipe plugin can be used. For general information about bpipe, see the :ref:`bpipe` section. The bpipe plugin is configured inside the :config:option:`dir/fileset/Include` section of a File Set, e.g.: 705 706.. code-block:: bareosconfig 707 :caption: bpipe directive for PostgreSQL backup 708 709 FileSet { 710 Name = "postgresql-all" 711 Include { 712 Options { 713 signature = MD5 714 compression = gzip 715 } 716 Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=su postgres -c pg_dumpall:writer=su postgres -c psql" 717 } 718 } 719 720This causes the File Daemon to call bpipe plugin, which will write its data into the "pseudo" file :file:`/POSTGRESQL/dump.sql` by calling the program :command:`pg_dumpall -U postgres` to read the data during backup. The :command:`pg_dumpall` command outputs all the data for the database, which will be read by the plugin and stored in the backup. During restore, the data that was backed up will be sent to the program specified in the last field, which in this 721case is psql. When psql is called, it will read the data sent to it by the plugin then write it back to the same database from which it came from. 722 723This can also be used, to backup a database that is running on a remote host: 724 725.. code-block:: bareosconfig 726 :caption: bpipe directive to backup a PostgreSQL database that is running on a remote host 727 728 FileSet { 729 Name = "postgresql-remote" 730 Include { 731 Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=pg_dumpall -h <hostname> -U <username> -W <password>:writer=psql -h <hostname> -U <username> -W <password>" 732 Options { 733 signature = MD5 734 compression = gzip 735 } 736 } 737 } 738 739 740Backup of a PostgreSQL Databases by using the PostgreSQL-Plugin 741^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 742 743See :ref:`plugin-postgresql-fd`. 744 745 746 747.. _backup-mysql: 748 749Backup of a MySQL Database 750~~~~~~~~~~~~~~~~~~~~~~~~~~ 751 752:index:`\ <single: MySQL; Backup>` 753:index:`\ <single: Database; MySQL; Backup>` 754 755In this section, we describe different methods to do a full backup of a MySQL database. 756 757 758Backup of a MySQL Database by using the RunScript directive 759^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 760 761:index:`\ <single: RunScript; Example>`\ 762 763One method to backup a MySQL database is to use the :command:`mysqldump` tool to dump the database into a file and then backup it as a normal file. After the backup, the file can be removed. It may also be an option not to remove it, so that the latest version is always available immediately. On the next job run it will be overwritten anyway. 764 765This can be done by using :config:option:`dir/job/RunScript`\ directives, for example: 766 767.. code-block:: bareosconfig 768 :caption: RunScript job resource for a MySQL backup 769 770 Job { 771 Name = "BackupDatabase" 772 JobDefs = "DefaultJob" 773 Client = dbserver-fd 774 Level = Full 775 FileSet="Database" 776 777 # This creates a dump of our database in the local filesystem on the Client 778 RunScript { 779 FailJobOnError = Yes 780 RunsOnClient = Yes 781 RunsWhen = Before 782 Command = "sh -c 'mysqldump --user=<username> --password=<password> --opt --all-databases > /var/lib/bareos/mysql_dump.sql'" 783 } 784 785 # This deletes the dump in the local filesystem on the Client 786 RunScript { 787 RunsOnSuccess = Yes 788 RunsOnClient = Yes 789 RunsWhen = After 790 Command = "rm /var/lib/bareos/mysql_dump.sql" 791 } 792 } 793 794 FileSet { 795 Name = "Database" 796 Include { 797 Options { 798 signature = MD5 799 compression = gzip 800 } 801 # database dump file 802 File = "/var/lib/bareos/mysql_dump.sql" 803 } 804 } 805 806Note that redirecting the :command:`mysqldump` output to a file requires to run the whole command line through a shell, otherwise the :command:`mysqldump` would not know what do with the :command:`>` character and the job would fail. As no shell features like redirection or piping are used for the :command:`rm`, the :command:`sh -c` is not needed there. See :config:option:`dir/job/RunScript`\ for more details. 807 808Backup of a MySQL Database by using the bpipe plugin 809^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 810 811:index:`\ <single: bpipe; MySQL backup>`\ 812 813Instead of creating a temporary database dump file, the bpipe plugin can be used. For general information about bpipe, see the :ref:`bpipe` section. The bpipe plugin is configured inside the Include section of a File Set, e.g.: 814 815.. code-block:: bareosconfig 816 :caption: bpipe fileset for MySQL backup 817 818 FileSet { 819 Name = "mysql-all" 820 Include { 821 Plugin = "bpipe:file=/MYSQL/dump.sql:reader=mysqldump --user=<user> --password=<password> --opt --all-databases:writer=mysql --user=<user> --password=<password>" 822 Options { 823 signature = MD5 824 compression = gzip 825 } 826 } 827 } 828 829This can also be used, to backup a database that is running on a remote host: 830 831.. code-block:: bareosconfig 832 :caption: bpipe directive to backup a MySQL database that is running on a remote host 833 834 FileSet{ 835 Name = "mysql-all" 836 Include { 837 Plugin = "bpipe:file=/MYSQL/dump.sql:reader=mysqldump --host=<hostname> --user=<user> --password=<password> --opt --all-databases:writer=mysql --host=<hostname> --user=<user> --password=<password>" 838 Options { 839 signature = MD5 840 compression = gzip 841 } 842 } 843 } 844 845If you do not want a direct restore of your data in your plugin directive, as shown in the examples above, there is the possibility to restore the dump to the filesystem first, which offers you more control over the restore process, e.g.: 846 847.. code-block:: bareosconfig 848 :caption: bpipe directive to backup a MySQL database and restore the dump to the filesystem first 849 850 FileSet{ 851 Name = "mysql-all" 852 Include { 853 Plugin = "bpipe:file=/MYSQL/dump.sql:reader=mysqldump --host=<hostname> --user=<user> --password=<password> --opt --all-databases:writer=/usr/lib/bareos/scripts/bpipe-restore.sh" 854 Options { 855 signature = MD5 856 compression = gzip 857 } 858 } 859 } 860 861A very simple corresponding shell script (:command:`bpipe-restore.sh`) to the method above might look like the following one: 862 863.. code-block:: bareosconfig 864 :caption: bpipe shell script for a restore to filesystem 865 866 #!/bin/bash 867 cat - > /tmp/dump.sql 868 exit 0 869 870 871.. _backup-mysql-python: 872 873Backup of MySQL Databases using the Python MySQL plugin 874^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 875 876.. index:: 877 single: Plugin; MySQL Backup 878 879The Python plugin from https://github.com/bareos/bareos-contrib/tree/master/fd-plugins/mysql-python makes a backup of all or selected MySQL databases from the |fd| or any other MySQL server. It makes use of the mysqldump command and basically grabs data from mysqldump via pipe. This plugin is suitable to backup database dumps. 880Following settings must be done on the Bareos client (|fd|): 881 882- install and enable the |fd| Python plugin 883 884- install the Python MySQL plugin (for some platforms it is available prepackaged from http://download.bareos.org/bareos/contrib/\ , on the other platforms: copy the plugin files to the Bareos Plugin Directory) 885 886- disable bacula compatibility (default for Bareos >= 15.2) 887 888.. code-block:: bareosconfig 889 :caption: bareos-fd.d/client/myself.conf 890 891 Client { 892 ... 893 Plugin Directory = /usr/lib64/bareos/plugins 894 Plugin Names = "python" 895 compatible = no 896 } 897 898Configure the plugin in the |dir|: 899 900.. code-block:: bareosconfig 901 :caption: bareos-dir.d/fileset/mysql.conf 902 903 FileSet { 904 Name = "mysql" 905 Include { 906 Options { 907 signature = MD5 908 compression = lz4 909 } 910 Plugin = "python:module_path=/usr/lib64/bareos/plugins:module_name=bareos-fd-mysql:db=test,wikidb" 911 #Plugin = "python:module_path=/usr/lib64/bareos/plugins:module_name=bareos-fd-mysql:mysqlhost=dbhost:mysqluser=bareos:mysqlpassword=bareos" 912 } 913 } 914 915In the above example the plugin creates and saves a dump from the databases called :strong:`test` and :strong:`wikidb`, running on the file-daemon. The commented example below specifies an explicit MySQL server called :strong:`dbhost`, and connects with user :strong:`bareos`, password :strong:`bareos`, to create and save a backup of all databases. 916 917The plugin creates a pipe internally, thus no extra space on disk is needed. You will find one file per database in the backups in the virtual directory :file:`/_mysqlbackups_`. 918 919List of supported options: 920 921db 922 comma separated list of databases to save, where each database will be stored in a separate file. If ommited, all databases will be saved. 923 924dumpbinary 925 command (with or without full path) to create the dumps. Default: :strong:`mysqldump` 926 927dumpoptions 928 options for dumpbinary, default: ":strong:`--events --single-transaction`" 929 930drop_and_recreate 931 if not set to :strong:`false`, adds :strong:`--add-drop-database --databases` to dumpoptions 932 933mysqlhost 934 MySQL host to connect to, default: :strong:`localhost` 935 936mysqluser 937 MySQL user. Default: unset, the user running the file-daemon will be used (usually root) 938 939mysqlpassword 940 MySQL password. Default: unset (better use :file:`my.cnf` to store passwords) 941 942On restore, the database dumps are restored to the subdirectory :file:`_mysqlbackups_` in the restore path. The database restore must be triggered manually (:command:`mysql < _mysqlbackups_/DATABASENAME.sql`). 943 944 945 946Backup of MySQL Databases using the Python xtrabackup plugin 947^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 948 949For incremental hot-backups of InnoDB tables, please use the Bareos MySQL / MariaDB Percona xtrabackup Plugin (see :ref:`backup-mysql-xtrabackup`). 950 951 952 953.. _section-MigrationMysqlToPostgresql: 954 955Migrate |mysql| to |postgresql| 956------------------------------- 957 958.. index:: 959 single: Migration from MySQL to PostgreSQL 960 single: Database; Migration; MySQL 961 single: Database; Migration; PostgreSQL 962 pair: Migration; MySQL 963 pair: Migration; PostgreSQL 964 single: bareos-dbcopy 965 see: dbcopy; bareos-dbcopy 966 967 968Since Bareos :sinceVersion:`19.0.0: MySQL Backend is deprecated` the use of |mysql| databases with 969Bareos is deprecated. Therefore Bareos provides a tool to conveniently copy the 970whole contents to a new |postgresql| database: :ref:`program-bareos-dbcopy`. This 971chapter describes how to do a migration using bareos-dbcopy. 972 973Make a backup of your old database 974~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 975 976.. warning:: 977 978 Make a backup of your old database before you start the migration process! 979 980 981Prepare the new database 982~~~~~~~~~~~~~~~~~~~~~~~~ 983 984Firstly, create a new |postgresql| database as described in :ref:`section-CreateDatabase`. 985Add the new |postgresql| database to the current |dir| configuration, but **do not remove** 986the |mysql| database from the config, yet. Both catalog resources must be present 987during the migration process. 988 989Both |mysql| and |postgresql| need to have the same Bareos database scheme version, 990i.e. have the schema from the identical Bareos version. 991 992.. note:: 993 994 Stop the |dir| before continuing. 995 996 997These are the catalog resources used in this example: 998 999.. code-block:: bareosconfig 1000 :caption: Existing catalog resource for |mysql| 1001 1002 Catalog 1003 { 1004 Name = MyCatalog 1005 DB Driver = mysql 1006 DB Name = bareos; 1007 DB User = bareos; 1008 DB Password = "" 1009 } 1010 1011.. code-block:: bareosconfig 1012 :caption: New catalog resource for |postgresql| 1013 1014 Catalog 1015 { 1016 Name = MyCatalog-psql 1017 DB Driver = postgresql 1018 DB Name = bareos; 1019 DB User = bareos; 1020 DB Password = "" 1021 } 1022 1023Run bareos-dbcopy 1024~~~~~~~~~~~~~~~~~ 1025 1026Once the databases are running you can start to copy the contents from |mysql| 1027to |postgresql|. Depending on the size of your database the copy process can run 1028up to several hours. In our tests with a database containing 160 Million rows 1029in the file table took about 5 hours to copy (the testsystem was equipped with SSDs). 1030 1031.. note:: 1032 1033 Please run bareos-dbcopy as user **bareos** to avoid problems with access rights. 1034 To start the shell as user **bareos** you can use this command: 1035 ``su -s /bin/bash - bareos`` 1036 1037 1038.. code-block:: shell-session 1039 :caption: Run the bareos-dbcopy command 1040 1041 # run this command as user bareos: 1042 bareos-dbcopy -c <path-to-bareos-config> MyCatalog MyCatalog-psql 1043 1044 1045*bareos-dbcopy* will then examine the databases and copy the tables one by one. 1046The *file table* is by far the largest table and usually takes the longest time 1047to copy. 1048 1049 1050 1051.. code-block:: shell-session 1052 :caption: Example output of bareos-dbcopy 1053 1054 Copying tables from "MyCatalog" to "MyCatalog-psql" 1055 gathering information about source catalog "MyCatalog"... 1056 gathering information about destination catalog "MyCatalog-psql"... 1057 getting column descriptions... 1058 --> basefiles 1059 --> client 1060 --> counters 1061 1062 ... 1063 1064 --> version 1065 copying tables... 1066 ====== table BaseFiles ====== 1067 --> checking destination table... 1068 --> requesting number of rows to be copied... 1069 --> nothing to copy... 1070 --> updating sequence 1071 --> success 1072 1073 ... 1074 1075 ====== table File ====== 1076 --> checking destination table... 1077 --> requesting number of rows to be copied... 1078 --> copying 100'000 rows... 1079 --> Start: 2020-02-26 16:52:01 1080 4% ETA:2020-02-26 16:52:08 (running:0h00m00s, remaining:0h00m07s) 1081 12% ETA:2020-02-26 16:52:07 (running:0h00m00s, remaining:0h00m05s) 1082 20% ETA:2020-02-26 16:52:08 (running:0h00m01s, remaining:0h00m05s) 1083 28% ETA:2020-02-26 16:52:07 (running:0h00m02s, remaining:0h00m04s) 1084 36% ETA:2020-02-26 16:52:07 (running:0h00m02s, remaining:0h00m04s) 1085 44% ETA:2020-02-26 16:52:07 (running:0h00m03s, remaining:0h00m03s) 1086 52% ETA:2020-02-26 16:52:08 (running:0h00m03s, remaining:0h00m03s) 1087 60% ETA:2020-02-26 16:52:07 (running:0h00m04s, remaining:0h00m02s) 1088 68% ETA:2020-02-26 16:52:08 (running:0h00m04s, remaining:0h00m02s) 1089 76% ETA:2020-02-26 16:52:08 (running:0h00m05s, remaining:0h00m01s) 1090 84% ETA:2020-02-26 16:52:08 (running:0h00m05s, remaining:0h00m01s) 1091 92% ETA:2020-02-26 16:52:08 (running:0h00m06s, remaining:0h00m00s) 1092 100% ETA:2020-02-26 16:52:08 (running:0h00m06s, remaining:0h00m00s) 1093 --> updating sequence 1094 --> success 1095 1096 ... 1097 1098 ====== table webacula_where_acl ====== 1099 --> checking destination table... 1100 --> destination table does not exist 1101 --> *** skipping *** 1102 database copy completed successfully 1103 1104.. note:: 1105 1106 In the example above the table *webacula_where_acl* was skipped, because it 1107 does not exist in the target database scheme. This is the desired behaviour. 1108 1109 1110Complete the migration process 1111~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1112 1113After bareos-dbcopy copied all data to the |postgresql| catalog the following 1114tasks need to be done: 1115 1116* rename the |postgresql| catalog resource to the name of the |mysql| resource 1117 (in our example: MyCatalog-psql -> MyCatalog) 1118* comment out the |mysql| catalog resource from the director configuration 1119* start the |dir| 1120 1121The migration is now completed. 1122 1123 1124.. _section-StatisticCollection: 1125 1126Statistics Collection 1127--------------------- 1128 1129.. index:: 1130 single: Webui; Configure Statistics Collection 1131 1132Statistics Collection can be controlled by a number of configuration directives. If Statistics Collection is enabled, statistics are collected by the |dir| and stored into the Catalog database. So enabling this feature will increase your database size. 1133 1134The Statistics are used by the |webui| to show the status of a running job. 1135 1136Director Configuration - Director Resource Directives 1137~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1138 1139- :config:option:`dir/director/StatisticsCollectInterval` 1140- :config:option:`dir/director/StatisticsRetention` 1141 1142Director Configuration - Storage Resource Directives 1143~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1144 1145- :config:option:`dir/storage/CollectStatistics` 1146 1147Storage Configuration - Storage Resource Directives 1148~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1149 1150- :config:option:`sd/storage/CollectDeviceStatistics` 1151- :config:option:`sd/storage/CollectJobStatistics` 1152- :config:option:`sd/storage/StatisticsCollectInterval` 1153 1154Storage Configuration - Device Resource Directives 1155~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1156 1157- :config:option:`sd/device/CollectStatistics` 1158 1159See chapter :ref:`section-JobStatistics` for additional information. 1160 1161 1162.. _section-RemoveClient: 1163 1164Removing a Client 1165----------------- 1166 1167Removing a client can mean several things in Bareos context. You can disable the client so no jobs that use this client will run anymore. You can also remove the client’s configuration so that no job can use the client anymore. However, you will still have the client in your catalog unless you explicitly remove it. 1168 1169Disable a client 1170~~~~~~~~~~~~~~~~ 1171 1172It is possible to disable a client either by calling :bcommand:`disable client=<client-name>` or by setting :config:option:`dir/client/Enabled`\ to no. While the configuration change will persist across restarts, the command just disables the client temporarily. 1173 1174When a client is disabled, the scheduler will not run any job for this client anymore, but you can still restore to it or manually trigger a job. 1175 1176Remove configuration for client 1177~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1178 1179If you want to remove the client permanently, you can also completely remove the client from your configuration. This will make sure that no job (neither backup nor restore) can use that client machine. After removing the Client Resource from the director configuration, you will also need to change or remove all Job and JobDefs Resources in the Director configuration that reference the removed client. 1180 1181Removing the client configuration does not remove the backed up data or the file and job records in the catalog for the client. You can still restore the data that has been backed up from the client to a different client. 1182 1183Remove catalog records related to the client 1184~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1185 1186Removing the client from the configuration will leave you with catalog records concerning the client. If you don’t want to restore any data backed up from the client anymore, you can call :bcommand:`purge jobs client=<client-name>`. This will remove all job and file records for the named client from the catalog. However, it will not remove the data from the volumes. 1187 1188 1189 1190.. warning:: 1191 1192 After removing the job and file records you will be unable to restore the client's data. 1193 The :bcommand:`purge` command ignores retention policies, so please take careful. 1194 1195 1196As soon as all jobs for a client have been removed from the catalog that client record becomes orphaned. Orphaned client records usually stay in your database indefinitely, but if you want them removed for cosmetic reasons you can do so using "Check for orphaned Client records" in :command:`bareos-dbcheck`. 1197 1198By default :command:`bareos-dbcheck` only lists the orphaned clients it finds. You need to enable the modify database flag to make it actually change the database. 1199