pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgresql HA Cluster
13+ messages / 8 participants
[nested] [flat]

* Postgresql HA Cluster
@ 2020-06-28 03:40  Brajendra Pratap Singh <bpsinghjobs@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Brajendra Pratap Singh @ 2020-06-28 03:40 UTC (permalink / raw)
  To: pgsql-general

Hi All,

Is there any functionality present in postgresql for High Availability
Cluster where we can setup multiple nodes/instances in READ-WRITE mode for
single database so that incase of one node/instance failure it will
automatically failover the traffic to 2nd node/instance (without
failure or in less time) ,this is just like oracle RAC concept .

Here High Availability Cluster means there will be zero downtime incase of
any one node/instance failure.

Please help us to know this.

Thanks & Regards,
Brajendra

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Postgresql HA Cluster
@ 2020-06-29 07:33  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Brajendra Pratap Singh <bpsinghjobs@gmail.com>
  0 siblings, 2 replies; 13+ messages in thread

From: Laurenz Albe @ 2020-06-29 07:33 UTC (permalink / raw)
  To: Brajendra Pratap Singh <bpsinghjobs@gmail.com>; pgsql-general

On Sun, 2020-06-28 at 09:10 +0530, Brajendra Pratap Singh wrote:
> Is there any functionality present in postgresql for High Availability Cluster where we can setup
> multiple nodes/instances in READ-WRITE mode for single database so that incase of one node/instance
> failure it will automatically failover the traffic to 2nd node/instance (without
> failure or in less time) ,this is just like oracle RAC concept .
> 
> Here High Availability Cluster means there will be zero downtime incase of any one node/instance failure.

There is no such functionality built into PostgreSQL.

An architecture like Oracle RAC is not ideal for high availability, since the
ASM/Tablespace/Segment "file system" is a single point of failure.

You can use Solutions like Patroni or repmgr for high availability.

That would not provode a multi-master solution, though.  There are some
commercial solutions for that, but be warned that it would require non-trivial
changes to your application.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Postgresql HA Cluster
@ 2020-06-29 07:40  Andreas Kretschmer <andreas@a-kretschmer.de>
  parent: Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 1 reply; 13+ messages in thread

From: Andreas Kretschmer @ 2020-06-29 07:40 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org



Am 29.06.20 um 09:33 schrieb Laurenz Albe:
> That would not provode a multi-master solution, though.  There are some
> commercial solutions for that, but be warned that it would require non-trivial
> changes to your application.

not really with BDR3 ;-)


Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Postgresql HA Cluster
@ 2020-06-29 10:36  Andreas Joseph Krogh <andreas@visena.com>
  parent: Andreas Kretschmer <andreas@a-kretschmer.de>
  0 siblings, 0 replies; 13+ messages in thread

From: Andreas Joseph Krogh @ 2020-06-29 10:36 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org


På mandag 29. juni 2020 kl. 09:40:13, skrev Andreas Kretschmer <
andreas@a-kretschmer.de <mailto:andreas@a-kretschmer.de>>: 


 Am 29.06.20 um 09:33 schrieb Laurenz Albe:
 > That would not provode a multi-master solution, though. There are some
 > commercial solutions for that, but be warned that it would require 
non-trivial
 > changes to your application.

 not really with BDR3 ;-) 

Well, BDR, last time I checked, still doesn't support exclusion-constraints, 
so it's not a drop-in replacement. 


--
 Andreas Joseph Krogh 

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Postgresql HA Cluster
@ 2020-06-29 15:12  Brajendra Pratap Singh <bpsinghjobs@gmail.com>
  parent: Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 0 replies; 13+ messages in thread

From: Brajendra Pratap Singh @ 2020-06-29 15:12 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general

Hi Albe,

Here is my one more concern regarding patroni and repmgr tool versions
compatibility with centos8 and postgresql 10/11/12 versions, could u plz
provide ur valuable output.

Thanks
Brajendra

On Mon, 29 Jun, 2020, 1:03 PM Laurenz Albe, <laurenz.albe@cybertec.at>
wrote:

> On Sun, 2020-06-28 at 09:10 +0530, Brajendra Pratap Singh wrote:
> > Is there any functionality present in postgresql for High Availability
> Cluster where we can setup
> > multiple nodes/instances in READ-WRITE mode for single database so that
> incase of one node/instance
> > failure it will automatically failover the traffic to 2nd node/instance
> (without
> > failure or in less time) ,this is just like oracle RAC concept .
> >
> > Here High Availability Cluster means there will be zero downtime incase
> of any one node/instance failure.
>
> There is no such functionality built into PostgreSQL.
>
> An architecture like Oracle RAC is not ideal for high availability, since
> the
> ASM/Tablespace/Segment "file system" is a single point of failure.
>
> You can use Solutions like Patroni or repmgr for high availability.
>
> That would not provode a multi-master solution, though.  There are some
> commercial solutions for that, but be warned that it would require
> non-trivial
> changes to your application.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Postgresql HA cluster
@ 2023-10-11 18:01  Jason Grammenos <jason.grammenos@agilitypr.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Jason Grammenos @ 2023-10-11 18:01 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hello,

I am new to PostgreSQL and having to migrate a Cassandra cluster to PostgreSQL.
Have a reasonable amount of experience with MySQL and use Master -> Master MySQL replication quite successfully.

I am trying to understand what configuration/option to pick for PostgreSQL that will provide the same or similar level of operational easy as the Cassandra cluster. What I mean by that is : Backups, Patching (rebooting nodes to patch)
With Cassandra any single node can be patched and rebooting without incurring downtime. This is also true with MySQL master->master replication, which we have fronted by a HAproxy reverse proxy (we can fail over between the two MySQL node, draining the connections as we do so and then patching and reboot).

I have found the following documentation:
https://www.postgresql.org/docs/current/different-replication-solutions.html

and have heard of "Traktor", "pgEdge" and some other third-party tools.
My difficulty is that with very little knowledge of PostgreSQL I am having a hard time sorting through the various options and determining which are potential candidates for the kind of setup I am looking for (something approximating MySQL master->master replication).

Any advice or resources would be much appreciated.
Regards
Jason Grammenos

[cid:image001.png@01D9FB7D.85F88200]
Jason Grammenos | Operations & Infrastructure Analyst
Pronouns: he/him
P: 613.232.7797 x1131
Toll-free: 866.545.3745 x1131
jason.grammenos@agilitypr.com<mailto:jason.grammenos@agilitypr.com>
agilitypr.com<https://agilitypr.com/;
Learn new PR tips from our free resources<https://www.agilitypr.com/resources/;.

