Recently I was needing to connect to a Postgresql database (specifically EMC’s Greenplum), from my Mac Air running OSX 10.7 Lion.  Greenplum provides four different drivers in their connectivity kit:

psqlodbc-08.02.0500 
psqlodbc-08.03.0400 
psqlodbc-08.04.0200 
psqlodbc-09.00.0200

I created an odbc.ini and odbcinst.ini files that have all the appropriate settings, tried each of the drivers, and go the same result:

nettles$ iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008

Enter ODBC connect string (? shows list): ?

DSN | Driver 
——————————————————————————
greenplum | psqlodbc-09.00.0200 

Enter ODBC connect string (? shows list): greenplum
1: SQLDriverConnect = [iODBC][Driver Manager]dlopen(/usr/local/greenplum-connectivity-4.2.1.0-build-2/drivers/odbc/psqlodbc-09.00.0200/unixodbc-2.2.12/psqlodbcw.so, 6): no suitable image found. Did find:
/usr/local/greenplum-connectivity-4.2.1.0-build-2/drivers/odbc/psqlodbc (0) SQLSTATE=00000
2: SQLDriverConnect = [iODBC][Driver Manager]Specified driver could not be loaded (0) SQLSTATE=IM003

A suggestion was made, that all of these libraries were quite old and to try psqlodbc-09.00.0200.  I skipped that and went directly to plan B, which was to use a different package.  The package that most use with OSX is unixodbc, specifically something like unixodbc-2.2.12.

In searching the net, I found that just about everyone running Lion, and possibly even Snow Leopard, has had issues with the ODBC postgresql drivers.  It seems they have been broken for some time, and you will see people get errors very similar to the above.  The solution I found is Actual ODBC Driver for Open Source Databases from Actual Technologies.  For $34.95 you get a plug and play driver that comes with a nice updated ODBC Manager.  Actual Technologies makes the ODBC Manager available to all for free in case you want it without purchasing the product.

The driver still uses the ODBC framework your probably used to.  Tools such as isql and iodbctest should work just fine.  I was able to connect using R’s RODBC library to my external Greenplum database just fine:

> library(RODBC)
> ch <- odbcConnect(“greenplum”,uid=”gpadmin”,pwd=”password”)
> odbcGetInfo(ch)
DBMS_Name      DBMS_Ver               Driver_ODBC_Ver Data_Source_Name
“PostgreSQL”       “8.2.1953722927″  ”03.00″                      ”greenplum”
Driver_Name       Driver_Ver               ODBC_Ver                Server_Name
“atopnsrc.so”        ”3.1.8″                       “03.52.0000″          ”gp-single-host”

If you actually are able to get ODBC working for postgresql on OSX, please update me so I can update this post.  I have read enough up to this point to be pretty convinced its broke.  I would especially be interested in any feedback good or bad for anyone who has tried psqlodbc-09.00.0200.

I have recently been playing with the Greenplum 4.1 Community Edition VM available from Greenplum.  EMC has an internal initiative to make all of its “demo’s” as VM’s and I generally agree with this.  I would say that make the VM’s available but also  make the software available so people can build their own environments as well.  Not sure if that will be the case.

Even though the VM is designed to work on VMware Player or Fusion, I got it working under Parallels with a few tweaks.

The VM itself fires up fine but there are a few problems with it you may encounter and I have made an attempt to catalog some of those things here and how to get around the problems.  I will add to this article as I find new things.

When the system boots up, you are presented with a nice desktop of icons, one of the first things you will likely do is click the Start Greenplum DB icon.  Here is a picture of the Desktop you are presented with:

You will be presented with an output that should show all has gone well, and at the end it directs you to fire up a browser to view the Performance Monitor User Interface:

Note: you can now use the GP monitor if you want monitor query and system performance Connect to the GUI by opening this link in a browser (outside of the VM): https://gp-single-host:28080/ Login using the user/pass: gpmon/password

