SQWareRepository:postgres

Presentation

It is the repository module, and stores rdbms indicators for all RDBMS.
It is a MySQL database.
Objects prefix by tsqw_% or isqw_% are generic.
Objects prefix by tsqw{{{4}}}_% or isqw{{{4}}}_% are specific for PostgreSQL.
Installation guide for SQWareRepository for PostgreSQL

Naming convention

All specific objects for PostgreSQL are named with prefixes:

• tsqw{{{4}}}_% : for tables
• isqw{{{4}}}_% : for index
• isqw{{{4}}}_%_u : for uniques
• isqw{{{4}}}_%_pk : for primary key

Database content

tsqw_Repository

This table contains : Instance repository
Desc of tsqw_Repository :

/* Repository */
create table tsqw_Repository (
 pg_sid varchar(80) not null,
 virt_host_name varchar (255) not null,
 host_name varchar (255) not null,
 username  varchar (60) not null,
 port integer not null,
 comments varchar(500),
 contact varchar(100),
 status varchar(10) default 'ON',
 client varchar(60) default 'N.A',
 upd_date timestamp,
 env  varchar(12),
 globalhost varchar(255) null,
 custom1 varchar(255) null,
 custom2 varchar(255) null
)
;
alter table tsqw_Repository add constraint isqw_Repository_pk primary key (pg_sid);

grant select on tsqw_Repository to dbsqware_rlec;
grant all on tsqw_Repository to dbsqware_rmaj;

tsqw_RepositoryOther

This table contains : Instance RepositoryOther
Desc of tsqw_RepositoryOther  :

create table tsqw_RepositoryOther (
 pg_sid varchar(80) not null,
 virt_host_name varchar (255) not null,
 host_name varchar(255) not null,
 username  varchar(60) not null,
 port integer not null,
 comments varchar(500),
 contact varchar(100),
 status varchar(10) default 'XXX',
 client varchar (60) default 'N.A',
 upd_date timestamp,
 env  varchar(12),
 globalhost varchar(255) null,
 custom1 varchar(255) null,
 custom2 varchar(255) null
)
;

grant select on tsqw_RepositoryOther to dbsqware_rlec;
grant all on tsqw_RepositoryOther to dbsqware_rmaj;

tsqw_JobsExec

This table contains : Jobs monitoring
Desc of tsqw_JobsExec :

/* Jobs monitoring */
create table tsqw_JobsExec
(
 pg_sid varchar(80) not null,
 beginning timestamp not null,
 script char(80) not null,
 parameters varchar(400) not null,
 end_trt timestamp,
 duration char(8),
 status integer,
 log_id bigint default -1,
 log_file text
)
;
alter table tsqw_JobsExec add constraint isqw_JobsExec_pk primary key (pg_sid, beginning, script, parameters);
create index isqw_JobsExec_Log on tsqw_JobsExec(log_id);

grant select on tsqw_JobsExec to dbsqware_rlec;
grant all on tsqw_JobsExec to dbsqware_rmaj;

create sequence ssqw_log_id no maxvalue start with 100 increment by 1;
grant all on ssqw_log_id to dbsqware_rmaj;

tsqw_VolFS

This table contains : vol fs
Desc of tsqw_VolFS :

/* vol fs */
create table tsqw_VolFS (
 pg_sid varchar(80) not null,
 gather_date date not null,
 host_name varchar (64) not null,
 filesystem varchar (200) not null,
 size_fs integer not null,
 used integer not null,
 free integer not null,
 rate integer not null,
 mount varchar (200)
)
;
alter table tsqw_VolFS add constraint isqw_VolFS_pk primary key (pg_sid, gather_date,mount);

grant select on tsqw_VolFS to dbsqware_rlec;
grant all on tsqw_VolFS to dbsqware_rmaj;

tsqw_VolFSThreshold

This table contains : threshold
Desc of tsqw_VolFSThreshold :

/* threshold */
create table tsqw_VolFSThreshold (
 pg_sid varchar(80) not null,
 mount varchar (200),
 threshold_c integer not null,
 threshold_w integer not null
)
;
alter table tsqw_VolFSThreshold add constraint isqw_VolFSThreshold_pk primary key (pg_sid, mount);

grant select on tsqw_VolFSThreshold to dbsqware_rlec;
grant all on tsqw_VolFSThreshold to dbsqware_rmaj;

create table tsqw_VolFSThresholdDef (
 size_fs integer not null,
 threshold_c integer not null,
 threshold_w integer not null
)
;
alter table tsqw_VolFSThresholdDef add constraint isqw_VolFSThreshold_pk primary key (size_fs);

grant select on tsqw_VolFSThresholdDef to dbsqware_rlec;
grant all on tsqw_VolFSThresholdDef to dbsqware_rmaj;

insert into tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (2048, 90, 80);
insert into tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (10240, 92, 85);
insert into tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (20480, 95, 90);
insert into tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (102400000, 99, 97);

