How to enable write mode from Active to Passive Postgresql Database node during failover

In-case of Active Postgresql Database is crashed the following steps are to be followed.

• Stop your Pentaho+ servers to change the jdbc URL in few configuration files in Pentaho+ BA server

• Edit the /tomcat/conf/context.xml with a text editor, at the end of the file

<Resource name="jdbc/postgres"

 auth="Container" type="javax.sql.DataSource"

 factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

 maxActive="20"

 maxIdle="5"

 maxWait="10000"

 username="postgres"

 password="password"

 driverClassName="org.postgresql.Driver"

 url="jdbc:postgresql://Hostname:PORT/myDataSource"

 />

• Change the Hostname with the Passive postgres server Hostname then save and close the file.

• Navigate to the $pentaho_Home/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file with any text editor.

• Following the table below, locate and change the Hostname to Passive Postgresql server.

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">

<param name="driver" value="org.postgresql.Driver"/>

<param name="url" value="jdbc:postgresql://Hostname:PORT/jackrabbit"/>

...

</FileSystem>

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">

<param name="url" value="jdbc:postgresql://Hostname:PORT/jackrabbit"/>

...

</DataStore>

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">

<param name="driver" value="org.postgresql.Driver"/>

<param name="url" value="jdbc:postgresql://Hostname:PORT/jackrabbit"/>

...

</FileSystem>

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">

<param name="url" value="jdbc:postgresql://Hostname:PORT/jackrabbit"/>

...

</PersistenceManager>

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">

<param name="driver" value="org.postgresql.Driver"/>

<param name="url" value="jdbc:postgresql://Hostname:PORT/jackrabbit"/>

...

</FileSystem>

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">

<param name="url" value="jdbc:postgresql://Hostname:PORT/jackrabbit"/>

...

</PersistenceManager>

<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">

<param name="revision" value="${rep.home}/revision.log"/>

<param name="url" value="jdbc:postgresql://Hostname:PORT/jackrabbit"/>

<param name="driver" value="org.postgresql.Driver"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="postgresql"/>

<param name="schemaObjectPrefix" value="cl_j_"/>

<param name="janitorEnabled" value="true"/>

<param name="janitorSleep" value="86400"/>

<param name="janitorFirstRunHourOfDay" value="3"/>

</Journal>

• Go to the pentaho-server/tomcat/webapps/pentaho/META-INF directory and open the context.xml file with any file editor.

• Change the Hostname with the Passive postgres server Hostname then save and close the file.

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" username="hibuser" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://Hostname:PORT/hibernate" validationQuery="select 1"/>

<Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" username="hibuser"

password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://Hostname:PORT/hibernate" validationQuery="select 1"/>

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" username="pentaho_user" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://Hostname:PORT/quartz" validationQuery="select 1"/>

<Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" username="hibuser" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://Hostname:PORT/pentaho_operations_mart" validationQuery="select 1"/>

<Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" username="hibuser" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://Hostname:PORT/hibernate" validationQuery="select 1"/>

• Locate the repository.xml file in the $pentaho_home/pentaho-server/pentaho-solutions/system/jackrabbit directory and open it with any text editor.

• Change the Hostname with the Passive postgres server Hostname then save and close the file.

<!--

Run with a cluster journal

-->

<Cluster id="node1">

<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">

<param name="revision" value="${rep.home}/revision.log"/>

<param name="url" value="jdbc:postgresql://Hostname:PORT/jackrabbit"/>

<param name="driver" value="org.postgresql.Driver"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="databaseType" value="postgresql"/>

<param name="janitorEnabled" value="true"/>

<param name="janitorSleep" value="86400"/>

<param name="janitorFirstRunHourOfDay" value="3"/>

</Journal>

</Cluster>

• Ssh to the Passive Postgresql server and stop the Passive postgresql server

• Locate the recovery.conf file under Path /var/lib/pgsql/9.6/data/

• Comment the content in the file to enable write mode for Passive Postgresql server . Now start the Passive Postgresql server to take over.