fb
API Management 4 min

Develop with PostgreSQL, Docker and WSO2 API Manager

Steve Liem
Steve Liem
Technical Consultant
Develop with Docker PostgreSQL and WSO2 APIM
Scroll

Develop with Docker PostgreSQL and WSO2 APIMInstalling PostgreSQL is not as simple as you think. Just look at the many different pages on the internet when searching for how to install PostgreSQL on your system. Especially the many different types of problems that people have with these installations.

I’ve tried a simple ‘brew install postgres’. Everything works fine. But you will get a very basic, not configured, installation. You have to find out how to get the database working with the database psql client. When you simply want to have a local server or client to be able to communicate with Postgres a lot of configuration needs to be done.

But there is a simpler way. The official PostgreSQL image is already preconfigured and ready to use. Nothing to be done there, then just to figure out how to start up a docker container.

The official Docker Postgres image repository URL has nice documentation which pretty much explains everything about how to spin up a container and how to link other applications that also run on the same docker host. But the reason why I write this blog is to tell about what is not documented!

WSO2 API Manager using PostgreSQL

What if you want to run a WSO2 API Manager container on your local machine? Just a simple ./wso2server.sh execution. When running a docker container with the proposed run command:

docker run --name anygivenname -e POSTGRES_PASSWORD=postgres -d postgres

it will make PostgreSQL only available for domains that are running in the same docker host. (in the same network range). Remember that on your local machine the Docker host is a VM in its own respect, so the containers own localhost is not your localhost. We need to make sure that any calls from our localhost are somehow redirected to the network of the Docker host. The way how to do this is to add an extra port redirect rule inside the run command like this:

docker run --name wso2-postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres

It simply tells if I want to connect to localhost:5432, Docker will redirect the call to the internal container IP with port 5432. This way we make it possible to for instance do a JDBC connection from a java application running locally to the PostgreSQL DB in Docker.

When spinning up WSO2 API Manager on your local machine, make sure to install the JDBC driver first. Place the JDBC jar in the [APIM-HOME]/repository/components/lib/ directory. Restart the API Manager to make the JDBC connector be available in the product.

Next you create a database and a user in PostgreSQL. You can do this easy with pgAdmin. Also for this we have a Docker image. Run:

docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" -e "PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4

When downloaded and runned, open a browser and go to 0.0.0.0.

Login with user user@domain.com with password admin. Once inside you can connect with postgres. The correct IP can be found with a ‘docker inspect {container id}’. The container ID you can find with a docker ps command. In pgAdmin connect with postgres with the found IP and standard port 5432. The rest is default ‘postgres’ with usr/pwd : postgres/postgres.

postgres docker .png

Once connected create a database ‘gregdb’ and a role ‘regadmin’ with password ‘regadmin’.

Pg4admin.png

Find the master-datasources.xml for WSO2 APIM at the {WSO2 product directory}/repository/conf/datasources/ directory. Inside change the WSO2_CARBON_DB, WSO2AM_DB and WSO2_MB_STORE_MB with the connectivity details for Postgres:

     <datasource>
            <name>WSO2_CARBON_DB</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/gregdb</url>
                        <username>regadmin</username>
                        <password>regadmin</password>
                     
<driverClassName>org.postgresql.Driver</driverClassName>
                        <maxActive>80</maxActive>
                        <maxWait>60000</maxWait>
                        <minIdle>5</minIdle>
                        <testOnBorrow>false</testOnBorrow>
                        <defaultAutoCommit>true</defaultAutoCommit>

                        <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

For WSO2AM_DB and WSO2_MB_STORE_MB the defaultAutoCommit is false. The rest is all the same.

On command line do a ./wso2server.sh –Dsetup. (for Windows you use the .bat file) It will automatically deploy all tables in the database when started up for the first time. The next times you can just do a ./wso2server.sh. Alternatively you can run the postgresql.sql script in the dbscripts directory.

NOTE: When stopping the postgres container you will lose the created database, role and tables. If you want to keep everything you first have to do a commit in Docker and save the image. Please refer to the Docker guidelines how to do this.

If you want to develop with any WSO2 product in combination with a PostgreSQL database and you need to quickly have a setup, there is no need anymore to waste time diving into postgres configuration subjects before being able to use the database. Just go for the Docker container with a fully configured PostgreSQL. Make Docker understand how to expose connectivity with the database, and run it! Happy hacking.

This blog is written on a MacBook pro, but this scenario can perfectly be executed also on  Windows, Ubuntu or any other kind of machine where Docker is pre-installed in combination with any WSO2 product where you want to apply PostgreSQL as database.

Full API lifecycle Management Selection Guide

WHITEPAPER

smartmockups l0qqucke