Обсуждение:

Hi team, 

You do understand that multi-master replication is not acid-compliant 
and the implications of that, right? It only works well for "read 
globally, write locally" scenarios.

Regards,
Michael Vitale

Firthouse banu wrote on 11/24/2021 4:18 AM:
> Hi team,
>
> The best open source tool to achieve Multi master replication in 
> Postgres ??
> I tried with pglogical but sequence are not replicated properly.
>
> Please do let me know best tool for this I have 5 servers all should 
> be in sync with data.
>
> Thanks
> Firrhouse

> On Nov 24, 2021, at 2:18 AM, Firthouse banu <penguinsfairy@gmail.com> wrote:
>
> Please do let me know best tool for this I have 5 servers all should be in sync with data.

Have you analyzed what data conflicts will occur and how to resolve them?

On Wed, 24 Nov 2021 at 14:03, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>
> You do understand that multi-master replication is not acid-compliant
> and the implications of that, right? It only works well for "read
> globally, write locally" scenarios.

This isn't true.

Async multi-master has performance advantages, but some drawbacks. But
systems such as BDR3 allow multiple modes of operation that overcome
these perceived issues.

-- 
Simon Riggs                http://www.EnterpriseDB.com/

Am 24. November 2021 18:06:45 MEZ schrieb Simon Riggs <simon.riggs@enterprisedb.com>:
>On Wed, 24 Nov 2021 at 14:03, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>>
>> You do understand that multi-master replication is not acid-compliant
>> and the implications of that, right? It only works well for "read
>> globally, write locally" scenarios.
>
>This isn't true.
>
>Async multi-master has performance advantages, but some drawbacks. But
>systems such as BDR3 allow multiple modes of operation that overcome
>these perceived issues.
>


Are you aware that the original poster asked for open source solutions?

Of course, BDR3 is a great tool.

--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

On Wed, 24 Nov 2021 at 17:08, Holger Jakobs <holger@jakobs.com> wrote:
>
> Am 24. November 2021 18:06:45 MEZ schrieb Simon Riggs <simon.riggs@enterprisedb.com>:
> >On Wed, 24 Nov 2021 at 14:03, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> >>
> >> You do understand that multi-master replication is not acid-compliant
> >> and the implications of that, right? It only works well for "read
> >> globally, write locally" scenarios.
> >
> >This isn't true.
> >
> >Async multi-master has performance advantages, but some drawbacks. But
> >systems such as BDR3 allow multiple modes of operation that overcome
> >these perceived issues.
> >
>
> Are you aware that the original poster asked for open source solutions?

Yes, I read that, which is why I didn't reply to the OP.

My response was to the statement about "multi-master replication".

> Of course, BDR3 is a great tool.

-- 
Simon Riggs                http://www.EnterpriseDB.com/

On Wed, 24 Nov 2021 at 14:17, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>
> > On Nov 24, 2021, at 2:18 AM, Firthouse banu <penguinsfairy@gmail.com> wrote:
> >
> > Please do let me know best tool for this I have 5 servers all should be in sync with data.
>
> Have you analyzed what data conflicts will occur and how to resolve them?

Which makes it sound like a problem exists, so I think some additional
information is needed for a balanced assessment.

BDR has facilities that allow you to find out whether any conflicts
occur for an application, so it is easy to assess this. Conflicts that
do occur are resolved automatically using programmable rules, yet
logged for later assessment. Data quality tools allow you to confirm
no anomalies exist in realtime.

Any conflicts that occur would be as a result of 1) data access
patterns, 2) choice of consistency, 3) how transactions are routed to
nodes. It isn't random and many applications are naturally conflict
free, even with randomly routed transactions.

If you use BDR using the AlwaysOn architecture then all transactions
are routed via a single node and no conflicts occur in normal running.
Depending on how failover is achieved, there may be a small window for
conflicts.

-- 
Simon Riggs                http://www.EnterpriseDB.com/

Unfortunately, it should be stated that BDR is not open source.  You 
have to be a EnterpriseDB customer to get it.  I think this applies to 
BDR versions 2 and 3.  I think only version 1 is truly open source at 
this point.