Attachments:

image001.png (image/png, 6.3K) download view image
^ permalink raw reply [nested|flat] 13+ messages in thread

* Re: Postgresql HA cluster
@ 2023-10-12 15:07  Олег Самойлов <splarv@ya.ru>
  parent: Jason Grammenos <jason.grammenos@agilitypr.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Олег Самойлов @ 2023-10-12 15:07 UTC (permalink / raw)
  To: Jason Grammenos <jason.grammenos@agilitypr.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>


------==--bound.758571.6dmalpruuq6nvlco.vla.yp-c.yandex.net
Content-Type: multipart/related;
	boundary="----==--bound.758572.6dmalpruuq6nvlco.vla.yp-c.yandex.net"


------==--bound.758572.6dmalpruuq6nvlco.vla.yp-c.yandex.net
Content-Transfer-Encoding: 8bit
Content-Type: text/html; charset=utf-8

<div>For Postgresql HA cluster the most popular solution is streaming replication. There is an option how implement this. Web programmer approach is using haproxy+consul+patrony. The "old schoool" is using Pacemaker, all in one bottle and well tested. If you interesting how implement in Pacemaker, you may look at my project of testbed that continuously test different HA clusters by random failures. I don't see such for  haproxy+consul+patrony.<br />https://github.com/domclick/tuchanka</div><div><br /></div><div><br /></div><div>11.10.2023, 21:02, "Jason Grammenos" &lt;jason.grammenos@agilitypr.com&gt;:</div><blockquote><div lang="EN-CA" style="word-wrap:break-word">
<div class="e092d429a57b7b749fb23c85464e9ac1WordSection1">
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">Hello,</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm"> </p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">I am new to PostgreSQL and having to migrate a Cassandra cluster to PostgreSQL.</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">Have a reasonable amount of experience with MySQL and use Master -&gt; Master MySQL replication quite successfully.</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm"> </p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">I am trying to understand what configuration/option to pick for PostgreSQL that will provide the same or similar level of operational easy as the Cassandra cluster. What I mean by that is : Backups, Patching (rebooting nodes to patch)</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">With Cassandra any single node can be patched and rebooting without incurring downtime. This is also true with MySQL master-&gt;master replication, which we have fronted by a HAproxy reverse proxy (we can fail over between the two MySQL node,
 draining the connections as we do so and then patching and reboot). </p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm"> </p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">I have found the following documentation:</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm"><a href="https://www.postgresql.org/docs/current/different-replication-solutions.html">https://www.pos...;
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm"> </p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">and have heard of “Traktor”, “pgEdge” and some other third-party tools.
</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">My difficulty is that with very little knowledge of PostgreSQL I am having a hard time sorting through the various options and determining which are potential candidates for the kind of setup I am looking for (something approximating MySQL
 master-&gt;master replication).</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm"> </p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">Any advice or resources would be much appreciated.</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">Regards</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm">Jason Grammenos</p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm"> </p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm"><span style="color:black"><img id="e7219ff6615377f72bc41cfaaa5edf15Picture_x0020_1" border="0" height="63" src="cid:image001.png@01D9FB7D.85F88200" width="204" style="height:0.6562in;width:2.125in" /></span></p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:4pt 0cm 0cm 0cm"><span style="color:#999899">Jason Grammenos | Operations &amp; Infrastructure Analyst</span><b><span style="color:#666666;font-size:8.5pt"> </span></b><span style="color:#999899"> </span><span style="color:#999899;font-size:9pt"><br />
Pronouns: he/him<br />
P: 613.232.7797 x1131<br />
Toll-free: 866.545.3745 x1131<br />
</span><span style="color:#0563c1;font-size:9pt"><a href="mailto:jason.grammenos@agilitypr.com"><span style="color:blue">jason.grammenos@agilitypr.com</span></a><br />
<a href="https://agilitypr.com/"; target="_blank">agilitypr.com</a></span><span style="color:black"></span></p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:4pt 0cm 0cm 0cm"><span style="color:#999899;font-size:9pt">Learn new PR tips from our </span><span style="color:#0563c1;font-size:9pt"><a href="https://www.agilitypr.com/resources/">free
 resources</a></span><span style="color:#999899;font-size:9pt">.</span><span style="color:black"></span></p>
<p class="bd6ff683d8d0a42f228bf8a64b8551e1MsoNormal" style="font-family:'calibri' , sans-serif;font-size:11pt;margin:0cm"> </p>
</div>
</div>
</blockquote>
------==--bound.758572.6dmalpruuq6nvlco.vla.yp-c.yandex.net
Content-Disposition: inline;
	filename="image001.png"
Content-Id: <image001.png@01D9FB7D.85F88200>
Content-Transfer-Encoding: base64
Content-Type: image/png;
	name="image001.png"

iVBORw0KGgoAAAANSUhEUgAAAMwAAAA/CAYAAACsJCdFAAAAAXNSR0ICQMB9xQAAGRtJREFUeNrt
XQmYXUWVThRQEQFZRBBZBRSEQRBxBEVEhyijSCC4Ic4YFeISUAYVQScjMj5lxLnDVNf5q27d97rT
2V5WINIBQUIGB2THBCEsRlkkrGFfEkL7/dVVnZuX916/7k7gNdz6vvt10n3fu3Xrnr/O9p9zR416
jQ0Ah6Y2/Ys19rGuzq7HoHHOq+XekiR5BzTuhOCS3lG9oxvc/2ZKqUMBfNbCngjgawD480P8W53z
d4fCmHDOSdbabwD4IoD9RxXj1T8s7Pgp3VN6M5f1Tps6rVe0XPQqAsxOEDwOwXVNAHNypVx5FoKV
Bub+cP5zFvYFa+wtWumTenv7PgtgUxG5spyVeyF4EoIHDczT1theZ92zqUnLSqldCqkaaJdW+JC1
9qta62MBbNFu8yslpXdqrf9ZKfXeOoA5sVKu9BqY3sldk3uhMaveLgzg0wA+EoVnhADmndB4CIL/
bwKYM+bOmdtrjJnX09OzSZIkmyDBrtCYVM7KL/k1AU4N524D4K/dk7t7oXB4kiQb+bVROAKCG2bN
nNWrtb4+SZK3FqhobNLsao29nwvb1dlFgftFm83vaGvsnw3M6nK5vBzA+MEAxjn3LqPNValNV2dZ
9rQRo6vV6uteRYA5jYIuIjNr/6a1njB1ylRq3fsAbB82jjs7K50EzD/mz1VK7WaNvZdamuZdgYzG
Js0pBArVsgeM4I4kSbZtE4F5A02I6VOn96Y27fU7o2B5XgsOBBgIfjl9Wt/nvaAIViqlDmoI0AR7
ADhopAEGqKNZqWkED/O+lVIf7unp2SgHmEPXOV9wCdcKwI8LZNR/IJtA4/cUOAj8USlXXrKwX2gT
7fIWaFxPoBAQYZ5/A7BVq4CRDslmTJ/h/04/hz8BfKLe9ZRS78tctlhEFrwaAOOU2020PMr1cc4d
kiTJxk0Bo7GwOqPaW6vFixGFTdsjU5u+wN03AoaLacQsaJs5wn7Tpe4FPnQKvIicPRiTjIKR2nR5
Z7kz3ttvCMQ6gneYMeZ+Dy4xl74qNIzCiX5NBA8nSHam09/EJNsntenTmcuowXcv0FFnGBgXd2+a
ZASMSx1t3ifaadEo9ADOBHBc0pNsNFinXyVqP2fd6Yyo5bVTznTZXbTcQ5OU3yUiPSMJMLNnzabT
PzX3u0211senabqiq7Nrtdb6tPB9W0fAWG0/5gMqpdKWFvYTWZrdWilXHtVKH18go76tvgPDkARI
0C4r81rGwv5khPhgAwKmBU37ZWed3zRGImD8pmfMQ6JlQdCg16U2fcal7nat9Zdy524DwdJg2l5H
E8yl7kZo3J9lWZIkyT8UyGgkJMp+k/F4H5PXuBYaHTR5gh/Dn3+sorrFawUwUcsGwPxmJAGGeSgA
9zFSJiKphT3LwX22NjwcAHOHN8k0zhSRcxkVS236UJIkOxaoaPwgNtYdeiGFIwjZaVDYL7XpixSa
6CAD+FQ7zHfixIkbM2JWHbVuOLgVwDD3wgAHv6fOWmxktf1qDWAu5RrFY8yYMZvUBkv8MSbZZBi3
NXrcuHGvX18mmYhMbSGIsk2/DwPs54MiWubMrM5k/mVmgYwGQ2v90dSmz3lBE/M4E4LMTRgxV4XQ
ci+dZAiqwwDlG6y2xxsx5xoxRkQ6rbUd3P0AjKmWqm9aR7BHecHeEcCWuYd8dKVcmU9zI0uz2QD+
aZBO/54GZpqF7clcdhGACeH3h/nvE/RQm/romSD6cit4PR4MtVbKlUuh+iJr9IMyl10MjYvDz+8N
Ifp3VKVcmVUul+dC42cE34Zw+psCJkTJlFJ7WWsfDMnMIjpWd+E0OihcXDjRcmU/dULjhzTR+kOw
Yh5WSr1nCAIxluCjf8Qw5ZzZc/zBnTCEeFdC4xZoWBH5D/K/jJh5ECyplCsPGTGdue86hzsggczP
QuP8QQEG+CzviQIRcgw3hd9P4P/5mUAV6T8MzEvRXOXhk3kaE8P1fsR5cD78vTV2BcO3g1ibN4qW
q6rTq35tRGRRtVp9/SsFmLCBfpcJzjRNGSF7d4GQtRdtWwjuISAqWYXO/b/lQovvFS3PxzBzoFX8
oNXvLpVKb4JGYo1d6QHZp6VuFpFfkarhAaLlAQovhTwKJB8gz/ecptQtA/DT3Hx/TOGMeRjpkHMH
CZhPQ7CK9xSAcV3QZG+Dxjhr7TF+fkG7hND11TRHLeynSMnhTyTYLnzf9nSSY7AkXPPUQaz/AQbm
Rc4nmL1f21Bh5VYBQ0oNN87AGJhP66BASs7BjcJjYOjs7Rr/RrOMZkjIiPc5/xrXM0zZyndLh5wd
d+zUpqsYCmbYcq0Qbx8F43KfE4Hx54rINdbaLzvl3lsqlbaqecjrFzAa19YKH02kCJjgw8xvGo4X
o0N+I67RQvokLQYpfsx5hADLciR45zABczq5ZADmtmAmbysiy4L59eHa0D3zXdQ0AE4pkBJ8BOmQ
CymAXLTgKI6ueaDjY+Y/OsJa6yMH3L0U9jEwT1IYgm+UNnlw77DG/oVCEwBzQyPS58sBGOYsagDT
NKxsrT0yNanXEsHneb4Z3SYfbBEtV0eN2myNBgGYU2dWZ64WkektXJ95mNs6K52rSLit49v+onty
9yoC2cHtXZhjCgdZY5+ioHqTAvhKnQewIwT3RrueD1d36EoLO+cZFAIKkYF5jmzYAfyoSVHQCQj6
MiMFMPQ5IPi/uLGEqNN5LZhEB1tj/abirHvRavuZ9QCY7a21R7fia9JXVUodEoIub67nX1ltPwrg
CJqsr3nAkFZC4fN2upZ7kiR5e4PzpsdoWTDd7qVWaCoMggu9T+I80Hj+1gOA9wBrfM2GB4wRs6ie
7dyOgAnXnbAWYASL67EIaubyM87RX0PLrdVqdfPhAmZDWyQhgjf6NQcWmjyi5Xbv7PcJXqmJn/Mx
a+xLMdQaaP/fbXQ+8wl8qP2JUMHdSU/zHAWA7SBYRk3nzTIttydYF8DtCpgkSXam6ZJP9jI4MJA5
xvN4aK3PaeEaryhgrLZfz1y2wFr78dciYMZSICg43hzT6NBas2jspJqDJa6nQfBU5JeFHZEaYOMG
JsrrIPhdzpl9kM59s/l0JV1bM/8RfR6GlBMk240UwMTwfHdX9xoto1Fpsv5HMDrGtTcwq5RSH1yf
gEmSZAdrLSN636OpDWDYNBcRKQf28oT1prX6zMIPaa2Paedo3GjRMiMKXgyfxt2u9qBgRbDkknmr
WbHYcHG1mK5KV/RhVjbbbaPjT6o+BSiYNNdVq3Xt6rYFjFPuEDr8cRMSLQ8D9aNeRsz/8D6Dtv49
Q/DrCzBkJWcuY1+D5wzMnwzMEyEgMdMpt8swAKP9fJuEvgca3mdKsEPNc38s0HmOaFft8m4L+0g/
0VKDkZAXAuGy0bEqD5ogjNJEfR/PWhrvxFe8/zO72ZyY3wjg6vNhtJlRz1ZuZ8CEMPzV8fp+o4H9
zjr3UMJWTNQSMCEM3VLephXAMILJ52qN/SMUDqbvmCDZ3Rgzx5cua3NVvWYYLwdgrLb/YmBWAGsC
QAwsADjfwMzPpzTaCzAaP+KNB8A8CeBzWuuPEeH1DvowpKDQka0hZC6l79EAlFtCcGs+v0LzoN65
pVLpLUbMFSFf4w+r7bEjJUpWs7Ynx7xVCJRcRqGoERzWHa0O13+EZdPrCzAMtgS6/idrPvtWA3ML
BI8MtYJ2uICBoK/hhhpBHWnI2eLuRkEIBVTzWt4hYL8XAdAPGuCEZjuKS91LBFkwCV6inQ/gQACb
s3CLtruFnRzPYT5Id+gpjeghbQ8YYHsDsyyasRZ2tVLqwBpz7Fe8Bw+sDsxv9bsHAkwIby/2zAzg
sDpz2wEK72kw78211h/nRsVWVfWCNLWAYf+HrnLX2ATragYkeL/W+ijP9gC200p/CcASb6VonNNV
6ToxNjEJoe0jajeWaA1BYSwpTc38MGp35r788+1jbHyGkdd6Vgp9Y24orKdSifrAQGrxM9bY1VzU
IPD/OogHtquBeTRSZQLV5XcD7Lg/YOWeFx5jo3/yBH0Uo821ouVZ/i4eRsxFSLBtE4Fsa8D0+29h
jp6fl6sKRQmbSYcs9bmXvtzXCesLMOH+ZpGXZozpJsWlxXzcGBEh4fRxaDzLIIQ19ookSfaqCxgd
AKNxZs/FPb156lLONF3A9aflkiTJHiJyEUmswQVY6KybQ6EtlUpvZIkBBC8C2DcPfufc913qHvJ5
PMHzLnVPu9SpPLAYNOAzTW16GXl8YX3uC0GqZwFUSQHK+VC0lG43MMuZ7nCpW5HalOfsWN/G7pAs
RzB8IEmSlispQ7h4dmg+wWQbfz7nlHvfAP7JsdbY52MEjNcnUZFHzEOkNr0FwMR6rOWRBhju0IyA
9X9ey23cwaNwUiCD4N05mIRgi4A5vFKuPOmJoLA9jfoV5M4/IXMZfdRrmEilQ25hJ2YuYxn4Uge3
byPAkHh64QUX9tY2S/RyJnJR0EbH9QeaRBZxTVSiDsndE6Ojt3kfGnh/TgvrkPM7DwDLqfegJUKC
rGiZGelZgQN4p+cewp7h/UNgU0ZluZmTqAvgv0IklgGG+6yxy0m74nkW9ockAgOYUm9xfNcQmj8+
m66lPATH7Us0s2IAIHCoftno/FKp9GZrbBq0B0mMZxkx3Qbmt6TZQ+Pn/M5mWuWVBozW+uJBmb3V
6usNzMKwEfBBroLGUcGO/1/Oa6CgyXCiZFrpr6YmfYzCkqYpn/PlDm5crcbhmgP4s7NueW0iWms9
kSFkrfS8aB7XAcwZF8y7gM/17DqAuTBYMGNzgLvCE2qVqwXMrdx4SUQNMvbx4CPPrqO9r+C8LOxx
QWO8y8C8IFrujptSTnMeHDb3G8O5H2Y0TivdXZvAh6rDl4t0/RiNGkpBmDcptNwVqTLh5x31dkom
RyGY62n0grtbdW7bDjDQvx1CYKXf+Q/zoO+2uWhZHIIBLw42ATjIPMy+LH1IbbqCAhY2tp58bwYS
bz2zWcHV+fzWBubPmcuYPth7PQCGptNCv9HmiJ41gNk/rN0syoy166YirLafn9zpeXeXR8BwLUVk
MYNHNSHsPX2eS8ytPjcIX4b/UKVceQRqANnPZ5ZDpOuOoXa1JCsgUmX62x2tUb1rHFCNDiKaESEo
fG49hcRfDsB8Pg8YAFcOdp5euAMHL/h8y6AxgSH6YObdMNhE3VAy/Z4Nbu0PXeqeYN2OdMg1kTHO
tQvM5tMb+GKL/Gan8IWXCTD7hu6bS1jtW68WBwl28v6O4JHQR3oX0UIm/JJaDUM+XQDMkmjCsY6p
s9y5OhCC51DrNHLsDjcwdJxiuPOXQxVa+ix8AP3Of1/h2UU1gvmFHM2FictPjBTA+AiLYHW/hunQ
twxlrqbD6HxUkYnZ/nkBZw4JhEOkxrAZORnh4drjg/DYWdVZDfNAtA5CsVxsLduxgQGzN8s5oHEv
nxGAfercx1YMAEDwDLUl16QVwOTJpfp8fWSWZfNorgUZsWRG1JoI55G2EagYT7dCxWiy+KwSXBS1
TH8+R/XVhYcFmxf/7k1AMUsc3EksBUaCfbl7hN5Y2/ucTZ2Q4isFGNrS+Yy9tfbxaF8Pap0UDorA
i+sU1oomwT4bAjB8BjRFGpiJ3/FOsO5zcLmWwWn/eQPAXDZjmq9qHddMw1jYnw4SMIc20TAbs8jQ
+x7AwfU0pmhZDcGj1Wr1Ta1qmDpsbM7nMAA3zJ45m9ea1u/jkYUsIn+lKRa0wSK2CR3OTs+Wsv2d
I0OSjvX5ccHINo42fBSW2ILWC4zgAQjugsaffIsfwZVsCZTadDKAX9CpI5heIcBsBo2/RPZxUN+X
D8TQriPgG4nIZXHjyBFXLx5KE/SBAOMjRn0d+P9aj3luYceGqNGUGMChycXy8VpeYPBhKDNPA9iz
EWAC4CbVydnMDQGTY2ujZPlMfx4wMdrK+QVgn1WPERLKJxaGc3dvySQreUbBFqz74XPJnbNL2ZUZ
hn4iUSGMzi4oFG5ONgjJqcM1jQIIH4v0mhA+vTnSLviukUj9iDsshTju2t5cc31s5phEpYDT5/GZ
amMZiVshIr9GaU0cfSDAQOMrwwVMOG98jm0dyxSWiYiysJNYqNUKxYQM32iW5brvfGuIa948cYnq
5tyE6MgbMVNrmmmMponlo0Rafy0ksd9sxPwhmF1fqVnH032UTGvTMHGpMcHvzhoX1GoYfm+thjFi
LgkgOqZZlIxRNJ/wTrMH8q/b8J06Bb9jFSgDM+F3ewwEGBJ5A8CPm9o9dXUe4CFndLM38RT2ZFRr
U5YV0+HzBDfBU8qp3Uath0ECJ7+XvQCYxAwEuhNjkEFr/W1ymij4Eaw8uOg8Nx7Rloy1OZEQypv1
TenEXN6VdL0txOfP5jUphOH9L6qWXcBdk3/nLiV67Z5ifIC8Bq/ny28FSxpkzV9HwTIwD3KOsSqV
QjRv7jx+7pmBGNhhHVi2cFd/olKwvFHdUYuBhIcZnm9kkjnrPulS94jfoQU9Sqnx7P5ijPmNB0uH
npUvMYfCR8pp+W+hd8G5PtsPOynM9w95gRURwzWw1n49RqH4pgeuIzsBcTMil42J27C2K/LJSAA/
IZgt7GXOub29RuzrjXa7bwqPNYwIrfQkv9EYc5cPlgDHpTbt8aaaxvlRIzrl9vLaG7htnbAysHew
Dm7zUTKFAyvlyhOhQPE/yTBIJT3bVxtrmeE1T1LyiZ1ZLnXXZGl2NTR+ur5e70BadqVcWQTBTbQF
0zS9kSXNNQ+ZN7TY79SAMCLDOdCnMjDn+99odBkxM0Ibo2W5sgAPHr/4uq+C0Wp7jEsdTbgbsyy7
HhrfqPUbMpcxCXdTpVy5gTtlzSIeRJPUGHNTlmbXGzGlpn4I/a2+Rh6XAriW/QcoHAwJt2pWQTA5
alDe7zB8x51Ey1N8d0szp59UHPopURDpX6YmvZlJulqhCufvb2AuIYuDCcTUpOzUY5LSOrmZJGiY
8fnMOWtkDMwzwex8hh1UU5POZSVnLW3Iwk5Pbfok+wWwmyq1HOWHzVbY/L3mWTJwdLOB4T2vTG16
l7Pu5Py9s0NPeGnUTbUaPwCaAYIbo4+slDrCpY7hdX5mJTdzyh4S7DxqLVVWwhb1Fmu4o1QqbRa6
j2zlmbE5W9j3OzPpzQRAiwVSG3Pi0PgigVND9Fwc+5NNSia9ldeqbZCRm9OWJZS2mTRp0tb1hJoc
tlKptA0/3+rmMTGZ+AYSGGkLD3KJWEqxIJBdXxqoDHkAwGxKFjiToK2ANTyX/RltKpVKWwyUbA2v
7zuQvaUbPJ93qER9sLZ6NoSC362U+kC43k7N2CI0lZRSB8dkteeLAQfUK3HwfgewD/9ObV1XZoB9
2E+t9lnSiQ9BkL1G9a7hlBFwCmpPbp5t00aKdipLCHzfMWP0ELTXfhb2gf5+A32vtdh51AgbZOay
mTu1Cyky7fKunWK0k5AAB1DYvb0L+wAdsyH5SCI9Od5bw1KCdh40OyIzuTZAUYxi+OZ9LBmInTQZ
LiYjdbDfQ3Wqtb6W3xH4QLOHGwp/uUcou14aonpP0VcoJKQYa0dpnI9arIhZfmj8cSi+E6Nd7JgZ
TTJr6xeUtbWmDdy9UONzwbhq82bjxXhtmmMHemJh6PrvUreSzTQGKWgnpDZ9lGAJYeWZjRputOvw
kSpgOTVkatOVUGs3TS9GMWLU4u0kdoZEpqeXswkGo2TNoichUPBRxvDZ/Lv/1RsCJkN3bWNg7Ka1
/n6lXPm+rwpUOJyhbgOzNKddKoVkFKPRGG2tPYuCElkAvroz87UhdzMnwVfGMZbvQ6TAt42Y/yZv
yYh5jvZ+dJLTNL1grfh4+/lrb+TLlpgoZa6IWpX5BM49gsXC3t6sX3IxitHXCUTjPNL6Y/Y+UlH8
G3xT19/sIhazMWsfu/inNl3KRoFDCRa8zPfJep+7Q6a4n50Q78ka+wcWMRUSUYzWBErhaGPMfGvs
36hpSM3gTpz/GfoCrDIwd5CU6Kz7VqlU2n4EOfY/J8jj/YQN4nGr7ZSiH3Exhmrn70VKCwmLFvYH
fMsWeUWkm5MYSgpFnmA5AjeGsWVXPstZ9+98LTqADxZPvRjFKEYxilGMYhSjGMUoRjGKUYxiFKMY
xShGMYpRjGIUoxjFKEYxilGMYhSjGMUoRjsNdjdnjQnfreHbGcF0sbukc+4DrXyeNSqVrDKVn+Vh
je2WDvl1K8TC0DH9lLIrd8fPZy6b2qj5de1gdxfpEJ2fO//N1yEUT7YYG2SQCEg2cOwo6an0U6a1
/OYrdjufPWt2/xvCyMoVLY816t+71uj17UHns/ld/HzoyrioxblvyzeU5efumwVq/Kh4ssXYUIA5
iG+ujcVc/gWtzjfVa+n1Exb25PjGMR6+Q6KWe6i5WgTMzHyPZVZRGjELWpz7NmyNmp+7/7fGacWT
LUYBmAIwxWgHk4w18vF9khR+vsJAa/3lFgFzytzZc3vja+bC28Qeb9UkA3Ax+/zGz9O8Y7f4Vk0y
ti7Nzz00zi5MsmJsMMDsyZ5ezrkF3NmNMQvKrryg3mup6w0Hd9SU7il9nxWzgH10WZe/zgtoGgPm
rM7Ozv7Pd3d189rntHLtarXKV9zNyM+d/+arsYsnW4wNMf4OMdJZ9Ys1I6gAAAAASUVORK5CYII=
------==--bound.758572.6dmalpruuq6nvlco.vla.yp-c.yandex.net--

