Installing WSO2 BPS on PostgreSQL

BPS requires two database schema's. One is for the registry and one for BPEL, Human Task engine. So we will need to create two database schema's on postgresql and grant permissions. Lets name the registry database as bpsRegistry and orchestration engine database as bps.
Step 1.
Install postgresql.
$ sudo apt-get install postgresql
Step 2.
Configure postgresql 
Edit postgresql.conf file located in /etc/postgresql/9.1/main directory and un-comment the following line
listen_addresses = 'localhost'

Step 3.

When postgres sql is installed, a user named postgres will be created. We need to login as this user in order to create our databases, add our default role to postgres and grant privileges.First login as root and then  change user to postgres user.

$ sudo su

$ su postgres

Step 4

Connect to the database and configure roles.

$ psql template1

Add default user to postgres.

template1=# CREATE USER nandika WITH PASSWORD 'password';

Step 5

Create the two databases.

template1=# CREATE DATABASE bps;

template1=# CREATE DATABASE bpsRegistry;

Now grant all privileges to database.

template1=# GRANT ALL PRIVILEGES ON DATABASE bps to nandika;

template1=# GRANT ALL PRIVILEGES ON DATABASE bpsRegistry to nandika;

you can use \q to quit.

Step 6.

Now, test the databases and run the db scripts found in wso2bps-3.0.0/dbscripts directory to create the db tables.

$ psql -d bpsregistry -U nandika;

bpsRegistry=>

Now run the postgresql.sql script to create the tables.

bpsRegistry=> \i /home/nandika/wso2bps-3.0.0/dbscripts/postgresql.sql

This should execute the sql script and create the database tables. 

Similarly, execute the postgres.sql in dbscripts/bps directory to create the bps database tables.

Note : you can use \? to view the available postgres sql commands.

Now we are done with configuring postgres sql. Lets configure bps db connection urls.

Step 7.

Go to repository/conf/datasources directory and edit master-datasources.xml and replace the existing H2 database configurations with corresponding postgresql configuration.

 
        <datasource>
            <name>BPSREG_DB</name>
            <description>The datasource used for registry</description>
            <jndiConfig>
                <name>jdbc/BPSRegDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://localhost/bpsregistry</url>
                    <username>nandika</username>
                    <password>password</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
            

Next , open the datasources.properties file in repository/conf directory and all following.

synapse.datasources.bpsds.registry=JNDI
synapse.datasources.bpsds.type=BasicDataSource
synapse.datasources.bpsds.driverClassName=org.postgresql.Driver
synapse.datasources.bpsds.url=jdbc:postgresql://127.0.0.1:5432/bps?user=nandika&password=password
synapse.datasources.bpsds.username=nandika
synapse.datasources.bpsds.password=password
synapse.datasources.bpsds.validationQuery=SELECT 1
synapse.datasources.bpsds.dsName=bpsds
synapse.datasources.bpsds.maxActive=100
synapse.datasources.bpsds.maxIdle=20
synapse.datasources.bpsds.maxWait=10000

Next, download and copy postgres sql driver to repository/components/lib directory. Now we have finished configurations. Go to bin directory and run wso2server.sh to start BPS.