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 ObjectIndex INTEGER DEFAULT 0,
85 ObjectType INTEGER DEFAULT 0,
86 FileIndex INTEGER UNSIGNED DEFAULT 0,
87 JobId INTEGER UNSIGNED,
88 ObjectCompression INTEGER DEFAULT 0,
89 PRIMARY KEY(RestoreObjectId),
91 CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
96 JobId serial not null,
99 Type char(1) not null,
100 Level char(1) not null,
101 ClientId integer default 0,
102 JobStatus char(1) not null,
103 SchedTime timestamp without time zone,
104 StartTime timestamp without time zone,
105 EndTime timestamp without time zone,
106 RealEndTime timestamp without time zone,
107 JobTDate bigint default 0,
108 VolSessionId integer default 0,
109 volSessionTime integer default 0,
110 JobFiles integer default 0,
111 JobBytes bigint default 0,
112 ReadBytes bigint default 0,
113 JobErrors integer default 0,
114 JobMissingFiles integer default 0,
115 PoolId integer default 0,
116 FilesetId integer default 0,
117 PriorJobid integer default 0,
118 PurgedFiles smallint default 0,
119 HasBase smallint default 0,
120 HasCache smallint default 0,
121 Reviewed smallint default 0,
126 CREATE INDEX job_name_idx on job (name);
128 -- Create a table like Job for long term statistics
129 CREATE TABLE JobHisto (LIKE Job);
130 CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
133 CREATE TABLE Location (
134 LocationId serial not null,
135 Location text not null,
136 Cost integer default 0,
138 primary key (LocationId)
144 filesetid serial not null,
145 fileset text not null,
147 createtime timestamp without time zone not null,
148 primary key (filesetid)
151 CREATE INDEX fileset_name_idx on fileset (fileset);
153 CREATE TABLE jobmedia
155 jobmediaid serial not null,
156 jobid integer not null,
157 mediaid integer not null,
158 firstindex integer default 0,
159 lastindex integer default 0,
160 startfile integer default 0,
161 endfile integer default 0,
162 startblock bigint default 0,
163 endblock bigint default 0,
164 volindex integer default 0,
165 primary key (jobmediaid)
168 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
172 mediaid serial not null,
173 volumename text not null,
174 slot integer default 0,
175 poolid integer default 0,
176 mediatype text not null,
177 mediatypeid integer default 0,
178 labeltype integer default 0,
179 firstwritten timestamp without time zone,
180 lastwritten timestamp without time zone,
181 labeldate timestamp without time zone,
182 voljobs integer default 0,
183 volfiles integer default 0,
184 volblocks integer default 0,
185 volmounts integer default 0,
186 volbytes bigint default 0,
187 volparts integer default 0,
188 volerrors integer default 0,
189 volwrites integer default 0,
190 volcapacitybytes bigint default 0,
191 volstatus text not null
192 check (volstatus in ('Full','Archive','Append',
193 'Recycle','Purged','Read-Only','Disabled',
194 'Error','Busy','Used','Cleaning','Scratch')),
195 enabled smallint default 1,
196 recycle smallint default 0,
197 ActionOnPurge smallint default 0,
198 volretention bigint default 0,
199 voluseduration bigint default 0,
200 maxvoljobs integer default 0,
201 maxvolfiles integer default 0,
202 maxvolbytes bigint default 0,
203 inchanger smallint default 0,
204 StorageId integer default 0,
205 DeviceId integer default 0,
206 mediaaddressing smallint default 0,
207 volreadtime bigint default 0,
208 volwritetime bigint default 0,
209 endfile integer default 0,
210 endblock bigint default 0,
211 LocationId integer default 0,
212 recyclecount integer default 0,
213 initialwrite timestamp without time zone,
214 scratchpoolid integer default 0,
215 recyclepoolid integer default 0,
217 primary key (mediaid)
220 create unique index media_volumename_id on media (volumename);
223 CREATE TABLE MediaType (
225 MediaType TEXT NOT NULL,
226 ReadOnly INTEGER DEFAULT 0,
227 PRIMARY KEY(MediaTypeId)
230 CREATE TABLE Storage (
233 AutoChanger INTEGER DEFAULT 0,
234 PRIMARY KEY(StorageId)
237 CREATE TABLE Device (
240 MediaTypeId INTEGER NOT NULL,
241 StorageId INTEGER NOT NULL,
242 DevMounts INTEGER NOT NULL DEFAULT 0,
243 DevReadBytes BIGINT NOT NULL DEFAULT 0,
244 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
245 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
246 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
247 DevReadTime BIGINT NOT NULL DEFAULT 0,
248 DevWriteTime BIGINT NOT NULL DEFAULT 0,
249 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
250 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
251 CleaningDate timestamp without time zone,
252 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
253 PRIMARY KEY(DeviceId)
259 poolid serial not null,
261 numvols integer default 0,
262 maxvols integer default 0,
263 useonce smallint default 0,
264 usecatalog smallint default 0,
265 acceptanyvolume smallint default 0,
266 volretention bigint default 0,
267 voluseduration bigint default 0,
268 maxvoljobs integer default 0,
269 maxvolfiles integer default 0,
270 maxvolbytes bigint default 0,
271 autoprune smallint default 0,
272 recycle smallint default 0,
273 ActionOnPurge smallint default 0,
275 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
276 labeltype integer default 0,
277 labelformat text not null,
278 enabled smallint default 1,
279 scratchpoolid integer default 0,
280 recyclepoolid integer default 0,
281 NextPoolId integer default 0,
282 MigrationHighBytes BIGINT DEFAULT 0,
283 MigrationLowBytes BIGINT DEFAULT 0,
284 MigrationTime BIGINT DEFAULT 0,
288 CREATE INDEX pool_name_idx on pool (name);
292 clientid serial not null,
295 autoprune smallint default 0,
296 fileretention bigint default 0,
297 jobretention bigint default 0,
298 primary key (clientid)
301 create unique index client_name_idx on client (name);
305 LogId serial not null,
306 JobId integer not null,
307 Time timestamp without time zone,
308 LogText text not null,
311 create index log_name_idx on Log (JobId);
313 CREATE TABLE LocationLog (
314 LocLogId SERIAL NOT NULL,
315 Date timestamp without time zone,
316 Comment TEXT NOT NULL,
317 MediaId INTEGER DEFAULT 0,
318 LocationId INTEGER DEFAULT 0,
319 newvolstatus text not null
320 check (newvolstatus in ('Full','Archive','Append',
321 'Recycle','Purged','Read-Only','Disabled',
322 'Error','Busy','Used','Cleaning','Scratch')),
324 PRIMARY KEY(LocLogId)
329 CREATE TABLE counters
331 counter text not null,
332 minvalue integer default 0,
333 maxvalue integer default 0,
334 currentvalue integer default 0,
335 wrapcounter text not null,
336 primary key (counter)
341 CREATE TABLE basefiles
343 baseid serial not null,
344 jobid integer not null,
345 fileid bigint not null,
351 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
353 CREATE TABLE unsavedfiles
355 UnsavedId integer not null,
356 jobid integer not null,
357 pathid integer not null,
358 filenameid integer not null,
359 primary key (UnsavedId)
362 CREATE TABLE CDImages
364 MediaId integer not null,
365 LastBurn timestamp without time zone not null,
366 primary key (MediaId)
370 CREATE TABLE PathHierarchy
372 PathId integer NOT NULL,
373 PPathId integer NOT NULL,
374 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
377 CREATE INDEX pathhierarchy_ppathid
378 ON PathHierarchy (PPathId);
380 CREATE TABLE PathVisibility
382 PathId integer NOT NULL,
383 JobId integer NOT NULL,
385 Files int4 DEFAULT 0,
386 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
388 CREATE INDEX pathvisibility_jobid
389 ON PathVisibility (JobId);
393 versionid integer not null
396 CREATE TABLE Status (
397 JobStatus CHAR(1) NOT NULL,
400 PRIMARY KEY (JobStatus)
403 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
404 ('C', 'Created, not yet running',15);
405 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
407 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
409 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
410 ('T', 'Completed successfully', 10);
411 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
412 ('E', 'Terminated with errors', 25);
413 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
414 ('e', 'Non-fatal error',20);
415 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
416 ('f', 'Fatal error',100);
417 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
418 ('D', 'Verify found differences',15);
419 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
420 ('A', 'Canceled by user',90);
421 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
422 ('F', 'Waiting for Client',15);
423 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
424 ('S', 'Waiting for Storage daemon',15);
425 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
426 ('m', 'Waiting for new media');
427 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
428 ('M', 'Waiting for media mount',15);
429 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
430 ('s', 'Waiting for storage resource',15);
431 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
432 ('j', 'Waiting for job resource',15);
433 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
434 ('c', 'Waiting for client resource',15);
435 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
436 ('d', 'Waiting on maximum jobs',15);
437 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
438 ('t', 'Waiting on start time',15);
439 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
440 ('p', 'Waiting on higher priority jobs',15);
441 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
442 ('a', 'SD despooling attributes',15);
443 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
444 ('i', 'Doing batch insert file records',15);
446 INSERT INTO Version (VersionId) VALUES (12);
448 -- Make sure we have appropriate permissions
455 echo "Creation of Bacula PostgreSQL tables succeeded."
457 echo "Creation of Bacula PostgreSQL tables failed."