Interacting with the VMware Update Manager 6.5 embedded vPostgres Database
search cancel

Interacting with the VMware Update Manager 6.5 embedded vPostgres Database

book

Article ID: 328151

calendar_today

Updated On:

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