WSO2TORIAL: Using PostgreSQL in a WSO2 Cluster Setup

Clustering WSO2 Carbon

When clustering any WSO2 product, one of the required steps is to set up the databases for the instances. Most of the available examples on the WSO2 documentation use MySQL (or Maria DB) as the default database, however if you want to use another one, examples may be hard to find or incomplete.This document is written as a quick guide to set up PostgreSQL as the defined database for a WSO2 cluster in a simple and fast way using only the PostgreSQL interactive terminal ‘psql’ and avoiding any extra tooling installation in your system. This setup is based on the WSO2 Database clustering example found in https://docs.wso2.com/display/CLUSTER44x/Setting+up+the+Database

Database setup description

The official WSO2 Database clustering example found in https://docs.wso2.com/display/CLUSTER44x/Setting+up+the+Database displays a Manager node and Worker node, each with its own CARBON_DBs for local registry plus one shared USER_DB and a shared REGISTRY_DB. The following steps will set up a similar cluster assuming PostgreSQL as the Relational Database Management System.

The next table and diagram describe the installation:

Database	Description
USER_DB	Shared user store and authorization manager
REGISTRY_DB	Shared database for config and governance registry
REGMANAGER_DB	Local registry space in the manager node
REGWORKER_DB	Local registry space in the worker node

Creating the Databases

The following steps will create the Database in PostgreSQL 9+. It is assumed that PostgreSQL interactive terminal ‘psql’ is available and the database is listening on (the default) port: 5432. Previous versions of PostrgreSQL do not support completely the GRANT ALL command so it would have to be replaced by granting each one of the privileges separately.

1. Log in to plsql (other parameters may be needed if the database is located in a remote host or the default postgres user is not going to be used)

$plsql –p5432

2. Create the Databases for the WSO2 instances and the registry admin user (regadmin)

postgres=# CREATE DATABASE user_db;

postgres=# CREATE DATABASE registry_db;

postgres=# CREATE DATABASE regmanager_db;

postgres=# CREATE DATABASE regworker_db;

postgres=# CREATE USER regadmin WITH PASSWORD 'regadmin';

3. Populate the Databases using the WSO2 PostgreSQL script located in <CARBON_HOME>/dbscripts/postgresql.sql and grant control to regadmin.

Note: <CARBON_HOME> is the fully qualified installation path of the WSO2 instance.

postgres=# c user_db

user_db=# i <CARBON_HOME>/dbscripts/postgresql.sql

user_db=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO regadmin;

user_db=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO regadmin;

postgres=# c registry_db

registry_db=# i <CARBON_HOME>/dbscripts/postgresql.sql

registry_db=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO regadmin;

registry_db=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO regadmin;

postgres=# c regmanager_db

regmanager_db=# i <CARBON_HOME>/dbscripts/postgresql.sql

regmanager_db=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO regadmin;

regmanager_db=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO regadmin;

postgres=# c regworker_db

regworker_db=# i <CARBON_HOME>/dbscripts/postgresql.sql

regworker_db=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO regadmin;

regworker_db=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO regadmin

Note: It is important to know that the GRANT ALL command in PostgreSQL only grants the privileges on already existing schemas and that is why it is here executed at the end of the Schema creation. If you are in need of creating a schema where the ‘regadmin’ will have to create or remove new tables or sequences, the following command has to be used:

postgres=# c other_wso2dbother_wso2db=# ALTER DEFAULT PRIVILEGES FOR ROLE regadmin IN SCHEMA PUBLIC GRANT ALL PRIVILEGES ON TABLES TO regadmin;

other_wso2db=# ALTER DEFAULT PRIVILEGES FOR ROLE regadmin IN SCHEMA PUBLIC GRANT ALL PRIVILEGES ON SEQUENCES TO regadmin;

5. Download the PostgreSQL JDBC library for the installed DB version from https://jdbc.postgresql.org/ and copy it to <CARBON_HOME>/repository/components/lib for each of the WSO2 instances that access the postgresql database(s).

Databases are now ready and it can be proceeded to update the connection parameters for the nodes.

6. Configure the <MANAGER_CARBON_HOME>/repository/conf/datasources/master-datasources.xml for the manager node

<datasource> <name>regmanager_db</name>
<description>The db used for registry-local</description>
<jndiConfig>
    <name>jdbc/WSO2CarbonDB</name>