tsqw_GenHisto

This table contains : Genral info histo
Desc of tsqw_GenHisto :

create table tsqw_GenHisto (
 pg_sid varchar(80) not null,
 month date not null,
 gather_date date not null,
 size_instance numeric,
 maj_version integer,
 version varchar(30),
 client varchar (60),
 env  varchar(12)
)
;
alter table tsqw_GenHisto add constraint isqw_GenHisto_pk primary key (pg_sid, month);

grant select on tsqw_GenHisto to dbsqware_rlec;
grant all on tsqw_GenHisto to dbsqware_rmaj;

tsqw_AlertFile

This table contains : Alert log location
Desc of tsqw_AlertFile :

/* Alert log location*/
create table tsqw_AlertFile  (
 pg_sid varchar(80) not null,
 gather_date date not null,
 alert_file  varchar(800)
)
;
alter table tsqw_AlertFile add constraint isqw_AlertFile_pk primary key (pg_sid);

grant select on tsqw_AlertFile to dbsqware_rlec;
grant all on tsqw_AlertFile to dbsqware_rmaj;

tsqw_VolBackups

This table contains : VolBackups
Desc of tsqw_VolBackups  :

create table tsqw_VolBackups (
  pg_sid varchar(80)     not null,
  database_name varchar(80) not null,
  beginning timestamp   not null,
  bck_type varchar(50)     not null,
  end_trt timestamp   not null,
  duration char(8)         not null,
  size_bck integer
)
;
alter table tsqw_VolBackups add constraint isqw_VolBackups_pk primary key (pg_sid, database_name, beginning);

grant select on tsqw_VolBackups to dbsqware_rlec;
grant all on tsqw_VolBackups to dbsqware_rmaj;

create table tsqw_VolBackupsExclude (
  pg_sid varchar(80)     not null,
  dbaname varchar(50)     not null,
  comments varchar(50)     not null,
  upd_date date
)
;
alter table tsqw_VolBackupsExclude add constraint isqw_VolBackupsExclude_pk primary key (pg_sid);

grant select on tsqw_VolBackupsExclude to dbsqware_rlec;
grant all on tsqw_VolBackupsExclude to dbsqware_rmaj;

create table tsqw_VolBackupsExcludeGlobalDb (
  database_name varchar(30)     not null,
  dbaname       varchar(50)     not null,
  comments      varchar(50)     not null,
  upd_date      date
)
;
alter table tsqw_VolBackupsExcludeGlobalDb add constraint isqw_VolBackupsExcludeGlobalDb_pk primary key (database_name);

grant select on tsqw_VolBackupsExcludeGlobalDb to dbsqware_rlec;
grant all on tsqw_VolBackupsExcludeGlobalDb to dbsqware_rmaj;

insert into tsqw_VolBackupsExcludeGlobalDb values ('template%','MP','Init dbs',now())
;

create table tsqw_VolBackupsExcludeDb (
  pg_sid varchar(80)     not null,
  database_name varchar(30)     not null,
  dbaname       varchar(50)     not null,
  comments      varchar(50)     not null,
  upd_date      date
)
;
alter table tsqw_VolBackupsExcludeDb add constraint isqw_tsqw_VolBackupsExcludeDb_pk primary key (pg_sid,database_name);

grant select on tsqw_VolBackupsExcludeDb to dbsqware_rlec;
grant all on tsqw_VolBackupsExcludeDb to dbsqware_rmaj;

tsqw_GenInfosLicense

This table contains : for licence infos
Desc of tsqw_GenInfosLicense :

/* for licence infos */
create table tsqw_GenInfosLicense (
 pg_sid varchar(80) not null,
 gather_date date not null,
 host_name varchar(64) not null,
 cpu_count int,
 port_string varchar(255),
 banner varchar(255),
 os_type varchar(255)
)
;

alter table tsqw_GenInfosLicense add constraint isqw_GenInfosLicense_pk primary key (pg_sid);

grant select on tsqw_GenInfosLicense to dbsqware_rlec;
grant all on tsqw_GenInfosLicense to dbsqware_rmaj;

tsqw_Parameters

This table contains : for instance parameters
Desc of tsqw_Parameters :

/* for instance parameters */
create table tsqw_Parameters (
 pg_sid varchar(80) not null,
 gather_date date not null,
 name varchar(255),
 value varchar(255)
)
;

alter table tsqw_Parameters add constraint isqw_Parameters_pk primary key (pg_sid,gather_date,name);

grant select on tsqw_Parameters to dbsqware_rlec;
grant all on tsqw_Parameters to dbsqware_rmaj;

tsqw_VolDb

This table contains : vol databases
Desc of tsqw_VolDb :

/* vol databases */
create table tsqw_VolDb (
 pg_sid varchar(80) not null,
 database_name varchar(80) not null,
 gather_date date not null,
 size_db numeric,
 owner varchar(255),
 encoding varchar(255),
 datcollate varchar(255),
 datctype varchar(255)
)
;
alter table tsqw_VolDb add constraint isqw_VolDb_pk primary key (pg_sid, database_name, gather_date);

