vRA – Quick Tip – How to access embedded PostgreSQL DB

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:

Screenshot 2019-08-09 at 18.00.52

Querying table with expanded display off:

Screenshot 2019-08-09 at 18.03.02

Querying table with expanded display on:

Screenshot 2019-08-09 at 18.03.17

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';

Screenshot 2019-08-09 at 18.07.38

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

Happy troubleshooting! 😀

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close