Interacting with the vCenter Server Appliance 6.5/6.7/7.0 embedded vPostgres Database
Article ID: 313597
Updated On:
Products
VMware vCenter Server
Issue/Introduction
The article provide steps to connect to the embedded vPostgres Database of a vCenter Server Appliance and check the size of the tables.
Caution: Ensure you have working backups and/or snapshots of the vCenter Server Appliance before interacting with the vCenter Server Appliance 6.5/6.7/7.0 Database.
Environment
VMware vCenter Server Appliance 6.5.x
VMware vCenter Server 6.7.x
VMware vCenter Server 7.0.x
VMware vCenter Server Appliance 6.7.x
VMware vCenter Server 6.5.x
VMware vCenter Server 6.7.x
VMware vCenter Server 7.0.x
VMware vCenter Server Appliance 6.7.x
VMware vCenter Server 6.5.x
Resolution
To connect to the embedded vPostgres Database of a vCenter Server Appliance 6.5/6.7/7.0:
- Log into the VCSA as root via SSH or Console.
- Type shell to switch to the BASH Shell.
- Connect to the VCDB by running this command from the vCenter Server Appliance 6.5/6.7/7.0 BASH Shell:
/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres
Once in the postgres database, standard SQL query language can be used.
- To list all tables, sequences, and views with size:
- To list all databases in the server run the following command:
\l+
- To list top 20 tables by their size run the following command:
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;
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;
- Run this command to quit postgres:
\q
Additional Information
Feedback
Yes
No
Powered by
