3 # shell script to create Bacula PostgreSQL tables
7 if $bindir/psql $* bacula -f - <<END-OF-DATA
11 versionid integer not null
14 INSERT INTO Version (VersionId) VALUES (7);
18 counter text not null,
21 currentvalue integer ,
22 wrapcounter text not null,
28 filenameid serial not null,
30 primary key (filenameid)
33 create index filename_name_idx on filename (name);
37 pathid serial not null,
42 create index path_name_idx on path (path);
46 filesetid serial not null,
47 fileset text not null,
49 createtime timestamp without time zone not null,
50 primary key (filesetid)
53 create index fileset_name_idx on fileset (fileset);
57 poolid serial not null,
59 numvols integer not null
61 maxvols integer not null
63 useonce smallint not null,
64 usecatalog smallint not null,
65 acceptanyvolume smallint
67 volretention bigint not null,
68 voluseduration bigint not null,
69 maxvoljobs integer not null
71 maxvolfiles integer not null
73 maxvolbytes bigint not null,
74 autoprune smallint not null
79 check (pooltype is null or (pooltype in ('Backup','Copy','Cloned','Archive','Migration'))),
80 labelformat text not null,
81 enabled smallint not null
83 scratchpoolid integer ,
84 recyclepoolid integer ,
88 create index pool_name_idx on pool (name);
92 clientid serial not null,
97 fileretention bigint not null,
98 jobretention bigint not null,
99 primary key (clientid)
102 create unique index client_name_idx on client (name);
106 mediaid serial not null,
107 volumename text not null,
108 slot integer not null
110 poolid integer not null,
111 mediatype text not null,
112 firstwritten timestamp without time zone,
113 lastwritten timestamp without time zone,
114 labeldate timestamp without time zone,
115 voljobs integer not null
117 volfiles integer not null
119 volblocks integer not null
121 volmounts integer not null
123 volbytes bigint not null
125 volerrors integer not null
127 volwrites integer not null
129 volcapacitybytes bigint not null,
130 volstatus text not null
131 check (volstatus in ('Full','Archive','Append','Recycle','Purged','Read-Only','Disabled','Error','Busy',
133 recycle smallint not null
135 volretention bigint not null
137 voluseduration bigint not null
139 maxvoljobs integer not null
141 maxvolfiles integer not null
143 maxvolbytes bigint not null
145 inchanger smallint not null
147 mediaaddressing smallint not null
149 volreadtime bigint not null
151 volwritetime bigint not null
153 primary key (mediaid)
156 create unique index media_volumename_id on media (volumename);
160 jobid serial not null,
163 type char(1) not null,
164 level char(1) not null,
166 jobstatus char(1) not null,
167 schedtime timestamp without time zone not null,
168 starttime timestamp without time zone ,
169 endtime timestamp without time zone ,
170 jobtdate bigint not null,
171 volsessionid integer not null
173 volsessiontime integer not null
175 jobfiles integer not null
177 jobbytes bigint not null
179 joberrors integer not null
181 jobmissingfiles integer not null
185 purgedfiles smallint not null
187 hasbase smallint not null
192 create index job_name_idx on job (name);
196 fileid serial not null,
197 fileindex integer not null
199 jobid integer not null,
200 pathid integer not null,
201 filenameid bigint not null,
202 markid integer not null
209 create index file_jobid_idx on file (jobid);
210 create index file_pathid_idx on file(pathid);
211 create index file_filenameid_idx on file(filenameid);
213 create table jobmedia
215 jobmediaid serial not null,
216 jobid integer not null,
217 mediaid integer not null,
218 firstindex integer not null
220 lastindex integer not null
222 startfile integer not null
224 endfile integer not null
226 startblock bigint not null
228 endblock bigint not null
230 volindex integer not null
232 primary key (jobmediaid)
235 create index job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
237 create table basefiles
239 baseid serial not null,
240 jobid integer not null,
241 fileid integer not null,
247 create table unsavedfiles
249 UnsavedId integer not null,
250 jobid integer not null,
251 pathid integer not null,
252 filenameid integer not null,
253 primary key (UnsavedId)
256 -- Make sure we have appropriate permissions
259 -- The following alter table commands have been removed
260 -- because they seem to create severe performance problems
263 -- add foreign key (scratchpoolid)
264 -- references pool (poolid) on update cascade on delete cascade;
267 -- add foreign key (recyclepoolid)
268 -- references pool (poolid) on update cascade on delete cascade;
271 -- add foreign key (poolid)
272 -- references pool (poolid) on update cascade on delete cascade;
275 -- add foreign key (poolid)
276 -- references pool (poolid) on update cascade on delete cascade;
279 -- add foreign key (filesetid)
280 -- references fileset (filesetid) on update cascade on delete cascade;
283 -- add foreign key (clientid)
284 -- references client (clientid) on update cascade on delete cascade;
287 -- add foreign key (jobid)
288 -- references job (jobid) on update cascade on delete cascade;
291 -- add foreign key (pathid)
292 -- references path (pathid) on update cascade on delete restrict;
295 -- add foreign key (filenameid)
296 -- references filename (filenameid) on update cascade on delete cascade;
298 --alter table jobmedia
299 -- add foreign key (jobid)
300 -- references job (jobid) on update cascade on delete cascade;
302 --alter table jobmedia
303 -- add foreign key (mediaid)
304 -- references media (mediaid) on update cascade on delete cascade;
306 --alter table basefiles
307 -- add foreign key (jobid)
308 -- references job (jobid) on update cascade on delete cascade;
310 --alter table basefiles
311 -- add foreign key (fileid)
312 -- references file (fileid) on update cascade on delete cascade;
314 --alter table basefiles
315 -- add foreign key (basejobid)
316 -- references job (jobid) on update cascade on delete cascade;
318 --alter table unsavedfiles
319 -- add foreign key (jobid)
320 -- references job (jobid) on update restrict on delete restrict;
322 --alter table unsavedfiles
323 -- add foreign key (pathid)
324 -- references path (pathid) on update restrict on delete restrict;
326 --alter table unsavedfiles
327 -- add foreign key (filenameid)
328 -- references filename (filenameid) on update restrict on delete restrict;
332 echo "Creation of Bacula PostgreSQL tables succeeded."
334 echo "Creation of Bacula PostgreSQL tables failed."