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,
94 filesetid serial not null,
95 fileset text not null,
97 createtime timestamp without time zone not null,
98 primary key (filesetid)
101 CREATE INDEX fileset_name_idx on fileset (fileset);
103 CREATE TABLE jobmedia
105 jobmediaid serial not null,
106 jobid integer not null,
107 mediaid integer not null,
108 firstindex integer not null default 0,
109 lastindex integer not null default 0,
110 startfile integer not null default 0,
111 endfile integer not null default 0,
112 startblock bigint not null default 0,
113 endblock bigint not null default 0,
114 volindex integer not null default 0,
115 copy integer not null default 0,
116 stripe integer not null 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 not null default 0,
127 poolid integer not null,
128 mediatype text not null,
129 mediatypeid integer not null,
130 labeltype integer not null default 0,
131 firstwritten timestamp without time zone,
132 lastwritten timestamp without time zone,
133 labeldate timestamp without time zone,
134 voljobs integer not null default 0,
135 volfiles integer not null default 0,
136 volblocks integer not null default 0,
137 volmounts integer not null default 0,
138 volbytes bigint not null default 0,
139 volparts integer not null default 0,
140 volerrors integer not null default 0,
141 volwrites integer not null default 0,
142 volcapacitybytes bigint not null 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 recycle smallint not null default 0,
148 volretention bigint not null default 0,
149 voluseduration bigint not null default 0,
150 maxvoljobs integer not null default 0,
151 maxvolfiles integer not null default 0,
152 maxvolbytes bigint not null default 0,
153 inchanger smallint not null default 0,
154 StorageId integer default 0,
155 DeviceId integer default 0,
156 mediaaddressing smallint not null default 0,
157 volreadtime bigint not null default 0,
158 volwritetime bigint not null default 0,
159 endfile integer not null default 0,
160 endblock bigint not null default 0,
161 LocationId integer default 0,
162 primary key (mediaid)
165 create unique index media_volumename_id on media (volumename);
168 CREATE TABLE MediaType (
170 MediaType TEXT NOT NULL,
171 ReadOnly INTEGER DEFAULT 0,
172 PRIMARY KEY(MediaTypeId)
175 CREATE TABLE Storage (
178 AutoChanger INTEGER DEFAULT 0,
179 PRIMARY KEY(StorageId)
182 CREATE TABLE Device (
185 MediaTypeId INTEGER NOT NULL,
186 StorageId INTEGER NOT NULL,
187 DevMounts INTEGER NOT NULL DEFAULT 0,
188 DevReadBytes BIGINT NOT NULL DEFAULT 0,
189 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
190 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
191 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
192 DevReadTime BIGINT NOT NULL DEFAULT 0,
193 DevWriteTime BIGINT NOT NULL DEFAULT 0,
194 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
195 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
196 CleaningDate TIMESTAMP WITHOUT TIME ZONE,
197 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
198 PRIMARY KEY(DeviceId)
204 poolid serial not null,
206 numvols integer not null default 0,
207 maxvols integer not null default 0,
208 useonce smallint not null default 0,
209 usecatalog smallint not null default 0,
210 acceptanyvolume smallint not null default 0,
211 volretention bigint not null default 0,
212 voluseduration bigint not null default 0,
213 maxvoljobs integer not null default 0,
214 maxvolfiles integer not null default 0,
215 maxvolbytes bigint not null default 0,
216 autoprune smallint not null default 0,
217 recycle smallint not null default 0,
219 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
220 labeltype integer not null default 0,
221 labelformat text not null,
222 enabled smallint not null default 1,
223 scratchpoolid integer default 0,
224 recyclepoolid integer default 0,
225 NextPoolId integer default 0,
226 MigrationHighBytes BIGINT DEFAULT 0,
227 MigrationLowBytes BIGINT DEFAULT 0,
228 MigrationTime BIGINT DEFAULT 0,
232 CREATE INDEX pool_name_idx on pool (name);
236 clientid serial not null,
239 autoprune smallint default 0,
240 fileretention bigint not null,
241 jobretention bigint not null,
242 primary key (clientid)
245 create unique index client_name_idx on client (name);
248 CREATE TABLE counters
250 counter text not null,
253 currentvalue integer,
254 wrapcounter text not null,
255 primary key (counter)
260 CREATE TABLE basefiles
262 baseid serial not null,
263 jobid integer not null,
264 fileid integer not null,
270 CREATE TABLE unsavedfiles
272 UnsavedId integer not null,
273 jobid integer not null,
274 pathid integer not null,
275 filenameid integer not null,
276 primary key (UnsavedId)
279 CREATE TABLE CDImages
281 MediaId integer not null,
282 LastBurn timestamp without time zone not null,
283 primary key (MediaId)
289 versionid integer not null
292 CREATE TABLE Status (
293 JobStatus CHAR(1) NOT NULL,
295 PRIMARY KEY (JobStatus)
298 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
299 ('C', 'Created, not yet running');
300 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
302 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
304 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
305 ('T', 'Completed successfully');
306 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
307 ('E', 'Terminated with errors');
308 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
309 ('e', 'Non-fatal error');
310 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
311 ('f', 'Fatal error');
312 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
313 ('D', 'Verify found differences');
314 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
315 ('A', 'Canceled by user');
316 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
317 ('F', 'Waiting for Client');
318 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
319 ('S', 'Waiting for Storage daemon');
320 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
321 ('m', 'Waiting for new media');
322 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
323 ('M', 'Waiting for media mount');
324 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
325 ('s', 'Waiting for storage resource');
326 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
327 ('j', 'Waiting for job resource');
328 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
329 ('c', 'Waiting for client resource');
330 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
331 ('d', 'Waiting on maximum jobs');
332 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
333 ('t', 'Waiting on start time');
334 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
335 ('p', 'Waiting on higher priority jobs');
338 INSERT INTO Version (VersionId) VALUES (9);
340 -- Make sure we have appropriate permissions
345 echo "Creation of Bacula PostgreSQL tables succeeded."
347 echo "Creation of Bacula PostgreSQL tables failed."