PostgreSQL replication by example

okay so what is replication we've got a master DB we replicate to a slave DB we have a copy of our DB that's always a great thing to have and we want to do this because we have DBS and we don't want to lose data we can also do things like high availability with us and yeah the different types of replication the older slow nice style trigger based ones you've got the binary 1 which is the sync and I think that's what I'm gonna do today you've got logical replication that's a newer feature I've not actually worked with that pretty cool but works on the same basic principle but slightly differently you can do it with a pool or something like PG pool can do a replication for you to some extent and you can do your own homegrown select from table insert somewhere else based on a trigger or whatever if you ever want to hear something interesting talk to Malcolm about what their company did crazy stuff okay so why do we want it data goes away hard drives break motherboards break machines get compromised crypto lockers delete and/or encrypt all your data that has actually happened recently which one of my clients and luckily we had to replicate the data and we could restore right up until the last transaction so we are going to cover streaming replication wall files do any of you not know what a wall file is I'm going to zoom you don't so when Postgres works with data it loads it into shared memory there's a bunch of pages and whenever you update something it effectively writes into those pages but it first goes and writes a call it a binary def into the wall file commits that and we make sure that's nicely on the drive we don't continually write to the 5 gig file as we update it we put that in a wall file and occasionally the checkpoint writer then goes on and writes the the actual database to disk when we get a crash the files on disk differs from Ram or what Ram was breather crash but luckily there's a wall file and your DB can chug and and look at the wall files and figure out what the state of the DB should really be this can take a couple of minutes it actually depends on your checkpoint writer timeout and that is what's used for replication so streaming replication is effectively taking that little binary deaf and shuffling it off to another machine to go apply it on a exact binary copy that is the the async and sync case with logical replication actually add a little bit of extra data in the wall file making it a bit more heavy but they can actually did extract the name and other the original query so effectively it instead of giving you our little binary bits it gives you a little insert and select an update queries that you can shuffle along and thus you can do an update on only one table so you can replicate per table with streaming replication you don't replicate per table you replicate the entire database all the data or actually the entire cluster so whether you've got one or fifteen databases they all get replicated okay so many many many moons ago these wall files they I think of they're sixteen Meg you can actually set it up they would get rotated so you they'll write a one file of 60 Meg and then go on to another one and as those values get played into the database they get cleaned out and there's this nice little archive command in your in your post peer setup where you can tell it to copy those files somewhere and that was what streaming li replication was many many years ago 16 Meg's at a time which um yeah it's not great it's better than nothing but it's not ideal so the bottom processes the old let's archive the files and copy it and the new method is as those little blocks get written they get shuffled along while actually they get they don't get sent they get they are fetched by the the slave but in essence you can look at it like a pipe pushing it down okay so the difference between async and synchronous replication really boils down to whether the transaction waits for that bit of well file to be shuffled down to one or more of its replicas before it returns from commit or not so async it commits it goes on and assumes that the server will fetch it soon typically in our setups which is gigabit from each other nice big little machines it runs about 80 to 100 milliseconds behind it in high load conditions and it's more or less perfectly in sync in low load conditions so the first thing people always say when they get to replication is high availability I want to do synchronous replication because that'll make my database more available and that is not true because that will just give you two points of failure if Machine number two breaks then no more transactions commit that's an expensive lesson at at a on a Thursday night at 11 o'clock when you get called it's not a fun one it is not being available while smoking weed I was clearly inebriated when I made these slides it it is a it is a fallacy to think that you get high availability there is some work you need to do to actually make a failover happen and there's a whole world in a che that you need to understand because if you if you get a split brain condition basically you think the one BBS offline you switch over to the other one and both are partially online you've got some transactions going in to both you are in a world of hurt so it is actually not that it that simple but typically what you do is you connect to your database with a PG pool or a or a PG bouncer and once your main DB goes offline you've got some mechanism to monitor that you shoot that guy in the head you switch over to the other one and you effectively just switch PG pool and/or PG bouncer to point at the other system you can either do that with IP switching or whatever but that's how you how do you do H I but what I'm going to do today is show you just how to set up the basics of replication okay so I'm gonna switch into my command line and start running commands first off I just want to check who here doesn't know what docker is anybody everybody's comfortable with docker and understands docker happy not ok are you shaking your head can I do it just a quick cover of what it is okay so what i'm gonna use this i'm gonna use docker as a just a way to kind of isolate my master and my slave without needing two machines and two fooling stalls so what you can do with docker is the docker is like a vm but lighter weight so it runs on your kernel there's not a second kernel running and you can tell docker there's a PG directory you're a Postgres you bind that direct directory into the little docker and it thinks it's a post grace it thinks it's got its own little sorry about that its own little little file data but actually it lives on the main machine so on my main laptop on the SSD there will be a folder called temp post grace that will be my master the database files will be in there i will bind it into that directory on docker and docker will start up and initialize that and think it's a database and a bit later on I'll do this I'll do a full copy on my local machine a PG base backup which you base dump come from as well see it in a second and that will serve as the replica so we'll have a docker which is I'm assuming some alpine Linux with a Postgres installed on it looking at that directory thinking it's a machine on its own running a whole Postgres instance looking at the data in that folder and in yeah okay so this is not gonna be happy from you on ok any case I'm going to quickly get there but that's the basics of docker and I'm effectively using it like a VM okay so quickly switch sure so this command is effectively creating a new machine called dev DB that's the name of it it's forwarding port 5 4 3 2 from inside the doctor to outside the docker it is creating a post grace user called my database it's called creating a post grace user password called my database very creative and it's forwarding in the temp Postgres port I have a folder oh this is wrong way around so my brain is going to not like it Oh and we run that command and we get a first grace first need to start my my local place grace Vasquez sequel come on okay so my local post grace is stopped and just to confirm that there's to be SQL to my database and it's just like oh dude nothing running okay so now we start this and it is partially started yeah docker or him my DB year okay doc darker stop Dave DB is that what I called it yep stopped it and I'm just going to RM it so I'm deleting the the VM and okay now we're starting it and it has started wonderful it is Sun shines and rainbows and all those wonderful things and something's wrong that is confusing me okay it took a second start up my database and I am in and there is a I well pretty much a 90 DB blah there's nothing but we could probably do something like PG stat activity and we're the only activity obviously okay so that was the first bit and then we are going to move along to the second bit so you start off with an initial eyes DB I can now go and do all kinds of fun things like create databases in there restore all my data and put it all in and it'll be happily in that little folder there it also has its own little post Chris confer on there I am going to set this up to trust the role of replicator you have to set up a replication user and it's a specific role never ever ever do what I'm doing now you never put trust in your PGH PA that is a good way to lose your data or give it to the nice folks at in China or Russia but because I am not in the mood to set all that up and because I did it's rather time consuming I'm just going to do that and that's not going to work because I'm not route but over here I am route so we've added that and we are also going to add sure that is not legible let's see if we can maybe make a little bit more legible this or this this is the three settings you're going to need I'll cover them in a second if I still know what they mean temp Postgres Postgres sequel dot conf and you can go through these folders in more detail but okay let's see if I can maybe zoom this a little bit you are going to set your wall level to hot standby this actually allows your slave to do queries while it's in recovery mode the max wall centers allows determines how many people can how many slaves can connect in and suck data from it and the hot standby similarly helps with the way it set up the wall level can't be minimal if it's minimal you can't replicate this not enough data that goes into the wall then there's a couple of levels in between that effectively boils down to replication and in this logical and I hope I'm not lying it is quite possible that I am actually lying so I think it's a little bit more nuanced but that's ballpark the truth of it okay at this point we are also going to create a user called replicator roll replication and we are going to give it the most imaginative password of a ripple boss I'm clearly a very creative person and that role is created now we only have to restart the DB but because we're dealing with Dockers it's actually easier to just stop the docker and start the docker so I'm just going to tell a little Dave DB that it is um it needs to restart and then I'm gonna start the base backup right after that do you you're all comfortable with what PG base backup does have you ever used this if you have not it is a way to make a full binary copy of the directory your Postgres is running and it does it through the the port so you don't actually have to have SSH or some kind of a some kind of a one RM access you can do this through external connection the PG based backup connects to the host port the directory we're gonna output the user we're going to use the P I don't know what that is the V is I think verbose and the X is supposed to copy the log files but it doesn't work on this version I don't know whether they've changed the format but yeah we've restarted that it's unhappy with X we do that it makes a QuickBase backup and it's done the backbase backup and happiness so now we have in temp we used to have only bus grace now we have post grace slave as well magical this is all fairly standard this is all part of what you would typically do when you backup a database okay but now that we've done that we can set up in this new copied directory which is a binary copy of the regional database we can set up Postgres slave the recovery own file this file determines a couple of things it gives you the primary connection info so that is where it's connecting to where do I suck my data from so this is effectively using user replicator repos you know all goodness about that and all the the goodies to connect into the primary it specifies a trigger file that will control the failover so on that fateful day when the panic is high and the main DB is dead and you want to get this thing to be a live DB you create that file and then the DB will go oh happened we'll just be moving along and becoming the master and it will from that point on become a writable vb idle or readwrite TB and the standby mode on just basically says you can query you can do queries on it there are limitations to what you can query on it but that's basically what it boils down to not rocket science yeah okay so let me just quickly copy that and goodness and it's not fun copying from a presentation but I'm sure you guys will forgive me for being a bit slow on this with this okay so yeah that was stupid just copy the following first always fun to watch someone else copy and paste code or commands in code okay and once we've created that we are going to start up this little monkey and it will be happy hopefully it won't take offense to be call them monkey apparently that's a terrible thing to do okay BG base backup this can also be created by adding - or - okay okay yeah so in the PG base backup command you can specify - R and it will automatically create the the require a recovery file but in my case I wanted to actually just show the file it's a very basic file there's a couple of more settings but you can you can cover that from the software of the documentation okay so now we're going to start another docker this is again extremely basic we are doing the same thing we're just calling it dev slave DB a massively creative again my database my database because we love security we're pointing in the folder that we base back up into this thing so typically what would happen in a system where you don't do docker you'll be pointing your main sorry I'm struggling with this guy your main Postgres et Cie Postgres Postgres doc on file you'll be pointing at wherever you put the backup so either you'll be restoring a backup you're doing a be doing a PG base backup into that folder or just changing it over okay so if we hit enter this all happily connect the only thing that I need to specify year in addition is I'm doing link dev DB to DB and if you were to look at the details that we had here I said please connect to host DB and that's just a little bit of a little bit of docker fun it'll kind of manage your DNS for you so you can kind of use names inside of your your little virtual machines and kind of connect them up because docker kind of help it's annoying it creates new IPs so it's a little annoying to get to that so it provides this nice naming mechanisms to do that so we hit enter it starts up it's happy it says we've reached a consistent state of recovery and everything is more or less happy okay however this is the most minimal possible thing you could do there's a couple of things that can go wrong in this if we were to now hit the it's going to be SQL and we go my database select star from PG stat so that replication yeah there we go and there we have a guy receiving we can actually see that he's the saint location that's the saint location that's the right location that's the flush location that's the replay location so you you can actually see where the different guys replicating from you or at so this allows you to to see how far behind there or whether they flushed it stuff like that you can actually run a replicate a replicating machine with a replay delay and the reason you would want to do that is if someone hacks your database it says dump table or a truncate table that'll happily replicate across and you will lose the date on the replicated machine as well so running that some time behind allows you to into what we basically break replication and recover to up to the point you can also with the wall files run something fairly far behind you can have a base backup and just copy all the wall files for a week and then do a point in time recovery just interesting interests like fairly handy thing to have if you get hacked not that we've been hacked but trading the day ok now one of the problems with this is that if I were to write heavily to this database between the point where I did the base backup and where I started the replication the wall files would have it would have filled the first one and the second one and the third one and at some point it would have recycled one of those wall files and if I start the replication then the service starting up says hello gimmie Wolf oil some point and it'll say oh my goodness that file has been removed and you cannot continue with replication this can be solved and the way we solve this is with replication slots and that is the correct way of doing replication so I'm going to show you how to do that ok so I'm not going to replicate that because replicating brokenness is I think kind of silly so we are going to move on to replication slots and I'm going to quickly show you that we need at least PG max replication slots at least at 1 you can say 2 three or four whatever and you are gonna create a replication slot BG create physical replication slot standby replication slot it's called and effectively what that does is as the slave sucks data off of the master it says by the way dude I've got this I'm at and it tells the master where it's at and in the master says well I'm done with me myself I'm done with foil one but oh goodness three or four of my slaves aren't so I'll just keep that file around do take note that as soon as you do run this you run the risk of filling up your hard drive if your slave goes down that has also happened and I've also been called late at night luckily that was a Friday night that was much more fun so these little things need to be monitored and you can just break the replication so it's fairly easy you log in and you say delete slots or cancel slot I come to me with a command and it's actually pretty good idea to have that around because when you are trying to find that come on because everything is down and everybody's screaming it Google feels slow but that's that's the way of life isn't it okay so if we look in this we are gonna just pop in max replication slot so I'm just gonna make that - because I'm superstitious and I am going to quickly restart my dev DB so now it has got two replication slots living and I am going to open my database okay in this case I am adding two things I'm first creating the physical replication slot and secondly I am Telling my and there you can actually see X log position null not a good thing at this point it will not hold any files back until there is a value in there it will be ignored so if you create the slot you start a base backup and your system moves ahead it'll delete the files and you will lose them what you can do is you can start something like PG receive log on that slot it'll receive one file it'll mark up as you can stop it and at that point you have to continue with your replication or else your harddrive will run out so typically yeah if you've got a extremely high speed maybe heavy right load it can actually be quite tricky to to to get replication set up especially if you're moving the data over slow connections okay the other thing we need to do is in that little friendly file in I am just going to stop the replication for a second and delete it docker often are not even completed all I all yeah sorry I am slightly annoyed by this because I need root rights okay oh goodness okay what we're adding here is primary slot name and we give it the slot name so you can call this my backup server in London my backup server in New York and then you know which backup server it is that might also be handy okay so at this stage we're just going to quickly do a pseudo docker or not pseudo goodness docker restart dev slave DB and at this stage it will likely be set up I'm so I'm just gonna go select star from PG stat ish I can never remember the name of that one as its physical replication slots there we go thank you tab complete and there we are we've got one set up and we can see where it is at and if we were to create a bunch of databases or whatnot this will happen and it will replicate and that's more or less it there is a couple of other nuances but for the most part that covers the talk and I think I am fairly early it's again Y warmed people because there is not all that much the replication it is a surprisingly basic thing to do and yeah any questions yeah hello in the past we've tried setting up the replication of PT logical and we struggle to find documentation do you have any good the PG logical stuff is done by the second quadrant guys so that should be a good place to start they'll probably be the best reference on what version of best crease are you nine point five okay so it only really came into proper being in ten they've been kind of adding bits and pieces of it so if you want to do full logical replication then is probably going to be a better experience it was kind of back border to nine point five so I I'm fairly sure that will be part of your issue but it is a fairly new thing unfortunately I've not played with it yet I'm looking forward to playing with it at some point [Music] I've actually every time I've tried doing it I've found kind of multiple sets of slide shows and every time kind of bits from each one doesn't work it's really slightly annoying and that I actually never did this to give it as a talk it was actually just as a kind of a copy for me and then I ended up doing it as a talk at the post grace of the whole thing user group and it really it is frustrating because the documentation is kind of open-ended it doesn't tell you how to do it it just kind of says these all the massive amount of features and you don't really understand it so you kind of need something that just guides you through I'm happy to share this this'll get it up and running for you is unfortunately in docker so it's slightly annoying in that sense but at least it tells you what to put into the files and you can replicate this with docker on any machine so transferring that then from dr. environment to real environments Auden be that hard I think the biggest issue is getting your main PG post Greystoke on file that loves Annie TC to look at the right directory that's about the only catch different from Dhaka that's the you know in Dhaka Europe you're the config lives within the directory so you can have got this little self-contained blob as opposed to yeah but I mean that's that's about it so I'm happy to share that all this and it's I mean it's like four or five commands it is stupid it is silly its basic and it can save your life it has saved our lives I mean it wasn't Yakub how long ago was it that we recovered data two weeks three weeks and we had we'd save data we saved a client's ass who has ours we saved them from themselves let's disable all log in IP blocking and this is on Windows machines let's disable all antivirus yes that's good ideas until the cryptolocker comes and then the cryptolocker came and ate all the data and the data win went to day to heaven and luckily we had a nice little copy which is handy you know working with financial data on all that thank you very much more questions okay thank you guys yeah you you can likely catch the end of the other talks because I rushed through this maybe a couple of minutes left

Channel: PostgresConf South Africa

Views: 13,302

Rating: 4.6770186 out of 5

Keywords: pgza, pgza18, KobusWolvaardt

Id: 5BeC1aD4z8E

Length: 30min 45sec (1845 seconds)

Published: Sat Oct 13 2018