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

[Robert Haas]

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.
>>
>> Example #1: [single server; changing IP address gracefully]
>>
>> Example #2: [xDB/BDR client uses local master if up; else a remote]
>>
>> Example #3: [SR/HS with changing primary]
>
> For all of those it is clear that we do not need (or want!)
> heartbeat, STONITH, fencing, etc. to be handled by the connector.
> If the above are the sorts of problems we are trying to solve, a
> very simple solution is the best.  I know you outlined several; I'm
> not sure it would matter all that much which one we used -- any
> would work and someone should Just Do It.
>
>> I'm sure there are more.
>
> Ay, there's the rub.  Some people seemed to be suggesting that this
> should be far more than what you describe above.  That would, IMO,
> be a mistake.

It sounds like we are pretty much on the same page.  :-)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Kevin Grittner]

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: [xDB/BDR client uses local master if up; else a remote]
>
> Example #3: [SR/HS with changing primary]

For all of those it is clear that we do not need (or want!)
heartbeat, STONITH, fencing, etc. to be handled by the connector.
If the above are the sorts of problems we are trying to solve, a
very simple solution is the best.  I know you outlined several; I'm
not sure it would matter all that much which one we used -- any
would work and someone should Just Do It.

> I'm sure there are more.

Ay, there's the rub.  Some people seemed to be suggesting that this
should be far more than what you describe above.  That would, IMO,
be a mistake.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Robert Haas]

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 which I can infer various possible problems; but to
> pick the best version of this as *the* solution I think we need a
> clear statement of the problem itself.

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: I need to move my database server to a different IP
address.  I could have clients connect by name, but using DNS sucks
for the reasons already discussed.  I could move the server and then
change all the client connect strings afterwards, but then I'm bound
to be down for longer than necessary.  Instead, I'll change all of my
connect strings FIRST, to specify that either IP is acceptable.  Then
I'll move the server to the new IP, and all of them will quickly find
that the old IP is down and the new IP is up and start using that
instead.  At leisure, I can remove the old IP from connect strings
(and then re-purpose that IP).

Example #2: I am using EnterpriseDB's xDB multi-master replication, or
2ndQuadrant's BDR, to replicate among geographically distributed
database servers.  I want clients to connect to their local server,
but if it is down, I want them to connect to one of the other masters.
Connecting to the local server first minimizes replication conflicts,
since most transactions on a given application server will be for data
local to that geography, but being willing to fall back to some other
server maximizes availability when my local server goes down.

Example #3: I have a master and and 3 SR standbys, all on different
subnets.  Periodically, I fail over, so that the master role moves
around.  Each server has an IP which can be used for read-only
connections.  Each also has a virtual IP which is up when it is the
write master and down when it is a standby.  Read-only queries are 90%
of my traffic, and eventual consistency is fine.  So, for a read
query, I want to pick among the IPs that are up; for write IPs, I want
to find the one place that writes can be performed, but that might be
any of 4 virtual IPs.

I'm sure there are more.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Kevin Grittner]

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 often.  We generally resist doing
something solely on that basis, so the rest of the email is really
what matters, I think, much as this does gall.

> Yes, we can do this in DNS, but that is all happening at a
> different layer.

More than that, there are technical reasons that can be a bad
solution.  As just one example, the servers might well be in
different domains.

> Now, the counter-argument is that this is the wrong layer to do
> it, and we will end up adding tons of configurations variables to
> libpq to control this.

Yeah, we definitely *don't* want to implement some sort of failover
manager in every connector -- that way madness lies.

> We are clearly not adding this just because JDBC has it --- we
> are adding it because it allows for more complex server
> configurations.

I think what we need is a clear description of use cases where we
think this is the solution, and some clear boundaries to the scope
-- so it is also clear what kinds of problems this is *not*
intended to solve.

> Could this ability be more powerfully done with DNS or a
> connection pooler, yes, but not everyone wants that complexity.
> For me, this libpq change has a simple user API with a small
> amount of code change that give us a simple solution to a common
> problem.

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 which I can infer various possible problems; but to 
pick the best version of this as *the* solution I think we need a 
clear statement of the problem itself.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 transaction_read_only 
> 
> statement before considering connection useful.

If the purpose of the feature is to wait for a failover to complete,
shouldn't it check for pg_is_in_recovery() rather than 
 transaction_read_only ?

That's because a database or user can be made read-only-on-connect 
on an otherwise read-write instance by issuing
  ALTER DATABASE dbname SET default_transaction_read_only TO on;
The same for a user with ALTER USER.

In that case,  transaction_read_only  would be OFF after connecting,
both on the master and on a slave, independantly of any failover
in progress or finished or not having occurred at all.

Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Victor Wagner]

В 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 transaction_read_only 
> > 
> > statement before considering connection useful.
> 
> If the purpose of the feature is to wait for a failover to complete,
> shouldn't it check for pg_is_in_recovery() rather than 
>  transaction_read_only ?
> 

Purpose of this feature is to distinguish between master and standby
servers. 

This allows failover system to work with standby servers accepting
client connections, and even to create system where read-only clients 
can be loadbalanced among several hot backup servers, and read-write
clients work with master, but do not need reconfiguration when
failover happens.

pg_is_in_recovery() is really better. But it seems that chapter 25 of
documentation should be improved and this function mentioned in the
section 25.5.1 (Hot Standby / User Overview)



-- 
   Victor Wagner 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Bruce Momjian]

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/
> https://datastax.github.io/python-driver/getting_started.html
> 
> This is a small patch with minimal to no downside implementing a
> feature that is present in most or all of the major competing
> products.  We're really doing ourselves a disservice if we reject it.
> I think it would be far better to progress to talking about what
> design we'd be comfortable with, rather than kidding ourselves that a
> feature that everyone else has and which somebody has taken the time
> to implement (thus, obviously it has value for them) and which has
> been discussed to general approval at PGCon developer meetings and
> which has been endorsed on this thread by three committers is somehow
> something that nobody really needs.  Seriously?

As much as I like to disagree with Robert, I can't in this case.  ;-)

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.  Yes, we can do this in DNS, but that is all
happening at a different layer.

Now, the counter-argument is that this is the wrong layer to do it, and
we will end up adding tons of configurations variables to libpq to
control this.

We are clearly not adding this just because JDBC has it --- we are
adding it because it allows for more complex server configurations. 
Could this ability be more powerfully done with DNS or a connection
pooler, yes, but not everyone wants that complexity.  For me, this libpq
change has a simple user API with a small amount of code change that
give us a simple solution to a common problem.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Stephen Frost]

* 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
> > client connection settings.
> 
> I think that's a great approach if all the machines are on the same
> subnet.  If they are in different datacenters, it doesn't work.

Anycast could technically be used to address that issue, but there's a
whole host of reasons why that would be quite painful for a PG
connection.

> I think it would be far better to progress to talking about what
> design we'd be comfortable with, rather than kidding ourselves that a
> feature that everyone else has and which somebody has taken the time
> to implement (thus, obviously it has value for them) and which has
> been discussed to general approval at PGCon developer meetings and
> which has been endorsed on this thread by three committers is somehow
> something that nobody really needs.  Seriously?

