3 # shell script to create Bacula PostgreSQL tables
8 $bindir/psql -f - -d ${db_name} $* <<END-OF-DATA
12 filenameid serial not null,
14 primary key (filenameid)
17 CREATE INDEX filename_name_idx on filename (name);
21 pathid serial not null,
26 CREATE INDEX path_name_idx on path (path);
30 fileid serial not null,
31 fileindex integer not null default 0,
32 jobid integer not null,
33 pathid integer not null,
34 filenameid integer not null,
35 markid integer not null default 0,
41 CREATE INDEX file_jobid_idx on file (jobid);
42 CREATE INDEX file_fp_idx on file (filenameid, pathid);
45 -- Possibly add one or more of the following indexes
46 -- if your Verifies are too slow.
48 -- CREATE INDEX file_pathid_idx on file(pathid);
49 -- CREATE INDEX file_filenameid_idx on file(filenameid);
50 -- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
54 jobid serial not null,
57 type char(1) not null,
58 level char(1) not null,
59 clientid integer default 0,
60 jobstatus char(1) not null,
61 schedtime timestamp without time zone,
62 starttime timestamp without time zone,
63 endtime timestamp without time zone,
64 realendtime timestamp without time zone,
65 jobtdate bigint default 0,
66 volsessionid integer default 0,
67 volsessiontime integer default 0,
68 jobfiles integer default 0,
69 jobbytes bigint default 0,
70 joberrors integer default 0,
71 jobmissingfiles integer default 0,
72 poolid integer default 0,
73 filesetid integer default 0,
74 purgedfiles smallint default 0,
75 hasbase smallint default 0,
76 priorjobid integer default 0,
80 CREATE INDEX job_name_idx on job (name);
82 CREATE TABLE Location (
83 LocationId serial not null,
84 Location text not null,
85 Cost integer default 0,
87 primary key (LocationId)
93 filesetid serial not null,
94 fileset text not null,
96 createtime timestamp without time zone not null,
97 primary key (filesetid)
100 CREATE INDEX fileset_name_idx on fileset (fileset);
102 CREATE TABLE jobmedia
104 jobmediaid serial not null,
105 jobid integer not null,
106 mediaid integer not null,
107 firstindex integer default 0,
108 lastindex integer default 0,
109 startfile integer default 0,
110 endfile integer default 0,
111 startblock bigint default 0,
112 endblock bigint default 0,
113 volindex integer default 0,
114 copy integer default 0,
115 primary key (jobmediaid)
118 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
122 mediaid serial not null,
123 volumename text not null,
124 slot integer default 0,
125 poolid integer default 0,
126 mediatype text not null,
127 mediatypeid integer default 0,
128 labeltype integer default 0,
129 firstwritten timestamp without time zone,
130 lastwritten timestamp without time zone,
131 labeldate timestamp without time zone,
132 voljobs integer default 0,
133 volfiles integer default 0,
134 volblocks integer default 0,
135 volmounts integer default 0,
136 volbytes bigint default 0,
137 volparts integer default 0,
138 volerrors integer default 0,
139 volwrites integer default 0,
140 volcapacitybytes bigint default 0,
141 volstatus text not null
142 check (volstatus in ('Full','Archive','Append',
143 'Recycle','Purged','Read-Only','Disabled',
144 'Error','Busy','Used','Cleaning','Scratch')),
145 enabled smallint default 1,
146 recycle smallint default 0,
147 volretention bigint default 0,
148 voluseduration bigint default 0,
149 maxvoljobs integer default 0,
150 maxvolfiles integer default 0,
151 maxvolbytes bigint default 0,
152 inchanger smallint default 0,
153 StorageId integer default 0,
154 DeviceId integer default 0,
155 mediaaddressing smallint default 0,
156 volreadtime bigint default 0,
157 volwritetime bigint default 0,
158 endfile integer default 0,
159 endblock bigint default 0,
160 LocationId integer default 0,
161 recyclecount integer default 0,
162 initialwrite timestamp without time zone,
163 scratchpoolid integer default 0,
164 recyclepoolid integer default 0,
166 primary key (mediaid)
169 create unique index media_volumename_id on media (volumename);
172 CREATE TABLE MediaType (
174 MediaType TEXT NOT NULL,
175 ReadOnly INTEGER DEFAULT 0,
176 PRIMARY KEY(MediaTypeId)
179 CREATE TABLE Storage (
182 AutoChanger INTEGER DEFAULT 0,
183 PRIMARY KEY(StorageId)
186 CREATE TABLE Device (
189 MediaTypeId INTEGER NOT NULL,
190 StorageId INTEGER NOT NULL,
191 DevMounts INTEGER NOT NULL DEFAULT 0,
192 DevReadBytes BIGINT NOT NULL DEFAULT 0,
193 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
194 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
195 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
196 DevReadTime BIGINT NOT NULL DEFAULT 0,
197 DevWriteTime BIGINT NOT NULL DEFAULT 0,
198 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
199 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
200 CleaningDate timestamp without time zone,
201 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
202 PRIMARY KEY(DeviceId)
208 poolid serial not null,
210 numvols integer default 0,
211 maxvols integer default 0,
212 useonce smallint default 0,
213 usecatalog smallint default 0,
214 acceptanyvolume smallint default 0,
215 volretention bigint default 0,
216 voluseduration bigint default 0,
217 maxvoljobs integer default 0,
218 maxvolfiles integer default 0,
219 maxvolbytes bigint default 0,
220 autoprune smallint default 0,
221 recycle smallint default 0,
223 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
224 labeltype integer default 0,
225 labelformat text not null,
226 enabled smallint default 1,
227 scratchpoolid integer default 0,
228 recyclepoolid integer default 0,
229 NextPoolId integer default 0,
230 MigrationHighBytes BIGINT DEFAULT 0,
231 MigrationLowBytes BIGINT DEFAULT 0,
232 MigrationTime BIGINT DEFAULT 0,
236 CREATE INDEX pool_name_idx on pool (name);
240 clientid serial not null,
243 autoprune smallint default 0,
244 fileretention bigint default 0,
245 jobretention bigint default 0,
246 primary key (clientid)
249 create unique index client_name_idx on client (name);
253 LogId serial not null,
254 JobId integer not null,
255 Time timestamp without time zone,
256 LogText text not null,
259 create index log_name_idx on Log (JobId);
261 CREATE TABLE LocationLog (
262 LocLogId SERIAL NOT NULL,
263 Date timestamp without time zone,
264 Comment TEXT NOT NULL,
265 MediaId INTEGER DEFAULT 0,
266 LocationId INTEGER DEFAULT 0,
267 newvolstatus text not null
268 check (newvolstatus in ('Full','Archive','Append',
269 'Recycle','Purged','Read-Only','Disabled',
270 'Error','Busy','Used','Cleaning','Scratch')),
272 PRIMARY KEY(LocLogId)
277 CREATE TABLE counters
279 counter text not null,
280 minvalue integer default 0,
281 maxvalue integer default 0,
282 currentvalue integer default 0,
283 wrapcounter text not null,
284 primary key (counter)
289 CREATE TABLE basefiles
291 baseid serial not null,
292 jobid integer not null,
293 fileid integer not null,
299 CREATE TABLE unsavedfiles
301 UnsavedId integer not null,
302 jobid integer not null,
303 pathid integer not null,
304 filenameid integer not null,
305 primary key (UnsavedId)
308 CREATE TABLE CDImages
310 MediaId integer not null,
311 LastBurn timestamp without time zone not null,
312 primary key (MediaId)
318 versionid integer not null
321 CREATE TABLE Status (
322 JobStatus CHAR(1) NOT NULL,
324 PRIMARY KEY (JobStatus)
327 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
328 ('C', 'Created, not yet running');
329 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
331 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
333 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
334 ('T', 'Completed successfully');
335 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
336 ('E', 'Terminated with errors');
337 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
338 ('e', 'Non-fatal error');
339 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
340 ('f', 'Fatal error');
341 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
342 ('D', 'Verify found differences');
343 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
344 ('A', 'Canceled by user');
345 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
346 ('F', 'Waiting for Client');
347 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
348 ('S', 'Waiting for Storage daemon');
349 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
350 ('m', 'Waiting for new media');
351 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
352 ('M', 'Waiting for media mount');
353 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
354 ('s', 'Waiting for storage resource');
355 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
356 ('j', 'Waiting for job resource');
357 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
358 ('c', 'Waiting for client resource');
359 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
360 ('d', 'Waiting on maximum jobs');
361 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
362 ('t', 'Waiting on start time');
363 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
364 ('p', 'Waiting on higher priority jobs');
367 INSERT INTO Version (VersionId) VALUES (10);
369 -- Make sure we have appropriate permissions
376 echo "Creation of Bacula PostgreSQL tables succeeded."
378 echo "Creation of Bacula PostgreSQL tables failed."