Changing Default DB of WSO2 API Manager to PostgreSQL

WSO2 products ships with a embedded H2 database instance. When it comes to API manager there are three databases that are being used in API manager, by default these DBs are created in an Embedded H2 instance. So in most cases you will need to change these default databases, So in this post I will be explaining how to install PostgreSQL and change the default databases to PostgreSQL.

So these are DBs that are being used,

• WSO2CARBON_DB-The datasource used for registry and user manager
• WSO2AM_STATS_DB-The datasource used for getting statistics to API Manager
• WSO2AM_DB-The datasource used for API Manager database

Installing PostgreSQL 

If you have apt-get you directly install postgre by executing following commands

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Now setup the password by issuing the following commands

sudo -u postgres psql template1

Type the following,

ALTER USER postgres with encrypted password 'your_password';

Press ctrl-d to exit.

Configuring PostgreSQL 

Configure MD5 Autentication

open /etc/postgresql/9.3/main/pg_hba.conf , Make sure that you replace the x with your postgre version

sudo vim /etc/postgresql/X.X/main/pg_hba.conf

Update the following entry

local all postgres

Entry to

local all postgres md5

To enable TCP/IP connections edit the following

sudo vim /etc/postgresql/X.X/main/postgresql.conf

Locate the following line

#listen_addresses = 'localhost'

and un-comment it like following

listen_addresses = 'localhost'

Restart the postgresql

sudo service postgresql restart

Using PosgreSQL

To use PostgreSQL you have to login as one of the posgreSQL users, as following

sudo -i -u postgres

Then just execute following

psql

Installing PGAdmin3 GUI console for PostgreSQL

Execute the following command to install PGADMIN3

sudo apt-get install pgadmin3

Now open a new terminal and execute the following command to open PGadmin

pgadmin3

Now you can connect to the DB and do any changes you need.

The admin console will look like following,

Changing the DBs of API-M 

Now create following three DBs

WSO2CARBON_DB
WSO2AM_STATS_DB
WSO2AM_DB


Now add add the ProsgreSQL driver which can be found here to <PRODUCT_HOME>/repository/components/lib directory

Now open the <PRODUCT_HOME>/repository/conf/datasources/master-datasources.xml and add the following content, make sure that you change the usernames and password accordingly.

<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">

<providers>
    <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
</providers>

<datasources>
<datasource>
      <name>WSO2CARBON_DB1</name>
           <description>The datasource used for registry and user manager</description>
            <jndiConfig>
              <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                   <url>jdbc:postgresql://localhost:5432/WSO2CARBON_DB1</url>
                   <defaultAutoCommit>false</defaultAutoCommit>
                    <username>postgres</username>
                    <password>wso2root</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
     
        <datasource>
            <name>WSO2AM_DB1</name>
            <description>The datasource used for API Manager database</description>
            <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://localhost:5432/WSO2AM_DB1</url>
                    <defaultAutoCommit>false</defaultAutoCommit>
                    <username>postgres</username>
                    <password>wso2root</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
     
        <datasource>
            <name>WSO2AM_STATS_DB1</name>
            <description>Stat DB</description>
            <jndiConfig>
                <name>jdbc/WSO2AM_STATS_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://localhost:5432/WSO2AM_STATS_DB1</url>
                    <defaultAutoCommit>false</defaultAutoCommit>
                    <username>postgres</username>
                    <password>wso2root</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
    </datasources>
</datasources-configuration>

Now start your server with the following command

./wso2server.sh -Dsetup

This will create necessary tables etc.