Agreed.  For my part, I like the JDBC configuration approach and
definitely would ask that we support 'host:port' options since not all
servers will be on the same port.  I don't agree with Tom's concern
regarding the simultaneous connection to all servers at once (yes, it's
a bit unfriendly, but I don't see that as a reason to not provide that
choice and there's a lot of reasons why you'd want it).

What would be nice is a better way to configure these more complicated
options than the single string or even the current very simple
pg_service.conf file.  For example, a service name which could define
*other* service names to try along with a plan for how to connect to
them (round robin, simultaneously, read/write only, etc) and perhaps
also support specifying multiple service names to the 'service'
parameter.  I'd prefer that we support all different configuration
options through the 'single string' method also, but I'm not convinced
that's a hard requirement.

Thanks!

Stephen


signature.asc
Description: Digital signature

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

[Dave Page]

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 or modification of the
>> client connection settings.
>
> I think that's a great approach if all the machines are on the same
> subnet.  If they are in different datacenters, it doesn't work.
>
> 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/
> https://datastax.github.io/python-driver/getting_started.html
>
> This is a small patch with minimal to no downside implementing a
> feature that is present in most or all of the major competing
> products.  We're really doing ourselves a disservice if we reject it.
> I think it would be far better to progress to talking about what
> design we'd be comfortable with, rather than kidding ourselves that a
> feature that everyone else has and which somebody has taken the time
> to implement (thus, obviously it has value for them) and which has
> been discussed to general approval at PGCon developer meetings and
> which has been endorsed on this thread by three committers is somehow
> something that nobody really needs.  Seriously?

+100

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Robert Haas]

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 approach if all the machines are on the same
subnet.  If they are in different datacenters, it doesn't work.

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/
https://datastax.github.io/python-driver/getting_started.html

This is a small patch with minimal to no downside implementing a
feature that is present in most or all of the major competing
products.  We're really doing ourselves a disservice if we reject it.
I think it would be far better to progress to talking about what
design we'd be comfortable with, rather than kidding ourselves that a
feature that everyone else has and which somebody has taken the time
to implement (thus, obviously it has value for them) and which has
been discussed to general approval at PGCon developer meetings and
which has been endorsed on this thread by three committers is somehow
something that nobody really needs.  Seriously?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Robert Haas]

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 approach if all the machines are on the same
>> subnet.  If they are in different datacenters, it doesn't work.
>
> Anycast could technically be used to address that issue, but there's a
> whole host of reasons why that would be quite painful for a PG
> connection.

/me rolls eyes.

>> I think it would be far better to progress to talking about what
>> design we'd be comfortable with, rather than kidding ourselves that a
>> feature that everyone else has and which somebody has taken the time
>> to implement (thus, obviously it has value for them) and which has
>> been discussed to general approval at PGCon developer meetings and
>> which has been endorsed on this thread by three committers is somehow
>> something that nobody really needs.  Seriously?
>
> Agreed.  For my part, I like the JDBC configuration approach and
> definitely would ask that we support 'host:port' options since not all
> servers will be on the same port.  I don't agree with Tom's concern
> regarding the simultaneous connection to all servers at once (yes, it's
> a bit unfriendly, but I don't see that as a reason to not provide that
> choice and there's a lot of reasons why you'd want it).

Yep.  And it can even be configurable behavior, as I suggested upthread.

> What would be nice is a better way to configure these more complicated
> options than the single string or even the current very simple
> pg_service.conf file.  For example, a service name which could define
> *other* service names to try along with a plan for how to connect to
> them (round robin, simultaneously, read/write only, etc) and perhaps
> also support specifying multiple service names to the 'service'
> parameter.  I'd prefer that we support all different configuration
> options through the 'single string' method also, but I'm not convinced
> that's a hard requirement.

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 repeat the host=X option N times, you can also repeat the
port=X option exactly N times, or else you can specify it just once.
Done.

Alternatively, leave the host=X option alone and add a new option
hostlist=X, allowing a comma-separated list of names or IPs, with each
hostname or IP allowed an optional :port suffix.  If host=X parameter
is omitted or the connection to that machine fails, try everybody in
the hostlist concurrently, or with some configurable (and presumably
short) delay between one and then next.  Again, done.

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.

I think we're really tying ourselves in knots about problems that
really aren't very hard to solve here.  I'm sure some of these
proposals are better than others and the idea thing may be something
else again.  But if NASA can send a space probe 7.5 billion kilometers
to a frigid spheroid in the outer solar system without crashing into
anything or having any catastrophic software or hardware failures, I
bet we can come up with a convenient way to specify multiple IP
addresses.  I'd like the story of this feature to resemble a work by
e.e. cummings more than it does one by Robert Jordan.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Robert Haas]

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 proposal
>> so far is to allow the host=X option to be repeated multiple times.
>> If you repeat the host=X option N times, you can also repeat the
>> port=X option exactly N times, or else you can specify it just once.
>> Done.
>
> But this already breaks backwards-compatibility with any clients who belief
> that whatever value specified the latest takes precedence.  I'm not arguing
> that there are such use cases in the wild or that it's entirely sane thing
> to do, but still.

Yep.  If we care about backward compatibility, there can be a new
option that must be specified to get the new behavior.  We can also
decide not to care about this case.

> More importantly, this will break any code that tries to parse the conninfo
> string and produce a hashmap from it for modification.

That is true, but I am not sure I agree that it is important.  Switch
to a hashmap whose values are arrays.

>> Alternatively, leave the host=X option alone and add a new option
>> hostlist=X, allowing a comma-separated list of names or IPs, with each
>> hostname or IP allowed an optional :port suffix.  If host=X parameter
>> is omitted or the connection to that machine fails, try everybody in
>> the hostlist concurrently, or with some configurable (and presumably
>> short) delay between one and then next.  Again, done.
>
> The exact behavior in case of both host/port and hostlist are specified
> becomes really tricky then.  It's already tricky enough, if you recall the
> service files -- how are they going to come into play here?

It doesn't seem that tricky to me, but maybe I'm biased by having just
invented it 5 minutes ago.

> I believe the less there are implicit workings in the way libpq connects,
> the better.

I don't disagree with that as a general rule - only when it keeps us
from implementing useful features.

>>> 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.
>
> It's tempting to use ':' as the separator here, but it's still valid for
> directory names and host can be one in case of UN*X sockets.

The directory name is only likely to contain : on Windows, and Windows
doesn't support UNIX sockets.

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 give up on the feature.  It's a good enough feature
that it is worth the possibility of slightly inconveniencing someone
running in an unusual configuration.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Shulgin, Oleksandr]

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 repeat the host=X option N times, you can also repeat the
> port=X option exactly N times, or else you can specify it just once.
> Done.
>

But this already breaks backwards-compatibility with any clients who belief
that whatever value specified the latest takes precedence.  I'm not arguing
that there are such use cases in the wild or that it's entirely sane thing
to do, but still.

More importantly, this will break any code that tries to parse the conninfo
string and produce a hashmap from it for modification.

