3 # shell script to create Bacula PostgreSQL tables
6 db_name=${db_name:-@db_name@}
8 sql $* ${db_name} <<END-OF-DATA
12 CREATE SEQUENCE filename_seq;
15 filenameid integer not null default filename_seq.nextval,
17 primary key (filenameid)
20 ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000;
21 CREATE UNIQUE INDEX filename_name_idx on filename (name);
23 CREATE SEQUENCE path_seq;
26 pathid integer not null default path_seq.nextval,
31 ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000;
32 CREATE UNIQUE INDEX path_name_idx on path (path);
34 CREATE SEQUENCE file_seq;
37 fileid integer8 not null default file_seq.nextval,
38 fileindex integer not null default 0,
39 jobid integer not null,
40 pathid integer not null,
41 filenameid integer not null,
42 markid integer not null default 0,
48 CREATE INDEX file_jobid_idx on file (jobid);
49 CREATE INDEX file_fp_idx on file (filenameid, pathid);
52 -- Add this if you have a good number of job
53 -- that run at the same time
54 -- ALTER SEQUENCE file_fileid_seq CACHE 1000;
57 -- Possibly add one or more of the following indexes
58 -- if your Verifies are too slow.
60 -- CREATE INDEX file_pathid_idx on file(pathid);
61 -- CREATE INDEX file_filenameid_idx on file(filenameid);
62 -- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
64 CREATE SEQUENCE job_seq;
67 jobid integer not null default job_seq.nextval,
70 type char(1) not null,
71 level char(1) not null,
72 clientid integer default 0,
73 jobstatus char(1) not null,
74 schedtime timestamp without time zone,
75 starttime timestamp without time zone,
76 endtime timestamp without time zone,
77 realendtime timestamp without time zone,
78 jobtdate bigint default 0,
79 volsessionid integer default 0,
80 volsessiontime integer default 0,
81 jobfiles integer default 0,
82 jobbytes bigint default 0,
83 readbytes bigint default 0,
84 joberrors integer default 0,
85 jobmissingfiles integer default 0,
86 poolid integer default 0,
87 filesetid integer default 0,
88 purgedfiles smallint default 0,
89 hasbase smallint default 0,
90 priorjobid integer default 0,
94 CREATE INDEX job_name_idx on job (name);
96 -- Create a table like Job for long term statistics
97 CREATE TABLE JobHisto (LIKE Job);
98 CREATE INDEX jobhisto_idx ON jobhisto ( starttime );
101 CREATE SEQUENCE Location_seq;
102 CREATE TABLE Location (
103 LocationId integer not null default Location_seq.nextval,
104 Location text not null,
105 Cost integer default 0,
107 primary key (LocationId)
111 CREATE SEQUENCE fileset_seq;
114 filesetid integer not null default fileset_seq.nextval,
115 fileset text not null,
117 createtime timestamp without time zone not null,
118 primary key (filesetid)
121 CREATE INDEX fileset_name_idx on fileset (fileset);
123 CREATE SEQUENCE jobmedia_seq;
124 CREATE TABLE jobmedia
126 jobmediaid integer not null default jobmedia_seq.nestval,
127 jobid integer not null,
128 mediaid integer not null,
129 firstindex integer default 0,
130 lastindex integer default 0,
131 startfile integer default 0,
132 endfile integer default 0,
133 startblock bigint default 0,
134 endblock bigint default 0,
135 volindex integer default 0,
136 copy integer default 0,
137 primary key (jobmediaid)
140 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
142 CREATE SEQUENCE media_seq;
145 mediaid integer not null default media_seq.nextval,
146 volumename text not null,
147 slot integer default 0,
148 poolid integer default 0,
149 mediatype text not null,
150 mediatypeid integer default 0,
151 labeltype integer default 0,
152 firstwritten timestamp without time zone,
153 lastwritten timestamp without time zone,
154 labeldate timestamp without time zone,
155 voljobs integer default 0,
156 volfiles integer default 0,
157 volblocks integer default 0,
158 volmounts integer default 0,
159 volbytes bigint default 0,
160 volparts integer default 0,
161 volerrors integer default 0,
162 volwrites integer default 0,
163 volcapacitybytes bigint default 0,
164 volstatus text not null
165 check (volstatus in ('Full','Archive','Append',
166 'Recycle','Purged','Read-Only','Disabled',
167 'Error','Busy','Used','Cleaning','Scratch')),
168 enabled smallint default 1,
169 recycle smallint default 0,
170 ActionOnPurge smallint default 0,
171 volretention bigint default 0,
172 voluseduration bigint default 0,
173 maxvoljobs integer default 0,
174 maxvolfiles integer default 0,
175 maxvolbytes bigint default 0,
176 inchanger smallint default 0,
177 StorageId integer default 0,
178 DeviceId integer default 0,
179 mediaaddressing smallint default 0,
180 volreadtime bigint default 0,
181 volwritetime bigint default 0,
182 endfile integer default 0,
183 endblock bigint default 0,
184 LocationId integer default 0,
185 recyclecount integer default 0,
186 initialwrite timestamp without time zone,
187 scratchpoolid integer default 0,
188 recyclepoolid integer default 0,
190 primary key (mediaid)
193 create unique index media_volumename_id on media (volumename);
196 CREATE SEQUENCE MediaType_seq;
197 CREATE TABLE MediaType (
198 MediaTypeId INTEGER DEFAULT MediaType_seq.NEXTVAL,
199 MediaType TEXT NOT NULL,
200 ReadOnly INTEGER DEFAULT 0,
201 PRIMARY KEY(MediaTypeId)
204 CREATE SEQUENCE Storage_seq;
205 CREATE TABLE Storage (
206 StorageId INTEGER DEFAULT Storage_seq.NEXTVAL,
208 AutoChanger INTEGER DEFAULT 0,
209 PRIMARY KEY(StorageId)
212 CREATE SEQUENCE Device_seq;
213 CREATE TABLE Device (
214 DeviceId INTEGER DEFAULT Device_seq.NEXTVAL,
216 MediaTypeId INTEGER NOT NULL,
217 StorageId INTEGER NOT NULL,
218 DevMounts INTEGER NOT NULL DEFAULT 0,
219 DevReadBytes BIGINT NOT NULL DEFAULT 0,
220 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
221 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
222 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
223 DevReadTime BIGINT NOT NULL DEFAULT 0,
224 DevWriteTime BIGINT NOT NULL DEFAULT 0,
225 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
226 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
227 CleaningDate timestamp without time zone,
228 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
229 PRIMARY KEY(DeviceId)
233 CREATE SEQUENCE pool_seq;
236 poolid integer not null default pool_seq.nextval,
238 numvols integer default 0,
239 maxvols integer default 0,
240 useonce smallint default 0,
241 usecatalog smallint default 0,
242 acceptanyvolume smallint default 0,
243 volretention bigint default 0,
244 voluseduration bigint default 0,
245 maxvoljobs integer default 0,
246 maxvolfiles integer default 0,
247 maxvolbytes bigint default 0,
248 autoprune smallint default 0,
249 recycle smallint default 0,
250 ActionOnPurge smallint default 0,
252 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
253 labeltype integer default 0,
254 labelformat text not null,
255 enabled smallint default 1,
256 scratchpoolid integer default 0,
257 recyclepoolid integer default 0,
258 NextPoolId integer default 0,
259 MigrationHighBytes BIGINT DEFAULT 0,
260 MigrationLowBytes BIGINT DEFAULT 0,
261 MigrationTime BIGINT DEFAULT 0,
265 CREATE INDEX pool_name_idx on pool (name);
267 CREATE SEQUENCE client_seq;
270 clientid integer not null default client_seq.nextval,
273 autoprune smallint default 0,
274 fileretention bigint default 0,
275 jobretention bigint default 0,
276 primary key (clientid)
279 create unique index client_name_idx on client (name);
281 CREATE SEQUENCE Log_seq;
284 LogId integer not null default Log_seq.nextval,
285 JobId integer not null,
286 Time timestamp without time zone,
287 LogText text not null,
290 create index log_name_idx on Log (JobId);
292 CREATE SEQUENCE LocationLog_seq;
293 CREATE TABLE LocationLog (
294 LocLogId INTEGER NOT NULL DEFAULT LocationLog_seq.NEXTVAL,
295 Date timestamp without time zone,
296 Comment TEXT NOT NULL,
297 MediaId INTEGER DEFAULT 0,
298 LocationId INTEGER DEFAULT 0,
299 newvolstatus text not null
300 check (newvolstatus in ('Full','Archive','Append',
301 'Recycle','Purged','Read-Only','Disabled',
302 'Error','Busy','Used','Cleaning','Scratch')),
304 PRIMARY KEY(LocLogId)
309 CREATE TABLE counters
311 counter text not null,
312 minvalue integer default 0,
313 maxvalue integer default 0,
314 currentvalue integer default 0,
315 wrapcounter text not null,
316 primary key (counter)
321 CREATE SEQUENCE basefiles_seq;
322 CREATE TABLE basefiles
324 baseid integer not null default basefiles_seq.nextval,
325 jobid integer not null,
326 fileid bigint not null,
332 CREATE TABLE unsavedfiles
334 UnsavedId integer not null,
335 jobid integer not null,
336 pathid integer not null,
337 filenameid integer not null,
338 primary key (UnsavedId)
341 CREATE TABLE CDImages
343 MediaId integer not null,
344 LastBurn timestamp without time zone not null,
345 primary key (MediaId)
351 versionid integer not null
354 CREATE TABLE Status (
355 JobStatus CHAR(1) NOT NULL,
357 PRIMARY KEY (JobStatus)
360 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
361 ('C', 'Created, not yet running');
362 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
364 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
366 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
367 ('T', 'Completed successfully');
368 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
369 ('E', 'Terminated with errors');
370 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
371 ('e', 'Non-fatal error');
372 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
373 ('f', 'Fatal error');
374 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
375 ('D', 'Verify found differences');
376 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
377 ('A', 'Canceled by user');
378 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
379 ('F', 'Waiting for Client');
380 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
381 ('S', 'Waiting for Storage daemon');
382 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
383 ('m', 'Waiting for new media');
384 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
385 ('M', 'Waiting for media mount');
386 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
387 ('s', 'Waiting for storage resource');
388 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
389 ('j', 'Waiting for job resource');
390 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
391 ('c', 'Waiting for client resource');
392 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
393 ('d', 'Waiting on maximum jobs');
394 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
395 ('t', 'Waiting on start time');
396 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
397 ('p', 'Waiting on higher priority jobs');
398 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
399 ('a', 'SD despooling attributes');
400 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
401 ('i', 'Doing batch insert file records');
403 INSERT INTO Version (VersionId) VALUES (11);
405 -- Make sure we have appropriate permissions
412 echo "Creation of Bacula Ingres tables succeeded."
414 echo "Creation of Bacula Ingres tables failed."