The output directs you to connect from “outside of the VM”, as in your laptop which is hosting the VM by hitting https://gp-single-host:28080.  Obviously you will need to add a host to your laptops host file with the name of gp-single-host and the IP address of the VM.  You can get the IP address of the VM by simply opening a terminal window and doing an ifconfig eth0.  A good test is to connect to the Performance Monitor UI from within the VM itself, this will fail.  First you must re-rerun the installer:

[gpadmin@gp-single-host gpquery]$ su - gpadmin Password:  [gpadmin@gp-single-host ~]$ gpperfmon_install --enable --password password --port 5432 [...]

After you run this, you should now be able to connect locally from within the VM.  You will notice however you cannot connect outside of the VM.  This is due to the firewall rules that are in effect on the CentOS VM:

[root@gp-single-host gpadmin]# /sbin/iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination
RH-Firewall-1-INPUT all — anywhere anywhere

Chain FORWARD (policy ACCEPT)
target prot opt source destination
RH-Firewall-1-INPUT all — anywhere anywhere

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

Chain RH-Firewall-1-INPUT (2 references)
target prot opt source destination
ACCEPT all — anywhere anywhere
ACCEPT icmp — anywhere anywhere icmp any
ACCEPT esp — anywhere anywhere
ACCEPT ah — anywhere anywhere
ACCEPT udp — anywhere 224.0.0.251 udp dpt:mdns
ACCEPT udp — anywhere anywhere udp dpt:ipp
ACCEPT tcp — anywhere anywhere tcp dpt:ipp
ACCEPT all — anywhere anywhere state RELATED,ESTABLISHED
ACCEPT tcp — anywhere anywhere state NEW tcp dpt:ssh
REJECT all — anywhere anywhere reject-with icmp-host-prohibited

The easiest thing to do here is just change the security level to disable the firewall.  If you are knowledgable in iptables you can modify it to suit your needs.  To change the security level execute:

[gpadmin@gp-single-host ~]$ su
Password:
[root@gp-single-host gpadmin]# system-config-securitylevel

Set the Security Level to “disabled” and you can leave the SELinux setting to “Enforcing”.  Now your web browser should be able to connect to the Performance Monitor UI from outside the VM.

The next issue you will encounter is when you click on the “Run Queries Demo” icon on the Desktop.  You will encounter the following error:

Running Query demo
This demo will create and load data for 8 tables, then will run 22 queries

Press enter key to continue…
Executing command: ./reload.sh
Running command “psql -d gpadmin -c ‘drop database if exists gpdemo’” …

Error running command psql -d gpadmin -c ‘drop database if exists gpdemo’
Exiting…
Output is in file /home/gpadmin/gpquery/sysout
There was an error running the command.

Press enter key to continue…

The issue is that the gpadmin database does not exist.

The script reload.sh in the demo tries to run:

runCmd “psql -d gpadmin -c ‘drop database if exists $PGDATABASE’”

yet there is no gpadmin database as installed by default in the Greenplum CE VM. So the script fails.

We must create it:

[gpadmin@gp-single-host ~]$ psql -d template1
psql (8.2.15)
Type “help” for help.

template1=# CREATE DATABASE gpadmin;
CREATE DATABASE
template1=# \q

Now you can re-run the “Run Queries Demo” script and it should succeed with no errors.

As a note, you should definitely read the documentation provided on the Desktop of the VM. The Installation Guide and Administration Guide have much  useful information in them.  For example, if you want to connect to the database externally, you will need to add users to the pg_hba.conf file.  The correct pg_hba.conf file lives in ${MASTER_DATA_DIRECTORY}. I just added a wildcard to allow all connections like so:

host     all         gpadmin         0.0.0.0/0      trust

As I run into any other caveats with the Greenplum 4.1 Community Edition VM I will update this article.