Alternatively, leave the host=X option alone and add a new option
> hostlist=X, allowing a comma-separated list of names or IPs, with each
> hostname or IP allowed an optional :port suffix.  If host=X parameter
> is omitted or the connection to that machine fails, try everybody in
> the hostlist concurrently, or with some configurable (and presumably
> short) delay between one and then next.  Again, done.
>

The exact behavior in case of both host/port and hostlist are specified
becomes really tricky then.  It's already tricky enough, if you recall the
service files -- how are they going to come into play here?

I believe the less there are implicit workings in the way libpq connects,
the better.

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.
>

It's tempting to use ':' as the separator here, but it's still valid for
directory names and host can be one in case of UN*X sockets.

--
Alex

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

[Stephen Frost]

* 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 from
> > > each entry; that value, if present, overrides port=X for that entry.
> > 
> > It's tempting to use ':' as the separator here, but it's still valid for
> > directory names and host can be one in case of UN*X sockets.
> 
> I think that's rare enough that we could just say that if you want to
> have a : in a directory name used for local connections, you have to
> escape the : character.  This is going to be pretty easy to detect as a
> problem because of the obvious error message ("cannot parse "pg" in
> /usr/sockets:pg as a port number"), except in the even rarer case that
> the only stuff after the colon is digits.

If we really want to worry about this, we could simply check if the
directory exists with the ':5433' or whatever at the end and, if it
does, use whatever the port specification is.  If that directory doesn't
exist, and one without the ':5433' does, then we try that directory and
that port.

Personally, I agree with Alvaro that it's really just overkill to worry
about though.

Thanks!

Stephen


signature.asc
Description: Digital signature

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

[Stephen Frost]

* 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 port=X for that entry.

Using a ':' should work just fine for that.  Having only one option for
how all the connections are done (concurrently, round robin, etc) and an
option for the timeout works for now.

Thanks!

Stephen


signature.asc
Description: Digital signature

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

[Alvaro Herrera]

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 for that entry.
> 
> It's tempting to use ':' as the separator here, but it's still valid for
> directory names and host can be one in case of UN*X sockets.

I think that's rare enough that we could just say that if you want to
have a : in a directory name used for local connections, you have to
escape the : character.  This is going to be pretty easy to detect as a
problem because of the obvious error message ("cannot parse "pg" in
/usr/sockets:pg as a port number"), except in the even rarer case that
the only stuff after the colon is digits.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Christopher Browne]

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 best proposal
>> so far is to allow the host=X option to be repeated multiple times.
>> If you repeat the host=X option N times, you can also repeat the
>> port=X option exactly N times, or else you can specify it just once.
>> Done.
>
>
> But this already breaks backwards-compatibility with any clients who
belief that whatever value specified the latest takes precedence.  I'm not
arguing that there are such use cases in the wild or that it's entirely
sane thing to do, but still.
>
> More importantly, this will break any code that tries to parse the
conninfo string and produce a hashmap from it for modification.

The notion of an "ordered hashmap" makes me break out in hives...

>> Alternatively, leave the host=X option alone and add a new option
>> hostlist=X, allowing a comma-separated list of names or IPs, with each
>> hostname or IP allowed an optional :port suffix.  If host=X parameter
>> is omitted or the connection to that machine fails, try everybody in
>> the hostlist concurrently, or with some configurable (and presumably
>> short) delay between one and then next.  Again, done.
>
>
> The exact behavior in case of both host/port and hostlist are specified
becomes really tricky then.  It's already tricky enough, if you recall the
service files -- how are they going to come into play here?
>
> I believe the less there are implicit workings in the way libpq connects,
the better.

In that case, let's have a New Option, and expressly break with the
implicit bits.

The new option ONLY accepts URIs, but allows it to be submitted multiple
times.

psql --uri postgresql://postgres@favehost:5432/some_db_name --uri
postgresql://postgres@favehost:5432/another_db_name --uri
postgresql://postgres@favehost:5432/third_db_name --uri
postgresql://postgres@favehost:5432/fourth_backup_db

Parsing conninfo strings is no fun.  I'm finding I prefer using URIs.  They
may even be easier to parse, not that I have thus far cared; using URIs
tends to mean I don't *need* to parse anything.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

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

[Alvaro Herrera]

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.

: is not a valid character in hostnames, so I think this makes sense.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Shulgin, Oleksandr]

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 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 widely available feature.
> >
> > But that won't actually help in the case described by Robert: if the
> master
> > server A failed, the client has no idea if B or C would become the new
> > master.
>
> Sure it does.  You just need to ensure that whichever of those is the
> new master accepts connections, and the other one doesn't.  There are
> lots of ways to do this; e.g. give the machine a second IP that
> accepts connections only when the machine is the designated master,
> and have read-write clients connect to that IP, and read-only clients
> connect to the machine's main IP.
>

Well, I see how that can help, but still sounds like a lot of hassle.

What if you have 5 servers: A..F, listed in client's connection settings in
that order, and after failing over from A, now F is the new master (for
whatever reason: I don't think it would be realistic to assume that you can
and always will fail over to the next host in the list).  So suddenly, the
read-write clients need to make 5 connection attempts before arriving at
the master (add name resolution to the picture for even more latency).
Connection pooling can probably mitigate this to some degree, of course.

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.

Andres's point is the same as mine: we ought to accept this feature,
> in some form, because it's really quite useful.
>

Even if someone is keen on implementing the multiple connection strings
approach, nothing stops them from doing that on top libpq, and I really
think it will be ever more flexible than anything we can build into libpq
itself.

--
Alex

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

[Shulgin, Oleksandr]

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 give up on the feature.  It's a good enough feature
> that it is worth the possibility of slightly inconveniencing someone
> running in an unusual configuration.

I give up.

Though I still don't see any compelling reason for this to be in libpq
itself. By the way, what about mixing conninfo and uris - should this not
be allowed?

-
Alex

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

[Shulgin, Oleksandr]

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.
>
> Especially when the alternative is a rather short patch implementing an
> otherwise widely available feature.
>

But that won't actually help in the case described by Robert: if the master
server A failed, the client has no idea if B or C would become the new
master.

Unless it actually tries to connect them in turn and check for the result
of pg_is_in_recovery().  I think that brings enough complexity for keeping
this outside of libpq.  Also think about all the extra flexibility people
will likely want to have: number of retries, delay between retries, delay
backoff, etc., to the point we'll have to support some sort of client code
retry_policy_callback.

For read-only clients you might want to include a number of slave
hostnames, and let the connector choose one, but then again you can't
achieve load-balancing on the client side, you're better off using
round-robin DNS.  To add or remove a slave you only need to update DNS, and
not configuration on all the clients.

For the master failover I think a common technique is to just move the
floating IP address from the old master to the new one.  This doesn't
require touching the DNS record.

--
Alex

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

[Robert Haas]

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
>> > 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 widely available feature.
>
> But that won't actually help in the case described by Robert: if the master
> server A failed, the client has no idea if B or C would become the new
> master.

Sure it does.  You just need to ensure that whichever of those is the
new master accepts connections, and the other one doesn't.  There are
lots of ways to do this; e.g. give the machine a second IP that
accepts connections only when the machine is the designated master,
and have read-write clients connect to that IP, and read-only clients
connect to the machine's main IP.

