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