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:
Shared user store and authorization manager
Shared database for config and governance registry
Local registry space in the manager node
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)
2. Create the Databases for the WSO2 instances and the registry admin user (regadmin)
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
<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>
This is true for both Worker and Manager nodes, each of course with it’s own settings.
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.
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.