------==--bound.758571.6dmalpruuq6nvlco.vla.yp-c.yandex.net--





Attachments:

image001.png (image/png, 6.3K) download view image
^ permalink raw reply [nested|flat] 13+ messages in thread

* RE: Postgresql HA cluster
@ 2023-10-13 12:02  Jason Grammenos <jason.grammenos@agilitypr.com>
  parent: Олег Самойлов <splarv@ya.ru>
  0 siblings, 1 reply; 13+ messages in thread

From: Jason Grammenos @ 2023-10-13 12:02 UTC (permalink / raw)
  To: Олег Самойлов <splarv@ya.ru>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Thank you for the feedback,

I have used pacemaker for other purposes previously so am a little familiar with it. It appears that in this scenario pacemaker is being used to manage a floating ip as well as deal with split brain scenarios. What isn’t clear is how effective master-> master replication is being accomplished. Postgresql streaming replication to the best of my limited knowledge only replicates in one direction, from the active to the standby servers. The issue this presents to me is that once you failover from the active to the standby (or one of the standby’s depending on how many you have) none of the data written on the standby is replicated back to the formerly active server.

Let us say that I have only 2 postgresql servers (absolute minimum number) and I want to patch server A. Ideally, I would use a load balancer (or other failover mechanism like pacemaker) and repoint the floating ip to server B. Now traffic would “drain” down off server A, and slowly (or rapidly) move to B. During the move some clients would still be writing to A and some clients would be writing to B. Once they have all moved to B, server A would then be patched. Then the load balancer would be used to repoint the floating ip again back to A, and the process would repeat, with traffic moving back to A. Just like in the first half of the failover some traffic would exist on both hosts as the failover progresses. Once completed all the traffic would be back on A.

