3 # shell script to create Bacula PostgreSQL tables
7 if $bindir/psql $* -f - <<END-OF-DATA
12 versionid integer not null
15 INSERT INTO Version (VersionId) VALUES (7);
19 counter text not null,
22 currentvalue integer ,
23 wrapcounter text not null,
29 filenameid serial not null,
31 primary key (filenameid)
34 create index filename_name_idx on filename (name);
38 pathid serial not null,
43 create index path_name_idx on path (path);
47 filesetid serial not null,
48 fileset text not null,
50 createtime timestamp without time zone not null,
51 primary key (filesetid)
54 create index fileset_name_idx on fileset (fileset);
58 poolid serial not null,
60 numvols integer not null
62 maxvols integer not null
64 useonce smallint not null,
65 usecatalog smallint not null,
66 acceptanyvolume smallint
68 volretention bigint not null,
69 voluseduration bigint not null,
70 maxvoljobs integer not null
72 maxvolfiles integer not null
74 maxvolbytes bigint not null,
75 autoprune smallint not null
80 check (pooltype is null or (pooltype in ('Backup','Copy','Cloned','Archive','Migration'))),
81 labelformat text not null,
82 enabled smallint not null
84 scratchpoolid integer ,
85 recyclepoolid integer ,
89 create index pool_name_idx on pool (name);
93 clientid serial not null,
98 fileretention bigint not null,
99 jobretention bigint not null,
100 primary key (clientid)
103 create unique index client_name_idx on client (name);
107 mediaid serial not null,
108 volumename text not null,
109 slot integer not null
111 poolid integer not null,
112 mediatype text not null,
113 firstwritten timestamp without time zone not null
114 default current_timestamp,
115 lastwritten timestamp without time zone not null
116 default current_timestamp,
117 labeldate timestamp without time zone not null
118 default current_timestamp,
119 voljobs integer not null
121 volfiles integer not null
123 volblocks integer not null
125 volmounts integer not null
127 volbytes bigint not null
129 volerrors integer not null
131 volwrites integer not null
133 volcapacitybytes bigint not null,
134 volstatus text not null
135 check (volstatus in ('Full','Archive','Append','Recycle','Purged','Read-Only','Disabled','Error','Busy',
137 recycle smallint not null
139 volretention bigint not null
141 voluseduration bigint not null
143 maxvoljobs integer not null
145 maxvolfiles integer not null
147 maxvolbytes bigint not null
149 drive integer not null
151 inchanger smallint not null
153 mediaaddressing smallint not null
155 primary key (mediaid)
158 create unique index media_volumename_id on media (volumename);
162 jobid serial not null,
165 type char(1) not null,
166 level char(1) not null,
168 jobstatus char(1) not null,
169 schedtime timestamp without time zone not null,
170 starttime timestamp without time zone ,
171 endtime timestamp without time zone ,
172 jobtdate bigint not null,
173 volsessionid integer not null
175 volsessiontime integer not null
177 jobfiles integer not null
179 jobbytes bigint not null
181 joberrors integer not null
183 jobmissingfiles integer not null
187 purgedfiles smallint not null
189 hasbase smallint not null
194 create index job_name_idx on job (name);
198 fileid serial not null,
199 fileindex integer not null
201 jobid integer not null,
202 pathid integer not null,
203 filenameid bigint not null,
204 markid integer not null
211 create table jobmedia
213 jobmediaid serial not null,
214 jobid integer not null,
215 mediaid integer not null,
216 firstindex integer not null
218 lastindex integer not null
220 startfile integer not null
222 endfile integer not null
224 startblock integer not null
226 endblock integer not null
228 volindex integer not null
230 primary key (jobmediaid)
233 create index job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
235 create table basefiles
237 baseid serial not null,
238 jobid integer not null,
239 fileid integer not null,
245 create table unsavedfiles
247 UnsavedId integer not null,
248 jobid integer not null,
249 pathid integer not null,
250 filenameid integer not null,
251 primary key (UnsavedId)
255 add foreign key (scratchpoolid)
256 references pool (poolid) on update cascade on delete cascade;
259 add foreign key (recyclepoolid)
260 references pool (poolid) on update cascade on delete cascade;
263 add foreign key (poolid)
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 (filesetid)
272 references fileset (filesetid) on update cascade on delete cascade;
275 add foreign key (clientid)
276 references client (clientid) on update cascade on delete cascade;
279 add foreign key (jobid)
280 references job (jobid) on update cascade on delete cascade;
283 add foreign key (pathid)
284 references path (pathid) on update cascade on delete restrict;
287 add foreign key (filenameid)
288 references filename (filenameid) on update cascade on delete cascade;
291 add foreign key (jobid)
292 references job (jobid) on update cascade on delete cascade;
295 add foreign key (mediaid)
296 references media (mediaid) on update cascade on delete cascade;
298 alter table basefiles
299 add foreign key (jobid)
300 references job (jobid) on update cascade on delete cascade;
302 alter table basefiles
303 add foreign key (fileid)
304 references file (fileid) on update cascade on delete cascade;
306 alter table basefiles
307 add foreign key (basejobid)
308 references job (jobid) on update cascade on delete cascade;
310 alter table unsavedfiles
311 add foreign key (jobid)
312 references job (jobid) on update restrict on delete restrict;
314 alter table unsavedfiles
315 add foreign key (pathid)
316 references path (pathid) on update restrict on delete restrict;
318 alter table unsavedfiles
319 add foreign key (filenameid)
320 references filename (filenameid) on update restrict on delete restrict;
326 echo "Creation of Bacula PostgreSQL tables succeeded."
328 echo "Creation of Bacula PostgreSQL tables failed."