Обсуждение:
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
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:
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.
Simon Riggs wrote on 11/24/2021 12:49 PM:
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.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.
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.Please explain the magic.Anyone interested to know more can start here: https://www.enterprisedb.com/products/bidirectional-replication-bdr-postgresql-database
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.
On 11/24/21 12:08, Holger Jakobs wrote:
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/
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
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
Hi Wim,
Thanks. Nice to hear from you.
Now, let us go to practical issues, and hope I could share with your experience.
1. It seems that I can't find good examples on complex math calculation in Spoon's samples folder. I need to calculate a formula with all the Ln/Log, n-power, +-*/ conbination. Do you have a sample formula.ktr that I can share?
2. I need a watch-dog to launch transformation/job once an update of the input logdata file being detected. I'm not that far yet. The problem at present is that I use a Community free version of PDI, and have no Pentaho Repository connection, when I try to test Pan.sh --file:trans.ktr to automaticly run trans.ktr on a Ubuntu/Linux Terminal, error pops up. It asks me to create Repository. To create Pentaho Repository I have to be a registered user. I'm not. Any good ideas? Do I need to pay to become a registered user?
Thank you.
Best regards, Ji
On Fri, 26 Nov 2021 at 14:48, Wim Bertels <wim.bertels@ucll.be> wrote:
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