VMware vCenter for Linux PostgreSQL support research

To support PostgreSQL database in VMware vCenter Server for Linux, you need to do some schema modifications. An expert explains how.

Edward Haletky

When VMware released the vCenter Server for Linux technical preview, the release notes stated that it supported only Oracle's database. I decided to try database schema modifications to see whether I could get PostgreSQL database to work with vCenter for Linux (in part one, we discussed vCenter for Linux MySQL research). Here we discuss my research for VMware vCenter Sever for Linux support for the PostgreSQL database.

PostgreSQL is a valid, but not supported, database
I started with a fresh version of the vCenter for Linux appliance before starting this phase of my research. Since the appliance runs on CentOS 5, I went to the CentOS 5 website and downloaded the following RPMS: postgresql-odbc-08.01.0200-3.1.i386.rpm. After transferring them to the VMware vCenter on Linux VM, I installed them using the following:

 

rpm -ivh postgresql-odbc-08.01.0200-3.1.i386.rpm postgresql-8.1.11-1.el5_1.1.i386.rpm

I also downloaded and transferred to the system the Oracle Open Database Connectivity (ODBC) and Basic instantclient ZIP files that are required to install Oracle within vCenter on Linux, rebooted vCenter for Linux. During the reboot of vCenter for Linux it will ask you to configure the Oracle ODBC package, which I proceeded to do even though I was attaching to a PostgreSQL server. At one point after entering the database name, host name, username, and password the set up will fail and ask if I want to continue. I continued regardless of the failure because this process help to create the necessary files that we will edit below

Now we have to modify the ODBC files created. First, we need to add the PostgreSQL connector information into the /etc/odbcinst.ini file. Add the following lines:

 
[pgsql]
Description = PSQL ODBC for PostgreSQL 8.3.02
Driver = /usr/lib/psqlodbc.so
Setup =
FileUsage = 1
CPTimeout =
CPReuse =
UsageCount = 2

Then I changed the DSN in the /etc/odbc.ini file for the existing VMware vCenter entry to be VMware vCenter Oracle, and added the following lines. Be sure to include your IP address of your database for IPofDatabaseServer, your database username for PostgreSQLUserName, your database password for password, and your database name in place of VC.

 
[VMware VirtualCenter]
Description = PostgreSQL
Driver = pgsql
DSN = VMware VirtualCenter
Trace = No
Database = VC
ServerName = IPofDatabaseServer
Username = PostgreSQLUserName
Password = Password
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowOidColumn = No
FakeOidInded = No
ConnSettings = No
UpdatableCursors = No

Install using:

 

odbcinst -i -d -f /etc/odbcinst.ini
odbcinst -i -s -l -f /etc/odbc.ini

Create a table named VC on the database server, add the user VC to this database and then test the connection using the following command.

 

odbcinst -s –q
isql -v "VMware VirtualCenter"

Now that this works, we are ready to create the database. We start this time with the Oracle-defined tables, as PostgreSQL tables are the most similar. Start with the file /usr/lib/vmware-vpx/VCDB_oracle.sql . I created a subsequent VCDB_postgresql.sql file.

The conversions are as follows. But as in the MySQL conversions discussed in part one, these changes will force vCenter to support only UTF-8 strings.

 

  • NVARCHAR2 changes to VARCHAR
  • VARCHAR2 changes to VARCHAR
  • NCLOB changes to TEXT
  • NUMBER changes to NUMERIC (careful not to change TO_NUMBER to be TO_NUMERIC)

We also need to make some modifications to the SQL statements, as follows:

 

  • Remove all references to "organization index" as it does not exist in postgresql.
  • NVL changes to COALESCE, however you need to make sure the types of both arguments are the same. Two statements need to be converted from the value 1 to '1.'
  • TO_NUMBER takes a second argument of '9999999999999999'
  • insert into vpx_device select 1,null from dual; to insert into vpx_device VALUES(1,null);
  • select vpx_device_seq.nextval from dual; to select nexval('VPX_DEVICE_SEQ');

Submit the new schema to the PostgreSQL database server using the following. Note you would use the IP address of your PostgreSQL server instead of IPOfDatabaseServer, your PostgreSQL username instead of PostgreSQLUserName, and the command will ask for the password.

 

pgsql -p -h IPOfDatabaseServer -u PostgreSQLUserName < VCDB_postgresql.sql

Restarting vCenter for Linux showed unixODBC errors in the log file, and vCenter failed to connect to the database. So I changed the /etc/odbcinst.ini file to use the PostgreSQL ODBC drivers that came with unixODBC as such.

 
[pgsql]
Description = PSQL ODBC for PostgreSQL 8.3
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
CPTimeout =
CPReuse =
UsageCount = 3

Install using:

odbcinst -i -d -f /etc/odbcinst.ini
odbcinst -i -s -l -f /etc/odbc.ini

Create a table named VC on the database server, add the user VC to this database and then test the connection using the following command.

 

odbcinst -s –q
isql -v "VMware VirtualCenter"

I then restarted vCenter for Linux and while it connected, vCenter did not like the data it found. We get the following error.

[2009-03-01 13:09:31.384 'App' 1767104 error] [VpxdVdb] Failed to init tableDef: Column VER_ID does not exist in table VPX_VERSION. Database version may be incompatible.

I thought this was related to the way PostgreSQL folds cases of all statements to lower case, but enforcing upper case table and field names results in not being able to find the table. Just forcing upper case on the version fields also does not work. So something else is happening within the vCenter Server code to prevent the action. I did verify that the column and database existed using the following, which does show the field exists.

In addition, when we forced cast of 'ver_id' to upper case as well we got similar results shown below and the same error as before.

Research takeaways
So what has this research taught us? MySQL and PostgreSQL can handle the database creations as well as the port of the stored procedures, but you are limited to UTF-8 characters within all string variables (which would impact Asian vCenter users). But if a few changes were made to the underlying table structure UTF-16 (multibyte Unicode) characters could be supported with MySQL. Currently, though, vCenter for Linux does not accept using MySQL; it will flat out deny its use. PostgreSQL does not get denied altogether, but it will not work as a database for vCenter due to other errors. Clearly there is still some work to do, but the hard part of finding proper versions of databases and how to hook ODBC up to the databases and the port of the schemas and views is now complete.

 

Edward L. Haletky is the author of VMware ESX Server in the Enterprise: Planning and Securing Virtualization Servers. He recently left Hewlett-Packard Co., where he worked on the virtualization, Linux and high-performance computing teams. Haletky owns AstroArch Consulting Inc. and is a champion and moderator for the VMware Communities Forums.


 

This was first published in April 2009

Dig deeper on VMware how-tos

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchServerVirtualization

SearchVirtualDesktop

SearchDataCenter

SearchCloudComputing

Close