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);
65 JobId serial not null,
68 Type char(1) not null,
69 Level char(1) not null,
70 ClientId integer default 0,
71 JobStatus char(1) not null,
72 SchedTime timestamp without time zone,
73 StartTime timestamp without time zone,
74 EndTime timestamp without time zone,
75 RealEndTime timestamp without time zone,
76 JobTDate bigint default 0,
77 VolSessionId integer default 0,
78 volSessionTime integer default 0,
79 JobFiles integer default 0,
80 JobBytes bigint default 0,
81 ReadBytes bigint default 0,
82 JobErrors integer default 0,
83 JobMissingFiles integer default 0,
84 PoolId integer default 0,
85 FilesetId integer default 0,
86 PriorJobid integer default 0,
87 PurgedFiles smallint default 0,
88 HasBase smallint default 0,
89 HasCache smallint default 0,
90 Reviewed smallint default 0,
95 CREATE INDEX job_name_idx on job (name);
97 -- Create a table like Job for long term statistics
98 CREATE TABLE JobHisto (LIKE Job);
99 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
102 CREATE TABLE Location (
103 LocationId serial not null,
104 Location text not null,
105 Cost integer default 0,
107 primary key (LocationId)
113 filesetid serial not null,
114 fileset text not null,
116 createtime timestamp without time zone not null,
117 primary key (filesetid)
120 CREATE INDEX fileset_name_idx on fileset (fileset);
122 CREATE TABLE jobmedia
124 jobmediaid serial not null,
125 jobid integer not null,
126 mediaid integer not null,
127 firstindex integer default 0,
128 lastindex integer default 0,
129 startfile integer default 0,
130 endfile integer default 0,
131 startblock bigint default 0,
132 endblock bigint default 0,
133 volindex integer default 0,
134 primary key (jobmediaid)
137 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
141 mediaid serial not null,
142 volumename text not null,
143 slot integer default 0,
144 poolid integer default 0,
145 mediatype text not null,
146 mediatypeid integer default 0,
147 labeltype integer default 0,
148 firstwritten timestamp without time zone,
149 lastwritten timestamp without time zone,
150 labeldate timestamp without time zone,
151 voljobs integer default 0,
152 volfiles integer default 0,
153 volblocks integer default 0,
154 volmounts integer default 0,
155 volbytes bigint default 0,
156 volparts integer default 0,
157 volerrors integer default 0,
158 volwrites integer default 0,
159 volcapacitybytes bigint default 0,
160 volstatus text not null
161 check (volstatus in ('Full','Archive','Append',
162 'Recycle','Purged','Read-Only','Disabled',
163 'Error','Busy','Used','Cleaning','Scratch')),
164 enabled smallint default 1,
165 recycle smallint default 0,
166 ActionOnPurge smallint default 0,
167 volretention bigint default 0,
168 voluseduration bigint default 0,
169 maxvoljobs integer default 0,
170 maxvolfiles integer default 0,
171 maxvolbytes bigint default 0,
172 inchanger smallint default 0,
173 StorageId integer default 0,
174 DeviceId integer default 0,
175 mediaaddressing smallint default 0,
176 volreadtime bigint default 0,
177 volwritetime bigint default 0,
178 endfile integer default 0,
179 endblock bigint default 0,
180 LocationId integer default 0,
181 recyclecount integer default 0,
182 initialwrite timestamp without time zone,
183 scratchpoolid integer default 0,
184 recyclepoolid integer default 0,
186 primary key (mediaid)
189 create unique index media_volumename_id on media (volumename);
192 CREATE TABLE MediaType (
194 MediaType TEXT NOT NULL,
195 ReadOnly INTEGER DEFAULT 0,
196 PRIMARY KEY(MediaTypeId)
199 CREATE TABLE Storage (
202 AutoChanger INTEGER DEFAULT 0,
203 PRIMARY KEY(StorageId)
206 CREATE TABLE Device (
209 MediaTypeId INTEGER NOT NULL,
210 StorageId INTEGER NOT NULL,
211 DevMounts INTEGER NOT NULL DEFAULT 0,
212 DevReadBytes BIGINT NOT NULL DEFAULT 0,
213 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
214 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
215 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
216 DevReadTime BIGINT NOT NULL DEFAULT 0,
217 DevWriteTime BIGINT NOT NULL DEFAULT 0,
218 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
219 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
220 CleaningDate timestamp without time zone,
221 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
222 PRIMARY KEY(DeviceId)
228 poolid serial not null,
230 numvols integer default 0,
231 maxvols integer default 0,
232 useonce smallint default 0,
233 usecatalog smallint default 0,
234 acceptanyvolume smallint default 0,
235 volretention bigint default 0,
236 voluseduration bigint default 0,
237 maxvoljobs integer default 0,
238 maxvolfiles integer default 0,
239 maxvolbytes bigint default 0,
240 autoprune smallint default 0,
241 recycle smallint default 0,
242 ActionOnPurge smallint default 0,
244 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
245 labeltype integer default 0,
246 labelformat text not null,
247 enabled smallint default 1,
248 scratchpoolid integer default 0,
249 recyclepoolid integer default 0,
250 NextPoolId integer default 0,
251 MigrationHighBytes BIGINT DEFAULT 0,
252 MigrationLowBytes BIGINT DEFAULT 0,
253 MigrationTime BIGINT DEFAULT 0,
257 CREATE INDEX pool_name_idx on pool (name);
261 clientid serial not null,
264 autoprune smallint default 0,
265 fileretention bigint default 0,
266 jobretention bigint default 0,
267 primary key (clientid)
270 create unique index client_name_idx on client (name);
274 LogId serial not null,
275 JobId integer not null,
276 Time timestamp without time zone,
277 LogText text not null,
280 create index log_name_idx on Log (JobId);
282 CREATE TABLE LocationLog (
283 LocLogId SERIAL NOT NULL,
284 Date timestamp without time zone,
285 Comment TEXT NOT NULL,
286 MediaId INTEGER DEFAULT 0,
287 LocationId INTEGER DEFAULT 0,
288 newvolstatus text not null
289 check (newvolstatus in ('Full','Archive','Append',
290 'Recycle','Purged','Read-Only','Disabled',
291 'Error','Busy','Used','Cleaning','Scratch')),
293 PRIMARY KEY(LocLogId)
298 CREATE TABLE counters
300 counter text not null,
301 minvalue integer default 0,
302 maxvalue integer default 0,
303 currentvalue integer default 0,
304 wrapcounter text not null,
305 primary key (counter)
310 CREATE TABLE basefiles
312 baseid serial not null,
313 jobid integer not null,
314 fileid bigint not null,
320 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
322 CREATE TABLE unsavedfiles
324 UnsavedId integer not null,
325 jobid integer not null,
326 pathid integer not null,
327 filenameid integer not null,
328 primary key (UnsavedId)
331 CREATE TABLE CDImages
333 MediaId integer not null,
334 LastBurn timestamp without time zone not null,
335 primary key (MediaId)
339 CREATE TABLE PathHierarchy
341 PathId integer NOT NULL,
342 PPathId integer NOT NULL,
343 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
346 CREATE INDEX pathhierarchy_ppathid
347 ON PathHierarchy (PPathId);
349 CREATE TABLE PathVisibility
351 PathId integer NOT NULL,
352 JobId integer NOT NULL,
354 Files int4 DEFAULT 0,
355 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
357 CREATE INDEX pathvisibility_jobid
358 ON PathVisibility (JobId);
362 versionid integer not null
365 CREATE TABLE Status (
366 JobStatus CHAR(1) NOT NULL,
369 PRIMARY KEY (JobStatus)
372 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
373 ('C', 'Created, not yet running',15);
374 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
376 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
378 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
379 ('T', 'Completed successfully', 10);
380 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
381 ('E', 'Terminated with errors', 25);
382 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
383 ('e', 'Non-fatal error',20);
384 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
385 ('f', 'Fatal error',100);
386 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
387 ('D', 'Verify found differences',15);
388 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
389 ('A', 'Canceled by user',90);
390 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
391 ('F', 'Waiting for Client',15);
392 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
393 ('S', 'Waiting for Storage daemon',15);
394 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
395 ('m', 'Waiting for new media');
396 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
397 ('M', 'Waiting for media mount',15);
398 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
399 ('s', 'Waiting for storage resource',15);
400 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
401 ('j', 'Waiting for job resource',15);
402 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
403 ('c', 'Waiting for client resource',15);
404 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
405 ('d', 'Waiting on maximum jobs',15);
406 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
407 ('t', 'Waiting on start time',15);
408 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
409 ('p', 'Waiting on higher priority jobs',15);
410 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
411 ('a', 'SD despooling attributes',15);
412 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
413 ('i', 'Doing batch insert file records',15);
415 INSERT INTO Version (VersionId) VALUES (12);
417 -- Make sure we have appropriate permissions
424 echo "Creation of Bacula PostgreSQL tables succeeded."
426 echo "Creation of Bacula PostgreSQL tables failed."