fb
WSO2 Products 12 minutes

WSO2TORIAL: Using PostgreSQL in a WSO2 Cluster Setup

Luis Bustamante
Luis Bustamante
Integration Consultant
User DB
Scroll

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

User DB.png

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_wso2db

other_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

<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>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 3rd 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.