SQWareCentral:postgres

From Wiki_dbSQWare
Jump to navigation Jump to search

auto

sqwpg_AdddbSQWareProfile.ksh

Action script $gvsqw_RootexpdbCentral/action/postgres/auto/sqwpg_AdddbSQWareProfile.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/auto_cust/sqwpg_AdddbSQWareProfile.ksh.
Add dbSQWare profile

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
lvsqw_Instance=$(echo "$lvsqw_Instance"|cut -d':' -f1)
ssh -o StrictHostKeyChecking=no -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
if [ -r ~/.profile ]
then
	if [ \$(grep -c 'gvsqw_PgBin' ~/.profile) -eq 0 ]
	then
		cat <<EOFCAT >>~/.profile

#dbSQWare
export gvsqw_Env=''
export gvsqw_PgBin=\\\$HOME/SQWareProduction/postgres/bin
lvsqw_IsTerminal=\\\$(tty 2>&1 >/dev/null;echo \\\$?)
if [ "\\\$lvsqw_IsTerminal" = "0" ] && [ -r \\\$gvsqw_PgBin/../etc/.profile_confort ]
then
	. \\\$gvsqw_PgBin/../etc/.profile_confort
fi

EOFCAT
	fi
elif [ -r ~/.bash_profile ]
then
	if [ \$(grep -c 'gvsqw_PgBin' ~/.bash_profile) -eq 0 ]
	then
		cat <<EOFCAT >>~/.bash_profile

#dbSQWare
export gvsqw_Env=''
export gvsqw_PgBin=\\\$HOME/SQWareProduction/postgres/bin
lvsqw_IsTerminal=\\\$(tty 2>&1 >/dev/null;echo \\\$?)
if [ "\\\$lvsqw_IsTerminal" = "0" ] && [ -r \\\$gvsqw_PgBin/../etc/.profile_confort ]
then
	. \\\$gvsqw_PgBin/../etc/.profile_confort
fi

EOFCAT
	fi
fi
EOFSSH
done <$gvsqw_TmpFile.lst

}


sqwpg_DeplScripts.ksh

Action script $gvsqw_RootexpdbCentral/action/postgres/auto/sqwpg_DeplScripts.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/auto_cust/sqwpg_DeplScripts.ksh.
SQWareProduction deployment for Postgres

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p $gvsqw_RootSQWareProductionPostgres/$gvsqw_VersDeplSQWareProductionPostgres
cd $gvsqw_RootSQWareProductionPostgres
rm -f $gvsqw_VersSQWareProductionPostgres
ln -s $gvsqw_VersDeplSQWareProductionPostgres $gvsqw_VersSQWareProductionPostgres
EOFSSH
rsync -aHv $gvsqw_RsyncPathPostgres --delete $gvsqw_RootSQWareProduction/postgres/$gvsqw_VersDeplSQWareProductionPostgres $lvsqw_UserName@$lvsqw_HostName:$gvsqw_RootSQWareProductionPostgres/.
done <$gvsqw_TmpFile.lst
}


sqwpg_GatherIndicatorsDist.ksh

Action script $gvsqw_RootexpdbCentral/action/postgres/auto/sqwpg_GatherIndicatorsDist.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/auto_cust/sqwpg_GatherIndicatorsDist.ksh.
Postgres gather indicators in distant mode

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
$gvsqw_PgBin/sqwpg_GatherIndicators.ksh -I "$lvsqw_Instance" -Dist &
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}


sqwpg_GatherIndicators.ksh

Action script $gvsqw_RootexpdbCentral/action/postgres/auto/sqwpg_GatherIndicators.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/auto_cust/sqwpg_GatherIndicators.ksh.
Postgres gather indicators

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_PgBin/../tools/sqwpg_SetEnv.ksh $lvsqw_Instance
\$gvsqw_PgBin/sqwpg_RunJob.ksh -I $lvsqw_Instance -A IndicDba
EOFSSH
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}


sqwpg_Gen_dbSQWare.ksh

Action script $gvsqw_RootexpdbCentral/action/postgres/auto/sqwpg_Gen_dbSQWare.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/auto_cust/sqwpg_Gen_dbSQWare.ksh.
generate dbSQWare files

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
lvsqw_Instance=$(echo "$lvsqw_Instance"|cut -d':' -f1)
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
. \$gvsqw_PgBin/../tools/sqwpg_SetEnv.ksh $lvsqw_Instance
if [ "\$PG_SID" != "$lvsqw_Instance" ]
then
	export PG_SID="$lvsqw_Instance"
fi
\$gvsqw_PgBin/../tools/sqwpg_GenerateCreateInstance.ksh -dbsOnly <<EOF
y
EOF
EOFSSH
done <$gvsqw_TmpFile.lst
}