Andres's point is the same as mine: we ought to accept this feature,
in some form, because it's really quite useful.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Robert Haas]

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 standard thing provided
> by most of the client-side drivers in other databases.  Considering what
> currently PostgreSQL offers in terms of high-availability functionality, for
> load-balancing, we need to be careful of many more things like redirecting
> read-queries to standby's, write statements should be executed via
> connection
> to master.

This can be really slow, though, if the master server is completely
offline rather than actively rejecting connections.  Maybe a good idea
would be to have a new connection string parameter, failover_time.  If
not specified or set to a negative value, we use only the
last-specified host=X parameter, just as now.  If set to zero, we try
to connect to everything at once, as Victor proposes.  If set to a
value >0, we try to connect to the servers one after another,
initiating each connection attempt after the number of milliseconds
specified by the parameter.  So then you can do host=A host=B host=C
failover_time=100 and this will launch a connection attempt against A
immediately; if that hasn't completed within 100ms, then we'll also
start one targeting server B, and then after another 100ms we'll try
C.  ISTM that this would cater to several different use cases.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Andres Freund]

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 widely available feature.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Magnus Hagander]

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 way to do this sort of thing:
> > > DNS-based load balancing.  All the clients think they connect to
> > > db.mycompany.com, but which server they actually get is determined by
> > > what IP address the DNS server tells them to use.
> >
> > But that kinda sucks.  I mean, suppose I have three servers, A, B, and
> > C.  I point db.mycompany.com to A, which is the master; then A dies.
> > Under your proposal, whatever script I use to control failover now has
> > to change the DNS records to repoint db.mycompany.com to B, my new,
> > and newly-promoted, new master.   It's quite possible that some
> > machines on the network, or some processes, will have the old IP
> > address cached, and it may be several minutes before those caches time
> > out.  In the meantime, I'm down: even if I bounce the application
> > servers, they may just try to reconnect to A.
>
> The solution to this part seems to be to lower the TTL, which seems
> easy enough.
>

It seems easy enough. Until you actually try to do it, and discover
platforms and appservers that completely ignore the ttl by default (like,
uh, java, which you may have run into - at least it used to do that), or
enforces a lower minimum of longer than you want in order to decrease load,
and things like that.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

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

[Alvaro Herrera]

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 connect to
> > db.mycompany.com, but which server they actually get is determined by
> > what IP address the DNS server tells them to use.
> 
> But that kinda sucks.  I mean, suppose I have three servers, A, B, and
> C.  I point db.mycompany.com to A, which is the master; then A dies.
> Under your proposal, whatever script I use to control failover now has
> to change the DNS records to repoint db.mycompany.com to B, my new,
> and newly-promoted, new master.   It's quite possible that some
> machines on the network, or some processes, will have the old IP
> address cached, and it may be several minutes before those caches time
> out.  In the meantime, I'm down: even if I bounce the application
> servers, they may just try to reconnect to A.

The solution to this part seems to be to lower the TTL, which seems
easy enough.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Robert Haas]

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 to do this sort of thing:
>> > DNS-based load balancing.  All the clients think they connect to
>> > db.mycompany.com, but which server they actually get is determined by
>> > what IP address the DNS server tells them to use.
>>
>> But that kinda sucks.  I mean, suppose I have three servers, A, B, and
>> C.  I point db.mycompany.com to A, which is the master; then A dies.
>> Under your proposal, whatever script I use to control failover now has
>> to change the DNS records to repoint db.mycompany.com to B, my new,
>> and newly-promoted, new master.   It's quite possible that some
>> machines on the network, or some processes, will have the old IP
>> address cached, and it may be several minutes before those caches time
>> out.  In the meantime, I'm down: even if I bounce the application
>> servers, they may just try to reconnect to A.
>
> The solution to this part seems to be to lower the TTL, which seems
> easy enough.

In theory, yeah.  In practice, not all systems obey the TTL, and in my
experience, that's actually a fairly common problem.   Sometimes the
TTL gets enforced separately at multiple levels, so that all of the
old records don't go away for 2 or 3 times the TTL, or occasionally
completely random intervals of time thoroughly unrelated to the TTL
you configured.  And that assumes that the guy who controls the DNS
server is willing to configure a different TTL for you, which is not
always the case.

It also assumes that guy is OK granting access to modify DNS records
to an automated system running on the database server machines.  That
may be OK if the database server is THE ONE THING that needs treatment
of this type, but if the company supports 50 or 100 services that all
need failover handling, suddenly giving all of those things the
ability to reconfigure the DNS server sounds like a pretty poor plan.
Plus, there may be multiple copies of the DNS server in different
geographies, all cloned from a master at the central office.  When the
central office dies, you lose not only the main database server but
also the main DNS server.  That's OK, because the backup DNS servers
still have copies of all the data from the master ... but you can't
make changes until the master is back up.

All of these problems can be solved if you're willing to put enough
time and energy into it.  For example, Akamai has (or had, at the time
I worked there) a service that did very robust geographical
load-balancing and failover.  So you could, like, go buy that, and
maybe it would solve your problem.  By now, there are probably other
companies offering similar services.  I have no doubt that similar
solutions can be crafted from purely open-source software, and there
may very well be great tools available for this that weren't around
the last time I worked as a network administrator.  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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Robert Haas]

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, but which server they actually get is determined by
> what IP address the DNS server tells them to use.

But that kinda sucks.  I mean, suppose I have three servers, A, B, and
C.  I point db.mycompany.com to A, which is the master; then A dies.
Under your proposal, whatever script I use to control failover now has
to change the DNS records to repoint db.mycompany.com to B, my new,
and newly-promoted, new master.   It's quite possible that some
machines on the network, or some processes, will have the old IP
address cached, and it may be several minutes before those caches time
out.  In the meantime, I'm down: even if I bounce the application
servers, they may just try to reconnect to A.

Victor's proposal is far more convenient.  When A goes offline, the
servers automatically begin trying to connect to B and C.  Let's
suppose I use iptables or something like that to prevent connections
to B and C as long as A is online.  Or pg_hba.conf or whatever.  But
once I'm sure A is dead, I can promote B and reconfigure it to allow
connections *and I'm done*.  At most, I need to restart my application
servers.  I don't need access to the DNS server - which the guys in IT
are unlikely to provide to a lowly DBA anyhow.  I don't have to worry
about stale caches.  Everything just works.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Shulgin, Oleksandr]

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,
 don't
 use it.


 +1

 Another thought:  multiple hosts in URI could be used in simple
 configuration for read-only clients. I faced with customers which manages
 two connections in process - to master and to one of several slaves.


Hm, but do they suffer any trouble while doing that *outside* of libpq?
What is the benefit in adding this to libpq itself while it already
provides very rich and finely grained connection control functions?

--
Alex

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

[Teodor Sigaev]

+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 in simple configuration 
for read-only clients. I faced with customers which manages two connections in 
process - to master and to one of several slaves.


