SQWareRepository:postgres

From Wiki_dbSQWare
Revision as of 21:40, 18 January 2013 by Mpayan (talk | contribs) (Created page with '{{SQWareRepositoryPresSgbd|PostgreSQL|database|instances}} == Database content == {{SQWareRepositoryTables|tsqw_Repository tsqw_RepositoryOther |<nowiki> Repository </nowiki>| <p…')
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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


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 tsqw_RepositoryOther 

===

This table contains : Repository
Desc of tsqw_Repository tsqw_RepositoryOther 

:


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

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=3891"