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