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 Bacula Community version
7#  5.0.x, 5.2.x, 7.0.x, 7.2.x, 7.4.x
8#
9echo " "
10echo "This script will update a Bacula PostgreSQL database from version 12-15 to 16"
11echo " "
12echo "Depending on the current version of your catalog,"
13echo "you may have to run this script multiple times."
14echo " "
15
16bindir=@POSTGRESQL_BINDIR@
17PATH="$bindir:$PATH"
18db_name=@db_name@
19
20ARGS=$*
21
22getVersion()
23{
24    DBVERSION=`psql -d ${db_name} -t --pset format=unaligned -c "select VersionId from Version LIMIT 1" $ARGS`
25}
26
27getVersion
28
29if [ "x$DBVERSION" = x ]; then
30    echo
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."
34    exit 1
35fi
36
37if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 15 ] ; then
38    echo " "
39    echo "The existing database is version $DBVERSION !!"
40    echo "This script can only update an existing version 12-15 database to version 16."
41    echo "Error. Cannot upgrade this database."
42    echo " "
43    exit 1
44fi
45
46if [ "$DBVERSION" -eq 12 ] ; then
47    # from 5.0
48    if psql -f - -d ${db_name} $* <<END-OF-DATA
49BEGIN; -- Necessary for Bacula core
50CREATE 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,
60   JobId INTEGER,
61   ObjectCompression INTEGER DEFAULT 0,
62   PRIMARY KEY(RestoreObjectId)
63   );
64
65CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
66UPDATE Version SET VersionId=13;
67
68COMMIT;
69END-OF-DATA
70    then
71	echo "Update of Bacula PostgreSQL tables 12 to 13 succeeded."
72	getVersion
73    else
74	echo "Update of Bacula PostgreSQL tables 12 to 13 failed."
75	exit 1
76    fi
77fi
78
79if [ "$DBVERSION" -eq 13 ] ; then
80    # from 4.0
81    if psql -f - -d ${db_name} $* <<END-OF-DATA
82BEGIN; -- Necessary for Bacula core
83
84ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0;
85
86UPDATE Version SET VersionId=14;
87COMMIT;
88
89-- ANALYSE;
90
91END-OF-DATA
92    then
93	echo "Update of Bacula PostgreSQL tables from 13 to 14 succeeded."
94	getVersion
95    else
96	echo "Update of Bacula PostgreSQL tables failed."
97	exit 1
98    fi
99fi
100
101
102if [ "$DBVERSION" -eq 14 ] ; then
103    # from 5.2
104    if psql -f - -d ${db_name} $* <<END-OF-DATA
105INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
106   ('I', 'Incomplete Job',25);
107ALTER TABLE Media ADD COLUMN volabytes bigint default 0;
108ALTER TABLE Media ADD COLUMN volapadding bigint default 0;
109ALTER TABLE Media ADD COLUMN volholebytes bigint default 0;
110ALTER TABLE Media ADD COLUMN volholes integer default 0;
111ALTER TABLE Media ALTER VolWrites TYPE BIGINT;
112
113CREATE TABLE Snapshot (
114  SnapshotId	  serial,
115  Name		  text not null,
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,
123  Type		  text not null,
124  Retention	  integer default 0,
125  Comment	  text,
126  primary key (SnapshotId)
127);
128
129CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device text_pattern_ops, 
130					      Volume text_pattern_ops, 
131					      Name text_pattern_ops);
132UPDATE Version SET VersionId=15;
133
134END-OF-DATA
135    then
136	echo "Update of Bacula PostgreSQL tables 14 to 15 succeeded."
137	getVersion
138    else
139	echo "Update of Bacula PostgreSQL tables 14 to 15 failed."
140	exit 1
141    fi
142fi
143
144if [ "$DBVERSION" -eq 15 ] ; then
145    if psql -f - -d ${db_name} $* <<END-OF-DATA
146begin;
147ALTER TABLE basefiles ALTER COLUMN baseid SET DATA TYPE bigint;
148ALTER TABLE media RENAME COLUMN volparts TO voltype;
149ALTER TABLE media ADD COLUMN volparts INTEGER DEFAULT 0;
150ALTER TABLE media ADD COLUMN volcloudparts INTEGER DEFAULT 0;
151ALTER TABLE media ADD COLUMN lastpartbytes BIGINT DEFAULT 0;
152ALTER TABLE media ADD COLUMN cacheretention BIGINT DEFAULT 0;
153ALTER TABLE pool ADD COLUMN cacheretention BIGINT DEFAULT 0;
154CREATE INDEX job_jobtdate_idx ON job (jobtdate);
155
156UPDATE Version SET VersionId=16;
157commit;
158END-OF-DATA
159    then
160	echo "Update of Bacula PostgreSQL tables 15 to 16 succeeded."
161	getVersion
162    else
163	echo "Update of Bacula PostgreSQL tables 15 to 16 failed."
164	exit 1
165    fi
166fi
167
168
169# For all versions, we need to create the Index on Media(PoolId/StorageId)
170# It may fail, but it's not a big problem
171psql -f - -d ${db_name} $* <<END-OF-DATA
172set client_min_messages = fatal;
173CREATE INDEX media_poolid_idx on Media (PoolId);
174CREATE INDEX media_storageid_idx ON Media (StorageId);
175END-OF-DATA
176