Обсуждение: Client IP in Patroni

Поиск
Список
Период
Сортировка

Client IP in Patroni

От
"Anjul Tyagi"
Дата:
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


Re: Client IP in Patroni

От
Ron
Дата:
On 11/21/22 09:14, Anjul Tyagi wrote:
#x4c7f8ad52f284f3 #xc9d58b400fe04aa4b229b62f4952033b p.MsoNormal {margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;}#x4c7f8ad52f284f3 #xf5dcd4e5bcaf4cd793556e604d1ae148 p.MsoNormal {font-size: 11pt; font-family: Calibri, sans-serif; margin: 0in 0in 0pt;}blockquote.cite { margin-left: 5px; margin-right: 0px; padding-left: 10px; padding-right:0px; border-left: 1px solid #cccccc }blockquote.cite2 {margin-left: 5px; margin-right: 0px; padding-left: 10px; padding-right:0px; border-left: 1px solid #cccccc; margin-top: 3px; padding-top: 0px; }a img { border: 0px; }li[style='text-align: center;'], li[style='text-align: center; '], li[style='text-align: right;'], li[style='text-align: right; '] { list-style-position: inside;}body { font-family: Calibri; font-size: 11pt; }.quote { margin-left: 1em; margin-right: 1em; border-left: 5px #ebebeb solid; padding-left: 0.3em; }
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.

Re: Client IP in Patroni

От
"Jim Chanco Jr."
Дата:
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.

Re: Client IP in Patroni

От
"Jim Chanco Jr."
Дата:
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.

Re: Client IP in Patroni

От
Ninad Shah
Дата:
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


Re: Client IP in Patroni

От
Michael Banck
Дата:
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