In the above scenario, I do not understand how streaming replication would handle the part of the scenario when there are clients writing to A and B. It would seem that something like `pgpool-ii` or `pgEdge` would be required, but with my limited knowledge it is unclear if or which would be appropriate.

Regards
Jason


[cid:image001.png@01D9FDA9.6A6562F0]
Jason Grammenos | Operations & Infrastructure Analyst
Pronouns: he/him
P: 613.232.7797 x1131
Toll-free: 866.545.3745 x1131
jason.grammenos@agilitypr.com<mailto:jason.grammenos@agilitypr.com>
agilitypr.com<https://agilitypr.com/;
Learn new PR tips from our free resources<https://www.agilitypr.com/resources/;.

From: Олег Самойлов <splarv@ya.ru>
Sent: Thursday, October 12, 2023 11:08 AM
To: Jason Grammenos <jason.grammenos@agilitypr.com>; pgsql-general@lists.postgresql.org
Subject: Re: Postgresql HA cluster

For Postgresql HA cluster the most popular solution is streaming replication. There is an option how implement this. Web programmer approach is using haproxy+consul+patrony. The "old schoool" is using Pacemaker, all in one bottle and well tested. If you interesting how implement in Pacemaker, you may look at my project of testbed that continuously test different HA clusters by random failures. I don't see such for  haproxy+consul+patrony.
https://github.com/domclick/tuchanka


