Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Sep 9, 2015 at 4:30 PM, Kevin Grittner wrote: > Robert Haas wrote: > >> I think the problem we should be trying to solve is: Given a set >> of server IPs, connect to one that is up. >> >> I believe this comes up in several different scenarios. >>

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Robert Haas wrote: > I think the problem we should be trying to solve is: Given a set > of server IPs, connect to one that is up. > > I believe this comes up in several different scenarios. > > Example #1: [single server; changing IP address gracefully] > > Example #2:

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Tue, Sep 8, 2015 at 9:29 AM, Kevin Grittner wrote: > I'm not saying we shouldn't have something like this; but we need a > clear definition of that common problem we are solving. I don't > think I've seen that yet. I've seen various spins on solutions > described, from

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Bruce Momjian wrote: > It is annoying for less capable database to say they have high > availability when that just involves having a client library that > can connect to multiple hosts. This sounds like the "But all the *other* kids are doing it!" argument, which comes up

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Victor Wagner wrote: > It would just take a bit more time for client and a bit more load for > server - to make sure that this connection is read-write by > issuing > >show transaction_read_only > > statement before considering connection useful. If the purpose of the feature is

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

В Mon, 07 Sep 2015 17:32:48 +0200 "Daniel Verite" пишет: > Victor Wagner wrote: > > > It would just take a bit more time for client and a bit more load > > for server - to make sure that this connection is read-write by > > issuing > > > >show

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Thu, Sep 3, 2015 at 10:56:42AM -0400, Robert Haas wrote: > The amount of opposition to this feature is remarkable considering > that it's available in Oracle, SQL Server, MongoDB, Cassandra, and > MySQL. See for example: > > http://docs.mongodb.org/manual/reference/connection-string/ >

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

* Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Sep 3, 2015 at 4:00 AM, Shulgin, Oleksandr > wrote: > > I believe that having a floating IP for the master is much more practical > > approach and it doesn't require any patch to libpq or modification of the > >

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Thu, Sep 3, 2015 at 3:56 PM, Robert Haas wrote: > On Thu, Sep 3, 2015 at 4:00 AM, Shulgin, Oleksandr > wrote: >> I believe that having a floating IP for the master is much more practical >> approach and it doesn't require any patch to libpq

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Thu, Sep 3, 2015 at 4:00 AM, Shulgin, Oleksandr wrote: > I believe that having a floating IP for the master is much more practical > approach and it doesn't require any patch to libpq or modification of the > client connection settings. I think that's a great

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Thu, Sep 3, 2015 at 11:42 AM, Stephen Frost wrote: >> > I believe that having a floating IP for the master is much more practical >> > approach and it doesn't require any patch to libpq or modification of the >> > client connection settings. >> >> I think that's a great

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Thu, Sep 3, 2015 at 12:57 PM, Shulgin, Oleksandr wrote: > On Thu, Sep 3, 2015 at 6:02 PM, Robert Haas wrote: >> Maybe someday we should have all that, but I think for right now >> that's complicating things unnecessarily. I think the best

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Thu, Sep 3, 2015 at 6:02 PM, Robert Haas wrote: > > Maybe someday we should have all that, but I think for right now > that's complicating things unnecessarily. I think the best proposal > so far is to allow the host=X option to be repeated multiple times. > If you

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > Shulgin, Oleksandr wrote: > > > > Alternatively, change the rules for parsing the existing host=X > > > parameter so that we split it on some separator that isn't a valid > > > hostname character, and then strip off an optional :port syntax

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

