Friday, January 31, 2014

Working with VCSA embedded database

It's not often but sometimes you have to work with vCenter database. Usually it should be done only if you are instructed by VMware Support or there is VMware KB article (like this one solving your problem.

Please do it very carefully in production systems.

VMware vSphere admin veterans usually have experience with MS-SQL but what about vCenter Server Appliance (VCSA) with embedded database? It is not very different. VMware uses Postgresql database (aka vPostgres) so logically it is the same as in any other SQL database. I would say even easier than in MS-SQL but that's highly dependent on administrator background and previous experience. I'm probably biases due to my *nix history and open-source (GNU) general preference.

Here are basic logical steps how to work with vCenter database.
  • Connect to database server
  • Discover database tables
  • Issue SQL commands
  • Exit from database server

Change working directory to vpostgres
cd /opt/vmware/vpostgres/current/bin/
Display database configuration
cat /etc/vmware-vpx/embedded_db.cfg
output should looks like
EMB_DB_PASSWORD='WZL2^y<-k8boy br="" fa="">EMB_DB_STORAGE='/storage/db/vpostgres'
connect to database
./psql VCDB -U vc
Update 2015-09-15: For VCSA 6 use /opt/vmware/vpostgres/current/bin/psql 
-d VCDB -U postgres (password is not required)
and you are in.


It's really good to know what tables are in the database. You need table names to compose SQL commands allowing you to select, insert and update data in the database.

Postgresql have special DBA (database administrator) commands witch start with character \ (slash). You can list all DBA commands by sequence \?

The output looks like this
vc01:/opt/vmware/vpostgres/current/bin # ./psql VCDB -U vc
psql.bin (9.0.13)
Type "help" for help.

VCDB=> \?
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S]  [PATTERN]      list conversions
  \dC     [PATTERN]      list casts
  \dd[S]  [PATTERN]      show comments on objects
  \ddp    [PATTERN]      list default privileges
  \dD[S]  [PATTERN]      list domains
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
We want list database tables so the command we are looking for is
where output looks like
                    List of relations
 Schema |              Name              | Type  | Owner
 vpx    | vpx_access                     | table | vc
 vpx    | vpx_alarm                      | table | vc
 vpx    | vpx_alarm_action               | table | vc
 vpx    | vpx_alarm_disabled_actions     | table | vc
 vpx    | vpx_alarm_expr_comp            | table | vc
 vpx    | vpx_alarm_expression           | table | vc
 vpx    | vpx_alarm_repeat_action        | table | vc
 vpx    | vpx_alarm_runtime              | table | vc
 vpx    | vpx_alarm_state                | table | vc
 vpx    | vpx_binary_data                | table | vc
 vpx    | vpx_bulletin_operation         | table | vc
 vpx    | vpx_change_tag                 | table | vc
 vpx    | vpx_compliance_status          | table | vc
 vpx    | vpx_compute_res_failover_host  | table | vc
 vpx    | vpx_compute_res_user_hb_ds     | table | vc
 vpx    | vpx_compute_resource           | table | vc
 vpx    | vpx_compute_resource_das_vm    | table | vc
 vpx    | vpx_compute_resource_dpm_host  | table | vc
 vpx    | vpx_compute_resource_drs_vm    | table | vc
 vpx    | vpx_compute_resource_vsan_host | table | vc

If we want select and view some data from database we use SQL statement SELECT. As an example  we will use first table from the list an it is vpx_access. Table vpx_access contains all vCenter users/groups who has access to vCenter and their roles. Here is SELECT statement:
select * from vpx_access
and output

 id  |          principal          | role_id | entity_id | flag
   1 | root                        |      -1 |         1 |    1
 101 | VSPHERE.LOCAL\Administrator |      -1 |         1 |    1
 201 | VPOD01\vsphere-admins       |      -1 |         1 |    3
(3 rows)
Update and delete statements can be composed in similar manner following ANSI SQL Standard. Postgresql is ANSI-SQL:2008 standard.


To exit from database server simply use DBA command \q

That's it pretty easy, isn't it? Working with vCenter database is not daily task of vSphere admin however we all know that sometimes you can be instructed by VMware support or KB to change something in the database. Don't be afraid - it's easy.

1 comment:


Great post. I just ran an update on vCenter 5.1 to 5.13b appliance and it failed. The first thing I noticed was the folder structure was 1.0 instead of 9.0. I tried renaming it but the vCenter would still not start. It seems the update has totally messed up the DB. The only option I could find was reset the DB or run an upgrade. I hate reinstalling everything when I am certain this should be a simple fix. Your post helped but vCenter will still not load and management interface states invalid schema when testing DB.
Any ideas?