Configuring WSO2 BPS with PostgreSql database

WSO2 Carbon Servers including WSO2 BPS use embedded H2 database as the data source by default. But you can plug other databases as external data source for Carbon Servers. WSO2 Carbon based servers supports Oracle, MSSQL, MySQL,PostgreSql, etc.

In this tutorial we explain how to configure WSO2 BPS to use PostgreSql database as it's external data source.


Applies To:

WSO2 BPS	2.1.2
Oracle JDK	1.6.0_26
PostgreSql	9.0.4-1

Table of Content

1. Installing PostgreSql in your system
2. Configuring PostgreSql database
3. Configuring BPS server with PostgreSql database
4. Finalizing Setup

Installing PostgreSql in your system

1. Downloaded PostgreSql 9.0.4-1 from [1].
2. Downloaded PostgreSql JDBC driver from [2].
3. Install the PostgreSql instance in your operating system.
4. Configure the PostgreSql server. (E.g: Username, Password, Port,etc)
5. Log in to the PostgreSql instance using pgAdmin3.

Configuring PostgreSql database

1. Create a new database called bpsds with default database settings.
2. Copy the content of postgresql.sql dbscript which resides in <BPS_HOME>/dbscripts folder to the SQL Query tool which comes with pgAdmin3.
3. Execute the SQL Script.in pgAdmin3
4. Browse bpsds database via pgAdmin3 and check whether there are bunch of tables created in it.

Configuring BPS server with PostgreSql database

Now we need to update the configuration files in order to set PostgreSql as the database for the BPS [3]

1. Database configuration for Registry
2. Database configuration for User Manager
3. Configuring BPS Data Source

Database configuration for Registry

Modify database configuration in <BPS_HOME>/repository/conf/registry.xml as follows.

    <dbConfig name="wso2registry"> 
    <url>jdbc:postgresql://localhost/[DATABASE-NAME]</url>
    <userName>[DATABASE_USERNAME]</userName>
    <password>[DATABASE_PASSWORD]</password>
    <driverName>org.postgresql.Driver</driverName>
    <maxActive>40</maxActive>
    <maxWait>60000</maxWait>
    <minIdle>5</minIdle>
    </dbConfig>

Database configuration for User Manager.

Modify database configuration in <BPS_HOME>/repository/conf/user-mgt.xml as follows.

    <Configuration>
    <AdminRole>admin</AdminRole>
    <AdminUser>
    <UserName>admin</UserName>
    <Password>admin</Password>
    </AdminUser>
    <EveryOneRoleName>everyone</EveryOneRoleName> <!-- By default users in thsi role sees the registry root -->
    <ReadOnly>false</ReadOnly>
    <MaxUserNameListLength>500</MaxUserNameListLength>
    <Property name="url">jdbc:postgresql://localhost/[DATABASE_NAME]</Property>
    <Property name="userName">[DATABASE_USERNAME]</Property>
    <Property name="password">[DATABASE_PASSWORD]</Property>
    <Property name="driverName">org.postgresql.Driver</Property>
    <Property name="maxActive">40</Property>
    <Property name="maxWait">60000</Property>
    <Property name="minIdle">5</Property>
    </Configuration>

Configuring BPS Data Source

You need to configure reusable data source in BPS. Modify reusable data source configuration in <BPS_HOME>/repository/conf/datasources.properties as follows.

    synapse.datasources.bpsds.driverClassName=org.postgresql.Driver
    synapse.datasources.bpsds.url=jdbc:postgresql://localhost/[DATABASE-NAME]
    synapse.datasources.bpsds.username=[DATABASE_USERNAME]
    synapse.datasources.bpsds.password=[DATABASE_PASSWORD]

Finalizing Setup
1. Now copy the downloaded PostgreSql driver to the <BPS_HOME>/repository/components/lib
2. Go to the <BPS_HOME>/bin folder and start the BPS with wso2server.bat or wso2server.sh command and check whether your BPS instance starts without any error.

References:

1. http://www.enterprisedb.com/products-services-training/pgdownload#windows
2. http://jdbc.postgresql.org/download/postgresql-9.0-801.jdbc4.jar
3. http://wso2.org/blog/sumedha/9154

Author:

Harshana Martin
Senior Software Engineer, WSO2 Inc