1#!/bin/sh 2# 3# Copyright (C) 2000-2020 Kern Sibbald 4# License: BSD 2-Clause; see file LICENSE-FOSS 5# 6echo " " 7echo "This script will update a Bacula PostgreSQL database" 8echo " from any from version 12-16 or 1014-1021 to version 1022" 9echo " which is needed to convert from any Bacula Enterprise" 10echo " version 4.0.x, 6.x.y, 8.x.y, 10.x.y to version 12.4.x" 11echo " " 12 13 14bindir=@POSTGRESQL_BINDIR@ 15PATH="$bindir:$PATH" 16db_name=@db_name@ 17 18# Special trick to not do the upgrade from 1015 to 1016 with this script 19if [ "$1" = "--stop1015" ]; then 20 STOP1015=yes 21 shift # remove this option from $* 22fi 23 24# Special trick to not do the upgrade from 1016 to 1017 with this script 25if [ "$1" = "--stop1016" ]; then 26 STOP1016=yes 27 shift # remove this option from $* 28fi 29 30ARGS=$* 31 32getVersion() 33{ 34 DBVERSION=`psql -d ${db_name} -t --pset format=unaligned -c "select VersionId from Version LIMIT 1" $ARGS` 35} 36 37getVersion 38 39if [ "x$DBVERSION" = x ]; then 40 echo " " 41 echo "Unable to detect database version, you can specify connection information" 42 echo "on the command line." 43 echo "Error. Cannot update this database." 44 echo " " 45 exit 1 46fi 47 48if [ "$DBVERSION" -lt 1014 -o "$DBVERSION" -gt 1021 ] ; then 49 if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 16 ] ; then 50 echo " " 51 echo "The existing database is version $DBVERSION !!" 52 echo "This script can only update an existing version 12-16, 1014-1021 database to version 1022." 53 echo "Error. Cannot update this database." 54 echo " " 55 exit 1 56 fi 57fi 58 59if [ "$DBVERSION" -eq 12 ] ; then 60 # from 5.0 61 if psql -f - -d ${db_name} $* <<END-OF-DATA 62BEGIN; -- Necessary for Bacula core 63CREATE TABLE RestoreObject ( 64 RestoreObjectId SERIAL NOT NULL, 65 ObjectName TEXT NOT NULL, 66 RestoreObject BYTEA NOT NULL, 67 PluginName TEXT NOT NULL, 68 ObjectLength INTEGER DEFAULT 0, 69 ObjectFullLength INTEGER DEFAULT 0, 70 ObjectIndex INTEGER DEFAULT 0, 71 ObjectType INTEGER DEFAULT 0, 72 FileIndex INTEGER DEFAULT 0, 73 JobId INTEGER, 74 ObjectCompression INTEGER DEFAULT 0, 75 PRIMARY KEY(RestoreObjectId) 76 ); 77 78CREATE INDEX restore_jobid_idx on RestoreObject(JobId); 79UPDATE Version SET VersionId=12; 80 81COMMIT; 82END-OF-DATA 83 then 84 echo "Update of Bacula PostgreSQL tables 12 to 13 succeeded." 85 getVersion 86 else 87 echo "Update of Bacula PostgreSQL tables 12 to 13 failed." 88 exit 1 89 fi 90fi 91 92if [ "$DBVERSION" -eq 13 ] ; then 93 # from 4.0 94 if psql -f - -d ${db_name} $* <<END-OF-DATA 95BEGIN; -- Necessary for Bacula core 96 97ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0; 98 99UPDATE Version SET VersionId=14; 100COMMIT; 101 102-- ANALYSE; 103 104END-OF-DATA 105 then 106 echo "Update of Bacula PostgreSQL tables from 13 to 14 succeeded." 107 getVersion 108 else 109 echo "Update of Bacula PostgreSQL tables failed." 110 exit 1 111 fi 112fi 113 114 115if [ "$DBVERSION" -eq 14 ] ; then 116 # from 5.2 117 if psql -f - -d ${db_name} $* <<END-OF-DATA 118INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 119 ('I', 'Incomplete Job',25); 120UPDATE Version SET VersionId=1014; 121 122END-OF-DATA 123 then 124 echo "Update of Bacula PostgreSQL tables 14 to 1014 succeeded." 125 getVersion 126 else 127 echo "Update of Bacula PostgreSQL tables 14 to 1014 failed." 128 exit 1 129 fi 130fi 131 132if [ "$DBVERSION" -eq 1014 ] ; then 133 if psql -f - -d ${db_name} $* <<END-OF-DATA 134ALTER TABLE Media ADD COLUMN volabytes bigint default 0; 135ALTER TABLE Media ADD COLUMN volapadding bigint default 0; 136ALTER TABLE Media ADD COLUMN volholebytes bigint default 0; 137ALTER TABLE Media ADD COLUMN volholes integer default 0; 138UPDATE Version SET VersionId=1015; 139END-OF-DATA 140 then 141 echo "Update of Bacula PostgreSQL tables 1014 to 1015 succeeded." 142 getVersion 143 else 144 echo "Update of Bacula PostgreSQL tables 1014 to 1015 failed." 145 exit 1 146 fi 147fi 148 149# Upgrade from the community edition 150# 15 to 1017 migration 151if [ "$DBVERSION" -eq 15 -o "$DBVERSION" -eq 16 ]; then 152 # In version 16, the upgrade 1018-1019 is already done 153 if [ "$DBVERSION" -eq 16 ]; then 154 SKIP1018=1 155 fi 156 157 # Can be adjusted 158 WORKMEM=1GB 159 160 COMP=`which pigz` 161 if [ "$COMP" = "" ]; then 162 COMP=`which pbzip2` 163 if [ "$COMP" = "" ]; then 164 COMP=`which lzop` 165 if [ "$COMP" = "" ]; then 166 COMP=`which gzip` 167 if [ "$COMP" = "" ]; then 168 echo "Error. Cannot find pigz, pbzip2, lzop or gzip" 169 exit 1 170 fi 171 fi 172 fi 173 fi 174 175 echo "Dumping File table to $PWD/file1017.data. " 176 echo "" 177 echo "The process may fail if the current user" 178 echo " doesn't have write permission on the current directory," 179 echo " or if the system doesn't have enough space to store a" 180 echo " compressed export of the File table" 181 psql --set ON_ERROR_STOP=1 -d ${db_name} $* -c "set work_mem='$WORKMEM';"'set enable_mergejoin to off ; set enable_hashjoin to off; copy (SELECT FileId, FileIndex, JobId, PathId, Filename.Name, DeltaSeq, MarkId, LStat, Md5 FROM File JOIN Filename USING (FilenameId)) TO STDOUT' | $COMP -1 -c > file1017.data 182 183 if [ $? -ne 0 ]; then 184 echo "Error while dumping file table to $PWD/file1017.data" 185 exit 1 186 fi 187 188 if ! psql --set ON_ERROR_STOP=1 -f - -d ${db_name} $* <<EOF 189BEGIN; 190 191DROP TABLE File; 192DROP TABLE Filename; 193 194CREATE TABLE File 195( 196 FileId bigint not null, 197 FileIndex integer not null default 0, 198 JobId integer not null, 199 PathId integer not null, 200 Filename text not null default '', 201 DeltaSeq smallint not null default 0, 202 MarkId integer not null default 0, 203 LStat text not null, 204 Md5 text not null 205); 206 207COMMIT; 208EOF 209 then 210 echo "Creation of new File table failed." 211 exit 1 212 fi 213 214 echo "Loading the File table from $PWD/file.$$.data..." 215 # we do everything in the same commit to avoid creating WALs on this operation 216 cat file1017.data | $COMP -d | psql --set ON_ERROR_STOP=1 -d ${db_name} $* -c "BEGIN; TRUNCATE File; COPY File FROM STDIN; set maintenance_work_mem='2000MB'; CREATE INDEX file_jpfid_idx on File (JobId, PathId, Filename text_pattern_ops); ALTER TABLE ONLY File ADD CONSTRAINT file_pkey PRIMARY KEY (FileId); COMMIT;" 217 218 if [ $? -ne 0 ]; then 219 echo "Inserting File data from file.$$.data failed." 220 exit 1 221 fi 222 223 echo "Creation of indexes and PK on the File table..." 224 225 # The maximum value for maintenance_work_mem is 2GB 226 if ! psql --set ON_ERROR_STOP=1 -f - -d ${db_name} $* <<EOF 227set maintenance_work_mem='2000MB'; 228BEGIN; 229CREATE SEQUENCE file_fileid_seq; 230ALTER SEQUENCE file_fileid_seq OWNED BY File.fileid; 231SELECT pg_catalog.setval('file_fileid_seq', (SELECT MAX(FileId) FROM File), true); 232ALTER TABLE File ALTER COLUMN FileId SET DEFAULT nextval('file_fileid_seq'::regclass); 233 234ANALYZE File; 235 236ALTER TABLE Media ALTER VolWrites TYPE BIGINT; 237ALTER TABLE unsavedfiles DROP COLUMN filenameid; 238ALTER TABLE unsavedfiles ADD COLUMN filename text not null; 239 240UPDATE Version SET VersionId = 1017; 241 242COMMIT; 243EOF 244 then 245 echo "Index creation for Bacula PostgreSQL tables." 246 exit 1 247 fi 248 echo "Upgrade of the File table succeeded. Version 1017" 249 rm -f file1017.data 250 getVersion 251fi 252 253# Specific option to not do the 1015 to 1016 migration 254if [ "$STOP1015" = "" -a "$DBVERSION" -eq 1015 ]; then 255 256 # Can be adjusted 257 WORKMEM=1GB 258 259 COMP=`which pigz` 260 if [ "$COMP" = "" ]; then 261 COMP=`which pbzip2` 262 if [ "$COMP" = "" ]; then 263 COMP=`which lzop` 264 if [ "$COMP" = "" ]; then 265 COMP=`which gzip` 266 if [ "$COMP" = "" ]; then 267 echo "Error. Cannot find pigz, pbzip2, lzop or gzip" 268 exit 1 269 fi 270 fi 271 fi 272 fi 273 274 echo "Dumping File table to $PWD/file1016.data. " 275 echo "" 276 echo "The process may fail if the current user" 277 echo " doesn't have write permission on the current directory," 278 echo " or if the system doesn't have enough space to store a" 279 echo " compressed export of the File table" 280 psql --set ON_ERROR_STOP=1 -d ${db_name} $* -c "set work_mem='$WORKMEM';"'set enable_mergejoin to off ; set enable_hashjoin to off; copy (SELECT FileId, FileIndex, JobId, PathId, Filename.Name, DeltaSeq, MarkId, LStat, Md5 FROM File JOIN Filename USING (FilenameId)) TO STDOUT' | $COMP -1 -c > file1016.data 281 282 if [ $? -ne 0 ]; then 283 echo "Error while dumping file table to $PWD/file1016.data" 284 exit 1 285 fi 286 287 if ! psql --set ON_ERROR_STOP=1 -f - -d ${db_name} $* <<EOF 288BEGIN; 289 290DROP TABLE File; 291DROP TABLE Filename; 292 293CREATE TABLE File 294( 295 FileId bigint not null, 296 FileIndex integer not null default 0, 297 JobId integer not null, 298 PathId integer not null, 299 Filename text not null default '', 300 DeltaSeq smallint not null default 0, 301 MarkId integer not null default 0, 302 LStat text not null, 303 Md5 text not null 304); 305 306COMMIT; 307EOF 308 then 309 echo "Creation of new File table failed." 310 exit 1 311 fi 312 313 echo "Loading the File table from $PWD/file.$$.data..." 314 # we do everything in the same commit to avoid creating WALs on this operation 315 cat file1016.data | $COMP -d | psql --set ON_ERROR_STOP=1 -d ${db_name} $* -c "BEGIN; TRUNCATE File; COPY File FROM STDIN; set maintenance_work_mem='2000MB'; CREATE INDEX file_jpfid_idx on File (JobId, PathId, Filename text_pattern_ops); ALTER TABLE ONLY File ADD CONSTRAINT file_pkey PRIMARY KEY (FileId); COMMIT;" 316 317 if [ $? -ne 0 ]; then 318 echo "Inserting File data from file.$$.data failed." 319 exit 1 320 fi 321 322 echo "Creation of indexes and PK on the File table..." 323 324 # The maximum value for maintenance_work_mem is 2GB 325 if ! psql --set ON_ERROR_STOP=1 -f - -d ${db_name} $* <<EOF 326set maintenance_work_mem='2000MB'; 327BEGIN; 328CREATE SEQUENCE file_fileid_seq; 329ALTER SEQUENCE file_fileid_seq OWNED BY File.fileid; 330SELECT pg_catalog.setval('file_fileid_seq', (SELECT MAX(FileId) FROM File), true); 331ALTER TABLE File ALTER COLUMN FileId SET DEFAULT nextval('file_fileid_seq'::regclass); 332 333ANALYZE File; 334 335ALTER TABLE Media ALTER VolWrites TYPE BIGINT; 336ALTER TABLE Job ADD COLUMN FileTable text default 'File'; 337ALTER TABLE JobHisto ADD COLUMN FileTable text default 'File'; 338 339CREATE TABLE Snapshot ( 340 SnapshotId serial, 341 Name text not null, 342 CreateTDate bigint default 0, 343 CreateDate timestamp without time zone not null, 344 ClientId int default 0, 345 Volume text not null, 346 Device text not null, 347 Type text not null, 348 Retention integer default 0, 349 Comment text, 350 primary key (SnapshotId) 351); 352 353CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device text_pattern_ops, 354 Volume text_pattern_ops, 355 Name text_pattern_ops); 356 357UPDATE Version SET VersionId = 1016; 358 359COMMIT; 360EOF 361 then 362 echo "Index creation for Bacula PostgreSQL tables." 363 exit 1 364 fi 365 echo "Upgrade of the File table succeeded." 366 rm -f file1016.data 367 getVersion 368fi 369 370if [ "$STOP1016" = "" -a "$DBVERSION" -eq 1016 ] ; then 371 if psql -f - -d ${db_name} $* <<END-OF-DATA 372begin; 373ALTER TABLE Snapshot ADD COLUMN JobId integer default 0; 374ALTER TABLE Snapshot ADD COLUMN FileSetId integer default 0; 375UPDATE Version SET VersionId=1017; 376commit; 377END-OF-DATA 378 then 379 echo "Update of Bacula PostgreSQL tables 1016 to 1017 succeeded." 380 getVersion 381 else 382 echo "Update of Bacula PostgreSQL tables 1016 to 1017 failed." 383 exit 1 384 fi 385fi 386 387if [ "$DBVERSION" -eq 1017 ] ; then 388 if psql -f - -d ${db_name} $* <<END-OF-DATA 389begin; 390CREATE TABLE FileMedia 391( 392 JobId integer not null, 393 FileIndex integer not null, 394 MediaId integer not null, 395 BlockAddress bigint default 0, 396 RecordNo integer default 0, 397 FileOffset bigint default 0 398); 399 400CREATE INDEX file_media_idx on FileMedia (JobId, FileIndex); 401 402UPDATE Version SET VersionId=1018; 403commit; 404END-OF-DATA 405 then 406 echo "Update of Bacula PostgreSQL tables 1017 to 1018 succeeded." 407 getVersion 408 else 409 echo "Update of Bacula PostgreSQL tables 1017 to 1018 failed." 410 exit 1 411 fi 412fi 413 414if [ "$DBVERSION" -eq 1018 -a "$SKIP1018" = 1 ]; then 415 # From version 16, the upgrade 1018-1019 is already done 416 if psql -f - -d ${db_name} $* <<END-OF-DATA 417UPDATE Version SET VersionId=1019; 418END-OF-DATA 419 then 420 echo "Update of Bacula PostgreSQL tables 1018 to 1019 succeeded." 421 getVersion 422 else 423 echo "Update of Bacula PostgreSQL tables 1018 to 1019 failed." 424 exit 1 425 fi 426fi 427 428if [ "$DBVERSION" -eq 1018 ] ; then 429 if psql -f - -d ${db_name} $* <<END-OF-DATA 430begin; 431ALTER TABLE basefiles ALTER COLUMN baseid SET DATA TYPE bigint; 432ALTER TABLE media RENAME COLUMN volparts TO voltype; 433ALTER TABLE media ADD COLUMN volparts INTEGER DEFAULT 0; 434ALTER TABLE media ADD COLUMN volcloudparts INTEGER DEFAULT 0; 435ALTER TABLE media ADD COLUMN lastpartbytes BIGINT DEFAULT 0; 436ALTER TABLE media ADD COLUMN cacheretention BIGINT DEFAULT 0; 437ALTER TABLE pool ADD COLUMN cacheretention BIGINT DEFAULT 0; 438 439UPDATE Version SET VersionId=1019; 440commit; 441END-OF-DATA 442 then 443 echo "Update of Bacula PostgreSQL tables 1018 to 1019 succeeded." 444 getVersion 445 else 446 echo "Update of Bacula PostgreSQL tables 1018 to 1019 failed." 447 exit 1 448 fi 449fi 450 451if [ "$DBVERSION" -eq 1019 ] ; then 452 if psql -f - -d ${db_name} $* <<END-OF-DATA 453begin; 454ALTER TABLE pool ADD COLUMN maxpoolbytes BIGINT DEFAULT 0; 455ALTER TABLE Job ADD COLUMN PriorJob text DEFAULT ''; 456ALTER TABLE JobHisto ADD COLUMN PriorJob text DEFAULT ''; 457UPDATE Version SET VersionId=1020; 458commit; 459END-OF-DATA 460 then 461 echo "Update of Bacula PostgreSQL tables 1019 to 1020 succeeded." 462 getVersion 463 else 464 echo "Update of Bacula PostgreSQL tables 1019 to 1020 failed." 465 exit 1 466 fi 467fi 468 469 470if [ "$DBVERSION" -eq 1020 ] ; then 471 if psql -f - -d ${db_name} $* <<END-OF-DATA 472begin; 473UPDATE Version SET VersionId=1021; 474commit; 475END-OF-DATA 476 then 477 echo "Update of Bacula PostgreSQL tables 1020 to 1021 succeeded." 478 getVersion 479 else 480 echo "Update of Bacula PostgreSQL tables 1020 to 1021 failed." 481 exit 1 482 fi 483fi 484 485if [ "$DBVERSION" -eq 1021 ] ; then 486 if psql -f - -d ${db_name} $* <<END-OF-DATA 487begin; 488 489CREATE TABLE TagJob 490( 491 JobId integer not null, 492 Tag text not null, 493 primary key (JobId, Tag) 494); 495 496CREATE TABLE TagClient 497( 498 ClientId integer not null, 499 Tag text not null, 500 primary key (ClientId, Tag) 501); 502 503CREATE TABLE TagMedia 504( 505 MediaId integer not null, 506 Tag text not null, 507 primary key (MediaId, Tag) 508); 509 510CREATE TABLE TagObject 511( 512 ObjectId integer not null, 513 Tag text not null, 514 primary key (ObjectId, Tag) 515); 516 517CREATE TABLE Object 518( 519 ObjectId bigserial not null, 520 521 JobId integer not null, 522 Path text not null, 523 Filename text not null, 524 PluginName text not null, 525 526 ObjectType text not null, 527 ObjectName text not null, 528 ObjectSource text not null, 529 ObjectUUID text not null, 530 ObjectSize bigint not null, 531 primary key (ObjectId) 532); 533 534create index object_jobid_idx on Object (JobId); 535create index object_type_idx on Object (ObjectType); 536create index object_name_idx on Object (ObjectName); 537create index object_source_idx on Object (ObjectSource); 538 539CREATE TABLE Events 540( 541 EventsId serial not null, 542 EventsCode text not null, 543 EventsType text not null, 544 EventsTime timestamp without time zone, 545 EventsInsertTime timestamp without time zone DEFAULT NOW(), 546 EventsDaemon text default '', 547 EventsSource text default '', 548 EventsRef text default '', 549 EventsText text not null, 550 primary key (EventsId) 551); 552create index events_time_idx on Events (EventsTime); 553UPDATE Version SET VersionId=1022; 554commit; 555END-OF-DATA 556 then 557 echo "Update of Bacula PostgreSQL tables 1021 to 1022 succeeded." 558 getVersion 559 else 560 echo "Update of Bacula PostgreSQL tables 1021 to 1022 failed." 561 exit 1 562 fi 563fi 564 565# 566# For all versions, we need to create the Index on Media(PoolId/StorageId) 567# It may fail, but it's not a big problem 568# 569psql -f - -d ${db_name} $* <<END-OF-DATA >/dev/null 2>/dev/null 570set client_min_messages = fatal; 571CREATE INDEX media_poolid_idx on Media (PoolId); 572CREATE INDEX media_storageid_idx ON Media (StorageId); 573END-OF-DATA 574 575exit 0 576