Informatica fails to connect to new Primary host post-failover when using DataDirect 7.1 PostgreSQL Wire Protocol driver

Products

• VMware Tanzu Data Intelligence
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

After a PostgreSQL database failover, Informatica is unable to perform write operations. Investigation reveals that the application remains connected to the original leader host (now a read-only standby) instead of switching to the newly promoted primary node.

Environment

• Application: Informatica using ODBC Linux
• Driver: DataDirect 7.1 PostgreSQL Wire Protocol (DWpsql27.so)
• Database: PostgreSQL cluster with primary/standby architecture
• Connection Property Used: target_session_attrs=read-write (ineffective)

Cause

The DataDirect 7.1 PostgreSQL Wire Protocol driver successfully authenticates against the standby node during failover. Unlike more modern drivers, it lacks the logic to parse target_session_attrs=read-write. Since the connection is technically successful (though read-only), the driver does not identify it as a failure and therefore does not attempt to connect to the next host in the AlternateServers list.

Resolution

Solution 1: Use an Initialization String (Recommended Workaround)

Add an InitializationString to your DSN in the odbc.ini file. This string executes an SQL check immediately after authentication. If the node is in recovery mode, it raises an exception, forcing the driver to trigger its failover logic.

Update odbc.ini:

[Your_DSN_Name]AlternateServers=(HostName=secondary_host:PortNumber=5051:Database=your_db)FailoverMode=1InitializationString=DO $$ BEGIN IF pg_is_in_recovery() THEN RAISE EXCEPTION 'Connected to read-only standby node. Forcing failover.'; END IF; END $$;

 

Solution 2: Switch to Community PostgreSQL ODBC Driver

If organizational policy allows, switch to the open-source psqlodbc driver. This driver natively respects target_session_attrs=read-write and will automatically move to the next server in the list if it connects to a read-only standby.

 

Solution 3: Implement a Database Proxy

Use a proxy layer (e.g., HAProxy, PgBouncer, or a Virtual IP managed by Patroni). Point the Informatica DSN to the proxy endpoint. The proxy handles the detection of the primary node, removing the need for AlternateServers and client-side failover logic.