Big backport from Enterprise

[bacula/bacula] / bacula / src / cats / make_postgresql_tables.in

diff --git a/bacula/src/cats/make_postgresql_tables.in b/bacula/src/cats/make_postgresql_tables.in
index 0bb7bfa00cccfdba1974e9910b93c201de787691..43833c4a9f0548a06d6bb214db0a5d10c85aee4d 100644 (file)
--- a/bacula/src/cats/make_postgresql_tables.in
+++ b/bacula/src/cats/make_postgresql_tables.in
@@ -2,6 +2,9 @@
 #
 # shell script to create Bacula PostgreSQL tables
 #
+# 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.
@@ -20,7 +23,7 @@ CREATE TABLE Filename
 );
 
 ALTER TABLE Filename ALTER COLUMN Name SET STATISTICS 1000;
-CREATE UNIQUE INDEX filename_name_idx on Filename (Name);
+CREATE UNIQUE INDEX filename_name_idx on Filename (Name text_pattern_ops);
 
 CREATE TABLE Path
 (
@@ -30,7 +33,7 @@ CREATE TABLE Path
 );
 
 ALTER TABLE Path ALTER COLUMN Path SET STATISTICS 1000;
-CREATE UNIQUE INDEX path_name_idx on Path (Path);
+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
@@ -53,6 +56,7 @@ CREATE TABLE File
     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,
@@ -65,7 +69,7 @@ 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
@@ -107,7 +111,7 @@ CREATE TABLE Job
     RealEndTime       timestamp   without time zone,
     JobTDate         bigint      default 0,
     VolSessionId      integer    default 0,
-    volSessionTime    integer    default 0,
+    VolSessionTime    integer    default 0,
     JobFiles         integer     default 0,
     JobBytes         bigint      default 0,
     ReadBytes        bigint      default 0,
@@ -121,10 +125,12 @@ CREATE TABLE Job
     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);
@@ -149,7 +155,7 @@ CREATE TABLE fileset
     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
 (
@@ -183,12 +189,19 @@ CREATE TABLE media
     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',
@@ -196,6 +209,7 @@ CREATE TABLE media
     enabled          smallint    default 1,
     recycle          smallint    default 0,
     ActionOnPurge     smallint   default 0,
+    cacheretention    bigint     default 0,
     volretention      bigint     default 0,
     voluseduration    bigint     default 0,
     maxvoljobs       integer     default 0,
@@ -218,8 +232,9 @@ CREATE TABLE media
     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,
@@ -264,6 +279,7 @@ CREATE TABLE pool
     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,
@@ -286,7 +302,7 @@ CREATE TABLE pool
     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
 (
@@ -299,7 +315,7 @@ 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
 (
@@ -341,7 +357,7 @@ CREATE TABLE counters
 
 CREATE TABLE basefiles
 (
-    baseid           serial                not null,
+    baseid           bigserial             not null,
     jobid            integer               not null,
     fileid           bigint                not null,
     fileindex        integer                       ,
@@ -443,8 +459,30 @@ 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 (@BDB_VERSION@);
+INSERT INTO Version (VersionId) VALUES (16);
 
 -- Make sure we have appropriate permissions