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 Device (
160 MediaTypeId INTEGER NOT NULL,
161 StorageId INTEGER UNSIGNED,
162 DevMounts INTEGER NOT NULL DEFAULT 0,
163 DevReadBytes BIGINT NOT NULL DEFAULT 0,
164 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
165 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
166 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
167 DevReadTime BIGINT NOT NULL DEFAULT 0,
168 DevWriteTime BIGINT NOT NULL DEFAULT 0,
169 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
170 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
171 CleaningDate TIMESTAMP WITHOUT TIME ZONE,
172 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
173 PRIMARY KEY(DeviceId)
176 CREATE TABLE Storage (
179 AutoChanger INTEGER DEFAULT 0,
180 PRIMARY KEY(StorageId)
185 poolid serial not null,
187 numvols integer not null default 0,
188 maxvols integer not null default 0,
189 useonce smallint not null default 0,
190 usecatalog smallint not null default 0,
191 acceptanyvolume smallint not null default 0,
192 volretention bigint not null default 0,
193 voluseduration bigint not null default 0,
194 maxvoljobs integer not null default 0,
195 maxvolfiles integer not null default 0,
196 maxvolbytes bigint not null default 0,
197 autoprune smallint not null default 0,
198 recycle smallint not null default 0,
200 check (pooltype is null or (pooltype in ('Backup','Copy','Cloned','Archive','Migration'))),
201 labeltype integer not null default 0,
202 labelformat text not null,
203 enabled smallint not null default 1,
204 scratchpoolid integer default 0,
205 recyclepoolid integer default 0,
206 NextPoolId integer default 0,
207 MigrationHighBytes BIGINT DEFAULT 0,
208 MigrationLowBytes BIGINT DEFAULT 0,
209 MigrationTime BIGINT DEFAULT 0,
213 CREATE INDEX pool_name_idx on pool (name);
217 clientid serial not null,
220 autoprune smallint default 0,
221 fileretention bigint not null,
222 jobretention bigint not null,
223 primary key (clientid)
226 create unique index client_name_idx on client (name);
229 CREATE TABLE counters
231 counter text not null,
234 currentvalue integer,
235 wrapcounter text not null,
236 primary key (counter)
241 versionid integer not null
244 INSERT INTO Version (VersionId) VALUES (8);
247 CREATE TABLE basefiles
249 baseid serial not null,
250 jobid integer not null,
251 fileid integer not null,
257 CREATE TABLE unsavedfiles
259 UnsavedId integer not null,
260 jobid integer not null,
261 pathid integer not null,
262 filenameid integer not null,
263 primary key (UnsavedId)
266 CREATE TABLE CDImages
268 MediaId integer not null,
269 LastBurn timestamp without time zone not null,
270 primary key (MediaId)
273 -- Make sure we have appropriate permissions
278 echo "Creation of Bacula PostgreSQL tables succeeded."
280 echo "Creation of Bacula PostgreSQL tables failed."