--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Oleg Bartunov]

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.

 https://jdbc.postgresql.org/documentation/head/connect.html :

  Connection Fail-over
 
  To support simple connection fail-over it is possible to define multiple
  endpoints (host and port pairs) in the connection url separated by
  commas. The driver will try to once connect to each of them in order
  until the connection succeeds. If none succeed, a normal connection
  exception is thrown.
 
  The syntax for the connection url is:
 
  jdbc:postgresql://host1:port1,host2:port2/database


yes, I also wanted to show this, but you was quicker.




  So I think we ought to reject this proposal, full stop.  I see no
  reason to re-invent this wheel, and there are good reasons not to.

 I don't really buy this argument. Allowing to connect to several
 endpoints isn't exactly new tech either. A lot of database connectors
 do support something very close to the above pgjdbc feature.



mysql, for example.



 Greetings,

 Andres Freund


 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

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

[Victor Wagner]

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 read-write by
  issuing
  
  show transaction_read_only
  
  statement before considering connection useful.
 
 That's not very comfortable, and a lot of middleware software won't easily
 learn the trick.

It shouldn't be left to middleware. It should be hidden into
PQConnectPoll. This function already handle very complicated state
transition diagram, including authentication, SSL negotiation and so on.
If we just add couple of new states such as CONNECTION_ASK_RW_STATUS
and CONNECTION_RW_STATUS_OK it should be fine.

Application would just call PQConnectPoll repeatedly (either via
PQconnectdb or explicitely when readable/writable condition detected on
the socket integrated into app even loop) until success or
unrecoverable error would be achieved. How many interaction with server
it would take, it is not middleware problem.


  It seems to me that in most cases last host in the connect string would
  be only host which accepts connections, so it wins anyway
 
 I'm not saying that it is particularly wide-spread and useful; it could
 happen through careless editing of connection strings or by using a
 connection service file entry
 (http://www.postgresql.org/docs/current/static/libpq-pgservice.html)
 and overriding the host parameter on the command line.


I don't think that host definition from all possible sources 
(service file, environment, command line) should be collected together.
Although it is essential to be clear when host list is appended and when
- replaced. 

If we implement sequential trial of all hosts, then we can start with
last one, to provide compatibility with existing behavior. In this case
if last host is online, no change occur.

Another idea - is to enable multiple host connection only if special
option (loadbalance or failover) present in the connect string.



  Other idea - allow to specify host-port pair as argument of host
  parameter.
  
host=db1.myorg.com:5432
  
  It is consistent with URL syntax and system administrators are used to
  it. And with long list of hosts there is less chances to made an error
  as host and corresponding port come together.
 
 I don't think that is very attactive as it confuses the distinction between
 host and port.  What would you do with
 
host=db1.myorg.com:2345 port=1234
 
I don't think that it does add any more confusion than simultaneous
existence of host and hostaddr, or ability to specify host and port both
in host part of URL and query parameters

postgresql://user@host:5432/dbname?host=otherhostport=2345

Bot really, you've convinced me that syntax with two or three (host, port
and hostaddr) parallel lists is the best. Although we'll need to allow
empty entries in the lists such as

host=master.db.com,standby1.db.com.standby2.db.com port=,2345, 
hostaddr=,192.168.0.4,192.160.1.8

with evident semantic:
get port, host and hostaddr elements with same number, and if some of
them are empty, behave as it was only host and corresponding parameter
not specified at all.

-- 
Victor Wagner vi...@wagner.pp.ru


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Andres Freund]

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 Fail-over
 
 To support simple connection fail-over it is possible to define multiple
 endpoints (host and port pairs) in the connection url separated by
 commas. The driver will try to once connect to each of them in order
 until the connection succeeds. If none succeed, a normal connection
 exception is thrown.
 
 The syntax for the connection url is:
 
 jdbc:postgresql://host1:port1,host2:port2/database


 So I think we ought to reject this proposal, full stop.  I see no
 reason to re-invent this wheel, and there are good reasons not to.

I don't really buy this argument. Allowing to connect to several
endpoints isn't exactly new tech either. A lot of database connectors
do support something very close to the above pgjdbc feature.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tom Lane]

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 very comfortable, and a lot of middleware software won't easily
 learn the trick.

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, but which server they actually get is determined by
what IP address the DNS server tells them to use.

This is a technology that is very well established, known to every
large-site admin, and usable for every Internet-based service.  Even if
libpq had its own nonstandard way of doing something similar, the site
admins would probably still need to use DNS load balancing for other
services.  In fact, they'd still need to use DNS balancing for Postgres,
because not everything connects with libpq (think JDBC for instance).

So I think we ought to reject this proposal, full stop.  I see no
reason to re-invent this wheel, and there are good reasons not to.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Simon Riggs]

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.

 https://jdbc.postgresql.org/documentation/head/connect.html :

  Connection Fail-over
 
  To support simple connection fail-over it is possible to define multiple
  endpoints (host and port pairs) in the connection url separated by
  commas. The driver will try to once connect to each of them in order
  until the connection succeeds. If none succeed, a normal connection
  exception is thrown.
 
  The syntax for the connection url is:
 
  jdbc:postgresql://host1:port1,host2:port2/database


When we discussed this feature at the Dev Meeting in 2014, I thought we
agreed that allowing multiple hosts in the connection string would be OK.

+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.

I think we do need some way of saying that a readonly connection is OK. So
the default would be to connect to each in turn until we find the master.
It should keep retrying for a period of time since for a short period it is
possible there is no master. If you specify readonly, then a connection to
a standby is acceptable and it will stop there.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
http://www.2ndquadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services

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

[''Victor Wagner *EXTERN*' *EXTERN*' *EXTERN*]

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 in turn until we find the master.
 It should keep retrying for a period of time since for a short period it is
 possible there is no master. If you specify readonly, then a connection to

It is very important addition  - to specify that if no host is able to
establish read-write session, we should retry and give a chance for
sever administration to promote one of standbys to master. Probably
there should be additional timeout parameter (we have
connection_timeout, and this would be failover_timeout) with some
reasonaable default.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tatsuo Ishii]

 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 approximately 25
 clients.
 
 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 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
example, the network cable is plugged out). I think round robin DNS is
better because the DNS server will drop the entry corresponding broken
server (or any solution which has similar capability). After all, this
type of client side solutions are not very stable in a real world
environment IMO (I heard the same opinion regarding HAProxy).

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Shulgin, Oleksandr]

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 readonly).
 But haven't yet checked if it is compatible with jdbc syntax.

  the default would be to connect to each in turn until we find the master.
  It should keep retrying for a period of time since for a short period it
 is
  possible there is no master. If you specify readonly, then a connection
 to

 It is very important addition  - to specify that if no host is able to
 establish read-write session, we should retry and give a chance for
 sever administration to promote one of standbys to master. Probably
 there should be additional timeout parameter (we have
 connection_timeout, and this would be failover_timeout) with some
 reasonaable default.


Are we going to put support for every existing and new jdbc feature into
libpq?  One day they might want to add another parameter, e.g. the number
of retries before failing ultimately (hm, and probably, delay between
retries).  Should we already prepare for that?

I believe a good library should provide all the building blocks instead of
trying to envision every possible use case and incorporate them as
convenience functions.  All the described above can be implemented in terms
of existing libpq features rather easily.  Not to mention that the proposed
approach doesn't scale really well, IMO: once you have incorporated all
your database hosts in client's connection string, you need additional
steps to maintain this list on the app configuration side.

