1
  • 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?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.