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,
94 CREATE INDEX job_name_idx on job (name);
96 -- Create a table like Job for long term statistics
97 CREATE TABLE JobHisto (LIKE Job);
98 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
101 CREATE TABLE Location (
102 LocationId serial not null,
103 Location text not null,
104 Cost integer default 0,
106 primary key (LocationId)
112 filesetid serial not null,
113 fileset text not null,
115 createtime timestamp without time zone not null,
116 primary key (filesetid)
119 CREATE INDEX fileset_name_idx on fileset (fileset);
121 CREATE TABLE jobmedia
123 jobmediaid serial not null,
124 jobid integer not null,
125 mediaid integer not null,
126 firstindex integer default 0,
127 lastindex integer default 0,
128 startfile integer default 0,
129 endfile integer default 0,
130 startblock bigint default 0,
131 endblock bigint default 0,
132 volindex integer default 0,
133 primary key (jobmediaid)
136 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
140 mediaid serial not null,
141 volumename text not null,
142 slot integer default 0,
143 poolid integer default 0,
144 mediatype text not null,
145 mediatypeid integer default 0,
146 labeltype integer default 0,
147 firstwritten timestamp without time zone,
148 lastwritten timestamp without time zone,
149 labeldate timestamp without time zone,
150 voljobs integer default 0,
151 volfiles integer default 0,
152 volblocks integer default 0,
153 volmounts integer default 0,
154 volbytes bigint default 0,
155 volparts integer default 0,
156 volerrors integer default 0,
157 volwrites integer default 0,
158 volcapacitybytes bigint default 0,
159 volstatus text not null
160 check (volstatus in ('Full','Archive','Append',
161 'Recycle','Purged','Read-Only','Disabled',
162 'Error','Busy','Used','Cleaning','Scratch')),
163 enabled smallint default 1,
164 recycle smallint default 0,
165 ActionOnPurge smallint default 0,
166 volretention bigint default 0,
167 voluseduration bigint default 0,
168 maxvoljobs integer default 0,
169 maxvolfiles integer default 0,
170 maxvolbytes bigint default 0,
171 inchanger smallint default 0,
172 StorageId integer default 0,
173 DeviceId integer default 0,
174 mediaaddressing smallint default 0,
175 volreadtime bigint default 0,
176 volwritetime bigint default 0,
177 endfile integer default 0,
178 endblock bigint default 0,
179 LocationId integer default 0,
180 recyclecount integer default 0,
181 initialwrite timestamp without time zone,
182 scratchpoolid integer default 0,
183 recyclepoolid integer default 0,
185 primary key (mediaid)
188 create unique index media_volumename_id on media (volumename);
191 CREATE TABLE MediaType (
193 MediaType TEXT NOT NULL,
194 ReadOnly INTEGER DEFAULT 0,
195 PRIMARY KEY(MediaTypeId)
198 CREATE TABLE Storage (
201 AutoChanger INTEGER DEFAULT 0,
202 PRIMARY KEY(StorageId)
205 CREATE TABLE Device (
208 MediaTypeId INTEGER NOT NULL,
209 StorageId INTEGER NOT NULL,
210 DevMounts INTEGER NOT NULL DEFAULT 0,
211 DevReadBytes BIGINT NOT NULL DEFAULT 0,
212 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
213 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
214 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
215 DevReadTime BIGINT NOT NULL DEFAULT 0,
216 DevWriteTime BIGINT NOT NULL DEFAULT 0,
217 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
218 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
219 CleaningDate timestamp without time zone,
220 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
221 PRIMARY KEY(DeviceId)
227 poolid serial not null,
229 numvols integer default 0,
230 maxvols integer default 0,
231 useonce smallint default 0,
232 usecatalog smallint default 0,
233 acceptanyvolume smallint default 0,
234 volretention bigint default 0,
235 voluseduration bigint default 0,
236 maxvoljobs integer default 0,
237 maxvolfiles integer default 0,
238 maxvolbytes bigint default 0,
239 autoprune smallint default 0,
240 recycle smallint default 0,
241 ActionOnPurge smallint default 0,
243 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
244 labeltype integer default 0,
245 labelformat text not null,
246 enabled smallint default 1,
247 scratchpoolid integer default 0,
248 recyclepoolid integer default 0,
249 NextPoolId integer default 0,
250 MigrationHighBytes BIGINT DEFAULT 0,
251 MigrationLowBytes BIGINT DEFAULT 0,
252 MigrationTime BIGINT DEFAULT 0,
256 CREATE INDEX pool_name_idx on pool (name);
260 clientid serial not null,
263 autoprune smallint default 0,
264 fileretention bigint default 0,
265 jobretention bigint default 0,
266 primary key (clientid)
269 create unique index client_name_idx on client (name);
273 LogId serial not null,
274 JobId integer not null,
275 Time timestamp without time zone,
276 LogText text not null,
279 create index log_name_idx on Log (JobId);
281 CREATE TABLE LocationLog (
282 LocLogId SERIAL NOT NULL,
283 Date timestamp without time zone,
284 Comment TEXT NOT NULL,
285 MediaId INTEGER DEFAULT 0,
286 LocationId INTEGER DEFAULT 0,
287 newvolstatus text not null
288 check (newvolstatus in ('Full','Archive','Append',
289 'Recycle','Purged','Read-Only','Disabled',
290 'Error','Busy','Used','Cleaning','Scratch')),
292 PRIMARY KEY(LocLogId)
297 CREATE TABLE counters
299 counter text not null,
300 minvalue integer default 0,
301 maxvalue integer default 0,
302 currentvalue integer default 0,
303 wrapcounter text not null,
304 primary key (counter)
309 CREATE TABLE basefiles
311 baseid serial not null,
312 jobid integer not null,
313 fileid bigint not null,
319 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
321 CREATE TABLE unsavedfiles
323 UnsavedId integer not null,
324 jobid integer not null,
325 pathid integer not null,
326 filenameid integer not null,
327 primary key (UnsavedId)
330 CREATE TABLE CDImages
332 MediaId integer not null,
333 LastBurn timestamp without time zone not null,
334 primary key (MediaId)
338 CREATE TABLE PathHierarchy
340 PathId integer NOT NULL,
341 PPathId integer NOT NULL,
342 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
345 CREATE INDEX pathhierarchy_ppathid
346 ON PathHierarchy (PPathId);
348 CREATE TABLE PathVisibility
350 PathId integer NOT NULL,
351 JobId integer NOT NULL,
353 Files int4 DEFAULT 0,
354 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
356 CREATE INDEX pathvisibility_jobid
357 ON PathVisibility (JobId);
361 versionid integer not null
364 CREATE TABLE Status (
365 JobStatus CHAR(1) NOT NULL,
368 PRIMARY KEY (JobStatus)
371 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
372 ('C', 'Created, not yet running',15);
373 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
375 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
377 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
378 ('T', 'Completed successfully', 10);
379 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
380 ('E', 'Terminated with errors', 25);
381 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
382 ('e', 'Non-fatal error',20);
383 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
384 ('f', 'Fatal error',100);
385 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
386 ('D', 'Verify found differences',15);
387 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
388 ('A', 'Canceled by user',90);
389 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
390 ('F', 'Waiting for Client',15);
391 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
392 ('S', 'Waiting for Storage daemon',15);
393 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
394 ('m', 'Waiting for new media');
395 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
396 ('M', 'Waiting for media mount',15);
397 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
398 ('s', 'Waiting for storage resource',15);
399 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
400 ('j', 'Waiting for job resource',15);
401 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
402 ('c', 'Waiting for client resource',15);
403 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
404 ('d', 'Waiting on maximum jobs',15);
405 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
406 ('t', 'Waiting on start time',15);
407 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
408 ('p', 'Waiting on higher priority jobs',15);
409 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
410 ('a', 'SD despooling attributes',15);
411 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
412 ('i', 'Doing batch insert file records',15);
414 INSERT INTO Version (VersionId) VALUES (12);
416 -- Make sure we have appropriate permissions
423 echo "Creation of Bacula PostgreSQL tables succeeded."
425 echo "Creation of Bacula PostgreSQL tables failed."