Thread: JDBC Postgres Failover with quorum based synchronous replication
Hi
Let's say I am using quorum based synchronous replication either ANY or FIRST in synchronous_standby_names
I am trying to read from slave as discussed here.
jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true
Can someone let me know
- How does a slave is selected? Is a slave randomly selected or a slave is preferred which has zero lag or the minimum lag?
- Is there any setting in jdbc url which will only select the slave with zero replication and if no such slave exists then read will be sent to master ?
Thanks,
Re: [External] JDBC Postgres Failover with quorum based synchronousreplication
From
Vijaykumar Jain
Date:
I guess the distinction between master and secondary logic can be looked up here(isMaster) https://github.com/pgjdbc/pgjdbc/blob/f78a639d1ed3c64e80e1fa107691b4af5945cb84/pgjdbc/src/main/java/org/postgresql/core/v3/ConnectionFactoryImpl.java and from within slaves iirc we did check the code and it is random shuffle and does not support any custom logic to pickany slaves. I might be wrong but I do remember looking it up in the source :) Regards, Vijay ________________________________________ From: Debraj Manna <subharaj.manna@gmail.com> Sent: Sunday, June 17, 2018 11:31:32 AM To: pgsql-admin Subject: [External] JDBC Postgres Failover with quorum based synchronous replication Hi Let's say I am using quorum based synchronous replication<https://wiki.postgresql.org/wiki/New_in_postgres_10#Quorum_Commit_for_Synchronous_Replication>either ANY or FIRSTin synchronous_standby_names I am trying to read from slave as discussed here<https://jdbc.postgresql.org/documentation/head/connect.html>. jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true Can someone let me know 1. How does a slave is selected? Is a slave randomly selected or a slave is preferred which has zero lag or the minimumlag? 2. Is there any setting in jdbc url which will only select the slave with zero replication and if no such slave existsthen read will be sent to master ? Thanks,
Re: [External] JDBC Postgres Failover with quorum based synchronous replication
From
Debraj Manna
Date:
Yes Vijaykumar you are right it is just a random shuffle while picking a slave.
Is there a way I can specify in the connection parameters to pick a slave with zero lag else fall back to master?
On Sun, Jun 17, 2018 at 2:11 PM, Vijaykumar Jain <vjain@opentable.com> wrote:
I guess the distinction between master and secondary logic can be looked up here(isMaster)
https://github.com/pgjdbc/pgjdbc/blob/ f78a639d1ed3c64e80e1fa107691b4 af5945cb84/pgjdbc/src/main/ java/org/postgresql/core/v3/ ConnectionFactoryImpl.java
and from within slaves iirc we did check the code and it is random shuffle and does not support any custom logic to pick any slaves.
I might be wrong but I do remember looking it up in the source :)
Regards,
Vijay
________________________________________
From: Debraj Manna <subharaj.manna@gmail.com>
Sent: Sunday, June 17, 2018 11:31:32 AM
To: pgsql-admin
Subject: [External] JDBC Postgres Failover with quorum based synchronous replication
Hi
Let's say I am using quorum based synchronous replication<https://wiki.postgresql.org/wiki/New_in_ postgres_10#Quorum_Commit_for_ Synchronous_Replication> either ANY or FIRST in synchronous_standby_names
I am trying to read from slave as discussed here<https://jdbc.postgresql.org/documentation/head/ connect.html>.
jdbc:postgresql://node1,node2,node3/accounting? targetServerType=preferSlave& loadBalanceHosts=true
Can someone let me know
1. How does a slave is selected? Is a slave randomly selected or a slave is preferred which has zero lag or the minimum lag?
2. Is there any setting in jdbc url which will only select the slave with zero replication and if no such slave exists then read will be sent to master ?
Thanks,
Re: [External] JDBC Postgres Failover with quorum based synchronousreplication
From
Vijaykumar Jain
Date:
No I guess there is no such option. Maybe you raise a PR if you can :) what you can do at infra level is put your databases behind a Load balancer (like haproxy or nginx) and have a health checkbased on lag etc and only then Route the connections to that standby node else take it off the load balancer. Regards, Vijay ________________________________________ From: Debraj Manna <subharaj.manna@gmail.com> Sent: Sunday, June 17, 2018 2:42:46 PM To: Vijaykumar Jain Cc: pgsql-admin Subject: Re: [External] JDBC Postgres Failover with quorum based synchronous replication Yes Vijaykumar you are right it is just a random shuffle while picking a slave. https://github.com/pgjdbc/pgjdbc/blob/f78a639d1ed3c64e80e1fa107691b4af5945cb84/pgjdbc/src/main/java/org/postgresql/hostchooser/MultiHostChooser.java#L51 Is there a way I can specify in the connection parameters to pick a slave with zero lag else fall back to master? On Sun, Jun 17, 2018 at 2:11 PM, Vijaykumar Jain <vjain@opentable.com<mailto:vjain@opentable.com>> wrote: I guess the distinction between master and secondary logic can be looked up here(isMaster) https://github.com/pgjdbc/pgjdbc/blob/f78a639d1ed3c64e80e1fa107691b4af5945cb84/pgjdbc/src/main/java/org/postgresql/core/v3/ConnectionFactoryImpl.java and from within slaves iirc we did check the code and it is random shuffle and does not support any custom logic to pickany slaves. I might be wrong but I do remember looking it up in the source :) Regards, Vijay ________________________________________ From: Debraj Manna <subharaj.manna@gmail.com<mailto:subharaj.manna@gmail.com>> Sent: Sunday, June 17, 2018 11:31:32 AM To: pgsql-admin Subject: [External] JDBC Postgres Failover with quorum based synchronous replication Hi Let's say I am using quorum based synchronous replication<https://wiki.postgresql.org/wiki/New_in_postgres_10#Quorum_Commit_for_Synchronous_Replication>either ANY or FIRSTin synchronous_standby_names I am trying to read from slave as discussed here<https://jdbc.postgresql.org/documentation/head/connect.html>. jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true Can someone let me know 1. How does a slave is selected? Is a slave randomly selected or a slave is preferred which has zero lag or the minimumlag? 2. Is there any setting in jdbc url which will only select the slave with zero replication and if no such slave existsthen read will be sent to master ? Thanks,