Friday, January 18, 2013

Installing Multicorn on RHEL6

The Multicorn project makes it possible to write Foreign Data Wrappers for PostgreSQL in Python.

To install Multicorn on RHEL6 the following is needed:
  • PostgreSQL 9.2
  • Python 2.7
  • make, GCC, etc.
Installing PostgreSQL 9.2 is easy as it's available in the PostgreSQL Yum repository.

Unfortunately Python 2.7 is not included in RHEL6. And replacing the 'system' python is a bad idea.

The solution is to do an 'altinstall' of Python. The "--shared" and ucs4 options are required. The altinstall will install a python binary with the name python2.7 instead of just python. This allows you to have multiple python versions on 1 system.

tar zxf Python-2.7.3.tgz
cd Python-2.7.3
./configure --shared --enable-unicode=ucs4
make altinstall

This will result in a /usr/local/bin/python2.7 which doesn't work. This is due to the fact that the libraries are installed /usr/local/lib, which is not in the library path. This can be solved by modifying the library path.
echo "/usr/local/lib/" > /etc/

Installing PostgreSQL 9.2 is easy:
yum install postgresql92-server postgresql92-devel

Installing with PGXN will not work as this commit is not yet included. So we'll have to clone the git repo.
git clone git://
cd Multicorn

sed -i 's/^PYEXEC = python$/PYEXEC = python2.7/' Makefile
export PATH="$PATH:/usr/pgsql-9.2/bin"
sudo make install

The step to set the PATH is not in the Multicorn documentation, but it's needed as pg_config is not in our path. 

The sed line is needed to force the use of python2.7 instead of python (which is 2.6).

And then we can load the extension in the database.
postgres=# CREATE EXTENSION multicorn;

And then we can use the examples from the documentation:
postgres=# CREATE SERVER csv_srv foreign data wrapper multicorn options (
postgres(#     wrapper 'multicorn.csvfdw.CsvFdw'
postgres(# );
postgres=# create foreign table csvtest (
postgres(#        year numeric,
postgres(#        make character varying,
postgres(#        model character varying,
postgres(#        length numeric
postgres(# ) server csv_srv options (
postgres(#        filename '/tmp/test.csv',
postgres(#        skip_header '1',
postgres(#        delimiter ',');
postgres=# select * from csvtest;
ERROR:  Error in python: """

DETAIL:  [Errno 2] No such file or directory: '/tmp/test.csv'
postgres=# \! echo -en 'Year,Make,Model,Length\n2013,Volkswagen,Golf,3\n2013,Volkswagen,Passat,3' > /tmp/test.csv
postgres=# select * from csvtest;
 year |    make    | model  | length
 2013 | Volkswagen | Golf   |      3
 2013 | Volkswagen | Passat |      3
(2 rows)

1 comment:

  1. Excellent tutorial, thanks for sharing this. Even though this post is 2.5 years old, the instructions are still very current. I did it with Centos 6.6 and Python 2.7.10. The only major difference is installing pg from source, not yum.