* Robert Haas (robertmh...@gmail.com) wrote: > Alternatively, change the rules for parsing the existing host=X > parameter so that we split it on some separator that isn't a valid > hostname character, and then strip off an optional :port syntax from > each entry; that value, if present, overrides

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Shulgin, Oleksandr wrote: > > Alternatively, change the rules for parsing the existing host=X > > parameter so that we split it on some separator that isn't a valid > > hostname character, and then strip off an optional :port syntax from > > each entry; that value, if present, overrides port=X

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 3 September 2015 at 12:57, Shulgin, Oleksandr < oleksandr.shul...@zalando.de> wrote > > On Thu, Sep 3, 2015 at 6:02 PM, Robert Haas wrote: >> >> >> Maybe someday we should have all that, but I think for right now >> that's complicating things unnecessarily. I think the

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Robert Haas wrote: > Alternatively, change the rules for parsing the existing host=X > parameter so that we split it on some separator that isn't a valid > hostname character, and then strip off an optional :port syntax from > each entry; that value, if present, overrides port=X for that entry.

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Sep 2, 2015 at 9:00 PM, Robert Haas wrote: > On Wed, Sep 2, 2015 at 4:52 AM, Shulgin, Oleksandr > wrote: > > On Tue, Sep 1, 2015 at 8:12 PM, Andres Freund > wrote: > >> > >> On 2015-09-01 14:07:19 -0400, Robert

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Sep 3, 2015 7:30 PM, "Robert Haas" wrote: > > All of these objections seem pretty thin to me. I'd accept any of > them as a reason for preferring one alternative over another, but I > don't accept that the presence of a few problems of this magnitude > means we should

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Tue, Sep 1, 2015 at 8:12 PM, Andres Freund wrote: > On 2015-09-01 14:07:19 -0400, Robert Haas wrote: > > But I think it's quite wrong to assume that the infrastructure for > > this is available and usable everywhere, because in my experience, > > that's far from the case.

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Sep 2, 2015 at 4:52 AM, Shulgin, Oleksandr wrote: > On Tue, Sep 1, 2015 at 8:12 PM, Andres Freund wrote: >> >> On 2015-09-01 14:07:19 -0400, Robert Haas wrote: >> > But I think it's quite wrong to assume that the infrastructure for >> >

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 11:06 PM, Amit Kapila wrote: > Always try with the first server specified in connection string and if that > is not available try with second and so on. I think for the case of > failover, > the design shouldn't be much complicated and it is a

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015-09-01 14:07:19 -0400, Robert Haas wrote: > But I think it's quite wrong to assume that the infrastructure for > this is available and usable everywhere, because in my experience, > that's far from the case. Especially when the alternative is a rather short patch implementing an otherwise

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Tue, Sep 1, 2015 at 7:50 PM, Alvaro Herrera wrote: > Robert Haas wrote: > > On Wed, Aug 19, 2015 at 9:41 AM, Tom Lane wrote: > > > That sort-of ties into what seems to me the main objection to this > > > proposal, namely that there is already a

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Robert Haas wrote: > On Wed, Aug 19, 2015 at 9:41 AM, Tom Lane wrote: > > That sort-of ties into what seems to me the main objection to this > > proposal, namely that there is already a way to do this sort of thing: > > DNS-based load balancing. All the clients think they

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Tue, Sep 1, 2015 at 1:50 PM, Alvaro Herrera wrote: > Robert Haas wrote: >> On Wed, Aug 19, 2015 at 9:41 AM, Tom Lane wrote: >> > That sort-of ties into what seems to me the main objection to this >> > proposal, namely that there is already a way

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 9:41 AM, Tom Lane wrote: > That sort-of ties into what seems to me the main objection to this > proposal, namely that there is already a way to do this sort of thing: > DNS-based load balancing. All the clients think they connect to > db.mycompany.com,

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Fri, Aug 28, 2015 at 6:10 PM, Teodor Sigaev teo...@sigaev.ru wrote: +1 for bringing the jdbc driver URI syntax into libpq, so that all interfaces can be optionally specified this way. This doesn't preclude the use of ipfailover, in fact it might be work well together. If you don't like it,

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

