#
# shell script to create Bacula PostgreSQL tables
#
-bindir=@SQL_BINDIR@
+# Copyright (C) 2000-2017 Kern Sibbald
+# License: BSD 2-Clause; see file LICENSE-FOSS
+#
+# Important note:
+# You won't get any support for performance issue if you changed the default
+# schema.
+#
+bindir=@POSTGRESQL_BINDIR@
+PATH="$bindir:$PATH"
db_name=${db_name:-@db_name@}
-$bindir/psql -f - -d ${db_name} $* <<END-OF-DATA
+psql -f - -d ${db_name} $* <<END-OF-DATA
-CREATE TABLE filename
+CREATE TABLE Filename
(
- filenameid serial not null,
- name text not null,
- primary key (filenameid)
+ 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);
+ALTER TABLE Filename ALTER COLUMN Name SET STATISTICS 1000;
+CREATE UNIQUE INDEX filename_name_idx on Filename (Name text_pattern_ops);
-CREATE TABLE path
+CREATE TABLE Path
(
- pathid serial not null,
- path text not null,
- primary key (pathid)
+ 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);
+ALTER TABLE Path ALTER COLUMN Path SET STATISTICS 1000;
+CREATE UNIQUE INDEX path_name_idx on Path (Path text_pattern_ops);
+
+-- 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
+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,
- markid integer not null default 0,
- lstat text not null,
- md5 text not null,
- primary key (fileid)
+ 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_jobid_idx on file (jobid);
-CREATE INDEX file_fp_idx on file (filenameid, pathid);
+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;
+-- ALTER SEQUENCE file_fileid_seq CACHE 10;
--
-- Possibly add one or more of the following indexes
--- if your Verifies are too slow.
+-- 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 INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
-CREATE TABLE job
+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,
- purgedfiles smallint default 0,
- hasbase smallint default 0,
- priorjobid integer 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,
+ FileTable text default 'File',
primary key (jobid)
);
-CREATE INDEX job_name_idx on job (name);
+CREATE INDEX job_name_idx on job (name text_pattern_ops);
+CREATE INDEX job_jobtdate_idx on job (jobtdate);
-- Create a table like Job for long term statistics
CREATE TABLE JobHisto (LIKE Job);
-CREATE INDEX jobhisto_idx ON jobhisto ( starttime );
+CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
CREATE TABLE Location (
primary key (filesetid)
);
-CREATE INDEX fileset_name_idx on fileset (fileset);
+CREATE INDEX fileset_name_idx on fileset (fileset text_pattern_ops);
CREATE TABLE jobmedia
(
startblock bigint default 0,
endblock bigint default 0,
volindex integer default 0,
- copy integer default 0,
primary key (jobmediaid)
);
voljobs integer default 0,
volfiles integer default 0,
volblocks integer default 0,
+ volparts integer default 0,
+ volcloudparts integer default 0,
volmounts integer default 0,
volbytes bigint default 0,
- volparts integer default 0,
+ volabytes bigint default 0,
+ volapadding bigint default 0,
+ volholebytes bigint default 0,
+ volholes integer default 0,
+ voltype integer default 0,
volerrors integer default 0,
- volwrites integer default 0,
+ volwrites bigint default 0,
volcapacitybytes bigint default 0,
+ lastpartbytes 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,
+ ActionOnPurge smallint default 0,
+ cacheretention bigint default 0,
volretention bigint default 0,
voluseduration bigint default 0,
maxvoljobs integer default 0,
primary key (mediaid)
);
-create unique index media_volumename_id on media (volumename);
-
+CREATE UNIQUE INDEX media_volumename_id ON Media (VolumeName text_pattern_ops);
+CREATE INDEX media_poolid_idx ON Media (PoolId);
+CREATE INDEX media_storageid_idx ON Media (StorageId);
CREATE TABLE MediaType (
MediaTypeId SERIAL,
useonce smallint default 0,
usecatalog smallint default 0,
acceptanyvolume smallint default 0,
+ cacheretention bigint default 0,
volretention bigint default 0,
voluseduration bigint default 0,
maxvoljobs integer default 0,
maxvolbytes bigint default 0,
autoprune smallint default 0,
recycle smallint default 0,
- ActionOnPurge smallint default 0,
+ ActionOnPurge smallint default 0,
pooltype text
check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
labeltype integer default 0,
primary key (poolid)
);
-CREATE INDEX pool_name_idx on pool (name);
+CREATE INDEX pool_name_idx on pool (name text_pattern_ops);
CREATE TABLE client
(
primary key (clientid)
);
-create unique index client_name_idx on client (name);
+create unique index client_name_idx on client (name text_pattern_ops);
CREATE TABLE Log
(
CREATE TABLE basefiles
(
- baseid serial not null,
+ baseid bigserial not null,
jobid integer not null,
fileid bigint not null,
fileindex integer ,
primary key (baseid)
);
+CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
+
CREATE TABLE unsavedfiles
(
UnsavedId integer not null,
);
+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,
+ JobStatusLong TEXT,
+ Severity int,
PRIMARY KEY (JobStatus)
);
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('C', 'Created, not yet running');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('R', 'Running');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('B', 'Blocked');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('T', 'Completed successfully');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('E', 'Terminated with errors');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('e', 'Non-fatal error');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('f', 'Fatal error');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('D', 'Verify found differences');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('A', 'Canceled by user');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('F', 'Waiting for Client');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('S', 'Waiting for Storage daemon');
+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) VALUES
- ('M', 'Waiting for media mount');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('s', 'Waiting for storage resource');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('j', 'Waiting for job resource');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('c', 'Waiting for client resource');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('d', 'Waiting on maximum jobs');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('t', 'Waiting on start time');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('p', 'Waiting on higher priority jobs');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('a', 'SD despooling attributes');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('i', 'Doing batch insert file records');
+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 Status (JobStatus,JobStatusLong,Severity) VALUES
+ ('I', 'Incomplete Job',25);
+
+CREATE TABLE Snapshot (
+ SnapshotId serial,
+ Name text not null,
+ JobId integer default 0,
+ FileSetId integer default 0,
+ CreateTDate bigint default 0,
+ CreateDate timestamp without time zone not null,
+ ClientId int default 0,
+ Volume text not null,
+ Device text not null,
+ Type text not null,
+ Retention integer default 0,
+ Comment text,
+ primary key (SnapshotId)
+);
+
+CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device text_pattern_ops,
+ Volume text_pattern_ops,
+ Name text_pattern_ops);
-INSERT INTO Version (VersionId) VALUES (11);
+INSERT INTO Version (VersionId) VALUES (16);
-- Make sure we have appropriate permissions
projects / bacula / bacula / blobdiff