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  

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:


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/ 
[ And comment (or oremove) ] 
cd ../database  
./ &  

I’d delete now the database folder:

rm -rf database 

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

vi bin/ 
[ 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:

    <Environment name="spagobi_resource_path" type="java.lang.String" value="${catalina.base}/resources"/>
    <Environment name="spagobi_sso_class" type="java.lang.String" value=""/>
    <Environment name="spagobi_service_url" type="java.lang.String" value=""/>   
    <Environment name="spagobi_host_url" type="java.lang.String" value=""/>
    <Resource name="jdbc/spagobi" auth="Container"  
              password="myPassword" />


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"
    <Connector port="8009" URIEncoding="UTF-8" protocol="AJP/1.3" redirectPort="8443" />

    <Engine name="Catalina" defaultHost="">
      <Realm className="org.apache.catalina.realm.LockOutRealm">
        <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
      <Host name=""  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" />

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
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 -->

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


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


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. 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/

[ 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.driver = org.postgresql.Driver
org.quartz.dataSource.quartz.URL = jdbc:postgresql://
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

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:


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

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

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 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.