Thread: PostgreSQL Replication

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 <jnmutuku@gmail.com
> <mailto:jnmutuku@gmail.com>> 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

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

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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"