3 # shell script to create Bacula PostgreSQL tables
7 $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,
58 clientid integer default 0,
59 jobstatus char(1) not null,
60 schedtime timestamp without time zone,
61 starttime timestamp without time zone,
62 endtime timestamp without time zone,
63 realendtime timestamp without time zone,
64 jobtdate bigint default 0,
65 volsessionid integer default 0,
66 volsessiontime integer default 0,
67 jobfiles integer default 0,
68 jobbytes bigint default 0,
69 joberrors integer default 0,
70 jobmissingfiles integer default 0,
71 poolid integer default 0,
72 filesetid integer default 0,
73 purgedfiles smallint default 0,
74 hasbase smallint default 0,
75 priorjobid integer default 0,
79 CREATE INDEX job_name_idx on job (name);
81 CREATE TABLE Location (
82 LocationId serial not null,
83 Location text not null,
84 Cost integer default 0,
86 primary key (LocationId)
92 filesetid serial not null,
93 fileset text not null,
95 createtime timestamp without time zone not null,
96 primary key (filesetid)
99 CREATE INDEX fileset_name_idx on fileset (fileset);
101 CREATE TABLE jobmedia
103 jobmediaid serial not null,
104 jobid integer not null,
105 mediaid integer not null,
106 firstindex integer default 0,
107 lastindex integer default 0,
108 startfile integer default 0,
109 endfile integer default 0,
110 startblock bigint default 0,
111 endblock bigint default 0,
112 volindex integer default 0,
113 copy integer default 0,
114 primary key (jobmediaid)
117 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
121 mediaid serial not null,
122 volumename text not null,
123 slot integer default 0,
124 poolid integer default 0,
125 mediatype text not null,
126 mediatypeid integer default 0,
127 labeltype integer default 0,
128 firstwritten timestamp without time zone,
129 lastwritten timestamp without time zone,
130 labeldate timestamp without time zone,
131 voljobs integer default 0,
132 volfiles integer default 0,
133 volblocks integer default 0,
134 volmounts integer default 0,
135 volbytes bigint default 0,
136 volparts integer default 0,
137 volerrors integer default 0,
138 volwrites integer default 0,
139 volcapacitybytes bigint default 0,
140 volstatus text not null
141 check (volstatus in ('Full','Archive','Append',
142 'Recycle','Purged','Read-Only','Disabled',
143 'Error','Busy','Used','Cleaning','Scratch')),
144 enabled smallint default 1,
145 recycle smallint default 0,
146 volretention bigint default 0,
147 voluseduration bigint default 0,
148 maxvoljobs integer default 0,
149 maxvolfiles integer default 0,
150 maxvolbytes bigint default 0,
151 inchanger smallint default 0,
152 StorageId integer default 0,
153 DeviceId integer default 0,
154 mediaaddressing smallint default 0,
155 volreadtime bigint default 0,
156 volwritetime bigint default 0,
157 endfile integer default 0,
158 endblock bigint default 0,
159 LocationId integer default 0,
160 recyclecount integer default 0,
161 initialwrite timestamp without time zone,
162 scratchpoolid integer default 0,
163 recyclepoolid integer default 0,
164 primary key (mediaid)
167 create unique index media_volumename_id on media (volumename);
170 CREATE TABLE MediaType (
172 MediaType TEXT NOT NULL,
173 ReadOnly INTEGER DEFAULT 0,
174 PRIMARY KEY(MediaTypeId)
177 CREATE TABLE Storage (
180 AutoChanger INTEGER DEFAULT 0,
181 PRIMARY KEY(StorageId)
184 CREATE TABLE Device (
187 MediaTypeId INTEGER NOT NULL,
188 StorageId INTEGER NOT NULL,
189 DevMounts INTEGER NOT NULL DEFAULT 0,
190 DevReadBytes BIGINT NOT NULL DEFAULT 0,
191 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
192 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
193 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
194 DevReadTime BIGINT NOT NULL DEFAULT 0,
195 DevWriteTime BIGINT NOT NULL DEFAULT 0,
196 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
197 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
198 CleaningDate timestamp without time zone,
199 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
200 PRIMARY KEY(DeviceId)
206 poolid serial not null,
208 numvols integer default 0,
209 maxvols integer default 0,
210 useonce smallint default 0,
211 usecatalog smallint default 0,
212 acceptanyvolume smallint default 0,
213 volretention bigint default 0,
214 voluseduration bigint default 0,
215 maxvoljobs integer default 0,
216 maxvolfiles integer default 0,
217 maxvolbytes bigint default 0,
218 autoprune smallint default 0,
219 recycle smallint default 0,
221 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
222 labeltype integer default 0,
223 labelformat text not null,
224 enabled smallint default 1,
225 scratchpoolid integer default 0,
226 recyclepoolid integer default 0,
227 NextPoolId integer default 0,
228 MigrationHighBytes BIGINT DEFAULT 0,
229 MigrationLowBytes BIGINT DEFAULT 0,
230 MigrationTime BIGINT DEFAULT 0,
234 CREATE INDEX pool_name_idx on pool (name);
238 clientid serial not null,
241 autoprune smallint default 0,
242 fileretention bigint default 0,
243 jobretention bigint default 0,
244 primary key (clientid)
247 create unique index client_name_idx on client (name);
251 JobId serial not null,
252 LogText text not null,
255 create index log_name_idx on Log (JobId);
259 CREATE TABLE counters
261 counter text not null,
262 minvalue integer default 0,
263 maxvalue integer default 0,
264 currentvalue integer default 0,
265 wrapcounter text not null,
266 primary key (counter)
271 CREATE TABLE basefiles
273 baseid serial not null,
274 jobid integer not null,
275 fileid integer not null,
281 CREATE TABLE unsavedfiles
283 UnsavedId integer not null,
284 jobid integer not null,
285 pathid integer not null,
286 filenameid integer not null,
287 primary key (UnsavedId)
290 CREATE TABLE CDImages
292 MediaId integer not null,
293 LastBurn timestamp without time zone not null,
294 primary key (MediaId)
300 versionid integer not null
303 CREATE TABLE Status (
304 JobStatus CHAR(1) NOT NULL,
306 PRIMARY KEY (JobStatus)
309 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
310 ('C', 'Created, not yet running');
311 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
313 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
315 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
316 ('T', 'Completed successfully');
317 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
318 ('E', 'Terminated with errors');
319 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
320 ('e', 'Non-fatal error');
321 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
322 ('f', 'Fatal error');
323 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
324 ('D', 'Verify found differences');
325 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
326 ('A', 'Canceled by user');
327 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
328 ('F', 'Waiting for Client');
329 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
330 ('S', 'Waiting for Storage daemon');
331 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
332 ('m', 'Waiting for new media');
333 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
334 ('M', 'Waiting for media mount');
335 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
336 ('s', 'Waiting for storage resource');
337 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
338 ('j', 'Waiting for job resource');
339 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
340 ('c', 'Waiting for client resource');
341 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
342 ('d', 'Waiting on maximum jobs');
343 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
344 ('t', 'Waiting on start time');
345 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
346 ('p', 'Waiting on higher priority jobs');
349 INSERT INTO Version (VersionId) VALUES (10);
351 -- Make sure we have appropriate permissions
358 echo "Creation of Bacula PostgreSQL tables succeeded."
360 echo "Creation of Bacula PostgreSQL tables failed."