3 # shell script to create Bacula PostgreSQL tables
6 # 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);
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);
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 1000;
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,
131 CREATE INDEX job_name_idx on job (name);
133 -- Create a table like Job for long term statistics
134 CREATE TABLE JobHisto (LIKE Job);
135 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
138 CREATE TABLE Location (
139 LocationId serial not null,
140 Location text not null,
141 Cost integer default 0,
143 primary key (LocationId)
149 filesetid serial not null,
150 fileset text not null,
152 createtime timestamp without time zone not null,
153 primary key (filesetid)
156 CREATE INDEX fileset_name_idx on fileset (fileset);
158 CREATE TABLE jobmedia
160 jobmediaid serial not null,
161 jobid integer not null,
162 mediaid integer not null,
163 firstindex integer default 0,
164 lastindex integer default 0,
165 startfile integer default 0,
166 endfile integer default 0,
167 startblock bigint default 0,
168 endblock bigint default 0,
169 volindex integer default 0,
170 primary key (jobmediaid)
173 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
177 mediaid serial not null,
178 volumename text not null,
179 slot integer default 0,
180 poolid integer default 0,
181 mediatype text not null,
182 mediatypeid integer default 0,
183 labeltype integer default 0,
184 firstwritten timestamp without time zone,
185 lastwritten timestamp without time zone,
186 labeldate timestamp without time zone,
187 voljobs integer default 0,
188 volfiles integer default 0,
189 volblocks integer default 0,
190 volmounts integer default 0,
191 volbytes bigint default 0,
192 volparts integer default 0,
193 volerrors integer default 0,
194 volwrites integer default 0,
195 volcapacitybytes bigint default 0,
196 volstatus text not null
197 check (volstatus in ('Full','Archive','Append',
198 'Recycle','Purged','Read-Only','Disabled',
199 'Error','Busy','Used','Cleaning','Scratch')),
200 enabled smallint default 1,
201 recycle smallint default 0,
202 ActionOnPurge smallint default 0,
203 volretention bigint default 0,
204 voluseduration bigint default 0,
205 maxvoljobs integer default 0,
206 maxvolfiles integer default 0,
207 maxvolbytes bigint default 0,
208 inchanger smallint default 0,
209 StorageId integer default 0,
210 DeviceId integer default 0,
211 mediaaddressing smallint default 0,
212 volreadtime bigint default 0,
213 volwritetime bigint default 0,
214 endfile integer default 0,
215 endblock bigint default 0,
216 LocationId integer default 0,
217 recyclecount integer default 0,
218 initialwrite timestamp without time zone,
219 scratchpoolid integer default 0,
220 recyclepoolid integer default 0,
222 primary key (mediaid)
225 CREATE UNIQUE INDEX media_volumename_id ON Media (VolumeName);
226 CREATE INDEX media_poolid_idx ON Media (PoolId);
229 CREATE TABLE MediaType (
231 MediaType TEXT NOT NULL,
232 ReadOnly INTEGER DEFAULT 0,
233 PRIMARY KEY(MediaTypeId)
236 CREATE TABLE Storage (
239 AutoChanger INTEGER DEFAULT 0,
240 PRIMARY KEY(StorageId)
243 CREATE TABLE Device (
246 MediaTypeId INTEGER NOT NULL,
247 StorageId INTEGER NOT NULL,
248 DevMounts INTEGER NOT NULL DEFAULT 0,
249 DevReadBytes BIGINT NOT NULL DEFAULT 0,
250 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
251 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
252 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
253 DevReadTime BIGINT NOT NULL DEFAULT 0,
254 DevWriteTime BIGINT NOT NULL DEFAULT 0,
255 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
256 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
257 CleaningDate timestamp without time zone,
258 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
259 PRIMARY KEY(DeviceId)
265 poolid serial not null,
267 numvols integer default 0,
268 maxvols integer default 0,
269 useonce smallint default 0,
270 usecatalog smallint default 0,
271 acceptanyvolume smallint default 0,
272 volretention bigint default 0,
273 voluseduration bigint default 0,
274 maxvoljobs integer default 0,
275 maxvolfiles integer default 0,
276 maxvolbytes bigint default 0,
277 autoprune smallint default 0,
278 recycle smallint default 0,
279 ActionOnPurge smallint default 0,
281 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
282 labeltype integer default 0,
283 labelformat text not null,
284 enabled smallint default 1,
285 scratchpoolid integer default 0,
286 recyclepoolid integer default 0,
287 NextPoolId integer default 0,
288 MigrationHighBytes BIGINT DEFAULT 0,
289 MigrationLowBytes BIGINT DEFAULT 0,
290 MigrationTime BIGINT DEFAULT 0,
294 CREATE INDEX pool_name_idx on pool (name);
298 clientid serial not null,
301 autoprune smallint default 0,
302 fileretention bigint default 0,
303 jobretention bigint default 0,
304 primary key (clientid)
307 create unique index client_name_idx on client (name);
311 LogId serial not null,
312 JobId integer not null,
313 Time timestamp without time zone,
314 LogText text not null,
317 create index log_name_idx on Log (JobId);
319 CREATE TABLE LocationLog (
320 LocLogId SERIAL NOT NULL,
321 Date timestamp without time zone,
322 Comment TEXT NOT NULL,
323 MediaId INTEGER DEFAULT 0,
324 LocationId INTEGER DEFAULT 0,
325 newvolstatus text not null
326 check (newvolstatus in ('Full','Archive','Append',
327 'Recycle','Purged','Read-Only','Disabled',
328 'Error','Busy','Used','Cleaning','Scratch')),
330 PRIMARY KEY(LocLogId)
335 CREATE TABLE counters
337 counter text not null,
338 minvalue integer default 0,
339 maxvalue integer default 0,
340 currentvalue integer default 0,
341 wrapcounter text not null,
342 primary key (counter)
347 CREATE TABLE basefiles
349 baseid serial not null,
350 jobid integer not null,
351 fileid bigint not null,
357 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
359 CREATE TABLE unsavedfiles
361 UnsavedId integer not null,
362 jobid integer not null,
363 pathid integer not null,
364 filenameid integer not null,
365 primary key (UnsavedId)
368 CREATE TABLE CDImages
370 MediaId integer not null,
371 LastBurn timestamp without time zone not null,
372 primary key (MediaId)
376 CREATE TABLE PathHierarchy
378 PathId integer NOT NULL,
379 PPathId integer NOT NULL,
380 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
383 CREATE INDEX pathhierarchy_ppathid
384 ON PathHierarchy (PPathId);
386 CREATE TABLE PathVisibility
388 PathId integer NOT NULL,
389 JobId integer NOT NULL,
391 Files int4 DEFAULT 0,
392 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
394 CREATE INDEX pathvisibility_jobid
395 ON PathVisibility (JobId);
399 versionid integer not null
402 CREATE TABLE Status (
403 JobStatus CHAR(1) NOT NULL,
406 PRIMARY KEY (JobStatus)
409 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
410 ('C', 'Created, not yet running',15);
411 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
413 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
415 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
416 ('T', 'Completed successfully', 10);
417 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
418 ('E', 'Terminated with errors', 25);
419 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
420 ('e', 'Non-fatal error',20);
421 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
422 ('f', 'Fatal error',100);
423 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
424 ('D', 'Verify found differences',15);
425 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
426 ('A', 'Canceled by user',90);
427 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
428 ('F', 'Waiting for Client',15);
429 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
430 ('S', 'Waiting for Storage daemon',15);
431 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
432 ('m', 'Waiting for new media');
433 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
434 ('M', 'Waiting for media mount',15);
435 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
436 ('s', 'Waiting for storage resource',15);
437 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
438 ('j', 'Waiting for job resource',15);
439 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
440 ('c', 'Waiting for client resource',15);
441 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
442 ('d', 'Waiting on maximum jobs',15);
443 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
444 ('t', 'Waiting on start time',15);
445 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
446 ('p', 'Waiting on higher priority jobs',15);
447 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
448 ('a', 'SD despooling attributes',15);
449 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
450 ('i', 'Doing batch insert file records',15);
452 INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
454 -- Make sure we have appropriate permissions
461 echo "Creation of Bacula PostgreSQL tables succeeded."
463 echo "Creation of Bacula PostgreSQL tables failed."