PostgreSQL Replication

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL Replication

Mutuku Ndeti
Hi,

Need some advice here. I have an application using PostgreSQL. I need to install it on 2 servers for redundancy purposes and have 2 databases. I need the DBs to replicate to each other, in real-time. Writes can be done on both DBs. 

Please let me know if this is a feasible setup and the best way to proceed. 

Thank you

--
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL Replication

raul.giucich

El mar., 16 feb. 2021 10:56, Mutuku Ndeti <[hidden email]> escribió:
Hi,

Need some advice here. I have an application using PostgreSQL. I need to install it on 2 servers for redundancy purposes and have 2 databases. I need the DBs to replicate to each other, in real-time. Writes can be done on both DBs. 

Please let me know if this is a feasible setup and the best way to proceed. 

Thank you

--
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL Replication

Mutuku Ndeti
Thanks. Is there a free version of BDR? 

On Tue, Feb 16, 2021, 5:29 PM Raul Giucich <[hidden email]> wrote:

El mar., 16 feb. 2021 10:56, Mutuku Ndeti <[hidden email]> escribió:
Hi,

Need some advice here. I have an application using PostgreSQL. I need to install it on 2 servers for redundancy purposes and have 2 databases. I need the DBs to replicate to each other, in real-time. Writes can be done on both DBs. 

Please let me know if this is a feasible setup and the best way to proceed. 

Thank you

--
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL Replication

Thomas Guyot-Sionnest
In reply to this post by raul.giucich
On 2021-02-16 09:28, Raul Giucich wrote:

> This article will help you
> https://wiki.postgresql.org/wiki/Multimaster
> <https://wiki.postgresql.org/wiki/Multimaster>. 
>
> El mar., 16 feb. 2021 10:56, Mutuku Ndeti <[hidden email]
> <mailto:[hidden email]>> escribió:
>
>     Hi,
>
>     Need some advice here. I have an application using PostgreSQL. I
>     need to install it on 2 servers for redundancy purposes and have 2
>     databases. I need the DBs to replicate to each other, in real-time.
>     Writes can be done on both DBs. 
>
>     Please let me know if this is a feasible setup and the best way to
>     proceed. 
>

Hi,

While I have no experience with replication on pgsql, in general
multi-master database replication is much more complex and often require
a pretty rigid setup. The graphs on that page seems to tell the same
story for pgsql.

Are you sure you really need multi-master replication as opposed to
having a single active master in a replicated set? If properly
configured, cluster software can automatically fail over the active
master, which provides very good redundancy and is much simpler from a
technological standpoint.

Regards,

--
Thomas


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL Replication

Mutuku Ndeti
Thank you. 

I agree with you. Single master, with a standby replica, seems easier to manage. Is there a way to automatically promote the standby, when the active master fails? Is it feasible to have 2 instances of the application, writing onto the same DB, reason for two instances of the application is to allow for redundancy/load balancing. 



On Wed, Feb 17, 2021 at 11:22 AM Thomas Guyot <[hidden email]> wrote:
On 2021-02-16 09:28, Raul Giucich wrote:
> This article will help you
> https://wiki.postgresql.org/wiki/Multimaster
> <https://wiki.postgresql.org/wiki/Multimaster>. 
>
> El mar., 16 feb. 2021 10:56, Mutuku Ndeti <[hidden email]
> <mailto:[hidden email]>> escribió:
>
>     Hi,
>
>     Need some advice here. I have an application using PostgreSQL. I
>     need to install it on 2 servers for redundancy purposes and have 2
>     databases. I need the DBs to replicate to each other, in real-time.
>     Writes can be done on both DBs. 
>
>     Please let me know if this is a feasible setup and the best way to
>     proceed. 
>

Hi,

While I have no experience with replication on pgsql, in general
multi-master database replication is much more complex and often require
a pretty rigid setup. The graphs on that page seems to tell the same
story for pgsql.

Are you sure you really need multi-master replication as opposed to
having a single active master in a replicated set? If properly
configured, cluster software can automatically fail over the active
master, which provides very good redundancy and is much simpler from a
technological standpoint.

Regards,

--
Thomas


--
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL Replication

Thomas Guyot-Sionnest
On 2021-02-17 04:22, Mutuku Ndeti wrote:
> Thank you. 
>
> I agree with you. Single master, with a standby replica, seems easier to
> manage. Is there a way to automatically promote the standby, when the
> active master fails? Is it feasible to have 2 instances of the
> application, writing onto the same DB, reason for two instances of the
> application is to allow for redundancy/load balancing. 
>

Again, not being PostgreSQL-specific, it is possible to have an
active/standby master node with shared storage (ex fibrechannel,
iscsi... it can only be mounted on only one node at any given time and
it's the cluster's responsibility to ensure the other node has unmounted
properly). This is ideal when dealing with fully redundant storage
arrays, and fairly simple to setup on the cluster side. Attaching
additional slaves to an active/standby cluster is also usually simpler
since there is just one server/replication log.

Another solution is to have a replication ring (ex. A replicates to B, B
replicates to A). With such setup it is very important writes only ever
happen on a single node, and is easiest to do so by using a floating IP
that can only be reached from one node at any given time (again it's the
cluster's responsibility to ensure the floating IP is never on both).
This method may require a more complex configuration to ensure updates
are consistent and to keep additional slaves synchronized to the
cluster. Also since both servers have a permanent IP it is important to
ensure no updates are made directly to them. The warm side can still be
used for read-only access though (with a very small lag).

In either case, there is likely some configuration needed. This page
seems to list a few projects that may make your cluster setup much easier:

https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

Regards,

--
Thomas


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL Replication

Peter J. Holzer
In reply to this post by Mutuku Ndeti
On 2021-02-17 12:22:50 +0300, Mutuku Ndeti wrote:
> I agree with you. Single master, with a standby replica, seems easier to
> manage. Is there a way to automatically promote the standby, when the active
> master fails?

Yes. There are several solutions. We use Patroni, which is simple to set
up and seems to "just work" (we routinely cause a failover every few
weeks and so far it has always worked fine). Patroni is quite
postgres-specific, though, so if you want to manage other applications,
too, you may want to look into more generic solutions.

> Is it feasible to have 2 instances of the application, writing onto
> the same DB, reason for two instances of the application is to allow
> for redundancy/load balancing. 

You can have any number of applications writing into the same database.
At some point you might exceed the capacity of your hardware, but that's
a function of load in general, not the number of applications.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (849 bytes) Download Attachment