Install JPC on non standard PostgreSQL setup

How to install JChem PostgreSQL Cartridge on a non-standard PostgreSQL setup

General

If you are using a custom linux or your postgreSQL installation is just not according to the ChemAxon requirement you face with a situation that the package installation does not provide you any error, but during the extension creation (in the postgreSQL database) you get the following exception:
ERROR: could not open extension control file "/usr/share/pgsql94/extension/chemaxon_type.control": No such file or directory
This guide provides you a method to be able to install the JChem PostgreSQL Cartridge in this situation.

Requirements

Requirements for the JChem Postgresql Cartridge are defined here.

Setup for non-standard PostgreSQL installation

First, you need to install the provided rpm or deb package. The download and installation of jchem-psql package detailed in the documentation. Also copy your license file to /etc/chemaxon/ .

Next, initialize the service
sudo service jchem-psql init

and start the service
sudo service jchem-psql manual-start

According to the documentation, you need to create the necessary extension in the postgreSQL database and this is the point where you can face with the following error:

psql mydb
mydb=# CREATE EXTENSION hstore;
mydb=# CREATE EXTENSION chemaxon_type;
 
ERROR: could not open extension control file "/usr/share/pgsql94/extension/chemaxon_type.control": No such file or directory

The problem is that the postgreSQL database is unable to open the extension control file and load the necessary library as it is not located on the directory where the server is searching for it.
You need to do it manually.
The necessary files are located under the /opt/jchem-psql/extension/lib/ folder.
The content of the share folder should be copied to the folder described in the error message:
sudo cp /opt/jchem-psql/extension/share/* /usr/share/pgsql94/extension/

The content of the lib folder should be copied to postgreSQL library folder. To do this, first you need to identify the postgreSQL library folder which contains shared object (.so) files. This can be done in multiple ways:

  1. Use the package manager, list the files of the postgresql 9.4 package and search for lib directory containing .so files.

  2. Locate the directory containing plpgsql.so file

  3. Google it providing your distribution

Copy the content of the lib folder to postgreSQL library folder:
sudo cp /opt/jchem-psql/extension/lib/* /usr/lib/postgresql/9.4/lib/

Now you should be able to create extension without any error:

psql mydb
mydb=# CREATE EXTENSION chemaxon_type;
mydb=# CREATE EXTENSION chemaxon_framework;

If you still face with some installation issue, please find ChemAxon support.

The cartridge should run now without any problem, so test the search:

mydb=# SELECT 'C'::Molecule('sample') |<| 'CC'::Molecule;
?column?
----------
t
(1 row)

Step by step installation on Amazon Linux AMI image

Install PostgreSQL 9.4 RDBMS and related stuff:

Install PostgreSQL server and the contrib package.
sudo yum install postgresql94-server.x86_64
sudo yum install postgresql94-contrib

Initialize database.
sudo service postgresql94 initdb

Start database.
sudo service postgresql94 start

Create ec2-user.

sudo su postgres -
bash-4.2$ createuser --interactive ec2-user
Shall the new role be a superuser? (y/n) y
bash-4.2$ exit

Create mydb database. createdb mydb

Test the created mydb database.

psql mydb
testdb=#\q

Install JChem PostgreSQL Cartridge related stuff

Install oracle java-8

wget --no-cookies --no-check-certificate --header "Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F; oraclelicense=accept-securebackup-cookie" \
"http://download.oracle.com/otn-pub/java/jdk/8u60-b27/jre-8u60-linux-x64.rpm"
sudo yum localinstall jre-8u60-linux-x64.rpm

Download and install jchem-psql according to the documentation.

Copy your license file to /etc/chemaxon/license.cxl

Setup JChem Postgresql Cartridge

Initialize the service.

sudo service jchem-psql init
Initializing index directory...
Error: Exception thrown by the agent : java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: ip-172-30-2-96: ip-172-30-2-96: Name or service not known
*** initialization failed
* if the error is JAVA_HOME related: set a valid JAVA_HOME in /etc/default/jchem-psql
* if the error is related to indexDir: check that the directory given in 'com.chemaxon.jchem.psql.env.indexDir'
(defined in /etc/chemaxon/jchem-psql.conf) exists, empty and writable by jchem-psql

The local hostname somehow cannot be verified, you need to add it to the /etc/hosts file. You need to change “ip-172-30-2-96” to the output of the hostname command.
[[email protected] ~]$ hostname
ip-172-30-2-96

sudo sh -c "echo '127.0.0.1 ip-172-30-2-96' >> /etc/hosts"

Try initialization again
sudo service jchem-psql init

Start the service
sudo service jchem-psql manual-start

Create the necessary extension

psql mydb
mydb=# CREATE EXTENSION hstore;
mydb=# CREATE EXTENSION chemaxon_type;
 
ERROR: could not open extension control file "/usr/share/pgsql94/extension/chemaxon_type.control": No such file or directory

You face with the issue that the postgreSQL is not installed into the standard place so the necessary control files and libraries need to be copied to the proper place.
In case of Amazon Linux AMI image the extensions should be copied to /usr/share/pgsql94/extension/ and the library to /usr/lib64/pgsql94/ .

sudo cp /opt/jchem-psql/extension/share/* /usr/share/pgsql94/extension/
sudo cp /opt/jchem-psql/extension/lib/libchemaxon--1.0.so /usr/lib64/pgsql94/

Try to install the extensions again.

psql mydb
mydb=# CREATE EXTENSION chemaxon_type;
mydb=# CREATE EXTENSION chemaxon_framework;

The cartridge should run now without any problem, so let’s test the search now.

mydb=# SELECT 'C'::Molecule('sample') |<| 'CC'::Molecule;
?column?
----------
t
(1 row)

You are done.