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,
65 ObjectName TEXT NOT NULL,
66 RestoreObject TEXT NOT NULL,
67 PluginName TEXT NOT NULL,
68 ObjectLength INTEGER DEFAULT 0,
69 ObjectIndex INTEGER DEFAULT 0,
70 ObjectType INTEGER DEFAULT 0,
71 FileIndex INTEGER UNSIGNED DEFAULT 0,
72 JobId INTEGER UNSIGNED,
73 ObjectCompression INTEGER DEFAULT 0,
74 PRIMARY KEY(RestoreObjectId),
76 CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
81 JobId serial not null,
84 Type char(1) not null,
85 Level char(1) not null,
86 ClientId integer default 0,
87 JobStatus char(1) not null,
88 SchedTime timestamp without time zone,
89 StartTime timestamp without time zone,
90 EndTime timestamp without time zone,
91 RealEndTime timestamp without time zone,
92 JobTDate bigint default 0,
93 VolSessionId integer default 0,
94 volSessionTime integer default 0,
95 JobFiles integer default 0,
96 JobBytes bigint default 0,
97 ReadBytes bigint default 0,
98 JobErrors integer default 0,
99 JobMissingFiles integer default 0,
100 PoolId integer default 0,
101 FilesetId integer default 0,
102 PriorJobid integer default 0,
103 PurgedFiles smallint default 0,
104 HasBase smallint default 0,
105 HasCache smallint default 0,
106 Reviewed smallint default 0,
111 CREATE INDEX job_name_idx on job (name);
113 -- Create a table like Job for long term statistics
114 CREATE TABLE JobHisto (LIKE Job);
115 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
118 CREATE TABLE Location (
119 LocationId serial not null,
120 Location text not null,
121 Cost integer default 0,
123 primary key (LocationId)
129 filesetid serial not null,
130 fileset text not null,
132 createtime timestamp without time zone not null,
133 primary key (filesetid)
136 CREATE INDEX fileset_name_idx on fileset (fileset);
138 CREATE TABLE jobmedia
140 jobmediaid serial not null,
141 jobid integer not null,
142 mediaid integer not null,
143 firstindex integer default 0,
144 lastindex integer default 0,
145 startfile integer default 0,
146 endfile integer default 0,
147 startblock bigint default 0,
148 endblock bigint default 0,
149 volindex integer default 0,
150 primary key (jobmediaid)
153 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
157 mediaid serial not null,
158 volumename text not null,
159 slot integer default 0,
160 poolid integer default 0,
161 mediatype text not null,
162 mediatypeid integer default 0,
163 labeltype integer default 0,
164 firstwritten timestamp without time zone,
165 lastwritten timestamp without time zone,
166 labeldate timestamp without time zone,
167 voljobs integer default 0,
168 volfiles integer default 0,
169 volblocks integer default 0,
170 volmounts integer default 0,
171 volbytes bigint default 0,
172 volparts integer default 0,
173 volerrors integer default 0,
174 volwrites integer default 0,
175 volcapacitybytes bigint default 0,
176 volstatus text not null
177 check (volstatus in ('Full','Archive','Append',
178 'Recycle','Purged','Read-Only','Disabled',
179 'Error','Busy','Used','Cleaning','Scratch')),
180 enabled smallint default 1,
181 recycle smallint default 0,
182 ActionOnPurge smallint default 0,
183 volretention bigint default 0,
184 voluseduration bigint default 0,
185 maxvoljobs integer default 0,
186 maxvolfiles integer default 0,
187 maxvolbytes bigint default 0,
188 inchanger smallint default 0,
189 StorageId integer default 0,
190 DeviceId integer default 0,
191 mediaaddressing smallint default 0,
192 volreadtime bigint default 0,
193 volwritetime bigint default 0,
194 endfile integer default 0,
195 endblock bigint default 0,
196 LocationId integer default 0,
197 recyclecount integer default 0,
198 initialwrite timestamp without time zone,
199 scratchpoolid integer default 0,
200 recyclepoolid integer default 0,
202 primary key (mediaid)
205 create unique index media_volumename_id on media (volumename);
208 CREATE TABLE MediaType (
210 MediaType TEXT NOT NULL,
211 ReadOnly INTEGER DEFAULT 0,
212 PRIMARY KEY(MediaTypeId)
215 CREATE TABLE Storage (
218 AutoChanger INTEGER DEFAULT 0,
219 PRIMARY KEY(StorageId)
222 CREATE TABLE Device (
225 MediaTypeId INTEGER NOT NULL,
226 StorageId INTEGER NOT NULL,
227 DevMounts INTEGER NOT NULL DEFAULT 0,
228 DevReadBytes BIGINT NOT NULL DEFAULT 0,
229 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
230 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
231 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
232 DevReadTime BIGINT NOT NULL DEFAULT 0,
233 DevWriteTime BIGINT NOT NULL DEFAULT 0,
234 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
235 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
236 CleaningDate timestamp without time zone,
237 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
238 PRIMARY KEY(DeviceId)
244 poolid serial not null,
246 numvols integer default 0,
247 maxvols integer default 0,
248 useonce smallint default 0,
249 usecatalog smallint default 0,
250 acceptanyvolume smallint default 0,
251 volretention bigint default 0,
252 voluseduration bigint default 0,
253 maxvoljobs integer default 0,
254 maxvolfiles integer default 0,
255 maxvolbytes bigint default 0,
256 autoprune smallint default 0,
257 recycle smallint default 0,
258 ActionOnPurge smallint default 0,
260 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
261 labeltype integer default 0,
262 labelformat text not null,
263 enabled smallint default 1,
264 scratchpoolid integer default 0,
265 recyclepoolid integer default 0,
266 NextPoolId integer default 0,
267 MigrationHighBytes BIGINT DEFAULT 0,
268 MigrationLowBytes BIGINT DEFAULT 0,
269 MigrationTime BIGINT DEFAULT 0,
273 CREATE INDEX pool_name_idx on pool (name);
277 clientid serial not null,
280 autoprune smallint default 0,
281 fileretention bigint default 0,
282 jobretention bigint default 0,
283 primary key (clientid)
286 create unique index client_name_idx on client (name);
290 LogId serial not null,
291 JobId integer not null,
292 Time timestamp without time zone,
293 LogText text not null,
296 create index log_name_idx on Log (JobId);
298 CREATE TABLE LocationLog (
299 LocLogId SERIAL NOT NULL,
300 Date timestamp without time zone,
301 Comment TEXT NOT NULL,
302 MediaId INTEGER DEFAULT 0,
303 LocationId INTEGER DEFAULT 0,
304 newvolstatus text not null
305 check (newvolstatus in ('Full','Archive','Append',
306 'Recycle','Purged','Read-Only','Disabled',
307 'Error','Busy','Used','Cleaning','Scratch')),
309 PRIMARY KEY(LocLogId)
314 CREATE TABLE counters
316 counter text not null,
317 minvalue integer default 0,
318 maxvalue integer default 0,
319 currentvalue integer default 0,
320 wrapcounter text not null,
321 primary key (counter)
326 CREATE TABLE basefiles
328 baseid serial not null,
329 jobid integer not null,
330 fileid bigint not null,
336 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
338 CREATE TABLE unsavedfiles
340 UnsavedId integer not null,
341 jobid integer not null,
342 pathid integer not null,
343 filenameid integer not null,
344 primary key (UnsavedId)
347 CREATE TABLE CDImages
349 MediaId integer not null,
350 LastBurn timestamp without time zone not null,
351 primary key (MediaId)
355 CREATE TABLE PathHierarchy
357 PathId integer NOT NULL,
358 PPathId integer NOT NULL,
359 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
362 CREATE INDEX pathhierarchy_ppathid
363 ON PathHierarchy (PPathId);
365 CREATE TABLE PathVisibility
367 PathId integer NOT NULL,
368 JobId integer NOT NULL,
370 Files int4 DEFAULT 0,
371 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
373 CREATE INDEX pathvisibility_jobid
374 ON PathVisibility (JobId);
378 versionid integer not null
381 CREATE TABLE Status (
382 JobStatus CHAR(1) NOT NULL,
385 PRIMARY KEY (JobStatus)
388 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
389 ('C', 'Created, not yet running',15);
390 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
392 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
394 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
395 ('T', 'Completed successfully', 10);
396 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
397 ('E', 'Terminated with errors', 25);
398 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
399 ('e', 'Non-fatal error',20);
400 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
401 ('f', 'Fatal error',100);
402 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
403 ('D', 'Verify found differences',15);
404 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
405 ('A', 'Canceled by user',90);
406 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
407 ('F', 'Waiting for Client',15);
408 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
409 ('S', 'Waiting for Storage daemon',15);
410 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
411 ('m', 'Waiting for new media');
412 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
413 ('M', 'Waiting for media mount',15);
414 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
415 ('s', 'Waiting for storage resource',15);
416 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
417 ('j', 'Waiting for job resource',15);
418 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
419 ('c', 'Waiting for client resource',15);
420 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
421 ('d', 'Waiting on maximum jobs',15);
422 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
423 ('t', 'Waiting on start time',15);
424 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
425 ('p', 'Waiting on higher priority jobs',15);
426 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
427 ('a', 'SD despooling attributes',15);
428 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
429 ('i', 'Doing batch insert file records',15);
431 INSERT INTO Version (VersionId) VALUES (12);
433 -- Make sure we have appropriate permissions
440 echo "Creation of Bacula PostgreSQL tables succeeded."
442 echo "Creation of Bacula PostgreSQL tables failed."