Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to send queries to master and failover to slave ?

How to send queries to master and failover to slave ?

From
Benoit Salotti
Date:
Hi all,

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).
I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.
I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.

Anyone already encountered the same need ?

Regards,

Benoit

Re: How to send queries to master and failover to slave ?

From
John R Pierce
Date:
On 6/28/2016 2:41 AM, Benoit Salotti wrote:
>
> I have a setup with 1 master and 1 slave (hot standy to serve read
> queries if necessary).
> I would like the JDBC driver to talk preferrably to the master (to be
> sure to get the freshest data). If the master is not available I would
> like to send the queries to the slave.
>
> The documentation mentions the following values for the parameter
> targetServerType: any, master, slave, preferSlave.
> I'm surprised there is no preferMaster option which would allow me to
> implement easily my requirement.
>


normally, if the master is not available, that means its failed, and
your HA cluster management will promote the slave to be the new master,
fencing off the old master until it can be manually reset and restored
as the new slave.



--
john r pierce, recycling bits in santa cruz

Re: How to send queries to master and failover to slave ?

From
Mikko Tiihonen
Date:

Assuming that the old slave will become new master if master ever goes down then the option "master" would do what you want. Right?

But if you do take the master down for maintenance without failing over to slave then yes. A small code change could be done to add support for preferMaster.


-Mikko

From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Benoit Salotti <benoit.salotti@gmail.com>
Sent: 28 June 2016 12:41
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] How to send queries to master and failover to slave ?
 
Hi all,

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).
I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.
I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.

Anyone already encountered the same need ?

Regards,

Benoit

Re: How to send queries to master and failover to slave ?

From
Mikko Tiihonen
Date:

Assuming that the old slave will become new master if master ever goes down then the option "master" would do what you want. Right?

But if you do take the master down for maintenance without failing over to slave then yes. A small code change could be done to add support for preferMaster.


-Mikko

From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Benoit Salotti <benoit.salotti@gmail.com>
Sent: 28 June 2016 12:41
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] How to send queries to master and failover to slave ?
 
Hi all,

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).
I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.
I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.

Anyone already encountered the same need ?

Regards,

Benoit

Re: How to send queries to master and failover to slave ?

From
John R Pierce
Date:
On 6/28/2016 2:41 AM, Benoit Salotti wrote:
>
> I have a setup with 1 master and 1 slave (hot standy to serve read
> queries if necessary).
> I would like the JDBC driver to talk preferrably to the master (to be
> sure to get the freshest data). If the master is not available I would
> like to send the queries to the slave.
>
> The documentation mentions the following values for the parameter
> targetServerType: any, master, slave, preferSlave.
> I'm surprised there is no preferMaster option which would allow me to
> implement easily my requirement.
>


normally, if the master is not available, that means its failed, and
your HA cluster management will promote the slave to be the new master,
fencing off the old master until it can be manually reset and restored
as the new slave.



--
john r pierce, recycling bits in santa cruz

Re: How to send queries to master and failover to slave ?

From
Benoit Salotti
Date:
Hi Mikko,

I agree, everything works as expected but as long as the failover is not done (about 1 minute with our settings, we are using repmgr) we are unable to serve queries... I'd like the system to serve at least the reads in the meantime :)

If the preferMaster feature can be done "easily" at the driver level I take it.

Benoit

2016-06-28 12:09 GMT+02:00 Mikko Tiihonen <mikko.tiihonen@nitor.fi>:

Assuming that the old slave will become new master if master ever goes down then the option "master" would do what you want. Right?

But if you do take the master down for maintenance without failing over to slave then yes. A small code change could be done to add support for preferMaster.


-Mikko

From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Benoit Salotti <benoit.salotti@gmail.com>
Sent: 28 June 2016 12:41
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] How to send queries to master and failover to slave ?
 
Hi all,

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).
I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.
I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.

Anyone already encountered the same need ?

Regards,

Benoit

Re: How to send queries to master and failover to slave ?

From
Benoit Salotti
Date:
I totally agree with you but this is not immediate. We use repmgr to handle the failures and the DBAs have setup 6 retries with 10 seconds delay between each retry.
So during 1 minute I'm not able to serve read queries even if my slave is available, the preferMaster option would allow me to do it very easily.

2016-06-28 12:37 GMT+02:00 John R Pierce <pierce@hogranch.com>:
On 6/28/2016 2:41 AM, Benoit Salotti wrote:

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).
I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.
I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.



 normally, if the master is not available, that means its failed, and your HA cluster management will promote the slave to be the new master, fencing off the old master until it can be manually reset and restored as the new slave.



--
john r pierce, recycling bits in santa cruz



Re: How to send queries to master and failover to slave ?

From
Benoit Salotti
Date:
Hi Mikko,

I agree, everything works as expected but as long as the failover is not done (about 1 minute with our settings, we are using repmgr) we are unable to serve queries... I'd like the system to serve at least the reads in the meantime :)

If the preferMaster feature can be done "easily" at the driver level I take it.

Benoit

2016-06-28 12:09 GMT+02:00 Mikko Tiihonen <mikko.tiihonen@nitor.fi>:

