Useful Postgres commands which can be executed on vRA appliance database

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;