- node1: 192.168.0.1 as primary
- node1: 192.168.0.2 on standby
The JDBC string is defined as follows:
jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/mydb
Using repmgr
do the folding and automatic tilting.
First, use node1 as the principal:
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 node1 is down, it can be changed 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 work well at this time.
But if the node1 recovery 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 that the application will try to connect again to node1, and then, as it is in read-only mode, can not insert new data. Therefore, you must change the JDBC connection order as follows:
jdbc:postgresql://192.168.0.2:5432,192.168.0.1:5432/mydb
Let node2 be in the first place. Restart the application. It works.
Even tried to add parameters to the connection string base in the original way (order node1, node2):
jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/mydb?targetServerType=master&loginTimeout=10&connectTimeout=10&tcpKeepAlive=true
The application searches for lost data and will create data again.
So, is the method of order of the songs the only way in this case? Can not he correctly choose the current main database?