# shell script to create Bacula PostgreSQL tables
#
bindir=@SQL_BINDIR@
+db_name=${db_name:-@db_name@}
-$bindir/psql -f - -d bacula $* <<END-OF-DATA
+$bindir/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,
CREATE INDEX file_jobid_idx on file (jobid);
CREATE INDEX file_fp_idx on file (filenameid, pathid);
+--
+-- 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.
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,
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,
'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,
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,
MediaId INTEGER DEFAULT 0,
LocationId INTEGER DEFAULT 0,
newvolstatus text not null
- check (volstatus in ('Full','Archive','Append',
+ check (newvolstatus in ('Full','Archive','Append',
'Recycle','Purged','Read-Only','Disabled',
'Error','Busy','Used','Cleaning','Scratch')),
newenabled smallint,
(
baseid serial not null,
jobid integer not null,
- fileid integer not null,
+ fileid bigint not null,
fileindex integer ,
basejobid integer ,
primary key (baseid)
('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 Version (VersionId) VALUES (10);
+INSERT INTO Version (VersionId) VALUES (11);
-- Make sure we have appropriate permissions