3 # shell script to create Bacula PostgreSQL tables
6 # You won't get any support for performance issue if you changed the default
9 bindir=@POSTGRESQL_BINDIR@
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 DeltaSeq smallint not null default 0,
57 MarkId integer not null default 0,
63 CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId);
64 CREATE INDEX file_jobid_idx on File (JobId);
67 -- Add this if you have a good number of job
68 -- that run at the same time
69 -- ALTER SEQUENCE file_fileid_seq CACHE 1000;
72 -- Possibly add one or more of the following indexes
73 -- if your Verifies are too slow, but they can slow down
76 -- CREATE INDEX file_pathid_idx on file(pathid);
77 -- CREATE INDEX file_filenameid_idx on file(filenameid);
79 CREATE TABLE RestoreObject (
80 RestoreObjectId SERIAL NOT NULL,
81 ObjectName TEXT NOT NULL,
82 RestoreObject BYTEA NOT NULL,
83 PluginName TEXT NOT NULL,
84 ObjectLength INTEGER DEFAULT 0,
85 ObjectFullLength INTEGER DEFAULT 0,
86 ObjectIndex INTEGER DEFAULT 0,
87 ObjectType INTEGER DEFAULT 0,
88 FileIndex INTEGER DEFAULT 0,
90 ObjectCompression INTEGER DEFAULT 0,
91 PRIMARY KEY(RestoreObjectId)
93 CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
98 JobId serial not null,
101 Type char(1) not null,
102 Level char(1) not null,
103 ClientId integer default 0,
104 JobStatus char(1) not null,
105 SchedTime timestamp without time zone,
106 StartTime timestamp without time zone,
107 EndTime timestamp without time zone,
108 RealEndTime timestamp without time zone,
109 JobTDate bigint default 0,
110 VolSessionId integer default 0,
111 volSessionTime integer default 0,
112 JobFiles integer default 0,
113 JobBytes bigint default 0,
114 ReadBytes bigint default 0,
115 JobErrors integer default 0,
116 JobMissingFiles integer default 0,
117 PoolId integer default 0,
118 FilesetId integer default 0,
119 PriorJobid integer default 0,
120 PurgedFiles smallint default 0,
121 HasBase smallint default 0,
122 HasCache smallint default 0,
123 Reviewed smallint default 0,
128 CREATE INDEX job_name_idx on job (name);
130 -- Create a table like Job for long term statistics
131 CREATE TABLE JobHisto (LIKE Job);
132 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
135 CREATE TABLE Location (
136 LocationId serial not null,
137 Location text not null,
138 Cost integer default 0,
140 primary key (LocationId)
146 filesetid serial not null,
147 fileset text not null,
149 createtime timestamp without time zone not null,
150 primary key (filesetid)
153 CREATE INDEX fileset_name_idx on fileset (fileset);
155 CREATE TABLE jobmedia
157 jobmediaid serial not null,
158 jobid integer not null,
159 mediaid integer not null,
160 firstindex integer default 0,
161 lastindex integer default 0,
162 startfile integer default 0,
163 endfile integer default 0,
164 startblock bigint default 0,
165 endblock bigint default 0,
166 volindex integer default 0,
167 primary key (jobmediaid)
170 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
174 mediaid serial not null,
175 volumename text not null,
176 slot integer default 0,
177 poolid integer default 0,
178 mediatype text not null,
179 mediatypeid integer default 0,
180 labeltype integer default 0,
181 firstwritten timestamp without time zone,
182 lastwritten timestamp without time zone,
183 labeldate timestamp without time zone,
184 voljobs integer default 0,
185 volfiles integer default 0,
186 volblocks integer default 0,
187 volmounts integer default 0,
188 volbytes bigint default 0,
189 volparts integer default 0,
190 volerrors integer default 0,
191 volwrites integer default 0,
192 volcapacitybytes bigint default 0,
193 volstatus text not null
194 check (volstatus in ('Full','Archive','Append',
195 'Recycle','Purged','Read-Only','Disabled',
196 'Error','Busy','Used','Cleaning','Scratch')),
197 enabled smallint default 1,
198 recycle smallint default 0,
199 ActionOnPurge smallint default 0,
200 volretention bigint default 0,
201 voluseduration bigint default 0,
202 maxvoljobs integer default 0,
203 maxvolfiles integer default 0,
204 maxvolbytes bigint default 0,
205 inchanger smallint default 0,
206 StorageId integer default 0,
207 DeviceId integer default 0,
208 mediaaddressing smallint default 0,
209 volreadtime bigint default 0,
210 volwritetime bigint default 0,
211 endfile integer default 0,
212 endblock bigint default 0,
213 LocationId integer default 0,
214 recyclecount integer default 0,
215 initialwrite timestamp without time zone,
216 scratchpoolid integer default 0,
217 recyclepoolid integer default 0,
219 primary key (mediaid)
222 create unique index media_volumename_id on media (volumename);
225 CREATE TABLE MediaType (
227 MediaType TEXT NOT NULL,
228 ReadOnly INTEGER DEFAULT 0,
229 PRIMARY KEY(MediaTypeId)
232 CREATE TABLE Storage (
235 AutoChanger INTEGER DEFAULT 0,
236 PRIMARY KEY(StorageId)
239 CREATE TABLE Device (
242 MediaTypeId INTEGER NOT NULL,
243 StorageId INTEGER NOT NULL,
244 DevMounts INTEGER NOT NULL DEFAULT 0,
245 DevReadBytes BIGINT NOT NULL DEFAULT 0,
246 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
247 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
248 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
249 DevReadTime BIGINT NOT NULL DEFAULT 0,
250 DevWriteTime BIGINT NOT NULL DEFAULT 0,
251 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
252 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
253 CleaningDate timestamp without time zone,
254 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
255 PRIMARY KEY(DeviceId)
261 poolid serial not null,
263 numvols integer default 0,
264 maxvols integer default 0,
265 useonce smallint default 0,
266 usecatalog smallint default 0,
267 acceptanyvolume smallint default 0,
268 volretention bigint default 0,
269 voluseduration bigint default 0,
270 maxvoljobs integer default 0,
271 maxvolfiles integer default 0,
272 maxvolbytes bigint default 0,
273 autoprune smallint default 0,
274 recycle smallint default 0,
275 ActionOnPurge smallint default 0,
277 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
278 labeltype integer default 0,
279 labelformat text not null,
280 enabled smallint default 1,
281 scratchpoolid integer default 0,
282 recyclepoolid integer default 0,
283 NextPoolId integer default 0,
284 MigrationHighBytes BIGINT DEFAULT 0,
285 MigrationLowBytes BIGINT DEFAULT 0,
286 MigrationTime BIGINT DEFAULT 0,
290 CREATE INDEX pool_name_idx on pool (name);
294 clientid serial not null,
297 autoprune smallint default 0,
298 fileretention bigint default 0,
299 jobretention bigint default 0,
300 primary key (clientid)
303 create unique index client_name_idx on client (name);
307 LogId serial not null,
308 JobId integer not null,
309 Time timestamp without time zone,
310 LogText text not null,
313 create index log_name_idx on Log (JobId);
315 CREATE TABLE LocationLog (
316 LocLogId SERIAL NOT NULL,
317 Date timestamp without time zone,
318 Comment TEXT NOT NULL,
319 MediaId INTEGER DEFAULT 0,
320 LocationId INTEGER DEFAULT 0,
321 newvolstatus text not null
322 check (newvolstatus in ('Full','Archive','Append',
323 'Recycle','Purged','Read-Only','Disabled',
324 'Error','Busy','Used','Cleaning','Scratch')),
326 PRIMARY KEY(LocLogId)
331 CREATE TABLE counters
333 counter text not null,
334 minvalue integer default 0,
335 maxvalue integer default 0,
336 currentvalue integer default 0,
337 wrapcounter text not null,
338 primary key (counter)
343 CREATE TABLE basefiles
345 baseid serial not null,
346 jobid integer not null,
347 fileid bigint not null,
353 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
355 CREATE TABLE unsavedfiles
357 UnsavedId integer not null,
358 jobid integer not null,
359 pathid integer not null,
360 filenameid integer not null,
361 primary key (UnsavedId)
364 CREATE TABLE CDImages
366 MediaId integer not null,
367 LastBurn timestamp without time zone not null,
368 primary key (MediaId)
372 CREATE TABLE PathHierarchy
374 PathId integer NOT NULL,
375 PPathId integer NOT NULL,
376 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
379 CREATE INDEX pathhierarchy_ppathid
380 ON PathHierarchy (PPathId);
382 CREATE TABLE PathVisibility
384 PathId integer NOT NULL,
385 JobId integer NOT NULL,
387 Files int4 DEFAULT 0,
388 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
390 CREATE INDEX pathvisibility_jobid
391 ON PathVisibility (JobId);
395 versionid integer not null
398 CREATE TABLE Status (
399 JobStatus CHAR(1) NOT NULL,
402 PRIMARY KEY (JobStatus)
405 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
406 ('C', 'Created, not yet running',15);
407 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
409 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
411 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
412 ('T', 'Completed successfully', 10);
413 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
414 ('E', 'Terminated with errors', 25);
415 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
416 ('e', 'Non-fatal error',20);
417 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
418 ('f', 'Fatal error',100);
419 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
420 ('D', 'Verify found differences',15);
421 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
422 ('A', 'Canceled by user',90);
423 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
424 ('F', 'Waiting for Client',15);
425 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
426 ('S', 'Waiting for Storage daemon',15);
427 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
428 ('m', 'Waiting for new media');
429 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
430 ('M', 'Waiting for media mount',15);
431 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
432 ('s', 'Waiting for storage resource',15);
433 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
434 ('j', 'Waiting for job resource',15);
435 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
436 ('c', 'Waiting for client resource',15);
437 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
438 ('d', 'Waiting on maximum jobs',15);
439 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
440 ('t', 'Waiting on start time',15);
441 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
442 ('p', 'Waiting on higher priority jobs',15);
443 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
444 ('a', 'SD despooling attributes',15);
445 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
446 ('i', 'Doing batch insert file records',15);
448 INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
450 -- Make sure we have appropriate permissions
457 echo "Creation of Bacula PostgreSQL tables succeeded."
459 echo "Creation of Bacula PostgreSQL tables failed."