Assuming that the old slave will become new master if master ever goes down then the option "master" would do what you want. Right?

But if you do take the master down for maintenance without failing over to slave then yes. A small code change could be done to add support for preferMaster.


-Mikko

From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Benoit Salotti <benoit.salotti@gmail.com>
Sent: 28 June 2016 12:41
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] How to send queries to master and failover to slave ?
 
Hi all,

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).
I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.
I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.

Anyone already encountered the same need ?

Regards,

Benoit

Re: How to send queries to master and failover to slave ?

From
Benoit Salotti
Date:
I totally agree with you but this is not immediate. We use repmgr to handle the failures and the DBAs have setup 6 retries with 10 seconds delay between each retry.
So during 1 minute I'm not able to serve read queries even if my slave is available, the preferMaster option would allow me to do it very easily.

2016-06-28 12:37 GMT+02:00 John R Pierce <pierce@hogranch.com>:
On 6/28/2016 2:41 AM, Benoit Salotti wrote:

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).
I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.
I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.



 normally, if the master is not available, that means its failed, and your HA cluster management will promote the slave to be the new master, fencing off the old master until it can be manually reset and restored as the new slave.



--
john r pierce, recycling bits in santa cruz



RE: How to send queries to master and failover to slave ?

From
Švorc Martin
Date:

Hello,

 

We have the very same scenario as Benoit describes – if the master instance fails, we would like the driver to communicate with the secondary instance for read-only operations before the failover process is commenced. The second use-case is when the master instance is deliberately shut down for maintenance reasons  and we do not want to fail over to the secondary instance, but instead allow it to process user queries throughout the maintenance.

 

For this, we intend to implement "preferMaster" targetServerType in the JDBC driver paramater. Would you consider it a good idea to integrate this feature into the official JDBC driver?

 

Cheers

 

Martin

 

From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Benoit Salotti
Sent: Tuesday, June 28, 2016 11:41 AM
To: pgsql-jdbc@postgresql.org
Subject: How to send queries to master and failover to slave ?

 

Hi all,

 

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).

I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

 

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.

I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.

 

Anyone already encountered the same need ?

 

Regards,

 

Benoit

Re: How to send queries to master and failover to slave ?

From
Dave Cramer
Date:
If you have a use case for it then I think it's a good idea

On Thu, 28 Feb 2019 at 07:01, Švorc Martin <svorc@sefira.cz> wrote:

Hello,

 

We have the very same scenario as Benoit describes – if the master instance fails, we would like the driver to communicate with the secondary instance for read-only operations before the failover process is commenced. The second use-case is when the master instance is deliberately shut down for maintenance reasons  and we do not want to fail over to the secondary instance, but instead allow it to process user queries throughout the maintenance.

 

For this, we intend to implement "preferMaster" targetServerType in the JDBC driver paramater. Would you consider it a good idea to integrate this feature into the official JDBC driver?

 

Cheers

 

Martin

 

From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Benoit Salotti
Sent: Tuesday, June 28, 2016 11:41 AM
To: pgsql-jdbc@postgresql.org
Subject: How to send queries to master and failover to slave ?

 

Hi all,

 

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).

I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

 

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.

I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.

 

Anyone already encountered the same need ?

 

Regards,

 

Benoit

RE: How to send queries to master and failover to slave ?

From
Švorc Martin
Date:

Okay thanks, I have opened a pull request for the feature: #1430

 

Martin

 

From: Dave Cramer [mailto:pg@fastcrypt.com]
Sent: Thursday, February 28, 2019 1:24 PM
To: Švorc Martin
Cc: pgsql-jdbc@postgresql.org
Subject: Re: How to send queries to master and failover to slave ?

 

If you have a use case for it then I think it's a good idea

 

On Thu, 28 Feb 2019 at 07:01, Švorc Martin <svorc@sefira.cz> wrote:

Hello,

 

We have the very same scenario as Benoit describes – if the master instance fails, we would like the driver to communicate with the secondary instance for read-only operations before the failover process is commenced. The second use-case is when the master instance is deliberately shut down for maintenance reasons  and we do not want to fail over to the secondary instance, but instead allow it to process user queries throughout the maintenance.

 

For this, we intend to implement "preferMaster" targetServerType in the JDBC driver paramater. Would you consider it a good idea to integrate this feature into the official JDBC driver?

 

Cheers

 

Martin

 

From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Benoit Salotti
Sent: Tuesday, June 28, 2016 11:41 AM
To: pgsql-jdbc@postgresql.org
Subject: How to send queries to master and failover to slave ?

 

Hi all,

 

I have a setup with 1 master and 1 slave (hot standy to serve read queries if necessary).

I would like the JDBC driver to talk preferrably to the master (to be sure to get the freshest data). If the master is not available I would like to send the queries to the slave.

 

The documentation mentions the following values for the parameter targetServerType: any, master, slave, preferSlave.

I'm surprised there is no preferMaster option which would allow me to implement easily my requirement.

 

Anyone already encountered the same need ?

 

Regards,

 

Benoit