grant select on tsqw_VolDb to dbsqware_rlec;
grant all on tsqw_VolDb to dbsqware_rmaj;

tsqw_VolTbs

This table contains : vol tablespaces
Desc of tsqw_VolTbs :

/* vol tablespaces */
create table tsqw_VolTbs (
 pg_sid varchar(80) not null,
 tablespace_name varchar(80) not null,
 gather_date date not null,
 size_tbs numeric,
 owner varchar(255),
 spclocation varchar
)
;
alter table tsqw_VolTbs add constraint isqw_VolTbs_pk primary key (pg_sid, tablespace_name, gather_date);

grant select on tsqw_VolTbs to dbsqware_rlec;
grant all on tsqw_VolTbs to dbsqware_rmaj;

tsqw_VolInstance

This table contains : vol instance
Desc of tsqw_VolInstance :

/* vol instance */
create table tsqw_VolInstance (
 pg_sid varchar(80) not null,
 gather_date date not null,
 size_instance numeric,
 maj_version integer,
 version varchar(30)
)
;
alter table tsqw_VolInstance add constraint isqw_VolInstance_pk primary key (pg_sid, gather_date);

grant select on tsqw_VolInstance to dbsqware_rlec;
grant all on tsqw_VolInstance to dbsqware_rmaj;

tsqw_RepositoryVersion

This table contains : version of SQWareRepository
Desc of tsqw_RepositoryVersion :

/* version of SQWareRepository */
create table tsqw_RepositoryVersion (
        upd_date timestamp not null,
        action varchar(32) not null,
        version  varchar(17) not null,
        comments varchar(255),
constraint isqw_RepositoryVersion_pk primary key (upd_date)
)
;

grant select on tsqw_RepositoryVersion to dbsqware_rlec;
grant all on tsqw_RepositoryVersion to dbsqware_rmaj;

tsqw_RepositoryExclude

This table contains : exclude check indicators
Desc of tsqw_RepositoryExclude :

/* exclude check indicators */
create table tsqw_RepositoryExclude (
 pg_sid varchar(80) not null
)
;
alter table tsqw_RepositoryExclude add constraint isqw_RepositoryExclude_pk primary key (pg_sid);

grant select on tsqw_RepositoryExclude to dbsqware_rlec;
grant all on tsqw_RepositoryExclude to dbsqware_rmaj;

tsqw_EndLife

This table contains : obsolescence management
Desc of tsqw_EndLife :

/* obsolescence management */
create table tsqw_EndLife  (
  maj_version integer not null,
  ReleaseDate date not null,
  Premier date not null,
  Extended date not null
)
;
alter table tsqw_EndLife add constraint isqw_EndLife_pk primary key (maj_version);

grant select on tsqw_EndLife to dbsqware_rlec;
grant all on tsqw_EndLife to dbsqware_rmaj;

-- http://www.postgresql.org/support/versioning/
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (92 , to_date('01/09/2012 ', 'DD/MM/YYYY'), to_date('01/09/2017', 'DD/MM/YYYY'), to_date('01/09/2017', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (91 , to_date('01/09/2011 ', 'DD/MM/YYYY'), to_date('01/09/2016', 'DD/MM/YYYY'), to_date('01/09/2016', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (90 , to_date('01/09/2010 ', 'DD/MM/YYYY'), to_date('01/09/2015', 'DD/MM/YYYY'), to_date('01/09/2015', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (84 , to_date('01/07/2009 ', 'DD/MM/YYYY'), to_date('01/07/2014', 'DD/MM/YYYY'), to_date('01/07/2014', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (83 , to_date('01/02/2008 ', 'DD/MM/YYYY'), to_date('01/02/2013', 'DD/MM/YYYY'), to_date('01/02/2013', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (82 , to_date('01/12/2006 ', 'DD/MM/YYYY'), to_date('01/12/2011', 'DD/MM/YYYY'), to_date('01/12/2011', 'DD/MM/YYYY'));
...

tsqw_VolTables

This table contains : VolTables
Desc of tsqw_VolTables :

/* table size */
create table tsqw_VolTables (
        pg_sid varchar(80) not null,
        gather_date date not null,
        database_name varchar(80) not null,
        table_schema varchar(64) not null,
        table_name varchar(64) not null,
        table_rows numeric,
        total_length numeric,
        heap_length numeric,
        toast_length numeric,
        index_length numeric,
constraint isqw_VolTables_pk primary key (pg_sid,gather_date,database_name,table_schema,table_name)
)
;

grant select on tsqw_VolTables to dbsqware_rlec;
grant all on tsqw_VolTables to dbsqware_rmaj;

insert into tsqw_RepositoryVersion (upd_date, action, version, comments) values ( now(), 'Full install', '2.0.13', 'Install full repo in 2.0.13 version');