3 # shell script to create Bacula PostgreSQL tables
7 if $bindir/psql -f - -d bacula $* <<END-OF-DATA
11 filenameid serial not null,
13 primary key (filenameid)
16 CREATE INDEX filename_name_idx on filename (name);
20 pathid serial not null,
25 CREATE INDEX path_name_idx on path (path);
29 fileid serial not null,
30 fileindex integer not null default 0,
31 jobid integer not null,
32 pathid integer not null,
33 filenameid integer not null,
34 markid integer not null default 0,
40 CREATE INDEX file_jobid_idx on file (jobid);
41 CREATE INDEX file_fp_idx on file (filenameid, pathid);
44 -- Possibly add one or more of the following indexes
45 -- if your Verifies are too slow.
47 -- CREATE INDEX file_pathid_idx on file(pathid);
48 -- CREATE INDEX file_filenameid_idx on file(filenameid);
49 -- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
53 jobid serial not null,
56 type char(1) not null,
57 level char(1) not null,
59 jobstatus char(1) not null,
60 schedtime timestamp without time zone not null,
61 starttime timestamp without time zone,
62 endtime timestamp without time zone,
63 jobtdate bigint not null,
64 volsessionid integer not null default 0,
65 volsessiontime integer not null default 0,
66 jobfiles integer not null default 0,
67 jobbytes bigint not null default 0,
68 joberrors integer not null default 0,
69 jobmissingfiles integer not null default 0,
72 purgedfiles smallint not null default 0,
73 hasbase smallint not null default 0,
77 CREATE INDEX job_name_idx on job (name);
80 JobId serial not null,
81 OriginalJobId serial not null,
82 JobType char(1) not null,
83 JobLevel char(1) not null,
84 schedtime timestamp without time zone not null,
85 starttime timestamp without time zone,
86 endtime timestamp without time zone,
87 jobtdate bigint not null,
91 CREATE TABLE Location (
92 LocationId serial not null,
93 Location text not null,
94 primary key (LocationId)
100 filesetid serial not null,
101 fileset text not null,
103 createtime timestamp without time zone not null,
104 primary key (filesetid)
107 CREATE INDEX fileset_name_idx on fileset (fileset);
109 CREATE TABLE jobmedia
111 jobmediaid serial not null,
112 jobid integer not null,
113 mediaid integer not null,
114 firstindex integer not null default 0,
115 lastindex integer not null default 0,
116 startfile integer not null default 0,
117 endfile integer not null default 0,
118 startblock bigint not null default 0,
119 endblock bigint not null default 0,
120 volindex integer not null default 0,
121 copy integer not null default 0,
122 stripe integer not null default 0,
123 primary key (jobmediaid)
126 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
130 mediaid serial not null,
131 volumename text not null,
132 slot integer not null default 0,
133 poolid integer not null,
134 mediatype text not null,
135 mediatypeid integer not null,
136 labeltype integer not null default 0,
137 firstwritten timestamp without time zone,
138 lastwritten timestamp without time zone,
139 labeldate timestamp without time zone,
140 voljobs integer not null default 0,
141 volfiles integer not null default 0,
142 volblocks integer not null default 0,
143 volmounts integer not null default 0,
144 volbytes bigint not null default 0,
145 volparts integer not null default 0,
146 volerrors integer not null default 0,
147 volwrites integer not null default 0,
148 volcapacitybytes bigint not null default 0,
149 volstatus text not null
150 check (volstatus in ('Full','Archive','Append',
151 'Recycle','Purged','Read-Only','Disabled',
152 'Error','Busy','Used','Cleaning','Scratch')),
153 recycle smallint not null default 0,
154 volretention bigint not null default 0,
155 voluseduration bigint not null default 0,
156 maxvoljobs integer not null default 0,
157 maxvolfiles integer not null default 0,
158 maxvolbytes bigint not null default 0,
159 inchanger smallint not null default 0,
160 StorageId integer default 0,
161 DeviceId integer default 0,
162 mediaaddressing smallint not null default 0,
163 volreadtime bigint not null default 0,
164 volwritetime bigint not null default 0,
165 endfile integer not null default 0,
166 endblock bigint not null default 0,
167 LocationId integer default 0,
168 recyclcount integer not null default 0,
169 initialwrite timestamp without time zone,
170 scratchpoolid integer default 0,
171 recyclepoolid integer default 0,
172 primary key (mediaid)
175 create unique index media_volumename_id on media (volumename);
178 CREATE TABLE MediaType (
180 MediaType TEXT NOT NULL,
181 ReadOnly INTEGER DEFAULT 0,
182 PRIMARY KEY(MediaTypeId)
185 CREATE TABLE Storage (
188 AutoChanger INTEGER DEFAULT 0,
189 PRIMARY KEY(StorageId)
192 CREATE TABLE Device (
195 MediaTypeId INTEGER NOT NULL,
196 StorageId INTEGER NOT NULL,
197 DevMounts INTEGER NOT NULL DEFAULT 0,
198 DevReadBytes BIGINT NOT NULL DEFAULT 0,
199 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
200 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
201 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
202 DevReadTime BIGINT NOT NULL DEFAULT 0,
203 DevWriteTime BIGINT NOT NULL DEFAULT 0,
204 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
205 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
206 CleaningDate TIMESTAMP WITHOUT TIME ZONE,
207 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
208 PRIMARY KEY(DeviceId)
214 poolid serial not null,
216 numvols integer not null default 0,
217 maxvols integer not null default 0,
218 useonce smallint not null default 0,
219 usecatalog smallint not null default 0,
220 acceptanyvolume smallint not null default 0,
221 volretention bigint not null default 0,
222 voluseduration bigint not null default 0,
223 maxvoljobs integer not null default 0,
224 maxvolfiles integer not null default 0,
225 maxvolbytes bigint not null default 0,
226 autoprune smallint not null default 0,
227 recycle smallint not null default 0,
229 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
230 labeltype integer not null default 0,
231 labelformat text not null,
232 enabled smallint not null default 1,
233 scratchpoolid integer default 0,
234 recyclepoolid integer default 0,
235 NextPoolId integer default 0,
236 MigrationHighBytes BIGINT DEFAULT 0,
237 MigrationLowBytes BIGINT DEFAULT 0,
238 MigrationTime BIGINT DEFAULT 0,
242 CREATE INDEX pool_name_idx on pool (name);
246 clientid serial not null,
249 autoprune smallint default 0,
250 fileretention bigint not null,
251 jobretention bigint not null,
252 primary key (clientid)
255 create unique index client_name_idx on client (name);
258 CREATE TABLE counters
260 counter text not null,
263 currentvalue integer,
264 wrapcounter text not null,
265 primary key (counter)
270 CREATE TABLE basefiles
272 baseid serial not null,
273 jobid integer not null,
274 fileid integer not null,
280 CREATE TABLE unsavedfiles
282 UnsavedId integer not null,
283 jobid integer not null,
284 pathid integer not null,
285 filenameid integer not null,
286 primary key (UnsavedId)
289 CREATE TABLE CDImages
291 MediaId integer not null,
292 LastBurn timestamp without time zone not null,
293 primary key (MediaId)
299 versionid integer not null
302 CREATE TABLE Status (
303 JobStatus CHAR(1) NOT NULL,
305 PRIMARY KEY (JobStatus)
308 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
309 ('C', 'Created, not yet running');
310 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
312 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
314 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
315 ('T', 'Completed successfully');
316 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
317 ('E', 'Terminated with errors');
318 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
319 ('e', 'Non-fatal error');
320 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
321 ('f', 'Fatal error');
322 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
323 ('D', 'Verify found differences');
324 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
325 ('A', 'Canceled by user');
326 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
327 ('F', 'Waiting for Client');
328 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
329 ('S', 'Waiting for Storage daemon');
330 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
331 ('m', 'Waiting for new media');
332 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
333 ('M', 'Waiting for media mount');
334 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
335 ('s', 'Waiting for storage resource');
336 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
337 ('j', 'Waiting for job resource');
338 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
339 ('c', 'Waiting for client resource');
340 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
341 ('d', 'Waiting on maximum jobs');
342 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
343 ('t', 'Waiting on start time');
344 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
345 ('p', 'Waiting on higher priority jobs');
348 INSERT INTO Version (VersionId) VALUES (9);
350 -- Make sure we have appropriate permissions
355 echo "Creation of Bacula PostgreSQL tables succeeded."
357 echo "Creation of Bacula PostgreSQL tables failed."