Install ODBC connector for PostgreSQL on Ubuntu Server 12.04

Question

I try to install the ODBC driver for PostgreSQL on Ubuntu Server 12.04. The purpose is to connect R (which is also running on the Server)to the database. I somehow got stock because I did not find a very comprehensive manual on how to do this.

So the first thing I did was to install the MySQL Database ODBC Driver with

apt-get install libmyodbc

as described here. Now I think I would need to enable ODBC in PostgreSQL. So normally if you want to achieve this you have to do it when manually building PostgreSQL. Since my database is already built and everything set up I wanted to do it afterwards. PostgreSQL-Manual states that:

To install the ODBC you simply need to supply the --enable-odbc option to the configure script when you are building the entire PostgreSQL distribution. The library will then automatically be built and installed with the rest of the programs. If you forget that option or want to build the ODBC driver later you can change into the directory src/interfaces/odbc and do make and make install there.

Ok. So Source doesn't come with the program if you install it with apt-get so I downloaded the source from here. I followed the steps described on the website to create the directory. Now, inside this directory src/interfaces/ there is no subfolder called odbc. So I also can't make the driver.

Now I googled this but I did not find any post of somebody that had a similar issue. So can anybody here please help me? I'm no database expert I only wan't to set this up to Work with R and PostgreSQL. Thx in advance.

Answer 1

Like most software projects, installation instructions in the PostgreSQL manual only apply if you have followed the rest of the instructions in the PostgreSQL manual. In other words, if you've installed from source as the manual describes. If you installed from distro packages, then the installation instructions in the PostgreSQL manual that refer to a source code installation do not apply and you must use distro-specific methods to install components according to your distro packaging.

Needless to say, the PostgreSQL team cannot possibly provide instructions for all distros and packaging methods, but I want the docs to make it clearer for new users that later parts of the source install docs do not apply if you didn't install from source.

In this case, you will need to install a package for Ubuntu that contains the postgresql odbc driver for unixodbc. apt-cache search postgres odbc reveals that the package is called .... odbc-postgresql.

So:

apt-get install odbc-postgresql

BTW, you might prefer to look into PL/R, which is R embedded within PostgreSQL.

---

I'm going to propose a patch to the Pg manual to address this point of new user confusion.

In this case a manual amendment would not help you, because you're reading a 10-year-old version of the manual. See the version links on the top of the page? Yeah. You're reading the manual for version 7.2, released in 2002-02-04. (It's annoying that Google likes to return old versions but we can't do much about it without causing other problems).