And the fact that a lot of other db connector libraries do this in one or
the other way, isn't actually an argument in favor of the feature, at least
not for me.

--
Alex

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

[David Fetter]

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 just do that rather than trying to piece them together from
constituent parts, where the former looks like:

host=service=foo 
sslmode=require,postgresql://bar.baz/mydb?sslmode=require,host=quux.corge 
user=grault port=6433

As far as I can tell, the only way a comma could sneak into these
strings is if it were in a database name or similarly bizarre spot, in
which case the usual quoting needed to handle it in general should
handle it here.

It's not clear to me that libpq is the correct place to add this
feature, as we have fairly large user bases--the JDBC world, for
example--that don't use it.

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Victor Wagner]

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 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 shouldn't happen. My proposal was to connect all servers
simultaneously, and then use that connection which would be established
first closing other ones

Even if we wouldn't do so (to properly randomize server load or to be
compatible with jdbc), there is connection_timeout parameter, so 
client wouldn't seat and just wait for hours while system TCP/IP stack
trying to connect nonexistent server.


 example, the network cable is plugged out). I think round robin DNS is
 better because the DNS server will drop the entry corresponding broken

DNS server wouldn't drop anything unless explicitely told so (by
administrator or by some watchdog software which is able to talk
nsupdate protocol). 

And not everyone database owner has control on his own domain.

Moreover, DNS is distributed system with agressive caching. If our
system is not local, DNS records for non-existing server would be cached
by DNS servers of client's internet provider. 



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tom Lane]

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 shouldn't happen. My proposal was to connect all servers
 simultaneously, and then use that connection which would be established
 first closing other ones

That seems like seriously unfriendly behavior.  It will trigger dropped
connection bleats in the server logs, not to mentioned wasted process
forks.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

['Victor Wagner *EXTERN*']

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 (either system administrator or some
cluster management software) have noticed failure of master and promoted
one of the standbys to master.

So, clients have only to find out which cluster node serves as master
just now.

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.

I consider in my proposal following situations:

1. Warm standby - doesn't accept client connection at all unless
promoted to master.

2. Hot standby - we have two types of clients - one for which readonly
access is sufficient, and other that need to connect only to master.
In this case intention to write is explicitely stated in the connect
string (readonly=false) and connect procedure would check if node it
tries to connect allowed write.

It seems that most people discussing in this thread think in millisecond
time intervals (failure and immediate reconnect).

I was thinking about much longer time intervals - it would probaly take
seconds to cluster management software to notice server failure and
promote backup server to master, it might be possible for application to
spend minute or so trying to reconnect, but it would take
hours to change connect string on clients - it would require visit of
support enginer to each client terminal, if we are thinking of
distributed OLTP system such as point-of-sale network with thick
clients.


 
  host=main-server host=standby1 host=standby2 port=5432 dbname=database
 
 It seems a bit arbitrary to require that all servers use the same port.

 Maybe parameters like host2, port2, host3, port3 etc. might be better.

I've thought about this approach. But PostgreSQL administration guide
insists that all servers in the cluster should have as identical
configuration as possible to simplify administration. 

Moreover I've seldom have seen configurations where postgresql is
accepting connection on non-default port.

Using host1, host2 etc would have unintended connotations, such is this
is first, main server. I think that client should treat all given
servers as equal and let cluster administration to choose which one
would accept connection.

-- 
Victor Wagner vi...@wagner.pp.ru



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Amit Kapila]

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 there.  Most applications wouldn't cope
  well with that.

 It is supposed that somebody (either system administrator or some
 cluster management software) have noticed failure of master and promoted
 one of the standbys to master.

 So, clients have only to find out which cluster node serves as master
 just now.

 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.

 I consider in my proposal following situations:

 1. Warm standby - doesn't accept client connection at all unless
 promoted to master.

 2. Hot standby - we have two types of clients - one for which readonly
 access is sufficient, and other that need to connect only to master.
 In this case intention to write is explicitely stated in the connect
 string (readonly=false) and connect procedure would check if node it
 tries to connect allowed write.

 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)?



With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

[Victor Wagner]

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 simultaneously,
  and begins to work with server which responds first, unless
  loadbalancing parameter is true.
 
 
 I think here you are mixing the behaviour for load balancing solution and
 failover solution.  It seems to me that for client-side failover solution
 (which is also known as Transparent Application Failover), the connection
 attempt to second server should be done after the first connection is
 broken as that provide more flexibility.

I think that failover procedure should begin before first connection is
ever established.

When client application starts, it has no way of knowing current state
of the server cluster - which of servers is working as master now.

Application uses connect string, placed into its configuration file
long time ago, and changing this configuration might require special
permissions, user of application doesn't have. But user typically know
how to restart application or reboot his terminal. So, for the
spatially distributed networks with  thick clients we can handle only 
initial connections, not connection resets. At least application author
always can implement restoration of connection as closing old
connection and establishing new.

So, when application first establishes connection it have to be prepared
to connect any of alternate hosts.

I don't think that making connections in sequential order provide big
flexibility. But it can greatly increase startup time, because connect
to host which is physically down fails after significant timeout. While
application waits for first connect to fail, it might complete session
initialization with working server several times.

Of course, connecting to servers in sequential order is simpler to
implement, and allows even more mixing of load balancing with failover,
because code would be same.


 Although both ideas (load balancing and failover) seems worth discussing,
 they are separate features and can be worked on separately.  It will be
 easier to sort out the details as well that way.

Really load balancing comes almost for free if we implement connect to
alternate server for failover purposes. I'm not sure that in case of hot
standby, where only readonly transactions can be loadbalanced,
loadbalancing is very useful. And included it in the proposal only
because it is very cheap to implement in this form,

 
 With Regards,
 Amit Kapila.
 EnterpriseDB: http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Victor Wagner *EXTERN*]

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 configuration. It is in the people
heads. Or may be in the organizational configuration of the environments
we are talking about.

Each of us imagining some use-case for discussed feature. And these
cases are completely different, and have different typical time
interval.

I haven't explicitely stated my use cases in the proposal. So people
thinking in terms of their use cases, and this is very significant
feedback for me.




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Amit Kapila]

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 hosts specified,
   it attempts to establish connection with all these hosts
 simultaneously,
   and begins to work with server which responds first, unless
   loadbalancing parameter is true.
  
  
  I think here you are mixing the behaviour for load balancing solution and
  failover solution.  It seems to me that for client-side failover solution
  (which is also known as Transparent Application Failover), the connection
  attempt to second server should be done after the first connection is
  broken as that provide more flexibility.

 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 you
have the connection to both the servers then it might not work.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

[Victor Wagner]

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 prepared statement created by the former
 PQprepare call.

Here we are discussing load-balancing on the client level, not on the
statement level.

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 approximately 25
clients.

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).



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Albe Laurenz]

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 some
 cluster management software) have noticed failure of master and promoted
 one of the standbys to master.
 
 So, clients have only to find out which cluster node serves as master
 just now.
 
 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
