#!/bin/sh
DOMAIN="<nom_du_domaine>"
DATABASE="postgres"
USERNAME="<userpostgres>"
ECO=<nom_ecosystem>
SERVER=<instance_de_l_ecosystem>
clear
echo "Entrer le nom de l ecosystem (ex:$ECO)"
read $ECO
echo "Entrer le nom de l instance (ex:$SERVER)"
read $SERVER
echo "-- Infos sur l instance :"
echo " "
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/config/$SERVER "
echo " "
echo "-- Liste des rôle dynamiques : "
echo " "
echo "vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles "
echo " "
vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles
echo " "
echo "-- Liste des rôle statiques : "
echo " "
echo "vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/static-roles "
echo " "
vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/static-roles
echo "-- Informations sur un compte spécifique (dont les SQL de creation et révocation) : "
echo " "
echo "Entrez le nom du role:"
read ROLE
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles/$ROLE"
echo " "
echo "-- Récupérer les creds d un utilisateur dynamique: "
echo " "
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/creds/$ROLE"
echo " "
echo "-- Récupérer les creds d un utilisateur statique: "
echo " "
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/static-creds/$ROLE"
echo " "
echo "-- Créer les creds d un utilisateur: "
echo " "
echo "vault write -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles/${ROLE} db_name=${SERVER} creation_statements=@create_dyna_role_OWN.sql revocation_statements=@drop_dyna_role_OWN.sql default_ttl=${ROTATION} max_ttl=${ROTATION} "
echo " "
echo "-- Révoquer un compte avant son TTL : "
echo " "
echo "lease_id database/postgres/$SERVER/creds/OWN_IBMCLOUDDB_SCHEMA1/qdqsdqsdqsdsdqsdqsd.mxxxx"
echo " "
echo "vault lease revoke -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/creds/$ROLE/qdqsdqsdqsdsdqsdqsd.mxxx "
echo " "
-
-
[ora2pg script] Data and metadata export (multiple schemas)
#!/bin/sh
#-------------------------------------------------------------------------------
#
# Created by joguess the Oracle database admin, version 2022
#
#-------------------------------------------------------------------------------
#data export
EXPORT_TYPE="COPY"
SCHEMA="TITI TOTO TATA"
namespace="."
for etype in $(echo $EXPORT_TYPE | tr " " "n")
do
for eschema in $(echo $SCHEMA | tr " " "n")
do
ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
ltype=`echo $ltype | sed 's/y$/ie/'`
echo "Running: ora2pg -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/data -c $namespace/config/ora2pg.conf"
time ora2pg -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/data -c $namespace/config/ora2pg.conf -j 8 -J 3 -L 60000
ret=`grep "Nothing found" $namespace/data/$ltype.sql 2> /dev/null`
if [ ! -z "$ret" ]; then
rm $namespace/data/$ltype.sql
fi
done
done
echo
echo
echo "Extraction effectuee sous :"
echo
echo " $namespace/data"
echo
exit 0
____________________________________
#!/bin/sh
#-------------------------------------------------------------------------------
#
# Created by joguess the Oracle database admin, version 2022
#
#-------------------------------------------------------------------------------
#metadata export
EXPORT_TYPE="SEQUENCE TABLE PACKAGE VIEW GRANT TRIGGER FUNCTION PROCEDURE PARTITION TYPE MVIEW SYNONYM"
SCHEMA="TITI TOTO TATA"
namespace="."
for etype in $(echo $EXPORT_TYPE | tr " " "n")
do
for eschema in $(echo $SCHEMA | tr " " "n")
do
ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
ltype=`echo $ltype | sed 's/y$/ie/'`
echo "Running: ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt"
time ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt
echo "Running: ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt"
time ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt
echo "Running ora2pg -t SHOW_COLUMN -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/columns_$eschema.txt"
time ora2pg -t SHOW_COLUMN -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/columns_$eschema.txt
echo "Running: ora2pg -t SHOW_REPORT -n $eschema -c $namespace/config/ora2pg.conf --estimate_cost --cost_unit_value 10 --dump_as_html > $namespace/reports/assessment/assessment_$eschema.html"
time ora2pg -t SHOW_REPORT -n $eschema -c $namespace/config/ora2pg.conf --estimate_cost --cost_unit_value 10 --dump_as_html > $namespace/reports/assessment/assessment_$eschema.html
echo "Running: ora2pg -p -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf"
time ora2pg -p -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf
ret=`grep "Nothing found" $namespace/schema/$ltype.sql 2> /dev/null`
if [ ! -z "$ret" ]; then
rm $namespace/schema/$ltype.sql
fi
done
done
echo
echo
echo "Extraction effectuee sous :"
echo
echo " $namespace/schema"
echo
exit 0 -
Configuration application cloud postgres
USERNAME :: postgres://<instance>/<role>/user
PASSWORD ::postgres://<instance>/<role>/password
URL :: jdbc:postgresql://<instance>.<domain>:<port>/<dbname>
-
Changer le owner des tables
#Change owner of tables qui sont à postgres
for tbl in `psql -qAt -c "select tablename from pg_tables where tableowner = 'postgres' and schemaname='SCHEMA';" dbname` ;
do psql -c "alter table SCHEMA."$tbl" owner to NEW_OWNER" dbname ; done -
Prendre une radio echographie
Radiologie Antibes: centres d'imagerie médicale d'Antibes (radiologie-antibes.fr)
-
Postgres kill session
# kill sessions
# trouver la sessions
select * from pg_stat_activity;
select pg_terminate_backend(pid)
from pg_stat_activity
where pid = 'PID'; -
un peu de compta
règle de calcul pour l'octroit d'un prêt immo pour une société avec bilan comptable :
- moyenne de l'excédant brut d'exploitation des deux dernières annéesrègle de calcul pour l'octroit d'un prêt immo pour une micro sans bilan comptable :
- 66 % du CA de la dernière année.
-
POST MIGRATION
# changer type en booleen
alter table "<your_table>"
alter column "<your_column>"
set data type boolean
using case
when "<your_column>" = '1' then true
when "<your_column>" = '0' then false
else null
end;alter database <dbname> set search_path = "$user", public, <schema> ;
# post migration pour un schema
for tablename in $(psql -U postgres -h <hostname> -d <dbname> -t -c "select table_name as _table from information_schema.tables t where t.table_schema='<schema>' order by _table asc");
do
echo $tablename
psql -U postgres -h <hostname> -d <dbname> -c "analyze ${tablename};"
psql -U postgres -h <hostname> -d <dbname> -c "select count(*) ${tablename} from ${tablename};" >> rowcount.log
done# post migration pour plusieurs schemas
#!/bin/sh
schema="('sche1','sche2','sche3','...')"
namespace="."
dbname="dbname"
host="hostname"
for tablename in $(psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc");
do
echo "psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc""
echo $tablename
psql -U postgres -h ${host} -d ${dbname} -c "analyze ${tablename};"
psql -U postgres -h ${host} -d ${dbname} -c "select count(*) as nr_rows, '${tablename}' as tablename from ${tablename};" >> logs/rowcount.log
done
#update sequ1- Récupérer la séquence sur Oracle :
SELECT sequence_name, last_number FROM user_sequences;
2- Mettre à jour la séquence sur Postgres :
SELECT setval('<nom_sequence',<last_number> , true); -
[ora2pg] Assessment
To be able to say “Yeah, we migrate to PostgreSQL”, we have to be confident by studying the technical aspects of it.
For that, we assess the feasibility of the migration from oracle to postgres.
The assessments consist in validating the :
- Metadata structure and datatypes
- Data consistency
- Data integrity
For this, we use the free tool called ora2Pg.