Helloooo!

Today I would like to share a quick tip with you on how to access the embedded vRA PostgreSQL database on your vRA appliances:

1. SSH to your vRA appliance as root
2. Once logged in, switch to the postgres user with the following command:
   su postgres
3. Change to the vRA database (VCAC): psql vcac
4. Show all tables in this database with: \dt
5. Run a query e.g. (do not forget ‘;’) : select * from cat_icon;
6. You can turn on expanded display to show output a bit better: \x

An example can be found below:

Querying table with expanded display off:

Querying table with expanded display on:

If you’re troubleshooting stuff within the vRA appliances, another thing that might come in handy is to know what the primary key is of a table.

This can be retrieved via the following command:

SELECT c.column_name, c.ordinal_position FROM information_schema.key_column_usage AS c LEFT JOIN information_schema.table_constraints AS t ON t.constraint_name = c.constraint_name WHERE t.table_name = '<YOUR TABLE HERE>' AND t.constraint_type = 'PRIMARY KEY';

Example:

SELECT c.column_name, c.ordinal_position FROM information_schema.key_column_usage AS c LEFT JOIN information_schema.table_constraints AS t ON t.constraint_name = c.constraint_name WHERE t.table_name = 'cat_icon' AND t.constraint_type = 'PRIMARY KEY';

In this example ‘id’ is the primary key of the table ‘cat_icon’.

Happy troubleshooting! 😀

• vRA