11.10.2023, 21:02, "Jason Grammenos" <jason.grammenos@agilitypr.com<mailto:jason.grammenos@agilitypr.com>>:

Hello,



I am new to PostgreSQL and having to migrate a Cassandra cluster to PostgreSQL.

Have a reasonable amount of experience with MySQL and use Master -> Master MySQL replication quite successfully.



I am trying to understand what configuration/option to pick for PostgreSQL that will provide the same or similar level of operational easy as the Cassandra cluster. What I mean by that is : Backups, Patching (rebooting nodes to patch)

With Cassandra any single node can be patched and rebooting without incurring downtime. This is also true with MySQL master->master replication, which we have fronted by a HAproxy reverse proxy (we can fail over between the two MySQL node, draining the connections as we do so and then patching and reboot).



I have found the following documentation:

https://www.postgresql.org/docs/current/different-replication-solutions.html



and have heard of “Traktor”, “pgEdge” and some other third-party tools.

My difficulty is that with very little knowledge of PostgreSQL I am having a hard time sorting through the various options and determining which are potential candidates for the kind of setup I am looking for (something approximating MySQL master->master replication).



Any advice or resources would be much appreciated.

Regards

Jason Grammenos



[cid:image001.png@01D9FDA9.6A6562F0]

Jason Grammenos | Operations & Infrastructure Analyst
Pronouns: he/him
P: 613.232.7797 x1131
Toll-free: 866.545.3745 x1131
jason.grammenos@agilitypr.com<mailto:jason.grammenos@agilitypr.com>
agilitypr.com<https://agilitypr.com/;

Learn new PR tips from our free resources<https://www.agilitypr.com/resources/;.




Attachments:

image001.png (image/png, 6.3K) download view image
^ permalink raw reply [nested|flat] 13+ messages in thread

* Re: Postgresql HA cluster
@ 2023-10-13 13:10  Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  parent: Jason Grammenos <jason.grammenos@agilitypr.com>
  0 siblings, 2 replies; 13+ messages in thread

From: Jehan-Guillaume de Rorthais @ 2023-10-13 13:10 UTC (permalink / raw)
  To: Jason Grammenos <jason.grammenos@agilitypr.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Fri, 13 Oct 2023 12:02:53 +0000
Jason Grammenos <jason.grammenos@agilitypr.com> wrote:

> Thank you for the feedback,
> 
> I have used pacemaker for other purposes previously so am a little familiar
> with it. 

So you might be familiar with shared-storage cluster, that are the simpler one
you could deploy (baring you have a reliable HA storage available). But it's
not a multi-primary cluster.

> It appears that in this scenario pacemaker is being used to manage a
> floating ip as well as deal with split brain scenarios.

There's also two different resource agents dealing with PostgreSQL itself:
pgsql and PAF. Both handle multi-status differently from the administration
point of view.

> What isn’t clear is how effective master-> master replication is being accomplished.

There's no master-master in PostgreSQL core. There's few external solutions out
there though, but double check you real needs, the real primary-standby
capacity to answer you needs, and the various constraints M-M imply before
rushing there.

> Postgresql streaming replication to the best of my limited knowledge only
> replicates in one direction, from the active to the standby servers. The
> issue this presents to me is that once you failover from the active to the
> standby (or one of the standby’s depending on how many you have) none of the
> data written on the standby is replicated back to the formerly active server.

It depend if this is a "controlled failover" (aka. "switchover") or a real
failover triggered by some failure. If this is a controlled failover, you can
hook back your old primary as a standby with no trouble. PAF even handle this
for you.

