Precedence: bulk --------=_MBEC681645-AFB4-40E2-B6C2-AA6D561E947C Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Team, We have setup in Postgres HA Cluster using Patroni. Also we have used=20 the HAProxy for load balancing... On Postgres side, we are getting HaProxy IP instead of Client IP, while=20 checking in pg_stat_activity tables. Can you please suggest the changes=20 needs to be done on HAProxy or Postgres side? Regards, Anjul TYAGI Group Technical Manager If a solution exists, I=E2=80=99ll deliver it; If it doesn't exist, I=E2=80= =99ll create=20 it =C3=BC Go Green --------=_MBEC681645-AFB4-40E2-B6C2-AA6D561E947C Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Team,

We have setup in Postgres HA Cl= uster using Patroni. Also we have used the HAProxy for load balancing...

On Postgres side, we are getting HaProxy IP instea= d of Client IP, while checking in pg_stat_activity tables. Can you please s= uggest the changes needs to be done on HAProxy or Postgres side?=C2=A0

=C2=A0

= Regards,

Anjul TYAGI

Group Technical Manager<= /span>

If a solution exists, I=E2=80=99ll deliv= er it; If it doesn't exist, I=E2=80=99ll create it

=C3=BC=C2=A0Go=C2=A0Green

--------=_MBEC681645-AFB4-40E2-B6C2-AA6D561E947C-- From ronljohnsonjr@gmail.com Mon Nov 21 15:25:33 2022 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ox8g4-00083G-7y for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 15:25:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ox8g2-0002UZ-Pe for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 15:25:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ox8g2-0002UP-FJ for pgsql-admin@lists.postgresql.org; Mon, 21 Nov 2022 15:25:42 +0000 Received: from mail-qt1-x82c.google.com ([2607:f8b0:4864:20::82c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ox8fw-00057C-1s for pgsql-admin@lists.postgresql.org; Mon, 21 Nov 2022 15:25:41 +0000 Received: by mail-qt1-x82c.google.com with SMTP id h21so7439248qtu.2 for ; Mon, 21 Nov 2022 07:25:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=Hs8UZSRKVBVS8XGWjoqRcDYOhmCU+Gww6e2NsLfBGug=; b=C6BhZk1SKC4awTVoQT1slB7ADwF58Phm6Y1Bkqio0P6wK/zTIySETz/feeef8b9sCz h9tGINVnoxqPTJOSIG3dX48wiVhEoGQZTiQu5y+puhA+WMp2xyWqHz6DANAm0I65/ZJT 1m81PTgszOg45jGmL87H5zAVLdoSCntMHLiw6gpO9R+IWminSD99mXzLfL2ZVV+j9zuD P69cw/rPF4jbc8gCYvnTdxA6MKL5ZheEdb2XEjyXJpd6dm0HG6whgfa2wY8RStA1dQcI m1qZaXZTlTY6NWAWcfeWlJBl8KA9C7CUJ0iMEvQTBl8riCcn729k4oCB6FEMep+QAFeQ 6A/Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=Hs8UZSRKVBVS8XGWjoqRcDYOhmCU+Gww6e2NsLfBGug=; b=iJchUpVvedp02VF01Fywnf5oavCECL+x/n4PZkKAs42aLefQt3eDU+B2zLB5BDs3ne e0lgUetGftz/17CINjghXB8AeDgX5eOshFAKA4Uxxz28krP463lwT3gW3PYD+PEgCTrO pZyfez3FirJaAlo488OfQp33h6mmCV0hXAkFnBdUxwH5YkBi8+OvHQz3Kn4TXTWznG/n tITvh1vKpXND/fuayfq2ehl/gcXjKbiAGWeiNbGxgm4Sc/URGJLFtWV58MNfpHH7P05H eqt82Anry2hxeGXdAoKke6gz1ie/l2vd1rXc5g/IpMr/Dprz+I+8En6Mtol1KCI1ILKU Eb6w== X-Gm-Message-State: ANoB5pmZyrWGBukYRgwrdl+yW3zGxVKv1VgXrkeYZubJNSm1iBKYI1HO Xuzip30rPhxZtX8rEL5MXMqwR5+qnic= X-Google-Smtp-Source: AA0mqf5kQm3w6YqPRQh6SKI1m0puJ6reyq7WPaG6k10rUVxg/YhZOxaTnM1iavuNpdziS4PnnkgCnA== X-Received: by 2002:a05:622a:5a15:b0:3a5:3388:4089 with SMTP id fy21-20020a05622a5a1500b003a533884089mr17427814qtb.601.1669044334677; Mon, 21 Nov 2022 07:25:34 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id cm5-20020a05622a250500b0039853b7b771sm6883227qtb.80.2022.11.21.07.25.34 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 21 Nov 2022 07:25:34 -0800 (PST) Content-Type: multipart/alternative; boundary="------------JBwV0Zt5crRt0dQXQN0yLh6q" Message-ID: <3b72f6be-3d45-d857-bcb7-86c031d7a6bf@gmail.com> Date: Mon, 21 Nov 2022 09:25:33 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Client IP in Patroni Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------JBwV0Zt5crRt0dQXQN0yLh6q Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------JBwV0Zt5crRt0dQXQN0yLh6q Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.

--------------JBwV0Zt5crRt0dQXQN0yLh6q-- From jim.chanco@crunchydata.com Mon Nov 21 15:28:37 2022 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ox9G6-0001jQ-1f for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 16:02:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ox9G4-0001Zk-V4 for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 16:02:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ox8j7-0003aq-MI for pgsql-admin@lists.postgresql.org; Mon, 21 Nov 2022 15:28:53 +0000 Received: from mail-wr1-x431.google.com ([2a00:1450:4864:20::431]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ox8j3-0005Ai-NV for pgsql-admin@lists.postgresql.org; Mon, 21 Nov 2022 15:28:52 +0000 Received: by mail-wr1-x431.google.com with SMTP id v1so20348143wrt.11 for ; Mon, 21 Nov 2022 07:28:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=crunchydata.com; s=google; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=wCYEMVsM3M0+rr521JleNtRPL7q0xV5wjpsIxp6VuRY=; b=N/bPXeujdnJq4yUv5e1ns13WXPfXZhMXpe/AU7y28+2Hxf0Z+jrbz+soisK8AumbT0 7VBP9FKSurkRGhTAjUYSpn1iYDBta9nuZZ+EvI6Ls9spNW5Biei/atF6FEHL1G93uKMB gTC+wqVt50EjBvx3JMfrNVncn4TOjsp/8zK6eeGyx9tQsYpR1nNJWoLHCoq5mp7OhMRT jXhqoXgY9w+MgSeinpkgXkhTwoJmEe4xg9Fxt6I5pHkNRepCatIRGVnzztF8gpOfsa0n VIOPpQksXRf5XX8BcEyeb9mcm2tq04WwUeGvQYfxnlPtYV2NBrNPSvZdMJsgxClJTMPc fGZQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=wCYEMVsM3M0+rr521JleNtRPL7q0xV5wjpsIxp6VuRY=; b=M5k1B3y1qwrvg67LGSySO67qtb3VcW9NzXrwPCsSId8b29xkymOYRL3VV4Z+/x84Wm clu1G4ma15RsVjl8XTntutLIIJwl/g5ld7kAtp3Y9V8YF7zt0davuegob2+q35tF/6Mi b+ynW9tlFGf6BFg2DKB/x39x4qeh3ONsgiv2Nf+3hKKW9r36ZFfUM36qnFsfkgbBLKI2 +uVzTY1JhvBza4aRMPZKWlWc4Ye9PqSWjH52dOmg9WFz+owAn4uCDrNFbA2faPaLAKB5 cIkfWCGM062bHsku7oRc5tWSmcEBKVtvcp17nDDPdZ2kXpB7tTnAQTgy5UKdUXAIzo74 SMAg== X-Gm-Message-State: ANoB5pkd9mM90vYQoPoy7yclHVzaSt3xr3Li4ROYWmZvjx925Fgv+5mn reZ8KNRjak/4no0m7llMc1GrKj2G+bpR+psMV8M7+KWIZvg= X-Google-Smtp-Source: AA0mqf5Pd0dUHstYUxmot/pssQW8NMIYeUXq9cN6Uw+1M6Da1mG8T0WouYtd00np10J8refbx27fSQ7mS7ZXtMWf+Ts= X-Received: by 2002:adf:fd46:0:b0:22e:382d:b06 with SMTP id h6-20020adffd46000000b0022e382d0b06mr11015274wrs.265.1669044528221; Mon, 21 Nov 2022 07:28:48 -0800 (PST) MIME-Version: 1.0 References: <3b72f6be-3d45-d857-bcb7-86c031d7a6bf@gmail.com> In-Reply-To: <3b72f6be-3d45-d857-bcb7-86c031d7a6bf@gmail.com> From: "Jim Chanco Jr." Date: Mon, 21 Nov 2022 10:28:37 -0500 Message-ID: Subject: Re: Client IP in Patroni To: Ron Cc: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000382cf205edfcb68c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000382cf205edfcb68c Content-Type: text/plain; charset="UTF-8" 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 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. > --000000000000382cf205edfcb68c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

AFAIK- HAProxy can only append an x-forwarded-fo= r in layer 7 mode (http). Unfortunately, using HAProxy for PostgreSQL conne= ctions is layer 4 (tcp). The best you could do would be to route the connec= tion through something like pgbouncer and have it append the source IP in t= he app name.

Kind regards,

Jim

On Mon, Nov 21= , 2022 at 10:25 AM Ron <ronlj= ohnsonjr@gmail.com> wrote:

=20 =20 =20
On 11/21/22 09:14, Anjul Tyagi wrote:

=20 =20 =20
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.=C2=A0 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.

--000000000000382cf205edfcb68c-- From jim.chanco@crunchydata.com Mon Nov 21 15:30:46 2022 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ox9IN-0001sB-Ux for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 16:05:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ox9IM-0003Yb-Ol for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 16:05:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ox8lB-0007D7-LY for pgsql-admin@lists.postgresql.org; Mon, 21 Nov 2022 15:31:01 +0000 Received: from mail-wr1-x431.google.com ([2a00:1450:4864:20::431]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ox8l8-0006KT-Uq for pgsql-admin@lists.postgresql.org; Mon, 21 Nov 2022 15:31:01 +0000 Received: by mail-wr1-x431.google.com with SMTP id d1so8280972wrs.12 for ; Mon, 21 Nov 2022 07:30:58 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=crunchydata.com; s=google; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=fEY0LEmzgr18TClA2+yT6RQRpocvj0zWGjmkm4Nta+s=; b=Tz1gYD0S3c64BS8B2pZp3R7ds0WDw5Va1Z+qwNKL+WmAoLLp+jEckE+x8EBJ8rA/eJ NrPzr/4KdZv3nhVmG7r2xpOMamS5XDGPsm299EclWah+UHxx7BiqllRdk/yUfgRoIkCr Niu+tUSrRBJcy7Edfhi6V+y/sWPiqT7bB7IQlwIHTQoefipvdakSARtes9NvHj/ByJNr suFNNZI71h5UbvsII7WhAADPNNnsO11u+GgiiIbJKPlmHN9ug6JC0du7e78ExE0UPekg DKl7bFs3dJVMKIk6KcCFmW5zXQGku2cVFK6P/0/1YTVKI3+0WbYEPQPSCHyyau7zaDdJ iqFg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=fEY0LEmzgr18TClA2+yT6RQRpocvj0zWGjmkm4Nta+s=; b=GEbdWDj4vdLfDB96sKmf7rb+32WtMIpyFWfM4ByNsglo273JXZmPdLdtrUeJ9oIFOz ONswT2fe8xYDOWA+AUJne+LX9tALuEhVCRwLfIPh5dK7FRnVOkxUQtqR0J3yfXhBtp9b tYFXq9HSY4xIFQWfflgba8EpiTZrZ6BumEds8bhv8DiwtyrdDjKNGE8Scc3iagvDNEiY NPLf64gMD38FXl7aqR796Xi1rK1RnaGN+9b+9qkgM6iXbZuco6EzKcVWizCcNa33aTzd 110fI443zf2Prio1A1ZCnmsHLYiCKTHFKlSMh4onLUS0gynC5b3QRqYUaftQy5Wu3scC EZzQ== X-Gm-Message-State: ANoB5pkDmjxpVyWspOmKu7PP6Yn3p/jDoge7DVat6vChMgulUnVB2Dy1 49cSHCVmTewBVlHwwJ4pyOl0UJZi9eyylkwNUje10Q== X-Google-Smtp-Source: AA0mqf7qprB9sk0GeTmXhnpwhCWmwBWLnkeZ6N/1Dp6OEgEN9FmebSccJ3Xry2fqqQeM29RMWH8PUYaFcbBPiT9pNbk= X-Received: by 2002:a5d:4c8c:0:b0:241:c222:e27e with SMTP id z12-20020a5d4c8c000000b00241c222e27emr7157210wrs.236.1669044657890; Mon, 21 Nov 2022 07:30:57 -0800 (PST) MIME-Version: 1.0 References: <3b72f6be-3d45-d857-bcb7-86c031d7a6bf@gmail.com> In-Reply-To: From: "Jim Chanco Jr." Date: Mon, 21 Nov 2022 10:30:46 -0500 Message-ID: Subject: Re: Client IP in Patroni To: Ron Cc: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000f2c44605edfcbdcc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f2c44605edfcbdcc Content-Type: text/plain; charset="UTF-8" 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. 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 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. >> > --000000000000f2c44605edfcbdcc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

If/when PostgreSQL adds support for TPROXY, then you&= #39;d be able to see the source IP.

This thread might be usef= ul to you:

https://www.postgresql.org/message-id/201905191= 53623.GB13208%40thinkpad

On Mon, Nov 21, 2022 at 10:28 AM Jim Chanco J= r. <jim.chanco@crunchydata= .com> wrote:

AFAIK- HAProxy can only append an x-forwarde= d-for in layer 7 mode (http). Unfortunately, using HAProxy for PostgreSQL c= onnections is layer 4 (tcp). The best you could do would be to route the co= nnection through something like pgbouncer and have it append the source IP = in the app name.

Kind regards,

Jim

=
On Mon, No= v 21, 2022 at 10:25 AM Ron <ronljohnsonjr@gmail.com> wrote:
=20 =20 =20
On 11/21/22 09:14, Anjul Tyagi wrote:

=20 =20 =20
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.=C2=A0 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.

--000000000000f2c44605edfcbdcc-- From ninad.shah@percona.com Mon Nov 21 18:57:38 2022 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxBzP-0001Sw-DI for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 18:57:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oxBzO-0002jJ-9E for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 18:57:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxBzN-0002jA-Pf for pgsql-admin@lists.postgresql.org; Mon, 21 Nov 2022 18:57:53 +0000 Received: from mail-ua1-x934.google.com ([2607:f8b0:4864:20::934]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oxBzL-0007XR-5T for pgsql-admin@postgresql.org; Mon, 21 Nov 2022 18:57:52 +0000 Received: by mail-ua1-x934.google.com with SMTP id e26so4388077uaa.7 for ; Mon, 21 Nov 2022 10:57:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=percona.com; s=google; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Sh7AlrVLQAavoxAFpjMSVHE2VGshEy6A7OTVz0bkH7w=; b=D0xSQUWsMcGkkzC+U51Xd5vBmcR/sd+9B0HrJeijspzqyTpZHu1DT7tXSx97WZV088 Wa7ZjUPRKMugji0b+8pFN80dxb/I4gLtaOwKKZ2TyhG3ckDZV0PW2iK4R7uOnChtfgvL q0CJSkWA6XmUwn51qw9gfSmx0ZNAzYK/A5qU0= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Sh7AlrVLQAavoxAFpjMSVHE2VGshEy6A7OTVz0bkH7w=; b=F9tzyZXwfskE/qosvJaYSrIAhsKMd8IRbaBIWJgRB1Vyy9h0vhTkbRVOa/lT0IEjFR q+tWzYb/Yji5TOj1SPUgXInLTSKbk7aQH2sK/4tnzcG7THvHB3ancrvvR52k8xeIJTa1 NZ7VoTH9/S5htyqFWk94iGAcSrZ7DF54txhYqFOZQ/BDXxH7B+pB3NSrzeQZLW6h3l8e 8O49fZbSC+uCsMo9X7XqcB/NhsbcsFaGm7cdYU5Q90mtZ5/VjfyDnYH4bDQeth2xiiFu aJ3UzyWP2tnj33kCY5r72h/q4NX2uTrZGZW3VtwNgwLX9xXqIRadSVUb9ShIMw2ijoKM 2fjQ== X-Gm-Message-State: ANoB5pmBqdUdcJvIGEXUVdF5oaWMaD5Ur2erGERbcmo53bmAdWwXRvqr ZcQy2UiNLpX7Eh2YCOz2mHEEkpsSRdUslb0O0VOkARFDFHef3pTAs72EEd4N2EcbtElyoVtHk+v tUjkAkdPWTQIqHGQWjHay/gK2FlW3WZTOVd6S5X0HDF9Dqkwxjdmk5THwuM8gH4ynP4NxS/+xod 1VSFqtAaQ+WnulGNruYTfN5e4vnzHxGKhf9Im5nGm4kqM5XGow X-Google-Smtp-Source: AA0mqf5S9S45AF2/xASwk3Nw1WFkdc82RdLe3fiRh3FanZPS2oJsGIKlHjCX87i0MVErik8zJojVUe6dX3xAb6B1owA= X-Received: by 2002:ab0:20b5:0:b0:418:bee3:5614 with SMTP id y21-20020ab020b5000000b00418bee35614mr2462589ual.20.1669057069608; Mon, 21 Nov 2022 10:57:49 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ninad Shah Date: Tue, 22 Nov 2022 00:27:38 +0530 Message-ID: Subject: Re: Client IP in Patroni To: Anjul Tyagi Cc: pgsql-admin Content-Type: multipart/alternative; boundary="000000000000be929f05edffa12c" X-CLOUD-SEC-AV-Sent: true X-CLOUD-SEC-AV-Info: percona,google_mail,monitor X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000be929f05edffa12c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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=E2=80=99ll deliver it; If it doesn't exist, I=E2= =80=99ll create > it* > > > *=C3=BC* *Go Green* > > --000000000000be929f05edffa12c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi Anjul,

There is not a simple solutio= n to it, because as Jim already mentioned that HAproxy works on the 4th lay= er for PostgreSQL, it is difficult for HAproxy to meet the expectations her= e.

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.

Thank= s,

On Mon, Nov 21= , 2022 at 8:44 PM Anjul Tyagi <anjul@ibosstech-us.com> wrote:

Team,

We have setup in Postgres HA Clust= er 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 sugges= t the changes needs to be done on HAProxy or Postgres side?=C2=A0

=C2=A0

=C2=A0

=C2=A0

<= font face=3D"Calibri">
Regards,<= u>

<= font face=3D"Calibri">Anjul TYAGI
Group Technical Manager=

If a solution exists, I=E2=80=99ll delive= r it; If it doesn't exist, I=E2=80=99ll create it

=C3=BC=C2=A0Go=C2=A0Gre= en

--000000000000be929f05edffa12c-- From mbanck@gmx.net Mon Nov 21 19:07:56 2022 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxC9E-0001u1-Ax for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 19:08:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oxC9D-0006ui-5c for pgsql-admin@arkaria.postgresql.org; Mon, 21 Nov 2022 19:08:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxC9C-0006uZ-O3 for pgsql-admin@lists.postgresql.org; Mon, 21 Nov 2022 19:08:02 +0000 Received: from mail-ej1-x62f.google.com ([2a00:1450:4864:20::62f]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oxC9A-0000N3-Bg for pgsql-admin@postgresql.org; Mon, 21 Nov 2022 19:08:02 +0000 Received: by mail-ej1-x62f.google.com with SMTP id m22so30676832eji.10 for ; Mon, 21 Nov 2022 11:07:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :subject:cc:to:from:date:sender:message-id:from:to:cc:subject:date :message-id:reply-to; bh=9ygTgi55dKJntHmr4mmKzavaUMqTIPFJ5hmiuUL6E1Y=; b=E7NViAgRm7rEDAVvDF51saVsNTMxCLaGYwl8L7fOjL3ftbD9TOX79wcv5orTMRGHFf mSIxy8p8X8EiyjmJvkn3cMaj76su92VBU544gHyOcAZmxTxxo6zH5sRHgzAEuzV53bHm K21M4FEiXqdSTwue3UOq+YFyiSHQICweJ3+EXPfOwNQZffcRhAMS+5y55o65LJhOd/Vz Qxz+3t/9jJx87ogbQVEkN66POxWklWQdxy5/GiujTPLUF8XLfOiw+uTHE+z1aTOOJLSg b+QulEMI3lTXAtDqOP5EPqbltbggQNkiBgTrPg4/BdAKhCRzCw9+sSI89W4Rzg6RYO6D CVJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :subject:cc:to:from:date:sender:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=9ygTgi55dKJntHmr4mmKzavaUMqTIPFJ5hmiuUL6E1Y=; b=JpR6onD34LpHoZwltmnKKTP8z7GENnRWD3gzcGIuJqfcw6sp9vq/7T5qXgYaih2GIu xjmeULS1nSmMXY5oPscaEG3v6RcG0I5G8ULbeSyr1DzL5LLSv50gQ3kPZj2uMqK/Ohw/ 7JB0WBwToZiqA0czl2TBoPX1/CGAMrmH4AGBIWbsbvBC3uhM4B+65jI9NgNV+SWgvJOC zPNLm5u9PHjf15ETyIOp5UEC86uLsACjglw6zzCyS15GckS3orlsMaZgotbdfS8lHaHO Jl5m2tl4KARYIuimpLgmCEfjO9UMRNw1Ahwdi3m1HyVY6U9ON4oDVNgTVqt4QJbdcujG pJfg== X-Gm-Message-State: ANoB5plC/27hVVJm9a0Zn5ptxGGROHHB9zJ6vp80PWodEgTyDIZ4hOF7 +0oujEWet8lUd4Csb1z0B6o= X-Google-Smtp-Source: AA0mqf66RQmq7wf5Z9q3+8jGIN2eQO9W3GpOjfSBhmUSIow2Z9nEfHRIXq+rNFOCPB7Sd6XfI5BTfw== X-Received: by 2002:a17:907:3ac5:b0:78d:5d4a:c12f with SMTP id fi5-20020a1709073ac500b0078d5d4ac12fmr17186364ejc.421.1669057678163; Mon, 21 Nov 2022 11:07:58 -0800 (PST) Received: from lightning.caipicrew.dd-dns.de ([2001:a61:3b5d:8501:b5d2:ef50:df61:ae38]) by smtp.gmail.com with ESMTPSA id fy21-20020a170906b7d500b0077d6f628e14sm5226861ejb.83.2022.11.21.11.07.57 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 21 Nov 2022 11:07:57 -0800 (PST) Message-ID: <637bcc8d.170a0220.d85c0.6cb3@mx.google.com> X-Google-Original-Message-ID: <20221121190756.GR9987@caipicrew.dd-dns.de;lightning.caipicrew.dd-dns.de> Sender: Michael Banck Received: from mbanck by lightning.caipicrew.dd-dns.de with local (Exim 4.92) (envelope-from ) id 1oxC96-0006in-N1; Mon, 21 Nov 2022 20:07:56 +0100 Date: Mon, 21 Nov 2022 20:07:56 +0100 From: Michael Banck To: Ninad Shah Cc: Anjul Tyagi , pgsql-admin Subject: Re: Client IP in Patroni References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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

Ninad Shah
PostgreSQL DBA I, Managed Services=

= e: ninad.shah@percona.com

=C2=A0 www.pe= rcona.com

Databa= ses Run Better with Percona.=C2=A0