[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 COUNT
echo ==================================
SELECT
    n.nspname as schema_name
    ,CASE c.relkind
       WHEN 'r' THEN 'table'
       WHEN 'v' THEN 'view'
       WHEN 'i' THEN 'index'
       WHEN 'S' THEN 'sequence'
       WHEN 's' THEN 'special'
    END as object_type
    ,count(1) as object_count
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','i','S','s')
GROUP BY  n.nspname,
    CASE c.relkind
       WHEN 'r' THEN 'table'
       WHEN 'v' THEN 'view'
       WHEN 'i' THEN 'index'
       WHEN 'S' THEN 'sequence'
       WHEN 's' THEN 'special'
    END
ORDER BY n.nspname,
    CASE c.relkind
       WHEN 'r' THEN 'table'
       WHEN 'v' THEN 'view'
       WHEN 'i' THEN 'index'
       WHEN 'S' THEN 'sequence'
       WHEN 's' THEN 'special'
    END;
 
 
echo ===================================
echo Database size
echo ==================================
SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where pg_database.datistemplate IS NOT TRUE;
echo ===================================
echo List of databases
echo ==================================
l
echo ===================================
echo List of users
echo ==================================
du