Preforming vCAC PostgreSQL DB Queries for VMware Ticket

Mindwatering Incorporated

Author: Tripp W Black

Created: 04/02 at 01:07 PM

 

Category:
VMWare
VRo - VCAC

Warning:
Updating the backend vCAC database is not supported by VMware. Do only read enquiries unless you are in an active support call with the VMware tech who is directing such.


Steps to Perform a Query:
$ ssh vRAappliance.mindwatering.net
# su - postgres
$ psql vcac vcac
vcac=#


Tips:
\x turns on verbose mode (from view column format to a longer serial blocks of info for each row)
\dt can be used to display/show all tables
\d vcac, used to display a specific table's columns
\c change to a table, e.g. \c vcac
\q quit/exit the console

Syntax for psql: psql [database][user]. Seems to work w/ and w/o the vcac user added to the end.

Location of Postgres db executables:
/opt/vmware/vpostgres/current/bin

If you do have to edit with VMware, you can dump the current contents of the table:
/opt/vmware/vpostgres/current/bin/pg_dump --username=vcac vcac > /root/tmp/db-dump.txt

Simple SELECT statement:
SELECT column1, column2, columnN FROM table_name;


If you need to get the password for vCAC
1. Get the password encrypted hash:
# cat /etc/vcac/server.xml
...
password="passwordhashblobofcharacters"
...
2. Copy that blob to the clipboard, and add to the following command:
# vcac-config prop-util -d --p "passwordblobofcharacters"

3. Copy that actual password and enter into the login w/
# su - postgres
$ cd /opt/vmware/vpostgres/current/bin
$ ./psql vcac -W
<enter the password copied>

Alternately, you can use the postgres user, and not switch to vcac for viewing:
$ cd /opt/vmware/vpostgres/current/bin
$./psql postgres
psql.bin (version info)
Type "help" for help.
postgres=# \c vcac;
You are now connected to database "vcac" as user "postgres".
vcac=#


previous page