 Here we go. All right, so I should prep you. Who here has seen me speak before? Okay, the ones that didn't Just deal Okay, that's our buckle up. That's exactly right. Okay. This is kicking the donkey of post school replication I think we all know what donkey means Yes, I have Donald Trump up here. I make fun of politicians because they're all crooks I Will say this Bernie Sanders, I do not believe is a crook But there's also a reason he's not gonna win so Donald he took his home state. I think that was probably a given no matter what He's kind of got that whole New York thing go. I mean look at him. He looks like a New Yorker I Warned you Okay, making post-crisky all great again. We know his tagline everybody's seen him I am with command prompt ink We are the oldest post-crisky all company in existence that I know of there might be one in Japan or something But in terms of the Western world were it talk to me. Oh There we go. Come here Back up. What are you doing? Maybe I should be running a Mac Okay This talk is There we go. This talk is for adults are comfortable in their own skin to give you an idea. These are the comics. I enjoy If these comics are a problem with you or for you you really should leave I Don't mean that is a bad thing. I understand that there are people that can only tolerate vanilla ice cream and that's okay But I'm not that person That is all I'm gonna say you've gotten your warning if you complain afterward kiss my ass Who am I? I am at Linux hiker feel free to follow me on Twitter Feel free to Facebook friend me on Facebook if you want to be trolled That's the only purpose for Facebook. I'm on Facebook because my daughters are on Facebook I am not on Facebook because I want to be on no I take that back I'm also on Facebook to troll Bernie Sanders fans Because it's easy it is so easy You think it's easy to troll Hillary fans or Trump fans I mean it is because we all kind of get wrapped up in the ideology But Bernie fans are great because even when they're 60 they think like they're under 30 so I Am the lead consultant of command prompt Inc. I'm also director for software in the public interest That is the non-profit behind Debian postcrestql.org versus us Libre office in the United States and a whole slew of others and what we do is basically provide a Financial and legal umbrella for those projects Deb comp for example runs through software in the public interest so they can sign contracts take money things like that If you saw the opening I'm also a director and a founder of United States postcrestql Which is putting on this lovely conference? I am a major contributor to the project I don't contribute code that much anymore and even when I did contribute code It took a lot of work for me to make it anywhere near reasonable I'm a practitioner. I'm a consultant. I can code when I have to Specifically see I'm actually a component Python developer And I hail from Washington State if you do not know what that leaf is you are not living a proper life This is one of my Things I actually am a BSD fan. I don't have a problem with closed-source software But I do believe that if you are an open-source advocate and you are using things like Slack Anything that's proprietary that has an open-source alternative. I understand that for example There is no open-source alternative yet for Google Docs Great use Google Docs But if there is an open-source alternative you should be using it. Otherwise, you're not actually an open-source advocate. You're a leech You're not actually providing for the community. Now. That's a little harsh. I already gave you the disclaimer And actually this used to be specifically I used to have a logo right here It was a three-letter logo and it was orange and black and Then underneath it. It said something like the enterprise Postgres company Their current slogan is like Postgres rocks or something I don't do that anymore because they're actually a really good community member And they've kind of owned up and they've admitted we're not an open-source company and they're not they contribute to open source But they're a closed-source company and that's cool. I dig that that's fine At least you're you're being straight with people and you're making your money. That's all cool Proactive SLA this is the one thing that I'll sell from command prompt. I don't like doing sales I don't employ salespeople ever in fact I've had salespeople come to me and say hey, I want to sell for you and I said that's why you're not being hired It's not that salespeople don't have their purpose I know they do and I know that companies with sales people make more money than we do But I am far happier It's a remote DBA sys admin thing proactive response and the way we do the proactive responses we install a monitoring infrastructure that alerts us 24-7 so we can take care of you It come it includes four hours of DBA or sys admin. It's 24 7365 After the four hours you get a flat rate So you don't have emergency fees or after-hour fees or anything like that. You have a reliable cost structure to rely on And it's enterprise right wide and what that means is is when you're Ruby on Rails developers Screw everything up and you fire them because of it and then everything dies and you need help now We'll help you with that too now We might have to read the docs because we're saying enough not to do Ruby on Rails every day But we'll get it fixed and That's from 1350 a month. That's my one little sales pitch What are we talking about? We're talking about replication options the pros the cons tools to make your life easier What the hell were they thinking? Guaranteed not to be in order. I only say that because although my slides I think are in order I tend to be a little ad hoc I don't script this stuff. It just kind of comes out skips over the filter and hopes nobody cries And PG logical are you let using less than 9.1? upgrade Period it's end of life this fall. I can already see people going. What's wrong with 9.1 well one It's slow as hell compared to 9.2 9.3 9.4 9.5. You see where I'm going with this you're running Windows XP upgrade 9.0 is unsupported 9.1 is you all this fall Even 9.2 is miles ahead and this is true performance wise 9.3 is where we got the management break We're all of a sudden other things that would have been nice to have in terms of monitoring like PG stat replication things like that Really came about but 9.2 is Extremely performant compared to 9.1 and 9.0. Are you running anything but Lennox? 2.6 and 3.8 plus only 2.6 is a great kernel, but it is also end of life Problem with three series is 3.2 to 3.8 is broken and I don't mean broken like oh we get weird messages in the logs I mean like 40% IO wait Zero changes to anything upgrade to 3.9 12% IO wait And the reading I got some notters here The reason is is there's a bug in PD flush because as awesome as Lennox is it's not tested that well That causes this problem and it's fixed in 3.9 Please upgrade if you say I can only I only run what comes from the distribution Great, but when you call me and say posters too slow, I'm gonna say no you're not listening Sorry free BS to users. I do love the devil. He just doesn't love me. What do I mean by that? I've been doing this Unix Lennox thing for 20 25 years I Didn't start with Lennox because when I started Lennox didn't start But I've done Xenix. I've done sco. I've done HP UX I did BSD light which predates free BSD And I just don't care for free BSD. It's not a technical thing. I know it technically rocks It's a great piece of software Are you using sloney? I am very sorry for you. I have customers that have to use sloney It's very unfortunate. It's not that sloney is bad. It's not But as I've said many times before the thing about sloney is a bunch of engineers specifically yawn because I think it's bipolar Sat in a room and we said let's solve a problem. The problem is replication But instead he said let's paint the walls black and then said which shade So every feature you can think of sloney can do if you have something that can't be solved within the inherent native Postgresql replication Sloney can probably do it which makes it awesome But when you have a million knobs to solve one problem, it's very hard to get them all tuned to the right volume It is trigger-based. It's good for obscure complicated setups an example of a great way time to use sloney And I those who took my backup training know this is I have a customer that is on Solaris spark on on postgres 9 and we are trying to upgrade them to 9 5 on Linux Intel You can't use binary replication for that. It must be logical replication. That would be a good place for sloney Unfortunately for them. Sloney is too high-impact of performance. And so we are trying to come up with something else master slave Postgresql supports one master to many slaves If you think you need multi master, you likely don't understand the problem My favorite phone call is from somebody who one says we are an enterprise Startup and we're on AWS. Well first if you're on an enterprise if you're an enterprise, you're not on AWS Now I know there are enterprises that use AWS and that's fine Netflix would be an example But you are enterprises are not on AWS They have long learned that it's bare metal where you need to be and then they say we need multi master Awesome, maybe I can give him to pay me to write it And then I say well why they said performance That is not why you use multi master. It is not faster ever But it does allow you to solve a lot of problems if you can get it right we don't have multi master that just works I don't know if in it and yeah, I know is any two queue people in here okay, good, so There is this idea of multi master that works. It's called bidirectional replications BDR It's actually very very cool if you're willing to pay for second quadrant to set it up for you and then Pay them to support it Now that's a great place to be I Personally like that position because I get to look at recurring revenue But I believe the goal is to create software that I only need to support if the customer doesn't want to I Don't want to get paid to support software because they can't figure it out So that would be my little gripe about BDR, but I do know it works I've played with it It's fun, and I'm hoping that they are able to get it into core the goal is to get it into core But right now it's not even looking like it might get into 97 at this point, so we'll see what happens and Scaling is a small percentage of the problem When it comes especially when it comes to multi master you have all kinds of other issues you need to think about when you're dealing with replication Here's an example of what else we can do we can do master slave and we can do master slave to slaves So we do have cascading replication if it's binary. We do not with logical Well, sloney can do it, but P. Geological can't Replication questions do we need to do sync or async binary or logical Streaming or shipping and they streaming or shipping is a fine line, but I'll explain it Async or sync async means the slave may lag behind the master without affecting the performance of the master Sync means the slave will not lag behind the master, but may affect performance of the master. Let me rephrase Will affect the performance of the master not may and this is why You are writing instead of twice once the log once the page You are writing once to log then over the network, which is likely slower than your drives even though you got gigabit To the slave which then has to validate that it can commit and then talk back with the master to allow it to commit and It's it will affect performance However, it guarantees data consistency between your masters and your slaves, and there is good reason for it when you need it async The pros it's fast It's easy to set up works well over slower networks as you can see with this graphic. This is perfectly acceptable with async Even if you wanted to have the master all the way and over one of those backwards countries like England or something Do I have any Europeans in here? I make funny Europeans because I make a lot of money investing in the euro. So it's kind of an up-and-down scenario The cons slaves may be further behind Here's the deal if you're in a data center if you're directly connected to your slaves I actually recommend even if you need sync to evaluate that two more times Because when you're directly connected async is so fast That you probably don't need sync and then you don't get the performance hit But if you are saying okay look in case San Diego or actually probably San Diego won't implode It's San Francisco Implodes because they realize all the political correctness is eating them alive They ship data over to New York Because they realize New York has got it going on and they don't really give a damn if you say something improper Because they live it and they love it and they embrace it because we're all different and we enjoy it well, eventually this is gonna die and But you won't take a performance hit and you'll fail over to the disaster recovery I think this actually app proposed this was in San Francisco and we said that we had a disaster and we want to recover So we're gonna go to New York Fail over to here. You're all good to go, but your app doesn't have a problem You're not gonna have a scenario where If you guys watch the compose governor Keynote yesterday where he showed the sync would wait. You're not gonna have that issue with this setup The cons are again slaves may be further behind and there is a potential for data loss Now you won't lose consistency You're not gonna end up in a situation where the master died and you fail over the slave and the slave doesn't come upright or your Foreign keys aren't in the right. You know, you don't have foreign keys that are matching none of that happens But you might lose that one transaction or even five transactions that were in process Before you fail over that is possible because you're running a sync sync pros zero data loss Great for load balancing for those of you. Now, I am not Suggest that's not good coloring. It looks much better on my screen. This is actually green and This is actually brown and what we're talking about here is Herbal technology We'll leave it at that The green Specifically with red flakes you want little red flakes in it. That's the good stuff if you're desperate poor or Well desperate You can use this stuff, but it's kind of like Dunkin Donuts versus freshly brewed cold press It's not gonna give you the coffee. It's not gonna give you the right the same thing now If anyone in here has an actual problem with the fact that I'm talking about cannabis You really need to read up on the facts Okay, you are more likely to cause a whole heck of a lot of damage to yourself and others doing what we all do at these Conferences, which is drink alcohol And I also didn't touch this stuff. I'll I have touched this stuff recently But I had never touched this stuff not once in my life before it became legal Which a lot of people like you're right. No, but it's true but I have sense because well Life is hard and this makes it easier And I'm gonna say something. I don't know if you'll believe me, but it is true. I use it for pain I Have back issues and knee issues and all kinds of other stuff But zero data loss great for load balancing absolutely You want to bounce PG pool to in front of synchronous cluster and do read load balancing and you want to guarantee that the Slaves are at the exact same point as your master That's the way to go Now I usually set up PG pool on an async scenario and I still get the same benefit But that's because we're not necessarily dealing with real-time data The cons and this is my love this one if the slave goes down. It's an outage on the master I'm gonna repeat that if you are running sync replication if the slave goes down It's an outage on your master Okay, just think real hard about that writes will be slower It's highly subject to network speed But the master outage can be mitigated with this you could script this out Or even use set global or set local. I don't know what you remember what it is The newer versions of Postgres you don't actually have to edit the file. You can do it from an SQL command I can't remember it's like set local or set global or something and but you can remove This the name of the slave from synchronous standby names and then reload Yes Alter system set. Okay, alter system set You can add the synchronous standby names remove the name and then reload and Your master will come back up and obviously if you have a proper monitoring infrastructure This could actually do it for you. You don't actually have to physically do it You have it all nice and scripted right we script everything you do right or not hand typing this stuff and Hopefully you document it. I know that's bull, but hopefully you document it I know it's bull because that's how I make my money. Well, yeah, we have this DBA Who was a rails developer? well and We had to Well, he had to go on sabbatical never to come back and We don't know what he was doing and then we get to go in there and go. Yeah, neither do we and so we have to document it All and figure it all out. Do I have any rails and developers in here that I'm hurting the feelings of Okay, good Binary or logical. This is binary. They're identical. They're like clones Are they cute? I actually I actually I typed in Clones into Google images and I had safe search turned on so I only got this And it was Yeah, like I said, this is not planned this stuff just kind of comes out, okay Um, but and then of course my daughter's walked by and my daughter's like So this is binary replication with Postgres QL you have Identical copies of the database down. I mean there are identical clusters If you were to pick one up with a tar ball or something and ship it over to another machine and drop it off and start it up It's still identical assuming that it's you know, little Indian and little Indian versus trying to cross the architectural boundaries This is logical We got Darth Vader You think well logical is not bad. No logical is not bad But Darth Vader's a badass and I just kind of like it Because he turns good in the end, right? You know, but and then the reason I have him cut off here is that you don't have to have all of Darth Vader Which is proven because he was maybe 20% human by the time it was over But yeah, you can have here's your master But your slave is only half of you so you can ship independent objects You can have five tables going off this direction ten tables going off that direction It's very useful for things like we have a reporting server and reporting is only against sales But we don't want sales anywhere near HR Okay, so you can have your logical data over on the HR side now Obviously we can use grant revoke and all those nice things But nobody does that anymore because I don't know why it's hard to type grant It's hard to keep track of permissions. We'd much rather just let the super user access it This is your data without f-sync This has absolutely nothing to do with this talk except that this is your data without f-sync If you're run is anyone here now I probably won't get you to raise your hand, but I might be able to tell by the look on your face Is there anybody here running f-sync off? Because if so I'm gonna find out who you are. I'm gonna call your boss. I'm gonna get you fired Do not run with f-sync off ever except on a bulk load that is the only time ever bulk load Don't do it any there That's true. He's right. He's right. So you're rolling the dice no matter. Oh, no, there's a kid Is the young gentleman staying He is oh, no Yeah, I need to go back to my disclaimer Okay So there's Crap okay, so there's actually a story behind this not last year, but the year before some of you may have seen this talk Okay Who here knows who Bruce mom Jin is? Okay, Bruce mom Jin Had his daughter who is lovely and smart and sweet Introduce me like she got him so this is Josh Drake and you know He does all this stuff and it's really important blah blah blah, and I was like oh god really But then she stayed Totally screwed up my whole talk. Yeah, I didn't there was one point. I didn't even realize she was still there I was like you're still here shit. Oh wait. I So Thank you ma'am for having him play with his iPhone out in the hallway because the whole talk would take on a new level of I'm not sure what I'm doing But yes, this is your data without f-sync Just don't do it. It's bad and I agree with you in the bag. It's it's a it's a bad idea Let's talk about configuring the master for replication. This is binary replication some of this has to do with logical But it's primarily binary. We're gonna deal with some listen addresses share preload libraries max worker processes Yes, you have to configure all this crap Wall level archive mode archive command some of these are optional Max wall senders wall keep segments do not ever use this stupid parameter Okay, it's it's I'll get to it max replication slots track commits timestamp and synchronous standby names Listen addresses most of you should does anyone here not know what this means? Okay, good. So basically this tells Postgres. Where do I want to actually? Listen in terms of network. You can say splat which is I'd never actually heard it called splat before I call it a star After watching compose. I want to call it a snowflake Listen addresses you can use a host name local host you can also use a combination thereof Okay, so listen on local host and dot five but not dot ten or whatever, okay? Shared preload libraries you do not need this for binary, but you do need it for logical only need for logical replication P. Geological it's required on master and slaves on a boon to or Debian You would the shared preload libraries would be this assuming you have P. Geological installed This won't work if you don't have it installed I mean that would seem to be obvious, but if you put you'd be surprised how many people would say well I added the line and restart it instead. It can't be found. Well, did you install it? Oh, I thought Postgres would do that for me No, man. This isn't a Mac So max worker processes it's used for binary and logical replication must be at least equal between the master and slaves Or it won't start master will start the slave wall and a slave will tell you that's the one thing We're getting better at we're not perfect yet, but we are getting better at saying yo doofus This is why it's not starting up versus error failed to start Which is a good thing Increase on master based on number of slaves So if you have two slaves at ten you might want to have the master at 20 Otherwise, you know, you're gonna overload them and then you're gonna wonder why things broke You're gonna sit now you need to sit come on you can do it. No sit right up here right up here. Come on You can be in front. This is Samir Singapore right? That's how international the United States are we're conquering through invitation now wall level Are the South Africans in here? There's one you make are you are you still seeing three? Down to two okay All right wall level set the amount and type of information stored in the wall. There's multiple levels. There's minimal archive hot standby and Logical and they are ink they increment in the amount of information. They're providing so therefore for logical or binary Just set it to logical Logical will work with hot standby Okay, so you you don't have to if you can set it to hot standby But the problem is is that once you want to deploy like hot standby and PG logical at the same time It won't work without a restart But if you just set it to logical your hot standby is a work and then later in life when you deploy the logical replication That parts good and you don't even have to have an outage archive mode set to on the reason is this You can leave it on and not use it But if you need it, it's a restart But if you leave it on you don't have to restart because archive command is just a reload So you can set archive command as something like been true if you look at me and say why I'm running Windows I'm gonna say sucks to be you you know use whatever command does nothing Any Windows users here Windows Postgres users So that's okay. I'm not gonna pick on you. I'll pick on Windows not you Um, the great thing about Windows is that it reminds people why they should be running Linux I Don't even I have a brand new beautiful sexy laptop here They only got a week and a half ago and Linux got to the point where I literally all I had to do was install Ubuntu upgrade the kernel And that's it and I don't mean upgrade the kernel like I went through and set all the options and compile But I stopped doing that about 15 years ago. I mean I hit app get upgrade Boom, and it just works and we're talking 3k screen or 4k screen display port SSD 12 giga RAM for a whole bit even with the pass off for the 940 M in video It just works and that's something to be said with Linux because it didn't used to be that way But again archive command. This is for point-in-time recovery. This is not replication Specifically it however can be used for replication and there are good reasons to use it for replication If you have weird issues like intermittent link drop-edge or link or for example, let's say you have Offices that are globally different one in New York and one in Singapore. The link is probably not that great Maybe the busiest times is six to six Okay, you can have the archive command Just return false from six to six and it will keep all those locks. And yes, I know about replication sauce. We'll get there and then That's 615 when busy times over you reload with the new archive command. It'll all it'll ship everything down To Singapore Max wall senders she's determined how many wall processes are created for sending walls to clients Wall is wall archive log at your PG X log Okay Increase for PG base backup streaming replication hot standby and logical replication in short increase it If you're doing any kind of setup that involves more than one server increase it It's overhead is very very low. Just crank it up 10 15 20 thousands probably a bit much but Start with setting equal to max background processes Wall keeps segments. I know this is unflattering for her But we'll get to that how many segments to keep in case a standby goes offline for a period of time This is why this is a stupid setting It's a stupid setting because Anyone who had been using this feature had already long scripted a way to not have that problem This was almost like the the hackers were like, you know what? We have to implement something. We don't even want to implement it, right? So we'll just give someone a parameter and we'll just keep them all and what this means is is If a log cannot be shipped to a destination in other words, it's not returning success Postgres will keep x amount of logs up to wall keep settings Hopefully that you will connect before it overruns Because once it overruns you have to run another base backup Okay, this is a this the only reason this is even in here is because I know not all of you are running nine four or nine five That is the only reason I even talk about this Use replication slots or archiving instead archiving because for example with and don't use this software because it's deprecated But with pitter tools What would happen is if it couldn't ship the log It would queue the log and wait nicely just for the slave that failed Not for everybody not globally. We just just like replication slots in a lot of ways And replication slots will get to but it's a very similar scenario This graphic is not really contextual But I kind of liked it so And I you know I had to give her credit I She when I've watched the Democratic debates, and I've watched the Republican debates And I and it used to be that I would watch the Republican debates, but now I really prefer to watch the Democrat debates She looks like an elegant sharp intelligent brilliant woman Powerful than what she's trying to deliver. Is she corrupt? Well, yeah, she's a politician. That's the way it works And then you get Bernie Bernie looks like the guy that's gonna throw lemons at you from a porch Somewhere in the south when it's a hundred and seventy two degrees Because you're smiling while you ride your bike You know, he just does he looks angry Max replication slots. It's the max number of slots to create to keep logs for disconnected clients A lot of people say oh one or two is probably fine Well, one or two is probably fine if you only need one or two. I'd actually say it to five six seven It's very low impact, but you never know When out of nowhere your developers gonna say I need a base backup. It's up to date as soon as possible You've got this setup. You can just do that and Then it'll catch up at the very end and then you can drop the slot and let them go off and do what they want without really affecting production Um Use these not wall keep segments if you're running nine four nine five and you're still using wall keep segments time to review Use replication slots instead Here's a real simple example select PG create physical replication slots briber zero Select star from PG replication slots boom. It's not active Because we're not connected to it from a slate Okay track commit time stamp It is useful for conflict resolution Instead just don't write to a scribe for table So where this actually comes from is that PG logical is based off the BDR code and Multimaster really means Solving conflicts. That's really what we're dealing with when we're dealing with multi master. It's not about scaling It's not about high availability. It's about making your life harder trying to figure out which commit was the most appropriate commit to be stored Wow track commit timestamp can help you do that You don't really need that with PG logical because it's master slave But you could up and end up in the scenario since logical replication isn't read only Someone could write to the replicated slave table and then you have to figure stuff out Now no one's asked a question yet. So either I am very thorough. You're terrified or I'm missing something Anybody got any questions because we're moving really fast. I think Yeah, really fast Yes No, you have to install BDR There's also others, but they're pearl and trigger based and there's crazy and then they're stupid. I'll stick with crazy Yes That's different that's Horizontal partitioning and yes, it can you can make it like multi master But that's really it worked on a totally different beast at that point which it's cool They actually post was XL just released RC one for nine five It's a very neat product. It's binary or it's binary and source code compatible In terms of being a fork Completely open source post risk you all so if you had like Drupal you can just connect to it They does horizontal partitioning of post skill nodes The only downside to it is I only know three people that have ever used it. So Your mileage may vary kind of like a Volkswagen diesel. Oh Come on Someone has had to have read the news on that Yes, oh, yeah, there's all kinds of things and they got different Conflict resolutions that you can use a new or what is it last commit or newest commit or whatever and then other Variances that you can use but it is not a simple piece of software. You will pay someone eventually and Hopefully, it'll be me For me to tell you to get off B of the R And no disrespect to the second quadrant really they have taken on a monumental Technological feet you need to understand that in the database world there is no harder problem than multi master none So when I mock them it is out of love because I wouldn't even bother to try even to get even if everyone in this room Got their companies to sponsor us to do it. We would do it on the premise that it's going to fail But we would happily do it, but it's not going to succeed because Multimaster by default does not work Yes, you will write to all your nodes But the moment you have to do conflict resolution It's not working. You are fixing the software. That's what that means So I prefer especially now with nine five with how far we scale up and how big machines are getting now Let's just you know add another four SSDs put that in a raid 10 add another 512 gig of RAM We'll make sure you can pull it off use logical replication to push off your your reads and use load balancing You know don't worry about multi master. It's a very obscure problem to be fixed Anybody else someone else back there? Yes Now one thing about multi master especially a BDR because it's asynchronous The really cool thing that it can do is the salesman problem who here knows what the salesman problem is Okay salesman problem is this you've got a guy who's on the road and you've got an app on a laptop now A lot of this problem isn't in existence for a lot of us because we all have 4g or Marriott Wi-Fi when it works, you know that kind of thing or we can run down to Starbucks We have a way to get an internet connection somewhere But especially in you know the olden days like 2005 That wasn't the case and you'd have a traveling salesman with the exact same application on their laptop that they run at their desktop And they would be updating their quotes and their estimates and all this stuff But how do they get it back into the master server? Well, BDR is a solution to that they're running BDR locally and the moment they go click into the dock Everything sinks back up Until there's a conflict But that is a very good cause for multi master now a lot of people like Oracle does it Oracle's got many billions of dollars to waste on yachts We don't and anyone who's actually used rack will tell you it also does not work And that basically that comes all the way back to what I was saying about you're not understanding the problem If you're running multi master, there are a few cases where it is Appropriate, but it never actually works All right Again instead just don't write to a subscriber table Synchronous standby names it's the name of your synchronous stand-by's just use the IP address. It's the easiest I actually don't like this I would prefer to use the names because then you can just change DNS instead of worry about IP addresses But All the docs say use the IP address. So use the IP address Remember in case of slave outage synchronous replication is an outage on the master to Yeah, that's not happening and I'm JD. So we're just gonna deal She's telling me I only have five minutes for everyone. It's like what the hell is he talking about? But let's see how close we can get configuration slave hot standby max standby archive delay Hot standby set to on for hot standby not needed for logical replication hot standby means you can read from the slave You can report against it. You can take backups off of it. You don't have to deal with your master Okay Max standby archive delay. How long do I wait before I cancel these queries? Basically if you have a long running query on your slave There's a limit that you can set to make those cancel so that they don't cause a bloat on the master Setting negative one negative one means the standby will wait indefinitely only applies if you're using archiving not streaming Same thing, but this is for streaming Okay Note about delay setting see He's not a happy man Do we really want a grumpy old man in the office? We had that it was Reagan. It was horrible. I Actually read an article from about from his staff saying they were so frustrated with him You know a lot of people like to a lot especially older people like oh Reagan was a great president No, no, no Reagan had a great staff Reagan was a great napper Not rapper napper The setting is not the same as the maximum length of a time a query can run before Cancellation rather it is the maximum total time allowed to apply wall data Once it has been received from the primary server plus if one query has resulted in significant delay Subsequent conflicting queries will have much less grace time until the standby server has caught up again I'm just gonna let you chew on that in your head. We don't have time to explain it But that's why I say read the fine manual I almost didn't say fine Wall receiver status interval. I hope you clap that love Tell the stand by how often to update its master on the current status The standby needs to tell the master. This is where I am. This is where I am So the master can continue to maintain itself If set to high PG stat replication will be out of date and you may overrun replication timeout on the master Hot standby feedback allows the standby including cascading to tell the master what queries are being run So that a master doesn't remove rows during cleanup and cause query cancellation Wouldn't that suck you're in the middle of a report You're doing stuff and then all of a sudden the master removes half the rows that you're reporting on and it tells the slave sorry We don't have that data anymore And then you got to call your boss and say, you know that four-hour report that takes to run I'm going home. See you tomorrow click Can cause bloat also humans shed about six hundred thousand particles of skin every hour You are not the first person to sit in that chair wall Wall received he was like, oh my lord Wall receiver timeout terminate replication connections that are inactive longer than the specified number of milliseconds If your slaves are just hanging out doing nothing Waiting for something it'll terminate those connections. They will reconnect when they need to while retrieved retry interval Keep trying how often How do I make a base backup because that's actually what you're here for you don't care about all the other stuff I told you configure PG logical This is actually easier than it looks if you do it right if you do it wrong. You got her start all over It sucks fail over fail back get help and donate Make a base backup. You got PG base backup and you got our sink. There's a lot of other tools I could write an entire train on just how to do this Okay, but we're not gonna cover that PG base backup used with the dash x option that means stream. It's faster It streams the wall archives to you as you're taking the base backup is not particularly useful with large databases That is because it is single threaded. You got a terabyte database. You're waiting for a terabyte copy over one connection Takes a while okay It's particularly useful with smaller database the inverse is also true if you have a recently small database There is no easier way to get a base backup. Just use this boom You got your you've got your database ready to go Well, it tends on the hardware and the network speed But I would say we think 100 gig Yeah, but a hundred hundred gig Can be used to make can be used to take a tar based backup You can tell it use tar instead of plain text, but there's some limitations to that read the manual I know you won't that's okay. Call me. I'll charge you for it Our sink within the math this is actually how you do it with our sink the actual steps involved I don't share my slides so pay attention. I Might share my slides. It depends on my mood Within the master create a replication slot named x or use PG receive x-log which will create the replication slot for you Within the master execute PG start backup. It takes arguments specifically It takes a name of the base backup that you're taking and it asks whether or not you want a fast checkpoint or a slow checkpoint Unless you are loaded up on your server use fast If you don't use fast you have to wait for checkpoint timeout Divided by checkpoint completion target, which could be anywhere from nothing to an hour. Okay? With archive PG data using our sink make sure the target is empty because you're not going to use the check Some picture because it's very slow Within the mat within the master execute PG stop backup. It does not take arguments Creator recovery.com within the slave. I also am not I'm not mentioning here Oh, right because we're using a reputation slot create a recovery.com. This is what your recovery.com looks like Our replication slot name. That's what we created Our primary connection info the master of the day the master database And we're going to go into standby mode. We're not actually failing over using it for develop Start postgres on the slide. That's it Now here's a tip our sink is not any faster than PG base backup unless you multi-thread it Yes, there are multi-threaded scripts out there What it basically does is you take you tell our sink give me the list of what you're going to copy and then you cut The list up and pass it to multiple our sync commands. It's beautiful. It's quick It's very Linuxy because you're using a lot of scripts to do what a program already should on the master Oh, this is PG logical on the master assuming you have PG logical installed And you've created the extension on the logical database. You're going to replicate and You have put PG logical so in the preload shared library thing Can't remember up top my head because I'm moving too fast Select PG logical create node node name. I Love DSN data source name. This is your connection the This is who I am Okay, this is the actual master IP The port and the DB name that we are going to replicate Then we create a table for testing Then we tell PG logical. We are replicating everything Set at all tables. There's also the ability to add just one or two tables or whatever in the public schema so I created logic the logical The logical test table in the public schema now it is ready to replicate. It's not replicating but it's ready to replicate on The slave PG logical create node. I am replica zero. I am the next slave This is my host This is the slave IP port and the name of The database that is being replicated Then we subscribe we say PG logical create subscription Subscription name replica zero relations provider Notice that's the master IP Specifically the master IP port five four three two DB name logical Synchronized structure true that means it will actually move over your table structure your DDL Synchronized data true. These are actually by default true I just put them in here for explicit purposes, which means as soon as this Subscription is created push the data over all of it And that's it For PG logical now here's the catch with PG logical. It's not done. It doesn't support cascading replication It's not very well documented, but it does work quickly and effectively for master slave and partial replication Okay We do know as you know we are out of time and I just love this graphic I just typed in ugly doubt into Google images Get help IRC I know a lot of you are like what's IRC it's slack, but works mailing list and it's a standard and it's not proprietary and it's not closed source and it's not Yelling at you all the time mailing list PG SQL general PG SQL hackers User groups if you're not part of a user group be part of a user group if you can't be part of a user group Start a user group if you need help starting to use a group contact me. I will not bite I will actually be helpful or Call me don't call me about user groups. That will piss me off You can call me to have me spend money or have you spend money. Sorry and donate and My time is up, but any questions? Thank you very much