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 primary key (jobmediaid)
105 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
109 mediaid serial not null,
110 volumename text not null,
111 slot integer not null default 0,
112 poolid integer not null,
113 mediatype text not null,
114 labeltype integer not null default 0,
115 firstwritten timestamp without time zone,
116 lastwritten timestamp without time zone,
117 labeldate timestamp without time zone,
118 voljobs integer not null default 0,
119 volfiles integer not null default 0,
120 volblocks integer not null default 0,
121 volmounts integer not null default 0,
122 volbytes bigint not null default 0,
123 volparts integer not null default 0,
124 volerrors integer not null default 0,
125 volwrites integer not null default 0,
126 volcapacitybytes bigint not null default 0,
127 volstatus text not null
128 check (volstatus in ('Full','Archive','Append',
129 'Recycle','Purged','Read-Only','Disabled',
130 'Error','Busy','Used','Cleaning')),
131 recycle smallint not null default 0,
132 volretention bigint not null default 0,
133 voluseduration bigint not null default 0,
134 maxvoljobs integer not null default 0,
135 maxvolfiles integer not null default 0,
136 maxvolbytes bigint not null default 0,
137 inchanger smallint not null default 0,
138 StorageId integer default 0,
139 mediaaddressing smallint not null default 0,
140 volreadtime bigint not null default 0,
141 volwritetime bigint not null default 0,
142 endfile integer not null default 0,
143 endblock bigint not null default 0,
144 primary key (mediaid)
147 create unique index media_volumename_id on media (volumename);
150 CREATE TABLE MediaType (
152 MediaType TEXT NOT NULL,
153 ReadOnly INTEGER DEFAULT 0,
154 PRIMARY KEY(MediaTypeId)
157 CREATE TABLE Storage (
160 AutoChanger INTEGER DEFAULT 0,
161 PRIMARY KEY(StorageId)
164 CREATE TABLE Device (
167 MediaTypeId INTEGER NOT NULL,
168 StorageId INTEGER UNSIGNED,
169 DevMounts INTEGER NOT NULL DEFAULT 0,
170 DevReadBytes BIGINT NOT NULL DEFAULT 0,
171 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
172 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
173 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
174 DevReadTime BIGINT NOT NULL DEFAULT 0,
175 DevWriteTime BIGINT NOT NULL DEFAULT 0,
176 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
177 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
178 CleaningDate TIMESTAMP WITHOUT TIME ZONE,
179 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
180 PRIMARY KEY(DeviceId)
186 poolid serial not null,
188 numvols integer not null default 0,
189 maxvols integer not null default 0,
190 useonce smallint not null default 0,
191 usecatalog smallint not null default 0,
192 acceptanyvolume smallint not null default 0,
193 volretention bigint not null default 0,
194 voluseduration bigint not null default 0,
195 maxvoljobs integer not null default 0,
196 maxvolfiles integer not null default 0,
197 maxvolbytes bigint not null default 0,
198 autoprune smallint not null default 0,
199 recycle smallint not null default 0,
201 check (pooltype is null or (pooltype in ('Backup','Copy','Cloned','Archive','Migration'))),
202 labeltype integer not null default 0,
203 labelformat text not null,
204 enabled smallint not null default 1,
205 scratchpoolid integer default 0,
206 recyclepoolid integer default 0,
207 NextPoolId integer default 0,
208 MigrationHighBytes BIGINT DEFAULT 0,
209 MigrationLowBytes BIGINT DEFAULT 0,
210 MigrationTime BIGINT DEFAULT 0,
214 CREATE INDEX pool_name_idx on pool (name);
218 clientid serial not null,
221 autoprune smallint default 0,
222 fileretention bigint not null,
223 jobretention bigint not null,
224 primary key (clientid)
227 create unique index client_name_idx on client (name);
230 CREATE TABLE counters
232 counter text not null,
235 currentvalue integer,
236 wrapcounter text not null,
237 primary key (counter)
242 versionid integer not null
245 INSERT INTO Version (VersionId) VALUES (8);
248 CREATE TABLE basefiles
250 baseid serial not null,
251 jobid integer not null,
252 fileid integer not null,
258 CREATE TABLE unsavedfiles
260 UnsavedId integer not null,
261 jobid integer not null,
262 pathid integer not null,
263 filenameid integer not null,
264 primary key (UnsavedId)
267 CREATE TABLE CDImages
269 MediaId integer not null,
270 LastBurn timestamp without time zone not null,
271 primary key (MediaId)
274 -- Make sure we have appropriate permissions
279 echo "Creation of Bacula PostgreSQL tables succeeded."
281 echo "Creation of Bacula PostgreSQL tables failed."