3 # Copyright (C) 2000-2015 Kern Sibbald
4 # License: BSD 2-Clause; see file LICENSE-FOSS
6 # Shell script to update PostgreSQL tables from Bacula Community version
10 echo "This script will update a Bacula PostgreSQL database from version 12-14 to 15"
12 echo "Depending on the current version of your catalog,"
13 echo "you may have to run this script multiple times."
16 bindir=@POSTGRESQL_BINDIR@
24 DBVERSION=`psql -d ${db_name} -t --pset format=unaligned -c "select VersionId from Version LIMIT 1" $ARGS`
29 if [ "x$DBVERSION" = x ]; then
31 echo "Unable to detect database version, you can specify connection information"
32 echo "on the command line."
33 echo "Error. Cannot upgrade this database."
37 if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 14 ] ; then
39 echo "The existing database is version $DBVERSION !!"
40 echo "This script can only update an existing version 12-14, 1014-1016 database to version 1017."
41 echo "Error. Cannot upgrade this database."
46 if [ "$DBVERSION" -eq 12 ] ; then
48 if psql -f - -d ${db_name} $* <<END-OF-DATA
49 BEGIN; -- Necessary for Bacula core
50 CREATE TABLE RestoreObject (
51 RestoreObjectId SERIAL NOT NULL,
52 ObjectName TEXT NOT NULL,
53 RestoreObject BYTEA NOT NULL,
54 PluginName TEXT NOT NULL,
55 ObjectLength INTEGER DEFAULT 0,
56 ObjectFullLength INTEGER DEFAULT 0,
57 ObjectIndex INTEGER DEFAULT 0,
58 ObjectType INTEGER DEFAULT 0,
59 FileIndex INTEGER DEFAULT 0,
61 ObjectCompression INTEGER DEFAULT 0,
62 PRIMARY KEY(RestoreObjectId)
65 CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
66 UPDATE Version SET VersionId=13;
71 echo "Update of Bacula PostgreSQL tables 12 to 13 succeeded."
74 echo "Update of Bacula PostgreSQL tables 12 to 13 failed."
79 if [ "$DBVERSION" -eq 13 ] ; then
81 if psql -f - -d ${db_name} $* <<END-OF-DATA
82 BEGIN; -- Necessary for Bacula core
84 ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0;
86 UPDATE Version SET VersionId=14;
93 echo "Update of Bacula PostgreSQL tables from 13 to 14 succeeded."
96 echo "Update of Bacula PostgreSQL tables failed."
102 if [ "$DBVERSION" -eq 14 ] ; then
104 if psql -f - -d ${db_name} $* <<END-OF-DATA
105 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
106 ('I', 'Incomplete Job',25);
107 ALTER TABLE Media ADD COLUMN volabytes bigint default 0;
108 ALTER TABLE Media ADD COLUMN volapadding bigint default 0;
109 ALTER TABLE Media ADD COLUMN volholebytes bigint default 0;
110 ALTER TABLE Media ADD COLUMN volholes integer default 0;
111 ALTER TABLE Media ALTER VolWrites TYPE BIGINT;
113 CREATE TABLE Snapshot (
116 JobId integer default 0,
117 FileSetId integer default 0,
118 CreateTDate bigint default 0,
119 CreateDate timestamp without time zone not null,
120 ClientId int default 0,
121 Volume text not null,
122 Device text not null,
124 Retention integer default 0,
126 primary key (SnapshotId)
129 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device text_pattern_ops,
130 Volume text_pattern_ops,
131 Name text_pattern_ops);
132 UPDATE Version SET VersionId=15;
136 echo "Update of Bacula PostgreSQL tables 14 to 15 succeeded."
139 echo "Update of Bacula PostgreSQL tables 14 to 15 failed."
144 # For all versions, we need to create the Index on Media(PoolId/StorageId)
145 # It may fail, but it's not a big problem
146 psql -f - -d ${db_name} $* <<END-OF-DATA
147 set client_min_messages = fatal;
148 CREATE INDEX media_poolid_idx on Media (PoolId);
149 CREATE INDEX media_storageid_idx ON Media (StorageId);