How to install SpagoBI on a Ubuntu server and use only PostgreSQL, without the demo database

This title is pretty long, uh? So, I recently worked on installing this tool (which actually does not need an installation, whatever..) and set it up without its demo database, using therefore a different database: PostgreSQL. Check it out ;).

We need to install Java, I tried both Java7 and Java8 and both version works well. Haven’t had the ability to make more tests on which version to prefer, so I decided to go for Java8.

I’m using the folder /opt to store the working-directoy of SpagoBI:

cd /opt  
wget http://download.forge.ow2.org/spagobi/postgres-dbscripts-5.2.0_24032016.zip  
wget http://download.forge.ow2.org/spagobi/All-In-One-SpagoBI-5.2.0_11042016.zip

At the moment I am writing this, 24.05.2017, both versions are at their latest release. I’d suggest to check them before downloading.

When the download ends, unzip both the folder:

unzip All-In-One-SpagoBI-5.2.0_11042016.zip
unzip postgres-dbscripts-5.2.0_24032016.zip

If you don’t have unzip install: apt-get install unzip

I prefer to rename the working directory of SpagoBI into something more easy to manage, and add executable permissions to the script file inside bin:

mv All-In-One-SpagoBI-5.2.0_24032016 spagobi  
cd spagobi
chmod +x bin/*.sh

To get rid of the demo database, this command below is needed but unfortunately not enough, we will see later how to not load the demo db, for now:

vi bin/SpagoBiStartup.sh 
 
[ And comment (or oremove) ] 
cd ../database  
./start.sh &  

I’d delete now the database folder:

rm -rf database 

And edit the properties of catalina.sh, to setup the ram size of SpagoBI in Tomcat:

vi bin/catalina.sh 
 
[ And add at the end of the commented section ]  
JAVA_OPTS="$JAVA_OPTS -Xms2048m -Xmx2048m"

If you’re using Java7, then use this:

JAVA_OPTS="$JAVA_OPTS -Xms2048m -Xmx2048m -XX:PermSize=512m -XX:MaxPermSize=512m"

At least, 2GB. I did some tests on a VM with 512MB ram and increased until 3,5GB, and with the VM’s ram I was increasing the Java_VM’s ram for Tomcat, until I was able to start SpagoBI. Do NOT give 1GB. 2GB is the minimum.

Edit the server.xml now, but best if you take a backup of the file:

cp conf/server.xml conf/server.xml.original
vi conf/server.xml

There are many rows to edit in this file.
The simplest is to delete ALL the section and use this that comes directly from my server:

  <GlobalNamingResources>
                          
    <Environment name="spagobi_resource_path" type="java.lang.String" value="${catalina.base}/resources"/>
    <Environment name="spagobi_sso_class" type="java.lang.String" value="it.eng.spagobi.services.common.FakeSsoService"/>
    <Environment name="spagobi_service_url" type="java.lang.String" value="http://192.168.0.100:8080/SpagoBI"/>   
    <Environment name="spagobi_host_url" type="java.lang.String" value="http://192.168.0.100:8080"/>
    
    <Resource name="jdbc/spagobi" auth="Container"  
              type="javax.sql.DataSource"  
              driverClassName="org.postgresql.Driver"  
              url="jdbc:postgresql://192.168.0.100:5432/spagobi"  
              username="postgres"  
              password="myPassword" />

  </GlobalNamingResources>

Just after GlobalNamingResources, starts the Service section.
Again, use mine. It is exactly the same you find in your server.xml but clean from comments. Use your server’s IP once again.

  <Service name="Catalina">

    <Connector port="8080" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="8443" 
                           URIEncoding="UTF-8"
                           />
    <Connector port="8009" URIEncoding="UTF-8" protocol="AJP/1.3" redirectPort="8443" />

    <Engine name="Catalina" defaultHost="192.168.0.100">
      <Realm className="org.apache.catalina.realm.LockOutRealm">
        <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
               resourceName="UserDatabase"/>
      </Realm>
      <Host name="192.168.0.100"  appBase="webapps"
            unpackWARs="true" autoDeploy="true">
        <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
               prefix="localhost_access_log." suffix=".txt"
               pattern="%h %l %u %t &quot;%r&quot; %s %b" />
      </Host>
    </Engine>
  </Service>

In the same folder, conf, there is another file to edit: context.xml.
These are settings that are applied to every engine inside webapps folder, every engine have its own, this is the general.

You can make a backup of the original and wipe its content, because I’m going to paste here the content you’ll want to use:

cp conf/context.xml conf/context.xml.original
>conf/context.xml
vi conf/context.xml

The content of the file is:

<?xml version='1.0' encoding='utf-8'?>
<Context docBase="SpagoBIProject" path="/opt/spagobi" privileged="true" reloadable="true" source="org.eclipse.jst.j2ee.server:SpagoBIProject
">
   <ResourceLink global="jdbc/spagobi" name="jdbc/spagobi" type="javax.sql.DataSource"/>     
   <ResourceLink global="spagobi_resource_path" name="spagobi_resource_path" type="java.lang.String"/>     
   <ResourceLink global="spagobi_sso_class" name="spagobi_sso_class" type="java.lang.String"/>     
   <ResourceLink global="spagobi_host_url" name="spagobi_host_url" type="java.lang.String"/>
    <!-- Default set of monitored resources -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <Manager pathname="" />

</Context>

Now that both the file in conf directory are updated, there are 3 more in another location: /opt/spagobi/webapps/SpagoBI/WEB-INF/classes/.

hibernate.cfg.xml 
jbpm.hibernate.cfg
quartz.properties 

Basically, the only thing to edit in these file is the string that says to which database to connect to. We are going to use PostgreSQL.

vi /opt/spagobi/webapps/SpagoBI/WEB-INF/classes/hibernate.cfg.xml

[ find, in the very first lines of the file ]
<property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>

[ and replace with ]
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>

Which is inside the commented section with all the strings for every database.

Do the same for the file jbpm.hibernate.cfg.

quartz.properties instead, is a bit different. One may chose to not use this file, it is used for Twitter and other social (I honestly do not know how one would use it). But…

vi /opt/spagobi/webapps/SpagoBI/WEB-INF/classes/quartz.properties

[ modify the section START JOB STORE into using this ]
#-------------- START JOB STORE --------------------------------------------
#org.quartz.jobStore.class = org.quartz.simpl.RAMJobStore
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.dataSource.quartz.jndiURL=java:comp/env/jdbc/spagobi

org.quartz.dataSource.quartz.driver = org.postgresql.Driver
org.quartz.dataSource.quartz.URL = jdbc:postgresql://192.168.0.100:5432/spagobi
org.quartz.dataSource.quartz.user = postgres
org.quartz.dataSource.quartz.password = myPassword
org.quartz.dataSource.quartz.maxConnections = 5

[ then, comment this row ]
# Hsqldb delegate class
# org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.HSQLDBDelegate

[ and uncomment the one for PostgreSQL ]
# Postgres delegate class
org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Almost ready, we now need to create the Postgres database!
Log in as postgres user:

su - postgres

And run these command to create the database, then exit from psql and run a SQL from a text file to update the DB:

psql

It will print something similar to:
psql (9.3.16)
Type “help” for help.

postgres=#
So, create this database:

create database spagobi;

And exit from psql mode using \q and ENTER

[ Run as postgres user ]
psql -d spagobi -a -f /opt/postgres/PG_create.sql  
psql -d spagobi -a -f /opt/postgres/PG_create_quartz_schema.sql

How to install Postgres, configure, secure […] is out of this document. I suppose you have Postgres installed already.

Everything done! .. more or less. To start SpagoBI, you need to run:

cd /opt/spagobi/bin
./SpagoBIStartup.sh 

Note that this won’t let the application start when the system start. There are quite a few results if you use Google, I let this task up to your preferences.

I suggest to give your server at least 4GB of ram and 4 cores.
What I noticed, is that the shutdown.sh script won’t quit the application and release the ram, I have to use

ps aux | grep javac

[ find the PID ]
kill PID

ABSOLUTELY BARBARIC! Haven’t yet find interest into fixing this.