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);
81 filesetid serial not null,
82 fileset text not null,
84 createtime timestamp without time zone not null,
85 primary key (filesetid)
88 CREATE INDEX fileset_name_idx on fileset (fileset);
92 jobmediaid serial not null,
93 jobid integer not null,
94 mediaid integer not null,
95 firstindex integer not null default 0,
96 lastindex integer not null default 0,
97 startfile integer not null default 0,
98 endfile integer not null default 0,
99 startblock bigint not null default 0,
100 endblock bigint not null default 0,
101 volindex integer not null default 0,
102 copy integer not null default 0,
103 stripe integer not null default 0,
104 primary key (jobmediaid)
107 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
111 mediaid serial not null,
112 volumename text not null,
113 slot integer not null default 0,
114 poolid integer not null,
115 mediatype text not null,
116 labeltype integer not null default 0,
117 firstwritten timestamp without time zone,
118 lastwritten timestamp without time zone,
119 labeldate timestamp without time zone,
120 voljobs integer not null default 0,
121 volfiles integer not null default 0,
122 volblocks integer not null default 0,
123 volmounts integer not null default 0,
124 volbytes bigint not null default 0,
125 volparts integer not null default 0,
126 volerrors integer not null default 0,
127 volwrites integer not null default 0,
128 volcapacitybytes bigint not null default 0,
129 volstatus text not null
130 check (volstatus in ('Full','Archive','Append',
131 'Recycle','Purged','Read-Only','Disabled',
132 'Error','Busy','Used','Cleaning',"Scratch')),
133 recycle smallint not null default 0,
134 volretention bigint not null default 0,
135 voluseduration bigint not null default 0,
136 maxvoljobs integer not null default 0,
137 maxvolfiles integer not null default 0,
138 maxvolbytes bigint not null default 0,
139 inchanger smallint not null default 0,
140 StorageId integer default 0,
141 mediaaddressing smallint not null default 0,
142 volreadtime bigint not null default 0,
143 volwritetime bigint not null default 0,
144 endfile integer not null default 0,
145 endblock bigint not null default 0,
146 primary key (mediaid)
149 create unique index media_volumename_id on media (volumename);
152 CREATE TABLE MediaType (
154 MediaType TEXT NOT NULL,
155 ReadOnly INTEGER DEFAULT 0,
156 PRIMARY KEY(MediaTypeId)
159 CREATE TABLE Storage (
162 AutoChanger INTEGER DEFAULT 0,
163 PRIMARY KEY(StorageId)
166 CREATE TABLE Device (
169 MediaTypeId INTEGER NOT NULL,
170 StorageId INTEGER UNSIGNED,
171 DevMounts INTEGER NOT NULL DEFAULT 0,
172 DevReadBytes BIGINT NOT NULL DEFAULT 0,
173 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
174 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
175 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
176 DevReadTime BIGINT NOT NULL DEFAULT 0,
177 DevWriteTime BIGINT NOT NULL DEFAULT 0,
178 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
179 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
180 CleaningDate TIMESTAMP WITHOUT TIME ZONE,
181 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
182 PRIMARY KEY(DeviceId)
188 poolid serial not null,
190 numvols integer not null default 0,
191 maxvols integer not null default 0,
192 useonce smallint not null default 0,
193 usecatalog smallint not null default 0,
194 acceptanyvolume smallint not null default 0,
195 volretention bigint not null default 0,
196 voluseduration bigint not null default 0,
197 maxvoljobs integer not null default 0,
198 maxvolfiles integer not null default 0,
199 maxvolbytes bigint not null default 0,
200 autoprune smallint not null default 0,
201 recycle smallint not null default 0,
203 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
204 labeltype integer not null default 0,
205 labelformat text not null,
206 enabled smallint not null default 1,
207 scratchpoolid integer default 0,
208 recyclepoolid integer default 0,
209 NextPoolId integer default 0,
210 MigrationHighBytes BIGINT DEFAULT 0,
211 MigrationLowBytes BIGINT DEFAULT 0,
212 MigrationTime BIGINT DEFAULT 0,
216 CREATE INDEX pool_name_idx on pool (name);
220 clientid serial not null,
223 autoprune smallint default 0,
224 fileretention bigint not null,
225 jobretention bigint not null,
226 primary key (clientid)
229 create unique index client_name_idx on client (name);
232 CREATE TABLE counters
234 counter text not null,
237 currentvalue integer,
238 wrapcounter text not null,
239 primary key (counter)
244 CREATE TABLE basefiles
246 baseid serial not null,
247 jobid integer not null,
248 fileid integer not null,
254 CREATE TABLE unsavedfiles
256 UnsavedId integer not null,
257 jobid integer not null,
258 pathid integer not null,
259 filenameid integer not null,
260 primary key (UnsavedId)
263 CREATE TABLE CDImages
265 MediaId integer not null,
266 LastBurn timestamp without time zone not null,
267 primary key (MediaId)
273 versionid integer not null
276 CREATE TABLE Status (
277 JobStatus CHAR(1) NOT NULL,
279 PRIMARY KEY (JobStatus)
282 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
283 ('C', 'Created, not yet running');
284 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
286 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
288 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
289 ('T', 'Completed successfully');
290 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
291 ('E', 'Terminated with errors');
292 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
293 ('e', 'Non-fatal error');
294 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
295 ('f', 'Fatal error');
296 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
297 ('D', 'Verify found differences');
298 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
299 ('A', 'Canceled by user');
300 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
301 ('F', 'Waiting for Client');
302 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
303 ('S', 'Waiting for Storage daemon');
304 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
305 ('m', 'Waiting for new media');
306 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
307 ('M', 'Waiting for media mount');
308 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
309 ('s', 'Waiting for storage resource');
310 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
311 ('j', 'Waiting for job resource');
312 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
313 ('c', 'Waiting for client resource');
314 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
315 ('d', 'Waiting on maximum jobs');
316 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
317 ('t', 'Waiting on start time');
318 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
319 ('p', 'Waiting on higher priority jobs');
322 INSERT INTO Version (VersionId) VALUES (9);
324 -- Make sure we have appropriate permissions
329 echo "Creation of Bacula PostgreSQL tables succeeded."
331 echo "Creation of Bacula PostgreSQL tables failed."