Moreover, even with a failure scenario, there's some solutions around to quickly
fix your old primary data and get it back in production quickly as a standby
(pg_rewind, PITR/pgbackrest, etc).

You just have to plan for failure and write you procedures accordingly to get
the cluster back on feet quickly after a failover.

> Let us say that I have only 2 postgresql servers (absolute minimum number)
> and I want to patch server A. Ideally, I would use a load balancer (or other
> failover mechanism like pacemaker) and repoint the floating ip to server B.
> Now traffic would “drain” down off server A, and slowly (or rapidly) move to
> B. During the move some clients would still be writing to A and some clients
> would be writing to B.

This doesn't exist as PostgreSQL has no multi-primary solution in core. You can
do rolling upgrade, but you'll have to pause the production during the
switchover between the primary and the standby.

> In the above scenario, I do not understand how streaming replication would
> handle the part of the scenario when there are clients writing to A and B.

It will not.

> It would seem that something like `pgpool-ii` or `pgEdge` would be required, but
> with my limited knowledge it is unclear if or which would be appropriate.

External multi-primary solution exists, pgpool-II, Bucardo, BDR, etc. But
you'll have to ask and evaluate these thrid party solutions yourself. 

But really, double check first why a simple primary-standby architecture doesn't
meet your needs. The simpler the architecture is, the better. Even from the
application point of view.

Regards,





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Postgresql HA cluster
@ 2023-10-13 19:21  Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch>
  parent: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Laura Smith @ 2023-10-13 19:21 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>


------- Original Message -------
On Friday, October 13th, 2023 at 14:10, Jehan-Guillaume de Rorthais <jgdr@dalibo.com> wrote:

> But really, double check first why a simple primary-standby architecture doesn't
> meet your needs. The simpler the architecture is, the better. Even from the
> application point of view.
> 




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Postgresql HA cluster
@ 2023-10-16 13:20  Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  parent: Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch>
  0 siblings, 0 replies; 13+ messages in thread

From: Jehan-Guillaume de Rorthais @ 2023-10-16 13:20 UTC (permalink / raw)
  To: Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Fri, 13 Oct 2023 19:21:46 +0000
Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> wrote:

> ------- Original Message -------
> On Friday, October 13th, 2023 at 14:10, Jehan-Guillaume de Rorthais
> <jgdr@dalibo.com> wrote:
> 
> > But really, double check first why a simple primary-standby architecture
> > doesn't meet your needs. The simpler the architecture is, the better. Even
> > from the application point of view.
> >   
> 
> 
> From my perspective I do wonder why Postgres doesn't have an equivalent to
> MySQL Group Replication.
> 
> Although you can run MySQL GR as multi-primary, most people run it as
> primary-standby.
> 
> However the difference with Postgres is that MySQL Group does leader
> election. Whilst Postgres failover/failback is a highly manual affair.

PostgreSQL core only cares about primary-standby replication.

Auto-failover must involved various components way outside of the scope of
PostgreSQL itself: the system, the network, sometime the storage, a quorum
mechanism, sometime some fencing, etc.

There's various auto-failover, non manual, solutions in PostgreSQL ecosystems,
they just all live outside of the core.

Regards,





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* RE: Postgresql HA cluster
@ 2023-10-17 19:59  Jason Grammenos <jason.grammenos@agilitypr.com>
  parent: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Jason Grammenos @ 2023-10-17 19:59 UTC (permalink / raw)
  To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hello Jehan-Guillaume de Rorthais

Perhaps you could help me understand a few things better about how primary-standby works in postgresql with streaming replication.

If you have 2 PostgreSQL nodes hooked up to a Load balancer (haproxy), and you move take node1 out of load balancing, you now have connections on node1 and connections on node2, as the Load balancer drains the connections off node1 and over to node2. How does PostgreSQL handle this scenario when there are writes happening on both nodes?

If instead you have 2 PostgreSQL nodes behind pacemaker (controlling a floating ip), what happens when you initiate a failover and move the floating ip? You want the connections to drain off node1 and move to node2. Again in this scenario, both nodes would be sustaining writes at some point in time. How does the write only replica get changed out of write mode during this failover? How does the primary node get switched to read only after the connection drain is complete?

Overall, I am trying to understand what it looks like operationally to run a 2 node postgresql "cluster" and how patching both nodes would work (and mentally comparing it to what I currently do with mysql).

You recommended that primary-standby could be sufficient and is much simpler, so I am simply trying to wrap my head around what exactly running it would look like. If primary standby is simple enough to failover, patch , reboot, maintain, etc. Then you could be correct that master->master may not be needed.

Thank You
Jason

Jason Grammenos | Operations & Infrastructure Analyst  
Pronouns: he/him
P: 613.232.7797 x1131
Toll-free: 866.545.3745 x1131
jason.grammenos@agilitypr.com
agilitypr.com
Learn new PR tips from our free resources.

-----Original Message-----
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com> 
Sent: Friday, October 13, 2023 9:10 AM
To: Jason Grammenos <jason.grammenos@agilitypr.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Postgresql HA cluster

On Fri, 13 Oct 2023 12:02:53 +0000
Jason Grammenos <jason.grammenos@agilitypr.com> wrote:

> Thank you for the feedback,
> 
> I have used pacemaker for other purposes previously so am a little 
> familiar with it.

So you might be familiar with shared-storage cluster, that are the simpler one you could deploy (baring you have a reliable HA storage available). But it's not a multi-primary cluster.

> It appears that in this scenario pacemaker is being used to manage a 
> floating ip as well as deal with split brain scenarios.

There's also two different resource agents dealing with PostgreSQL itself:
pgsql and PAF. Both handle multi-status differently from the administration point of view.

> What isn’t clear is how effective master-> master replication is being accomplished.

There's no master-master in PostgreSQL core. There's few external solutions out there though, but double check you real needs, the real primary-standby capacity to answer you needs, and the various constraints M-M imply before rushing there.

> Postgresql streaming replication to the best of my limited knowledge 
> only replicates in one direction, from the active to the standby 
> servers. The issue this presents to me is that once you failover from 
> the active to the standby (or one of the standby’s depending on how 
> many you have) none of the data written on the standby is replicated back to the formerly active server.

It depend if this is a "controlled failover" (aka. "switchover") or a real failover triggered by some failure. If this is a controlled failover, you can hook back your old primary as a standby with no trouble. PAF even handle this for you.

Moreover, even with a failure scenario, there's some solutions around to quickly fix your old primary data and get it back in production quickly as a standby (pg_rewind, PITR/pgbackrest, etc).

