3 # shell script to create Bacula PostgreSQL tables
5 # Copyright (C) 2000-2015 Kern Sibbald
6 # License: BSD 2-Clause; see file LICENSE-FOSS
9 # You won't get any support for performance issue if you changed the default
12 bindir=@POSTGRESQL_BINDIR@
14 db_name=${db_name:-@db_name@}
16 psql -f - -d ${db_name} $* <<END-OF-DATA
20 FilenameId serial not null,
22 primary key (FilenameId)
25 ALTER TABLE Filename ALTER COLUMN Name SET STATISTICS 1000;
26 CREATE UNIQUE INDEX filename_name_idx on Filename (Name text_pattern_ops);
30 PathId serial not null,
35 ALTER TABLE Path ALTER COLUMN Path SET STATISTICS 1000;
36 CREATE UNIQUE INDEX path_name_idx on Path (Path text_pattern_ops);
38 -- We strongly recommend to avoid the temptation to add new indexes.
39 -- In general, these will cause very significant performance
40 -- problems in other areas. A better approch is to carefully check
41 -- that all your memory configuation parameters are
42 -- suitable for the size of your installation. If you backup
43 -- millions of files, you need to adapt the database memory
44 -- configuration parameters concerning sorting, joining and global
45 -- memory. By default, sort and join parameters are very small
46 -- (sometimes 8Kb), and having sufficient memory specified by those
47 -- parameters is extremely important to run fast.
50 -- FileIndex can be 0 for FT_DELETED files
51 -- FileNameId can link to Filename.Name='' for directories
54 FileId bigserial not null,
55 FileIndex integer not null default 0,
56 JobId integer not null,
57 PathId integer not null,
58 FilenameId integer not null,
59 DeltaSeq smallint not null default 0,
60 MarkId integer not null default 0,
66 CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId);
67 CREATE INDEX file_jobid_idx on File (JobId);
70 -- Add this if you have a good number of job
71 -- that run at the same time
72 -- ALTER SEQUENCE file_fileid_seq CACHE 10;
75 -- Possibly add one or more of the following indexes
76 -- if your Verifies are too slow, but they can slow down
79 -- CREATE INDEX file_pathid_idx on file(pathid);
80 -- CREATE INDEX file_filenameid_idx on file(filenameid);
82 CREATE TABLE RestoreObject (
83 RestoreObjectId SERIAL NOT NULL,
84 ObjectName TEXT NOT NULL,
85 RestoreObject BYTEA NOT NULL,
86 PluginName TEXT NOT NULL,
87 ObjectLength INTEGER DEFAULT 0,
88 ObjectFullLength INTEGER DEFAULT 0,
89 ObjectIndex INTEGER DEFAULT 0,
90 ObjectType INTEGER DEFAULT 0,
91 FileIndex INTEGER DEFAULT 0,
93 ObjectCompression INTEGER DEFAULT 0,
94 PRIMARY KEY(RestoreObjectId)
96 CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
101 JobId serial not null,
104 Type char(1) not null,
105 Level char(1) not null,
106 ClientId integer default 0,
107 JobStatus char(1) not null,
108 SchedTime timestamp without time zone,
109 StartTime timestamp without time zone,
110 EndTime timestamp without time zone,
111 RealEndTime timestamp without time zone,
112 JobTDate bigint default 0,
113 VolSessionId integer default 0,
114 volSessionTime integer default 0,
115 JobFiles integer default 0,
116 JobBytes bigint default 0,
117 ReadBytes bigint default 0,
118 JobErrors integer default 0,
119 JobMissingFiles integer default 0,
120 PoolId integer default 0,
121 FilesetId integer default 0,
122 PriorJobid integer default 0,
123 PurgedFiles smallint default 0,
124 HasBase smallint default 0,
125 HasCache smallint default 0,
126 Reviewed smallint default 0,
128 FileTable text default 'File',
132 CREATE INDEX job_name_idx on job (name text_pattern_ops);
134 -- Create a table like Job for long term statistics
135 CREATE TABLE JobHisto (LIKE Job);
136 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
139 CREATE TABLE Location (
140 LocationId serial not null,
141 Location text not null,
142 Cost integer default 0,
144 primary key (LocationId)
150 filesetid serial not null,
151 fileset text not null,
153 createtime timestamp without time zone not null,
154 primary key (filesetid)
157 CREATE INDEX fileset_name_idx on fileset (fileset text_pattern_ops);
159 CREATE TABLE jobmedia
161 jobmediaid serial not null,
162 jobid integer not null,
163 mediaid integer not null,
164 firstindex integer default 0,
165 lastindex integer default 0,
166 startfile integer default 0,
167 endfile integer default 0,
168 startblock bigint default 0,
169 endblock bigint default 0,
170 volindex integer default 0,
171 primary key (jobmediaid)
174 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
178 mediaid serial not null,
179 volumename text not null,
180 slot integer default 0,
181 poolid integer default 0,
182 mediatype text not null,
183 mediatypeid integer default 0,
184 labeltype integer default 0,
185 firstwritten timestamp without time zone,
186 lastwritten timestamp without time zone,
187 labeldate timestamp without time zone,
188 voljobs integer default 0,
189 volfiles integer default 0,
190 volblocks integer default 0,
191 volmounts integer default 0,
192 volbytes bigint default 0,
193 volabytes bigint default 0,
194 volapadding bigint default 0,
195 volholebytes bigint default 0,
196 volholes integer default 0,
197 volparts integer default 0, /* Now used for VolType */
198 volerrors integer default 0,
199 volwrites bigint default 0,
200 volcapacitybytes bigint default 0,
201 volstatus text not null
202 check (volstatus in ('Full','Archive','Append',
203 'Recycle','Purged','Read-Only','Disabled',
204 'Error','Busy','Used','Cleaning','Scratch')),
205 enabled smallint default 1,
206 recycle smallint default 0,
207 ActionOnPurge smallint default 0,
208 volretention bigint default 0,
209 voluseduration bigint default 0,
210 maxvoljobs integer default 0,
211 maxvolfiles integer default 0,
212 maxvolbytes bigint default 0,
213 inchanger smallint default 0,
214 StorageId integer default 0,
215 DeviceId integer default 0,
216 mediaaddressing smallint default 0,
217 volreadtime bigint default 0,
218 volwritetime bigint default 0,
219 endfile integer default 0,
220 endblock bigint default 0,
221 LocationId integer default 0,
222 recyclecount integer default 0,
223 initialwrite timestamp without time zone,
224 scratchpoolid integer default 0,
225 recyclepoolid integer default 0,
227 primary key (mediaid)
230 CREATE UNIQUE INDEX media_volumename_id ON Media (VolumeName text_pattern_ops);
231 CREATE INDEX media_poolid_idx ON Media (PoolId);
232 CREATE INDEX media_storageid_idx ON Media (StorageId);
234 CREATE TABLE MediaType (
236 MediaType TEXT NOT NULL,
237 ReadOnly INTEGER DEFAULT 0,
238 PRIMARY KEY(MediaTypeId)
241 CREATE TABLE Storage (
244 AutoChanger INTEGER DEFAULT 0,
245 PRIMARY KEY(StorageId)
248 CREATE TABLE Device (
251 MediaTypeId INTEGER NOT NULL,
252 StorageId INTEGER NOT NULL,
253 DevMounts INTEGER NOT NULL DEFAULT 0,
254 DevReadBytes BIGINT NOT NULL DEFAULT 0,
255 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
256 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
257 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
258 DevReadTime BIGINT NOT NULL DEFAULT 0,
259 DevWriteTime BIGINT NOT NULL DEFAULT 0,
260 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
261 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
262 CleaningDate timestamp without time zone,
263 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
264 PRIMARY KEY(DeviceId)
270 poolid serial not null,
272 numvols integer default 0,
273 maxvols integer default 0,
274 useonce smallint default 0,
275 usecatalog smallint default 0,
276 acceptanyvolume smallint default 0,
277 volretention bigint default 0,
278 voluseduration bigint default 0,
279 maxvoljobs integer default 0,
280 maxvolfiles integer default 0,
281 maxvolbytes bigint default 0,
282 autoprune smallint default 0,
283 recycle smallint default 0,
284 ActionOnPurge smallint default 0,
286 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
287 labeltype integer default 0,
288 labelformat text not null,
289 enabled smallint default 1,
290 scratchpoolid integer default 0,
291 recyclepoolid integer default 0,
292 NextPoolId integer default 0,
293 MigrationHighBytes BIGINT DEFAULT 0,
294 MigrationLowBytes BIGINT DEFAULT 0,
295 MigrationTime BIGINT DEFAULT 0,
299 CREATE INDEX pool_name_idx on pool (name text_pattern_ops);
303 clientid serial not null,
306 autoprune smallint default 0,
307 fileretention bigint default 0,
308 jobretention bigint default 0,
309 primary key (clientid)
312 create unique index client_name_idx on client (name text_pattern_ops);
316 LogId serial not null,
317 JobId integer not null,
318 Time timestamp without time zone,
319 LogText text not null,
322 create index log_name_idx on Log (JobId);
324 CREATE TABLE LocationLog (
325 LocLogId SERIAL NOT NULL,
326 Date timestamp without time zone,
327 Comment TEXT NOT NULL,
328 MediaId INTEGER DEFAULT 0,
329 LocationId INTEGER DEFAULT 0,
330 newvolstatus text not null
331 check (newvolstatus in ('Full','Archive','Append',
332 'Recycle','Purged','Read-Only','Disabled',
333 'Error','Busy','Used','Cleaning','Scratch')),
335 PRIMARY KEY(LocLogId)
340 CREATE TABLE counters
342 counter text not null,
343 minvalue integer default 0,
344 maxvalue integer default 0,
345 currentvalue integer default 0,
346 wrapcounter text not null,
347 primary key (counter)
352 CREATE TABLE basefiles
354 baseid serial not null,
355 jobid integer not null,
356 fileid bigint not null,
362 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
364 CREATE TABLE unsavedfiles
366 UnsavedId integer not null,
367 jobid integer not null,
368 pathid integer not null,
369 filenameid integer not null,
370 primary key (UnsavedId)
373 CREATE TABLE CDImages
375 MediaId integer not null,
376 LastBurn timestamp without time zone not null,
377 primary key (MediaId)
381 CREATE TABLE PathHierarchy
383 PathId integer NOT NULL,
384 PPathId integer NOT NULL,
385 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
388 CREATE INDEX pathhierarchy_ppathid
389 ON PathHierarchy (PPathId);
391 CREATE TABLE PathVisibility
393 PathId integer NOT NULL,
394 JobId integer NOT NULL,
396 Files int4 DEFAULT 0,
397 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
399 CREATE INDEX pathvisibility_jobid
400 ON PathVisibility (JobId);
404 versionid integer not null
407 CREATE TABLE Status (
408 JobStatus CHAR(1) NOT NULL,
411 PRIMARY KEY (JobStatus)
414 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
415 ('C', 'Created, not yet running',15);
416 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
418 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
420 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
421 ('T', 'Completed successfully', 10);
422 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
423 ('E', 'Terminated with errors', 25);
424 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
425 ('e', 'Non-fatal error',20);
426 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
427 ('f', 'Fatal error',100);
428 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
429 ('D', 'Verify found differences',15);
430 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
431 ('A', 'Canceled by user',90);
432 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
433 ('F', 'Waiting for Client',15);
434 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
435 ('S', 'Waiting for Storage daemon',15);
436 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
437 ('m', 'Waiting for new media');
438 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
439 ('M', 'Waiting for media mount',15);
440 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
441 ('s', 'Waiting for storage resource',15);
442 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
443 ('j', 'Waiting for job resource',15);
444 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
445 ('c', 'Waiting for client resource',15);
446 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
447 ('d', 'Waiting on maximum jobs',15);
448 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
449 ('t', 'Waiting on start time',15);
450 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
451 ('p', 'Waiting on higher priority jobs',15);
452 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
453 ('a', 'SD despooling attributes',15);
454 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
455 ('i', 'Doing batch insert file records',15);
456 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
457 ('I', 'Incomplete Job',25);
459 CREATE TABLE Snapshot (
462 JobId integer default 0,
463 FileSetId integer default 0,
464 CreateTDate bigint default 0,
465 CreateDate timestamp without time zone not null,
466 ClientId int default 0,
467 Volume text not null,
468 Device text not null,
470 Retention integer default 0,
472 primary key (SnapshotId)
475 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device text_pattern_ops,
476 Volume text_pattern_ops,
477 Name text_pattern_ops);
479 INSERT INTO Version (VersionId) VALUES (15);
481 -- Make sure we have appropriate permissions
488 echo "Creation of Bacula PostgreSQL tables succeeded."
490 echo "Creation of Bacula PostgreSQL tables failed."