Greenplum released Community Edition 4.1 which is a great free VM appliance you can run to get your feet wet with Greenplum and gain an understanding of what it can offer.  Unfortunately it was only released to work on VMware Workstation or Fusion.  Personally I like to run Parallels Desktop on my Macbook Air, so I worked to figure out how to make this work.

First off, the VM includes its vmdk broken up into 2GB chunks.  Parallels may or may not be able to deal with this.  For good measure, I just converted it to a single file.  To do this I used VMware Fusion, unchecked the box to split the vmdk into 2GB chunks and let it do its thing.  Another way you can accomplish this would be to use the QEMU tools:

qemu-img convert file.vmdk -O raw file.hdd

You can then take the file.hdd and add it to a VM  you create in Parallels.  Greenplum 4.1 CE does not require much resources, the default its only set for 1536MB and a single processor in the stock vmx file.  So you could create a Parallels VM with a single processor, 1536MB memory and simply add the file.hdd as its hard disk.

When creating the VM in Parallels make sure you select IDE0:0 as the hard disk, it will fail otherwise.

If you try to import the VMX file into Parallels it may leave you with a CD-ROM trying to connect to an image that doesn’t exist as well, so you will need to correct that.  There is really no benefit to importing the VMX.  Best thing is to just create a new VM, as CentOS 4/5 64-bit, single processor, 1536MB memory and connect the raw .hdd file.  Parallels should glue all of this together and successfully build a .pvm file.

You will want to install the Parallels Tools once the system boots.

I am excited about an upcoming road trip I have planned. We are going to take a drive on historic Route 66. A stretch from California, through Arizona and finally ending in New Mexico.  A total of about 900 miles.  Ideally I wanted to be able to rent a 4×4, perhaps a Jeep Wrangler, but options seem to be quite limited.  Route 66 has a variety of roads, some in good condition, and some so bad that an alternate path is highly recommended unless you have 4×4.  In these few instances we will likely take the alternate path.

Pacing ourselves over approximately three full days of driving, our itinerary roughly looks like this:

Day 1

Los Angeles, California to Needles, California
Lodging: Best Western Colorado River Inn , Needles, California

Day 2

Needles, California to Flagstaff, Arizona
Lodging: Hyatt Pinon Pointe , Sedona, Arizona

Day 3

Flagstaff, Arizona to Albuquerque, New Mexico
Lodging: Hyatt Regency, Albuquerque, New Mexico

No real plans along the route yet.  Likely just take it as it comes.  I do plan to bring my metal detector, there is much gold in those areas as well as meteorites.  We will definitely be taking lots of pics.  I will blog about each day on my site and post pictures.

One cool part about this trip, is that it was almost entirely financed by my rewards points.  I am Hyatt Diamond and that sure came in handy.  I was surprised that the Hyatt Regency in Albuquerque was a mere 6000 points and likewise the Hyatt Pinon Pointe Resort was only 12000 points.  The flight in is Palm Beach to Los Angeles via Southwest.  I used points here as well, unfortunately it’s two stops along the way, so basically a full day of traveling out.  My travel days are not counted in the above three road trip days, those are full days of being on Route 66.  Flight out is Albuquerque back to Palm Beach, once again two stops along the way.

Although I travel quite a bit, I have spent the least amount of time in the mountains, deserts and other places out west. The closest I have even come to that was several weeks I have spent in California around Orange County and Anaheim, and several months that I spent in Las Vegas (yes, I spent months in Vegas).  This gave me a feel for the intense desert climate with its dry heat and cold nights, but I did not get to go around the actual Mojave Desert and see mesas, mountains, cacti, etc.

If anyone has any tips on places to see along this stretch of Route 66, or something not to far off the beaten path, please let me know.

I never do this, but I am dead set on winning this contest.  I could use your help.  I recently purchased a Sport Brella umbrella (great for beach) and below is a link to my photo.  Please like it and help me win a vacation!

http://shar.es/sBdgN