Обсуждение: 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

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

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 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/

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

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