3 # shell script to create Bacula PostgreSQL tables
6 # You won't get any support for performance issue if you changed the default
10 # Bacula® - The Network Backup Solution
12 # Copyright (C) 2000-2014 Free Software Foundation Europe e.V.
14 # The main author of Bacula is Kern Sibbald, with contributions from many
15 # others, a complete list can be found in the file AUTHORS.
17 # You may use this file and others of this release according to the
18 # license defined in the LICENSE file, which includes the Affero General
19 # Public License, v3.0 ("AGPLv3") and some additional permissions and
20 # terms pursuant to its AGPLv3 Section 7.
22 # Bacula® is a registered trademark of Kern Sibbald.
24 bindir=@POSTGRESQL_BINDIR@
26 db_name=${db_name:-@db_name@}
28 psql -f - -d ${db_name} $* <<END-OF-DATA
32 FilenameId serial not null,
34 primary key (FilenameId)
37 ALTER TABLE Filename ALTER COLUMN Name SET STATISTICS 1000;
38 CREATE UNIQUE INDEX filename_name_idx on Filename (Name);
42 PathId serial not null,
47 ALTER TABLE Path ALTER COLUMN Path SET STATISTICS 1000;
48 CREATE UNIQUE INDEX path_name_idx on Path (Path);
50 -- We strongly recommend to avoid the temptation to add new indexes.
51 -- In general, these will cause very significant performance
52 -- problems in other areas. A better approch is to carefully check
53 -- that all your memory configuation parameters are
54 -- suitable for the size of your installation. If you backup
55 -- millions of files, you need to adapt the database memory
56 -- configuration parameters concerning sorting, joining and global
57 -- memory. By default, sort and join parameters are very small
58 -- (sometimes 8Kb), and having sufficient memory specified by those
59 -- parameters is extremely important to run fast.
62 -- FileIndex can be 0 for FT_DELETED files
63 -- FileNameId can link to Filename.Name='' for directories
66 FileId bigserial not null,
67 FileIndex integer not null default 0,
68 JobId integer not null,
69 PathId integer not null,
70 FilenameId integer not null,
71 DeltaSeq smallint not null default 0,
72 MarkId integer not null default 0,
78 CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId);
79 CREATE INDEX file_jobid_idx on File (JobId);
82 -- Add this if you have a good number of job
83 -- that run at the same time
84 -- ALTER SEQUENCE file_fileid_seq CACHE 1000;
87 -- Possibly add one or more of the following indexes
88 -- if your Verifies are too slow, but they can slow down
91 -- CREATE INDEX file_pathid_idx on file(pathid);
92 -- CREATE INDEX file_filenameid_idx on file(filenameid);
94 CREATE TABLE RestoreObject (
95 RestoreObjectId SERIAL NOT NULL,
96 ObjectName TEXT NOT NULL,
97 RestoreObject BYTEA NOT NULL,
98 PluginName TEXT NOT NULL,
99 ObjectLength INTEGER DEFAULT 0,
100 ObjectFullLength INTEGER DEFAULT 0,
101 ObjectIndex INTEGER DEFAULT 0,
102 ObjectType INTEGER DEFAULT 0,
103 FileIndex INTEGER DEFAULT 0,
105 ObjectCompression INTEGER DEFAULT 0,
106 PRIMARY KEY(RestoreObjectId)
108 CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
113 JobId serial not null,
116 Type char(1) not null,
117 Level char(1) not null,
118 ClientId integer default 0,
119 JobStatus char(1) not null,
120 SchedTime timestamp without time zone,
121 StartTime timestamp without time zone,
122 EndTime timestamp without time zone,
123 RealEndTime timestamp without time zone,
124 JobTDate bigint default 0,
125 VolSessionId integer default 0,
126 volSessionTime integer default 0,
127 JobFiles integer default 0,
128 JobBytes bigint default 0,
129 ReadBytes bigint default 0,
130 JobErrors integer default 0,
131 JobMissingFiles integer default 0,
132 PoolId integer default 0,
133 FilesetId integer default 0,
134 PriorJobid integer default 0,
135 PurgedFiles smallint default 0,
136 HasBase smallint default 0,
137 HasCache smallint default 0,
138 Reviewed smallint default 0,
143 CREATE INDEX job_name_idx on job (name);
145 -- Create a table like Job for long term statistics
146 CREATE TABLE JobHisto (LIKE Job);
147 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
150 CREATE TABLE Location (
151 LocationId serial not null,
152 Location text not null,
153 Cost integer default 0,
155 primary key (LocationId)
161 filesetid serial not null,
162 fileset text not null,
164 createtime timestamp without time zone not null,
165 primary key (filesetid)
168 CREATE INDEX fileset_name_idx on fileset (fileset);
170 CREATE TABLE jobmedia
172 jobmediaid serial not null,
173 jobid integer not null,
174 mediaid integer not null,
175 firstindex integer default 0,
176 lastindex integer default 0,
177 startfile integer default 0,
178 endfile integer default 0,
179 startblock bigint default 0,
180 endblock bigint default 0,
181 volindex integer default 0,
182 primary key (jobmediaid)
185 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
189 mediaid serial not null,
190 volumename text not null,
191 slot integer default 0,
192 poolid integer default 0,
193 mediatype text not null,
194 mediatypeid integer default 0,
195 labeltype integer default 0,
196 firstwritten timestamp without time zone,
197 lastwritten timestamp without time zone,
198 labeldate timestamp without time zone,
199 voljobs integer default 0,
200 volfiles integer default 0,
201 volblocks integer default 0,
202 volmounts integer default 0,
203 volbytes bigint default 0,
204 volparts integer default 0,
205 volerrors integer default 0,
206 volwrites integer default 0,
207 volcapacitybytes bigint default 0,
208 volstatus text not null
209 check (volstatus in ('Full','Archive','Append',
210 'Recycle','Purged','Read-Only','Disabled',
211 'Error','Busy','Used','Cleaning','Scratch')),
212 enabled smallint default 1,
213 recycle smallint default 0,
214 ActionOnPurge smallint default 0,
215 volretention bigint default 0,
216 voluseduration bigint default 0,
217 maxvoljobs integer default 0,
218 maxvolfiles integer default 0,
219 maxvolbytes bigint default 0,
220 inchanger smallint default 0,
221 StorageId integer default 0,
222 DeviceId integer default 0,
223 mediaaddressing smallint default 0,
224 volreadtime bigint default 0,
225 volwritetime bigint default 0,
226 endfile integer default 0,
227 endblock bigint default 0,
228 LocationId integer default 0,
229 recyclecount integer default 0,
230 initialwrite timestamp without time zone,
231 scratchpoolid integer default 0,
232 recyclepoolid integer default 0,
234 primary key (mediaid)
237 CREATE UNIQUE INDEX media_volumename_id ON Media (VolumeName);
238 CREATE INDEX media_poolid_idx ON Media (PoolId);
241 CREATE TABLE MediaType (
243 MediaType TEXT NOT NULL,
244 ReadOnly INTEGER DEFAULT 0,
245 PRIMARY KEY(MediaTypeId)
248 CREATE TABLE Storage (
251 AutoChanger INTEGER DEFAULT 0,
252 PRIMARY KEY(StorageId)
255 CREATE TABLE Device (
258 MediaTypeId INTEGER NOT NULL,
259 StorageId INTEGER NOT NULL,
260 DevMounts INTEGER NOT NULL DEFAULT 0,
261 DevReadBytes BIGINT NOT NULL DEFAULT 0,
262 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
263 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
264 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
265 DevReadTime BIGINT NOT NULL DEFAULT 0,
266 DevWriteTime BIGINT NOT NULL DEFAULT 0,
267 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
268 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
269 CleaningDate timestamp without time zone,
270 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
271 PRIMARY KEY(DeviceId)
277 poolid serial not null,
279 numvols integer default 0,
280 maxvols integer default 0,
281 useonce smallint default 0,
282 usecatalog smallint default 0,
283 acceptanyvolume smallint default 0,
284 volretention bigint default 0,
285 voluseduration bigint default 0,
286 maxvoljobs integer default 0,
287 maxvolfiles integer default 0,
288 maxvolbytes bigint default 0,
289 autoprune smallint default 0,
290 recycle smallint default 0,
291 ActionOnPurge smallint default 0,
293 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
294 labeltype integer default 0,
295 labelformat text not null,
296 enabled smallint default 1,
297 scratchpoolid integer default 0,
298 recyclepoolid integer default 0,
299 NextPoolId integer default 0,
300 MigrationHighBytes BIGINT DEFAULT 0,
301 MigrationLowBytes BIGINT DEFAULT 0,
302 MigrationTime BIGINT DEFAULT 0,
306 CREATE INDEX pool_name_idx on pool (name);
310 clientid serial not null,
313 autoprune smallint default 0,
314 fileretention bigint default 0,
315 jobretention bigint default 0,
316 primary key (clientid)
319 create unique index client_name_idx on client (name);
323 LogId serial not null,
324 JobId integer not null,
325 Time timestamp without time zone,
326 LogText text not null,
329 create index log_name_idx on Log (JobId);
331 CREATE TABLE LocationLog (
332 LocLogId SERIAL NOT NULL,
333 Date timestamp without time zone,
334 Comment TEXT NOT NULL,
335 MediaId INTEGER DEFAULT 0,
336 LocationId INTEGER DEFAULT 0,
337 newvolstatus text not null
338 check (newvolstatus in ('Full','Archive','Append',
339 'Recycle','Purged','Read-Only','Disabled',
340 'Error','Busy','Used','Cleaning','Scratch')),
342 PRIMARY KEY(LocLogId)
347 CREATE TABLE counters
349 counter text not null,
350 minvalue integer default 0,
351 maxvalue integer default 0,
352 currentvalue integer default 0,
353 wrapcounter text not null,
354 primary key (counter)
359 CREATE TABLE basefiles
361 baseid serial not null,
362 jobid integer not null,
363 fileid bigint not null,
369 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
371 CREATE TABLE unsavedfiles
373 UnsavedId integer not null,
374 jobid integer not null,
375 pathid integer not null,
376 filenameid integer not null,
377 primary key (UnsavedId)
380 CREATE TABLE CDImages
382 MediaId integer not null,
383 LastBurn timestamp without time zone not null,
384 primary key (MediaId)
388 CREATE TABLE PathHierarchy
390 PathId integer NOT NULL,
391 PPathId integer NOT NULL,
392 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
395 CREATE INDEX pathhierarchy_ppathid
396 ON PathHierarchy (PPathId);
398 CREATE TABLE PathVisibility
400 PathId integer NOT NULL,
401 JobId integer NOT NULL,
403 Files int4 DEFAULT 0,
404 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
406 CREATE INDEX pathvisibility_jobid
407 ON PathVisibility (JobId);
411 versionid integer not null
414 CREATE TABLE Status (
415 JobStatus CHAR(1) NOT NULL,
418 PRIMARY KEY (JobStatus)
421 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
422 ('C', 'Created, not yet running',15);
423 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
425 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
427 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
428 ('T', 'Completed successfully', 10);
429 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
430 ('E', 'Terminated with errors', 25);
431 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
432 ('e', 'Non-fatal error',20);
433 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
434 ('f', 'Fatal error',100);
435 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
436 ('D', 'Verify found differences',15);
437 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
438 ('A', 'Canceled by user',90);
439 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
440 ('F', 'Waiting for Client',15);
441 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
442 ('S', 'Waiting for Storage daemon',15);
443 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
444 ('m', 'Waiting for new media');
445 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
446 ('M', 'Waiting for media mount',15);
447 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
448 ('s', 'Waiting for storage resource',15);
449 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
450 ('j', 'Waiting for job resource',15);
451 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
452 ('c', 'Waiting for client resource',15);
453 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
454 ('d', 'Waiting on maximum jobs',15);
455 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
456 ('t', 'Waiting on start time',15);
457 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
458 ('p', 'Waiting on higher priority jobs',15);
459 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
460 ('a', 'SD despooling attributes',15);
461 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
462 ('i', 'Doing batch insert file records',15);
464 INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
466 -- Make sure we have appropriate permissions
473 echo "Creation of Bacula PostgreSQL tables succeeded."
475 echo "Creation of Bacula PostgreSQL tables failed."