Thursday, March 26, 2015

Connect WSO2 server to PostgreSQL

I'm going to install PostgreSQL in Ubuntu machine and connect WSO2 API manager 1.8.0.

1. Install PostgreSQL.
sudo apt-get install -y postgresql postgresql-contrib
2. Open the postgresql.conf  file (/etc/postgresql/9.3/main/postgresql.conf ) and change the listen_addresses.
listen_addresses = 'localhost'
3. Logged to the  PostgreSQL.
sudo -u postgres psql
4. Create a user(eg : vajira/vajira123).
CREATE USER <user_name> WITH PASSWORD '<password>';
5. Create a database (eg: wso2carbon_db).
CREATE DATABASE <database_name>;
6. Grant permission to for user for that database.
GRANT ALL PRIVILEGES ON DATABASE <database_name> to <user_name>;
7. Open the pg_hba.conf file (/etc/postgresql/9.3/main/pg_hba.conf) and change the peer authentication to md5. (using the peer authentication only the operating system user can login to the database)
# TYPE     DATABASE USER ADDRESS METHOD

    local      all                all                        peer
8.  Restart the PostgreSQL.
sudo service postgresql restart
9. Run the script to create registry and user manager database.
psql -U vajira -d wso2carbon_db -a -f wso2am-1.8.0/dbscripts/postgresql.sql
7. Logged in to the database.
psql -U <user_name> -d <database_name> -h 127.0.0.1
8. Use the following command to view the table list of wso2carbon_db.
\dt
8. Download the jdbc driver https://jdbc.postgresql.org/download.html and copy to <server_home>/repository/components/lib.

9. Open the master-datasources.xml  file (server_home/repository/conf/datasources/master-datasources.xml) and the change the data source configuration as bellow.
<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/wso2carbon_db</url>
                    <username>vajira</username>
                    <password>vajira123</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> 

9. Start the server.
10. Using same steps you can create other databases in   PostgreSQL.

11. You can use pgAdmin PostgreSQL tool to connect to databse.