Changing the default Apache Derby DB to Postgres

About this task

Below is explain how to change the default Apache Derby DB to use Postgres. Other databases can be configured the same way.

Procedure

Make sure the Postgres JDBC Driver (or any other driver that corresponds to the database you want to use) has been deployed to the Talend Runtime Container before starting the configuration of Postgres. If the jdbc driver is installed later, the Service Registry, XACML Authorization Repository, Auxiliary Storage, or Provisioning Service server bundle would need to be refreshed.
The Postgres Driver can be downloaded at http://jdbc.postgresql.org/download.html, and the BND tool, needed to bundelize the driver jar, can be found at https://bndtools.org/.

Apply the BND to create a driver bundle:

java -jar bnd.jar wrap postgresql-9.2-1003.jdbc4.jar

mv postgresql-9.2-1003.jdbc4.bar postgresql-9.2-1003.jdbc4.jar

And in the container console, type in the following command to deploy the JDBC driver bundle into the container:
```
install file:///PATH_TO_DRIVER/postgresql-9.2-1003.jdbc4.jar
```
Use Postgres pgAdmin III to create a new database named:
- jcrRegistry for the Service Registry
- jcrXacml for the XACML Authorization Repository
- jcrAuxiliary for Auxiliary Storage
- jcrProvisioning for Provisioning Service
Assign the appropriate access rights to a role corresponding to the user/password used for the configuration.
If you choose a different name for the database, replace all occurrences of jcrRegistry, jcrXacml, jcrAuxiliary, or jcrProvisioning in the following configuration examples accordingly.
Please choose different database names for each feature (Service Registry, XACML Authorization Repository, Auxiliary Storage, and Provisioning Service) in case they use Postgres, to avoid clashes.

In the configuration file, substitute the PersistenceManager for both the workspace and the versioning as shown in the example configurations below.

In the Workspace configuration, replace the default DerbyPersistenceManager by PostgreSQLPersistenceManager:

<Workspace name="$ {wsp.name}">
    <!-- persistence manager of the workspace:  -->
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreS
QLPersistenceManager">
        <param name="driver" value="org.postgresql.Driver"/>
        <param name="url" value="jdbc:postgresql://localhost:5432/jcrRegistry"/>
        <param name="schema" value="postgresql"/>
        <param name="user" value="postgres"/>
        <param name="password" value="secret"/>
        <param name="schemaObjectPrefix" value="jcr_${wsp.name} _"/>
        <param name="externalBLOBs" value="false"/>
    </PersistenceManager>

In the Versioning configuration, replace the default DerbyPersistenceManager by PostgreSQLPersistenceManager:

<Versioning rootPath="$ {rep.home}/version">
<!-- 
Configures the persistence manager to be used for persisting version state.
Please note that the current versioning implementation is based on
a 'normal' persistence manager, but this could change in future
implementations.
 -->
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreS
QLPersistenceManager">
        <param name="driver" value="org.postgresql.Driver"/>
        <param name="url" value="jdbc:postgresql://localhost:5432/jcrRegistry"/>
        <param name="schema" value="postgresql"/>
        <param name="user" value="postgres"/>
        <param name="password" value="secret"/>
        <param name="schemaObjectPrefix" value="version_"/>
        <param name="externalBLOBs" value="false"/>
    </PersistenceManager>

If you also want to store large binary objects inside the database, adapt the DataStore definition as follows. By default, a file system based solution is used. For potential drawbacks of this decision, see http://wiki.apache.org/jackrabbit/DataStore.

In the DataStore configuration, replace the default FileDataStore by DbDataStore:

<!-- data store configuration -->
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="url" value="jdbc:postgresql://localhost:5432/jcrRegistry"/>
    <param name="user" value="postgres"/>
    <param name="password" value="secret"/>
    <param name="databaseType" value="postgresql"/>
    <param name="driver" value="org.postgresql.Driver"/>
    <param name="minRecordLength" value="1024"/>
    <param name="maxConnections" value="3"/>
    <param name="copyWhenReading" value="true"/>
    <param name="tablePrefix" value=""/>
    <param name="schemaObjectPrefix" value=""/>
</DataStore>

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here