How to enable write mode from Active to Passive Postgresql Database node during failover
PostgreSQL Failover : Enable Write Mode on Passive Node
PostgreSQL database failover requires configuration changes in Pentaho 10.2 (built on Java 17) Business Analytics (PBA) and Data Integration (PDI) to redirect connections from the active (failed) database node to the passive (standby) node. This guide provides steps to enable write mode on the passive node and update Pentaho 10.2 configuration for seamless failover.
Learn about Pentaho PostgreSQL integration or explore Pentaho high availability for comprehensive database solutions.
Failover Procedure
When Active PostgreSQL Database Crashes
Follow these steps to enable write mode on the passive PostgreSQL node:
1. Stop Pentaho 10.2 Servers:
- Stop all Pentaho 10.2 servers to allow configuration changes
- This prevents connection errors during configuration updates
2. Edit Tomcat Configuration:
- Edit
/tomcat/conf/context.xmlwith a text editor - Locate the JDBC resource configuration 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.
| Item | Code Section |
| Repository | <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 | <DataStore class=”org.apache.jackrabbit.core.data.db.DbDataStore”> <param name=”url” value=”jdbc:postgresql://Hostname:PORT/jackrabbit”/> … </DataStore> |
| Workspaces | <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> |
| Persistence Manager (1) | <PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager”> <param name=”url” value=”jdbc:postgresql://Hostname:PORT/jackrabbit”/> … </PersistenceManager> |
| Versioning | <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> |
| Persistence Manager (2) | <PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager”> <param name=”url” value=”jdbc:postgresql://Hostname:PORT/jackrabbit”/> … </PersistenceManager> |
| Database Journal | <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.
- 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.
Frequently Asked Questions
How do I enable write mode on passive PostgreSQL node during failover?
Enable write mode on passive PostgreSQL node by stopping the passive server, editing recovery.conf (comment content), starting the passive PostgreSQL server (write mode enabled), and updating Pentaho 10.2 configuration to redirect connections to the passive node.
What are the steps for PostgreSQL failover with Pentaho?
Steps for PostgreSQL failover include: 1) Stop passive PostgreSQL server, 2) Edit recovery.conf (comment content), 3) Start passive PostgreSQL server (write mode enabled), 4) Update Pentaho 10.2 configuration, 5) Restart Pentaho 10.2 servers, 6) Verify connections, 7) Test failover process.
How does Pentaho 10.2 handle PostgreSQL failover?
Pentaho 10.2 handles PostgreSQL failover through enhanced connection pooling (improves failover reliability), Docker containerization (simplifies failover configuration), automated failover workflows, continuous real-time monitoring (tracks database health), and automated alerts on failures.
What are the benefits of PostgreSQL failover?
Key benefits include high availability (maintain database access during primary node failures), seamless transition (quick failover to passive node), business continuity (minimal downtime), enhanced reliability (automated failover processes), and improved performance (enhanced connection pooling).
How does Docker help with PostgreSQL failover?
Docker containerization simplifies PostgreSQL failover configuration by providing consistent environments, automated failover workflows, easier configuration management, and seamless integration with Pentaho 10.2’s containerized deployment options.
Can Pentaho 10.2 automate PostgreSQL failover?
Yes. Pentaho 10.2 enables automated PostgreSQL failover through automated failover workflows, continuous real-time monitoring (tracks database health), automated alerts on failures, enhanced connection pooling (improves failover reliability), and seamless integration with PostgreSQL high availability configurations.
How does failover ensure business continuity?
Failover ensures business continuity by maintaining database access during primary node failures, providing quick transition to passive node, minimizing downtime, ensuring data availability, and enabling seamless operation continuation with minimal disruption.
🎯 Ready to configure PostgreSQL failover?
PostgreSQL database failover requires proper configuration to ensure seamless high availability and business continuity. Learn how to enable write mode on passive node and configure Pentaho 10.2 for seamless failover.
Contact TenthPlanet for expert Pentaho PostgreSQL integration and high availability services.
Note: This guide provides steps for enabling write mode on passive PostgreSQL node during failover. Actual failover configurations may vary based on your specific PostgreSQL setup, high availability requirements, and deployment environment.
Related Resources:
