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)

    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
8. Use the following command to view the table list of wso2carbon_db.
8. Download the jdbc driver 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.
            <description>The datasource used for registry and user manager</description>
            <definition type="RDBMS">
                    <validationQuery>SELECT 1</validationQuery>

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.