vPostgres in vCenter 6.0
Unlike earlier versions, vCenter 6.0 introduces a new default database for Windows installations, vPostgres. Some who have used the vCenter Appliance might recognize it, as it was adopted there in vCenter 5.0 Update 1 and above. vPostgres is an adapted version of PostgreSQL which very closely mimics its open source origins. For those used to SQL Management Studio, the interaction with this new database will be a bit different, but not difficult once you know the proper steps.
Connecting to a Windows vPostgres Database
To start, we’ll go over how to use the built-in CLI tool, psql, to log in to your Windows vPostgres DB.
First things first, you need to collect the randomly generated password for your db user. Navigate to C:\ProgramData\VMware\vCenterServer\cfg\vmware-vpx\vcdb.properties.
It should look something like this.
Open a command prompt and navigate to the bin directory of vPostgres. This will usually be C:\Program Files\VMware\vCenter Server\vPostgres\bin. From here you can now run psql, specifying the username and password you collected earlier.
psql -d VCDB -U vc
That’s it! You’re in psql at this point. You can run basic SQL queries from here such as SELECT and UPDATE, as well as some proprietary PostgreSQL commands. But BE CAREFUL. Changes in here can damage your vCenter installation just like any other backend database. If you want to quit out, just type \q from the prompt.
Using PGAdmin to connect to vPostgres
Already miss the GUI? Don’t fret! An excellent free utility exists that gives you quick access to your database in a very user-friendly way.
- Download 32bit PGAdmin here.
- If you’re not under a rock somewhere and are on a 64bit system, you’ll need the 32bit C++ Visual Studio 2013 Redistributable package. Link.
- Collect the database password just like we did above for psql.
Once installed onto the same server as vCenter, open PGAdmin and start a new connection. Fill in the name and host similar to below. Change the username to vc and enter the password from vcdb.properties. Un-select the Store Password checkbox
Note: Connection attempts from external machines will NOT work without modification to postgresql.conf and pg_hba.conf. Read more here.
Once you’re connected, you can drill down into the database structure. You’ll quickly notice that vPostgres differs from MSSQL Express in its architecture, and in order to interact with the database tables, you need to expand into a few more levels of the hierarchy.
Where are the Stored Procedures?
Another difference of note is that stored procedures from MSSQL are considered Functions in vPostgres. Manually running them requires using a SELECT statement.
For example, the commonly executed procedure to cleanup Tasks and Events would be run like this.
vPostgres Helpful Commands
If you are new to PostgreSQL and want to use psql, here are some simple proprietary commands that will help you interact with the DB from the CLI. Keep in mind that regular statements such as SELECT will work the same as in other SQL databases. Just don’t forget the trailing “;” or it will not execute!
Quit from psql
Describe all tables
Describe a specific table
Toggle Expanded mode – Makes tables with a lot of columns easier to read
\x List the available functions that run in VCDB
Display the overall size of the VCDB database in MB
Display the table size of VPX_EVENT in KB
Export the table VPX_ACCESS to a CSV file in the root of C:
\copy VPX_ACCESS to 'C:\users.csv' with CSV;
For more great psql information, check out the PostgreSQL documentation page here.