3 # shell script to create Bacula PostgreSQL tables
7 if $bindir/psql $* bacula -f - <<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 mediaaddressing smallint not null default 0,
139 volreadtime bigint not null default 0,
140 volwritetime bigint not null default 0,
141 endfile integer not null default 0,
142 endblock bigint not null default 0,
143 primary key (mediaid)
146 create unique index media_volumename_id on media (volumename);
149 CREATE TABLE MediaType (
151 MediaType TEXT NOT NULL,
152 ReadOnly INTEGER DEFAULT 0,
153 PRIMARY KEY(MediaTypeId)
156 CREATE TABLE Device (
159 MediaTypeId INTEGER NOT NULL,
160 StorageId INTEGER UNSIGNED,
161 DevMounts INTEGER NOT NULL DEFAULT 0,
162 DevReadBytes BIGINT NOT NULL DEFAULT 0,
163 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
164 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
165 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
166 DevReadTime BIGINT NOT NULL DEFAULT 0,
167 DevWriteTime BIGINT NOT NULL DEFAULT 0,
168 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
169 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
170 CleaningDate TIMESTAMP WITHOUT TIME ZONE,
171 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
172 PRIMARY KEY(DeviceId)
175 CREATE TABLE Storage (
178 AutoChanger INTEGER DEFAULT 0,
179 PRIMARY KEY(StorageId)
184 poolid serial not null,
186 numvols integer not null default 0,
187 maxvols integer not null default 0,
188 useonce smallint not null default 0,
189 usecatalog smallint not null default 0,
190 acceptanyvolume smallint not null default 0,
191 volretention bigint not null default 0,
192 voluseduration bigint not null default 0,
193 maxvoljobs integer not null default 0,
194 maxvolfiles integer not null default 0,
195 maxvolbytes bigint not null default 0,
196 autoprune smallint not null default 0,
197 recycle smallint not null default 0,
199 check (pooltype is null or (pooltype in ('Backup','Copy','Cloned','Archive','Migration'))),
200 labeltype integer not null default 0,
201 labelformat text not null,
202 enabled smallint not null default 1,
203 scratchpoolid integer,
204 recyclepoolid integer,
208 CREATE INDEX pool_name_idx on pool (name);
212 clientid serial not null,
215 autoprune smallint default 0,
216 fileretention bigint not null,
217 jobretention bigint not null,
218 primary key (clientid)
221 create unique index client_name_idx on client (name);
224 CREATE TABLE counters
226 counter text not null,
229 currentvalue integer,
230 wrapcounter text not null,
231 primary key (counter)
236 versionid integer not null
239 INSERT INTO Version (VersionId) VALUES (8);
242 CREATE TABLE basefiles
244 baseid serial not null,
245 jobid integer not null,
246 fileid integer not null,
252 CREATE TABLE unsavedfiles
254 UnsavedId integer not null,
255 jobid integer not null,
256 pathid integer not null,
257 filenameid integer not null,
258 primary key (UnsavedId)
261 CREATE TABLE CDImages
263 MediaId integer not null,
264 LastBurn timestamp without time zone not null,
265 primary key (MediaId)
268 -- Make sure we have appropriate permissions
273 echo "Creation of Bacula PostgreSQL tables succeeded."
275 echo "Creation of Bacula PostgreSQL tables failed."