PostgreSQL Active-Active

Am 06.02.2018 um 12:01 schrieb Dhandapani Shanmugam:

Hi Gurus,

Do we have active-active setup in postgreSQL, like we have in oracle
RAC?. Sorry if this question has been addressed already. What will be
best open source HA for postgresql with zero RTO and RPO.

-D

we have BDR, but this isn't the same as Oracle RAC. BDR is more for
replication over long distances (around the globe), and BDR isn't a
solution for HA.
For HA you should consider normal streaming replication and our repmgr,
for instance. And for Backup (RTO, RPO) our Barman.

http://www.pgbarman.org/
https://blog.2ndquadrant.com/news-and-roadmap-for-bdr-multi-master-postgresql/

Regards, Andreas

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

Greetings,

* Dhandapani Shanmugam (postgresql95@gmail.com) wrote:

Do we have active-active setup in postgreSQL, like we have in oracle RAC?.
Sorry if this question has been addressed already. What will be best open
source HA for postgresql with zero RTO and RPO.

What you're probably looking for is a 3 (or more) node setup with
patroni, etcd, and synchronous replication. Note that this will be a
shared-nothing architecture, avoiding the risk that something bad
happens with the storage system but requiring that the data be
replicated multiple times. Using synchronous replication means that you
have to have the nodes all close to each other, of course, but meets the
zero RPO. Zero RTO is just silly, but with patroni and haproxy you can
detect and failover pretty darn fast.

Thanks!

Stephen

Greetings,

* Andreas Kretschmer (andreas@a-kretschmer.de) wrote:

Am 06.02.2018 um 12:01 schrieb Dhandapani Shanmugam:

Do we have active-active setup in postgreSQL, like we have in oracle RAC?.
Sorry if this question has been addressed already. What will be best open
source HA for postgresql with zero RTO and RPO.

we have BDR, but this isn't the same as Oracle RAC. BDR is more for
replication over long distances (around the globe), and BDR isn't a solution
for HA.

BDR and logical replication are options but that's independent of the
"zero" RTO/RPO goal. If you're looking for write-scaling, that's a
different question (note that to actually get real write-scaling, even
with Oracle RAC, you have to be prepared to make app-level changes..).

For HA you should consider normal streaming replication and our repmgr, for
instance. And for Backup (RTO, RPO) our Barman.

There's a number of different options both for managing streaming
replication and for backup. As mentioned, Patroni is quite good for HA
requirements and for for backup, I'd suggest also considering
pgbackrest: http://pgbackrest.org

Thanks!

Stephen

Dhandapani Shanmugam wrote:

Do we have active-active setup in postgreSQL, like we have in oracle RAC?.
Sorry if this question has been addressed already.
What will be best open source HA for postgresql with zero RTO and RPO.

No, PostgreSQL does not have a shared storage cluster technology like RAC,
and I don't think it ever will: too complicated, does not scale well
(cache fusion, global SCN).

The normal way is to use physical replication with some third-party
cluster software that handles the failover.

There is *always* a short down time, but it shouldn't be more than a minute.
If you need to avoid data loss, use synchronous replication, but be aware
that it requires a low latency network, else your write throughput will
be very bad.

Yours,
Laurenz Albe

Dhandapani Shanmugam schrieb am 06.02.2018 um 12:01:

Do we have active-active setup in postgreSQL, like we have in oracle
RAC?. Sorry if this question has been addressed already. What will be
best open source HA for postgresql with zero RTO and RPO.

I think the closest to Oracle's RAC is Postgres-XL

Postgres-XL however does not make the same mistake as Oracle did, because they are not using a shared storage architecture.

https://www.2ndquadrant.com/en/resources/postgres-xl/

You can achieve this HA with forward and backward reverse synchronous
streaming replication with 3 nodes automatic failover with pgpool in
PostgreSQL.

There is always downtime but it should not be more than a minute its
depends on your hardware and network bandwidth speed availability.

--
[image: http://www.shreeyansh.com] <http://www.shreeyansh.com/&gt;

On Tue, Feb 6, 2018 at 4:31 PM, Dhandapani Shanmugam <postgresql95@gmail.com

wrote:

Hi Gurus,

Do we have active-active setup in postgreSQL, like we have in oracle RAC?.
Sorry if this question has been addressed already. What will be best open
source HA for postgresql with zero RTO and RPO.

-D

​

Do you have any tutorial about patroni + ha-proxy with postgresql (what
about load balancing with this setup?)

Regards.

-----Mensaje original-----
De: Stephen Frost [mailto:sfrost@snowman.net]
Enviado el: martes, 6 de febrero de 2018 06:12 a. m.
Para: Dhandapani Shanmugam
CC: pgsql-admin; pgsql-general@postgresql.org
Asunto: Re: PostgreSQL Active-Active

Greetings,

* Dhandapani Shanmugam (postgresql95@gmail.com) wrote:

Do we have active-active setup in postgreSQL, like we have in oracle RAC?.
Sorry if this question has been addressed already. What will be best
open source HA for postgresql with zero RTO and RPO.

What you're probably looking for is a 3 (or more) node setup with patroni,
etcd, and synchronous replication. Note that this will be a shared-nothing
architecture, avoiding the risk that something bad happens with the storage
system but requiring that the data be replicated multiple times. Using
synchronous replication means that you have to have the nodes all close to
each other, of course, but meets the zero RPO. Zero RTO is just silly, but
with patroni and haproxy you can detect and failover pretty darn fast.

Thanks!

Stephen

I would recommend to read
https://linode.com/docs/databases/postgresql/create-a-highly-available-postgresql-cluster-using-patroni-and-haproxy/

And actually we just did a 3 hours tutorial at pgconf.ru and soon we will
publish slides.

In April we will give the Patroni tutorial at postgresconf.us

On 7 Feb 2018 17:29, "Lazaro Garcia" <lazaro3487@gmail.com> wrote:

Do you have any tutorial about patroni + ha-proxy with postgresql (what
about load balancing with this setup?)

Regards.

-----Mensaje original-----
De: Stephen Frost [mailto:sfrost@snowman.net]
Enviado el: martes, 6 de febrero de 2018 06:12 a. m.
Para: Dhandapani Shanmugam
CC: pgsql-admin; pgsql-general@postgresql.org
Asunto: Re: PostgreSQL Active-Active

Greetings,

* Dhandapani Shanmugam (postgresql95@gmail.com) wrote:

Do we have active-active setup in postgreSQL, like we have in oracle RAC?.
Sorry if this question has been addressed already. What will be best
open source HA for postgresql with zero RTO and RPO.

What you're probably looking for is a 3 (or more) node setup with patroni,
etcd, and synchronous replication. Note that this will be a shared-nothing
architecture, avoiding the risk that something bad happens with the storage
system but requiring that the data be replicated multiple times. Using
synchronous replication means that you have to have the nodes all close to
each other, of course, but meets the zero RPO. Zero RTO is just silly, but
with patroni and haproxy you can detect and failover pretty darn fast.

Thanks!

Stephen

Regard,
Alexander Kukushkin