</jndiConfig>
<definition type="RDBMS">
  <configuration>
      <url>jdbc:postgresql://[POSTGRESQL_HOST]:5432/regmanager_db</url>
      <driverClassName>org.postgresql.Driver</driverClassName>
      <username>regadmin</username>
      <password>regadmin</password>
      <maxActive>80</maxActive>
      <minIdle>5</minIdle>
      <maxWait>60000</maxWait>
      <defaultAutoCommit>false</defaultAutoCommit>
      <testOnBorrow>true</testOnBorrow>
      <validationInterval>30000</validationInterval>
    </configuration>
</definition>
</datasource><datasource>
<name>registry_db</name>
<description>The db used for registry-config/governance</description>
<jndiConfig>
    <name>jdbc/WSO2RegistryDB</name>
</jndiConfig><definition type="RDBMS">
    <configuration>
      <url>jdbc:postgresql://[POSTGRESQL_HOST]:5432/registry_db</url>
      <driverClassName>org.postgresql.Driver</driverClassName>
      <username>regadmin</username>
      <password>regadmin</password>
      <maxActive>80</maxActive>
      <minIdle>5</minIdle>
      <maxWait>60000</maxWait>
      <defaultAutoCommit>false</defaultAutoCommit>
      <testOnBorrow>true</testOnBorrow>
      <validationInterval>30000</validationInterval>
    </configuration>
</definition>
</datasource><datasource>
<name>user_db</name>
<description>The db used for registry and user manager</description>
<jndiConfig>
    <name>jdbc/WSO2UMDB</name>
</jndiConfig>
<definition type="RDBMS">
    <configuration>
      <url>jdbc:postgresql://[POSTGRESQL_HOST]:5432/user_db</url>
      <driverClassName>org.postgresql.Driver</driverClassName>
      <username>regadmin</username>
      <password>regadmin</password>
      <maxActive>80</maxActive>
      <minIdle>5</minIdle>
      <maxWait>60000</maxWait>
      <defaultAutoCommit>false</defaultAutoCommit>
      <testOnBorrow>true</testOnBorrow>
      <validationInterval>30000</validationInterval>
    </configuration>
</definition>
</datasource>

6. Configure the <WORKER_CARBON_HOME>/repository/conf/datasources/master-datasources.xml for the worker node

<datasource>
<name>regworker_db</name>
<description>The db used for registry-local</description>
<jndiConfig>
    <name>jdbc/WSO2CarbonDB</name>
</jndiConfig>
<definition type="RDBMS">
    <configuration>
      <url>jdbc:postgresql://[POSTGRESQL_HOST]:5432/regworker_db</url>
      <driverClassName>org.postgresql.Driver</driverClassName>
      <username>regadmin</username>
      <password>regadmin</password>
      <maxActive>80</maxActive>
      <minIdle>5</minIdle>
      <maxWait>60000</maxWait>
    <defaultAutoCommit>false</defaultAutoCommit>
      <testOnBorrow>true</testOnBorrow>
      <validationInterval>30000</validationInterval>
    </configuration>
</definition>
</datasource><datasource>
<name>registry_db</name>
<description>The db used for registry-config/governance</description>
<jndiConfig>
    <name>jdbc/WSO2RegistryDB</name>
</jndiConfig>
<definition type="RDBMS">
    <configuration>
      <url>jdbc:postgresql://[POSTGRESQL_HOST]:5432/registry_db</url>
      <driverClassName>org.postgresql.Driver</driverClassName>
      <username>regadmin</username>
      <password>regadmin</password>
      <maxActive>80</maxActive>
      <minIdle>5</minIdle>
      <maxWait>60000</maxWait>
      <defaultAutoCommit>false</defaultAutoCommit>
      <testOnBorrow>true</testOnBorrow>
      <validationInterval>30000</validationInterval>
    </configuration>
</definition>
</datasource><datasource>
<name>user_db</name>
<description>The db used for registry and user manager</description>
<jndiConfig>
    <name>jdbc/WSO2UMDB</name>
</jndiConfig>
<definition type="RDBMS">
    <configuration>
      <url>jdbc:postgresql://[POSTGRESQL_HOST]:5432/user_db</url>
      <driverClassName>org.postgresql.Driver</driverClassName>
      <username>regadmin</username>
      <password>regadmin</password>
      <maxActive>80</maxActive>
      <minIdle>5</minIdle>
      <maxWait>60000</maxWait>
      <defaultAutoCommit>false</defaultAutoCommit>
      <testOnBorrow>true</testOnBorrow>
      <validationInterval>30000</validationInterval>
    </configuration>
</definition>
</datasource>

7. Update the dataSource property in <CARBON_HOME>/repository/conf/user-mgt.xml for the manager node and the worker node.

<Property name="dataSource">jdbc/WSO2UMDB</Property>

