intéressant mon cher watson SELECT current_database() AS « Database », schemaname AS « Schema », tablename AS « Table », reltuples::bigint AS « Lines », /*relpages::bigint, otta,*/ ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS « Table bloat », . read more…
Catégorie : Postgresql
[Postgres] Mesure the bloat
intéressant mon cher watson SELECT current_database() AS « Database », schemaname AS « Schema », tablename AS « Table », reltuples::bigint AS « Lines », /*relpages::bigint, otta,*/ ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS « Table bloat », . read more…
[Postgres] Spot a missing postgreSQL index
–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. read more…
Migration assessment
Migration assessment Migration assessment 1rst Method We use 3 servers : · Management server : which hosts ora2pg server, open flow between the oracle server and the management server. Open flow. read more…
[Postgres] Découverte d’une DB
Découverte d’une base postgres SELECT schemaname as table_schema, relname as table_name, n_live_tup as row_count FROM pg_stat_user_tables ORDER BY n_live_tup DESC; echo ===================================echo OBJECT COUNTecho ==================================SELECT n.nspname as schema_name ,CASE. read more…
PostgreSQL in IBM Cloud.
Création d’une base postgresql avec role hvault en mode batch dans le cloud IBM.. #!/bin/sh# Desc : $1 is the name of the ASSET you want to deploy# . read more…
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 //on the ora2Pg management server : 0- prerequisite create role role_own_DB login; 1 – export schema gives the metadata // on. read more…
POST MIGRATION ajouter un booleen
# changer type en booleenalter table « <your_table> »alter column « <your_column> »set data type booleanusing case when « <your_column> » = ‘1’ then true when « <your_column> » = ‘0’ then false else nullend;. read more…
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 ownerfor tbl in `psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -qAt -c « select tablename from pg_tables where schemaname. read more…