Simon Riggs wrote on 11/24/2021 12:21 PM:
> On Wed, 24 Nov 2021 at 14:17, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>>> On Nov 24, 2021, at 2:18 AM, Firthouse banu <penguinsfairy@gmail.com> wrote:
>>>
>>> Please do let me know best tool for this I have 5 servers all should be in sync with data.
>> Have you analyzed what data conflicts will occur and how to resolve them?
> Which makes it sound like a problem exists, so I think some additional
> information is needed for a balanced assessment.
>
> BDR has facilities that allow you to find out whether any conflicts
> occur for an application, so it is easy to assess this. Conflicts that
> do occur are resolved automatically using programmable rules, yet
> logged for later assessment. Data quality tools allow you to confirm
> no anomalies exist in realtime.
>
> Any conflicts that occur would be as a result of 1) data access
> patterns, 2) choice of consistency, 3) how transactions are routed to
> nodes. It isn't random and many applications are naturally conflict
> free, even with randomly routed transactions.
>
> If you use BDR using the AlwaysOn architecture then all transactions
> are routed via a single node and no conflicts occur in normal running.
> Depending on how failover is achieved, there may be a small window for
> conflicts.
>

Oh really?  BDR is acid-compliant? How can it be without a global lock manager to control access to resources and a consistent view of data and enforce isolation levels?

Please explain the magic.


On Wed, 24 Nov 2021 at 14:03, MichaelDBA <MichaelDBA@sqlexec.com> wrote:

You do understand that multi-master replication is not acid-compliant
and the implications of that, right? It only works well for "read
globally, write locally" scenarios.

This isn't true.

Async multi-master has performance advantages, but some drawbacks. But
systems such as BDR3 allow multiple modes of operation that overcome
these perceived issues.

Holger Jakobs wrote on 11/24/2021 12:08 PM:

On Wed, 24 Nov 2021 at 14:03, MichaelDBA <MichaelDBA@sqlexec.com> wrote:

You do understand that multi-master replication is not acid-compliant
and the implications of that, right? It only works well for "read
globally, write locally" scenarios.

This isn't true.

Async multi-master has performance advantages, but some drawbacks. But
systems such as BDR3 allow multiple modes of operation that overcome
these perceived issues.

On Wed, 24 Nov 2021 at 17:38, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>
> Oh really?  BDR is acid-compliant? How can it be without a global lock manager to control access to resources and a
consistentview of data and enforce isolation levels?
 

Many types of distributed system offer consistency. Very few use a
global lock manager, so this is not a requirement.

> Please explain the magic.

Anyone interested to know more can start here:
https://www.enterprisedb.com/products/bidirectional-replication-bdr-postgresql-database

-- 
Simon Riggs                http://www.EnterpriseDB.com/

Thanks, Simon, for your continued feedback.

Simon Riggs wrote on 11/24/2021 12:49 PM:

On Wed, 24 Nov 2021 at 17:38, MichaelDBA <MichaelDBA@sqlexec.com> wrote:

Oh really?  BDR is acid-compliant? How can it be without a global lock manager to control access to resources and a consistent view of data and enforce isolation levels?

Many types of distributed system offer consistency. Very few use a
global lock manager, so this is not a requirement.

Let me try to state it another way... Without a central place where you can see all the SQL coming against all of the read-write PG nodes at the same time, you cannot avoid conflicts.  PG is active/passive so it cannot resolve conflicts across multiple primary PG clusters.  Hence, BDR offers an "underneath the covers" approach to deal with conflicts when they do arise, but innevitably a conflict causes a previous commit to be rolled back or "suspended" for some kind of manual intervention later.  That is why BDR nowhere states that BDR is ACID-compliant. If it were ACID-compliant, there would be no external need to address SQL conflicts.

Please explain the magic.

Anyone interested to know more can start here:
https://www.enterprisedb.com/products/bidirectional-replication-bdr-postgresql-database

I spent about 15 minutes starting at the URL stated above to drill down into some areas where this subject might be addressed and I couldn't find it.  Perhaps you could be more specific.
I did find this in separate BDR documentation: "BDR is a loosely coupled shared-nothing multi-master design."  I guess you can say "loosely coupled" is a nice way to say not ACID-compliant.

Are you aware that the original poster asked for open source solutions?

Of course, BDR3 is a great tool.

Postgres-XL is not exactly a replication solution but it is ACID compliant and it doesn't have problem with sequences. It is a decent high availability solution. More information is available here: https://www.postgres-xl.org/

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On Wed, 24 Nov 2021 at 18:18, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>
> Thanks, Simon, for your continued feedback.
>
> Simon Riggs wrote on 11/24/2021 12:49 PM:
>
> Anyone interested to know more can start here:
> https://www.enterprisedb.com/products/bidirectional-replication-bdr-postgresql-database

You clearly have an interest in BDR, and some knowledge of BDR1, so I
thank you for that.

