1#!/bin/sh 2# 3# Shell script to update PostgreSQL tables from version 1.36 to 1.38 4# 5echo " " 6echo "This script will update a Bacula PostgreSQL database from version 8 to 9" 7echo "Depending on the size of your database," 8echo "this script may take several minutes to run." 9echo " " 10bindir=/usr/bin 11 12if $bindir/psql -f - -d bacula $* <<END-OF-DATA 13 14ALTER TABLE media ADD COLUMN labeltype integer; 15UPDATE media SET labeltype=0; 16ALTER TABLE media ALTER COLUMN labeltype SET NOT NULL; 17ALTER TABLE media ADD COLUMN StorageId integer; 18UPDATE media SET StorageId=0; 19 20ALTER TABLE pool ADD COLUMN labeltype integer; 21UPDATE pool set labeltype=0; 22ALTER TABLE pool ALTER COLUMN labeltype SET NOT NULL; 23ALTER TABLE pool ADD COLUMN NextPoolId integer; 24UPDATE pool SET NextPoolId=0; 25ALTER TABLE pool ADD COLUMN MigrationHighBytes BIGINT; 26UPDATE pool SET MigrationHighBytes=0; 27ALTER TABLE pool ADD COLUMN MigrationLowBytes BIGINT; 28UPDATE pool SET MigrationLowBytes=0; 29ALTER TABLE pool ADD COLUMN MigrationTime BIGINT; 30UPDATE pool SET MigrationTime=0; 31 32 33ALTER TABLE jobmedia ADD COLUMN Copy integer; 34UPDATE jobmedia SET Copy=0; 35ALTER TABLE jobmedia ADD COLUMN Stripe integer; 36UPDATE jobmedia SET Stripe=0; 37 38 39ALTER TABLE media ADD COLUMN volparts integer; 40UPDATE media SET volparts=0; 41ALTER TABLE media ALTER COLUMN volparts SET NOT NULL; 42 43CREATE TABLE MediaType ( 44 MediaTypeId SERIAL, 45 MediaType TEXT NOT NULL, 46 ReadOnly INTEGER DEFAULT 0, 47 PRIMARY KEY(MediaTypeId) 48 ); 49 50CREATE TABLE Device ( 51 DeviceId SERIAL, 52 Name TEXT NOT NULL, 53 MediaTypeId INTEGER NOT NULL, 54 StorageId INTEGER, 55 DevMounts INTEGER NOT NULL DEFAULT 0, 56 DevReadBytes BIGINT NOT NULL DEFAULT 0, 57 DevWriteBytes BIGINT NOT NULL DEFAULT 0, 58 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0, 59 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0, 60 DevReadTime BIGINT NOT NULL DEFAULT 0, 61 DevWriteTime BIGINT NOT NULL DEFAULT 0, 62 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0, 63 DevWriteTimeSinceCleaning BIGINT DEFAULT 0, 64 CleaningDate TIMESTAMP WITHOUT TIME ZONE, 65 CleaningPeriod BIGINT NOT NULL DEFAULT 0, 66 PRIMARY KEY(DeviceId) 67 ); 68 69CREATE TABLE Storage ( 70 StorageId SERIAL, 71 Name TEXT NOT NULL, 72 AutoChanger INTEGER DEFAULT 0, 73 PRIMARY KEY(StorageId) 74 ); 75 76CREATE TABLE Status ( 77 JobStatus CHAR(1) NOT NULL, 78 JobStatusLong TEXT, 79 PRIMARY KEY (JobStatus) 80 ); 81 82INSERT INTO Status (JobStatus,JobStatusLong) VALUES 83 ('C', 'Created, not yet running'); 84INSERT INTO Status (JobStatus,JobStatusLong) VALUES 85 ('R', 'Running'); 86INSERT INTO Status (JobStatus,JobStatusLong) VALUES 87 ('B', 'Blocked'); 88INSERT INTO Status (JobStatus,JobStatusLong) VALUES 89 ('T', 'Completed successfully'); 90INSERT INTO Status (JobStatus,JobStatusLong) VALUES 91 ('E', 'Terminated with errors'); 92INSERT INTO Status (JobStatus,JobStatusLong) VALUES 93 ('e', 'Non-fatal error'); 94INSERT INTO Status (JobStatus,JobStatusLong) VALUES 95 ('f', 'Fatal error'); 96INSERT INTO Status (JobStatus,JobStatusLong) VALUES 97 ('D', 'Verify found differences'); 98INSERT INTO Status (JobStatus,JobStatusLong) VALUES 99 ('A', 'Canceled by user'); 100INSERT INTO Status (JobStatus,JobStatusLong) VALUES 101 ('F', 'Waiting for Client'); 102INSERT INTO Status (JobStatus,JobStatusLong) VALUES 103 ('S', 'Waiting for Storage daemon'); 104INSERT INTO Status (JobStatus,JobStatusLong) VALUES 105 ('m', 'Waiting for new media'); 106INSERT INTO Status (JobStatus,JobStatusLong) VALUES 107 ('M', 'Waiting for media mount'); 108INSERT INTO Status (JobStatus,JobStatusLong) VALUES 109 ('s', 'Waiting for storage resource'); 110INSERT INTO Status (JobStatus,JobStatusLong) VALUES 111 ('j', 'Waiting for job resource'); 112INSERT INTO Status (JobStatus,JobStatusLong) VALUES 113 ('c', 'Waiting for client resource'); 114INSERT INTO Status (JobStatus,JobStatusLong) VALUES 115 ('d', 'Waiting on maximum jobs'); 116INSERT INTO Status (JobStatus,JobStatusLong) VALUES 117 ('t', 'Waiting on start time'); 118INSERT INTO Status (JobStatus,JobStatusLong) VALUES 119 ('p', 'Waiting on higher priority jobs'); 120 121 122DELETE FROM version; 123INSERT INTO version (versionId) VALUES (9); 124 125vacuum; 126 127END-OF-DATA 128then 129 echo "Update of Bacula PostgreSQL tables succeeded." 130else 131 echo "Update of Bacula PostgreSQL tables failed." 132fi 133exit 0 134