• Arun Nukula

Useful Postgres commands which can be executed on vRA appliance database

Updated: Mar 1

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


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


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


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;

237 views0 comments

Recent Posts

See All

Enabling debug logging for vRA's horizon

Execute below commands to enable debug logging for vRA's horizon and connector components. sed -i 's/rootLogger=INFO/rootLogger=DEBUG/g' /usr/local/horizon/conf/saas-log4j.properties sed -i 's/rootLog