List of few commands which can help in troubleshooting database related issues
Login into Database
/opt/vmware/vpostgres/current/bin/psql vcac postgres
Exit Database
\q
Display largest tables across all formats
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
Display last Vaccum stuff
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public';
Display largest objects
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE relpages >= 8
ORDER BY relpages DESC;
Whole database size
select pg_size_pretty(pg_database_size('vcac'));
Single table size
SELECT pg_size_pretty(pg_total_relation_size('event'));
Size per row from the table
SELECT primary_key, pg_column_size(tablename.*) FROM tablename;
Comments