allow connections before it is promoted.

 I consider in my proposal following situations:
 
 1. Warm standby - doesn't accept client connection at all unless
 promoted to master.
 
 2. Hot standby - we have two types of clients - one for which readonly
 access is sufficient, and other that need to connect only to master.
 In this case intention to write is explicitely stated in the connect
 string (readonly=false) and connect procedure would check if node it
 tries to connect allowed write.

I think that these are both valid use cases.

And as Robert said, there are people out using BDR or other proprietary
multi-master solutions, so there might well be an audience for this feature.

 host=main-server host=standby1 host=standby2 port=5432 dbname=database

 It seems a bit arbitrary to require that all servers use the same port.
 Maybe parameters like host2, port2, host3, port3 etc. might be better.

 I've thought about this approach. But PostgreSQL administration guide
 insists that all servers in the cluster should have as identical
 configuration as possible to simplify administration.

 Moreover I've seldom have seen configurations where postgresql is
 accepting connection on non-default port.

We do it all the time.

 Using host1, host2 etc would have unintended connotations, such is this
 is first, main server. I think that client should treat all given
 servers as equal and let cluster administration to choose which one
 would accept connection.

I don't think that my idea of host, host3 is very appealing myself,
but I still don't like your original proposal of having multiple host
parameters.

One problem with that is that this syntax is already allowed, but
your proposal would silently change the semantics.
Today, if you have multiple host parameters, the last one wins.
So with your modification in place, some connect strings that work today
would start behaving in unexpected ways.

Maybe a better idea would be:
  host=db1.myorg.com,db2.myorg.com port=5432,2345

Yours,
Laurenz Albe

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[''Victor Wagner *EXTERN*' *EXTERN*']

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 (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 read-write by
issuing

show transaction_read_only 

statement before considering connection useful.

 
 And as Robert said, there are people out using BDR or other proprietary
 multi-master solutions, so there might well be an audience for this feature.
 
Unfortunately I have no experience with such solutions, so I'd greatly
appreciate feedback from those people.

I've modelled my proposal after another proprietary solution  - Oracle
RAC.


 One problem with that is that this syntax is already allowed, but
 your proposal would silently change the semantics.
 Today, if you have multiple host parameters, the last one wins.
 So with your modification in place, some connect strings that work today
 would start behaving in unexpected ways.

This is serious argument. But what the use case of these connect strings
now? 

It seems to me that in most cases last host in the connect string would
be only host which accepts connections, so it wins anyway

 
 Maybe a better idea would be:
   host=db1.myorg.com,db2.myorg.com port=5432,2345

I've tried not to introduce new delimiters. But this syntax definitely
have some advantages. At least it allows to specify host-port pairs as
two parallel lists.

Other idea - allow to specify host-port pair as argument of host
parameter. 

  host=db1.myorg.com:5432

It is consistent with URL syntax and system administrators are used to
it. And with long list of hosts there is less chances to made an error
as host and corresponding port come together.

But your variant allows to handle hostaddr parameter same way as host
and port.
-- 
Victor Wagner vi...@wagner.pp.ru


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Albe Laurenz]

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
  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 read-write by
 issuing
 
 show transaction_read_only
 
 statement before considering connection useful.

That's not very comfortable, and a lot of middleware software won't easily
learn the trick.

But even without that use case I think that the feature is probably
worth the effort.

[about having multiple host parameters in the connection string]

  One problem with that is that this syntax is already allowed, but
  your proposal would silently change the semantics.
  Today, if you have multiple host parameters, the last one wins.
  So with your modification in place, some connect strings that work today
  would start behaving in unexpected ways.
 
 This is serious argument. But what the use case of these connect strings
 now?

 It seems to me that in most cases last host in the connect string would
 be only host which accepts connections, so it wins anyway

I'm not saying that it is particularly wide-spread and useful; it could
happen through careless editing of connection strings or by using a
connection service file entry
(http://www.postgresql.org/docs/current/static/libpq-pgservice.html)
and overriding the host parameter on the command line.

  Maybe a better idea would be:
host=db1.myorg.com,db2.myorg.com port=5432,2345
 
 I've tried not to introduce new delimiters. But this syntax definitely
 have some advantages. At least it allows to specify host-port pairs as
 two parallel lists.
 
 Other idea - allow to specify host-port pair as argument of host
 parameter.
 
   host=db1.myorg.com:5432
 
 It is consistent with URL syntax and system administrators are used to
 it. And with long list of hosts there is less chances to made an error
 as host and corresponding port come together.

I don't think that is very attactive as it confuses the distinction between
host and port.  What would you do with

   host=db1.myorg.com:2345 port=1234

Yours,
Laurenz Albe

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Victor Wagner]

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 you
 have the connection to both the servers then it might not work.

Master server might go down when client is not started yet. 
And when client starts up, it has to find out which server to connect
now.

Consider point-of-sale terminals, bank offices or anything else, which
do not work round the clock. Clerk comes to his workplace in the
morning, switches on terminal and inserts her smartcard to authorize
with server. She doesn't need to know what server name is and where it
is located. Either application finds the server automatically, or
support engineer has to be called to fix things.

Moreover, in some situations restart of application (or even client
terminal) is acceptable price for failover, as long as there is no need
to manually fix the configuration.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Amit Kapila]

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 server
goes
  down or is not accessible due to some reason, so for such cases if you
  have the connection to both the servers then it might not work.

 Master server might go down when client is not started yet.
 And when client starts up, it has to find out which server to connect
 now.


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 standard thing provided
by most of the client-side drivers in other databases.  Considering what
currently PostgreSQL offers in terms of high-availability functionality, for
load-balancing, we need to be careful of many more things like redirecting
read-queries to standby's, write statements should be executed via
connection
to master.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

[Albe Laurenz]

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
 failed connections. however, overall i think it is more of a danger than a 
 benefit.

Maybe I misunderstood the original proposal, but my impression was that the 
alternative
servers would be tried only at the time the connection is established, and 
there would be no
such problems as you describe.

Those could only happen if libpq automatically tried to reconnect upon failure 
without
the client noticing.

So the stupid middleware would get an error message, but the reconnect would 
actually work.

Yours,
Laurenz Albe

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[PostgreSQL - Hans-Jürgen Schönig]

 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, however, is that stupid middleware is sometimes not able to 
 handle
 failed connections. however, overall i think it is more of a danger than a 
 benefit.
 
 Maybe I misunderstood the original proposal, but my impression was that the 
 alternative
 servers would be tried only at the time the connection is established, and 
 there would be no
 such problems as you describe.



it would still leave the problem of having a read only on the other side unless 
you are using BDR or so.

regards,

hans




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Albe Laurenz]

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 client application failover is IP address
 migration. This approach has some limitation, i.e. it requires that
 master and backup servers reside in the same subnet or may not be
 feasible for other reasons.
 
 Commercial RDBMS, such as Oracle, employ more flexible approach. They
 allow to specify multiple servers in the connect string, so if primary
 server is not available, client library tries to connect to other ones.
 
 This approach allows to use geographically distributed failover clusters
 and also is a cheap way to implement load-balancing (which is not
 possible with IP address migration).

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.

