1#!/bin/sh 2# 3# Copyright (C) 2000-2020 Kern Sibbald 4# License: BSD 2-Clause; see file LICENSE-FOSS 5# 6# Shell script to update PostgreSQL tables from version 1.38 to 2.0.0 or higher 7# 8echo " " 9echo "This script will update a Bacula PostgreSQL database from version 9 to 10" 10echo " which is needed to convert from Bacula version 1.38.x to 2.0.0 or higher" 11echo "Depending on the size of your database," 12echo "this script may take several minutes to run." 13echo " " 14 15bindir=@POSTGRESQL_BINDIR@ 16PATH="$bindir:$PATH" 17db_name=${db_name:-@db_name@} 18 19if $bindir/psql -f - -d ${db_name} $* <<END-OF-DATA 20ALTER TABLE media ADD COLUMN DeviceId integer; 21UPDATE media SET DeviceId=0; 22ALTER TABLE media ADD COLUMN MediaTypeId integer; 23UPDATE media SET MediaTypeId=0; 24ALTER TABLE media ADD COLUMN LocationId integer; 25UPDATE media SET LocationId=0; 26ALTER TABLE media ADD COLUMN RecycleCount integer; 27UPDATE media SET RecycleCount=0; 28ALTER TABLE media ADD COLUMN InitialWrite timestamp without time zone; 29ALTER TABLE media ADD COLUMN scratchpoolid integer; 30UPDATE media SET scratchpoolid=0; 31ALTER TABLE media ADD COLUMN recyclepoolid integer; 32UPDATE media SET recyclepoolid=0; 33ALTER TABLE media ADD COLUMN enabled integer; 34UPDATE media SET enabled=1; 35ALTER TABLE media ADD COLUMN Comment TEXT; 36 37ALTER TABLE job ADD COLUMN RealEndTime timestamp without time zone; 38ALTER TABLE job ADD COLUMN PriorJobId integer; 39UPDATE job SET PriorJobId=0; 40 41ALTER TABLE jobmedia DROP COLUMN Stripe; 42 43CREATE TABLE Location ( 44 LocationId SERIAL NOT NULL, 45 Location TEXT NOT NULL, 46 Cost integer default 0, 47 Enabled integer, 48 PRIMARY KEY (LocationId) 49); 50 51CREATE TABLE LocationLog ( 52 LocLogId SERIAL NOT NULL, 53 Date timestamp without time zone, 54 Comment TEXT NOT NULL, 55 MediaId INTEGER DEFAULT 0, 56 LocationId INTEGER DEFAULT 0, 57 newvolstatus text not null 58 check (newvolstatus in ('Full','Archive','Append', 59 'Recycle','Purged','Read-Only','Disabled', 60 'Error','Busy','Used','Cleaning','Scratch')), 61 newenabled smallint, 62 PRIMARY KEY(LocLogId) 63); 64 65 66CREATE TABLE Log 67( 68 LogId serial not null, 69 JobId integer not null, 70 Time timestamp without time zone, 71 LogText text not null, 72 primary key (LogId) 73); 74create index log_name_idx on Log (JobId); 75 76 77DELETE FROM version; 78INSERT INTO version (versionId) VALUES (10); 79 80vacuum; 81 82END-OF-DATA 83then 84 echo "Update of Bacula PostgreSQL tables succeeded." 85else 86 echo "Update of Bacula PostgreSQL tables failed." 87fi 88exit 0 89