syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me

Question:
How to install PL/Java on Postgres 9.5 on Centos 6.5?

I encountered a use-case recently which asked me to use PL/Java on Postgres.

I wrote up some notes on how to install it.

I started by downloading a copy of Centos from the URLs listed below: Next, I did a simple text-only install of CentOS-6.5-x86_64-LiveDVD.iso into VirtualBox on my laptop.

Then, I logged in as root and ran some shell commands:

su
cd ~root
yum groupinstall  'Development Tools'
yum install openssl-devel
wget https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm
yum install pgdg-centos95-9.5-2.noarch.rpm 
yum list postgresql95*
yum install postgresql95 postgresql95-server postgresql95-libs postgresql95-devel
/etc/init.d/postgresql-9.5 initdb
echo 'export PATH=/usr/pgsql-9.5/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin' >> ~postgres/.bash_profile

cd /var/lib/pgsql/9.5/data/
cp pg_hba.conf pg_hba.conf.bak
chown postgres pg_hba.conf.bak

sed -i '/^host/s/ident/md5/' pg_hba.conf
sed -i '/128/s/md5/ident/'   pg_hba.conf

diff pg_hba.conf.bak pg_hba.conf

/etc/init.d/postgresql-9.5 start
/etc/init.d/postgresql-9.5 reload
Next, I logged in as postgres and ran some commands:

su - postgres
psql
alter user postgres password 'hello';
\q

psql -U postgres -h localhost
\q
Next, I install jdk 1.8 which I download from oracle.com:

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

If Oracle moves that link, try this link:

http://www.google.com/search?q=java+jdk+1.8+download

The section below assumes the jdk from oracle is in ~root/

su
cd ~root
mkdir -p /opt/downloads
ls -la jdk-8u92-linux-x64.tar.gz
cp     jdk-8u92-linux-x64.tar.gz /opt/downloads
chown -R postgres /opt/downloads
The above commands get the download ready for a call to tar.

I want postgres to run the tar command because I see this jdk as part of postgres rather than public.

su - postgres
cd /opt/downloads
tar zxf jdk-8u92-linux-x64.tar.gz
I want the jdk to reside in /opt/ so I rely on root to put it there.

su
mv jdk1.8.0_92 /opt
cd /opt
ln -s jdk1.8.0_92 jdk
echo 'export JAVA_HOME=/opt/jdk'            >> ~root/.bashrc
echo 'export PATH=${JAVA_HOME}/bin:${PATH}' >> ~root/.bashrc
bash
javac              -version
/opt/jdk/bin/javac -version

echo 'export JAVA_HOME=/opt/jdk'            >> ~postgres/.bash_profile
echo 'export PATH=${JAVA_HOME}/bin:${PATH}' >> ~postgres/.bash_profile

su - postgres

javac              -version
/opt/jdk/bin/javac -version
Next, I installed maven.

su
cd /opt/downloads
wget http://www-us.apache.org/dist/maven/maven-3/3.3.9/binaries/apache-maven-3.3.9-bin.tar.gz
cd /usr/local
tar zxf /opt/downloads/apache-maven-3.3.9-bin.tar.gz 
ln -s apache-maven-3.3.9 maven
echo 'export PATH=/usr/local/maven/bin:$PATH' >> ~postgres/.bash_profile
su - postgres
which mvn
mvn -version
Then, I installed a repo with PL/Java software.

su - postgres
cd /opt/downloads
git clone https://github.com/tada/pljava
cd pljava
git checkout V1_5_0
git checkout -b branch150

# I used maven:
mvn clean install
# I should see this at the end:
# [INFO] BUILD SUCCESS

# I install pljava:
find . -name pljava-pg9.5-amd64-Linux-gpp.jar -print
# I should see:
#         ./pljava-packaging/target/pljava-pg9.5-amd64-Linux-gpp.jar
# So I use it:
su
java -jar ./pljava-packaging/target/pljava-pg9.5-amd64-Linux-gpp.jar

su - postgres

# I look for libjvm.so
find /opt -name libjvm.so -print
# I should see:
# /opt/jdk1.8.0_92/jre/lib/amd64/server/libjvm.so

# I enhance the database
psql

SET pljava.libjvm_location TO '/opt/jdk1.8.0_92/jre/lib/amd64/server/libjvm.so';
CREATE EXTENSION pljava;
\dx

# I test PL/Java:
create function java_max(a integer, b integer)
returns integer
language java volatile
as 'java.lang.Math.max(int, int)'
;
select java_max(2, 4);

# The last call should return 4
Next, I used one line of shell syntax to make the above changes persist between sessions:
echo "pljava.libjvm_location = '/opt/jdk1.8.0_92/jre/lib/amd64/server/libjvm.so'" >> /var/lib/pgsql/9.5/data/postgresql.conf
So, an install of PL/Java requires a number of steps but if you are careful it should not be very difficult.

PL/Java gives you the ability to leverage a large amount of software written by other people and make your database applications much more powerful.

If you have questions, e-me: bikle101@gmail.com


syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me