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

diff --git a/bacula/src/cats/make_postgresql_tables.in b/bacula/src/cats/make_postgresql_tables.in
index ce6bbdc285..fd831d3774 100644
--- a/bacula/src/cats/make_postgresql_tables.in
+++ b/bacula/src/cats/make_postgresql_tables.in
@@ -2,83 +2,135 @@
 #
 # 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@
-db_name=@db_name@
+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)
 );
 
-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	  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,
-    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,
     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,
@@ -111,7 +163,6 @@ CREATE TABLE jobmedia
     startblock	      bigint	  default 0,
     endblock	      bigint	  default 0,
     volindex	      integer	  default 0,
-    copy	      integer	  default 0,
     primary key (jobmediaid)
 );
 
@@ -144,6 +195,7 @@ CREATE TABLE media
 	      '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,
@@ -219,6 +271,7 @@ CREATE TABLE pool
     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,
@@ -290,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,
@@ -313,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
@@ -320,54 +396,55 @@ 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 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 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