Once we have multi-master replication that can be used for fail-over,
the picture will change.  Then a feature like that would be very useful indeed.

 host=main-server host=standby1 host=standby2 port=5432 dbname=database

It seems a bit arbitrary to require that all servers use the same port.

Maybe parameters like host2, port2, host3, port3 etc. might be better.

Yours,
Laurenz Albe

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[PostgreSQL - Hans-Jürgen Schönig]

 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 failover in the client libraries. So, only
 way to provide transparent for client application failover is IP address
 migration. This approach has some limitation, i.e. it requires that
 master and backup servers reside in the same subnet or may not be
 feasible for other reasons.
 
 Commercial RDBMS, such as Oracle, employ more flexible approach. They
 allow to specify multiple servers in the connect string, so if primary
 server is not available, client library tries to connect to other ones.
 
 This approach allows to use geographically distributed failover clusters
 and also is a cheap way to implement load-balancing (which is not
 possible with IP address migration).
 
 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.
 
 Once we have multi-master replication that can be used for fail-over,
 the picture will change.  Then a feature like that would be very useful 
 indeed.
 
host=main-server host=standby1 host=standby2 port=5432 dbname=database
 
 It seems a bit arbitrary to require that all servers use the same port.
 
 Maybe parameters like host2, port2, host3, port3 etc. might be better.
 
 Yours,
 Laurenz Albe


i totally agree with laurenz.
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
failed connections. however, overall i think it is more of a danger than a 
benefit.

regards,

hans



 
 -- 
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Robert Haas]

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 server at the other side.

 something like repmgr + pgbouncer will work fine.

Sure, but pgbouncer is an extra hop, and has its own foibles.  There's
real appeal to doing this in the client.

 i agree that once/if we ever have multimaster included then this could
 be a good idea

I think it has a lot of appeal *now*.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tatsuo Ishii]

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 simple procotol is used, same thing can be said to SQL
PREPARE/EXECUTE/DEALLOCATE.

SQL BEGIN/COMMIT/ROLLBACK would be more interesting example in load
balancing mode.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Amit Kapila]

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, unless
 loadbalancing parameter is true.


I think here you are mixing the behaviour for load balancing solution and
failover solution.  It seems to me that for client-side failover solution
(which is also known as Transparent Application Failover), the connection
attempt to second server should be done after the first connection is
broken as that provide more flexibility.


 If the loadbalancing parameter is true, it tries servers sequentially in
 the random order.

 If the parameter readonly is false, after authenticating with server it
 executes show transaction_read_only, to find out whether current
 connection is to the master or to the hot standby, and connection is
 considered successful only if server allows read write transactions.


Although both ideas (load balancing and failover) seems worth discussing,
they are separate features and can be worked on separately.  It will be
easier to sort out the details as well that way.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

[Robert Haas]

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 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
 failed connections. however, overall i think it is more of a danger than a 
 benefit.

 Maybe I misunderstood the original proposal, but my impression was that the 
 alternative
 servers would be tried only at the time the connection is established, and 
 there would be no
 such problems as you describe.

 it would still leave the problem of having a read only on the other side 
 unless you are using BDR or so.

That doesn't make this a bad idea.  Some people are using replication
solutions that can cope with this already (EDB has a proprietary
product, and I'm sure there are people using BDR, too) and, as the
solutions get better and more widely deployed, more people will want
to do it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Jaime Casanova]

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 libraries. So, only
 way to provide transparent for client application failover is IP address
 migration. This approach has some limitation, i.e. it requires that
 master and backup servers reside in the same subnet or may not be
 feasible for other reasons.


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 server at the other side.

something like repmgr + pgbouncer will work fine.

i agree that once/if we ever have multimaster included then this could
be a good idea

-- 
Jaime Casanova  www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Victor Wagner]

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 is IP address
migration. This approach has some limitation, i.e. it requires that
master and backup servers reside in the same subnet or may not be
feasible for other reasons.

Commercial RDBMS, such as Oracle, employ more flexible approach. They
allow to specify multiple servers in the connect string, so if primary
server is not available, client library tries to connect to other ones.

This approach allows to use geographically distributed failover clusters
and also is a cheap way to implement load-balancing (which is not
possible with IP address migration).

Proposed change
===

Allow to specify multiple hosts in the libpq connect string. Make libpq
attempt to connect to all host simultaneously or in random order 
and use of the server which successfully establishes connection first.


Syntax
--


Libpq connect string can be either set of the keyword=value pairs
or an URL. 

In the first form it can be just allowed to specify keyword host
multiple times.

host=main-server host=standby1 host=standby2 port=5432 dbname=database

In the second form host can be specified either in the first part of URL
or in the query parameters.

postgresql://user@host/database

postgresql:///database?host=hostnameuser=username

If host is specified as a parameter, it is also possible to allow
multiple host parameters without breaking existing syntax.

postgresql:///database?host=main-serverhost=standby1host=standby2

In order to implement load-balancing clusters, additional parameters
should be added readonly=boolean and loadbalancing=boolean

Support for this syntax extensions is added to the PQconnectdb, 
PQconnectdbParams, PQConnectStart and PQConnectStartParams,
but not PQsetdb/PQsetdblogin functions. 


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, unless
loadbalancing parameter is true.

If the loadbalancing parameter is true, it tries servers sequentially in 
the random order.

If the parameter readonly is false, after authenticating with server it
executes show transaction_read_only, to find out whether current
connection is to the master or to the hot standby, and connection is
considered successful only if server allows read write transactions.

This allows to have clients which write to the database and clients
which perform read-only access. Read-only clients would be load-balanced
between the master and slave servers, and read-write clients connect only to
the master (whichever server has this role at the moment of connection).

Information of the alternate servers should be stored in the PGconn structure.

Function PQreset should be able to take advantage of new syntax and
possibly open connection to the new master, if failover occurred
during lifetime of the connection.

Possible drawbacks
==

Compatibility
-

Proposed patch requires no modifications to the server or protocol, and 
modification of synchronous function (PQconnectdb, PQconnectdbParams) 
doesn't introduce incompatible changes to the client library. 

Even if connect string with multiple host would be erroneously used
with version of libpq, which do not support this feature, it is not an
error.  It just use last host specified in the connect string.

There could be some compatibility problems with asynchronous connections
created with PQConnectStart functions. Problem is that we are trying
to establish several connections at once, and there are several sockets
which should be integrated into application event loop.

Even if we would try servers in some particular order (such as randomized
order during load balancing), file descriptor of socket can change during
execution PQConnectPoll, and existing applications are not prepared to it.

Performance impact
--

Performance impact seems to be negligible.

1. If connect string contain only one host, the only complication is the
maintenance of the data structure, which possible can hold more than
one host name. Connection process itself would not be affected.

2. If there is pure high-availability cluster, i.e. standby servers do
not accept client connections on the specified port, there is no extra
load on standby servers, and almost no (only several unsuccessful
connect calls) on client.

3. If there is load balancing cluster, there is no performance impacts
for read-only client, but each read-write client causes standby servers
to process extra connection to the point where server can report
read-only state of transaction (i.e. including SSL handshake and