Interacting with the VMware Update Manager 6.5 embedded vPostgres Database

Products

• VMware vSphere ESXi

Issue/Introduction

This article explains how to connect to the embedded VMware Update Manager vPostgres Database of a vCenter Server Appliance 6.5.

Caution: Ensure you have working backups and/or snapshots of the vCenter Server Appliance 6.5 before interacting with the embedded VMware Update Manager 6.5 Database.

Resolution

To connect to the embedded VMware Upgrade Manager vPostgres Database:

1. Connect to the vCenter Server Appliance 6.5 using SSH.
   
   
2. Run the shell command to switch to the BASH Shell:
   
   shell
   
   

3. Locate the password for the database user vumuser. This is located in the vci-integrity.xml file.
   
   grep password /usr/lib/vmware-updatemgr/bin/vci-integrity.xml
   
   Example:
   
   # grep password /usr/lib/vmware-updatemgr/bin/vci-integrity.xml
   <password>PASSWORD</password>

4. To connect to the VUM DB, run this command from the vCenter Server Appliance 6.5 BASH Shell. The command will prompt for the password recorded in previous step.
   
   /opt/vmware/vpostgres/current/bin/psql -d VCDB -U vumuser
   
   Example:
   
   root@ [ ~ ]# /opt/vmware/vpostgres/current/bin/psql -d VCDB -U vumuser
   Password for user vumuser:
   psql.bin (9.4.9 (VMware Postgres 9.4.9.0-4442725 release))
   Type "help" for help.
   VCDB=#

   • To list all databases in instance and their size, run this command:
     
     Note: The VMware Update Manager tables are stored within the VCDB Database but under the vumdbc schema.
     
     \l+

     Example:
     
     VCDB=# \l+
     List of databases
     Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
     -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
     VCDB | vc | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/vc +| 69 MB | pg_default |
     | | | | | vc=CTc/vc | | |
     postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 6540 kB | pg_default | default administrative connection database
     template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 6409 kB | pg_default | unmodifiable empty database
     | | | | | postgres=CTc/postgres | | |
     template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 6417 kB | pg_default | default template for new databases
     | | | | | postgres=CTc/postgres | | |

   • To list all tables and their size, run this command:
     
     \d+
     
     Example:

     VCDB=# \d+
     List of relations
     Schema | Name | Type | Owner | Size | Description
     --------+--------------------------------+----------+---------+------------+-------------
     vumdbc | vci_aging_snapshots | table | vumuser | 8192 bytes |
     vumdbc | vci_baseline_group_baselines | table | vumuser | 0 bytes |
     vumdbc | vci_baseline_groups | table | vumuser | 8192 bytes |
     vumdbc | vci_baseline_groups_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_baselines | table | vumuser | 16 kB |
     vumdbc | vci_baselines_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_bundle_keys | table | vumuser | 0 bytes |
     vumdbc | vci_component_spec | table | vumuser | 168 kB |
     vumdbc | vci_content_keys | table | vumuser | 8192 bytes |
     vumdbc | vci_eula | table | vumuser | 16 kB |
     vumdbc | vci_eula_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_host_upgrade_baselines | table | vumuser | 0 bytes |
     vumdbc | vci_host_upgrade_packages | table | vumuser | 48 kB |
     vumdbc | vci_host_upgrade_packages_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_host_upgrades | table | vumuser | 16 kB |
     vumdbc | vci_host_upgrades_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_languages_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_locales | table | vumuser | 8192 bytes |
     vumdbc | vci_metadata_files | table | vumuser | 56 kB |
     vumdbc | vci_metadata_files_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_notifications | table | vumuser | 8192 bytes |
     vumdbc | vci_notifications_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_operation_history | table | vumuser | 8192 bytes |
     vumdbc | vci_operation_history_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_operation_history_subtasks | table | vumuser | 0 bytes |
     vumdbc | vci_package_files | table | vumuser | 80 kB |
     vumdbc | vci_package_files_seq | sequence | vumuser | 8192 bytes |
     vumdbc | vci_package_locales | table | vumuser | 0 bytes |
     vumdbc | vci_packages | table | vumuser | 120 kB |
   • Run these SQL Statements using Standard SQL syntax.
     
     
     Note: Ensure to end the statement with a semi-colon (;)
      
     Example:
     SELECT * FROM vci_packages;
     SELECT * FROM vci_baselines;
     
   • To quit psql, run this command:
     
     \q