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 ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000;
18 CREATE INDEX filename_name_idx on filename (name);
22 pathid serial not null,
27 ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000;
28 CREATE INDEX path_name_idx on path (path);
32 fileid serial not null,
33 fileindex integer not null default 0,
34 jobid integer not null,
35 pathid integer not null,
36 filenameid integer not null,
37 markid integer not null default 0,
43 CREATE INDEX file_jobid_idx on file (jobid);
44 CREATE INDEX file_fp_idx on file (filenameid, pathid);
47 -- Possibly add one or more of the following indexes
48 -- if your Verifies are too slow.
50 -- CREATE INDEX file_pathid_idx on file(pathid);
51 -- CREATE INDEX file_filenameid_idx on file(filenameid);
52 -- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
56 jobid serial not null,
59 type char(1) not null,
60 level char(1) not null,
61 clientid integer default 0,
62 jobstatus char(1) not null,
63 schedtime timestamp without time zone,
64 starttime timestamp without time zone,
65 endtime timestamp without time zone,
66 realendtime timestamp without time zone,
67 jobtdate bigint default 0,
68 volsessionid integer default 0,
69 volsessiontime integer default 0,
70 jobfiles integer default 0,
71 jobbytes bigint default 0,
72 joberrors integer default 0,
73 jobmissingfiles integer default 0,
74 poolid integer default 0,
75 filesetid integer default 0,
76 purgedfiles smallint default 0,
77 hasbase smallint default 0,
78 priorjobid integer default 0,
82 CREATE INDEX job_name_idx on job (name);
84 -- Create a table like Job for long term statistics
85 CREATE TABLE jobstat (LIKE job);
87 CREATE TABLE Location (
88 LocationId serial not null,
89 Location text not null,
90 Cost integer default 0,
92 primary key (LocationId)
98 filesetid serial not null,
99 fileset text not null,
101 createtime timestamp without time zone not null,
102 primary key (filesetid)
105 CREATE INDEX fileset_name_idx on fileset (fileset);
107 CREATE TABLE jobmedia
109 jobmediaid serial not null,
110 jobid integer not null,
111 mediaid integer not null,
112 firstindex integer default 0,
113 lastindex integer default 0,
114 startfile integer default 0,
115 endfile integer default 0,
116 startblock bigint default 0,
117 endblock bigint default 0,
118 volindex integer default 0,
119 copy integer default 0,
120 primary key (jobmediaid)
123 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
127 mediaid serial not null,
128 volumename text not null,
129 slot integer default 0,
130 poolid integer default 0,
131 mediatype text not null,
132 mediatypeid integer default 0,
133 labeltype integer default 0,
134 firstwritten timestamp without time zone,
135 lastwritten timestamp without time zone,
136 labeldate timestamp without time zone,
137 voljobs integer default 0,
138 volfiles integer default 0,
139 volblocks integer default 0,
140 volmounts integer default 0,
141 volbytes bigint default 0,
142 volparts integer default 0,
143 volerrors integer default 0,
144 volwrites integer default 0,
145 volcapacitybytes bigint default 0,
146 volstatus text not null
147 check (volstatus in ('Full','Archive','Append',
148 'Recycle','Purged','Read-Only','Disabled',
149 'Error','Busy','Used','Cleaning','Scratch')),
150 enabled smallint default 1,
151 recycle smallint default 0,
152 volretention bigint default 0,
153 voluseduration bigint default 0,
154 maxvoljobs integer default 0,
155 maxvolfiles integer default 0,
156 maxvolbytes bigint default 0,
157 inchanger smallint default 0,
158 StorageId integer default 0,
159 DeviceId integer default 0,
160 mediaaddressing smallint default 0,
161 volreadtime bigint default 0,
162 volwritetime bigint default 0,
163 endfile integer default 0,
164 endblock bigint default 0,
165 LocationId integer default 0,
166 recyclecount integer default 0,
167 initialwrite timestamp without time zone,
168 scratchpoolid integer default 0,
169 recyclepoolid integer default 0,
171 primary key (mediaid)
174 create unique index media_volumename_id on media (volumename);
177 CREATE TABLE MediaType (
179 MediaType TEXT NOT NULL,
180 ReadOnly INTEGER DEFAULT 0,
181 PRIMARY KEY(MediaTypeId)
184 CREATE TABLE Storage (
187 AutoChanger INTEGER DEFAULT 0,
188 PRIMARY KEY(StorageId)
191 CREATE TABLE Device (
194 MediaTypeId INTEGER NOT NULL,
195 StorageId INTEGER NOT NULL,
196 DevMounts INTEGER NOT NULL DEFAULT 0,
197 DevReadBytes BIGINT NOT NULL DEFAULT 0,
198 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
199 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
200 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
201 DevReadTime BIGINT NOT NULL DEFAULT 0,
202 DevWriteTime BIGINT NOT NULL DEFAULT 0,
203 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
204 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
205 CleaningDate timestamp without time zone,
206 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
207 PRIMARY KEY(DeviceId)
213 poolid serial not null,
215 numvols integer default 0,
216 maxvols integer default 0,
217 useonce smallint default 0,
218 usecatalog smallint default 0,
219 acceptanyvolume smallint default 0,
220 volretention bigint default 0,
221 voluseduration bigint default 0,
222 maxvoljobs integer default 0,
223 maxvolfiles integer default 0,
224 maxvolbytes bigint default 0,
225 autoprune smallint default 0,
226 recycle smallint default 0,
228 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
229 labeltype integer default 0,
230 labelformat text not null,
231 enabled smallint default 1,
232 scratchpoolid integer default 0,
233 recyclepoolid integer default 0,
234 NextPoolId integer default 0,
235 MigrationHighBytes BIGINT DEFAULT 0,
236 MigrationLowBytes BIGINT DEFAULT 0,
237 MigrationTime BIGINT DEFAULT 0,
241 CREATE INDEX pool_name_idx on pool (name);
245 clientid serial not null,
248 autoprune smallint default 0,
249 fileretention bigint default 0,
250 jobretention bigint default 0,
251 primary key (clientid)
254 create unique index client_name_idx on client (name);
258 LogId serial not null,
259 JobId integer not null,
260 Time timestamp without time zone,
261 LogText text not null,
264 create index log_name_idx on Log (JobId);
266 CREATE TABLE LocationLog (
267 LocLogId SERIAL NOT NULL,
268 Date timestamp without time zone,
269 Comment TEXT NOT NULL,
270 MediaId INTEGER DEFAULT 0,
271 LocationId INTEGER DEFAULT 0,
272 newvolstatus text not null
273 check (newvolstatus in ('Full','Archive','Append',
274 'Recycle','Purged','Read-Only','Disabled',
275 'Error','Busy','Used','Cleaning','Scratch')),
277 PRIMARY KEY(LocLogId)
282 CREATE TABLE counters
284 counter text not null,
285 minvalue integer default 0,
286 maxvalue integer default 0,
287 currentvalue integer default 0,
288 wrapcounter text not null,
289 primary key (counter)
294 CREATE TABLE basefiles
296 baseid serial not null,
297 jobid integer not null,
298 fileid integer not null,
304 CREATE TABLE unsavedfiles
306 UnsavedId integer not null,
307 jobid integer not null,
308 pathid integer not null,
309 filenameid integer not null,
310 primary key (UnsavedId)
313 CREATE TABLE CDImages
315 MediaId integer not null,
316 LastBurn timestamp without time zone not null,
317 primary key (MediaId)
323 versionid integer not null
326 CREATE TABLE Status (
327 JobStatus CHAR(1) NOT NULL,
329 PRIMARY KEY (JobStatus)
332 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
333 ('C', 'Created, not yet running');
334 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
336 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
338 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
339 ('T', 'Completed successfully');
340 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
341 ('E', 'Terminated with errors');
342 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
343 ('e', 'Non-fatal error');
344 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
345 ('f', 'Fatal error');
346 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
347 ('D', 'Verify found differences');
348 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
349 ('A', 'Canceled by user');
350 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
351 ('F', 'Waiting for Client');
352 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
353 ('S', 'Waiting for Storage daemon');
354 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
355 ('m', 'Waiting for new media');
356 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
357 ('M', 'Waiting for media mount');
358 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
359 ('s', 'Waiting for storage resource');
360 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
361 ('j', 'Waiting for job resource');
362 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
363 ('c', 'Waiting for client resource');
364 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
365 ('d', 'Waiting on maximum jobs');
366 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
367 ('t', 'Waiting on start time');
368 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
369 ('p', 'Waiting on higher priority jobs');
370 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
371 ('a', 'SD despooling attributes');
372 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
373 ('i', 'Doing batch insert file records');
375 INSERT INTO Version (VersionId) VALUES (11);
377 -- Make sure we have appropriate permissions
384 echo "Creation of Bacula PostgreSQL tables succeeded."
386 echo "Creation of Bacula PostgreSQL tables failed."