/tech/postgres/dba/jjy setDB.bash
#! /bin/bash
##################################################################
## Program : setDB.bash #
## Description : Set environement variables according to #
## database #
##################################################################
#
#
#set -xv
# CONSTANTS
PRGNAME=$(basename $0 2> /dev/null)
if [ $? -ne 0 ]; then
PRGNAME="Unknown"
fi
PRGPATH=$(dirname $0 2> /dev/null)
if [ "${PRGPATH}x" == "x" ] || [ ! -e "${PRGPATH}" ]; then
PRGPATH="/tech/postgres/bin"
fi
# INCLUDE
if [ -e ${PRGPATH}/__pg.include ]; then
. ${PRGPATH}/__pg.include
elif [ -e /tech/postgres/bin/__pg.include ]; then
. /tech/postgres/bin/__pg.include
fi
# Check environement
if [ $# -ne 1 ]; then
SID=""
DBSet
printf "nUsage: dbset SIDnn"
else
SID=$1
DBSet ${SID}
echo "PGSID : "${PGSID}" (port ${PGPORT}) STATUS : "${PGSTATUS,,}
echo "PGVERSION : "${PGVERSION}
echo "PGHOME : "${PGHOME}
echo "PGDATA : "${PGDATA}
fi
RDBMS Expertise - Page 4
-
[Postgres] SET DB ENV
-
[Postgres] AUDIT
-
Nombre de ligne dans les tables postgres
SELECT reltuples::bigint AS estimate FROM pg_class WHERE oid = 'myschema.mytable'::regclass; -
[Postgres] check_postgres
https://wiki.postgresql.org/wiki/Index_Maintenance
-
[Postgres] Create partitionned tables and rotate
Script to create partitionned tables and rotate it on one year
-
PostgreSQL performance Turing Indexes
https://www.timescale.com/learn/postgresql-performance-tuning-optimizing-database-indexes
-
[Postgres] Fonction pour compter les lignes
Pratique lors d'une migration pour vérifier que toutes les lignes sont bien là !
-
[Postgres] Mesure the bloat
intéressant mon cher watson
-
Convertisseur EUR BTC
convertisseur euro btc
-
Connexions aux db du cloud
#!/bin/sh
#-------------------------------------------------------------------------------
#
# Script to connect to IBM Cloud DB, version 2.0
# Author : JJY
#
#-------------------------------------------------------------------------------
#set -vx
##############
#INSTANCIATION
##############
DOMAIN="<domain>"
DATABASE="postgres"
USERNAME="<user_dba>"
while true
do
DATABASE="postgres"
clear
echo "CONNEXION AUX BASES DU CLOUD "
echo " "
echo "CONNEXION A l ECOSYSTEM : <ecosystem>"
echo " "
echo "1 - DEV(dbname) - serveur : <instance> connexion DBA"
echo " "
echo "2 - DEV(dbname) - serveur : <instance> connexion dynamique applicative"
echo " "
echo "3 - QUAL(dbname) - serveur : <instance> connexion DBA"
echo " "
echo "4 - QUAL(dbname) - serveur : <instance> connexion dynamique applicative"
echo " "
echo "0 - TOOLBOX VAULT "
echo " "
echo " Faites votre choix (0 a 4) :"
echo " Taper Q pour quitter."
echo " "
read var
case $var in
1)
echo "Connexion a la base, veuillez patienter..."
export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME | grep password | awk '{print $2}')
export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
;;
2)
export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
echo "Liste des roles:"
vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
echo " "
echo "A quel role souhaitez-vous vous connecter:"
read ROLE
clear
echo "Connexion a la base en mode dynamique, veuillez patienter..."
var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
username=$(echo "$var" | grep username | awk '{print $2}');
password=$(echo "$var" | grep password | awk '{print $2}');
echo "User is : $username"
echo "Password is : $password"
echo " Tapez entrée pour continuer."
read bidon
export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
;;
3)
echo "Connexion a la base, veuillez patienter..."
export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME | grep password | awk '{print $2}')
export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
;;
4)
export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
echo "Liste des roles:"
vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
echo " "
echo "A quel role souhaitez-vous vous connecter:"
read ROLE
clear
echo "Connexion a la base en mode dynamique, veuillez patienter..."
var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
username=$(echo "$var" | grep username | awk '{print $2}');
password=$(echo "$var" | grep password | awk '{print $2}');
echo "User is : $username"
echo "Password is : $password"
echo " Tapez entrée pour continuer."
read bidon
export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
;;
0)
./toolbox.sh
echo " Tapez entrée pour continuer."
read bidon
;;
Q)
echo "Merci d'avoir utiliser ce programme."
exit
;;
*)
echo "Merci de renseignez un chiffre entre 0 et 4."
echo " Tapez entrée pour continuer."
read bidon
;;
esac
done
exit