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 table jobmedia
211 jobmediaid serial not null,
212 jobid integer not null,
213 mediaid integer not null,
214 firstindex integer not null
216 lastindex integer not null
218 startfile integer not null
220 endfile integer not null
222 startblock bigint not null
224 endblock bigint not null
226 volindex integer not null
228 primary key (jobmediaid)
231 create index job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
233 create table basefiles
235 baseid serial not null,
236 jobid integer not null,
237 fileid integer not null,
243 create table unsavedfiles
245 UnsavedId integer not null,
246 jobid integer not null,
247 pathid integer not null,
248 filenameid integer not null,
249 primary key (UnsavedId)
252 -- Make sure we have appropriate permissions
255 -- The following alter table commands have been removed
256 -- because they seem to create severe performance problems
259 -- add foreign key (scratchpoolid)
260 -- references pool (poolid) on update cascade on delete cascade;
263 -- add foreign key (recyclepoolid)
264 -- references pool (poolid) on update cascade on delete cascade;
267 -- add foreign key (poolid)
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 (filesetid)
276 -- references fileset (filesetid) on update cascade on delete cascade;
279 -- add foreign key (clientid)
280 -- references client (clientid) on update cascade on delete cascade;
283 -- add foreign key (jobid)
284 -- references job (jobid) on update cascade on delete cascade;
287 -- add foreign key (pathid)
288 -- references path (pathid) on update cascade on delete restrict;
291 -- add foreign key (filenameid)
292 -- references filename (filenameid) on update cascade on delete cascade;
294 --alter table jobmedia
295 -- add foreign key (jobid)
296 -- references job (jobid) on update cascade on delete cascade;
298 --alter table jobmedia
299 -- add foreign key (mediaid)
300 -- references media (mediaid) on update cascade on delete cascade;
302 --alter table basefiles
303 -- add foreign key (jobid)
304 -- references job (jobid) on update cascade on delete cascade;
306 --alter table basefiles
307 -- add foreign key (fileid)
308 -- references file (fileid) on update cascade on delete cascade;
310 --alter table basefiles
311 -- add foreign key (basejobid)
312 -- references job (jobid) on update cascade on delete cascade;
314 --alter table unsavedfiles
315 -- add foreign key (jobid)
316 -- references job (jobid) on update restrict on delete restrict;
318 --alter table unsavedfiles
319 -- add foreign key (pathid)
320 -- references path (pathid) on update restrict on delete restrict;
322 --alter table unsavedfiles
323 -- add foreign key (filenameid)
324 -- references filename (filenameid) on update restrict on delete restrict;
328 echo "Creation of Bacula PostgreSQL tables succeeded."
330 echo "Creation of Bacula PostgreSQL tables failed."