+1 for bringing the jdbc driver URI syntax into libpq, so that all interfaces can be optionally specified this way. This doesn't preclude the use of ipfailover, in fact it might be work well together. If you don't like it, don't use it. +1 Another thought: multiple hosts in URI could be used

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 4:46 PM, Andres Freund and...@anarazel.de wrote: On 2015-08-19 09:41:32 -0400, Tom Lane wrote: In fact, they'd still need to use DNS balancing for Postgres, because not everything connects with libpq (think JDBC for instance). It already does support this though.

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015.08.19 at 09:21:50 +, Albe Laurenz wrote: Yes, but that will only work reliably if the (read-only) standby does not allow connections before it is promoted. It would just take a bit more time for client and a bit more load for server - to make sure that this connection is

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015-08-19 09:41:32 -0400, Tom Lane wrote: In fact, they'd still need to use DNS balancing for Postgres, because not everything connects with libpq (think JDBC for instance). It already does support this though. https://jdbc.postgresql.org/documentation/head/connect.html : Connection

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Albe Laurenz laurenz.a...@wien.gv.at writes: Victor Wagner wrote: It would just take a bit more time for client and a bit more load for server - to make sure that this connection is read-write by issuing show transaction_read_only statement before considering connection useful. That's not

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 19 August 2015 at 14:46, Andres Freund and...@anarazel.de wrote: On 2015-08-19 09:41:32 -0400, Tom Lane wrote: In fact, they'd still need to use DNS balancing for Postgres, because not everything connects with libpq (think JDBC for instance). It already does support this though.

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015.08.19 at 15:35:17 +0100, Simon Riggs wrote: I think we do need some way of saying that a readonly connection is OK. So I had such thing in my propsal (boolean parameter readonly). But haven't yet checked if it is compatible with jdbc syntax. the default would be to connect to each

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Here we are discussing load-balancing on the client level, not on the statement level. I see. Suppose that we have 100 readonly clients and 3 standby servers + master. If all clients specify all four servers in the their connect strings, and connect randomly to them, each server would have

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 4:45 PM, ''Victor Wagner *EXTERN*' *EXTERN*' *EXTERN* vi...@wagner.pp.ru wrote: On 2015.08.19 at 15:35:17 +0100, Simon Riggs wrote: I think we do need some way of saying that a readonly connection is OK. So I had such thing in my propsal (boolean parameter

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 07:15:30AM +, Laurenz Albe wrote: Victor Wagner wrote: I wonder how useful this is at the present time. Maybe a better idea would be: host=db1.myorg.com,db2.myorg.com port=5432,2345 I think if we're going to provide multiple sets of connection info, we should

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015.08.20 at 00:17:35 +0900, Tatsuo Ishii wrote: But once connection is established, each client works with one server (at least until communication failure occurs and it would call PQreset. In this case it has to reprepare statements anyway). One downside of this is, if one of the

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Victor Wagner vi...@wagner.pp.ru writes: On 2015.08.20 at 00:17:35 +0900, Tatsuo Ishii wrote: One downside of this is, if one of the standby servers is not responding, every time clients will be blocked by the server before giving up the connection trial. This could last for hours (for This

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015.08.18 at 08:32:28 +, Albe Laurenz wrote: I wonder how useful this is at the present time. If the primary goes down and the client gets connected to the standby, it would have read-only access there. Most applications wouldn't cope well with that. It is supposed that somebody

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 12:21 PM, Victor Wagner *EXTERN* vi...@wagner.pp.ru wrote: On 2015.08.18 at 08:32:28 +, Albe Laurenz wrote: I wonder how useful this is at the present time. If the primary goes down and the client gets connected to the standby, it would have read-only access

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015.08.19 at 08:28:32 +0530, Amit Kapila wrote: On Tue, Aug 18, 2015 at 9:48 AM, Victor Wagner vi...@wagner.pp.ru wrote: Behavoir If PQconnectdb encounters connect string with multiple hosts specified, it attempts to establish connection with all these hosts

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015.08.19 at 12:29:51 +0530, Amit Kapila wrote: It seems that most people discussing in this thread think in millisecond time intervals (failure and immediate reconnect). Why not have this as a separate parameter (*_timeout or something like that)? Because it is not in the software

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 12:35 PM, Victor Wagner vi...@wagner.pp.ru wrote: On 2015.08.19 at 08:28:32 +0530, Amit Kapila wrote: On Tue, Aug 18, 2015 at 9:48 AM, Victor Wagner vi...@wagner.pp.ru wrote: Behavoir If PQconnectdb encounters connect string with multiple

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015.08.19 at 12:42:45 +0900, Tatsuo Ishii wrote: I wonder how extended protocol is handled by this proposal. Suppose load balacing mode is enabled. PQprepare is executed on standby1. Then PQexecPrepared gets called. This may be executed on standby2, which will fail because there's no

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Victor Wagner wrote: I wonder how useful this is at the present time. If the primary goes down and the client gets connected to the standby, it would have read-only access there. Most applications wouldn't cope well with that. It is supposed that somebody (either system administrator or

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015.08.19 at 07:15:30 +, Albe Laurenz wrote: Idea is that we don't need any extra administration actions such as IP migration to do it. Clients have list of alternate servers and discover which one to work with by trial and error. Yes, but that will only work reliably if the

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Victor Wagner wrote: Idea is that we don't need any extra administration actions such as IP migration to do it. Clients have list of alternate servers and discover which one to work with by trial and error. Yes, but that will only work reliably if the (read-only) standby does not

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 2015.08.19 at 12:55:15 +0530, Amit Kapila wrote: I think that failover procedure should begin before first connection is ever established. As far as I understand, failover gets initiated once the master server goes down or is not accessible due to some reason, so for such cases if

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 1:23 PM, Victor Wagner vi...@wagner.pp.ru wrote: On 2015.08.19 at 12:55:15 +0530, Amit Kapila wrote: I think that failover procedure should begin before first connection is ever established. As far as I understand, failover gets initiated once the master

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Hans-Jürgen Schönig wrote: in addition to that you have the “problem” of transactions. if you failover in the middle of a transaction, strange things might happen from the application point of view. the good thing, however, is that stupid middleware is sometimes not able to handle

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 18 Aug 2015, at 11:19, Albe Laurenz laurenz.a...@wien.gv.at wrote: Hans-Jürgen Schönig wrote: in addition to that you have the “problem” of transactions. if you failover in the middle of a transaction, strange things might happen from the application point of view. the good thing,

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

Victor Wagner wrote: Rationale = Since introduction of the WAL-based replication into the PostgreSQL, it is possible to create high-availability and load-balancing clusters. However, there is no support for failover in the client libraries. So, only way to provide transparent for

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 18 Aug 2015, at 10:32, Albe Laurenz laurenz.a...@wien.gv.at wrote: Victor Wagner wrote: Rationale = Since introduction of the WAL-based replication into the PostgreSQL, it is possible to create high-availability and load-balancing clusters. However, there is no support for

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Tue, Aug 18, 2015 at 12:53 PM, Jaime Casanova jaime.casan...@2ndquadrant.com wrote: This is not completely true, you can always use something like pgbouncer or other middleware to change the server to which clients connect. you still need to solve the fact that you will have a read-only

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

I wonder how extended protocol is handled by this proposal. Suppose load balacing mode is enabled. PQprepare is executed on standby1. Then PQexecPrepared gets called. This may be executed on standby2, which will fail because there's no prepared statement created by the former PQprepare call. Even

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Tue, Aug 18, 2015 at 9:48 AM, Victor Wagner vi...@wagner.pp.ru wrote: Behavoir If PQconnectdb encounters connect string with multiple hosts specified, it attempts to establish connection with all these hosts simultaneously, and begins to work with server which responds first,

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Tue, Aug 18, 2015 at 6:07 AM, PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at wrote: On 18 Aug 2015, at 11:19, Albe Laurenz laurenz.a...@wien.gv.at wrote: Hans-Jürgen Schönig wrote: in addition to that you have the “problem” of transactions. if you failover in the middle of a

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 17 August 2015 at 23:18, Victor Wagner vi...@wagner.pp.ru wrote: Rationale = Since introduction of the WAL-based replication into the PostgreSQL, it is possible to create high-availability and load-balancing clusters. However, there is no support for failover in the client

[HACKERS] Proposal: Implement failover on libpq connect level.

Rationale = Since introduction of the WAL-based replication into the PostgreSQL, it is possible to create high-availability and load-balancing clusters. However, there is no support for failover in the client libraries. So, only way to provide transparent for client application failover