(2 intermediate revisions by the same user not shown) Line 1:
Line 1: {{SQWareRepositoryPresSgbd|PostgreSQL|database|instances}}
{{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>
}}
}}
Presentation It is the repository module, and stores rdbms indicators for all RDBMS. 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
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 :
...
Field Type
dbalias varchar(80)
gather_date date
name varchar(255)
value varchar(255)
tsqwpg_VolArchlogs This table contains :
...
Field Type
dbalias varchar(80)
gather_date datetime
nb_arch decimal(12,2)
size_arch decimal(12,2)
tsqwpg_VolArchlogsDetail This table contains :
...
Field Type
dbalias varchar(80)
gather_date datetime
filename varchar(255)
destination varchar(512)
tsqwpg_VolArchlogsHourly This table contains :
...
Field Type
dbalias varchar(80)
gather_date datetime
nb_arch decimal(12,2)
size_arch decimal(12,2)
tsqwpg_VolDb This table contains :
...
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 :
...
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 :
...
Field Type
dbalias varchar(80)
tablespace_name varchar(80)
gather_date date
size_tbs decimal(12,2)
owner varchar(255)
spclocation varchar(800)
