Setting up a PostgreSQL Database¶
Follow the steps given below to set up the required Postgre databases for your Micro Integrator.
Tip
WSO2 Micro Integrator requires databases for the following scenarios:
Setting up the database and login role¶
The following Postgre scripts are stored in the <MI_HOME>/dbscripts/postgres
directory of your Micro Integrator. First, select the scripts that are required for your deployment.
You can run the scripts on one database instance or set up separate instances for each requirement. For convenience, it is recommended to set up separate databases for each use case.
Script | Description |
---|---|
postgresql_cluster.sql | This script creates the database tables that are required for cluster coordination (i.e., coordinating the server nodes in your VM deployment). This is only applicable if you have stateful integration artifacts deployed in a clustered setup. |
postgresql_user.sql | This script creates the database tables that are required for storing users and roles. This is only required if you have configured an RDBMS user store. |
postgresql_transaction_count.sql | This script creates the database tables that are required for storing the transaction counts. This is only required if you want to monitor transaction counts in your deployment. |
Create the databases and then create the DB tables by pointing to the relevant script in the <MI_HOME>/dbscripts/postgres
directory.
- Install PostgreSQL on your computer as follows:
- Start the PostgreSQL service using the following command:
- Create a database and the login role from a GUI using the
- To connect PGAdminIII to a PostgreSQL database server, locate the server from the object browser, right-click the client and click Connect . This will show you the databases, tablespaces, and login roles as follows:
- To create a database, click Databases in the tree (inside the object browser), and click New Database .
- In the New Database dialog box, give a name to the database, e.g., gregdb and click OK .
-
To create a login role, click Login Roles in the tree (inside the object browser), and click New Login Role . Enter the role name and a password.
These values will be used in the product configurations as described in the following sections. In the sample configuration,
dbadmin
will be used as both the role name and the password. -
Optionally, enter other policies, such as the expiration time for the login and the connection limit.
- Click OK to finish creating the login role.
Setting up the drivers¶
- Download the PostgreSQL JDBC4 driver.
- Copy the driver to the
MI_HOME/lib
directory.
Connecting the database to the server¶
Open the deployment.toml
file in the <MI_HOME>/conf
directory and add the following sections to create the connection between the Micro Integrator and the relevant database. Note that you need separate configurations corresponding to the separate databases (clusterdb
, userdb
, and transactiondb
).
[[datasource]]
id = "WSO2_COORDINATION_DB"
url= "jdbc:postgresql://localhost:5432/clusterdb"
username="root"
password="root"
driver="org.postgresql.Driver"
pool_options.maxActive=50
pool_options.maxWait = 60000
pool_options.testOnBorrow = true
[[datasource]]
id = "WSO2CarbonDB"
url= "jdbc:postgresql://localhost:5432/userdb"
username="root"
password="root"
driver="org.postgresql.Driver"
pool_options.maxActive=50
pool_options.maxWait = 60000
pool_options.testOnBorrow = true
[[datasource]]
id = "WSO2_TRANSACTION_DB"
url= "jdbc:postgresql://localhost:5432/transactiondb"
username="root"
password="root"
driver="org.postgresql.Driver"
pool_options.maxActive=50
pool_options.maxWait = 60000
pool_options.testOnBorrow = true
[transaction_counter]
enable = true
data_source = "WSO2_TRANSACTION_DB"
update_interval = 2
About User DB
If you replace 'WSO2CarbonDB' with a different id in the user DB configuration, you also need to list the id as a datasource under the [realm_manager]
section in the deployment.toml
file as shown below.
[realm_manager]
data_source = "new_id"
Otherwise the user store database id defaults to 'WSO2CarbonDB' in the realm manager configurations..
See the descriptions of database connection parameters.
Note
To access tables in non-public schemas when using PostgreSQL, you need to explicitly define the schema name in the database connection URL as follows:
postgres://user:password@host/dbname?sslmode=disable&search_path=schema
Alternatively, you can set the schema directly within the PostgreSQL database configuration as follows:
SET search_path TO wso2schema;