[Postgres] TUTO

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

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

[Postgres] Migration of roles + DB (including data and privs)

Se connecter sur le serveur cible.

 

 pg_dumpall --roles-only -h remotehost -U remoteuser  | psql -h localhost -U localuser 
 pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser 

[Postgresql]Explain plan analyzer

Analyser un explain plan 

Un peu plus sur les indexes

 les indexes bitmap n existent pas en postgreSQL.

Il existe l équivalent, les indexes BRIN, que l'on met sur des tables volumineuses : 

https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#BRIN_Indexes

 

Es tu au courant qu'il existe d autres types d indexes , le GiST: 

https://en.m.wikipedia.org/wiki/GiST

 

Le Gin qui peut être utilisé dans un contexte plus général : 

https://wiki.postgresql.org/wiki/GIN_generalization

 

Pour résumé : 

 

PostgreSQL inclut une prise en charge intégrée des index B-tree et de table de hachage réguliers, et quatre méthodes d'accès aux index : arbres de recherche généralisés (GiST), index inversés généralisés (GIN), GiST partitionné par espace (SP-GiST)[33] et bloc Indices de gamme (BRIN).

 

In choosing which index type to use, GiST or GIN, consider these performance differences:

 

GIN index lookups are about three times faster than GiST

 

GIN indexes take about three times longer to build than GiST

 

GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled (see Section 54.3.1 for details)

 

GIN indexes are two-to-three times larger than GiST indexes

tables sans oid en big serial

select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
       and typ.typname = 'int4'
order by sch.nspname,
       tab.relname,
       col.attnum;


with seqs as
       (select oid,
             relname as sequencename
             from pg_class
             where relkind = 'S' )
select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype,
       seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
order by sch.nspname,
       tab.relname,
       col.attnum;

select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
       and typ.typname = 'int4'
order by sch.nspname,
       tab.relname,
       col.attnum;


with seqs as
       (select oid,
             relname as sequencename
             from pg_class
             where relkind = 'S' )
select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype,
       seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
order by sch.nspname,
       tab.relname,
       col.attnum;

[Postgres] Afficher plus d'infos

postgres=> dtvsi+
                                                          List of relations
     Schema     |             Name             |   Type   |        Owner        |          Table          |    Size    | Description

[Postgres] Trouver les schemas

select nspname
from pg_catalog.pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema';

[Postgres] Privilege par user

WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ '^pg_'
        AND n.nspname <> 'information_schema'
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
    FROM "names";

restore de blobs

SELECT lo_unlink(oid) FROM pg_largeobject_metadata;
WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

 

Migration of Synonyms from Oracle to PostgreSQL

Migration of Synonyms from Oracle to PostgreSQL - MigOps

ADRCI

existe depuis la 11G  , 
adrci est un outil pratique pour à la fois 
visualiser facilement le contenu de l'alert.log
purger les fichiers de logs , trace, et incidents
pacakger les incidents ou les problemes pour les envoyer au support

s'utilise en mode interactif ou batch  (demo)

relation entre incident et problemes

un incident est une occurence d'un probleme 
en d'autre terme, le meme probleme peut avoir plusieurs incidents

avec adrci nous avons un utilitaire efficace pour traiter les erreurs et pour collecter des informations pour les envoyer au support Oracle.    

Document 454927.1.pdf

Installation Oracle19c

Installation Oracle 19c

Running Health Checks Manually

Managing Diagnostic Data (oracle.com)

Top shell script pour monitoring BDD

Top DBA Shell Scripts for Monitoring the Database – BMC Software | Blogs

PostgreSQL in IBM Cloud.

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

Deploy new postgreSQL instance dMZR

Deploy new postgreSQL instance dMZR

Badge WAICF

pdf_39aaf456b8f44ab3265fd5f0e0f841e7004e8f8e2d7f1f427ce1a7c0787618104c70a3cfa1025e61334fa86bf77978ca.pdf (mobicheckin-badges.s3-eu-west-1.amazonaws.com)

pdf_1cae86610290b086ab67333c9e9a994ca8a7069d0490b6b6b4a65587f90e008585f678e713994118897be3a6d2efe987.pdf (mobicheckin-badges.s3-eu-west-1.amazonaws.com)

About JSON in RDBMS

2 links :

JSONB PostgreSQL: How To Store & Index JSON Data (scalegrid.io)

PostgreSQL JSON Tutorial (postgresqltutorial.com)

Assessment ora2pg

Assessment ora2pg : 
ora2pg -t SHOW_REPORT -c ./config/ora2pg.conf --dump_as_html --cost_unit_value 5 --estimate_cost > ./reports/report.html
ora2pg -t SHOW_TABLE -c ./config/ora2pg.conf > ./reports/tables.txt
ora2pg -t SHOW_COLUMN -c ./config/ora2pg.conf > ./reports/columns.txt

Compare source VS target

ora2pg --type TEST --count_rows --conf confia2pg.conf

Migration from Oracle Legacy to postgreSQL dMZR

//on the ora2Pg management server : 

0- prerequisite 

create role role_own_DB login;

 

1 - export schema

gives the metadata

 

// on the dMZR jump server (inside ibm cloud)

2 - create the roles in hashicorp vault ( script : new_deploy.sh) 

create the roles : dba/own/app

 

//on the ora2Pg management server : 

3 - import schema (script : automation.sh)

import the metadata and upload the data inside the DB postgresql.

4 - backup the postgresql database (pg_dump)

pg_dump --format custom --clean --no-owner --no-privileges apro > bckp_DB.dmp

5 - transfert the backup to dMZR jump server

 

// on the dMZR jump server 

5 - restore only the schema (metadata and data)  on ibmclouddb (pg_restore)

pg_restore --role=role_own_DB -n <schemaTOIMPORT> -d ibmclouddb bckp_DB.dmp

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

Migration de base oracle vers postgreSQL

Script de déploiement en préparation d'une migration de base oracle vers postgreSQL (dMZR) avec roles hashicorp.

EDB feedback

Why Mastercard's Secret to Zero Downtime is Postgres (enterprisedb.com)

[ora2pg] Conversion datatype

Conversion datatype 

useful to convert object into postgresql

[HVAULT] Roles creation for dMZR postgreSQL

Roles : own , app,  devpg

[Cloud IBM PSQL] Role creation with HVAULT (static dba)

script : prepare_dba_static_role.sh

 

[Cloud IBM PSQL] Role creation with HVAULT

Roles creation using hvault : 

- dynamic roles

The roles are created with this command :

vault write -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/${SERVER}/roles/${ROLE} 
db_name=${SERVER} 
creation_statements=@${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql 
revocation_statements=@${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql 
default_ttl=${ROTATION} 
max_ttl=${ROTATION}

[PostgreSQL] Privilèges

Privs useful to any new user created.

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

[Systeme] Create a logical volume for ora2pg

Create a logical volume of 450 Go