sqwpg_GenSshKeysInstance.ksh

Action script $gvsqw_RootexpdbCentral/action/postgres/auto/sqwpg_GenSshKeysInstance.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/auto_cust/sqwpg_GenSshKeysInstance.ksh.
ssh keys deployment for PostgreSQL

Action: 

lfsqw_Action()
{

echo "#########################################################################"
echo "#Create/Retrieve key for SQWareCentral"
mkdir -p $HOME/tmp
if [ -r $HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l $HOME/.ssh/id_dsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_dsa.pub)
elif [ -r $HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l $HOME/.ssh/id_rsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
else
	ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
fi
#Retrieve additionnal keys if found (your windows key for exemple)
if [ -r $HOME/.ssh/id_dbSQWare.pub ]
then
	echo "Id dbSQWare found:"
	ls -l $HOME/.ssh/id_dbSQWare.pub
	lvsqw_AdditionnalKey=$(cat $HOME/.ssh/id_dbSQWare.pub)
else
	lvsqw_AdditionnalKey=""
fi
chmod go-w $HOME
touch $HOME/.ssh/authorized_keys
chmod 700 $HOME/.ssh
chmod 600 $HOME/.ssh/authorized_keys
echo "${c_menu}#########################################################################"
echo "#create key if not found and put SQWareCentral key for distant users"
echo "Enter password when necessary${c_normal}"
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo ""
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh -o StrictHostKeyChecking=no $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p \$HOME/tmp
if [ -r \$HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l \$HOME/.ssh/id_dsa.pub
	cat \$HOME/.ssh/id_dsa.pub
elif [ -r \$HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l \$HOME/.ssh/id_rsa.pub
	cat \$HOME/.ssh/id_rsa.pub
else
	ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
	cat \$HOME/.ssh/id_rsa.pub
fi
chmod go-w \$HOME
echo "$lvsqw_CentralKey" >>\$HOME/.ssh/authorized_keys
echo "$lvsqw_AdditionnalKey" >>\$HOME/.ssh/authorized_keys
chmod 700 \$HOME/.ssh
chmod 600 \$HOME/.ssh/authorized_keys
EOFSSH

done <$gvsqw_TmpFile.lst
}


sqwpg_GenSshKeys.ksh

Action script $gvsqw_RootexpdbCentral/action/postgres/auto/sqwpg_GenSshKeys.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/auto_cust/sqwpg_GenSshKeys.ksh.
ssh keys deployment for PostgreSQL

Action: 

lfsqw_Action()
{

echo "#########################################################################"
echo "#Create/Retrieve key for SQWareCentral"
mkdir -p $HOME/tmp
if [ -r $HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l $HOME/.ssh/id_dsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_dsa.pub)
elif [ -r $HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l $HOME/.ssh/id_rsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
else
	ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
fi
#Retrieve additionnal keys if found (your windows key for exemple)
if [ -r $HOME/.ssh/id_dbSQWare.pub ]
then
	echo "Id dbSQWare found:"
	ls -l $HOME/.ssh/id_dbSQWare.pub
	lvsqw_AdditionnalKey=$(cat $HOME/.ssh/id_dbSQWare.pub)
else
	lvsqw_AdditionnalKey=""
fi
chmod go-w $HOME
touch $HOME/.ssh/authorized_keys
chmod 700 $HOME/.ssh
chmod 600 $HOME/.ssh/authorized_keys
echo "${c_menu}#########################################################################"
echo "#create key if not found and put SQWareCentral key for distant users"
echo "Enter password when necessary${c_normal}"
while read lvsqw_HostName lvsqw_UserName
do
echo ""
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh -o StrictHostKeyChecking=no $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p \$HOME/tmp
if [ -r \$HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l \$HOME/.ssh/id_dsa.pub
	cat \$HOME/.ssh/id_dsa.pub
elif [ -r \$HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l \$HOME/.ssh/id_rsa.pub
	cat \$HOME/.ssh/id_rsa.pub
else
	ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
	cat \$HOME/.ssh/id_rsa.pub
fi
chmod go-w \$HOME
echo "$lvsqw_CentralKey" >>\$HOME/.ssh/authorized_keys
echo "$lvsqw_AdditionnalKey" >>\$HOME/.ssh/authorized_keys
chmod 700 \$HOME/.ssh
chmod 600 \$HOME/.ssh/authorized_keys
EOFSSH

done <$gvsqw_TmpFile.lst
}




etc

CrontabRef.cfg

Configuration file $gvsqw_RootexpdbCentral/action/postgres/etc/CrontabRef.cfg:
It contains the operating variables of SQWareCentral for postgres.
If you want to overload default variables,
put in $gvsqw_RootexpdbCentral/action/postgres/etc_cust/CrontabRef.cfg
your modifications (only variables you want to modify).

File content: 







Variable explanation:










sqwc_DbRefVar.cfg


Configuration file $gvsqw_RootexpdbCentral/action/postgres/etc/sqwc_DbRefVar.cfg:

It contains the operating variables of SQWareCentral for postgres.

If you want to overload default variables,

put in $gvsqw_RootexpdbCentral/action/postgres/etc_cust/sqwc_DbRefVar.cfg

your modifications (only variables you want to modify).



File content:



export gvsqw_DbRefType_postgres='postgres'
export gvsqw_DbRefPort_postgres='InstancePortForPostgres'
export gvsqw_DbRefInstance_postgres='InstanceNameForPostgres'
export gvsqw_DbRefDatabase_postgres='DatabaseInstanceNameForPostgres'
export gvsqw_DbRefUser_postgres='ConnexionUserForPostgres'
export gvsqw_DbRefPass_postgres='ConnexionPassForPostgres'







Variable explanation:



gvsqw_DbRefDatabase_postgres => 
gvsqw_DbRefInstance_postgres => 
gvsqw_DbRefPass_postgres => 
gvsqw_DbRefPort_postgres => 
gvsqw_DbRefType_postgres => 
gvsqw_DbRefUser_postgres => 








sqwc_GlobalVar.cfg


Configuration file $gvsqw_RootexpdbCentral/action/postgres/etc/sqwc_GlobalVar.cfg:

It contains the operating variables of SQWareCentral for postgres.

If you want to overload default variables,

put in $gvsqw_RootexpdbCentral/action/postgres/etc_cust/sqwc_GlobalVar.cfg

your modifications (only variables you want to modify).



File content:



export gvsqw_PGPASSFILE="$gvsqw_RootexpdbCentral/action/postgres/etc_cust/.pgpass"
export gvsqw_PostgresErrorMsg="^ERROR: |^FAILED: |^ERREUR: |^FATAL: |^psql: ERROR: |^psql: FAILED: |^psql: ERREUR: |^psql: FATAL: "
export gvsqw_Mail_postgres='default@dbsqware.com'
export gvsqw_MailMsg_postgres='TrtPostgresAuto'
export gvsqw_UserUniquePostgres='dbsqware'
export gvsqw_RootSQWareProductionPostgres="~$gvsqw_UserUniquePostgres/SQWareProduction/postgres"
export gvsqw_VersSQWareProductionPostgres='prod'
export gvsqw_VersDeplSQWareProductionPostgres='v3.1'







Variable explanation:



gvsqw_MailMsg_postgres => 
gvsqw_Mail_postgres => 
gvsqw_PGPASSFILE => 
gvsqw_PostgresErrorMsg => 
gvsqw_RootSQWareProductionPostgres => 
gvsqw_UserUniquePostgres => 
gvsqw_VersDeplSQWareProductionPostgres => 
gvsqw_VersSQWareProductionPostgres => 








.profile_confort


File to put aliases $gvsqw_RootexpdbCentral/action/postgres/etc/.profile_confort:

Contains confort aliases of SQWareCentral for RDBMS postgres.



Alias explanation:



	  act_pg => 'cd $gvsqw_RootexpdbCentral/action/postgres/prod;ls -al'
	  etc_pg => 'cd $gvsqw_RootexpdbCentral/action/postgres/etc;ls -al'
	  lst_pg => 'cd $gvsqw_RootexpdbCentral/action/postgres/lst;ls -al'
	  auto_pg => 'cd $gvsqw_RootexpdbCentral/action/postgres/auto;ls -al'
	  cetc_pg => 'cd $gvsqw_RootexpdbCentral/action/postgres/etc_cust;ls -al'
	  clst_pg => 'cd $gvsqw_RootexpdbCentral/action/postgres/lst_cust;ls -al'
	  cauto_pg => 'cd $gvsqw_RootexpdbCentral/action/postgres/auto_cust;ls -al'
	  depl_pg => '$gvsqw_RootexpdbCentral/menu/sqwc_MenuAction.ksh postgres sqwpg_DeplScripts.ksh sqwpg_GenLstUnique.ksh'
	  repind_pg => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh postgres sqwpg_GatherIndicators.ksh sqwpg_GenLstRepind.ksh NOLOG'
	  repind_mys_dist => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh postgres sqwpg_GatherIndicatorsDist.ksh sqwpg_GenLstRepindDist.ksh NOLOG'
	  gen_pg => '$gvsqw_RootexpdbCentral/action/postgres/lst/sqwc_GenRefPostgres.ksh'
	  menu_pg => '$gvsqw_RootexpdbCentral/menu/sqwc_MenuAction.ksh postgres'











lst


sqwc_Extract_CMDB_Postgres.ksh


List generator $gvsqw_RootexpdbCentral/action/postgres/lst/sqwc_Extract_CMDB_Postgres.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/lst_cust/sqwc_Extract_CMDB_Postgres.ksh.




Query used:



select concat('PG;',ref.pg_sid,';',coalesce(hst.host_name,'N.A'),';',
from tsqw_Repository ref left outer join tsqw_GenHisto hist
        on (ref.pg_sid = hist.pg_sid and hist.month = date_trunc('month',now()))
order by 1








sqwc_GenRefDbPostgres.ksh


List generator $gvsqw_RootexpdbCentral/action/postgres/lst/sqwc_GenRefDbPostgres.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/lst_cust/sqwc_GenRefDbPostgres.ksh.




Query used:



select concat('PG;',ref.pg_sid,';',vol.database_name,';',ref.env,';',
from tsqw_Repository ref, tsqw_GenHisto hist, tsqw_VolInstance inst, tsqw_VolDb vol
where hist.month = date_trunc('month',now())
    and hist.pg_sid = ref.pg_sid
    and hist.pg_sid = vol.pg_sid
    and hist.gather_date = vol.gather_date
    and hist.pg_sid = inst.pg_sid
    and hist.gather_date = inst.gather_date
order by 1








sqwc_GenRefPostgres.ksh


List generator $gvsqw_RootexpdbCentral/action/postgres/lst/sqwc_GenRefPostgres.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/lst_cust/sqwc_GenRefPostgres.ksh.




Query used:



select cast(virt_host_name as char(22)) "Virthost",
from tsqw_Repository
select cast(virt_host_name as char(22)) "Virthost",
from tsqw_RepositoryOther
order by 6,2,1,3,4;








sqwpg_GenLstInstanceDist.ksh


List generator $gvsqw_RootexpdbCentral/action/postgres/lst/sqwpg_GenLstInstanceDist.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/lst_cust/sqwpg_GenLstInstanceDist.ksh.

Generation of the list of Postgres instances for distant connection (tsqw_Repository)



Query used:



select virt_host_name, username, pg_sid
from tsqw_Repository
where status  in ('DIST','MIX')
order by virt_host_name, username








sqwpg_GenLstInstance.ksh


List generator $gvsqw_RootexpdbCentral/action/postgres/lst/sqwpg_GenLstInstance.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/lst_cust/sqwpg_GenLstInstance.ksh.

Generation of the list of Postgres instances (tsqw_Repository)



Query used:



select virt_host_name, username, pg_sid
from tsqw_Repository
where status  in ('ON','MIX')
order by virt_host_name, username








sqwpg_GenLstRepindDist.ksh


List generator $gvsqw_RootexpdbCentral/action/postgres/lst/sqwpg_GenLstRepindDist.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/lst_cust/sqwpg_GenLstRepindDist.ksh.

Generation of the list for reprise of indicators for distant connection (tsqw_Repository/tsqw_GenHisto)



Query used:



select virt_host_name, username, pg_sid
from tsqw_Repository ref
where ref.status in ('DIST','MIX')
and not exists ( select 1 from tsqw_GenHisto inf
where inf.gather_date = date(now())
and ref.pg_sid = inf.pg_sid)








sqwpg_GenLstRepind.ksh


List generator $gvsqw_RootexpdbCentral/action/postgres/lst/sqwpg_GenLstRepind.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/lst_cust/sqwpg_GenLstRepind.ksh.

Generation of the list for reprise of indicators (tsqw_Repository/tsqw_GenHisto)



Query used:



select virt_host_name, username, pg_sid
from tsqw_Repository ref
where ref.status in ('ON','MIX')
and not exists ( select 1 from tsqw_GenHisto inf
where inf.gather_date = date(now())
and ref.pg_sid = inf.pg_sid)








sqwpg_GenLstUnique.ksh


List generator $gvsqw_RootexpdbCentral/action/postgres/lst/sqwpg_GenLstUnique.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/postgres/lst_cust/sqwpg_GenLstUnique.ksh.

Generation of the user unique list (tsqw_Repository)



Query used:



select distinct host_name, '$gvsqw_UserUniquePostgres'
from tsqw_Repository
where status != 'OFF'
select distinct host_name, '$gvsqw_UserUniquePostgres'
from tsqw_RepositoryOther
order by host_name

















Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareCentral:postgres&oldid=3817"