8. Configure the registry mounting for the manager node by editing the file <MANAGER_CARBON_HOME>/repository/conf/registry.xml

<currentDBConfig>wso2registry</currentDBConfig>
<readOnly>false</readOnly>
<enableCache>true</enableCache>
<registryRoot>/</registryRoot><dbConfig name="wso2registry">
<dataSource>jdbc/WSO2CarbonDB</dataSource>
</dbConfig><dbConfig name="sharedregistry">

   <dataSource>jdbc/WSO2RegistryDB</dataSource>

</dbConfig>

<remoteInstance url="https://localhost:9443/registry">

<id>instanceid</id>

<dbConfig>sharedregistry</dbConfig>

<readOnly>false</readOnly>

<enableCache>true</enableCache>

<registryRoot>/</registryRoot>

<cacheId>regadmin@jdbc:postgresql://[POSTGRESQL_HOST]:5432/registry_db  </cacheId>

</remoteInstance>

<mount path="/_system/config" overwrite="true">

<instanceId>instanceid</instanceId>

<targetPath>/_system/config</targetPath>

</mount>

<mount path="/_system/governance" overwrite="true">

<instanceId>instanceid</instanceId>

<targetPath>/_system/governance</targetPath>

</mount>

9. Configure the registry mounting for the worker node by editing the file <WORKER_CARBON_HOME>/repository/conf/registry.xml

   <dataSource>jdbc/WSO2RegistryDB</dataSource>

</dbConfig>

<remoteInstance url="https://localhost:9443/registry">

<id>instanceid</id>

<dbConfig>sharedregistry</dbConfig>

<readOnly>true</readOnly>

<enableCache>true</enableCache>

<registryRoot>/</registryRoot>

<cacheId>regadmin@jdbc:postgresql://[POSTGRESQL_HOST]:5432/registry_db  </cacheId>

</remoteInstance>

<mount path="/_system/config" overwrite="true">

<instanceId>instanceid</instanceId>

<targetPath>/_system/config</targetPath>

</mount>

<mount path="/_system/governance" overwrite="true">

<instanceId>instanceid</instanceId>

<targetPath>/_system/governance</targetPath>

</mount>

Final note: During the example the default jndi Config name jdbc/WSO2CarbonDB was used. If this name is changed then it will have to be updated in the following files:

<CARBON_HOME>/conf/datasources/master-datasources.xml: <name>jdbc/WSO2CarbonDB</name>
<CARBON_HOME>/conf/identity/identity.xml: <Name>jdbc/WSO2CarbonDB</Name>
<CARBON_HOME>/conf/registry.xml: <dataSource>jdbc/WSO2CarbonDB</dataSource>
<CARBON_HOME>/conf/user-mgt.xml:<Property name=”dataSource”>jdbc/WSO2CarbonDB</Property>

This is true for both Worker and Manager nodes, each of course with it’s own settings.

Conclusion

When setting up a WSO2 carbon cluster, the configuration files are pretty standard in the list of parameters that have to be adjusted. However, the database system in which the cluster will rely on may be based on different technologies than just the default MySQL concepts exposed in the WSO2 documentation. This document aimed to help set up as fast as possible a PostgreSQL database for clustering WSO2 without having to rely on 3^rd party tools or to go through the extensive PostgreSQL documentation.

Read also our other WSO2 tutorials and blogs, written by our WSO2 Gurus. In case you need WSO2 support, contact the Yenlo WSO2 Guru team to get WSO2 Development Support or WSO2 Operational Support. Of course we do deliver excellent WSO2 training services as well, based on reallife WSO2 tutorials.

About Luis Bustamante

Luis is a Master of Computer Science with around 10 years of experience in a big variety of Software Development and Enterprise Applications Integration projects. During his career he has performed roles as Integration Consultant, Software Engineer, Requirements Engineer, and Web Engineer. He is always enthusiastic about new technologies and challenges.

All blogs by Luis

WSO2TORIAL: Using PostgreSQL in a WSO2 Cluster Setup

In this article

Published

By

Categories

Tags

Clustering WSO2 Carbon

Database setup description

Creating the Databases

Conclusion

About Luis Bustamante

Other Related Blogs

Our solutions for your Digital Transformation

Our services to facilitate the digital transformation of your business

Our Technologies for your Digital Transformation

Solutions by industry

Learnings

Search

What kind of content are you looking for?

WSO2TORIAL: Using PostgreSQL in a WSO2 Cluster Setup

In this article

Published

By

Categories

Tags

Care to share

Clustering WSO2 Carbon

Database setup description

Creating the Databases

Conclusion

About Luis Bustamante

Other Related Blogs