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 TABLE Location (
85 LocationId serial not null,
86 Location text not null,
87 Cost integer default 0,
89 primary key (LocationId)
95 filesetid serial not null,
96 fileset text not null,
98 createtime timestamp without time zone not null,
99 primary key (filesetid)
102 CREATE INDEX fileset_name_idx on fileset (fileset);
104 CREATE TABLE jobmedia
106 jobmediaid serial not null,
107 jobid integer not null,
108 mediaid integer not null,
109 firstindex integer default 0,
110 lastindex integer default 0,
111 startfile integer default 0,
112 endfile integer default 0,
113 startblock bigint default 0,
114 endblock bigint default 0,
115 volindex integer default 0,
116 copy integer default 0,
117 primary key (jobmediaid)
120 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
124 mediaid serial not null,
125 volumename text not null,
126 slot integer default 0,
127 poolid integer default 0,
128 mediatype text not null,
129 mediatypeid integer default 0,
130 labeltype integer default 0,
131 firstwritten timestamp without time zone,
132 lastwritten timestamp without time zone,
133 labeldate timestamp without time zone,
134 voljobs integer default 0,
135 volfiles integer default 0,
136 volblocks integer default 0,
137 volmounts integer default 0,
138 volbytes bigint default 0,
139 volparts integer default 0,
140 volerrors integer default 0,
141 volwrites integer default 0,
142 volcapacitybytes bigint default 0,
143 volstatus text not null
144 check (volstatus in ('Full','Archive','Append',
145 'Recycle','Purged','Read-Only','Disabled',
146 'Error','Busy','Used','Cleaning','Scratch')),
147 enabled smallint default 1,
148 recycle smallint default 0,
149 volretention bigint default 0,
150 voluseduration bigint default 0,
151 maxvoljobs integer default 0,
152 maxvolfiles integer default 0,
153 maxvolbytes bigint default 0,
154 inchanger smallint default 0,
155 StorageId integer default 0,
156 DeviceId integer default 0,
157 mediaaddressing smallint default 0,
158 volreadtime bigint default 0,
159 volwritetime bigint default 0,
160 endfile integer default 0,
161 endblock bigint default 0,
162 LocationId integer default 0,
163 recyclecount integer default 0,
164 initialwrite timestamp without time zone,
165 scratchpoolid integer default 0,
166 recyclepoolid integer default 0,
168 primary key (mediaid)
171 create unique index media_volumename_id on media (volumename);
174 CREATE TABLE MediaType (
176 MediaType TEXT NOT NULL,
177 ReadOnly INTEGER DEFAULT 0,
178 PRIMARY KEY(MediaTypeId)
181 CREATE TABLE Storage (
184 AutoChanger INTEGER DEFAULT 0,
185 PRIMARY KEY(StorageId)
188 CREATE TABLE Device (
191 MediaTypeId INTEGER NOT NULL,
192 StorageId INTEGER NOT NULL,
193 DevMounts INTEGER NOT NULL DEFAULT 0,
194 DevReadBytes BIGINT NOT NULL DEFAULT 0,
195 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
196 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
197 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
198 DevReadTime BIGINT NOT NULL DEFAULT 0,
199 DevWriteTime BIGINT NOT NULL DEFAULT 0,
200 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
201 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
202 CleaningDate timestamp without time zone,
203 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
204 PRIMARY KEY(DeviceId)
210 poolid serial not null,
212 numvols integer default 0,
213 maxvols integer default 0,
214 useonce smallint default 0,
215 usecatalog smallint default 0,
216 acceptanyvolume smallint default 0,
217 volretention bigint default 0,
218 voluseduration bigint default 0,
219 maxvoljobs integer default 0,
220 maxvolfiles integer default 0,
221 maxvolbytes bigint default 0,
222 autoprune smallint default 0,
223 recycle smallint default 0,
225 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
226 labeltype integer default 0,
227 labelformat text not null,
228 enabled smallint default 1,
229 scratchpoolid integer default 0,
230 recyclepoolid integer default 0,
231 NextPoolId integer default 0,
232 MigrationHighBytes BIGINT DEFAULT 0,
233 MigrationLowBytes BIGINT DEFAULT 0,
234 MigrationTime BIGINT DEFAULT 0,
238 CREATE INDEX pool_name_idx on pool (name);
242 clientid serial not null,
245 autoprune smallint default 0,
246 fileretention bigint default 0,
247 jobretention bigint default 0,
248 primary key (clientid)
251 create unique index client_name_idx on client (name);
255 LogId serial not null,
256 JobId integer not null,
257 Time timestamp without time zone,
258 LogText text not null,
261 create index log_name_idx on Log (JobId);
263 CREATE TABLE LocationLog (
264 LocLogId SERIAL NOT NULL,
265 Date timestamp without time zone,
266 Comment TEXT NOT NULL,
267 MediaId INTEGER DEFAULT 0,
268 LocationId INTEGER DEFAULT 0,
269 newvolstatus text not null
270 check (newvolstatus in ('Full','Archive','Append',
271 'Recycle','Purged','Read-Only','Disabled',
272 'Error','Busy','Used','Cleaning','Scratch')),
274 PRIMARY KEY(LocLogId)
279 CREATE TABLE counters
281 counter text not null,
282 minvalue integer default 0,
283 maxvalue integer default 0,
284 currentvalue integer default 0,
285 wrapcounter text not null,
286 primary key (counter)
291 CREATE TABLE basefiles
293 baseid serial not null,
294 jobid integer not null,
295 fileid integer not null,
301 CREATE TABLE unsavedfiles
303 UnsavedId integer not null,
304 jobid integer not null,
305 pathid integer not null,
306 filenameid integer not null,
307 primary key (UnsavedId)
310 CREATE TABLE CDImages
312 MediaId integer not null,
313 LastBurn timestamp without time zone not null,
314 primary key (MediaId)
320 versionid integer not null
323 CREATE TABLE Status (
324 JobStatus CHAR(1) NOT NULL,
326 PRIMARY KEY (JobStatus)
329 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
330 ('C', 'Created, not yet running');
331 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
333 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
335 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
336 ('T', 'Completed successfully');
337 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
338 ('E', 'Terminated with errors');
339 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
340 ('e', 'Non-fatal error');
341 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
342 ('f', 'Fatal error');
343 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
344 ('D', 'Verify found differences');
345 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
346 ('A', 'Canceled by user');
347 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
348 ('F', 'Waiting for Client');
349 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
350 ('S', 'Waiting for Storage daemon');
351 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
352 ('m', 'Waiting for new media');
353 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
354 ('M', 'Waiting for media mount');
355 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
356 ('s', 'Waiting for storage resource');
357 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
358 ('j', 'Waiting for job resource');
359 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
360 ('c', 'Waiting for client resource');
361 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
362 ('d', 'Waiting on maximum jobs');
363 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
364 ('t', 'Waiting on start time');
365 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
366 ('p', 'Waiting on higher priority jobs');
367 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
368 ('a', 'SD despooling attributes');
369 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
370 ('i', 'Doing batch insert file records');
372 INSERT INTO Version (VersionId) VALUES (10);
374 -- Make sure we have appropriate permissions
381 echo "Creation of Bacula PostgreSQL tables succeeded."
383 echo "Creation of Bacula PostgreSQL tables failed."