/usr/share/bacula-director/make_postgresql_tables is in bacula-director-pgsql 5.2.6+dfsg-9.3.
This file is owned by root:root, with mode 0o755.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 | #!/bin/sh
#
# shell script to create Bacula PostgreSQL tables
#
# Important note:
# You won't get any support for performance issue if you changed the default
# schema.
#
bindir=/usr/bin
PATH="$bindir:$PATH"
db_name=${db_name:-XXX_DBNAME_XXX}
psql -f - -d ${db_name} $* <<END-OF-DATA
CREATE TABLE Filename
(
FilenameId serial not null,
Name text not null,
primary key (FilenameId)
);
ALTER TABLE Filename ALTER COLUMN Name SET STATISTICS 1000;
CREATE UNIQUE INDEX filename_name_idx on Filename (Name);
CREATE TABLE Path
(
PathId serial not null,
Path text not null,
primary key (PathId)
);
ALTER TABLE Path ALTER COLUMN Path SET STATISTICS 1000;
CREATE UNIQUE INDEX path_name_idx on Path (Path);
-- We strongly recommend to avoid the temptation to add new indexes.
-- In general, these will cause very significant performance
-- problems in other areas. A better approch is to carefully check
-- that all your memory configuation parameters are
-- suitable for the size of your installation. If you backup
-- millions of files, you need to adapt the database memory
-- configuration parameters concerning sorting, joining and global
-- memory. By default, sort and join parameters are very small
-- (sometimes 8Kb), and having sufficient memory specified by those
-- parameters is extremely important to run fast.
-- In File table
-- FileIndex can be 0 for FT_DELETED files
-- FileNameId can link to Filename.Name='' for directories
CREATE TABLE File
(
FileId bigserial not null,
FileIndex integer not null default 0,
JobId integer not null,
PathId integer not null,
FilenameId integer not null,
DeltaSeq smallint not null default 0,
MarkId integer not null default 0,
LStat text not null,
Md5 text not null,
primary key (FileId)
);
CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId);
CREATE INDEX file_jobid_idx on File (JobId);
--
-- Add this if you have a good number of job
-- that run at the same time
-- ALTER SEQUENCE file_fileid_seq CACHE 1000;
--
-- Possibly add one or more of the following indexes
-- if your Verifies are too slow, but they can slow down
-- backups.
--
-- CREATE INDEX file_pathid_idx on file(pathid);
-- CREATE INDEX file_filenameid_idx on file(filenameid);
CREATE TABLE RestoreObject (
RestoreObjectId SERIAL NOT NULL,
ObjectName TEXT NOT NULL,
RestoreObject BYTEA NOT NULL,
PluginName TEXT NOT NULL,
ObjectLength INTEGER DEFAULT 0,
ObjectFullLength INTEGER DEFAULT 0,
ObjectIndex INTEGER DEFAULT 0,
ObjectType INTEGER DEFAULT 0,
FileIndex INTEGER DEFAULT 0,
JobId INTEGER,
ObjectCompression INTEGER DEFAULT 0,
PRIMARY KEY(RestoreObjectId)
);
CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
CREATE TABLE Job
(
JobId serial not null,
Job text not null,
Name text not null,
Type char(1) not null,
Level char(1) not null,
ClientId integer default 0,
JobStatus char(1) not null,
SchedTime timestamp without time zone,
StartTime timestamp without time zone,
EndTime timestamp without time zone,
RealEndTime timestamp without time zone,
JobTDate bigint default 0,
VolSessionId integer default 0,
volSessionTime integer default 0,
JobFiles integer default 0,
JobBytes bigint default 0,
ReadBytes bigint default 0,
JobErrors integer default 0,
JobMissingFiles integer default 0,
PoolId integer default 0,
FilesetId integer default 0,
PriorJobid integer default 0,
PurgedFiles smallint default 0,
HasBase smallint default 0,
HasCache smallint default 0,
Reviewed smallint default 0,
Comment text,
primary key (jobid)
);
CREATE INDEX job_name_idx on job (name);
-- Create a table like Job for long term statistics
CREATE TABLE JobHisto (LIKE Job);
CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
CREATE TABLE Location (
LocationId serial not null,
Location text not null,
Cost integer default 0,
Enabled smallint,
primary key (LocationId)
);
CREATE TABLE fileset
(
filesetid serial not null,
fileset text not null,
md5 text not null,
createtime timestamp without time zone not null,
primary key (filesetid)
);
CREATE INDEX fileset_name_idx on fileset (fileset);
CREATE TABLE jobmedia
(
jobmediaid serial not null,
jobid integer not null,
mediaid integer not null,
firstindex integer default 0,
lastindex integer default 0,
startfile integer default 0,
endfile integer default 0,
startblock bigint default 0,
endblock bigint default 0,
volindex integer default 0,
primary key (jobmediaid)
);
CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
CREATE TABLE media
(
mediaid serial not null,
volumename text not null,
slot integer default 0,
poolid integer default 0,
mediatype text not null,
mediatypeid integer default 0,
labeltype integer default 0,
firstwritten timestamp without time zone,
lastwritten timestamp without time zone,
labeldate timestamp without time zone,
voljobs integer default 0,
volfiles integer default 0,
volblocks integer default 0,
volmounts integer default 0,
volbytes bigint default 0,
volparts integer default 0,
volerrors integer default 0,
volwrites integer default 0,
volcapacitybytes bigint default 0,
volstatus text not null
check (volstatus in ('Full','Archive','Append',
'Recycle','Purged','Read-Only','Disabled',
'Error','Busy','Used','Cleaning','Scratch')),
enabled smallint default 1,
recycle smallint default 0,
ActionOnPurge smallint default 0,
volretention bigint default 0,
voluseduration bigint default 0,
maxvoljobs integer default 0,
maxvolfiles integer default 0,
maxvolbytes bigint default 0,
inchanger smallint default 0,
StorageId integer default 0,
DeviceId integer default 0,
mediaaddressing smallint default 0,
volreadtime bigint default 0,
volwritetime bigint default 0,
endfile integer default 0,
endblock bigint default 0,
LocationId integer default 0,
recyclecount integer default 0,
initialwrite timestamp without time zone,
scratchpoolid integer default 0,
recyclepoolid integer default 0,
comment text,
primary key (mediaid)
);
create unique index media_volumename_id on media (volumename);
CREATE TABLE MediaType (
MediaTypeId SERIAL,
MediaType TEXT NOT NULL,
ReadOnly INTEGER DEFAULT 0,
PRIMARY KEY(MediaTypeId)
);
CREATE TABLE Storage (
StorageId SERIAL,
Name TEXT NOT NULL,
AutoChanger INTEGER DEFAULT 0,
PRIMARY KEY(StorageId)
);
CREATE TABLE Device (
DeviceId SERIAL,
Name TEXT NOT NULL,
MediaTypeId INTEGER NOT NULL,
StorageId INTEGER NOT NULL,
DevMounts INTEGER NOT NULL DEFAULT 0,
DevReadBytes BIGINT NOT NULL DEFAULT 0,
DevWriteBytes BIGINT NOT NULL DEFAULT 0,
DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
DevReadTime BIGINT NOT NULL DEFAULT 0,
DevWriteTime BIGINT NOT NULL DEFAULT 0,
DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
CleaningDate timestamp without time zone,
CleaningPeriod BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY(DeviceId)
);
CREATE TABLE pool
(
poolid serial not null,
name text not null,
numvols integer default 0,
maxvols integer default 0,
useonce smallint default 0,
usecatalog smallint default 0,
acceptanyvolume smallint default 0,
volretention bigint default 0,
voluseduration bigint default 0,
maxvoljobs integer default 0,
maxvolfiles integer default 0,
maxvolbytes bigint default 0,
autoprune smallint default 0,
recycle smallint default 0,
ActionOnPurge smallint default 0,
pooltype text
check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
labeltype integer default 0,
labelformat text not null,
enabled smallint default 1,
scratchpoolid integer default 0,
recyclepoolid integer default 0,
NextPoolId integer default 0,
MigrationHighBytes BIGINT DEFAULT 0,
MigrationLowBytes BIGINT DEFAULT 0,
MigrationTime BIGINT DEFAULT 0,
primary key (poolid)
);
CREATE INDEX pool_name_idx on pool (name);
CREATE TABLE client
(
clientid serial not null,
name text not null,
uname text not null,
autoprune smallint default 0,
fileretention bigint default 0,
jobretention bigint default 0,
primary key (clientid)
);
create unique index client_name_idx on client (name);
CREATE TABLE Log
(
LogId serial not null,
JobId integer not null,
Time timestamp without time zone,
LogText text not null,
primary key (LogId)
);
create index log_name_idx on Log (JobId);
CREATE TABLE LocationLog (
LocLogId SERIAL NOT NULL,
Date timestamp without time zone,
Comment TEXT NOT NULL,
MediaId INTEGER DEFAULT 0,
LocationId INTEGER DEFAULT 0,
newvolstatus text not null
check (newvolstatus in ('Full','Archive','Append',
'Recycle','Purged','Read-Only','Disabled',
'Error','Busy','Used','Cleaning','Scratch')),
newenabled smallint,
PRIMARY KEY(LocLogId)
);
CREATE TABLE counters
(
counter text not null,
minvalue integer default 0,
maxvalue integer default 0,
currentvalue integer default 0,
wrapcounter text not null,
primary key (counter)
);
CREATE TABLE basefiles
(
baseid serial not null,
jobid integer not null,
fileid bigint not null,
fileindex integer ,
basejobid integer ,
primary key (baseid)
);
CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
CREATE TABLE unsavedfiles
(
UnsavedId integer not null,
jobid integer not null,
pathid integer not null,
filenameid integer not null,
primary key (UnsavedId)
);
CREATE TABLE CDImages
(
MediaId integer not null,
LastBurn timestamp without time zone not null,
primary key (MediaId)
);
CREATE TABLE PathHierarchy
(
PathId integer NOT NULL,
PPathId integer NOT NULL,
CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
);
CREATE INDEX pathhierarchy_ppathid
ON PathHierarchy (PPathId);
CREATE TABLE PathVisibility
(
PathId integer NOT NULL,
JobId integer NOT NULL,
Size int8 DEFAULT 0,
Files int4 DEFAULT 0,
CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
);
CREATE INDEX pathvisibility_jobid
ON PathVisibility (JobId);
CREATE TABLE version
(
versionid integer not null
);
CREATE TABLE Status (
JobStatus CHAR(1) NOT NULL,
JobStatusLong TEXT,
Severity int,
PRIMARY KEY (JobStatus)
);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('C', 'Created, not yet running',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('R', 'Running',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('B', 'Blocked',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('T', 'Completed successfully', 10);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('E', 'Terminated with errors', 25);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('e', 'Non-fatal error',20);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('f', 'Fatal error',100);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('D', 'Verify found differences',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('A', 'Canceled by user',90);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('F', 'Waiting for Client',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('S', 'Waiting for Storage daemon',15);
INSERT INTO Status (JobStatus,JobStatusLong) VALUES
('m', 'Waiting for new media');
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('M', 'Waiting for media mount',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('s', 'Waiting for storage resource',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('j', 'Waiting for job resource',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('c', 'Waiting for client resource',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('d', 'Waiting on maximum jobs',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('t', 'Waiting on start time',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('p', 'Waiting on higher priority jobs',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('a', 'SD despooling attributes',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('i', 'Doing batch insert file records',15);
INSERT INTO Version (VersionId) VALUES (14);
-- Make sure we have appropriate permissions
END-OF-DATA
pstat=$?
if test $pstat = 0;
then
echo "Creation of Bacula PostgreSQL tables succeeded."
else
echo "Creation of Bacula PostgreSQL tables failed."
fi
exit $pstat
|