3 # shell script to create Bacula PostgreSQL tables
7 $bindir/psql -f - -d bacula $* <<END-OF-DATA
11 filenameid serial not null,
13 primary key (filenameid)
16 CREATE INDEX filename_name_idx on filename (name);
20 pathid serial not null,
25 CREATE INDEX path_name_idx on path (path);
29 fileid serial not null,
30 fileindex integer not null default 0,
31 jobid integer not null,
32 pathid integer not null,
33 filenameid integer not null,
34 markid integer not null default 0,
40 CREATE INDEX file_jobid_idx on file (jobid);
41 CREATE INDEX file_fp_idx on file (filenameid, pathid);
44 -- Possibly add one or more of the following indexes
45 -- if your Verifies are too slow.
47 -- CREATE INDEX file_pathid_idx on file(pathid);
48 -- CREATE INDEX file_filenameid_idx on file(filenameid);
49 -- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
53 jobid serial not null,
56 type char(1) not null,
57 level char(1) not null,
58 clientid integer default 0,
59 jobstatus char(1) not null,
60 schedtime timestamp without time zone,
61 starttime timestamp without time zone,
62 endtime timestamp without time zone,
63 realendtime timestamp without time zone,
64 jobtdate bigint default 0,
65 volsessionid integer default 0,
66 volsessiontime integer default 0,
67 jobfiles integer default 0,
68 jobbytes bigint default 0,
69 joberrors integer default 0,
70 jobmissingfiles integer default 0,
71 poolid integer default 0,
72 filesetid integer default 0,
73 purgedfiles smallint default 0,
74 hasbase smallint default 0,
75 priorjobid integer default 0,
79 CREATE INDEX job_name_idx on job (name);
81 CREATE TABLE Location (
82 LocationId serial not null,
83 Location text not null,
84 Cost integer default 0,
86 primary key (LocationId)
92 filesetid serial not null,
93 fileset text not null,
95 createtime timestamp without time zone not null,
96 primary key (filesetid)
99 CREATE INDEX fileset_name_idx on fileset (fileset);
101 CREATE TABLE jobmedia
103 jobmediaid serial not null,
104 jobid integer not null,
105 mediaid integer not null,
106 firstindex integer default 0,
107 lastindex integer default 0,
108 startfile integer default 0,
109 endfile integer default 0,
110 startblock bigint default 0,
111 endblock bigint default 0,
112 volindex integer default 0,
113 copy integer default 0,
114 primary key (jobmediaid)
117 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
121 mediaid serial not null,
122 volumename text not null,
123 slot integer default 0,
124 poolid integer default 0,
125 mediatype text not null,
126 mediatypeid integer default 0,
127 labeltype integer default 0,
128 firstwritten timestamp without time zone,
129 lastwritten timestamp without time zone,
130 labeldate timestamp without time zone,
131 voljobs integer default 0,
132 volfiles integer default 0,
133 volblocks integer default 0,
134 volmounts integer default 0,
135 volbytes bigint default 0,
136 volparts integer default 0,
137 volerrors integer default 0,
138 volwrites integer default 0,
139 volcapacitybytes bigint default 0,
140 volstatus text not null
141 check (volstatus in ('Full','Archive','Append',
142 'Recycle','Purged','Read-Only','Disabled',
143 'Error','Busy','Used','Cleaning','Scratch')),
144 enabled smallint default 1,
145 recycle smallint default 0,
146 volretention bigint default 0,
147 voluseduration bigint default 0,
148 maxvoljobs integer default 0,
149 maxvolfiles integer default 0,
150 maxvolbytes bigint default 0,
151 inchanger smallint default 0,
152 StorageId integer default 0,
153 DeviceId integer default 0,
154 mediaaddressing smallint default 0,
155 volreadtime bigint default 0,
156 volwritetime bigint default 0,
157 endfile integer default 0,
158 endblock bigint default 0,
159 LocationId integer default 0,
160 recyclecount integer default 0,
161 initialwrite timestamp without time zone,
162 scratchpoolid integer default 0,
163 recyclepoolid integer default 0,
165 primary key (mediaid)
168 create unique index media_volumename_id on media (volumename);
171 CREATE TABLE MediaType (
173 MediaType TEXT NOT NULL,
174 ReadOnly INTEGER DEFAULT 0,
175 PRIMARY KEY(MediaTypeId)
178 CREATE TABLE Storage (
181 AutoChanger INTEGER DEFAULT 0,
182 PRIMARY KEY(StorageId)
185 CREATE TABLE Device (
188 MediaTypeId INTEGER NOT NULL,
189 StorageId INTEGER NOT NULL,
190 DevMounts INTEGER NOT NULL DEFAULT 0,
191 DevReadBytes BIGINT NOT NULL DEFAULT 0,
192 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
193 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
194 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
195 DevReadTime BIGINT NOT NULL DEFAULT 0,
196 DevWriteTime BIGINT NOT NULL DEFAULT 0,
197 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
198 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
199 CleaningDate timestamp without time zone,
200 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
201 PRIMARY KEY(DeviceId)
207 poolid serial not null,
209 numvols integer default 0,
210 maxvols integer default 0,
211 useonce smallint default 0,
212 usecatalog smallint default 0,
213 acceptanyvolume smallint default 0,
214 volretention bigint default 0,
215 voluseduration bigint default 0,
216 maxvoljobs integer default 0,
217 maxvolfiles integer default 0,
218 maxvolbytes bigint default 0,
219 autoprune smallint default 0,
220 recycle smallint default 0,
222 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
223 labeltype integer default 0,
224 labelformat text not null,
225 enabled smallint default 1,
226 scratchpoolid integer default 0,
227 recyclepoolid integer default 0,
228 NextPoolId integer default 0,
229 MigrationHighBytes BIGINT DEFAULT 0,
230 MigrationLowBytes BIGINT DEFAULT 0,
231 MigrationTime BIGINT DEFAULT 0,
235 CREATE INDEX pool_name_idx on pool (name);
239 clientid serial not null,
242 autoprune smallint default 0,
243 fileretention bigint default 0,
244 jobretention bigint default 0,
245 primary key (clientid)
248 create unique index client_name_idx on client (name);
252 LogId serial not null,
253 JobId integer not null,
254 Time timestamp without time zone,
255 LogText text not null,
258 create index log_name_idx on Log (JobId);
260 CREATE TABLE LocationLog (
261 LocLogId SERIAL NOT NULL,
262 Date timestamp without time zone,
263 Comment TEXT NOT NULL,
264 MediaId INTEGER DEFAULT 0,
265 LocationId INTEGER DEFAULT 0,
266 newvolstatus text not null
267 check (newvolstatus in ('Full','Archive','Append',
268 'Recycle','Purged','Read-Only','Disabled',
269 'Error','Busy','Used','Cleaning','Scratch')),
271 PRIMARY KEY(LocLogId)
276 CREATE TABLE counters
278 counter text not null,
279 minvalue integer default 0,
280 maxvalue integer default 0,
281 currentvalue integer default 0,
282 wrapcounter text not null,
283 primary key (counter)
288 CREATE TABLE basefiles
290 baseid serial not null,
291 jobid integer not null,
292 fileid integer not null,
298 CREATE TABLE unsavedfiles
300 UnsavedId integer not null,
301 jobid integer not null,
302 pathid integer not null,
303 filenameid integer not null,
304 primary key (UnsavedId)
307 CREATE TABLE CDImages
309 MediaId integer not null,
310 LastBurn timestamp without time zone not null,
311 primary key (MediaId)
317 versionid integer not null
320 CREATE TABLE Status (
321 JobStatus CHAR(1) NOT NULL,
323 PRIMARY KEY (JobStatus)
326 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
327 ('C', 'Created, not yet running');
328 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
330 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
332 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
333 ('T', 'Completed successfully');
334 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
335 ('E', 'Terminated with errors');
336 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
337 ('e', 'Non-fatal error');
338 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
339 ('f', 'Fatal error');
340 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
341 ('D', 'Verify found differences');
342 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
343 ('A', 'Canceled by user');
344 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
345 ('F', 'Waiting for Client');
346 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
347 ('S', 'Waiting for Storage daemon');
348 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
349 ('m', 'Waiting for new media');
350 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
351 ('M', 'Waiting for media mount');
352 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
353 ('s', 'Waiting for storage resource');
354 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
355 ('j', 'Waiting for job resource');
356 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
357 ('c', 'Waiting for client resource');
358 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
359 ('d', 'Waiting on maximum jobs');
360 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
361 ('t', 'Waiting on start time');
362 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
363 ('p', 'Waiting on higher priority jobs');
366 INSERT INTO Version (VersionId) VALUES (10);
368 -- Make sure we have appropriate permissions
375 echo "Creation of Bacula PostgreSQL tables succeeded."
377 echo "Creation of Bacula PostgreSQL tables failed."