How to configure WSO2 Api Manager with PostgreSQL

Most Enterprise level application developers tend to use PostgreSQL over MySql as their open source Database of choice, due to it's close compliance with SQL standards, compared to the former. What this means is, if at some point the application owner chooses to go ahead with a closed-source solution such as Oracle for its database, the swap can be accomplished with much of a hassle.

This article will discuss the installation of PostgreSQL and configuration of the same with WSO2 Api Manager, on a linux Debian distribution.

Please note that if you have several installations of PostgreSQL locally or have had trouble getting it to work due to corrupt installations and etc. I would suggest starting off with a clean slate. Hence, you may run the commands listed below to completely uninstall PostgreSQL from your system.

apt-get --purge remove postgresql\*

Now lets delete all configurations.

rm -r /etc/postgresql*
rm -r /var/lib/postgresql*
userdel -r postgres
groupdel postgres

So lets start with the installation. Just type the following command.

sudo apt-get install postgresql

Ok, now that you have installed PostgreSQL, lets set it up. At the time of installation PostgreSQL will create a user by the name "postgres". Executing the following command will log you in to the PSQL admin console

sudo -u postgres psql

Enter the admin user password and you should see a prompt indicating "postgres=#".
Now lets go ahead and create two databases. One for the API Manager specific tables, lets call it "am_db". Another for the WSO2 Carbon tables, lets call it "carbon_db". Remember, even if you use upper case to create the databases, the persisted database name will comprise of only lowercase characters, hence might as well go with lower case at the time of creation.

create database am_db;

create database carbon_db;

Now, we need to create the tables. The API Manager pack comes with the scripts to create each set of tables they can be found inside the "dbscripts" folder of the AM zip.

The carbon DB script is at <AM_home>/dbscripts/postgresql.sql
The carbon DB script is at <AM_home>/dbscripts/apimgt/postgresql.sql

Remember what we learned earlier about the user "postgres" you will need be logged in as this user to execute whatever PostgreSQL scripts that you may wish to run.

Before attempting that, lets change the password of the 'postgres' user. I found it easier to do, rather that scour the internet  to find what the default password was. Just login as admin user and change it to a desirable value via the following command.

passwd postgres;

Lets login as 'potgres' via;

su - postgres;

and execute the two table creation scripts against each Database;

psql -d am_db -a -f <path_to_am_script>/postgresql.sql 

psql -d carbon_db -a -f <path_to_carbon_script>/postgresql.sql 

Voila! now you have the two databases setup. What's left is to configure the "master-datasources.xml" inside <AM_HOME>/repository/conf/datasources/, add the PSQL driver and start the server.

You will need to change the WSO2_CARBON_DB and WSO2AM_DB data sources in the master-datasources file as follows.

 WSO2_CARBON_DB
 The datasource used for registry and user manager
 
  jdbc/WSO2CarbonDB
 
 
  
   jdbc:postgresql://localhost:5432/carbon_db
   postgres
   postgres
   org.postgresql.Driver
   50
   60000
   true
   SELECT 1
   30000
  
 



 WSO2AM_DB
 The datasource used for registry and user manager
 
  jdbc/WSO2AM_DB
 
 
  
   jdbc:postgresql://localhost:5432/am_db
   postgres
   postgres
   org.postgresql.Driver
   50
   60000
   true
   SELECT 1
   30000
  
 

Done? great. Download the PostgreSQL driver relevant for your version from here, and copy the .jar file to the the following location inside the AM Pack.

<AM_HOME>/repository/components/lib/

That's it! go and start API Manager by running "./wso2server.sh" from inside of  <AM_HOME>/bin.