X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fmake_postgresql_tables.in;h=fd831d377440b197866a9bdc620718c26eff5a3c;hb=f5984fe3b5fe567e1609078ed5966f4d0428f0a7;hp=d1a6ad14df43f2b387c220492638de229b68f481;hpb=cb55ec8aa42630175e8839dd40161bd2efc2a44a;p=bacula%2Fbacula

diff --git a/bacula/src/cats/make_postgresql_tables.in b/bacula/src/cats/make_postgresql_tables.in
index d1a6ad14df..fd831d3774 100644
--- a/bacula/src/cats/make_postgresql_tables.in
+++ b/bacula/src/cats/make_postgresql_tables.in
@@ -2,80 +2,144 @@
 #
 # 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=@SQL_BINDIR@
+PATH="$bindir:$PATH"
+db_name=${db_name:-@db_name@}
 
-if $bindir/psql -f - -d bacula $* <<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)
 );
 
-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
+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)
 );
 
-CREATE 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	      serial	  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,
+    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;
 
 --
 -- 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,
-    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,
@@ -92,15 +156,13 @@ CREATE TABLE jobmedia
     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,
-    copy	      integer	  not null default 0,
-    stripe	      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)
 );
 
@@ -110,39 +172,49 @@ CREATE TABLE media
 (
     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',"Scratch')),
-    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,
-    StorageId	      integer		   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)
 );
 
@@ -167,7 +239,7 @@ 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,
@@ -176,8 +248,8 @@ CREATE TABLE Device (
    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)
    );
@@ -187,29 +259,30 @@ 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 in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
-    labeltype	      integer	  not null default 0,
+    labeltype	      integer	  default 0,
     labelformat       text	  not null,
-    enabled	      smallint	  not null 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,
+    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)
 );
 
@@ -221,20 +294,45 @@ CREATE TABLE client
     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)
 );
@@ -245,12 +343,14 @@ 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,
@@ -268,6 +368,27 @@ CREATE TABLE CDImages
 );
 
 
+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
@@ -275,59 +396,65 @@ CREATE TABLE version
 
 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 Version (VersionId) VALUES (9);
+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