Difference between revisions of "SQWareRepository:postgres"

From Wiki_dbSQWare
Jump to: navigation, search
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
 
{{SQWareRepositoryPresSgbd|PostgreSQL|database|instances|pg}}
 
{{SQWareRepositoryPresSgbd|PostgreSQL|database|instances|pg}}
−
== Database content ==
+
== Specific database content for PostgreSQL ==
−
{{SQWareRepositoryTables|tsqw_Repository|
 
−
<nowiki>Instance repository</nowiki>|
 
−
<pre>
 
−
/* 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;
 
−
 
 
−
</pre>
 
−
}}
 
−
 
 
−
{{SQWareRepositoryTables|tsqw_RepositoryOther |
 
−
<nowiki>Instance RepositoryOther</nowiki>|
 
−
<pre>
 
−
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;
 
−
 
 
−
</pre>
 
−
}}
 
  
−
{{SQWareRepositoryTables|tsqw_JobsExec|
+
{{SQWareRepositoryTables|tsqwpg_Parameters|
−
<nowiki>Jobs monitoring</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
/* Jobs monitoring */
+
Field Type
−
create table tsqw_JobsExec
+
dbalias varchar(80)
−
(
+
gather_date date
−
pg_sid varchar(80) not null,
+
name varchar(255)
−
beginning timestamp not null,
+
value varchar(255)
−
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;
 
−
 
 
 
</pre>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_VolFS|
+
{{SQWareRepositoryTables|tsqwpg_VolArchlogs|
−
<nowiki>vol fs</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
/* vol fs */
+
Field Type
−
create table tsqw_VolFS (
+
dbalias varchar(80)
−
pg_sid varchar(80) not null,
+
gather_date datetime
−
gather_date date not null,
+
nb_arch decimal(12,2)
−
host_name varchar (64) not null,
+
size_arch decimal(12,2)
−
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;
 
−
 
 
−
</pre>
 
−
}}
 
−
 
 
−
{{SQWareRepositoryTables|tsqw_VolFSThreshold|
 
−
<nowiki>threshold</nowiki>|
 
−
<pre>
 
−
/* 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);
 
−
 
 
 
</pre>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_GenHisto|
+
{{SQWareRepositoryTables|tsqwpg_VolArchlogsDetail|
−
<nowiki>Genral info histo</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
create table tsqw_GenHisto (
+
Field Type
−
pg_sid varchar(80) not null,
+
dbalias varchar(80)
−
month date not null,
+
gather_date datetime
−
gather_date date not null,
+
filename varchar(255)
−
size_instance numeric,
+
destination varchar(512)
−
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;
 
−
 
 
 
</pre>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_AlertFile|
+
{{SQWareRepositoryTables|tsqwpg_VolArchlogsHourly|
−
<nowiki>Alert log location</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
/* Alert log location*/
+
Field Type
−
create table tsqw_AlertFile  (
+
dbalias varchar(80)
−
pg_sid varchar(80) not null,
+
gather_date datetime
−
gather_date date not null,
+
nb_arch decimal(12,2)
−
alert_file  varchar(800)
+
size_arch decimal(12,2)
−
)
 
−
;
 
−
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;
 
−
 
 
 
</pre>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_VolBackups |
+
{{SQWareRepositoryTables|tsqwpg_VolDb|
−
<nowiki>VolBackups</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
create table tsqw_VolBackups (
+
Field Type
−
  pg_sid varchar(80)    not null,
+
dbalias varchar(80)
−
  database_name varchar(80) not null,
+
database_name varchar(255)
−
  beginning timestamp  not null,
+
gather_date date
−
  bck_type varchar(50)    not null,
+
size_db decimal(12,2)
−
  end_trt timestamp  not null,
+
owner varchar(255)
−
  duration char(8)        not null,
+
encoding varchar(255)
−
  size_bck integer
+
datcollate varchar(255)
−
)
+
datctype varchar(255)
−
;
 
−
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;
 
−
 
 
 
</pre>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_GenInfosLicense|
+
{{SQWareRepositoryTables|tsqwpg_VolTables|
−
<nowiki>for licence infos</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
/* for licence infos */
+
Field Type
−
create table tsqw_GenInfosLicense (
+
dbalias varchar(80)
−
pg_sid varchar(80) not null,
+
gather_date date
−
gather_date date not null,
+
database_name varchar(255)
−
host_name varchar(64) not null,
+
table_schema varchar(64)
−
cpu_count int,
+
table_name varchar(64)
−
port_string varchar(255),
+
table_rows decimal(10,0)
−
banner varchar(255),
+
total_length decimal(12,2)
−
os_type varchar(255)
+
heap_length decimal(12,2)
−
)
+
toast_length decimal(12,2)
−
;
+
index_length decimal(12,2)
−
 
 
−
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;
 
−
 
 
 
</pre>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_Parameters|
+
{{SQWareRepositoryTables|tsqwpg_VolTbs|
−
<nowiki>for instance parameters</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
/* for instance parameters */
+
Field Type
−
create table tsqw_Parameters (
+
dbalias varchar(80)
−
pg_sid varchar(80) not null,
+
tablespace_name varchar(80)
−
gather_date date not null,
+
gather_date date
−
name varchar(255),
+
size_tbs decimal(12,2)
−
value varchar(255)
+
owner varchar(255)
−
)
+
spclocation varchar(800)
−
;
 
−
 
 
−
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;
 
−
 
 
−
</pre>
 
−
}}
 
−
 
 
−
{{SQWareRepositoryTables|tsqw_VolDb|
 
−
<nowiki>vol databases</nowiki>|
 
−
<pre>
 
−
/* 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;
 
−
 
 
−
</pre>
 
−
}}
 
−
 
 
−
{{SQWareRepositoryTables|tsqw_VolTbs|
 
−
<nowiki>vol tablespaces</nowiki>|
 
−
<pre>
 
−
/* 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;
 
−
 
 
−
</pre>
 
−
}}
 
−
 
 
−
{{SQWareRepositoryTables|tsqw_VolInstance|
 
−
<nowiki>vol instance</nowiki>|
 
−
<pre>
 
−
/* 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;
 
−
 
 
−
</pre>
 
−
}}
 
−
 
 
−
{{SQWareRepositoryTables|tsqw_RepositoryVersion|
 
−
<nowiki>version of SQWareRepository</nowiki>|
 
−
<pre>
 
−
/* 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;
 
−
 
 
−
</pre>
 
−
}}
 
−
 
 
−
{{SQWareRepositoryTables|tsqw_RepositoryExclude|
 
−
<nowiki>exclude check indicators</nowiki>|
 
−
<pre>
 
−
/* 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;
 
−
 
 
−
</pre>
 
−
}}
 
−
 
 
−
{{SQWareRepositoryTables|tsqw_EndLife|
 
−
<nowiki>obsolescence management</nowiki>|
 
−
<pre>
 
−
/* 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'));
 
−
...
 
−
 
 
−
</pre>
 
−
}}
 
−
 
 
−
{{SQWareRepositoryTables|tsqw_VolTables|
 
−
<nowiki>VolTables</nowiki>|
 
−
<pre>
 
−
/* 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;
 
−
 
 
 
</pre>
 
</pre>
 
}}
 
}}

Latest revision as of 18:16, 8 August 2015

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 tsqwpg_% or isqwpg_% are specific for PostgreSQL.
Installation guide for SQWareRepository for PostgreSQL

Naming convention

All specific objects for PostgreSQL are named with prefixes:

  • tsqwpg_% : for tables
  • isqwpg_% : for index
  • isqwpg_%_u : for uniques
  • isqwpg_%_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.


Specific database content for PostgreSQL

tsqwpg_Parameters

This table contains : ...
Desc of tsqwpg_Parameters :

Field Type
dbalias varchar(80)
gather_date date
name varchar(255)
value varchar(255)


tsqwpg_VolArchlogs

This table contains : ...
Desc of tsqwpg_VolArchlogs :

Field Type
dbalias varchar(80)
gather_date datetime
nb_arch decimal(12,2)
size_arch decimal(12,2)


tsqwpg_VolArchlogsDetail

This table contains : ...
Desc of tsqwpg_VolArchlogsDetail :

Field Type
dbalias varchar(80)
gather_date datetime
filename varchar(255)
destination varchar(512)


tsqwpg_VolArchlogsHourly

This table contains : ...
Desc of tsqwpg_VolArchlogsHourly :

Field Type
dbalias varchar(80)
gather_date datetime
nb_arch decimal(12,2)
size_arch decimal(12,2)


tsqwpg_VolDb

This table contains : ...
Desc of tsqwpg_VolDb :

Field Type
dbalias varchar(80)
database_name varchar(255)
gather_date date
size_db decimal(12,2)
owner varchar(255)
encoding varchar(255)
datcollate varchar(255)
datctype varchar(255)


tsqwpg_VolTables

This table contains : ...
Desc of tsqwpg_VolTables :

Field Type
dbalias varchar(80)
gather_date date
database_name varchar(255)
table_schema varchar(64)
table_name varchar(64)
table_rows decimal(10,0)
total_length decimal(12,2)
heap_length decimal(12,2)
toast_length decimal(12,2)
index_length decimal(12,2)


tsqwpg_VolTbs

This table contains : ...
Desc of tsqwpg_VolTbs :

Field Type
dbalias varchar(80)
tablespace_name varchar(80)
gather_date date
size_tbs decimal(12,2)
owner varchar(255)
spclocation varchar(800)
Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:postgres&oldid=7268"