Next generation PostgreSQL replication with pglogical 3 and BDR 3
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 37 | |
| Author | ||
| License | CC Attribution 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/49113 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
PGCon 201821 / 37
1
4
5
7
10
11
12
14
15
16
18
22
23
24
27
30
33
34
36
00:00
Lecture/Conference
00:37
Lecture/Conference
09:11
Lecture/Conference
10:51
Lecture/Conference
20:21
Lecture/Conference
21:16
Lecture/Conference
25:51
Lecture/Conference
27:01
Lecture/Conference
31:06
Lecture/Conference
33:41
Lecture/Conference
38:06
Lecture/Conference
42:21
Lecture/Conference
43:41
Lecture/Conference
Transcript: English(auto-generated)
00:16
Which is it's 1550 now in my clock
00:21
We're good. Yeah. All right. Okay. Well, welcome This is about Peachtree logical 3 and BDR 3 and the work that we have been doing on those projects I'm Peter Eisen chart. I work for second quadrant. I'm a postgres developer postgres committer and
00:42
If you want to reach me and you can read that down there you can contact me there this is on so last year I Was here talking about the work. We've been doing in
01:01
Core postgres 10 on logical application. So this is not about that. This is about these external Projects and products So In that sense the stock is informative about what we have been doing and what these products can do for you But this this being a sort of also a developer conference, I guess there's a so the second aspect of
01:24
you know Showing and introducing some of the technology that exists around logical application in postgres that you know currently exists in some of these other projects but We had we had an unconference session yesterday
01:40
Well, we you know been talking about some you know, what should we do in postgres core and then these things and so You know some of that technology is already in existence in some of these Projects and products. So this is also if you're sort of a developer who's interested in this You can also take a look at You know some of that existing code or or build on some of that existing code if you're interested in that
02:04
So it has maybe both of these aspects in this talk So just to clarify what This is really so there are Currently sort of three Tracks or three sibling code bases that maybe more but this is sort of what I am aware of right
02:25
Three sibling code bases of logical application around postgres the the first one that came into existence around What was started around postgres 9.2 9.3 arrow was BDR bidirectional replication, which I'll talk about at the end of this talk
02:41
so that that was a Project to develop a multi master application system on postgres and a lot of that work that originally came out of the BDR fork Has been merged into postgres over time and some well-known features such as logical decoding application slots
03:00
Event triggers and some things like that came out of that The intention and Was and continues to be to eventually merge all that in the core, but you know the community processes are very particular and so that all has to be you know, A lot of development originally happens in these extensions and then it extends well trickled back
03:23
Which you know, which is why there's These different tracks exist so around postgres 9.6 what the desire was to have some kind of logical replication in postgres core So what was done is basically take BDR and strip it down to the sort of the minimum viable product in a way
03:42
and that was submitted as PG as a contrib module at the time Pgological to postgres 9.6, but it was not accepted Mostly for the reasons that people didn't want it to be an extension but wanted to be more built in and that then caused the
04:01
effort in postgres 10 to essentially take the pgeological code and Morph it into something that's more built in is what we and that's what we have now We create publication create subscription, but the the code You know and some of the people who worked on this or it's so it's all very similar internally, right?
04:22
There are sort of three separate code bases at the moment so Again, I'm not going to talk about the built-in Logical application facilities in this talk much except sold by reference that is last year's talk and
04:41
recorded somewhere, right so PG logical is a Plug in it's a technically an extension to postgres that provides logical application Okay, so that's basically what does there's some URLs. It's on github. It's open source There's also kind of a company or a commercial page It supports all major postgres version back to 9.4, which is when logical decoding was introduced
05:05
So it can't be used for anything before that but it supports all other major versions and it will also continue to support in upcoming versions and Just to kind of see how you know, what does it look like it should be pretty straightforward and familiar
05:21
you have to set the wall level to a logical you have to load the shared library and Then you're good to go. Essentially. There's some optional settings that Sort of will make sense later if you want to do stuff with conflict resolution you There's there's you know Again, I'll tell later what these dots are here
05:42
But you can basically set a conflict resolution mechanism and if you want to do conflict resolution on based on timestamps You you have to set commit track commit timestamp on which is which is a Feature in core postgres that you might have seen mentioned here and there but it has no real application in core postgres
06:01
So but it is used by these replication plugins Okay, then you create an extension and actually also the great extension cascade feature also came was sort of was submitted by People associated with the physiological
06:23
Project to make that kind of thing simpler. So get an extension and Then you can set up stuff So it's it's it's similar but I guess a little bit more complicated than the stuff that we have built in here You first have to create a node with some connection information I mean, it's not like shocking right like many replication systems work like that
06:43
But you have to do a little bit more work to name the nodes Okay, and then you create a replication set which is in core postgres called a publication but it's Effectively the same thing. So and this is all as Functions instead of DDL commands, but it's the same idea, right? You create you add some tables to it
07:04
You can also add sequences to a physiological support sequences Sequence replication, which is not supported in core postgres yet And then there's some other stuff like dropping and alter and add some table to move some table all that usual stuff There's just a bunch of functions for that Again, this is it just like a publication. This doesn't do much except to create some catalog structures on the on the
07:27
provider And then on the other end that's where the real action happens Is that you create a subscription in this term the terms that the terminology is exactly the same so you have a subscription The name some connection information should look very familiar if you play with create subscription
07:43
you tell it what replication set you want to do and then there's some additional options of Synchronizing data synchronized data is what postgres call also supports what PG logical can actually also do is synchronize the structure Which means that it can create the tables for you
08:00
Right. So if you have a replication set You know table when we have table one and sequence one here and you create the subscription and you tell it to synchronize the structure will take a you know It will just create those tables that does not mean that there is full DDL replication support It will just do it initially. So it's just an aid to
08:22
To so it was a question No, sorry It's just an aid to kind of get you started and this is the kind of functionality, you know that is quite useful in practice and customers want that But submitting something like that to core if you were to see how that is done would probably not be acceptable
08:42
So this is why having these sort of outside of core facilities is occasionally useful to get that kind of stuff done And and then that again, you know, there's disable and enable and Changing all of these things there's functions to drop things. It's very standard and at that point once you do that
09:01
background worker starts running and do the Synchronization and then do the apply very similar to our core postcode works Okay, and then there's an endless amount of catalogs even in views you can look at to see these things and it's actually I kind Of complained a little bit that is a bit too much now So there are some obvious ones like okay, the node information is stored in the catalog
09:24
It's called node and the subscription information is stored in the catalog called subscription and stuff like that the actual Sort of logical decoding and and while shipping is exactly the same as all in the Core post goes worked and how physical application works. So these standard views you will use the exact same use on the sending side
09:46
yeah, you see your Wall centers in pg-stat replication and also other things we have now pg-stat activity even and all that Right and you see the replication slot. So that's exactly the same and then on the receiving end There's just a bunch more a bunch more stuff to see
10:03
You know, how far has the initial sync come? What's the current status of the subscription? And is there are there any problems and things like that? So that's just a bunch of things that One doesn't have to memorize So one or a couple of sort of extra features that pgeological supports as a column filter
10:25
so you can tell it to only replicate a subset of columns, which On occasion is quite nice if you only want to take a part of the table to maybe some some kind of archiving or analytics Platform you don't want to do just a one-to-one replication so you can just tell it just take these columns and that's fine
10:43
It's pretty straightforward And then so column filters and you can also do row filters, which you give an expression and then it filters those out so again, maybe for some reason you want to filter out Certain things that you don't want to archive or something like that and there is a
11:01
Some of you might have seen there's a patch proposed for core Postgres to implement row filtering and Petri Alinek has actually volunteered to review that so he has implemented that and so he'll review that so That's the kind of thing so that you know eventually we want to trick all those features back into into core Postgres if they have sort of achieved a
11:26
Certain amount of stability All right, and then there's conflict resolution, which does not exist in core Postgres at the moment right now in core Postgres if you if the
11:40
subscription application Results in a an error of any kind including a unique constraint violation or something like that. You just get an error and You will kind of have to fix that manually You Know it's just not ideal, but that's just sort of the minimum viable product at the time there are
12:00
In each logical a variety of options some of which we talked about yesterday. So all of these are available Basically around you can do the error if you want It's usually not useful or the most useful in practice or either you apply the remote change overriding a local change So basically it if you if you have this sort of default setting that you just have one node you write to and you
12:23
Copy that to another node any local changes you do what would be all written in that case if they conflict Or you can do the other way around. I don't know if that's useful and Then the if you want to do it by timestamp You can use these two settings and you will have to do the commit times commit timestamp tracking option enabled for that
12:45
Right, so you can change those and there's a global setting for this and in Pgeological three you can also do it per subscription. This is kind of neat. This is a program that pgeological ships with
13:06
which helps setting up a Subscription so if you want to if you have this sort of Common case that you want to basically replicate everything The way to do that would be to set up, you know, you have a
13:22
creative replication set including all the tables There's a shortcut to do that. You don't have to add all the tables So you set up a replication set and then you set up a subscription and the subscription has to start Initially syncing every single table, which is slow and also has some robustness issues And so it's a very sort of time-consuming and then slightly fragile process
13:45
There is this facility By which you can take a physical base back up and then This kind of then goes into and converts it to a logical subscriber So it kind of goes through sees what all the tables are sets up a subscription
14:01
fixes up the replication slot converts them from a Physical to logical application slot doesn't convert them kind of makes a new one and adjusts everything So there's a bit of magic going on, but it's all you know, it's an external program. It's in user space It doesn't do any poking around where it shouldn't be. This is all kind of a Just basically a giant
14:20
You know, it's not it's a C program But you can think of this as a sort of a giant shell script that does useful things So that's kind of a neat thing to set up a subscriber much faster and easier so new and P geological three Or so this is what's coming out later this year and I'll get to that in a moment
14:45
We Have we have separated the receiver in the writer process which is Isn't sort of an architectural simplification But it also makes things faster because then the receiving and the writing can happen and sort of in a pipeline way
15:03
This failover support which I'll explain in a moment. There's some improved error reporting Which I also want to backport to postgres core, which is this View down here or table and this is the problem that you have now in or with older versions of P geological or in core postgres if the
15:23
subscription apply process has a failure of any kind It's you don't notice that unless you you know, check the logs all the time There's no other way a background process can report this All right, it can just log something and then it will terminate itself restart try again
15:42
And the only way you really recognize this unless you have really tight Log checking is that the replication will then fall behind and you'll have to dig into that. So What this will do is leave the last error In this table and then you can just kind of monitor that table So that makes it a little bit easier and you have to don't dig into the logs all the time
16:03
So if you have some kind of you know a conflict that is not resolved because maybe you said it to error And that would show up here That's kind of neat so support for replica identity fall we had the That doesn't that means you don't need a primary key anymore on a table so
16:24
Normally, you need a primary key on a table and it's still recommended But You don't need that anymore now, but it's gonna be very slow because it the primary key is needed to Well, you need a key to do the update or delete
16:40
If you if you just do inserts, you don't need a primary key But if you want to do updates or leads, it needs a key to find the road update and if you don't have a key Then you now have the option to use all the columns as the key But that you know, you can think of how this has all kinds of problems in terms of just performance
17:01
But also you that doesn't that doesn't actually guarantee uniqueness, so you can kind of make stuff work Maybe if you want to run an upgrade upgrade through this and you really don't have a key you can kind of work around that now So one thing that in in honesty so to speak where peachy logical is actually kind of worse than
17:23
Built-in logical application is the initial table synchronization, which in the core postgres You know is fully transactional and it's it's On a per table basis in the table the synchronization of multiple tables is is parallelized So it's it works really well in peachy logical 3 for a variety of reasons some of which have to do with the DDL
17:46
Synchronization it's actually a little bit more complicated. So it's in in past releases it was So it was the case that if Synchronization failed you kind of had to undo it manually With you know to some degree so that really sucked
18:04
Now it's it's more robust. It's still it runs everything transactionally. So there's a failure it all goes away automatically But it still runs all in one transaction as opposed to one transaction per table. So you can't parallelize it as easily as the
18:21
In core facilities have so that's a bit of a difference in terms of how these work so one others a neat aspect or some additional functionality that came out of the separation of the of the The receiver and the writer is now there's different writers. So what does that mean?
18:42
Right the default way of what the default behavior of the apply process is well, it takes the row and it sticks it into the table and it Does that on a quite low sea level right it Pokes into the executor and inserts a row into the heap and updates indexes and run some trigger
19:02
So it's some low-level C code that does it right? It's always done it so that That's what you want normally but you can also do different things with the row and and one option is that you use what is it called the SPI writer which Calls SPI for those of you who don't exactly know what SPI is is an internal
19:22
In an internal s an internal API in the post goes back and it allows you to run SQL inside the post goes back end So Instead of using low-level C APIs to stick stuff into a table You can also just construct an insert command or an update command and run through SPI which
19:42
Effectively does the same thing, but it's gonna be much slower. So you You don't want to use that if you can just use the other way But this allows you to then write into not just straight into the heap But it allows you to write into anything that kind of looks like a table and these are sort of examples down here, right?
20:03
So anything you can run an insert or an update command on you can Use that to write into it so you can replicate into a view if you if the view is updatable or insertable You can replicate into partition tables Meaning you can rep you could replicate into the partition route and then the port then the normal mechanisms will route it
20:24
So if you have a non partition table You can replicate it that into a partition table and it will then do its thing which is currently not otherwise possible Or you can even replicate into foreign tables if you have a foreign data rubber. That's that's writable
20:40
You can then basically replicate into anything you want and there are some use cases that you know I've become aware of that you want to replicate in something that's not postgres I don't know why anyone would want to do that, but it's not possible, right? So you can replicate in I don't not totally up-to-date on what the right ability of different for data rappers is But conceivably you could just replicate into some other, you know competing
21:04
SQL product or replicate maybe even into like, you know a file or things like that that exists right so anything is possible now and also things like postgresxl or You know any of these similar products. I am not exactly I know postgresxl works, but
21:25
Probably ciders screenplums stuff like that anything that looks like a table you can replicate into that Obviously super useful if you want to replicate for me So transactional environment to some analytics environment that uses a different kind of product. So that's all possible now
21:40
Again, not super fast, but it's possible One question I've received in this context before is can you replicate the other way around from a non postgres product into postgres Maybe but not using this, right? Because if this relies on logical decoding and and core postgres
22:02
Facilities to pull out changes and then this allows you to put the changes somewhere else But the other way around you would need to have a different piece of software that that's not this one. So that doesn't exist All right So that's the SPI writer list because that was too much fun. We just wrote a bunch of other writers, too
22:24
So there's a Kafka writer which writes into not a table but into Kafka and So instead of you know You give it an option you use the same sort of subscription and you tell what? Replication set so maybe some tables or maybe not other tables or
22:41
Maybe even filtered by column or row you can do that all here and then you just give it a different writer name and you give some connection information here and then it doesn't write it into a table writes it off to your Kafka environment and there there have been other systems that do this that Use, you know It's not super hard to do this yourself
23:02
to to write a Decoding output plug-in that does this so You know, this is not like sort of brand new functionality But if you're already using pedagogical and you want some of this filtering and all that stuff You can use all the same setup and management and then use and use this additional functionality, right?
23:23
So if you already have this then you have new options now, right? So and if you want to use it in Q it's the same thing so that's also available So slight sidebar, which is not in the slides here but we were just talking like an hour ago with some people you if you want to do sort of queuing and Postgres you could
23:47
really do that with Some of these facilities because they're all there's also something called a a generic wall message Which means there's a there's a function PG logical emit message
24:01
Which allows you to write essentially an arbitrary piece of string or byte a into the wall and then pgeological or any other output plug-in that supports that would decode that and allow you to do something with that and then potentially
24:21
Feed it into a rabbit MQ or whatever you want with it So if you have use cases, but this is not I just kind of made that up in the last hour, too but if you have use cases that kind of call for queuing inside the database and maybe if you PGQ in the past or you're looking into something like that and maybe think about this as well
24:43
because that might help you also and it's maybe a little bit of a More modern and maintain piece of software that you can also use for other things. So just a thought there okay, so failure or support this is
25:01
Quite important So what until now if you have a physical replication Pair, all right, just your normal master and then the standby for just general availability And you have a logical replication
25:21
Hanging off the master for archiving or analytics or anything like that Until now if you do a fail over here Then your logical replication Connection is or your logical application setup is dead and broken and you'll have to set it up from scratch because the
25:41
logical Replication information is not actually replicated over the physical application stream, right? So this is kind of a triangle that I'm drawing into the air here. And so this is this is clearly bad and Those of you who are sort of following hackers and stuff. They have seen various patches being proposed that
26:01
Have been named failover slots and things like that and They were quite contentious and then don't exist in any core postgres version Somehow through the magic of the developers who are involved in this they have now figured out how to
26:21
Do this without any changes in core postgres, so essentially PG logical ensures that the Replication information is synchronized Over the physical application stream and the information is now there if you are using postgres 10 So this is not going to work with 9.6 and before but in postgres 10, it just works out of the box now
26:44
So it you don't need any failover slot patches or anything like that if you have ever looked into that So that's now solved hopefully There I mean pgeological has Documentation so this I
27:03
This is In a way this just works now, but I There's no specific setup. You have to do unlike the failover slot the failover slot feature kind of was a variant of a replication slot where you explicitly had to tell this is a failover slot or this is a
27:23
Replication slot with this additional attribute and then things would happen in this case, you don't have to do anything in addition, but It's it's documented. But I don't there's nothing really you have to do. It just it just in principle just works, right?
27:43
alright, so this is the kind of pros and cons kind of List here So pgeological is it works for older versions so this is also useful if you want to just do an upgrade from 9.4 or newer to most recent versions Rollin column filtering as I explained some DDL support
28:04
sequence replication Truncate replication with the asterisk that truncate replication now also works in Postgres 11. So that was a new a New feature in Postgres 11 or is will be I guess depending on where you are in a sort of a death cycle
28:22
And conflict resolution and failover sport and truncated down there is the SPI right option So those are all kind of additional features and you know again We want to kind of push all that stuff into into core Postgres eventually a sort of time and community processes permit but if you kind of have interest in any of these kind of functionalities you can
28:41
So take a look in there and see how they work or even contribute to Back porting them or whatever, however you want to describe it so again if you ever You know pgeological source code is on github if you ever look into it It looks very similar to what's in in core Postgres in terms of structure. So
29:02
There shouldn't be a whole lot of effort to kind of get started with it. Okay Any questions on video Pgeological before I get into PDR briefly a question on does it support Kafka what?
29:22
Yeah Curborized Kafka Okay, the question is when you do this does it support Curborized Kafka, I think there is probably a lack of support for all the different
29:43
I know you can do a lot of things when you connect to Kafka in terms of SSL and The certificate validation and even Kerberos and stuff that I would probably Guess that that might not work, but it probably wouldn't be a whole lot of work to make it work You just have to you know a few more options here this basically there's a library behind this obviously
30:05
This is not doesn't do it all by itself. There's a library librd Kafka behind this and In a way most of these options are just passed through and we could add more So if this is a necessity, I think it could easily be added. All right, but we're not I mean
30:21
There's sort of some of these uses of sort of Advanced SSL options and Kerberos options. They're not you know, not first on the list to do but it could quite quite likely be done Okay So BDR is what started all this mess many years ago it's a
30:41
The name kind of stuck somehow it was kind of more of a Prototype name in a way, but it's a multi-master replication system for postgres multi-master asynchronous And it's it's it's been around for a while and it works. So Perhaps not well known. I know yesterday people talked about we should have multi-master for postgres in a way
31:03
It's been around for a while So the original use case it's again a little bit truncated the original use case for it was this sort of geographically distributed use case Because you know you you have this situation where
31:20
You you put a database in you know one location you build your app and put you all stack on top of it and you have you know Intra data center data center latency of you know, a few milliseconds and that's great and then When you grow and you start building front ends and different
31:40
Different locations, maybe not even across the world. Maybe just few under on the continent on different coasts, right? you will then have latency between your maybe your your Application server or web server and the database of you know, hundreds of milliseconds or more just because of the the physics involved and
32:04
People build all kinds of queuing and caching on top of things to to hide that which you know obviously has worked fine because that's how the internet works, but you know the the proposal is to have a better option of just putting Databases in different locations and then have local latency and then have the databases
32:24
Synchronize themselves. And again, this does not work for all applications and there's issues with this. So it's not One thing that works for everybody and there's a whole Not going to go into all the details of what's better or worse and then how to do this this would be another talk or tutorial even so
32:43
That's just a note that this is you know, we think Useful for many people but it's not a universal solution what would also happened is that a lot of people try to use this not for that but also as an HA setup and
33:04
Initially so you would basically have to you know, you put them in the same data center or maybe At least not across the world different availability zones, maybe that's how we term it nowadays and Then you just write to one but if there's a failover event, you don't have to do any promotion or anything You just start writing to the other because it's already up and fully qualified as a master
33:26
And that did not work Well with old PDR versions So basically what we did is try to like fix that and now that use case works So those are the two use cases we try to support
33:40
All right. So this is the version history of this The version one which is the one out now is Works on a Slightly patched postgres 9.4 plus a plug-in on top of that the version 2 Was then only a plug-in because all the again as I mentioned a bunch of features that were
34:07
Written for this within merge back into postgres and then at some point then we reached us the state where we didn't need to patch postgres anymore For for various reasons postgres br2 was then sort of not widely published
34:25
Because essentially the decision was made to you know At that point we would have had to support sort of three or four different code bases of logical application because at the same time we are also working on postgres 10 and So ultimately you would have to have you know, three four
34:43
Branches and there's only so many resources for that so basically two tracks of bd2 tracks of bdr core postgres and pgeological that's kind of if you want to think about it that way, so It was then basically decided to not Sort of publish that further second quadrant customers are using that but the decision was that made to
35:05
basically rewrite BDR as an extension on top of pgeological And that's what we're currently Focusing on so and in a way this makes a lot of sense because pgeological can provide the
35:27
transportation layer in a sense Because pgeological job is to get data from here to there and that's all it does and then you have BDR on top of that Which does sort of node coordination and some raft stuff and things like that, right?
35:41
And then it just sort of is an abstraction on top of multiple pgeological subscriptions So if you just have two notes, you will have this underneath that you don't really see that underneath that you have One pgeological subscription going that way and one going that way roughly speaking But pd pdr sits on top of that and abstracts that away and just says this is just a node group
36:05
So this is like, you know That's an architecture I think it's much easier to work with and develop for but also it reduces the number of code bases We have to maintain because now we basically only have to worry about pgeological doing its data shipping and BDR it doesn't have a whole another copy of doing this and a whole nother protocol and
36:25
All these views to manage that so you don't have to have that anymore Okay So the way this looks if you set it up is very similar again. You need to set wall level low to shared libraries in this case and
36:43
Set commit timestamp tracking to on if and most likely Great extension again, but the cascade is useful here because it depends on pgeological so it would pull that in automatically You should do cascade as the default because there's a lot of application extensions now that require another extension and there's no real
37:07
Reason not to pull those in if you want that I maybe something to think about and Then there's a bunch of commands to set this up, you know, again I just kind of alluded to that you create a node group and have nodes join and it does some raft stuff underneath
37:23
And then underneath it basically sets up a bunch of pgeological Replications that's and subscriptions and stuff like that In version three, I'll be the commands look the same in version one and two days. Just do different things underneath so there's there's
37:42
Well, there is kind of the default Ideas, there's only one replication set you can do a couple of variants of this But so the normal way to do is you just have one replication set and then you include the tables you want so you don't have to replicate everything but The best way to do it is to put all the things you do want to replicate into one replication set
38:04
There is DDL support and this has changed a fair amount between version one two and three but You know most DDL commands are replicated automatically and There there are some that just because of some internals
38:22
Having to do with adventure or stuff and business like that some commands are not captured by that such as create user commands I believe in that sort of thing but the normal like if you create a table here, it will automatically appear everywhere else if you Drop the column off the table will automatically appear everywhere else. So that's sort of normal behaviors is taken care of
38:44
And there there is also this explicit way of say you want to Do a specific just of any command really you want to run that on all nodes Maybe you have some maintenance procedure or some weird stuff. You can also do that. So that's pretty it's pretty It works pretty well, but just because of the way
39:02
Various postgres internals work. It's not sort of a 100% solution you still have to kind of hand-hold it a little bit But the basic idea is that you know If you just sort of create a schema create some tables and a couple views it'll it'll all replicate automatically so
39:22
Another Tricky business in these various iterations of VDRs been how to handle sequences because You Want to be able to write on multiple nodes, right? That's the whole idea. And so you need to some way to Partition the sequences. So if you use a number here, it's not already used there and there have been various again
39:42
If you've been following hackers over the last several years have been various attempts at doing this one of the attempts was labeled sequence access methods if you've ever seen that that was sort of an idea to do that and In a way we've given up on doing it the complicated way
40:00
and there is now a in bdr3 a So basically bdr1 kind of used a patch postgres that did something like the sequence access methods Be your to kind of just use kind of offsets of different sequences But bdr3 kind of has what I believe is sort of a good and quite simple solution so you can
40:25
You can choose for each sequence whether you want to just be a local sequence and then it's just local call and maybe you have conflicts or maybe you don't that's up to your setup or you can make it a time short sequence and that kind of uses this a
40:42
Way of creating sequence numbers that uses that at the node ID timestamp and then Attach a local sequence. You might have seen this elsewhere and it's it's sort of a an ad hoc standard in a sense It's also some sometimes called ksu ID or there's some
41:01
You know sort of an ad hoc standard, but it works just fine. You know, obviously the node ID is in there So it's distinct anyway, and you put the timestamp in there so that it's it's still kind of sorts So if you assign us a number here and you assign a number here if you just have the node ID You still get distinct numbers, but they will you know, they won't
41:22
all the sequences from one node will sort before the sequences of the other node, which it's not wrong, but It's sort of more convenient if they would kind of sort in the in the order they were assigned So that's why they put the timestamp in there And you can just kind of pick that and it does some some sort of
41:42
Plant tree rewriting in the background to make this work, but it uses all official Postgres hooks to do this so that this is a Exclusively and it's exclusively an extension, right? It doesn't do any sort of deep hacking it uses normal hook points to do this
42:03
So one of the good things about what is what about conflict resolution and BDR? Well, it doesn't do anything about it. It just does what physiological does. So that's one of the advantages of this new architecture But it does have the exact same facilities for you know doing First update wins last update wins local wins remote wins, whichever you want to do. It's the same idea
42:25
Okay. All right, we're landing just on the time here. So When and where and how is all is gonna happen the current versions which are out there are pgeological 2
42:40
It's open source. It's on github. You can use it. It's in use BDR 1 is again is all it's open source. It's on Presumably github or at least on the second one web page But That you know that requires you to use a patch Postgres 9.4, so that's probably a
43:00
An expiring solution there So When I originally wrote this they told me that this would be Available to for internal customers as today. Apparently I haven't phoned home to see if that actually happened that So this is sort of ready but we're rolling it out internally first
43:23
and Pgeological 3 Will be open source later this year There's currently no timeline for when BDR 3 is going to be open sourced or sort of published further. We're kind of working it out but probably not in the next couple months, so don't expect that but if you're
43:43
You know interested in doing some business with us. I guess you could talk to us about that so That's the end of that we've sort of just hit the the end of the time here so again, you can you know, you know feel free to try these out and play with some of these and
44:00
If you are sort of interested in where technology is going you can look at each of pgeological 3 or you know ask me in the meantime and Maybe have time for one or two questions or otherwise just you know, see me later at the social event maybe David please. Yes
44:41
So the question is about well, how do you If you have backups and you need to restore both nodes And what do you do then right? I mean the way we're really want to roll this out with BDR is not just two nodes But each you know, each logical node would also have physical Standbys That is what we recommend
45:00
So you would have sort of more on the order of six or seven nodes really to support all this if you want to have real ha so you you you're not really gonna but because of the failover support that I explained this would work if you if you do a Fail over between your logic between your physical pair the logical information would travel with it. I
45:27
Is probably not going to work smoothly Depending on exact some details, but that's I don't think that works. All right, I think we're out of time. Anyway, thank you