JDBC can't use the current primary database by setting multiple hosts

Question

• node1: 192.168.0.1 as primary
• node1: 192.168.0.2 as standby

The JDBC string is setting as this:

<url>jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/mydb</url>

Using repmgr doing the repliation and automatic failover.

First, use node1 as primary:

 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 7        | host=node1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | standby |   running | node1    | default  | 100      | 7        | host=node2 user=repmgr dbname=repmgr connect_timeout=2

If the node1 down, it can be change to node2:

 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
 1  | node1 | primary | - failed  |          | default  | 100      | ?        | host=node1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | primary | * running |          | default  | 100      | 8        | host=node2 user=repmgr dbname=repmgr connect_timeout=2

The application can run well at this timing.

But if recovery node1 manually:

 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
 1  | node1 | standby |   running | node2    | default  | 100      | 8        | host=node1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | primary | * running |          | default  | 100      | 8        | host=node2 user=repmgr dbname=repmgr connect_timeout=2

It seems the application will try to connect node1 again, then as it is read-only mode, can't insert new data. So should change the JDBC connect order as:

<url>jdbc:postgresql://192.168.0.2:5432,192.168.0.1:5432/mydb</url>

Let node2 at first place. Restart the application. It works.

Even tried adding params to the connect string base on the original way(node1, node2 order):

<url>jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/mydb?targetServerType=master&loginTimeout=10&connectTimeout=10&tcpKeepAlive=true</url>

The application looks lost data and will create data again.

So is the chaning order method the only way in this case? Can't it choose current primary database correctly?