# shell script to create Bacula PostgreSQL tables
#
bindir=@SQL_BINDIR@
+PATH="$bindir:$PATH"
+db_name=${db_name:-@db_name@}
-if $bindir/psql $* bacula -f - <<END-OF-DATA
+psql -f - -d ${db_name} $* <<END-OF-DATA
CREATE TABLE filename
(
primary key (filenameid)
);
-CREATE INDEX filename_name_idx on filename (name);
+ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000;
+CREATE UNIQUE INDEX filename_name_idx on filename (name);
CREATE TABLE path
(
primary key (pathid)
);
-CREATE INDEX path_name_idx on path (path);
+ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000;
+CREATE UNIQUE INDEX path_name_idx on path (path);
CREATE TABLE file
(
- fileid serial not null,
+ fileid bigserial not null,
fileindex integer not null default 0,
jobid integer not null,
pathid integer not null,
primary key (fileid)
);
-CREATE INDEX file_jobid_idx on file (jobid);
-CREATE INDEX file_fp_idx on file (filenameid, pathid);
+CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId);
+
+-- If you need performances, you can remove this index
+-- the database engine is able to use the composite index
+-- to find all records with a given JobId
+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
--
-- CREATE INDEX file_pathid_idx on file(pathid);
-- CREATE INDEX file_filenameid_idx on file(filenameid);
--- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
-CREATE TABLE job
+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,
- jobstatus char(1) not null,
- schedtime timestamp without time zone not null,
- starttime timestamp without time zone,
- endtime timestamp without time zone,
- jobtdate bigint not null,
- volsessionid integer not null default 0,
- volsessiontime integer not null default 0,
- jobfiles integer not null default 0,
- jobbytes bigint not null default 0,
- joberrors integer not null default 0,
- jobmissingfiles integer not null default 0,
- poolid integer,
- filesetid integer,
- purgedfiles smallint not null default 0,
- hasbase smallint not null default 0,
+ 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,
jobmediaid serial not null,
jobid integer not null,
mediaid integer not null,
- firstindex integer not null default 0,
- lastindex integer not null default 0,
- startfile integer not null default 0,
- endfile integer not null default 0,
- startblock bigint not null default 0,
- endblock bigint not null default 0,
- volindex integer not null default 0,
+ 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)
);
(
mediaid serial not null,
volumename text not null,
- slot integer not null default 0,
- poolid integer not null,
+ slot integer default 0,
+ poolid integer default 0,
mediatype text not null,
- labeltype integer not null default 0,
+ 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 not null default 0,
- volfiles integer not null default 0,
- volblocks integer not null default 0,
- volmounts integer not null default 0,
- volbytes bigint not null default 0,
- volparts integer not null default 0,
- volerrors integer not null default 0,
- volwrites integer not null default 0,
- volcapacitybytes bigint not null default 0,
+ 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')),
- recycle smallint not null default 0,
- volretention bigint not null default 0,
- voluseduration bigint not null default 0,
- maxvoljobs integer not null default 0,
- maxvolfiles integer not null default 0,
- maxvolbytes bigint not null default 0,
- inchanger smallint not null default 0,
- mediaaddressing smallint not null default 0,
- volreadtime bigint not null default 0,
- volwritetime bigint not null default 0,
- endfile integer not null default 0,
- endblock bigint not null default 0,
+ '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)
);
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 UNSIGNED,
+ StorageId INTEGER NOT NULL,
DevMounts INTEGER NOT NULL DEFAULT 0,
DevReadBytes BIGINT NOT NULL DEFAULT 0,
DevWriteBytes 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 UNSIGNED DEFAULT 0,
- CleaningDate TIMESTAMP WITHOUT TIME ZONE,
+ DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
+ CleaningDate timestamp without time zone,
CleaningPeriod BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY(DeviceId)
);
-CREATE TABLE Storage (
- StorageId SERIAL,
- Name TEXT NOT NULL,
- AutoChanger INTEGER DEFAULT 0,
- PRIMARY KEY(StorageId)
- );
CREATE TABLE pool
(
poolid serial not null,
name text not null,
- numvols integer not null default 0,
- maxvols integer not null default 0,
- useonce smallint not null default 0,
- usecatalog smallint not null default 0,
- acceptanyvolume smallint not null default 0,
- volretention bigint not null default 0,
- voluseduration bigint not null default 0,
- maxvoljobs integer not null default 0,
- maxvolfiles integer not null default 0,
- maxvolbytes bigint not null default 0,
- autoprune smallint not null default 0,
- recycle smallint not null default 0,
+ 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 is null or (pooltype in ('Backup','Copy','Cloned','Archive','Migration'))),
- labeltype integer not null default 0,
+ check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
+ labeltype integer default 0,
labelformat text not null,
- enabled smallint not null default 1,
- scratchpoolid integer,
- recyclepoolid integer,
+ 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)
);
name text not null,
uname text not null,
autoprune smallint default 0,
- fileretention bigint not null,
- jobretention bigint not null,
+ 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,
- maxvalue integer,
- currentvalue integer,
+ minvalue integer default 0,
+ maxvalue integer default 0,
+ currentvalue integer default 0,
wrapcounter text not null,
primary key (counter)
);
-CREATE TABLE version
-(
- versionid integer not null
-);
-
-INSERT INTO Version (VersionId) VALUES (8);
CREATE TABLE basefiles
(
baseid serial not null,
jobid integer not null,
- fileid 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,
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 (12);
+
-- 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 0
+exit $pstat
projects / bacula / bacula / blobdiff