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,
85 primary key (LocationId)
91 filesetid serial not null,
92 fileset text not null,
94 createtime timestamp without time zone not null,
95 primary key (filesetid)
98 CREATE INDEX fileset_name_idx on fileset (fileset);
100 CREATE TABLE jobmedia
102 jobmediaid serial not null,
103 jobid integer not null,
104 mediaid integer not null,
105 firstindex integer default 0,
106 lastindex integer default 0,
107 startfile integer default 0,
108 endfile integer default 0,
109 startblock bigint default 0,
110 endblock bigint default 0,
111 volindex integer default 0,
112 copy integer default 0,
113 primary key (jobmediaid)
116 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
120 mediaid serial not null,
121 volumename text not null,
122 slot integer default 0,
123 poolid integer default 0,
124 mediatype text not null,
125 mediatypeid integer default 0,
126 labeltype integer default 0,
127 firstwritten timestamp without time zone,
128 lastwritten timestamp without time zone,
129 labeldate timestamp without time zone,
130 voljobs integer default 0,
131 volfiles integer default 0,
132 volblocks integer default 0,
133 volmounts integer default 0,
134 volbytes bigint default 0,
135 volparts integer default 0,
136 volerrors integer default 0,
137 volwrites integer default 0,
138 volcapacitybytes bigint default 0,
139 volstatus text not null
140 check (volstatus in ('Full','Archive','Append',
141 'Recycle','Purged','Read-Only','Disabled',
142 'Error','Busy','Used','Cleaning','Scratch')),
143 enabled smallint default 1,
144 recycle smallint default 0,
145 volretention bigint default 0,
146 voluseduration bigint default 0,
147 maxvoljobs integer default 0,
148 maxvolfiles integer default 0,
149 maxvolbytes bigint default 0,
150 inchanger smallint default 0,
151 StorageId integer default 0,
152 DeviceId integer default 0,
153 mediaaddressing smallint default 0,
154 volreadtime bigint default 0,
155 volwritetime bigint default 0,
156 endfile integer default 0,
157 endblock bigint default 0,
158 LocationId integer default 0,
159 recyclecount integer default 0,
160 initialwrite timestamp without time zone,
161 scratchpoolid integer default 0,
162 recyclepoolid integer default 0,
163 primary key (mediaid)
166 create unique index media_volumename_id on media (volumename);
169 CREATE TABLE MediaType (
171 MediaType TEXT NOT NULL,
172 ReadOnly INTEGER DEFAULT 0,
173 PRIMARY KEY(MediaTypeId)
176 CREATE TABLE Storage (
179 AutoChanger INTEGER DEFAULT 0,
180 PRIMARY KEY(StorageId)
183 CREATE TABLE Device (
186 MediaTypeId INTEGER NOT NULL,
187 StorageId INTEGER NOT NULL,
188 DevMounts INTEGER NOT NULL DEFAULT 0,
189 DevReadBytes BIGINT NOT NULL DEFAULT 0,
190 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
191 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
192 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
193 DevReadTime BIGINT NOT NULL DEFAULT 0,
194 DevWriteTime BIGINT NOT NULL DEFAULT 0,
195 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
196 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
197 CleaningDate timestamp without time zone,
198 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
199 PRIMARY KEY(DeviceId)
205 poolid serial not null,
207 numvols integer default 0,
208 maxvols integer default 0,
209 useonce smallint default 0,
210 usecatalog smallint default 0,
211 acceptanyvolume smallint default 0,
212 volretention bigint default 0,
213 voluseduration bigint default 0,
214 maxvoljobs integer default 0,
215 maxvolfiles integer default 0,
216 maxvolbytes bigint default 0,
217 autoprune smallint default 0,
218 recycle smallint default 0,
220 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
221 labeltype integer default 0,
222 labelformat text not null,
223 enabled smallint default 1,
224 scratchpoolid integer default 0,
225 recyclepoolid integer default 0,
226 NextPoolId integer default 0,
227 MigrationHighBytes BIGINT DEFAULT 0,
228 MigrationLowBytes BIGINT DEFAULT 0,
229 MigrationTime BIGINT DEFAULT 0,
233 CREATE INDEX pool_name_idx on pool (name);
237 clientid serial not null,
240 autoprune smallint default 0,
241 fileretention bigint default 0,
242 jobretention bigint default 0,
243 primary key (clientid)
246 create unique index client_name_idx on client (name);
250 JobId serial not null,
251 LogText text not null,
254 create index log_name_idx on Log (JobId);
258 CREATE TABLE counters
260 counter text not null,
261 minvalue integer default 0,
262 maxvalue integer default 0,
263 currentvalue integer default 0,
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 (10);
350 -- Make sure we have appropriate permissions
357 echo "Creation of Bacula PostgreSQL tables succeeded."
359 echo "Creation of Bacula PostgreSQL tables failed."