Connect and work with the vCSA Embedded vPostgres Database

This article explains how to connect and work with the embedded vPostgres Database of a vCenter Server Appliance version 6.5, 6.7, and 7.0.

Caution: Working with the database can cause issues with the vCenter Service. Make sure that you know what you are doing and double-check that you have a working backup or snapshots of the vCenter Server Appliance. Consider working with VMware Support if you have problems in a critical production environment.

Connect to the Postgres Database

To connect to the database, you have to enable SSH for the vCenter Server, login as root, and launch the bash shell. When first connecting to the appliance, you see the "Appliance Shell". Just enter "shell" to enter the fully-featured bash shell.

The simplest way to connect to the databases is by using the "postgres" user, which has no password. It is convenient to also use the -d option to directly connect to the VCDB instance.

# /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB

When connecting, make sure that you use the psql binaries located in /opt/vmware/vpostgres/current/bin/ and not just the psql command. The reason is that VMware uses a more recent version than it is provided by the OS. In vSphere 7.0 for example, the OS binaries are at version 10.5 while the Postgres server is running 11.6:

root@vcenter [ ~ ]# /opt/vmware/vpostgres/current/bin/psql --version
psql (PostgreSQL) 11.6 (VMware Postgres 11.6.0-15394911 release)
root@vcenter [ ~ ]# psql --version
psql (PostgreSQL) 10.5

PSQL Basic Commands

While in the PSQL Shell you should make yourself familiar with a couple of basic commands. All commands start with a backslash (\) to differentiate from standard SQL Queries.

• \q - Quit PSQL
• \a - Toggle Aligned output
• \x - Toggle Extended output
• \dt - List Tables
• \dv - List Views

Get Database and Table Sizes

Use \l+ to list all databases with their size:

You can use \d+ to display table sizes. The VCDB has more than 1000 tables, so it might become difficult to identify which tables are the biggest. Use the following SQL command to list top 10 tables by their size:

SELECT nspname || '.' || relname AS "table",
  pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 10;

Output Formatting

When querying large tables you might see an output like this, which is not readable at all:

Depending on the situation you can either disable unaligned output or activate expanded output. Disabling unaligned output (\a) removes all blanks that are used to align the table:

Enabling expanded output (\x) produces the best readability for that table:

Run SQL Queries directly from Bash

With the -c parameter, you can run SQL queries directly from bash:

# /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "SELECT * FROM vpx_access;"
 id |          principal          | role_id | entity_id | flag | surr_key
----+-----------------------------+---------+-----------+------+----------
  1 | VSPHERE.LOCAL\Administrator |      -1 |         1 |    1 |        1
(1 row)

If you want to get a single value without header and footer you can turn those off with --tuples-only and -P "footer=off". This might help to use the output in scripts:

# /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "SELECT local_file_name FROM vc.vpx_vm WHERE dns_name = 'nsx1';" -A --tuples-only -P "footer=off"
/vmfs/volumes/5e84e57a-988270e7-81e0-d45ddf1390d5/nsx1.virten.lab/nsx1.virten.lab.vmx

Create a SQL Dump

A full SQL Database Dump can be created with pg_dumpall:

# /opt/vmware/vpostgres/current/bin/pg_dumpall -U postgres > vcdb.sql

Get vCenter Events from the Database

When you want to search for events that rolled out of vCenters event tab, you can use the database to find them very fast. This example lists all deleted Virtual Machines. A full list of event types is available here. You can do the same with PowerCLI for example, but in very large databases this method is very slow.

# psql -U postgres -d VCDB -c "SELECT event_type,create_time,username,vm_name FROM vc.vpxv_events where event_type like '%VmRemovedEvent%';"