• Arun Nukula

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;

106 views

Recent Posts

See All

Subscribe Now

  • Twitter
  • Facebook Social Icon

Copyright © 2019 nukescloud