3 # shell script to create Bacula PostgreSQL tables
6 db_name=${db_name:-@db_name@}
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 -- Add this if you have a good number of job
48 -- that run at the same time
49 -- ALTER SEQUENCE file_fileid_seq CACHE 1000;
52 -- Possibly add one or more of the following indexes
53 -- if your Verifies are too slow.
55 -- CREATE INDEX file_pathid_idx on file(pathid);
56 -- CREATE INDEX file_filenameid_idx on file(filenameid);
57 -- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
61 jobid serial not null,
64 type char(1) not null,
65 level char(1) not null,
66 clientid integer default 0,
67 jobstatus char(1) not null,
68 schedtime timestamp without time zone,
69 starttime timestamp without time zone,
70 endtime timestamp without time zone,
71 realendtime timestamp without time zone,
72 jobtdate bigint default 0,
73 volsessionid integer default 0,
74 volsessiontime integer default 0,
75 jobfiles integer default 0,
76 jobbytes bigint default 0,
77 joberrors integer default 0,
78 jobmissingfiles integer default 0,
79 poolid integer default 0,
80 filesetid integer default 0,
81 purgedfiles smallint default 0,
82 hasbase smallint default 0,
83 priorjobid integer default 0,
87 CREATE INDEX job_name_idx on job (name);
89 -- Create a table like Job for long term statistics
90 CREATE TABLE JobHistory (LIKE Job);
92 CREATE TABLE Location (
93 LocationId serial not null,
94 Location text not null,
95 Cost integer default 0,
97 primary key (LocationId)
103 filesetid serial not null,
104 fileset text not null,
106 createtime timestamp without time zone not null,
107 primary key (filesetid)
110 CREATE INDEX fileset_name_idx on fileset (fileset);
112 CREATE TABLE jobmedia
114 jobmediaid serial not null,
115 jobid integer not null,
116 mediaid integer not null,
117 firstindex integer default 0,
118 lastindex integer default 0,
119 startfile integer default 0,
120 endfile integer default 0,
121 startblock bigint default 0,
122 endblock bigint default 0,
123 volindex integer default 0,
124 copy integer default 0,
125 primary key (jobmediaid)
128 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
132 mediaid serial not null,
133 volumename text not null,
134 slot integer default 0,
135 poolid integer default 0,
136 mediatype text not null,
137 mediatypeid integer default 0,
138 labeltype integer default 0,
139 firstwritten timestamp without time zone,
140 lastwritten timestamp without time zone,
141 labeldate timestamp without time zone,
142 voljobs integer default 0,
143 volfiles integer default 0,
144 volblocks integer default 0,
145 volmounts integer default 0,
146 volbytes bigint default 0,
147 volparts integer default 0,
148 volerrors integer default 0,
149 volwrites integer default 0,
150 volcapacitybytes bigint default 0,
151 volstatus text not null
152 check (volstatus in ('Full','Archive','Append',
153 'Recycle','Purged','Read-Only','Disabled',
154 'Error','Busy','Used','Cleaning','Scratch')),
155 enabled smallint default 1,
156 recycle smallint default 0,
157 volretention bigint default 0,
158 voluseduration bigint default 0,
159 maxvoljobs integer default 0,
160 maxvolfiles integer default 0,
161 maxvolbytes bigint default 0,
162 inchanger smallint default 0,
163 StorageId integer default 0,
164 DeviceId integer default 0,
165 mediaaddressing smallint default 0,
166 volreadtime bigint default 0,
167 volwritetime bigint default 0,
168 endfile integer default 0,
169 endblock bigint default 0,
170 LocationId integer default 0,
171 recyclecount integer default 0,
172 initialwrite timestamp without time zone,
173 scratchpoolid integer default 0,
174 recyclepoolid integer default 0,
176 primary key (mediaid)
179 create unique index media_volumename_id on media (volumename);
182 CREATE TABLE MediaType (
184 MediaType TEXT NOT NULL,
185 ReadOnly INTEGER DEFAULT 0,
186 PRIMARY KEY(MediaTypeId)
189 CREATE TABLE Storage (
192 AutoChanger INTEGER DEFAULT 0,
193 PRIMARY KEY(StorageId)
196 CREATE TABLE Device (
199 MediaTypeId INTEGER NOT NULL,
200 StorageId INTEGER NOT NULL,
201 DevMounts INTEGER NOT NULL DEFAULT 0,
202 DevReadBytes BIGINT NOT NULL DEFAULT 0,
203 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
204 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
205 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
206 DevReadTime BIGINT NOT NULL DEFAULT 0,
207 DevWriteTime BIGINT NOT NULL DEFAULT 0,
208 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
209 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
210 CleaningDate timestamp without time zone,
211 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
212 PRIMARY KEY(DeviceId)
218 poolid serial not null,
220 numvols integer default 0,
221 maxvols integer default 0,
222 useonce smallint default 0,
223 usecatalog smallint default 0,
224 acceptanyvolume smallint default 0,
225 volretention bigint default 0,
226 voluseduration bigint default 0,
227 maxvoljobs integer default 0,
228 maxvolfiles integer default 0,
229 maxvolbytes bigint default 0,
230 autoprune smallint default 0,
231 recycle smallint default 0,
233 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
234 labeltype integer default 0,
235 labelformat text not null,
236 enabled smallint default 1,
237 scratchpoolid integer default 0,
238 recyclepoolid integer default 0,
239 NextPoolId integer default 0,
240 MigrationHighBytes BIGINT DEFAULT 0,
241 MigrationLowBytes BIGINT DEFAULT 0,
242 MigrationTime BIGINT DEFAULT 0,
246 CREATE INDEX pool_name_idx on pool (name);
250 clientid serial not null,
253 autoprune smallint default 0,
254 fileretention bigint default 0,
255 jobretention bigint default 0,
256 primary key (clientid)
259 create unique index client_name_idx on client (name);
263 LogId serial not null,
264 JobId integer not null,
265 Time timestamp without time zone,
266 LogText text not null,
269 create index log_name_idx on Log (JobId);
271 CREATE TABLE LocationLog (
272 LocLogId SERIAL NOT NULL,
273 Date timestamp without time zone,
274 Comment TEXT NOT NULL,
275 MediaId INTEGER DEFAULT 0,
276 LocationId INTEGER DEFAULT 0,
277 newvolstatus text not null
278 check (newvolstatus in ('Full','Archive','Append',
279 'Recycle','Purged','Read-Only','Disabled',
280 'Error','Busy','Used','Cleaning','Scratch')),
282 PRIMARY KEY(LocLogId)
287 CREATE TABLE counters
289 counter text not null,
290 minvalue integer default 0,
291 maxvalue integer default 0,
292 currentvalue integer default 0,
293 wrapcounter text not null,
294 primary key (counter)
299 CREATE TABLE basefiles
301 baseid serial not null,
302 jobid integer not null,
303 fileid integer not null,
309 CREATE TABLE unsavedfiles
311 UnsavedId integer not null,
312 jobid integer not null,
313 pathid integer not null,
314 filenameid integer not null,
315 primary key (UnsavedId)
318 CREATE TABLE CDImages
320 MediaId integer not null,
321 LastBurn timestamp without time zone not null,
322 primary key (MediaId)
328 versionid integer not null
331 CREATE TABLE Status (
332 JobStatus CHAR(1) NOT NULL,
334 PRIMARY KEY (JobStatus)
337 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
338 ('C', 'Created, not yet running');
339 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
341 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
343 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
344 ('T', 'Completed successfully');
345 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
346 ('E', 'Terminated with errors');
347 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
348 ('e', 'Non-fatal error');
349 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
350 ('f', 'Fatal error');
351 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
352 ('D', 'Verify found differences');
353 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
354 ('A', 'Canceled by user');
355 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
356 ('F', 'Waiting for Client');
357 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
358 ('S', 'Waiting for Storage daemon');
359 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
360 ('m', 'Waiting for new media');
361 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
362 ('M', 'Waiting for media mount');
363 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
364 ('s', 'Waiting for storage resource');
365 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
366 ('j', 'Waiting for job resource');
367 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
368 ('c', 'Waiting for client resource');
369 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
370 ('d', 'Waiting on maximum jobs');
371 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
372 ('t', 'Waiting on start time');
373 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
374 ('p', 'Waiting on higher priority jobs');
375 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
376 ('a', 'SD despooling attributes');
377 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
378 ('i', 'Doing batch insert file records');
380 INSERT INTO Version (VersionId) VALUES (10);
382 -- Make sure we have appropriate permissions
389 echo "Creation of Bacula PostgreSQL tables succeeded."
391 echo "Creation of Bacula PostgreSQL tables failed."