pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedClient IP in Patroni
6+ messages / 5 participants
[nested] [flat]
* Client IP in Patroni
@ 2022-11-21 15:14 Anjul Tyagi <anjul@ibosstech-us.com>
0 siblings, 2 replies; 6+ messages in thread
From: Anjul Tyagi @ 2022-11-21 15:14 UTC (permalink / raw)
To: pgsql-admin
Team,
We have setup in Postgres HA Cluster using Patroni. Also we have used
the HAProxy for load balancing...
On Postgres side, we are getting HaProxy IP instead of Client IP, while
checking in pg_stat_activity tables. Can you please suggest the changes
needs to be done on HAProxy or Postgres side?
Regards,
Anjul TYAGI
Group Technical Manager
If a solution exists, I’ll deliver it; If it doesn't exist, I’ll create
it
ü Go Green
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Client IP in Patroni
@ 2022-11-21 15:25 Ron <ronljohnsonjr@gmail.com>
parent: Anjul Tyagi <anjul@ibosstech-us.com>
1 sibling, 1 reply; 6+ messages in thread
From: Ron @ 2022-11-21 15:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/21/22 09:14, Anjul Tyagi wrote:
> Team,
>
> We have setup in Postgres HA Cluster using Patroni. Also we have used the
> HAProxy for load balancing...
>
> On Postgres side, we are getting HaProxy IP instead of Client IP, while
> checking in pg_stat_activity tables. Can you please suggest the changes
> needs to be done on HAProxy or Postgres side?
Postgresql does not know that HAProxy is a proxy. It just knows that an IP
address (which just happens to come from an HAProxy server) has connected
and "doing stuff".
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Client IP in Patroni
@ 2022-11-21 15:28 Jim Chanco Jr. <jim.chanco@crunchydata.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jim Chanco Jr. @ 2022-11-21 15:28 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
AFAIK- HAProxy can only append an x-forwarded-for in layer 7 mode (http).
Unfortunately, using HAProxy for PostgreSQL connections is layer 4 (tcp).
The best you could do would be to route the connection through something
like pgbouncer and have it append the source IP in the app name.
Kind regards,
Jim
On Mon, Nov 21, 2022 at 10:25 AM Ron <ronljohnsonjr@gmail.com> wrote:
> On 11/21/22 09:14, Anjul Tyagi wrote:
>
> Team,
>
> We have setup in Postgres HA Cluster using Patroni. Also we have used the
> HAProxy for load balancing...
>
> On Postgres side, we are getting HaProxy IP instead of Client IP, while
> checking in pg_stat_activity tables. Can you please suggest the changes
> needs to be done on HAProxy or Postgres side?
>
>
> Postgresql does not know that HAProxy is a proxy. It just knows that an
> IP address (which just happens to come from an HAProxy server) has
> connected and "doing stuff".
>
> --
> Angular momentum makes the world go 'round.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Client IP in Patroni
@ 2022-11-21 15:30 Jim Chanco Jr. <jim.chanco@crunchydata.com>
parent: Jim Chanco Jr. <jim.chanco@crunchydata.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jim Chanco Jr. @ 2022-11-21 15:30 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
If/when PostgreSQL adds support for TPROXY, then you'd be able to see the
source IP.
This thread might be useful to you:
https://www.postgresql.org/message-id/20190519153623.GB13208%40thinkpad
On Mon, Nov 21, 2022 at 10:28 AM Jim Chanco Jr. <jim.chanco@crunchydata.com>
wrote:
> AFAIK- HAProxy can only append an x-forwarded-for in layer 7 mode (http).
> Unfortunately, using HAProxy for PostgreSQL connections is layer 4 (tcp).
> The best you could do would be to route the connection through something
> like pgbouncer and have it append the source IP in the app name.
>
> Kind regards,
>
> Jim
>
> On Mon, Nov 21, 2022 at 10:25 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
>> On 11/21/22 09:14, Anjul Tyagi wrote:
>>
>> Team,
>>
>> We have setup in Postgres HA Cluster using Patroni. Also we have used the
>> HAProxy for load balancing...
>>
>> On Postgres side, we are getting HaProxy IP instead of Client IP, while
>> checking in pg_stat_activity tables. Can you please suggest the changes
>> needs to be done on HAProxy or Postgres side?
>>
>>
>> Postgresql does not know that HAProxy is a proxy. It just knows that an
>> IP address (which just happens to come from an HAProxy server) has
>> connected and "doing stuff".
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Client IP in Patroni
@ 2022-11-21 18:57 Ninad Shah <ninad.shah@percona.com>
parent: Anjul Tyagi <anjul@ibosstech-us.com>
1 sibling, 1 reply; 6+ messages in thread
From: Ninad Shah @ 2022-11-21 18:57 UTC (permalink / raw)
To: Anjul Tyagi <anjul@ibosstech-us.com>; +Cc: pgsql-admin
Hi Anjul,
There is not a simple solution to it, because as Jim already mentioned that
HAproxy works on the 4th layer for PostgreSQL, it is difficult for HAproxy
to meet the expectations here.
Also, it is not compulsory to use HAProxy as a front of Patroni. You may
use Pgbouncer instead of HAProxy as well, however there some concerns
associated with it.
Thanks,
--
Ninad Shah
PostgreSQL DBA I, Managed Services
e: ninad.shah@percona.com
w: www.percona.com
Databases Run Better with Percona.
On Mon, Nov 21, 2022 at 8:44 PM Anjul Tyagi <anjul@ibosstech-us.com> wrote:
> Team,
>
> We have setup in Postgres HA Cluster using Patroni. Also we have used the
> HAProxy for load balancing...
>
> On Postgres side, we are getting HaProxy IP instead of Client IP, while
> checking in pg_stat_activity tables. Can you please suggest the changes
> needs to be done on HAProxy or Postgres side?
>
>
>
>
>
>
> Regards,
>
>
> Anjul *TYAGI*
>
> Group Technical Manager
>
>
> *If a solution exists, I’ll deliver it; If it doesn't exist, I’ll create
> it*
>
>
> *ü* *Go Green*
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Client IP in Patroni
@ 2022-11-21 19:07 Michael Banck <mbanck@gmx.net>
parent: Ninad Shah <ninad.shah@percona.com>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Banck @ 2022-11-21 19:07 UTC (permalink / raw)
To: Ninad Shah <ninad.shah@percona.com>; +Cc: Anjul Tyagi <anjul@ibosstech-us.com>; pgsql-admin
Hi,
On Tue, Nov 22, 2022 at 12:27:38AM +0530, Ninad Shah wrote:
> Also, it is not compulsory to use HAProxy as a front of Patroni. You
> may use Pgbouncer instead of HAProxy as well, however there some
> concerns associated with it.
Another option would be to use vip-manager in order to maintain a
virtual ip on the leader node, or to use client-based failover by
putting all Patroni hosts in the connection string along with the
target_session_attrs option.
Michael
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-11-21 19:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-21 15:14 Client IP in Patroni Anjul Tyagi <anjul@ibosstech-us.com>
2022-11-21 15:25 ` Ron <ronljohnsonjr@gmail.com>
2022-11-21 15:28 ` Jim Chanco Jr. <jim.chanco@crunchydata.com>
2022-11-21 15:30 ` Jim Chanco Jr. <jim.chanco@crunchydata.com>
2022-11-21 18:57 ` Ninad Shah <ninad.shah@percona.com>
2022-11-21 19:07 ` Michael Banck <mbanck@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox