So, welcome to our second talk. Thomas got us off to a great start with some of the interesting
data about how performance has changed in the last 20 years. He also referenced, it's not
just about performance, it's also around new features. One of the features I'm super excited
about is logical replication. So, Boris, I think a few of you will recognize Boris, very active
member of the community through Postgres User Groups events like this. So, I'll hand
of the Boris to talk about another really cool feature in Postgres which keeps on growing
logical replication.
Thank you very much. Can you make up for this working well? Yes. I need to move a little with you.
So, we're going to start with our survey because they always say, I'm trying to become
a better speaker so they say, you need to know your audience. So, I'm going to try to get to know
the audience. So, the first question is, have you ever raised your hand in a presentation before
please raise your hand? So, you have already the training, that's excellent. Who is running Postgres
in production today? Good. Who is running Postgres 17? 16 or 15 or 14? Good, because you guys need to
upgrade and that's a good audience for this one, for logical replication because we use it to
upgrade. Who here can see themselves as DBAs? Okay, good. Excellent. Developers? More,
excellent. This is for them. That's good. Excellent. Another developer there. Great. So,
this is something that is have to be well understood by DBAs so that they can support the
developers but the developers also can know a lot so that they can help design in the application
and the schemas so that it works much better for any upgrade or any fancy architecture build with
logical replication. Now, logical replication is more powerful like power metal than a physical
replication, however, is complex compared to physical replication. But, simpler than complex
is better than complicated. So, we are good. It's not complicated. It's just complex. Now,
my name is Boris Mehlis. I'm originally from Chile, leaving in Belgium since the beginning of the
century. I think this is my 21st first step. I'm super happy to be able to speak in a Devroom.
And then my title is Holistic Systems Software Engineer. That means that I like to see the things
from the part of the system and also from above from the design application. So, try to combine
everything because I believe in the fundamental interconnectedness of all things hence the term Holistic.
Now, I'm also actually an er guitar player that actually what I do every day more than engineering
but not professionally. So, I'm not going to do it here. Now, this reminds me about another
question that I wanted to have regarding the audience who likes here heavy metal. Oh, that's good,
excellent. Who tolerates heavy metal? Okay, it's good. So, you're the right audience for this one.
Because we're going to use as an example. I like learning by example. And we are going to talk
about logical replication. So, logical replication has many use cases and the most common one is this one.
So, I was saying upgrade of a major version. I'm going to explain the difference between physical
and logical and then you understand why is it good for major upgrades. Now, for doing major upgrades,
one of my goals in life is to become a better writer but still not very yet. So, I cannot write
things to put them in images in your head. So, I decided to draw images instead of writing them because
it's going to be more difficult. So, the idea here is that what is my pen here? Good.
You're going to have a primarily note which is run in your version 15 or 16 or below with a
standby. This is a physical replica. In physical replicas, it's just going to be absolutely
everything. But then what you want to do is you want to move to a version 17, which also has a replica.
The advantage. Well, they have to bear with me with a few in the streaming story. Sorry for
the streaming. I'm going to disappear for a few moments. So, you can do is to take everything out
with PG dump and then inject it on the other side but this mean a lot of downturn. So, what you can do
is to take only this schema which is all the definitions of your table then you put it here
and then you have all the tables on your target which is a different version of major version
and then what you're going to do is you're going to set up logical replication which is going to
send streaming first, synchronize all the data and then it's going to change every gender
application write something here. So, the application is continuously writing,
put it's going to be sent to the logical replica and then at a certain moment you say, okay,
we are done with the old version. We stop everything. So, we fence it and then we say, now the
application is going to be pointing to the new fantastic version of 17 and then it's nearly
zero downtime. It's not zero downtime but it's nearly zero downtime. So, this is what we are
going to try to achieve. Now the point is what happens is why you are doing this? So, some step back,
there is something that is going to break here the replication somehow. So, this is what we are
going to study. But if something goes wrong then you will say, all we need to start all over again
or maybe not. So, this is what I'm going to show you. So, this is one of the main stuff that you can do.
The other thing that you can do is that you have this typical situation where you want to have
one to have back here for this. You have one node which is super large that is to start collecting
data from different places. And this one can be done with logic or replication as well because
you have different sources and you aggregate them. The thing is that here you are going to have
the possibility because you can write on this node to have some conflicts of data. And this is how
we are also going to study today. Now, in version 1617 people decided that they are ready to do
active active by the external replication changing data from one source to the target and from the
target to the source. But it has a lot of complexity and a lot of things to solve. So, I'm not
going to go into some details here but some of the stuff that we are going to study today are
going to be super important if you ever want to try active active. So, this is all the settings
why we are doing this stuff. But first, let's go with physical replication. Just just to know
how fast I need to go, who here in the room thinks that you could sit with a colleague and then
explain them how physical replication works. Let's see your knowledge of physical replication.
Okay, we can do the same about with logic or replication. Okay, so not everybody which is good,
so I'm going to explain this. This is your database. You have at the left side this is one node
and this is our replica. Okay, what we have here is the share buffers. What we have here is
your database, your files. And while it's going to happen is the application
through a backend is going to start reading some stuff from the database, select everything from
a table. If the table is not there, it's going to be a red from this point and it's going to be put
in memory. And this is how you access your database in memory. You never access directly on the
disk. Then you decide to write and then you make it dirty. Yeah, so your buffer is dirty.
But who know about acid? Yeah, yes, this is like an answer to them. Every time I ask an answer
to them, every day, they say, well, we're talking about databases. Let me get you excited.
So this is the properties, the D for durability in acid or acid means that every time that I write
something, when I commit, I have to have the guarantee that it's going to be really on this
and if the RAM crashes, it is there. So what happens is that every time that you change something,
through the world buffers is going to go to the wall file, which is right ahead log. It's a
log of changes. So this log of changes is used for multiple things for recovering your database
when it crashes or for sending it to another machine and then we can do a replication. Yeah,
so this is how physical replication works, actually. So replication is based on restored.
Okay, so that's basically when you have one single note, this is not enough because you send it to
the, well, it's called the file system, but the file system could have some also some cache in between.
So that's why we need to flush the data. And we're going to make sure, like when you
amount the disk first there, you just be before you put it out, you have to make sure that first
you amount it and then you take it out otherwise you're going to not have the physical copy here.
Okay, so this is one note. Now you're going to have a replica which is going to have the following situation.
You have a wall sender which is going to read every change here and it's going to send it to the
wall receiver which is at the other machine and it's going to write it on the wall files at the
replica. Absolutely everything. Even if you say insert something and then you say roll back that
garbage is going to send to the other machine as well. Even if you do vacuum to remove and clean up
your database, it's also going to send that all the maintenance is sent. Now the important thing here
is that here you can identify at which state is my replica. It receives the data, this is this.
It receives it, but it's also on disk at the other side for sure is this one and it is apply.
So that is readable for the application at the other side that is also something that we can measure.
So we know that this is good for read at the other side, but this is read only. It's a physical copy.
So you can only read. If I try to write here, boom, it crashes. That's not crash. Sorry,
the transaction crashes. Sorry, you cannot write here because this is read only.
Yep. Physical replication. Good. And then because in a certain moment you want to send things
from the run to the database, this is called a checkpoint. Here's called a restart point.
Things are going to go from the run down, okay. Right. Now how logical replication works.
Let's get to this point. We were here when we have one node. Now instead of having a wall sender
and a wall receiver just like that, in addition we have, but we also have the wall sender and the
wall receiver, but a logical decoder decoder gives us already a lot of information. It means that
it's going to look at the transaction and it's going to decode it. It's going to extract and it's
going to say, I'm not going to talk about blocks or anything. I'm going to say, this row should have
this value. Yeah. And because I'm decoding this, it means that at the other side, I can
store it in whatever order format. That means that I can do a different major version of postgres
and the implementation is going to be different. So I can do that. In fact, you could take this
representation, convert it into JSON and send it through Kafka to all the nodes. So all these things
of getting postgres sent to other different type of databases is going to load your replication.
You decode information and you stream it to all the places. So this is very powerful. So
important thing, load your replication is not going to just take anything. Only when you commit,
it's going to say, okay, this transaction is a good thing. So it's worse doing all the decoding
work and then send it. Otherwise, it's just going to skip it. It means also that all the
maintenance stuff like vacuuming and all the stuff is also skipped. Yeah. So I commit and then
the logical decode, I say, okay, this is the thing I have to start to do and then it's going to
look at backward and then I'm going to take everything. There are new protocols that says that if you
have super large transactions going to start doing work in advance, so that doesn't get to a very
slow moment. But then it says, it's a row operation that it says. Yeah, it doesn't say in block,
it says, like, insert this row, update this row, delete this row, truncate this table. Yeah.
And then the downstreamer, it really sounds like a character from Ronin James' Deal,
like the star gaser, the downstreamer. Yeah. So that one is not going to apply to the wall. See,
it's going to, through a process, put it immediately on the shareholders. Yeah. So it is available
there together with all the stuff that applications are right in there. And I try to do as much
as possible, friendly for blind color blind people. But this is one color, this is a different color,
if you don't see it. So there is this one, it's also able to write. Yeah. You can see also that
the representation here is kind of horizontal, here is kind of vertical. So different representations
of the block. And then everything will go through the wall buffer here from the other primary
nodes. So you have two right of all those two primaries. Okay. So this is important to
consider, because if you want to do active active, actually what you're doing is that you're mixing
things here and then having to filter stuff to send them back to the other side. We're not going
to get there yet, but that's a little bit of the idea. And this one can do his own checkpoints
in a completely different way. Yeah. Okay. Good. Have any questions about this?
It's clear now. Okay. Good. Excellent. Try to explain it to somebody else. When you try to
explain to somebody else, you test your own knowledge. That's the reason actually what I
give talks because otherwise I would think that I know. Yeah. Yes. Good question. So it goes the both
both the replicated data and the updated goal, shared buffer stem into a red headlog. Yes.
But if we want to do a cent from this node to another one back to that one. Yeah. Okay.
So the question is, Boris, can you repeat the question? Yes. So I want to do a bit of the question,
of course. So he's asking, okay, everything coming from this side gets into the share buffer
here and the application that writes here also gets here and the same share buffer everything goes
through the world files. How do I send this to all the places? Well, I need to set up
that on publication and subscriber. So this is the key. At this moment, this is a publication
and this is a subscriber. This is a subscriber. We receive all from the publisher. So if you
want to start sending things to another node, then you have to create a new publisher here.
So you have to set up. Yeah. Send it to this one. It's going to complicate stuff. But
but that's it is possible now since version 16. Yeah. Good. So this is all the the theoretical stuff.
Now let's go do some cool stuff. So before that, that's why I put this one to remind myself,
this is per database. You put a publisher for a single database. And the subscriber is also one
single database. If your post list instance has 10 databases, you need to set up 10 publishers
and 10 subscriber. The advantages that you can do a major upgrade of one database at the time.
So you can say, okay, we are going to coordinate to that development team and they can do the upgrade
whereas the other team is waiting instead of like waiting that everybody agrees to move to version 17.
Or you can even say like, well, this database is actually using more than half of the CPU
and all the nine databases are kind of having trouble. So let's take it away. How you take it away
with a publisher and a subscriber and then you send it to another node. So plenty of possibilities,
super cool. But it's one single database at the time. Yeah. Which means that users when you create a
user that doesn't belong to a database, you have to make sure that your users are present in
all the subscribers as well. Otherwise, you're going to see some funky stuff.
And metal is better than funky. So you better don't see funky stuff. Although it's not bad,
but I mean, it's not as good as metal. Okay. So let's break it. Let's see how it's working and then
let's break it and then let's see how it's going to. The first thing that I'm going to show you is
so let's say there's some who needs to Belgium here. So there's some metal festival here. One
is called Grass Pop, the other one is called Ikatras and I realize now there's one in Durui. So the
French-speaking community also have their own metal festival. I think in Durui, they also bring
some rock stuff from most of the time. Not that much. So let's have a table here which is for the
genres of heavy metal. You know what I mean? But isn't it just one single genre? No. There are many,
many heavy metal styles. And this is what we're going to study here. So we have an idea which is a
serial number that incremented self. It's our primary key and a genre which is unique. Because
each genre is unique in itself. You're going to repeat this stuff. Very simple but very useful
for our test. So sorry for the streaming but I need to move here. Because I'm going to show you
here. This is one database which is Grass Pop. Let me try to increase the phones. Is it better?
So let me show you the table select what you can do. You can do select star
from genre. And so nothing or you can also do a table. And it's exactly the same. So let me
copy this and I'm going to put it in another database here which is the, this is the upgrade.
This is looks select version. This is running version 17. This one here. It is running version 16.
Yep. And now here I'm going to show you a table. Nothing. Okay. So what I'm going to do here
is I'm going to insert a genre. Insert into genre. The genre and then it will be values.
Let's put a fair classical one heavy metal. I also like bear metal more than
little machines and stuff like that. But it's a different story. So then let me do. You can do watch here.
Yeah. So every second is going to you see it inserts a value heavy metal. I made a type of there.
So let's fix the type of update genre sets value genre to heavy metal. Where I
think it is 11. 11. Okay. So I change. So you see, insert updates. They're all replicated.
Awesome. Can you read? Can you know everybody with this? Yeah. Okay. Good. Yeah. So that works well.
Very nice. Here I can check in publications that I have select star from PG publication. Look,
this one doesn't look very nice. But if you do exactly the same query and then you replace the
semicolon. There's a song from Lannor. So we're good with semicolons. With backslatch GX,
it's going to show it vertically. Cool, isn't it? The good thing. I mean you can do backslatch EX
and you change every query but if you want to change only one, you do it like this. And you can see
that I'm going to publish inserts, also updates, also deletes and truncates. So then you can know
what you're doing. So you could have your publication, like you have let's say internet of things,
you just do logging. So you only add stuff, you publish only the insertion. And then at the other
side, like this, better, you say select star from PG subscription. Sorry. Yeah. And here you can see
that I'm connected to the database, grass pop with the whole grass pop. I'm trying to also take
advantage to change the name of the database to GMM because it's shorter and some people say
that shorter is better than longer. However, I think the GMM, it means grass pop metal knitting.
It is a festival. How you go to the meeting? I mean, it's like if you have a super nice events
and you call it metal knitting, maybe gathering sounds more like epic. But no metal knitting,
imagine if you have a super nice conference of developers and you call it, I don't know,
developers, European knitting. And you say you have meetings from Monday to Friday and then you
want to have meetings on the weekend as well. But not for some is great. I love it. But I mean,
why did it put within at the end? Anyway. So that's the subscriber. And it's working well.
But I told you that I was going to break this stuff. So let me break it. And the reason that I'm going
to break it is with this video. Video means data definition language. So it's a language to
define your schema. So I'm going to change the definition of the table jar. So let's change it.
And this is going to break my logical replication. So I'm going to say here,
author, table, well, was it a jar of art or column? Because we want you to know which bands are
more willing to come to the festival. So popularity, popularity, which is going to be an integer.
Yeah. And now I'm going to insert a new. Let me put it here. I'll see it again.
Table, jar, and then we do watch one. Anybody want to suggest a jar of heavy metal that
is particularly like? Power metal. Power metal, okay. Insert into genre. And then it's a genre
values. And then the values are going to be power. And I really like power metal, so let's see.
Okay, it's inserted. But it's not here. See? Why not?
Because the column at the publisher has three columns. It has ID, genre, and popularity. Whereas
the receiver only has two. So it doesn't know what you do with the data. It's a missing column.
The missing link. How do we know? Well, we go to the logs. So we go to logs and exit. And then
we do a tail minus 11, because 11 is a nice number. Follow bar, log, postgres. So you have to
logs are very important. Very useful. Okay. But lazy. Error. Logical replication target replication.
Graspo is missing column popularity. So the log is going to help me to figure out why this
thing broke. So we need to keep back on this streaming stuff. So let me just do the same thing here.
So P-sql, gmm. And then let me do outer table. Oops.
Check. And then I want to see the tables. And then it is broken at the moment. So what happens is
that. Yeah. Now you can back. So what happened is that it breaks. But it doesn't try to
consistently give me the data given the data. And I said, okay, well, let me try a little bit later,
because maybe the DBA is fixing this stuff. So it's not going to reconnect immediately.
It's going to wait some random moment of time between X and Y. And then it's going to reconnect.
Yes. And right now it's time now. So go to have the questions for the end.
And this is a very important question. Is it a very important question that you think
that I need to ask this question? Good for the other way around. What if I had the column
field? I'm not there. Okay. We'll work the other way around. Like I asked a column of the other
side actually, I don't remember. But I have a slide there at the end that says test your assumption.
So I assume that there are some rules. At least I work with another software, which is not open source,
where you can define rules. And then you say it is a missing column of the receiver, just skip that column
and don't worry about it. Yeah. So the question was, what if I added first the column at the target,
would that work or not? If we have time, we would try it otherwise we try it on the
or T break, because T is shorter than coffee, so it should be a better word.
According to some people, I don't know why. I like long wars. Like in Dutch, you have a
water, a real water server in Cislasi, super long word that I learned and I'm super proud that I
learned that word. Okay. Good. So we broke it and we fixed it. So that's nice. So that's the title
of this presentation. To keep on streaming even though it's broken. So DBL is not replicated,
which means that if you're doing a publisher's describing, you need to be able to manage your
changes on your schema. Every time that you do something, you do it in the two nodes. Okay?
Good. Okay. Next one. Sequences. What about sequences? Because this one was a serial. Yeah. So
let's see what is happening with sequences. This one I'm not going to break it at this moment,
but if you look at the definition again of the table, it says that here it is going to be the next
value of this request. The one that is going to be the next value for the ID. Yeah? So if I call that
here, next, well, it gave me 13. We're talking about metal and then we get all these kind of numbers.
Okay. But then if I want exactly the same query here, I get one. Because I haven't inserted anything there
at the replica. So I'm not breaking it now, but I want to show you that when you are going to
do remember when I have the application that switched from the source to the target, at that moment,
if you're starting 17 data, it is going to break then because it's going to think that, oh my
next value is 1. No, it is 665. Yeah? Which is almost evil, but not there yet. So you need to be able
to synchronize the whole thing with the sequences. So this is super important when you do the
cut-over, that's something that you need to take into consideration. Sequences. Okay? Good.
Next one, conflict. All right. So this one is a little bit more complicated and have two ways
of breaking the conflict. Yeah? Now, the first one is going to be the following. So I don't have
only the situation of doing the major upgrade that I go from a source to a target, which is exactly
same festival. I mentioned that there is another festival called Akatras. Akatras is very interested
in also getting the information from the other system, the other festival. So it's going to say,
you don't have two DNS DNS. Two actually schemas, one for Akatras and the other one is for
glassboard. Yeah? The use of actually that the owners here are referring to two important
Chilean women. This is Juanita Parra, drummer from the band, Los Hibas, really very good drummer.
And Tiripaneque, which is an astronomer from Chile, which is also an ambassador, ambassadors
from for UNESCO. So that's why I use them as the owners of this stuff. Okay? So
this one also have a subscription. So look, select start from PG subscription.
And it's also going to take you the host grasp book. Yeah?
And so it means that if I do select start from genre, it's empty. Why is empty?
No, I was missing the column, but it should have get the heavy meta first.
No, it had the subscription. What is the schema? I showed you that I have two schemas.
You need to do it like this, show search path. This is nothing to do with the logic
of application, but it is something that some people kind of, I have done it multiple times,
and it's like why is not working. The search path is super important. The search path is like the path
in Linux. When you try to find your commands to execute, this is the same thing, but with the
schemas, it's going to search the tables on this schemas that I have defined. And for this database,
I define architectures. So I have to do it explicitly. And if you know the zen of Python,
explicit is better than implicit. So let's make it explicit. Grasper. There you go. So I have
very metal with a new update, but I don't have the other one because I'm missing the column. Yeah,
so it's very good. So where was my name? All right. So this one was was broken, but if I show
everything from the table, it's going to come back at any time. Watch one. I should come.
How about I still have a, no. Thank you. Thank you. Thank you. Thank you. What did I forget?
Oh, when did the other table? Yeah, perfect. Perfect. Yeah. Thank you.
It's good to have a such a collaborative stuff. Yeah, there you go. Good. So you see,
that's why we're working in the open, because you can get fixed in fixes from the other people.
Super important for open source. Okay. So it's working now. But let's say that the people in
Alcatraz say like, oh, I know that this super good band Brutus from Belgium is going to be playing,
but they haven't put the information yet. And I'm going to put the genre of a Brutus already there.
So let's insert it anyway. Insert into a grass pop. Then I need to put the genre.
And then they're going to put the values directly. Values are going to be 42.
And then a Brutus plays post metal. Some people say that it's post hardcore post rock.
There's some discussion there, but let's say that it's post metal. Yeah, then there it is.
So you see, now you have, this subscriber can also write. And that's dangerous,
because why does the publisher also write exactly the same thing? Or not exactly the same thing,
but something that is going to conflict with my data locally. Yeah. So let's say that I
be able from grass pop seller. I have a forgotten to insert Brutus. Let's insert Brutus then.
Let's insert post metal. Yep. Okay. Insert it. No problem. But here,
it's still 42. However, here, it is number 15. So this one have 15 post metal. And the other one
has 42. So let's insert another one. I really like non-war of steel. It used to be non-war,
but they need to change the name for no reason. And then have part of the now. I have a part of
you here now. What happened here? I don't have part of it because it breaks. Why did it break?
It didn't break because I have the same ID, but I said that the genre is unique. So this is going
to be a problem that you're violating a constraint that says that this has to be unique. And I
have two rows with the same genre. And that's bad. Okay. So let me look at the logs. You have the
logs here of here. Yeah, here. But this is agatrust. Let me put it larger. Let me run here again.
You can see here. Here error. So I'm sure you have to debug your logical replication. It says
duplicate key via this unique constraint, genre, genre key, which is a unique stuff,
post metal already exists. Yeah. So there are two ways here. You're fixing it. How much
then you have it? Okay. There are two ways to fix it. And help me decide which one. I can either.
The easiest one, ID leads the one that I have in the target. The one with number 42. And this is
going to release the whole stuff. It's very simple. Okay. Now I can receive this stuff because
there is no conflict anymore. Yeah. If you have active active, you delete it here. So you send
the lead at the other side that you break it even worse. So that's active active is dangerous. Yeah.
So that's one thing. I can delete it and it's going to be fixed. The other one, I can try to figure
out what is the LSN that break and I can skip it. I'm your subscriber, but I don't want that
particular transaction because it's going to break my whole thing. Which one do you like? Number
two? Number two? Number one. Raise your hand if you prefer delete the row. Raise your hand
if you want to see how to skip the LSN. Okay. It's clear. I like democracy. Tell the LSN
I'll leave it here. So first of all, I need to figure out which one is the LSN.
Here, it says, I need two pieces of information. The LSN that break my transaction as I'm
in my streaming and the name of the streaming that go broken. And that is here. Here. You see
context. This is like humor. And humor context is super important in error. The context is also
very important. You're going to just tell a joke in the middle of some other context. Nobody's
going to think funny might be offensive. Here, the error is also important. So the context is
glass book journal in transaction 7080 finish. And here's the LSN. Logical application, broke.
Yeah, this is the finish of this one. So this is the number and the subscription. What is it
in the name of the subscription? Ah, here it is. TG 16659 and this is the, this is the different
section. Okay. So, this is the query. Select PG remote. I always forget. When you forget the function,
I have this a trick. You do backslash df for this cry function. Everything that I was called PG
something with origin on it. And it was something with advance on it. Yeah, advance. There you go.
I found the function. It is called PG underscore replication origin advance. And it received a text
for the subscription and the LSN. Yeah. So let me copy that. Okay. So I do select that function.
Yeah. And then I pass a text, which is my subscription. This one here.
And then the LSN. But the LSN have to be slightly.
So this is like this. And I need to cast this. All the ones in the string. So I need to say that
this is not this number, but it is a LSN PG LSN. But now this is not the one that I need. I need the
next one. Yeah. So I need to add that. Yeah. It's super difficult to automate this kind of stuff. But
first you need to do it manually in the nearest one. Okay. I say, I know that this particular LSN
LSN is for log sequence numbers. So your log not as a log in for the errors and information,
but the right ahead log, it has a sequence number, everything gets serialized. And you
say, that particular change, I want to skip it in advance to the next one. So that's what I did.
And now let's look at this.
Sir, I fixed it. I got the part of the, I skipped the one that was giving me the problem with the post
metal and now I have my table there. Yeah. Okay. So if I would have deleted, it would have been, okay.
But are you going to have an issue now with 42? Yes. So I'm going to have an issue now because the
key 42 might be a four in key for all the tables that refers to the ID. So then because this is also
a rightable note, I can update the key and I can say update a grasp of genre set key equals 15
where key equals 42. What did I say? ID. And I think you think.
Now it's just just because I like keys. You know, there's a very nice album called the
keyboard of the seven keys. All right. So it is, it is a complex. But once you understand,
like you said, hey, the key, they're strong. It's good. Yeah, conflict. So this is just a reminder that
share stake concurrency because our share in the state of the table is a source of all evil.
If you read the book, the call of Tulu by Lovecraft, you will see that Tulu was created
in a problem of share stake concurrency and that's why he's living in multiple dimensions.
The same thing happened with Becna, which is also one of the new evil stuff that everybody
was a problem of share stake concurrency. So beware. Okay, conflict again, that will be a problem
with keys, not just the unique constraint, but I'm going to skip that one. Now let's see the
importance of the primary key because I told you that we have to publish insert updates,
delete and all the stuff. Turnkate was the other one. So let's go here again to the grasp book.
There's one table that I haven't show you yet, which is select star from editions.
Edition has all the years where grasp book happened, yeah. But they said it never here.
Because 2020 was canceled because of evil epidemic. So I can have a 2020. I need to delete it.
So let's delete it. Delete from editions where year, year, year equals 2020.
But it doesn't let me delete it. You cannot delete what I mean, you cannot delete.
If I wouldn't have just a normal database, this would have been able to delete anything,
no problem. Delete is just a normal operation. However, I had a publication that says that I need
to publish delete. But if I don't have a primary key, another one to be able to send
either updates or delete. So let's look at the definition of the table.
Additions. Here, there's no primary key. So in the drawing, I said that this one doesn't
sense blocks of data that gets modified. It sends raw operation. So I cannot say delete the
raw that has year 2020 because it's not a key. So I cannot find the other side.
I would have to scan the entire thing in order to be able to find it. Well, then it suggests me,
well, if you don't have a primary key, you can set this to a replica identity full for everything.
You need to have an identity for your replica. If you don't have an identity, you cannot.
There's another way which is just half the tables that have primary key in a publication that sends
everything and another one that is an insert only publication. Because I know that I'm just going to
insert stuff. But if you need to delete stuff, then you delete it on the publisher and on the
subscribers. All right. So that's important on the primary key. Good. Our other extensions,
usually when you extract your schema, you get the definition of the tables, but also you have
create extension. Some extensions, like PG carbonara, which tells you how to cook the carbonara,
it has already data on the tables as well. So if you do that, you're going to create an extension
with data or PG agent with PG admin. Who has PG agent with PG admin? Yeah, that's going to
also have description of jobs. If you create extension is a table with data. So you say publish
for all tables. It's also going to send that data from the extension and it's going to arrive
here. It's going to say all error. The data already exists. So you are violation of the same key.
Yeah. So this is going to be a problem. The way you fix it is either you create the section
afterwards. Nobody is going to fail because of this. So you have to turn K the table and then
start the publication. It's not obvious. But there's a word. So closely words. Keep the logical
replication streaming. It is complex but it's possible. What you need to remember is that these
are two primary notes. So you can write on the publisher and on the subscriber as well. That's
the main difference with physical replication. There is no idea the other replication. There are some
software that provides that built in. But those are different. Suppose you don't have it at,
I mean, there are some functions that PG logical. You can have a function. You say,
with this function, I want you to add the idea here and also on my target. But by default,
there is no idea a replication. So you have to manage it yourself. There is no remote looking.
So you cannot look another table at the target to be able to do your modification consistently
across everywhere. I like this because if the other one fails, remote looking, it is a share
second currency. Unless you really, really, really, really need to do it. Then you evaluate it again.
You make sure that you really, really, really remote looking. I don't like it. Personal opinion.
But you will see the consequences. The subscriber will follow the publisher, but not blindly.
If it's going to break my data here, I'm just going to say, I don't want your transaction
talk to the hand. I'm not accepting it because it's going to break my consistency. And then the
other one said, I've been going to accept it. I'm not sending you anything else ever. That's a publisher.
So what you need to do is, okay, well, let's negotiate. Let me modify something here so that I can
accept your change. And then you continue. That will be delete in the row or you say, well, well,
I'm going to skip that one and then send me all the rest. But then I'm going to manage the consistency
myself. So you have to be careful about it. Primary keys are keys. I mean, yes, but a key concept in
your logical replication because you have to identify your row is a row operation that you're sending
to the other side. So use primary keys as much as you can unless you are just doing insert only data.
And then mind the sequences because they are not going to be synchronized. Don't forget about them
because it happens to me a few times. So it might happen to you as well. And the extension.
Don't think that off extension just create the extension. Some of the extension is they have data.
You have to be able to manage what's going on with that, okay? I think that was it. I know,
there are two my last messages that I want to give to you. First one is test your assumptions.
When I was doing the delete, I think it depends on the version of post. This one is just
going to say, I'm just going to delete it, but I'm not going to be able to send it because I don't
have a primary key, but I'm deleting locally. Now, this version of Postgres 16,
the source didn't allow me to delete. So test your assumptions in general in life. It's not just
for Postgres. And practice regularly. I've been discussing with people firemen or fire people
firewomen as well. They practice a lot. They do many, many exercises because when you are
understress in a situation, if you know your stuff, it's going to be much more relaxed and
you're going to really be able to fix and identify, like you were saying, I'm in super stress here
in front of the audience, but somebody in the box said, oh, you forgot this key on aim because
you are more relaxed. So if you want to be more relaxed in a stressful situation, practice a lot.
Yeah. So do these kind of exercises, test your assumption, break your logical replication,
try to fix it in two different ways by deleting the row, by skipping the transaction.
And I think that's it. Thank you very much.
Yeah, you have a question here. I can repeat the question, so.
So the question is, you mentioned that it's going to be useful for people who want to upgrade.
Yes. But you didn't specify what kind of conditions you would see. It's just where the
source code location is. I mean, I think you might have that we can do migration or
a large code location, right? Yeah. So the question is, where are the considerations that you
need to take into account when you want to do an upgrade with logical replication?
About the version. Yeah. So version compatibility, I think anything that is supported by
Postgres, to anything that is supported by Postgres will work. So all this one, which one is 12? 12
is the oldest one that we have? 13, 13. 13, 14, 15, 16 can upgrade to 14, 15, 16, 17.
So then what about application or work, for example, 14, 17? Yeah, no problem.
Yeah, you don't need to go one by one. You can go from 13, the register 17.
One more question. What is the purpose of being able to select what kind of updates you were
to publish? Why do they want to select updates? Okay, so if you want to separate the kind of
information that you're going to publish, I don't want to say updates or delete or just insert,
that is, most of the time when you have data that is a collection of things is you're not really
making a clone, but you are collecting some stuff. So you say, I'm going to do auditing,
which means I want to have all the data and if something gets deleted, I don't want to delete it
in the auditing service because I want to be able to see all the operations. So that will be
one way or say, like, I'm just going to do insert, but I'm not going to send any delete or updates.
Yeah. Thank you. So if you can chat the question out and Boris will repeat it for the people
who are online. So, yeah, there's a question there, sorry.
That for the long duration queries at some point database will close the query and say, oh no,
I have two of these, I'm going to like data about some practicating changes, all the tables you
are trying to query at the moment. I mean, that one solution is to enable that one publisher
and say, okay, they're not holding that so that this will be back. So the question is about
the fact that you use your replica, what is physical or logical, actually, for doing read queries.
But sometimes you are reading something that takes a lot of time here and the publisher is either
preventive for pushing stuff because it's going to touch data that you're using for reading
or it takes too much time and this one decides, like, I forget about you, I need to remove
my wall files because I need some disk space and then it breaks the entire thing and then
you're out of synchronization. So, actually, a logical replication, you are forced to use
a replication slot. So you cannot do it without replication slot. That's why I'm showing you this
replication slot and you see that for the grasp of GMM for the upgrade and for the grasp of
ICATRAS, which is just to get some information, both of them need to have a replication slot,
which is logical. You can see a slot type logical. In both cases it's active, you will see that
it falls when it breaks the streaming. So this is one way of seeing, like, oh, I don't have the
streaming working. My logical slot is accumulating a lot of disk space. I need to do something
about it. Either I fix the streaming or I say, okay, well, this is broken. Let me get rid of
the replication slot and then I continue. But this is the best way of keeping everything in sync.
The logical replication slot. Yeah. I hope, if that doesn't, the question I'm going to be in the
boost of Postgres from 12 to 4 p.m., you can also ask me questions there. We have time for one
more quick question that we're going to finish. Probably at 10, 50. Anyone have a quick question?
When you change the video, you use it off-table and afterwards you're thinking on the
of this subscriber, can you add that moment to the course of the single initiation?
Ah. So I showed that when it breaks the streaming, the streaming is not going to immediately
start doing the risk synchronization, but it's going to wait something. And then the question
is whether I can force the trying to get the synchronization as fast as possible. Because sometimes
you can fix it only one day and then the spirit of time is longer. I don't remember whether it's
a way of saying, like, try to synchronize again. Ah, there is one way, say, altars description,
disable and enable it. So you disable it and you enable it immediately. When you enable the
subscription, it's going to say, okay, let's try to connect and then let's try to get to the
public share now. And then it's going to start getting the error again or it's going to fix the error.
So re-enable the subscription is going to force the connection. Yeah. Thank you very much. We are at
time. So thank you. Thank you. Thank you. Thank you. Thank you.