SQWareRepository:postgres

From Wiki_dbSQWare
Revision as of 20:42, 18 January 2013 by Expdbtools (talk | contribs) (→‎Database content)
Jump to: navigation, search

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


Nuvola apps important.png
Warning:
Do not change the standard tables of the tool. For your custom objects, use another name convention than dbSQWare.

This will facilitate version upgrades.


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 tsqw_VolFSThresholdDef tsqw_GenHisto 

===

This table contains : threshold
Desc of tsqw_VolFSThreshold tsqw_VolFSThresholdDef tsqw_GenHisto 

:


/* 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);

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 tsqw_VolBackups tsqw_VolBackupsExclude tsqw_VolBackupsExcludeGlobalDb tsqw_VolBackupsExcludeDb 

===

This table contains : Alert log location
Desc of tsqw_AlertFile tsqw_VolBackups tsqw_VolBackupsExclude tsqw_VolBackupsExcludeGlobalDb tsqw_VolBackupsExcludeDb 

:


/* 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;

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'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (81 , to_date('01/11/2005 ', 'DD/MM/YYYY'), to_date('01/11/2010', 'DD/MM/YYYY'), to_date('01/11/2010', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (80 , to_date('01/01/2005 ', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (74 , to_date('01/11/2003 ', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (73 , to_date('01/11/2002 ', 'DD/MM/YYYY'), to_date('01/11/2007', 'DD/MM/YYYY'), to_date('01/11/2007', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (72 , to_date('01/02/2002 ', 'DD/MM/YYYY'), to_date('01/02/2007', 'DD/MM/YYYY'), to_date('01/02/2007', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (71 , to_date('01/04/2001 ', 'DD/MM/YYYY'), to_date('01/04/2006', 'DD/MM/YYYY'), to_date('01/04/2006', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (70 , to_date('01/05/2000 ', 'DD/MM/YYYY'), to_date('01/05/2005', 'DD/MM/YYYY'), to_date('01/05/2005', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (65 , to_date('01/06/1999 ', 'DD/MM/YYYY'), to_date('01/06/2004', 'DD/MM/YYYY'), to_date('01/06/2004', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (64 , to_date('01/10/1998 ', 'DD/MM/YYYY'), to_date('01/10/2003', 'DD/MM/YYYY'), to_date('01/10/2003', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (63 , to_date('01/03/1998 ', 'DD/MM/YYYY'), to_date('01/03/2003', 'DD/MM/YYYY'), to_date('01/03/2003', 'DD/MM/YYYY'));


=== tsqw_VolTables 

===

This table contains : insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (80 , to_date('001/2005 ', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'));
Desc of tsqw_VolTables 

:


insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (80 , to_date('01/01/2005 ', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (74 , to_date('01/11/2003 ', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'), to_date('01/10/2010', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (73 , to_date('01/11/2002 ', 'DD/MM/YYYY'), to_date('01/11/2007', 'DD/MM/YYYY'), to_date('01/11/2007', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (72 , to_date('01/02/2002 ', 'DD/MM/YYYY'), to_date('01/02/2007', 'DD/MM/YYYY'), to_date('01/02/2007', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (71 , to_date('01/04/2001 ', 'DD/MM/YYYY'), to_date('01/04/2006', 'DD/MM/YYYY'), to_date('01/04/2006', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (70 , to_date('01/05/2000 ', 'DD/MM/YYYY'), to_date('01/05/2005', 'DD/MM/YYYY'), to_date('01/05/2005', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (65 , to_date('01/06/1999 ', 'DD/MM/YYYY'), to_date('01/06/2004', 'DD/MM/YYYY'), to_date('01/06/2004', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (64 , to_date('01/10/1998 ', 'DD/MM/YYYY'), to_date('01/10/2003', 'DD/MM/YYYY'), to_date('01/10/2003', 'DD/MM/YYYY'));
insert into tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values (63 , to_date('01/03/1998 ', 'DD/MM/YYYY'), to_date('01/03/2003', 'DD/MM/YYYY'), to_date('01/03/2003', 'DD/MM/YYYY'));

/* 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');
Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:postgres&oldid=3892"