#
# shell script to create Bacula PostgreSQL tables
#
-bindir=@SQL_BINDIR@
+# 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@}
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);
-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);
-
-CREATE TABLE file
+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,
- 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_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);
+CREATE INDEX file_jobid_idx on File (JobId);
--
-- Add this if you have a good number of job
--
-- 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 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,
+ 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,
- Comment text,
+ 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)
);
'Error','Busy','Used','Cleaning','Scratch')),
enabled smallint default 1,
recycle smallint default 0,
- ActionOnPurge smallint default 0,
+ ActionOnPurge smallint 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,
);
CREATE INDEX pathhierarchy_ppathid
- ON PathHierarchy (PPathId);
+ ON PathHierarchy (PPathId);
CREATE TABLE PathVisibility
(
CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
);
CREATE INDEX pathvisibility_jobid
- ON PathVisibility (JobId);
+ ON PathVisibility (JobId);
CREATE TABLE version
(
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('i', 'Doing batch insert file records',15);
-INSERT INTO Version (VersionId) VALUES (12);
+INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
-- Make sure we have appropriate permissions