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 (8);
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 volparts integer not null
127 volerrors integer not null
129 volwrites integer not null
131 volcapacitybytes bigint not null,
132 volstatus text not null
133 check (volstatus in ('Full','Archive','Append','Recycle','Purged','Read-Only','Disabled','Error','Busy',
135 recycle smallint not null
137 volretention bigint not null
139 voluseduration bigint not null
141 maxvoljobs integer not null
143 maxvolfiles integer not null
145 maxvolbytes bigint not null
147 inchanger smallint not null
149 mediaaddressing smallint not null
151 volreadtime bigint not null
153 volwritetime bigint not null
155 endfile integer not null
157 endblock bigint not null
159 primary key (mediaid)
162 create unique index media_volumename_id on media (volumename);
166 jobid serial not null,
169 type char(1) not null,
170 level char(1) not null,
172 jobstatus char(1) not null,
173 schedtime timestamp without time zone not null,
174 starttime timestamp without time zone ,
175 endtime timestamp without time zone ,
176 jobtdate bigint not null,
177 volsessionid integer not null
179 volsessiontime integer not null
181 jobfiles integer not null
183 jobbytes bigint not null
185 joberrors integer not null
187 jobmissingfiles integer not null
191 purgedfiles smallint not null
193 hasbase smallint not null
198 create index job_name_idx on job (name);
202 fileid serial not null,
203 fileindex integer not null
205 jobid integer not null,
206 pathid integer not null,
207 filenameid integer not null,
208 markid integer not null
215 create index file_jobid_idx on file (jobid);
216 create index file_fp_idx on file (filenameid, pathid);
219 -- Possibly add one or more of the following indexes
220 -- if your Verifies are too slow.
222 -- create index file_pathid_idx on file(pathid);
223 -- create index file_filenameid_idx on file(filenameid);
224 -- create index file_jpfid_idx on file (jobid, pathid, filenameid);
226 create table jobmedia
228 jobmediaid serial not null,
229 jobid integer not null,
230 mediaid integer not null,
231 firstindex integer not null
233 lastindex integer not null
235 startfile integer not null
237 endfile integer not null
239 startblock bigint not null
241 endblock bigint not null
243 volindex integer not null
245 primary key (jobmediaid)
248 create index job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
250 create table basefiles
252 baseid serial not null,
253 jobid integer not null,
254 fileid integer not null,
260 create table unsavedfiles
262 UnsavedId integer not null,
263 jobid integer not null,
264 pathid integer not null,
265 filenameid integer not null,
266 primary key (UnsavedId)
269 create table CDImages
271 MediaId integer not null,
272 LastBurn timestamp without time zone not null,
273 primary key (MediaId)
276 -- Make sure we have appropriate permissions
281 echo "Creation of Bacula PostgreSQL tables succeeded."
283 echo "Creation of Bacula PostgreSQL tables failed."