|
|
| 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|
| |
| − | <nowiki>Jobs monitoring</nowiki>|
| |
| − | <pre>
| |
| − | /* 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;
| |
| − | | |
| − | </pre>
| |
| − | }}
| |
| − | | |
| − | {{SQWareRepositoryTables|tsqw_VolFS|
| |
| − | <nowiki>vol fs</nowiki>|
| |
| − | <pre>
| |
| − | /* 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;
| |
| − | | |
| − | </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>
| |
| − | }}
| |
| − | | |
| − | {{SQWareRepositoryTables|tsqw_GenHisto|
| |
| − | <nowiki>Genral info histo</nowiki>|
| |
| − | <pre>
| |
| − | 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;
| |
| − | | |
| − | </pre>
| |
| − | }}
| |
| − | | |
| − | {{SQWareRepositoryTables|tsqw_AlertFile|
| |
| − | <nowiki>Alert log location</nowiki>|
| |
| − | <pre>
| |
| − | /* 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;
| |
| − | | |
| − | </pre>
| |
| − | }}
| |
| − | | |
| − | {{SQWareRepositoryTables|tsqw_VolBackups |
| |
| − | <nowiki>VolBackups</nowiki>|
| |
| − | <pre>
| |
| − | 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;
| |
| − | | |
| − | </pre>
| |
| − | }}
| |
| − | | |
| − | {{SQWareRepositoryTables|tsqw_GenInfosLicense|
| |
| − | <nowiki>for licence infos</nowiki>|
| |
| − | <pre>
| |
| − | /* 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;
| |
| − | | |
| − | </pre>
| |
| − | }}
| |
| − | | |
| − | {{SQWareRepositoryTables|tsqw_Parameters|
| |
| − | <nowiki>for instance parameters</nowiki>|
| |
| − | <pre>
| |
| − | /* 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;
| |
| − | | |
| − | </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>
| |
| − | }}
| |
