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.

wget http://www.python.org/ftp/python/2.7.3/Python-2.7.3.tgz
tar zxf Python-2.7.3.tgz
cd Python-2.7.3
./configure --shared --enable-unicode=ucs4
make
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/ld.so.conf.d/usr-local-lib.conf
ldconfig

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://github.com/Kozea/Multicorn.git
cd Multicorn

sed -i 's/^PYEXEC = python$/PYEXEC = python2.7/' Makefile
export PATH="$PATH:/usr/pgsql-9.2/bin"
make
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;
CREATE EXTENSION


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(# );
CREATE SERVER
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 ',');
CREATE FOREIGN TABLE
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.

    ReplyDelete