Difference between Clustered and Non-clustered index
link Difference between Clustered and Non-clustered index (guru99.com)
Expertise en SGBDR - Page 6
-
-
Modify the owner of the tables and the sequences (post migration)
Modify the owner of the tables and the sequences (post migration)
#change table owner
for tbl in `psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -qAt -c "select tablename from pg_tables where schemaname = '<your_schema>';" <database>` ; do psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -c "alter table "$tbl" owner to <your_role>" <database>; done
#change sequence owner
for tbl in `psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = '<your_schema>';" <database>` ; do psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -c "alter sequence "$tbl" owner to <your_role>" <database>; done -
[SYSTEM]
This script uses the local tnsnames.ora file to create a connection, and requires the variable ${p} to hold the current SYSTEM password.
-
Status instance postgres
POSTGRES_IS_RUNNING=$(export PGPASSWORD=${PG_PWD}; psql --host=${MACH_DEST} --port=${PORT} --user=${PG_USER} --dbname=postgres --quiet --tuples-only --no-psqlrc --command "SELECT 1;" 2>/dev/null)
INSTANCE_STATUS=$(export PGPASSWORD=${PG_PWD}; psql --host=${MACH_DEST} --port=${PORT} --user=${PG_USER} --dbname=postgres --quiet --tuples-only --no-psqlrc --command="SELECT CASE pg_is_in_recovery() WHEN true THEN 'IN RECOVERY' ELSE 'OPEN' END;" 2>/dev/null) -
Calcul de stats postgreSQL
$PGHOME/bin/vacuumdb --port=$PORT --username=$PG_USER --analyze-only --verbose --jobs=$NB_JOBS $PG_DATABASE" 2>&1
-
Ora2PG script creation project
#!/bin/sh
#-------------------------------------------------------------------------------
#
# Script to create an ora2pg project
# Author : JJY
#
#-------------------------------------------------------------------------------
#set -vx
clear
echo " "
echo " Welcome in the program to create a migration project. "
echo " "
echo " Write the working environment (dev/qa/prd) :"
echo " "
read env
echo " "
echo " Write the name of your asset :"
echo " "
read base
echo " "
mkdir -p $ORA2PG_HOME/$env
ora2pg --project_base $ORA2PG_HOME/$env --init_project $base -c $ORA2PG_CONF
#cp $ORA2PG_HOME/config/commonstruct.conf $ORA2PG_HOME/$env/$base/config/metadata_ora2pg.conf
#cp $ORA2PG_HOME/config/commondata.conf $ORA2PG_HOME/$env/$base/config/data_ora2pg.conf
echo " "
echo " !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
echo " Working environment created for $base: $ORA2PG_HOME/$env/$base"
echo " !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
echo " "
echo " !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
echo " The file $ORA2PG_HOME/$env/$base/config/metadata_ora2pg.conf "
echo " and $ORA2PG_HOME/$env/$base/config/data_ora2pg.conf have to be fixed."
echo " !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
exit 0 -
DEPLOY HA postgres
-
About JSON in RDBMS
2 links :
JSONB PostgreSQL: How To Store & Index JSON Data (scalegrid.io)
-
Désactiver le mode visual sur VI
J'ai remarqué un truc bien chiant sur les nouvelles VM linux : le clic droit de la souris ne peut plus coller...!
pour y remédier :
echo "set compatible" > ~/.vimrc
echo "set mouse-=a" >> ~/.vimrc -
PG_BADGER
pgbadger -j 4 --outfile $HOME/rapport_complet.html -f stderr -p '%t:%r:%u@%d:[%p]:' /pglog/postgresql-2021-09-10_000000.log