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

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

Posted on  May 24, 2017  by Juri Calleri

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.

This entry was posted in Personal works by Juri. Bookmark the permalink.

转载：http://www.juricalleri.net/?p=948