SELECT relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) As « Total Size », pg_size_pretty(pg_indexes_size(relid)) as « Index Size », pg_size_pretty(pg_relation_size(relid)) as « Actual Size » FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
Catégorie : Postgresql
PostgreSQL Defrag les plus gros indexes et tables
Réduire la fragmentation https://www.timescale.com/learn/how-to-reduce-bloat-in-large-postgresql-tables Afficher les (10) plus gros indexes : SELECT indexrelname AS index_name, pg_size_pretty(pg_relation_size(relid)) AS index_size, pg_size_pretty(pg_total_relation_size(relid)) As « Total Size »FROM pg_catalog.pg_stat_all_indexes WHERE schemaname =’public’ORDER BY pg_total_relation_size(relid). read more…
[Postgres] Size taille table / index
SELECT relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) As « Total Size », pg_size_pretty(pg_indexes_size(relid)) as « Index Size », pg_size_pretty(pg_relation_size(relid)) as « Actual Size » FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
[Postgres] Migrate the role to distant host
ssh <targetServer> ». /usr/postgres/bin/setDB.bash <DBNAME>;export PGPASSWORD=pwd;pg_dumpall –roles-only -h <sourceServer> -p 5432 -U <username>| psql -a »
[Postgres] SET DB ENV
/tech/postgres/dba/jjy setDB.bash#! /bin/bash#################################################################### Program : setDB.bash ### Description : Set environement variables. read more…
[Postgres] AUDIT
Requêtes pour audit
Nombre de ligne dans les tables postgres
SELECT reltuples::bigint AS estimate FROM pg_class WHERE oid = ‘myschema.mytable’::regclass;
[Postgres] check_postgres
https://wiki.postgresql.org/wiki/Index_Maintenance
[Postgres] Create partitionned tables and rotate
Script to create partitionned tables and rotate it on one year DECLARE . read more…
PostgreSQL performance Turing Indexes
https://www.timescale.com/learn/postgresql-performance-tuning-optimizing-database-indexes
[Postgres] Fonction pour compter les lignes
Pratique lors d’une migration pour vérifier que toutes les lignes sont bien là ! set schema ‘admin’;drop function IF EXISTS count_rows;create or replace function count_rows(schema text, tablename text) returns integeras$body$declare result. read more…