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
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.
