3 # shell script to create Bacula PostgreSQL tables
6 # You won't get any support for performance issue if you changed the default
11 db_name=${db_name:-@db_name@}
13 psql -f - -d ${db_name} $* <<END-OF-DATA
17 FilenameId serial not null,
19 primary key (FilenameId)
22 ALTER TABLE Filename ALTER COLUMN Name SET STATISTICS 1000;
23 CREATE UNIQUE INDEX filename_name_idx on Filename (Name);
27 PathId serial not null,
32 ALTER TABLE Path ALTER COLUMN Path SET STATISTICS 1000;
33 CREATE UNIQUE INDEX path_name_idx on Path (Path);
35 -- We strongly recommend to avoid the temptation to add new indexes.
36 -- In general, these will cause very significant performance
37 -- problems in other areas. A better approch is to carefully check
38 -- that all your memory configuation parameters are
39 -- suitable for the size of your installation. If you backup
40 -- millions of files, you need to adapt the database memory
41 -- configuration parameters concerning sorting, joining and global
42 -- memory. By default, sort and join parameters are very small
43 -- (sometimes 8Kb), and having sufficient memory specified by those
44 -- parameters is extremely important to run fast.
47 -- FileIndex can be 0 for FT_DELETED files
48 -- FileNameId can link to Filename.Name='' for directories
51 FileId bigserial not null,
52 FileIndex integer not null default 0,
53 JobId integer not null,
54 PathId integer not null,
55 FilenameId integer not null,
56 MarkId integer not null default 0,
62 CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId);
63 CREATE INDEX file_jobid_idx on File (JobId);
66 -- Add this if you have a good number of job
67 -- that run at the same time
68 -- ALTER SEQUENCE file_fileid_seq CACHE 1000;
71 -- Possibly add one or more of the following indexes
72 -- if your Verifies are too slow, but they can slow down
75 -- CREATE INDEX file_pathid_idx on file(pathid);
76 -- CREATE INDEX file_filenameid_idx on file(filenameid);
78 CREATE TABLE RestoreObject (
79 RestoreObjectId SERIAL NOT NULL,
80 ObjectName TEXT NOT NULL,
81 RestoreObject TEXT NOT NULL,
82 PluginName TEXT NOT NULL,
83 ObjectLength INTEGER DEFAULT 0,
84 ObjectFullLength INTEGER DEFAULT 0,
85 ObjectIndex INTEGER DEFAULT 0,
86 ObjectType INTEGER DEFAULT 0,
87 FileIndex INTEGER UNSIGNED DEFAULT 0,
88 JobId INTEGER UNSIGNED,
89 ObjectCompression INTEGER DEFAULT 0,
90 PRIMARY KEY(RestoreObjectId),
92 CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
97 JobId serial not null,
100 Type char(1) not null,
101 Level char(1) not null,
102 ClientId integer default 0,
103 JobStatus char(1) not null,
104 SchedTime timestamp without time zone,
105 StartTime timestamp without time zone,
106 EndTime timestamp without time zone,
107 RealEndTime timestamp without time zone,
108 JobTDate bigint default 0,
109 VolSessionId integer default 0,
110 volSessionTime integer default 0,
111 JobFiles integer default 0,
112 JobBytes bigint default 0,
113 ReadBytes bigint default 0,
114 JobErrors integer default 0,
115 JobMissingFiles integer default 0,
116 PoolId integer default 0,
117 FilesetId integer default 0,
118 PriorJobid integer default 0,
119 PurgedFiles smallint default 0,
120 HasBase smallint default 0,
121 HasCache smallint default 0,
122 Reviewed smallint default 0,
127 CREATE INDEX job_name_idx on job (name);
129 -- Create a table like Job for long term statistics
130 CREATE TABLE JobHisto (LIKE Job);
131 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
134 CREATE TABLE Location (
135 LocationId serial not null,
136 Location text not null,
137 Cost integer default 0,
139 primary key (LocationId)
145 filesetid serial not null,
146 fileset text not null,
148 createtime timestamp without time zone not null,
149 primary key (filesetid)
152 CREATE INDEX fileset_name_idx on fileset (fileset);
154 CREATE TABLE jobmedia
156 jobmediaid serial not null,
157 jobid integer not null,
158 mediaid integer not null,
159 firstindex integer default 0,
160 lastindex integer default 0,
161 startfile integer default 0,
162 endfile integer default 0,
163 startblock bigint default 0,
164 endblock bigint default 0,
165 volindex integer default 0,
166 primary key (jobmediaid)
169 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
173 mediaid serial not null,
174 volumename text not null,
175 slot integer default 0,
176 poolid integer default 0,
177 mediatype text not null,
178 mediatypeid integer default 0,
179 labeltype integer default 0,
180 firstwritten timestamp without time zone,
181 lastwritten timestamp without time zone,
182 labeldate timestamp without time zone,
183 voljobs integer default 0,
184 volfiles integer default 0,
185 volblocks integer default 0,
186 volmounts integer default 0,
187 volbytes bigint default 0,
188 volparts integer default 0,
189 volerrors integer default 0,
190 volwrites integer default 0,
191 volcapacitybytes bigint default 0,
192 volstatus text not null
193 check (volstatus in ('Full','Archive','Append',
194 'Recycle','Purged','Read-Only','Disabled',
195 'Error','Busy','Used','Cleaning','Scratch')),
196 enabled smallint default 1,
197 recycle smallint default 0,
198 ActionOnPurge smallint default 0,
199 volretention bigint default 0,
200 voluseduration bigint default 0,
201 maxvoljobs integer default 0,
202 maxvolfiles integer default 0,
203 maxvolbytes bigint default 0,
204 inchanger smallint default 0,
205 StorageId integer default 0,
206 DeviceId integer default 0,
207 mediaaddressing smallint default 0,
208 volreadtime bigint default 0,
209 volwritetime bigint default 0,
210 endfile integer default 0,
211 endblock bigint default 0,
212 LocationId integer default 0,
213 recyclecount integer default 0,
214 initialwrite timestamp without time zone,
215 scratchpoolid integer default 0,
216 recyclepoolid integer default 0,
218 primary key (mediaid)
221 create unique index media_volumename_id on media (volumename);
224 CREATE TABLE MediaType (
226 MediaType TEXT NOT NULL,
227 ReadOnly INTEGER DEFAULT 0,
228 PRIMARY KEY(MediaTypeId)
231 CREATE TABLE Storage (
234 AutoChanger INTEGER DEFAULT 0,
235 PRIMARY KEY(StorageId)
238 CREATE TABLE Device (
241 MediaTypeId INTEGER NOT NULL,
242 StorageId INTEGER NOT NULL,
243 DevMounts INTEGER NOT NULL DEFAULT 0,
244 DevReadBytes BIGINT NOT NULL DEFAULT 0,
245 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
246 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
247 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
248 DevReadTime BIGINT NOT NULL DEFAULT 0,
249 DevWriteTime BIGINT NOT NULL DEFAULT 0,
250 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
251 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
252 CleaningDate timestamp without time zone,
253 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
254 PRIMARY KEY(DeviceId)
260 poolid serial not null,
262 numvols integer default 0,
263 maxvols integer default 0,
264 useonce smallint default 0,
265 usecatalog smallint default 0,
266 acceptanyvolume smallint default 0,
267 volretention bigint default 0,
268 voluseduration bigint default 0,
269 maxvoljobs integer default 0,
270 maxvolfiles integer default 0,
271 maxvolbytes bigint default 0,
272 autoprune smallint default 0,
273 recycle smallint default 0,
274 ActionOnPurge smallint default 0,
276 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
277 labeltype integer default 0,
278 labelformat text not null,
279 enabled smallint default 1,
280 scratchpoolid integer default 0,
281 recyclepoolid integer default 0,
282 NextPoolId integer default 0,
283 MigrationHighBytes BIGINT DEFAULT 0,
284 MigrationLowBytes BIGINT DEFAULT 0,
285 MigrationTime BIGINT DEFAULT 0,
289 CREATE INDEX pool_name_idx on pool (name);
293 clientid serial not null,
296 autoprune smallint default 0,
297 fileretention bigint default 0,
298 jobretention bigint default 0,
299 primary key (clientid)
302 create unique index client_name_idx on client (name);
306 LogId serial not null,
307 JobId integer not null,
308 Time timestamp without time zone,
309 LogText text not null,
312 create index log_name_idx on Log (JobId);
314 CREATE TABLE LocationLog (
315 LocLogId SERIAL NOT NULL,
316 Date timestamp without time zone,
317 Comment TEXT NOT NULL,
318 MediaId INTEGER DEFAULT 0,
319 LocationId INTEGER DEFAULT 0,
320 newvolstatus text not null
321 check (newvolstatus in ('Full','Archive','Append',
322 'Recycle','Purged','Read-Only','Disabled',
323 'Error','Busy','Used','Cleaning','Scratch')),
325 PRIMARY KEY(LocLogId)
330 CREATE TABLE counters
332 counter text not null,
333 minvalue integer default 0,
334 maxvalue integer default 0,
335 currentvalue integer default 0,
336 wrapcounter text not null,
337 primary key (counter)
342 CREATE TABLE basefiles
344 baseid serial not null,
345 jobid integer not null,
346 fileid bigint not null,
352 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
354 CREATE TABLE unsavedfiles
356 UnsavedId integer not null,
357 jobid integer not null,
358 pathid integer not null,
359 filenameid integer not null,
360 primary key (UnsavedId)
363 CREATE TABLE CDImages
365 MediaId integer not null,
366 LastBurn timestamp without time zone not null,
367 primary key (MediaId)
371 CREATE TABLE PathHierarchy
373 PathId integer NOT NULL,
374 PPathId integer NOT NULL,
375 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
378 CREATE INDEX pathhierarchy_ppathid
379 ON PathHierarchy (PPathId);
381 CREATE TABLE PathVisibility
383 PathId integer NOT NULL,
384 JobId integer NOT NULL,
386 Files int4 DEFAULT 0,
387 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
389 CREATE INDEX pathvisibility_jobid
390 ON PathVisibility (JobId);
394 versionid integer not null
397 CREATE TABLE Status (
398 JobStatus CHAR(1) NOT NULL,
401 PRIMARY KEY (JobStatus)
404 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
405 ('C', 'Created, not yet running',15);
406 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
408 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
410 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
411 ('T', 'Completed successfully', 10);
412 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
413 ('E', 'Terminated with errors', 25);
414 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
415 ('e', 'Non-fatal error',20);
416 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
417 ('f', 'Fatal error',100);
418 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
419 ('D', 'Verify found differences',15);
420 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
421 ('A', 'Canceled by user',90);
422 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
423 ('F', 'Waiting for Client',15);
424 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
425 ('S', 'Waiting for Storage daemon',15);
426 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
427 ('m', 'Waiting for new media');
428 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
429 ('M', 'Waiting for media mount',15);
430 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
431 ('s', 'Waiting for storage resource',15);
432 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
433 ('j', 'Waiting for job resource',15);
434 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
435 ('c', 'Waiting for client resource',15);
436 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
437 ('d', 'Waiting on maximum jobs',15);
438 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
439 ('t', 'Waiting on start time',15);
440 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
441 ('p', 'Waiting on higher priority jobs',15);
442 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
443 ('a', 'SD despooling attributes',15);
444 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
445 ('i', 'Doing batch insert file records',15);
447 INSERT INTO Version (VersionId) VALUES (12);
449 -- Make sure we have appropriate permissions
456 echo "Creation of Bacula PostgreSQL tables succeeded."
458 echo "Creation of Bacula PostgreSQL tables failed."