The limitations of BDR1, written in 2014, are not limitations of the
architecture in general, merely things it didn't do. That has led to
some misunderstandings about what is possible and regrettably some
incorrect points have been made that I've attempted to rebut. Some of
those points relate to how Postgres-XL worked, but are not relevant to
BDR.

7 years later, BDR3 has a significant number of features not present
in BDR1. That may not be as well known, since as you say, BDR3 is not
fully open source. I regret that I was not able to fund further
development of BDR without charging users. Having said that, probably
more than 50% of BDR features are actually open source and part of
PostgreSQL - the contribution of new features has continued with each
new release.

Specific to this conversation, BDR3 supports multiple transaction
modes - with various kinds of consistency. I would point out that
those modes are slower - which is why multiple options are present.
Some modes allow conflicts, some do not.  This has nothing to do with
"Loosely-coupled", which does not present a limitation.

Should a conflict occur, a conflict doesn't *inevitably* cause a
rollback/suspension requiring manual intervention. That doesn't seem
to be a fair characterisation of the current behavior, which again
differs significantly from BDR1. Just like any form of replication,
various actions can cause breakage or difficulties.

Thank you for pointing out deficiencies in the docs. I wrote a large
part of it myself over many years and it seems we haven't yet captured
all of the possible options there.

I'll work on improving the information available to help those
interested in BDR and/or related tech.

-- 
Simon Riggs                http://www.EnterpriseDB.com/

Hi Simon,

Thanks for taking your time to elaborate.

Sent from my iPhone

> On Nov 24, 2021, at 5:00 PM, Simon Riggs <simon.riggs@enterprisedb.com> wrote:
>
> On Wed, 24 Nov 2021 at 18:18, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>>
>> Thanks, Simon, for your continued feedback.
>>
>> Simon Riggs wrote on 11/24/2021 12:49 PM:
>>
>> Anyone interested to know more can start here:
>> https://www.enterprisedb.com/products/bidirectional-replication-bdr-postgresql-database
>
> You clearly have an interest in BDR, and some knowledge of BDR1, so I
> thank you for that.
>
> The limitations of BDR1, written in 2014, are not limitations of the
> architecture in general, merely things it didn't do. That has led to
> some misunderstandings about what is possible and regrettably some
> incorrect points have been made that I've attempted to rebut. Some of
> those points relate to how Postgres-XL worked, but are not relevant to
> BDR.
>
> 7 years later, BDR3 has a significant number of features not present
> in BDR1. That may not be as well known, since as you say, BDR3 is not
> fully open source. I regret that I was not able to fund further
> development of BDR without charging users. Having said that, probably
> more than 50% of BDR features are actually open source and part of
> PostgreSQL - the contribution of new features has continued with each
> new release.
>
> Specific to this conversation, BDR3 supports multiple transaction
> modes - with various kinds of consistency. I would point out that
> those modes are slower - which is why multiple options are present.
> Some modes allow conflicts, some do not.  This has nothing to do with
> "Loosely-coupled", which does not present a limitation.
>
> Should a conflict occur, a conflict doesn't *inevitably* cause a
> rollback/suspension requiring manual intervention. That doesn't seem
> to be a fair characterisation of the current behavior, which again
> differs significantly from BDR1. Just like any form of replication,
> various actions can cause breakage or difficulties.
>
> Thank you for pointing out deficiencies in the docs. I wrote a large
> part of it myself over many years and it seems we haven't yet captured
> all of the possible options there.
>
> I'll work on improving the information available to help those
> interested in BDR and/or related tech.
>
> --
> Simon Riggs                http://www.EnterpriseDB.com/

Hello Ji,

normally this works,
with some of my students i use this combination of pdi and pg, starting
out with 
https://www.packtpub.com/product/pentaho-data-integration-quick-start-guide/9781789343328
(PDI 8, but the same applies with PDI 9)

basically: reading/writing to plain pg from pdi should be ok in my
experience

Jiankang Ji schreef op do 25-11-2021 om 10:12 [+0100]:
> Hi Team Experts,
> 
> I am new on using pentaho data integration(PDI) on postgesql to set
> up a ETL process to handle a time-series postgresql DB with data
> collected from a IoT network. I've encountered a lot of problems when
> set up this process from either the PDI tool or PostgreSQL or both.
> So, if you are familiar to both PDI and PostgreSQL, please give a
> hand. Thank you so much in advance for your assistance.
> 
> Best regards,
> Ji

-- 
mvg,
Wim Bertels