As you all know, WSO2 products support a wide range of RDBMs. In this post, I will explain how you can install PostgreSQL 9.3 on your Linux machine and what commands you have to run to create users/databases and grant permission for databases etc. In the latter part of the post, I will briefly explain on the configuration that needs to be done to connect WSO2 products to a PostgreSQL database.
Installing PostgreSQLYou can install using the apt-get command on Linux as below.
Step 1 - To get information on the newest versions of packages and dependencies
sudo apt-get update
Step 2 - To install PostgreSQL v9.3
sudo apt-get install postgresql-9.3
Configure PostgreSQL password
To setup a password to login to PostgreSQL, issue the below commands.
sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'postgres';
Note: To quit from the console, press Ctrl + D
Additional configuration for PostgreSQL
Step 1 - Configure md5 Authentication
1. Open the pg_hba.conf
sudo vim /etc/postgresql/9.3/main/pg_hba.conf
2. Find the below line
local all postgres
and change it to
local all postgres md5
Step 2 - Enable TCP/IP connections
1. Open the postgresql.conf file
sudo vim /etc/postgresql/9.3/main/postgresql.conf
2. Locate the line and uncomment it
#listen_addresses = 'localhost'
Creating a user, a database and then granting permission for the database.
Step 1 - Creating a Linux user
CREATE USER apim WITH PASSWORD 'apim';
Note: Give the password as 'apim'
Step 2 - Login as super user
sudo su - postgres
If successfully logged in, you should get a prompt as below
postgres@ubuntu2:~$
Step 3 - Then connect to the database serve
psql -d template1 -U postgres
Step 4 - Create a user
CREATE USER apim WITH PASSWORD 'apim';
Step 5 - Create a database
CREATE DATABASE apim;
Step 6 - Grant permission for the user to the database
GRANT ALL PRIVILEGES ON DATABASE apim to apim;
Now that you have created the user, database and granted permission, you can provide these credentials and try to connect your WSO2 server with this newly created database.
Configuring WSO2 Products with PostgreSQL
Step 1 - Configuring master-datasources.xml
Open the master-datasources.xml of the product you have selected and provide the configuration as below
<datasource>
<name>WSO2AM_DB</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/apim</url>
<username>apim</username>
<password>apim</password>
<defaultAutoCommit>false</defaultAutoCommit>
<driverClassName>org.postgresql.Driver</driverClassName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery>
<validationInterval>30000</validationInterval>
</configuration>
</definition>
</datasource>
Step 2 - Downloading the correct DB driver
You have to download the correct database driver from the PostgreSQL site. Once downloaded, drop it to $CARBON_HOME/repository/component/lib.
Step 3 - Starting up the server
Now start up the server using the -Dsetup command and it will create the relevant tables in the database.
