How to edit the vCAC 6 appliance database

Disclaimer: This article is only for educational/testing purposes. Editing the vCAC database is not advised in a production environment and should only be done when VMware directs you to do so.

This article discusses how to edit the database of the vCAC appliance. This comes in handy when troubleshooting vCloud Automation Center; for example if you cannot delete an item from the catalog anymore.

vCAC databases

VMware vCloud Automation Center 6.0 uses two different databases:

• A vFabric vPostgresql database, which runs on the vCAC appliance. You can use an external database if required;
• A Microsoft SQL Server database, which is used by the Windows IAAS server. The SQL Server database can run on the IAAS server or on a separate Microsoft SQL Server.

During installation you have to configure these two databases. This article discusses editing the appliance database; for editing the IAAS database you can use Microsoft SQL Server Management Studio.

The vCAC database holds different kinds of information, for example: general configuration data, catalog data and data related to the Advanced Service Designer. Data related to blueprint definitions, provisioning groups and IAAS endpoints are stored in the Microsoft SQL Server database.

You can find the vCAC database settings in the management interface of the vCAC appliance, as detailed in the next figure:

The vCAC configuration interface is available on https://vcacurl:5480/.

After discussion with some colleagues I was wondering how to view and edit the vCAC database. The question is whether this is something you want to do. For a testing environment it’s up to you; for a production environment, be very careful and first discuss your problem and solution with VMware Global Support Services.

My personal experience is that vCAC can sometimes get ‘out of sync’ with the live situation in your infrastructure. For example: an Advanced Service Designer item is deleted outside vCAC, but the catalog item remains in the vCAC interface. This can make it impossible to delete the catalog item. Editing/deleting the correct record in the database can be a solution here.

Edit the database

To edit the database, create an SSH connection to the vCAC appliance and log on using the same root account that is used for the administrative interface.

First, let’s dump the current database contents to the file db-dump.txt:

/opt/vmware/vpostgres/current/bin/pg_dump --username=vcac vcac > db-dump.txt

The username for the database is ‘vcac’, and the database name is ‘vcac’ as well. You can download the dump file using a tool such as WINSCP or Filezilla or view using a tool like vi.

Now let’s find out which tables are available in the vCAC database. We’re using the ‘psql’ tool for this, which will provide us with an interactive shell to manage the database.

The syntax to start psql is: psql [database][user]. No password is required in this example (the database is not password protected):

/opt/vmware/vpostgres/current/bin/psql vcac vcac

The command \d in the psql shell will provide a list of available tables in the database; escape with ‘q’.

vcac=> \d

Schema |
                         Name                             |   Type   | Owner --------+------+----------+------- public | 
approval                                                  | table    | vcac public | 
approvallevel                                             | table    | vcac public | 
approvallevel_approvers                                   | table    | vcac public | 
approvalleveldecision                                     | table    | vcac public | 
approvalphasetype                                         | table    | vcac public | 
approvalpolicy                                            | table    | vcac public | 
approvalpolicy_phase                                      | table    | vcac public | 
------------ (about 124 tables are displayed)

Now let’s see which columns are in the asd_endpoint database:

vcac=> \d asd_endpoint
Table "public.asd_endpoint"
Column               |            Type             | Modifiers -----------+-----------------------------+-----------
id                   | 
uuid                 | not null deletable                                 | boolean                     |not null 
description          | character varying(2000)     | editable             | boolean                     | not null 
lastupdatedby        | character varying(255)      | 
lastupdateddate      | timestamp without time zone | multiendpointallowed | boolean                     | not null 
name                 | character varying(255)      | not null 
pluginname           | character varying(255)      | 
providerid           | character varying(255)      | rootobjectclass      | character varying(255)      | 
rootobjectid         | character varying(255)      | rootobjecturi        | character varying(2083)     | 
tenantid             | character varying(255)      | not null

Indexes: "asd_endpoint_pkey" PRIMARY KEY, btree (id)
<\pre>

To exit the interative psql shell simply type \q:

vcac=> \q

Editing the database involves some SQL coding which I will demonstrate in a future article. Stay tuned via twitter or leave your e-mail address in the ‘subscribe to this blog’ box on the right.