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 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 endfile integer not null
155 endblock 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 integer not null,
206 markid integer not null
213 create index file_jobid_idx on file (jobid);
214 create index file_pathid_idx on file(pathid);
215 create index file_filenameid_idx on file(filenameid);
216 create index file_jpfid_idx on file (jobid, pathid, filenameid);
218 create table jobmedia
220 jobmediaid serial not null,
221 jobid integer not null,
222 mediaid integer not null,
223 firstindex integer not null
225 lastindex integer not null
227 startfile integer not null
229 endfile integer not null
231 startblock bigint not null
233 endblock bigint not null
235 volindex integer not null
237 primary key (jobmediaid)
240 create index job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
242 create table basefiles
244 baseid serial not null,
245 jobid integer not null,
246 fileid integer not null,
252 create table unsavedfiles
254 UnsavedId integer not null,
255 jobid integer not null,
256 pathid integer not null,
257 filenameid integer not null,
258 primary key (UnsavedId)
261 create table CDImages
263 MediaId integer not null,
264 LastBurn timestamp without time zone not null,
265 primary key (MediaId)
268 -- Make sure we have appropriate permissions
273 echo "Creation of Bacula PostgreSQL tables succeeded."
275 echo "Creation of Bacula PostgreSQL tables failed."