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);
249 CREATE TABLE counters
251 counter text not null,
252 minvalue integer default 0,
253 maxvalue integer default 0,
254 currentvalue integer default 0,
255 wrapcounter text not null,
256 primary key (counter)
261 CREATE TABLE basefiles
263 baseid serial not null,
264 jobid integer not null,
265 fileid integer not null,
271 CREATE TABLE unsavedfiles
273 UnsavedId integer not null,
274 jobid integer not null,
275 pathid integer not null,
276 filenameid integer not null,
277 primary key (UnsavedId)
280 CREATE TABLE CDImages
282 MediaId integer not null,
283 LastBurn timestamp without time zone not null,
284 primary key (MediaId)
290 versionid integer not null
293 CREATE TABLE Status (
294 JobStatus CHAR(1) NOT NULL,
296 PRIMARY KEY (JobStatus)
299 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
300 ('C', 'Created, not yet running');
301 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
303 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
305 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
306 ('T', 'Completed successfully');
307 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
308 ('E', 'Terminated with errors');
309 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
310 ('e', 'Non-fatal error');
311 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
312 ('f', 'Fatal error');
313 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
314 ('D', 'Verify found differences');
315 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
316 ('A', 'Canceled by user');
317 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
318 ('F', 'Waiting for Client');
319 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
320 ('S', 'Waiting for Storage daemon');
321 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
322 ('m', 'Waiting for new media');
323 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
324 ('M', 'Waiting for media mount');
325 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
326 ('s', 'Waiting for storage resource');
327 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
328 ('j', 'Waiting for job resource');
329 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
330 ('c', 'Waiting for client resource');
331 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
332 ('d', 'Waiting on maximum jobs');
333 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
334 ('t', 'Waiting on start time');
335 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
336 ('p', 'Waiting on higher priority jobs');
339 INSERT INTO Version (VersionId) VALUES (10);
341 -- Make sure we have appropriate permissions
348 echo "Creation of Bacula PostgreSQL tables succeeded."
350 echo "Creation of Bacula PostgreSQL tables failed."