[Postgres] Mesure the bloat

intéressant mon cher watson

[Postgres] Spot a missing postgreSQL index

--tuto tuning psql
--Spot a missing PostgreSQL index

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
      AND 50 * seq_scan > idx_scan -- more than 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

--To find unused index in a db

SELECT
  relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan =0 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;

--returns the tables which have been hit by sequential scans the most and tells us how many rows a sequential scan has hit on average.
SELECT schemaname, 
              relname, 
              seq_scan, 
              seq_tup_read, 
              idx_scan, 
              seq_tup_read / seq_scan AS avg
 FROM         pg_stat_user_tables 
 WHERE        seq_scan > 0 
 ORDER BY     seq_tup_read DESC;

--Finding slow queries to be executed on pgadmin
SELECT query, 
              total_exec_time, 
              calls, 
              mean_exec_time 
       FROM   pg_stat_statements 
       ORDER BY total_exec_time DESC;

Migration assessment

[Postgres] Découverte d'une DB

Découverte d'une base postgres

PostgreSQL in IBM Cloud.

Création d'une base postgresql avec role hvault en mode batch dans le cloud IBM..

Migration of Synonyms from Oracle to PostgreSQL

Migration of Synonyms from Oracle to PostgreSQL - MigOps

Migration from Oracle Legacy to postgreSQL dMZR

Migration from Oracle Legacy to postgreSQL dMZR

POST MIGRATION ajouter un booleen

# 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 sequ

1-    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); 

Difference between Clustered and Non-clustered index

link Difference between Clustered and Non-clustered index (guru99.com)

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