Configuring WSO2 BPS with PostgreSQL database

Applies To:

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 (For E.g: 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. https://www.enterprisedb.com/products-services-training/pgdownload#windows
2. https://jdbc.postgresql.org/download/postgresql-9.0-801.jdbc4.jar
3. https://wso2.org/blog/sumedha/9154

Author:

Harshana Martin

Senior Software Engineer, WSO2 Inc