Compiere Postgresql

Installing Compiere with PostgreSQL 8.0 by Armen Rizal (Goodwill Consulting) http://www.goodwill.co.id

Overview This article provides series of guides to install Compiere with PostgreSQL 8.0 on a Linux systems in accordance with the work of E-Evolution (http://www.e-evolution.com.mx) on Compiere Libero. My hat off to E-Evolution!

Part I: Pre-Installation Steps In this article, we are going to store PostgreSQL and Compiere application in /u01/app , while the database files will be in /u02/pg_data. This guide adheres to the Optimal Flexible Architecture (OFA) for the naming conventions used in creating the directory structure. Login as root. First, create a directory to store the applications. # mkdir /u01 # mkdir /u01/app Extract Compiere Libero binary distribution under /u01/app.

Setup Environment Variables On some systems, we must set the environment variables priorly. So let's do it now then. You can also put them in file .profile so they get run everytime you log in. # cat >> /etc/profile <<EOF export JAVA_HOME=/usr/local/bin/j2sdk1.4.2_08 export COMPIERE_HOME=/u01/app/Compiere2 export PATH=$PATH:/u01/app/pgsql/bin:$JAVA_HOME/bin export LD_LIBRARY_PATH= /u01/app/pgsql/lib:$JAVA_HOME/jre/lib/i386:$JAVA_HOME/jre/lib/i386/client EOF

Part II: Installing Java Login in as root. Download Java 2 SDK from Sun Microsystems website. Assume it is J2SDK version 1.4.2.8. Put it in /usr/local/bin. Install with: # ./j2sdk-1_4_2_08-linux-i586.bin And link with: # ln -s /usr/local/bin/j2sdk1.4.2_08/bin/java /usr/bin/java If you run the following command and see the same result, you re doing good. # java -version java version "1.4.2_08"

Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_08-b03) Java HotSpot(TM) Client VM (build 1.4.2_08-b03, mixed mode)

Part III: Installing PostgreSQL The PostgreSQL 8.0.3 sources can be obtained by anonymous FTP from ftp://ftp.postgresql.org/pub/source/v8.0.3/postgresql-8.0.3.tar.gz. Use a mirror if possible. After you have obtained the file, unpack it: # gunzip postgresql-8.0.3.tar.gz # tar xf postgresql-8.0.3.tar This will create a directory postgresql-8.0.3 under the current directory with the PostgreSQL sources. Change into that directory for the rest of the installation procedure. Next, we rebuild the source. # # # #

./configure --prefix=/u01/app/pgsql gmake su gmake install

Now we create PostgreSQL user account and create a new database cluster. # # # # # $

adduser postgres mkdir /u02 mkdir /u02/pg_data chown postgres /u02/pg_data su - postgres /u01/app/pgsql/bin/initdb -D /u02/pg_data

For more information on installing PostgreSQL, please refer to: http://www.postgresql.org/docs/8.0/interactive/installation.html To PostgreSQL works in network is necessary to run postmaster with parameter -i, remember to review client authentification (http://www.postgresql.org/docs/8.0/interactive/client-authentication.html) Example contents of file /u02/pg_data/pg_hba.conf host all all 192.168.0.0 255.255.255.0 trust If all goes well, now we can start the server: $ pg_ctl start -D /u02/pg_data -l logfile -o "-i"

Install Procedure Languages This is necessary for a emulation because PLJava does not support mapping java type $ createlang plpgsql template1

Create User & Database for Compiere $ createuser compiere $ createdb compiere -e unicode

Install PLJava

Login as root and create directory: # mkdir /u01/app/pgsql/pljava # mkdir /u01/app/pgsql/jdbc Download PLJava from http://gborg.postgresql.org/project/pljava/genpage.php?downloads Extract the file into /u01/app/pgsql/pljava # gunzip pljava-i686-pc-linux-gnu-pg8.0-1.1.0.tar.gz # cd /u01/app/pgsql/pljava # tar xf pljava-i686-pc-linux-gnu-pg8.0-1.1.0.tar.gz You may also want to download PostgreSQL's JDBC Library from http://jdbc.postgresql.org/download/postgresql-8.0310.jdbc3.jar. Put it into /u01/app/pgsql/jdbc Add parameters to your file /u02/pg_data/postgresql.conf: # define "pljava" as a custom variable class. This is a comma separated # list of names. # custom_variable_classes = 'pljava' # define the class path that the JVM will use when loading the # initial library. Only meaningful for non GJC installations # pljava.classpath = '/u01/app/pgsql/pljava/pljava.jar' # Set the size of the prepared statement MRU cache # #pljava.statement_cache_size = 10 # If true, lingering savepoints will be released on function exit. If false, # the will be rolled back # pljava.release_lingering_savepoints = true # Define startup options for the Java VM. # pljava.vmoptions = '-Xmx64M -Dbackchannel.port=48' # Setting debug to true will cause the postgres process to go # into a sleep(1) loop on its first call to java. This is # only useful if you want to debug the PLJava internal C code. # pljava.debug = false Also still in /u02/pg_data/postgresql.conf, you might want to set the dynamic_library_path. Scroll up a little bit, you will find the entry dynamic_library_path = $libdir . Uncomment the line and change that to point to where you install PLJava. dynamic_library_path = '/u01/app/pgsql/pljava' And then scroll up even more until you find the Connection settings. Uncomment these lines and make the necessary changes. This is when you need to connect to PostgreSQL from other computers on the network. listen_address = '192.168.0.10' port = '5432'

Install PLJava into Database # java -cp /u01/app/pgsql/pljava/deploy.jar:/u01/app/pgsql/jdbc/postgresql-8.0310.jdbc3.jar org.postgresql.pljava.deploy.Deployer -install -user compiere -database compiere # su - postgres $ pg_ctl stop -D /u02/pg_data waiting for postmaster to shut down.... done postmaster stopped $ pg_ctl start -D /u02/pg_data -l logfile -o "-i" $ psql -U compiere -d compiere Welcome to psql 8.0.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit compiere=#CREATE SCHEMA compiere; compiere=# SET search_path TO compiere,sqlj; compiere=# SELECT sqlj.install_jar('file:///u01/app/Compiere2/lib/sqlj.jar', 'sqlj', true); compiere=# SELECT sqlj.set_classpath('compiere', 'sqlj'); compiere=# \q

Part IV: Installing Compiere Now login as root. Assuming the previous installation steps were successfully done by you (e.g. PostgreSQL is running), execute the Compiere setup script. # cd /u01/app/Compiere2 # ./RUN_setup.sh Now we test our environment. # utils/RUN_Env.sh if ok, go ahead importing data # psql -U compiere

d compiere < $COMPIERE_HOME/data/CompierePGSQL.dmp

If all is ok, we can test the Compiere server. # ./RUN_Server2.sh And now the client: # ./RUN_Compiere2.sh If it runs ok as expected, congratulations! You have succeeded installing Compiere with PostgreSQL.

Reference: 1. Compiere Libero E-Evolution 2. Installing Compiere on Red Hat Enterprise Linux 3

Armen Rizal