Freitag, 7. Juni 2013

Installing pgchem with postgres 9 and openbabel 2.3.2

pgchem is an extension for the postgreSQL database system that allows in-database molecule handling. It was developed by Ernst-Georg Schmid and allows rather quick handling of chemical molecules in a relational database system.
The repository of the developer can be found at github.

Because the code has been developed a few years ago there are some problems if you try to install pgchem with the current (or at least close to current) postgres and openbabel releases.
It took me at least a few weeks and a lot of help from some colleagues at Freiburg university to get a postgres/pgchem system up and running on my local linux x64 machine (Ubuntu 12.10).
In the end it proved to be easier than expected due to the big help of Björn Grüning and Kiran Telukunta who fixed the x64 Makefile and thus allowed the pgchem code to compile on a modern system.
Björn's repository with the updated code can be found here:

and they also included a nice shell script that handles the installation of postgres and openbabel as well as the actual pgchem and barsoi installation. It can also be found in Björn's repository ( The current version of the repo also contains changed SQL function definitions of the pgchem functions because two of them did not work with the new versions of openbabel and pgchem.

However, if you don't want to use this script, you have to do the following:
  1. Install postgres
    1. this could be done from source code or using a packet manager such as apt-get
    2. Create a new user account for your database
  2. Install openbabel (not necessary if you don't want a global ob installation)
    1. If you install it globally, you can also use the command line tools
    2. If you install python bindings as well you can use openbabel/pybel in python scrips
    3. The shared-flag has to be switched on for the libraries to work with pgchem:
  3. Install pgchem
    • Download the package from Björn's gibthub
    • Install the included openbabel version 
    • Install barsoi in the pgchem directory:
      cd barsoi make -f Makefile.linux
    • Now some re-naming has to take place for the makefile to work:
      1. locale.h in openbabel-2.3.2/include/openbabel/locale.h has to be renamed to _locale.h (in the same dir as locale.h) 
      2. In openbabel-2.3.2/build/lib there has to be a symbolic link from to
    • change into the pgchem directory 
    • Have a look at the makefile (only x64 is updated!) and change it if needed (should not be the case)
    • Compilemake -f Makefile.linux.x64
    • Then again some copying needs to be done:
      1. copy, barsoi/, openbabel-2.3.2/build/lib/, openbabel-2.3.2/build/lib/, openbabel-2.3.2/build/lib/ to your postgres library directory
      2. copy setup/tigress/obdata/dictionary* to your portgres lib dir at openbabel/share/openbabel/2.3.2
      3. Create the following links in the porstgres lib dir:
        sudo ln -s ln -s libinchi.sosudo ln -s ln -s libopenbabel.sosudo ln -s
    • Because only the openbabel library files are needed you can then remove the bin and include folders from your pgchem openbabel installtion
    • Restart postgres
  4. Maybe you need to copy the content of to /usr/local/share
  5. Now you have to import the new function into your postgres
    1. Create a new database, etc.
    2. Run the pgchem_setup.sql script in your database
      In the interactive command prompt you have to use the "\i" command:
      <your_db># \i /$POSTGRES_DIR/contrib/pgchem/setup/pgchem_setup.sql
Examples for how to use pgchem are given in the Documentation that can be accessed from the any of the repositories or found here.
Yay. First post. I'm probably expected to write something like "Hello world" which I've hereby done. Good. Now lets get started with more interesting stuff - at least interesting to me as a poor PhD student struggling with lack of certain up-to-date information on certain bio- and cheminformatics topics on the web.
I will use this blog from now on as an online "brain prosthesis" for all that stuff that has cost me hours of googling. If it is of interest of other like-minded - even better!