You just have to plan for failure and write you procedures accordingly to get the cluster back on feet quickly after a failover.

> Let us say that I have only 2 postgresql servers (absolute minimum 
> number) and I want to patch server A. Ideally, I would use a load 
> balancer (or other failover mechanism like pacemaker) and repoint the floating ip to server B.
> Now traffic would “drain” down off server A, and slowly (or rapidly) 
> move to B. During the move some clients would still be writing to A 
> and some clients would be writing to B.

This doesn't exist as PostgreSQL has no multi-primary solution in core. You can do rolling upgrade, but you'll have to pause the production during the switchover between the primary and the standby.

> In the above scenario, I do not understand how streaming replication 
> would handle the part of the scenario when there are clients writing to A and B.

It will not.

> It would seem that something like `pgpool-ii` or `pgEdge` would be 
> required, but with my limited knowledge it is unclear if or which would be appropriate.

External multi-primary solution exists, pgpool-II, Bucardo, BDR, etc. But you'll have to ask and evaluate these thrid party solutions yourself. 

But really, double check first why a simple primary-standby architecture doesn't meet your needs. The simpler the architecture is, the better. Even from the application point of view.

Regards,


^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Postgresql HA cluster
@ 2023-10-18 07:44  Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  parent: Jason Grammenos <jason.grammenos@agilitypr.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Jehan-Guillaume de Rorthais @ 2023-10-18 07:44 UTC (permalink / raw)
  To: Jason Grammenos <jason.grammenos@agilitypr.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi Jason,

On Tue, 17 Oct 2023 19:59:00 +0000
Jason Grammenos <jason.grammenos@agilitypr.com> wrote:

[...]
> If you have 2 PostgreSQL nodes hooked up to a Load balancer (haproxy), and
> you move take node1 out of load balancing, you now have connections on node1
> and connections on node2, as the Load balancer drains the connections off
> node1 and over to node2. How does PostgreSQL handle this scenario when there
> are writes happening on both nodes?

As you write about "writes happening on both nodes", you describe a
Multi-primary architecture that PostgreSQL does not support.

A standby is in "recovery mode", receiving and applying WALs directly from
the primary (or archives).

A standby will only accept read-only (RO) queries (providing you set
"hot_standby=on") as it can not produce WAL itself. Any write attempt raises
an ERROR to the client. This is a really strict state, not an advisory one. You
just can't write on a standby, even as a superuser.

So, in the situation you describe, as long as the standby is not promoted to
primary, any write (RW) query on it will just fails hard.

Obviously, you should definitely NOT promote a standby while another primary is
still alive in the cluster. You would end up with a bad split brain scenario.

About haproxy, you should use tcp-check (eg. on a Systemd socket) or http-check
(eg. on Patroni API) to decide on which server the primary is hosted. It
avoids this transition period where both nodes have RW connections.

> If instead you have 2 PostgreSQL nodes behind pacemaker (controlling a
> floating ip), what happens when you initiate a failover and move the floating
> ip? You want the connections to drain off node1 and move to node2. Again in
> this scenario, both nodes would be sustaining writes at some point in time.

No, both nodes would not be sustaining writes in the same time. Two primaries
can not leave in the same cluster in the same time, this would be a split brain
scenario. If this happen, you have to pick one and rewind or restore it from
PITR, losing the transactions it wrote in the meantime. Of course, you can also
compare both nodes and tries to recover missing transaction before restoring
one of them, but it takes a lot of time...That's why clusters (not just for
PostgreSQL) tries hard to avoid split brain scenario using fencing, quorum,
watchdog, etc.

Before moving the IP address, you have to demote your primary as standby.
Unfortunately, this can not be achieved without restarting the primary
PostgreSQL as a standby one on its node. But because demote == restart as
standby, you effectively end all clients sessions. At this point, all your
nodes will be in standby mode, without primary to feed them.

So, during a switchover Pacemaker will:

* demote your current primary (ending all sessions)
* promote the other standby as primary
* move the IP address to the new primary (colocation rule in Pacemaker between
  the IP and the promoted role).

As soon as a primary appears in the cluster and the IP address (or haproxy)
points on it, all other standbies nodes will start replicating from it.

> How does the write only replica get changed out of write mode during this
> failover?

"write only"? Do you speak about the primary in RW mode or about a standby in
RO mode? 

For a primary: a PostgreSQL restart.
For a standby: promotion happen live, all existing sessions are kept and can
then issue write queries.

> How does the primary node get switched to read only after the
> connection drain is complete?

A PostgreSQL restart.

> Overall, I am trying to understand what it looks like operationally to run a
> 2 node postgresql "cluster" and how patching both nodes would work (and
> mentally comparing it to what I currently do with mysql).
> 
> You recommended that primary-standby could be sufficient and is much simpler,
> so I am simply trying to wrap my head around what exactly running it would
> look like. If primary standby is simple enough to failover, patch , reboot,
> maintain, etc. Then you could be correct that master->master may not be
> needed.

And if you have a strong, safe and high available storage, a shared storage
cluster for the primary is even simpler IMHO (because you move all the
complexity off your hands to the storage guys ones).

For details, I recommend reading this chapter in documentation:
https://www.postgresql.org/docs/current/high-availability.html

Regards,





^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2023-10-18 07:44 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-28 03:40 Postgresql HA Cluster Brajendra Pratap Singh <bpsinghjobs@gmail.com>
2020-06-29 07:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-06-29 07:40   ` Andreas Kretschmer <andreas@a-kretschmer.de>
2020-06-29 10:36     ` Andreas Joseph Krogh <andreas@visena.com>
2020-06-29 15:12   ` Brajendra Pratap Singh <bpsinghjobs@gmail.com>
2023-10-11 18:01 Postgresql HA cluster Jason Grammenos <jason.grammenos@agilitypr.com>
2023-10-12 15:07 ` Re: Postgresql HA cluster Олег Самойлов <splarv@ya.ru>
2023-10-13 12:02   ` RE: Postgresql HA cluster Jason Grammenos <jason.grammenos@agilitypr.com>
2023-10-13 13:10     ` Re: Postgresql HA cluster Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
2023-10-13 19:21       ` Re: Postgresql HA cluster Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch>
2023-10-16 13:20         ` Re: Postgresql HA cluster Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
2023-10-17 19:59       ` RE: Postgresql HA cluster Jason Grammenos <jason.grammenos@agilitypr.com>
2023-10-18 07:44         ` Re: Postgresql HA cluster Jehan-Guillaume de Rorthais <jgdr@dalibo.com>

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