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