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 inchanger smallint not null
151 mediaaddressing smallint not null
153 volreadtime bigint not null
155 volwritetime bigint not null
157 primary key (mediaid)
160 create unique index media_volumename_id on media (volumename);
164 jobid serial not null,
167 type char(1) not null,
168 level char(1) not null,
170 jobstatus char(1) not null,
171 schedtime timestamp without time zone not null,
172 starttime timestamp without time zone ,
173 endtime timestamp without time zone ,
174 jobtdate bigint not null,
175 volsessionid integer not null
177 volsessiontime integer not null
179 jobfiles integer not null
181 jobbytes bigint not null
183 joberrors integer not null
185 jobmissingfiles integer not null
189 purgedfiles smallint not null
191 hasbase smallint not null
196 create index job_name_idx on job (name);
200 fileid serial not null,
201 fileindex integer not null
203 jobid integer not null,
204 pathid integer not null,
205 filenameid bigint not null,
206 markid integer not null
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 integer not null
228 endblock integer 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)
257 add foreign key (scratchpoolid)
258 references pool (poolid) on update cascade on delete cascade;
261 add foreign key (recyclepoolid)
262 references pool (poolid) on update cascade on delete cascade;
265 add foreign key (poolid)
266 references pool (poolid) on update cascade on delete cascade;
269 add foreign key (poolid)
270 references pool (poolid) on update cascade on delete cascade;
273 add foreign key (filesetid)
274 references fileset (filesetid) on update cascade on delete cascade;
277 add foreign key (clientid)
278 references client (clientid) on update cascade on delete cascade;
281 add foreign key (jobid)
282 references job (jobid) on update cascade on delete cascade;
285 add foreign key (pathid)
286 references path (pathid) on update cascade on delete restrict;
289 add foreign key (filenameid)
290 references filename (filenameid) on update cascade on delete cascade;
293 add foreign key (jobid)
294 references job (jobid) on update cascade on delete cascade;
297 add foreign key (mediaid)
298 references media (mediaid) on update cascade on delete cascade;
300 alter table basefiles
301 add foreign key (jobid)
302 references job (jobid) on update cascade on delete cascade;
304 alter table basefiles
305 add foreign key (fileid)
306 references file (fileid) on update cascade on delete cascade;
308 alter table basefiles
309 add foreign key (basejobid)
310 references job (jobid) on update cascade on delete cascade;
312 alter table unsavedfiles
313 add foreign key (jobid)
314 references job (jobid) on update restrict on delete restrict;
316 alter table unsavedfiles
317 add foreign key (pathid)
318 references path (pathid) on update restrict on delete restrict;
320 alter table unsavedfiles
321 add foreign key (filenameid)
322 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."