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.cfgoutput should looks like
EMB_DB_INSTALL_DIR='/opt/vmware/vpostgres/9.0'connect to database
EMB_DB_PASSWORD='WZL2^y<-k8boy br="" fa="">EMB_DB_STORAGE='/storage/db/vpostgres'-k8boy>
./psql VCDB -U vc
Update 2015-09-15: For VCSA 6 use /opt/vmware/vpostgres/current/bin/psqland you are in.
-d VCDB -U postgres (password is not required)
DISCOVER DATABASE TABLES
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 vcWe want list database tables so the command we are looking for is
Type "help" for help.
\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
\dtwhere output looks like
List of relationsISSUE SQL COMMANDS
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_accessand output
id | principal | role_id | entity_id | flagUpdate and delete statements can be composed in similar manner following ANSI SQL Standard. Postgresql is ANSI-SQL:2008 standard.
1 | root | -1 | 1 | 1
101 | VSPHERE.LOCAL\Administrator | -1 | 1 | 1
201 | VPOD01\vsphere-admins | -1 | 1 | 3
EXIT FROM DATABASE SERVER
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.