Interacting with the vPostgres Database in vSphere 6.0

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.
 
vcdbprops
 
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

Install
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.

Connect
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
 
pgadminlogin
 
Note: Connection attempts from external machines will NOT work without modification to postgresql.conf and pg_hba.conf. Read more here.
 
Browse
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.

 
pgadmin tables
 
Although there are certainly differences from SQL Management Studio, you’ll find that PGAdmin is actually quite intuitive and is easy to transition into.
 

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.
 

SELECT cleanup_events_tasks_proc();

 
 

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

    \q

Describe all tables

    \dt

Describe a specific table

    \d table_name

Toggle Expanded mode – Makes tables with a lot of columns easier to read

    \x
    
List the available functions that run in VCDB
    \df

Display the overall size of the VCDB database in MB

    SELECT pg_size_pretty(pg_database_size('VCDB'));

Display the table size of VPX_EVENT in KB

    SELECT pg_size_pretty(pg_total_relation_size('VPX_EVENT'));

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.

Posted by:

Chris Morrow

10 Comments

  1. […] Поэтому пришлось искать в глубь и ширь. Поиск в базе знаний даёт подсказку When attempting to add an ESXi 5.x host to a DVS, the host is missing or the list is empty (2039046), но я мигрировал базу на vPostgres, поэтому необходимо понять как vPostgres готовить. […]

    Reply
  2. Eric Gilbert -  June 23, 2015 - 12:13 pm 182

    You might mention that if 32-bit pgadmin is installed, 32-bit C++ Visual Studio 2013 Redistributable package is needed as well. Otherwise we get an error when starting pgadmin:

    “pgadmin3.exe – System error Impossible to launch the program because MSVCP120.dll is missing on your computer. Try to reinstall the program to correct the problem”

    Reply
    • Chris Morrow -  June 23, 2015 - 12:27 pm 183

      Updated the post to specify 32bit, Thanks Eric!

      Reply
  3. Rafael Ferreira Sousa -  February 1, 2016 - 11:10 am 384

    I am a great admirer of your work.

    But recently, seeking in his articles ask you a question? It is possible to migrate the vCenter database that is in Postgres to SQL Server?

    When former manager set up our vCenter he made the installation in embedded mode.

    Today acquired SQL Server 2012, after the bank always corrupt when the host had to be restarted.

    Reply
    • Chris Morrow -  February 3, 2016 - 9:18 am 386

      Hi Rafael, Unfortunately, there isnt a supported way to move the vCenters postgres DB to a SQL instance yet. Hopefully we get something like this soon!

      Reply
  4. kuver -  February 3, 2016 - 2:06 am 385

    Hey, I would like to use external Cluster based vpostgres DB with vCenter 6 appliance to mitigate SPOF for DB.
    I have not much knowledge about vPostgres, that’s why asking here.
    Thankyou
    Kuver

    Reply
    • Chris Morrow -  February 3, 2016 - 9:21 am 387

      Even though the clustering feature has been used with some of VMware other products, I dont think vCenter has not been tested on this platform yet. It would probably make an interesting project for learning purposes, but I would not recommend it in a production environment yet.

      Reply
  5. Vaibhav Tiwari -  September 14, 2016 - 9:56 am 522

    Hi,

    I have a Virtual Center Server Appliance 6.0 U2 — how do I connect to the postgres DB on it?

    Thanks

    Reply
  6. Basavaraj Navalgund -  October 31, 2016 - 4:10 pm 532

    Hi ,
    This is simple but useful info.
    Thanks.

    Reply
  7. Basavaraj Navalgund -  October 31, 2016 - 4:26 pm 533

    Hi Sean,

    Thanks, very simple explanation.

    Raj
    banavalg@yahoo.com
    Bangalore -India

    Reply

Leave A Comment

Your email address will not be published. Required fields are marked (required):

Back to Top