3 # shell script to create Bacula PostgreSQL tables
5 # Copyright (C) 2000-2017 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);
133 CREATE INDEX job_jobtdate_idx on job (jobtdate);
135 -- Create a table like Job for long term statistics
136 CREATE TABLE JobHisto (LIKE Job);
137 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
140 CREATE TABLE Location (
141 LocationId serial not null,
142 Location text not null,
143 Cost integer default 0,
145 primary key (LocationId)
151 filesetid serial not null,
152 fileset text not null,
154 createtime timestamp without time zone not null,
155 primary key (filesetid)
158 CREATE INDEX fileset_name_idx on fileset (fileset text_pattern_ops);
160 CREATE TABLE jobmedia
162 jobmediaid serial not null,
163 jobid integer not null,
164 mediaid integer not null,
165 firstindex integer default 0,
166 lastindex integer default 0,
167 startfile integer default 0,
168 endfile integer default 0,
169 startblock bigint default 0,
170 endblock bigint default 0,
171 volindex integer default 0,
172 primary key (jobmediaid)
175 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
179 mediaid serial not null,
180 volumename text not null,
181 slot integer default 0,
182 poolid integer default 0,
183 mediatype text not null,
184 mediatypeid integer default 0,
185 labeltype integer default 0,
186 firstwritten timestamp without time zone,
187 lastwritten timestamp without time zone,
188 labeldate timestamp without time zone,
189 voljobs integer default 0,
190 volfiles integer default 0,
191 volblocks integer default 0,
192 volparts integer default 0,
193 volcloudparts integer default 0,
194 volmounts integer default 0,
195 volbytes bigint default 0,
196 volabytes bigint default 0,
197 volapadding bigint default 0,
198 volholebytes bigint default 0,
199 volholes integer default 0,
200 voltype integer default 0,
201 volerrors integer default 0,
202 volwrites bigint default 0,
203 volcapacitybytes bigint default 0,
204 lastpartbytes bigint default 0,
205 volstatus text not null
206 check (volstatus in ('Full','Archive','Append',
207 'Recycle','Purged','Read-Only','Disabled',
208 'Error','Busy','Used','Cleaning','Scratch')),
209 enabled smallint default 1,
210 recycle smallint default 0,
211 ActionOnPurge smallint default 0,
212 cacheretention bigint default 0,
213 volretention bigint default 0,
214 voluseduration bigint default 0,
215 maxvoljobs integer default 0,
216 maxvolfiles integer default 0,
217 maxvolbytes bigint default 0,
218 inchanger smallint default 0,
219 StorageId integer default 0,
220 DeviceId integer default 0,
221 mediaaddressing smallint default 0,
222 volreadtime bigint default 0,
223 volwritetime bigint default 0,
224 endfile integer default 0,
225 endblock bigint default 0,
226 LocationId integer default 0,
227 recyclecount integer default 0,
228 initialwrite timestamp without time zone,
229 scratchpoolid integer default 0,
230 recyclepoolid integer default 0,
232 primary key (mediaid)
235 CREATE UNIQUE INDEX media_volumename_id ON Media (VolumeName text_pattern_ops);
236 CREATE INDEX media_poolid_idx ON Media (PoolId);
237 CREATE INDEX media_storageid_idx ON Media (StorageId);
239 CREATE TABLE MediaType (
241 MediaType TEXT NOT NULL,
242 ReadOnly INTEGER DEFAULT 0,
243 PRIMARY KEY(MediaTypeId)
246 CREATE TABLE Storage (
249 AutoChanger INTEGER DEFAULT 0,
250 PRIMARY KEY(StorageId)
253 CREATE TABLE Device (
256 MediaTypeId INTEGER NOT NULL,
257 StorageId INTEGER NOT NULL,
258 DevMounts INTEGER NOT NULL DEFAULT 0,
259 DevReadBytes BIGINT NOT NULL DEFAULT 0,
260 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
261 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
262 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
263 DevReadTime BIGINT NOT NULL DEFAULT 0,
264 DevWriteTime BIGINT NOT NULL DEFAULT 0,
265 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
266 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
267 CleaningDate timestamp without time zone,
268 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
269 PRIMARY KEY(DeviceId)
275 poolid serial not null,
277 numvols integer default 0,
278 maxvols integer default 0,
279 useonce smallint default 0,
280 usecatalog smallint default 0,
281 acceptanyvolume smallint default 0,
282 cacheretention bigint default 0,
283 volretention bigint default 0,
284 voluseduration bigint default 0,
285 maxvoljobs integer default 0,
286 maxvolfiles integer default 0,
287 maxvolbytes bigint default 0,
288 autoprune smallint default 0,
289 recycle smallint default 0,
290 ActionOnPurge smallint default 0,
292 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
293 labeltype integer default 0,
294 labelformat text not null,
295 enabled smallint default 1,
296 scratchpoolid integer default 0,
297 recyclepoolid integer default 0,
298 NextPoolId integer default 0,
299 MigrationHighBytes BIGINT DEFAULT 0,
300 MigrationLowBytes BIGINT DEFAULT 0,
301 MigrationTime BIGINT DEFAULT 0,
305 CREATE INDEX pool_name_idx on pool (name text_pattern_ops);
309 clientid serial not null,
312 autoprune smallint default 0,
313 fileretention bigint default 0,
314 jobretention bigint default 0,
315 primary key (clientid)
318 create unique index client_name_idx on client (name text_pattern_ops);
322 LogId serial not null,
323 JobId integer not null,
324 Time timestamp without time zone,
325 LogText text not null,
328 create index log_name_idx on Log (JobId);
330 CREATE TABLE LocationLog (
331 LocLogId SERIAL NOT NULL,
332 Date timestamp without time zone,
333 Comment TEXT NOT NULL,
334 MediaId INTEGER DEFAULT 0,
335 LocationId INTEGER DEFAULT 0,
336 newvolstatus text not null
337 check (newvolstatus in ('Full','Archive','Append',
338 'Recycle','Purged','Read-Only','Disabled',
339 'Error','Busy','Used','Cleaning','Scratch')),
341 PRIMARY KEY(LocLogId)
346 CREATE TABLE counters
348 counter text not null,
349 minvalue integer default 0,
350 maxvalue integer default 0,
351 currentvalue integer default 0,
352 wrapcounter text not null,
353 primary key (counter)
358 CREATE TABLE basefiles
360 baseid bigserial not null,
361 jobid integer not null,
362 fileid bigint not null,
368 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
370 CREATE TABLE unsavedfiles
372 UnsavedId integer not null,
373 jobid integer not null,
374 pathid integer not null,
375 filenameid integer not null,
376 primary key (UnsavedId)
379 CREATE TABLE CDImages
381 MediaId integer not null,
382 LastBurn timestamp without time zone not null,
383 primary key (MediaId)
387 CREATE TABLE PathHierarchy
389 PathId integer NOT NULL,
390 PPathId integer NOT NULL,
391 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
394 CREATE INDEX pathhierarchy_ppathid
395 ON PathHierarchy (PPathId);
397 CREATE TABLE PathVisibility
399 PathId integer NOT NULL,
400 JobId integer NOT NULL,
402 Files int4 DEFAULT 0,
403 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
405 CREATE INDEX pathvisibility_jobid
406 ON PathVisibility (JobId);
410 versionid integer not null
413 CREATE TABLE Status (
414 JobStatus CHAR(1) NOT NULL,
417 PRIMARY KEY (JobStatus)
420 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
421 ('C', 'Created, not yet running',15);
422 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
424 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
426 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
427 ('T', 'Completed successfully', 10);
428 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
429 ('E', 'Terminated with errors', 25);
430 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
431 ('e', 'Non-fatal error',20);
432 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
433 ('f', 'Fatal error',100);
434 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
435 ('D', 'Verify found differences',15);
436 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
437 ('A', 'Canceled by user',90);
438 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
439 ('F', 'Waiting for Client',15);
440 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
441 ('S', 'Waiting for Storage daemon',15);
442 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
443 ('m', 'Waiting for new media');
444 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
445 ('M', 'Waiting for media mount',15);
446 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
447 ('s', 'Waiting for storage resource',15);
448 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
449 ('j', 'Waiting for job resource',15);
450 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
451 ('c', 'Waiting for client resource',15);
452 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
453 ('d', 'Waiting on maximum jobs',15);
454 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
455 ('t', 'Waiting on start time',15);
456 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
457 ('p', 'Waiting on higher priority jobs',15);
458 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
459 ('a', 'SD despooling attributes',15);
460 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
461 ('i', 'Doing batch insert file records',15);
462 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
463 ('I', 'Incomplete Job',25);
465 CREATE TABLE Snapshot (
468 JobId integer default 0,
469 FileSetId integer default 0,
470 CreateTDate bigint default 0,
471 CreateDate timestamp without time zone not null,
472 ClientId int default 0,
473 Volume text not null,
474 Device text not null,
476 Retention integer default 0,
478 primary key (SnapshotId)
481 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device text_pattern_ops,
482 Volume text_pattern_ops,
483 Name text_pattern_ops);
485 INSERT INTO Version (VersionId) VALUES (16);
487 -- Make sure we have appropriate permissions
494 echo "Creation of Bacula PostgreSQL tables succeeded."
496 echo "Creation of Bacula PostgreSQL tables failed."