3 # shell script to create Bacula PostgreSQL tables
7 db_name=${db_name:-@db_name@}
9 psql -f - -d ${db_name} $* <<END-OF-DATA
13 filenameid serial not null,
15 primary key (filenameid)
18 ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000;
19 CREATE UNIQUE INDEX filename_name_idx on filename (name);
23 pathid serial not null,
28 ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000;
29 CREATE UNIQUE INDEX path_name_idx on path (path);
33 fileid bigserial not null,
34 fileindex integer not null default 0,
35 jobid integer not null,
36 pathid integer not null,
37 filenameid integer not null,
38 markid integer not null default 0,
44 CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId);
46 -- If you need performances, you can remove this index
47 -- the database engine is able to use the composite index
48 -- to find all records with a given JobId
49 CREATE INDEX file_jobid_idx on file(jobid);
52 -- Add this if you have a good number of job
53 -- that run at the same time
54 -- ALTER SEQUENCE file_fileid_seq CACHE 1000;
57 -- Possibly add one or more of the following indexes
58 -- if your Verifies are too slow.
60 -- CREATE INDEX file_pathid_idx on file(pathid);
61 -- CREATE INDEX file_filenameid_idx on file(filenameid);
63 CREATE TABLE RestoreObject (
64 RestoreObjectId SERIAL NOT NULL,
67 RestoreObject TEXT NOT NULL,
68 PluginName TEXT NOT NULL,
69 ObjectIndex INTEGER DEFAULT 0,
70 ObjectType INTEGER DEFAULT 0,
71 FileIndex INTEGER UNSIGNED DEFAULT 0,
72 JobId INTEGER UNSIGNED,
73 PRIMARY KEY(RestoreObjectId),
75 CREATE INDEX restore_jobid_idx on file(JobId);
80 JobId serial not null,
83 Type char(1) not null,
84 Level char(1) not null,
85 ClientId integer default 0,
86 JobStatus char(1) not null,
87 SchedTime timestamp without time zone,
88 StartTime timestamp without time zone,
89 EndTime timestamp without time zone,
90 RealEndTime timestamp without time zone,
91 JobTDate bigint default 0,
92 VolSessionId integer default 0,
93 volSessionTime integer default 0,
94 JobFiles integer default 0,
95 JobBytes bigint default 0,
96 ReadBytes bigint default 0,
97 JobErrors integer default 0,
98 JobMissingFiles integer default 0,
99 PoolId integer default 0,
100 FilesetId integer default 0,
101 PriorJobid integer default 0,
102 PurgedFiles smallint default 0,
103 HasBase smallint default 0,
104 HasCache smallint default 0,
105 Reviewed smallint default 0,
110 CREATE INDEX job_name_idx on job (name);
112 -- Create a table like Job for long term statistics
113 CREATE TABLE JobHisto (LIKE Job);
114 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
117 CREATE TABLE Location (
118 LocationId serial not null,
119 Location text not null,
120 Cost integer default 0,
122 primary key (LocationId)
128 filesetid serial not null,
129 fileset text not null,
131 createtime timestamp without time zone not null,
132 primary key (filesetid)
135 CREATE INDEX fileset_name_idx on fileset (fileset);
137 CREATE TABLE jobmedia
139 jobmediaid serial not null,
140 jobid integer not null,
141 mediaid integer not null,
142 firstindex integer default 0,
143 lastindex integer default 0,
144 startfile integer default 0,
145 endfile integer default 0,
146 startblock bigint default 0,
147 endblock bigint default 0,
148 volindex integer default 0,
149 primary key (jobmediaid)
152 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
156 mediaid serial not null,
157 volumename text not null,
158 slot integer default 0,
159 poolid integer default 0,
160 mediatype text not null,
161 mediatypeid integer default 0,
162 labeltype integer default 0,
163 firstwritten timestamp without time zone,
164 lastwritten timestamp without time zone,
165 labeldate timestamp without time zone,
166 voljobs integer default 0,
167 volfiles integer default 0,
168 volblocks integer default 0,
169 volmounts integer default 0,
170 volbytes bigint default 0,
171 volparts integer default 0,
172 volerrors integer default 0,
173 volwrites integer default 0,
174 volcapacitybytes bigint default 0,
175 volstatus text not null
176 check (volstatus in ('Full','Archive','Append',
177 'Recycle','Purged','Read-Only','Disabled',
178 'Error','Busy','Used','Cleaning','Scratch')),
179 enabled smallint default 1,
180 recycle smallint default 0,
181 ActionOnPurge smallint default 0,
182 volretention bigint default 0,
183 voluseduration bigint default 0,
184 maxvoljobs integer default 0,
185 maxvolfiles integer default 0,
186 maxvolbytes bigint default 0,
187 inchanger smallint default 0,
188 StorageId integer default 0,
189 DeviceId integer default 0,
190 mediaaddressing smallint default 0,
191 volreadtime bigint default 0,
192 volwritetime bigint default 0,
193 endfile integer default 0,
194 endblock bigint default 0,
195 LocationId integer default 0,
196 recyclecount integer default 0,
197 initialwrite timestamp without time zone,
198 scratchpoolid integer default 0,
199 recyclepoolid integer default 0,
201 primary key (mediaid)
204 create unique index media_volumename_id on media (volumename);
207 CREATE TABLE MediaType (
209 MediaType TEXT NOT NULL,
210 ReadOnly INTEGER DEFAULT 0,
211 PRIMARY KEY(MediaTypeId)
214 CREATE TABLE Storage (
217 AutoChanger INTEGER DEFAULT 0,
218 PRIMARY KEY(StorageId)
221 CREATE TABLE Device (
224 MediaTypeId INTEGER NOT NULL,
225 StorageId INTEGER NOT NULL,
226 DevMounts INTEGER NOT NULL DEFAULT 0,
227 DevReadBytes BIGINT NOT NULL DEFAULT 0,
228 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
229 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
230 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
231 DevReadTime BIGINT NOT NULL DEFAULT 0,
232 DevWriteTime BIGINT NOT NULL DEFAULT 0,
233 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
234 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
235 CleaningDate timestamp without time zone,
236 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
237 PRIMARY KEY(DeviceId)
243 poolid serial not null,
245 numvols integer default 0,
246 maxvols integer default 0,
247 useonce smallint default 0,
248 usecatalog smallint default 0,
249 acceptanyvolume smallint default 0,
250 volretention bigint default 0,
251 voluseduration bigint default 0,
252 maxvoljobs integer default 0,
253 maxvolfiles integer default 0,
254 maxvolbytes bigint default 0,
255 autoprune smallint default 0,
256 recycle smallint default 0,
257 ActionOnPurge smallint default 0,
259 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
260 labeltype integer default 0,
261 labelformat text not null,
262 enabled smallint default 1,
263 scratchpoolid integer default 0,
264 recyclepoolid integer default 0,
265 NextPoolId integer default 0,
266 MigrationHighBytes BIGINT DEFAULT 0,
267 MigrationLowBytes BIGINT DEFAULT 0,
268 MigrationTime BIGINT DEFAULT 0,
272 CREATE INDEX pool_name_idx on pool (name);
276 clientid serial not null,
279 autoprune smallint default 0,
280 fileretention bigint default 0,
281 jobretention bigint default 0,
282 primary key (clientid)
285 create unique index client_name_idx on client (name);
289 LogId serial not null,
290 JobId integer not null,
291 Time timestamp without time zone,
292 LogText text not null,
295 create index log_name_idx on Log (JobId);
297 CREATE TABLE LocationLog (
298 LocLogId SERIAL NOT NULL,
299 Date timestamp without time zone,
300 Comment TEXT NOT NULL,
301 MediaId INTEGER DEFAULT 0,
302 LocationId INTEGER DEFAULT 0,
303 newvolstatus text not null
304 check (newvolstatus in ('Full','Archive','Append',
305 'Recycle','Purged','Read-Only','Disabled',
306 'Error','Busy','Used','Cleaning','Scratch')),
308 PRIMARY KEY(LocLogId)
313 CREATE TABLE counters
315 counter text not null,
316 minvalue integer default 0,
317 maxvalue integer default 0,
318 currentvalue integer default 0,
319 wrapcounter text not null,
320 primary key (counter)
325 CREATE TABLE basefiles
327 baseid serial not null,
328 jobid integer not null,
329 fileid bigint not null,
335 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
337 CREATE TABLE unsavedfiles
339 UnsavedId integer not null,
340 jobid integer not null,
341 pathid integer not null,
342 filenameid integer not null,
343 primary key (UnsavedId)
346 CREATE TABLE CDImages
348 MediaId integer not null,
349 LastBurn timestamp without time zone not null,
350 primary key (MediaId)
354 CREATE TABLE PathHierarchy
356 PathId integer NOT NULL,
357 PPathId integer NOT NULL,
358 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
361 CREATE INDEX pathhierarchy_ppathid
362 ON PathHierarchy (PPathId);
364 CREATE TABLE PathVisibility
366 PathId integer NOT NULL,
367 JobId integer NOT NULL,
369 Files int4 DEFAULT 0,
370 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
372 CREATE INDEX pathvisibility_jobid
373 ON PathVisibility (JobId);
377 versionid integer not null
380 CREATE TABLE Status (
381 JobStatus CHAR(1) NOT NULL,
384 PRIMARY KEY (JobStatus)
387 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
388 ('C', 'Created, not yet running',15);
389 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
391 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
393 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
394 ('T', 'Completed successfully', 10);
395 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
396 ('E', 'Terminated with errors', 25);
397 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
398 ('e', 'Non-fatal error',20);
399 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
400 ('f', 'Fatal error',100);
401 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
402 ('D', 'Verify found differences',15);
403 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
404 ('A', 'Canceled by user',90);
405 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
406 ('F', 'Waiting for Client',15);
407 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
408 ('S', 'Waiting for Storage daemon',15);
409 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
410 ('m', 'Waiting for new media');
411 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
412 ('M', 'Waiting for media mount',15);
413 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
414 ('s', 'Waiting for storage resource',15);
415 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
416 ('j', 'Waiting for job resource',15);
417 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
418 ('c', 'Waiting for client resource',15);
419 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
420 ('d', 'Waiting on maximum jobs',15);
421 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
422 ('t', 'Waiting on start time',15);
423 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
424 ('p', 'Waiting on higher priority jobs',15);
425 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
426 ('a', 'SD despooling attributes',15);
427 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
428 ('i', 'Doing batch insert file records',15);
430 INSERT INTO Version (VersionId) VALUES (12);
432 -- Make sure we have appropriate permissions
439 echo "Creation of Bacula PostgreSQL tables succeeded."
441 echo "Creation of Bacula PostgreSQL tables failed."