 Is that mostly readable to people in the back? I don't want to zoom it in too much further, otherwise it's going to make the output of stuff start getting unreadable. What they need is new projectors that don't just do 1024 by 768. I'm actually going to be doing the whole thing inside the VM itself. So if you start up Chrome inside the VM, it should go right to the slides. So you'll have the slides there available for yourself to copy and paste commands and stuff out of everything. Anybody that just walked in, if you didn't set up the VM beforehand, I have USB sticks up here to have the virtual box and the virtual box images on there. So I'll feel free to come up and grab one. And I'll see how people are progressed and set up about 1030 or so if people still need more time. That's fine. So this is hopefully the worst part of the presentation is just getting the virtual box image set up. Is anybody having problems that they need help with? Well then, yeah. So if anybody is confused, I really do mean or at the top there. The zip file is the same thing as the unzipped file. I just did that in case somebody can't uncompress the image. But it takes about 10 to 15 minutes to copy that image off the USB drive. So it only takes about a minute or so for the zipped one. So either or is fine. Oh, really? Wi-Fi, it'll probably take you 20 minutes. Welcome, everybody, for the first day of Scale 16x. I don't know if anybody has a memory about this. You're on for a little track with Joel Fernandez. And he's going to be speaking to you about the BPFD, which is a tracing tool. And I don't know if you've been to any of Brett and Greg's talks that scaled them over the last few years. But this is one of the tools that's quite useful for getting into the internals and doing profiling of things in the kernel. So one of the things that Joel's going to show us is a demo of running this on an embedded target and how you can use this tool to do tracing without having to install much software or add much of a footprint to your target. For anybody that just came in, if you didn't download the VM before you came, there's USB sticks up here. You can have a virtual box and the VM image on there and these are the instructions to hopefully get you set up. If you have any questions, please just raise your hand and come around and try to help you. For the Wi-Fi, there's public scale fast and public scale slow, I think. I couldn't get fast to work, so I tried slow. Maybe fast is working now. And the password is all lowercase, Pasadena. OK, OK. That may be the difference then there. Postgres, it's down there. I tried to fit it in there in the bottom of the slide. It's Postgres, all lowercase. Again, for anybody that just came in, if you didn't download the VM image before you came and got set up, I've got a USB sticks up here. Feel free to come up and grab one. And if you have any questions about getting virtual box set up, please raise your hand. We'll come around and help you. Training password? That's Postgres, P-O-S-T-G-R-E-S, yep. Again, anybody that just came in, if you didn't download the VM and have things set up, I have the USB keys up here. I have virtual box and the virtual box image on. So feel free to come up and grab one. We'll see how some people are still working on trying to get it working. We'll see how people are at 10.30. If people still need a little more time, we'll try to give it to them. Hopefully we'll get going after that. If you have any questions in the meantime, please feel free to ask. So apparently the internet's not working very well, so we're going to test people's ability to use virtual box and snapshots in a little while, most likely. Because I don't think 30, 40 people trying to download Postgres from the MB repo in here right now is going to work very well. One person up here has been trying to do it for about half an hour. So anybody that just came in, if you don't have the virtual machine set up, please feel free to come up and grab a USB stick and try to get a virtual box up and running. If you have any questions, please raise your hand. We'll come around and try to help you. I think the difference between the fast and the slow is what somebody pointed out. It's whether you can do a 5 gigahertz Wi-Fi or not. So if you're not seeing the fast network, it's probably just because your laptop doesn't support it. And apparently the slow network is very slow. It's not much better. OK. The Postgres package is not even anywhere. It's near that big. Yeah. Give another 10 minutes for anybody that just came in. If you don't have the virtual machine set up, please feel free to come up and grab a USB stick and try to get it set up. If you have any questions, please raise your hand. So I'm going to come around and help. They're right up here. Anybody that just came in, make it start in about 10 minutes or so, so I appreciate your patience. If you don't have the virtual, the VM image set up, I've got some USB sticks up here. Feel free to come up and grab one. And try to get things set up. If you have any questions, please raise your hand. Thanks. Is anybody else having any issues with the VM? So hopefully get started in another five minutes or so unless people are still having any issues that we can't figure out what the problem is with the VM. Thanks for your patience. I'll pass the microphone around if anybody wants to have a question. You said that this works around some of the issues that you have often tracing remote targets, like not having debug symbols, getting the right kernel sources, and things like that. How do you actually do that? How do you actually solve those problems, get the kernel sources synchronized on the host and the remote, do the debug symbols? Anyways, getting the error when you start to start with the VM about USB 2.0 not being supported. Make sure you have the extensions pack installed. That's what adds the USB 2.0 support. But that also adds being able to copy and paste back the debug symbols from the kernel that will build on your machine later. You don't need the USB functionality, but depending on how comfortable you are doing things, you may want to be able to copy and paste between your host system and the VM. And that's also what that enables. It seems like this has applicability beyond embedded. So I was curious if you had any intentions for authentication, authorization, or otherwise securing the connection between the client and the host. I was just about to get started, but somebody else got started. Launched something against my host, my Wi-Fi connection drops, or something like that. Is my BPF program going to continue running on that host? Or does BPFD handle that failure mode already? Is there anybody still working on trying to get the VM started up? OK. Is there anybody still working on trying to get it that didn't have the CPU compatibility issue? That's the one thing I haven't been able to figure out. Everybody else is ready to go? Welcome to Postgres Administration Training. My name's Keith Fisk. I am a senior database engineer with Crunchy Data. I've been working on, lately I've been working on extensions and stuff for Postgres. One of the more popular ones is PG Partman. I've been working on some other tools called PG Monitor, Extractor, and Mimeo, which is a logical replication tool. Not quite as relevant anymore, but it was fun at the time. The slides are available online, down there at the URL, down there at the bottom. But if you're in the VM image and you start up Chrome, it should go straight to these exact slides that I'm going through. So I'm actually in the VM image, doing the full training session. Some way of monitoring multiple embedded devices? Or would it be just now? So I work for Crunchy Data Solutions. We're an enterprise Postgres Squales support company. We provide common criteria certified Postgres packages. So if you're working in government institutions and are trying to get Postgres working in there, we'd be happy to talk to you about that. Any questions? We're also actively hiring. DVAs, of course, system engineers, and especially anybody that likes very much in the container environment, we're definitely looking to talk to you. I think the light controls are back there. I'm not sure how good or bad they are. So just so you know, throughout these slides as well, if you have them up yourself, if you see anything that's blue, anything that's blue like this, those are actually active links, so you can feel free to click on those. Those should open up whatever pages you need to look at. So all or nothing? So is anybody here that's completely brand new to Postgres and hasn't used it very much at all? OK, great. That's what this session is for. This is like somebody asked about starting up Postgres, the service and everything. That's part of this session. And as the description says, this is setting up Postgres and Scratch on CentOS in this case. So what Postgres QL is, obviously it's a relational database management system. It's actually much older than people realize it is. It actually got started back in 1986. And the first open source version was in 1996. It's a BSD type license, which basically means you can do whatever you want with it. You can go make a product out of it, hack it however you want, and you don't have to do any attribution other than include that license along with it. So it's a great development database. It follows there's the SQL standard very, very closely. And one of the other great things about it that I've done, like I said in the intro, is writing third party plugins and support for Postgres is fantastic. You can write it in almost any language you can think of. You can write add-ons for Postgres in any of these languages listed here plus others and extensions. And also in 9.3 added something that I found really useful, it's called background workers, which means instead of having to run things in a separate chron instances, you can actually write an extension for Postgres and it'll run whatever your application is as a separate process inside Postgres itself. So I'll schedule orders and all that kind of stuff. You can actually program within Postgres itself. It's really nice. And also the community around Postgres is great. That's one of the reasons they have a track here at scale, mailing lists, there's active IRC and Slack channels, and conferences like this. Any questions about Postgres SQL in general? Anybody has? So the training of VM. Hopefully everybody is mostly open. Oh, yes. That's arguable. Yeah, it's not something I'm happy to say. I'm not happy to say that. But if you actually go look at this. Yes, yeah, that they thought Postgres was more popular than MySQL. Yeah, that's actually really good to hear. But if you actually go look at the statistics of database use and all that kind of stuff, Oracle and SQL server are still top of that list. And as far as open source, MySQL is still pretty high up there. But if you watch those charts over time, Postgres SQL is gaining fast on that. So that's a really good thing. That's a whole talk in itself. Yeah, I'd be happy to talk to you about that more outside of here. And me and a bunch of other people, we have a Postgres booth here at the vendor section. We'd be happy to talk to you about that kind of stuff there. Yeah, but there are literal whole conference talks about those one versus the other and that kind of stuff. Yeah. Any other questions? All right, great. So hopefully everybody has the VM up and running. If you haven't logged in yet, there's only one user on there, it's a training user. That training user does have pseudo privileges. So you should be able to do everything we're doing here as that training user. If you need to get to the terminal, I did make a shortcut here on the desktop for it. But it's also up here. And the first time you go to it, it'll show up here in your favorites. But other than that, if you need to get to the terminal, that's the path for where that is. Hopefully the internet should be working inside of it. If it's working on your system, it should be working in the VM. Internet should mostly be working. It may not work for the way we need it to work, but hopefully we'll have a way around that. If you need to copy and paste between the VM and the host, it should work if you have the extensions back installed for VirtualBox. If not, let me know. I can try to figure something out. For editing, we're going to be doing some editing files and all that kind of stuff. Vim and Nano should be on the VM for editing on the command line. That's not something you're really comfortable with. GE-edit is more like a notepad program. But you will need root privileges on whatever you're editing. So if you would rather use a GUI file editor, just open the terminal and type sudo gedit and ampersand. And that should open up an equivalent of a notepad terminal, notepad screen for you that should let you edit any of the files that we'll be doing. Otherwise, I'm going to be doing all my editing in Vim. And this is VirtualBox. So if you're scared about doing something and breaking it, you can go back out to the VirtualBox control panel here. I do have, you can take snapshots in VirtualBox. And with the machine running, too, they should work like that. And it'll save the state of your machine. And you can do what you want. And if it didn't work, well, you'll state back and hopefully that works. So this is hopefully how we may have to get around the internet if your internet not working. So we may actually be doing this anyway. Does anybody have any? Everybody good with the VM setup at this point? All right, taking that as a yes. So this is CentOS, obviously. You may be running different operating systems in your environment. The instructions for doing so, for whatever operating system you're on, they should be at the postgresql.org website. For doing it for CentOS and Red Hat systems, it's obviously the Yum repo. So inside, we can give this a try and see how the internet is going at this point. But inside your VM, so if you, yeah, I can get the website to load at this point. So it's not looking good for downloading the Yum repo. See if I can at least get this to load here so you can see what it looks like. For CentOS-based systems, it's basically got an internet form. You tell what version of CentOS or Red Hat that you're running, what version of Postgres you want to run, and then it gives you the RPM to download, the instructions to do it, and then the instructions further after that to actually install the Postgresql packages. So I would run through this if I could, but I don't think it's going to be working today. So it doesn't sound like this is working. So normally what you would, as far as the internet, so this is likely not going to work. What normally we do is first you download the repo itself, which is usually a pretty small download. That actually, so by default, CentOS 7 is still, like if you just install CentOS 7 and do it to anything else, the version of Postgresql that's given to you in CentOS 7 by default is 9.2. And 9.2 is actually out of support. There are no patches or anything anymore coming out for 9.2. So don't use the one that comes with CentOS. If you're on CentOS 6, you really don't want to use that one because that default is 8.4. So it's way behind. So definitely go here to the Postgresql.org website and grab the RPM from there to get the more modern versions of Postgres for you. And then once you do that, these are the two packages that I usually just tell it to install. The server package itself, so that's the actual database server itself is what that package is. That doesn't actually include the Postgres client to connect. But that's a dependency with this package. That's the name of the package is just Postgresql10. That installs like PSQL, which is the client we'll be using a little bit later. That also grabs the libraries and any other dependencies that the server needs to run automatically. And to contribute a package, I usually always install by default as well. Once you start getting used to Postgres more and what it does, you'll learn that there's a section of third party modules for Postgres that actually come with core. It's called Contrib. It has extra things like a PG Crypto for doing encryption inside the database, blanking on a couple other ones right now. Just a lot of extra tools that aren't part of the core system but are still maintained by the core team that come with Postgres. That single Contrib package installs all of those. It's like about 30 or 40 of them at this point. I definitely always recommend installing that even if you're not using any of it because you will probably use some of the things eventually. And then once the package is installed, so it's a Red Hat policy that installing packages do not enable and start the services that the packages set up for you. So you actually have to do that yourself in this case. So there's a binary setup that CentOS comes with called PG Postgresql10-setup. And then the command to initialize the database is initdb. That sets up the cluster on your server for you. And then CentOS 7 uses systemd. So the systemd control command is systemctl. That enables the Postgresql10 service. And then you start it. And then you can check the status of what it looks like after that. So like we found out, the internet is not going to support this many people all downloading it at the same time. So you can follow along with what I'm going to do up here. So basically just shut down the VM. So you go up here to power and go to here and power off. And then here in the virtual box manager, up here make sure this training CentOS 7 VM is selected. Go up here to machine tools and choose snapshots. Is everybody there? And then there's this snapshot right here called PG10 packages installed. And then just hit the restore button. You can create a snapshot. You can let it create another snapshot here if you want. I don't need it myself. So you can uncheck, create snapshot of the current machine state, and then hit restore. That's it. Hopefully everything should be installed now. And then you can just go back and start the VM up again. So I'll give you about five, 10 minutes. Make sure everybody's caught up to that point. And then we'll continue. If anybody needs any help, please raise your hand. I'll come around and help. Orson? This is why the PSQL man just worked as a Postgres user. I'll go over some of the comments a little bit later. You can just hit slash d slash d u. That shows you all the roles that are in a database. You can see there's a Postgres super user that exists in a database right now. And you can do slash help. There's a Postgres database. So that's why just typing psql as a Postgres user just works. So to make it so we can actually log in as a training user, we can come back here. And this command here, create role training. So there's different properties you can get to the role you're creating. If you want to actually make that user able to log in, you have to give it a login role. We'll go over a little bit of all the reasons why you don't want to use it to log in. You can use it as a root environment. And we also want to make the training user a super user to make our lives easier for the training user. So I'm actually not going to make the database yet. So if I should go back here and try to log in as a training user again, you'll see. So it's found that the first error was it couldn't find a loop for training. Now it's giving a different error that it can't find the training data. So whenever you try to log in with psql and you don't tell it what you've been and the database you're going to log into, first it tries to find a matching role. And if it finds a matching role, then it tries to find a database that has the same name as the role that you're trying to log in at. This is if you don't tell psql what you're actually trying to log in at, which is where a lot of people, when they're first setting a Postgres, this is the big problem that they run into. So now, if you go back to the Postgres user again, and we're all involved into the database, if we create database training, enter, and go back here, now we can log in. I'll give people a few minutes if they need to catch up here, but in that case, if we actually go here back and see you again, because we have a training user, and flash it out, we have a Postgres and a training database that works on the template of the database just right now, so. Anybody not at this point where they're able to log in as a training role in the Postgres? Well, a lot of people, when they first saw Postgres, they say, oh man, now I need psql to log in, and they're their own system user, and they're trying to log in, and they can't because of these things I just showed. Because what psql tries to do, it tries to do those things. It tries to find a matching user and a matching database to log in as a database, if you don't tell it what to do. Normally, you're telling it what user and role you want to log in at, but that's not what a lot of people even know how to do in the first place when they try to log in. And that actually doesn't even technically work in the way Red Hat has things set up that you just make a user. You still can't just log in immediately, right away. I'm sorry, can I hear you? If you're using psql, there's a, they want to try to log in as a Postgres user, and it's probably not going to let me in at this point, but user's a dash u, there's a whole bunch of options for psql. If you do it as a user, if there's a substitute there won't be, because I'm here. Yes, right, so if I try, if I try to do this now, we'll go over why this failed in a little while. But yeah, those are the options for the psql. There's a whole lot of stuff. It's going to do well while we're here in the database. So you can do this, so you can see, you can also tell which database you actually log in to. Just from the psql prompt itself, by default. So if you go over here, you can see it as a Postgres user, it's got this Postgres rule 10 pound. That means I'm logged in to a Postgres database. So if I go back over here to the training, you can see there's training, equal pound. So that's equal pound, actually, I mean too, but I'm going to need to talk about that later. But you can see it here in the slide, I try to be consistent with when I'm logged in to the database, what user I'm running things as, where I'm logged in and running these things. You should be able to tell by the file prompt itself, or please ask if you're not sure. In Postgres, no, if you do create users, if you do the create user command, does it exist in Postgres? If you do that, it automatically gives it the login privilege. So I usually just try to use create role all the time, just so I'm explicit in what I'm doing. But yeah, they make it like an alias for create user to do that kind of thing. You'll see a lot of discussion, most people call the role in the database just to be clear on what it is. Oh yeah. So that was the thing where, so over here I'm logged, I remember wanting to do those to the Postgres users in one tab. So that's the tab where you're logging in and you're logging in to the Postgres database. So if you created the training user or the training role in the training database, you should be, I'm at a prompt for the training, as a training user right now, you should just be able to log in like that. There's, you can change that. It's kind of like a shell, this slide, you can change what that shows to the other things by default, it shows what the database you're logged in to. That's where you saw that error, I guess. No, you don't have to have, it doesn't have to have it on the database. I was just showing you what the sql does and why you can't log in a lot of the time for the training set up on there. You can tell it's logging in to whatever database you want. Yeah, and you don't have to have the user's role in the database in that one, but it's gonna make things easier. Do you actually try to do that? I think I did it here. So I tried to log in as the, try to log in to the Postgres database. Oh, actually it does log in. It's the user, it doesn't have to work. So yeah, now I'm logging in as the training user to the Postgres database. It's because it's, well, we'll get into that so it's a little bit, it's the way the authentication works. Yeah. So one other thing we want to do while we're back here, we actually want to create this, create this role called replication. While we're here, you can do this as either as the Postgres user or the training user. So that's something we're gonna use for later. So you can actually, you can set the password, you can actually set the password at the time you create the Postgres user. But I prefer not to do that, for that reason. That shows your entire history of command to be ran. So if you create a post, if you create a role and set the password at the time the postgres user created, it's there in the Postgres field history. You can go delete that of course, but it's just one thing we try to avoid. So there is, when you're logged in, there's a backflash password command. Or actually you want to set, if you don't give it a user, it changes the password for the user from the logged in. But I'm just gonna be able to look at here. So, and I'm just, we think that, I'm just taking the password password. We actually want to do this also. We definitely want to do this for the replication user. Making a password password and that's what we do. No, no, no, those are properties. Those are properties you're getting to it. So the login, the login, everything after width is a property that you're getting to that role. So login is the property. The login property is the thing that just allows we'll roll the login in the first place. And the replication property is something we'll use later as a user to do the streaming application. Everybody else caught up to this point? And so I'm the unlogging hero CSU, I'll be the backflash question mark. You'll get a whole lot of things to show here. The most interesting ones here are most of these flash B1s. That's basically all of the commands to look at all the objects in the database. So it's like before I didn't come out of this, I just used a show that you learned and maybe you don't have your cable right now. So, yeah, there's a whole lot of options. This goes very, very, the only comparison I know of this is if you're used to the my people commands from, it's tremendous that they're in that. That's why a lot of people don't like using the command line and my people, because the command line and the my people are not very good and they come to post that they don't even do their area. I actually prefer that. I prefer CSU to all of the end of the week to post it at all. It's very, very, function called current database. Do you just use what current database? And I'll tell you what it is. What's the CSU LOL to tell you that? So, one other question he was asking about that and then to the next slide here. Is the, and this is actually another basic part of what's best for all administration is this file called pthda.com. HDA stands for host-based authentication. This is basically what, so the other things were defining a goal and what they do in a database. This is the final. Welcome everybody to the second talk today of the embedded latest track of scale 16x. I'd like to introduce you to the other day the speaker Robert Hall, he's the leader and developer for the Libre CRT project. Which is totally free as a Libre operating system. He's been working with open WRT, LDE, or is it open LDE? Okay, yeah, Libre, the WRT project. I'd like to mention that this really is being sponsored by the YACA project. It is a tool that uses to build distributions. So, it's possible if you use to build this, I'm not sure if you use it, but it's one of the tools that people continue to build operating systems. Anyway, thank you very much. Not my mic. Can you guys get me some hands back? I think it's just a speaker out here. So, this is what controls the actual, actually connecting to the database and how you connect to the database. If you're coming from the MySQL world, it's actually all controlled in the database itself. When you like set the IPs and all that kind of stuff that you're connecting to, this is actually controlled outside of the database and poster from this file. So, if you want to see where your database, the data directory actually is, which is a very common thing people want to know. Where's my data at? So, if you can get logged into the database, there's a bunch of show commands that the other people have as well. And one of them is data directory. There it goes. That's pretty simple. And it doesn't even send it to one at the end. So, this shows you where the data directory, the default data directory to send out to three for you is in this path, far with the PGS2L or 10 data source. So, one other thing we'll see this later, the Postgres data directory is the five defaults that you're only the user that created that interface and actually go into that data directory. So, that's another reason I created this separate path out of the Postgres user. Because only the Postgres user could go to that directory if I try to go there as the training user. And the one file that we're interested in right now is Debian Rearranges and Organizes Postgres the way it's file. Yeah, it's a file hierarchy system, yeah. So, Debian has a set file hierarchy system and all configuration files have to go in execute. So, that's where they are in Debian. Do you initialize the database? I mean, are you logged into the database and it's working? Yeah. I'm honestly, there's a reason that I did this in CentOS right now. I know, that's why that's the reason to ask. Yeah, but yeah, it should be in... ...XT plus URL10. Yeah. XT plus URL10. Do you repeat your PA on Debian? Yeah. XT plus ATT plus URL10. Do you also not able to get a file with them? I'm free. Are you the Postgres system user? Okay, open another terminal or another path and do sudo sudo sudo su-postgres. Become the Postgres user and you should be able to do that. Now we're here in the hba.com file. Well, the data directory is still there. The data is still in that location, but they can take files and not take them. Yeah, the data, yeah. You actually do select start from, turn it up into a killall. You'll see where HBA file is referenced in there. It's in there, it's also... So, this is where all the authentication is, is controlled with this file. You can actually always have a whole talk on this file and all the things you can do in it. So, we're not gonna get too in depth with it. We just kinda kinda show you what goes in here. And you can go follow those languages, or run it to top and go to that page and the Postgres will go back and goes over everything that's in there. So, this is also where everyone can control whether connections are done via FSL. This is where that's controlled. The very, very important thing about this file is it's evaluated in top-down order. So, you can't just throw anything in there. If there's a rule at the top that blocks all users who are logging in, or most users who are logging in, and you add a specific user you wanna log in further down the file, it's not gonna work. You have to put it above that thing so it's evaluated in order to talk about it. So, that's one very important thing to realize about this file. And so, the way Red Hat systems do this, is it sets things up to allow peer authentication. What peer means is, if the system user matches the role in the database, allow it to log in. So, that's why we couldn't log in as the training user or even specify that, I couldn't even specify as the training user that I wanted to log in as the Postgres user. Because peer is set up like this. So, it's actually set up like this for local and for identity, basically the equivalent of peer for PCG ID. So, local here means it's a local socket connection, host means it's a PCG connection. So, that's the main difference there. So, that's why we didn't log in as the training user of Postgres before we... And if at all possible, it's highly recommended because there's one of these over here under the method, method called trust, that basically allows like for whatever you define over here, if that matches, just allow the user to log in. No matter what. A lot of people usually set it so that local system users are with trust and they can just log in without a password. Not too bad, you definitely don't want to allow remote users to even log in with trust. That's definitely a big no no. A lot of people will allow local users to log in with trust but if you can avoid that, I would highly recommend avoiding that. And if you don't want to have to type in a password every single time because you don't have a trust, there's this file called .cgpassfile that you put in that user's home directory and you can go there and just do the form out over there that it'll, it's a special file that requires very strict user's permission and it'll allow you to log in without having to type in a password every time. And I'm going to completely break that rule and do a trust, just to make the training session easier. So, do as I say, not as I do. So, what we're actually going to go in here and do for the pghda.com. So, we're going to set up replication data. You can see I actually am not doing trust for replication. That is one user you definitely don't want to do trust for if at all possible, if you're a stringing application user. Use password authentication, use the .cgpassfile for the replication role across the network in our kind of stuff, it still works. So, do as I say, not as I do here and do trust and not password. So, to do password authentication, it's basically this method called md5. It's despite being called md5, and I can't hear the whole other talk. It's not why it's still md5. But that's a grant, right, it's quite good. So, what we're going to do, we're going to actually add these two lines to the top. Anywhere in the top of the pghda.com file. That means it's going to, you can see it's actually going to override these replication things down here at the bottom because I want it to log in. I want it to be password verification. And this is just going to make it so the training user can log in with the replication role of our replication property, which is going to be widening that later. So, just add those two lines anywhere in the top of the pghda.com file. There shouldn't be, I mean you can see a whole bunch of stuff here. As long as it's above these other things that are on top, I'm sure. It evaluates from the top down. So, it starts up top and goes down. As soon as it finds a match to the top, that's the one you would use. As soon as it matches? Oh, it doesn't keep going. No, it doesn't keep going. Yes, yes. I'm sorry. I think I should have filed for that. As soon as it finds a match, it stops the value. So, thank you. I didn't, I wasn't there on that one. It requires you to get a password to log in. Down here at the bottom is the idn, which just means if the system ends, I want to force a title. It goes there and stops and matches and forces it to do that. Anytime you actually do a change to this file, you don't have to restart the database. All you have to do is reload the database. There's basically, reloading the database. There's two ways you can do this. My preferred way of doing it is because it works no matter what operating system you're on. As long as you can log into the database as a super user, you can run the CSTL, collect, CG, constate, underscore, below. The other way you can do that is most of the CSTL reloads, man. I usually like doing the reload within the database. In this instance, you can't see within the CSTL what I'm talking about in the database. But other settings, like you're standing in, you change another setting and you want to make just get the login of the database to be there. Marty, log in to the database. You'll see it up there. You'll see it up there. And then check and make sure my CSTL changes went through. And it's consistent no matter what operating system you're running at. If you're on Debian, you're not going to move. If you're on older Debian or 3DSC or something, you're not going to be using CSTL. We want to actually, the other way you can check to make sure that it actually went through is we're in the database directory here. There's a log directory in here. Should only need one file in here right now. So CSTL, it's actually ran. It logged in, got a signal. And if you actually change your configuration parameter and reload, it'll fill in the log what configuration parameter it changed. So that's the other way to make sure your reload went through. So it's in the data, in the same place where you're in the pgaqa.com file. There's a folder called log. This is LOD, the Postgres user. Only the Postgres user can see. So this is actually one change in Postgres UL10. If you're on older versions of Postgres somewhere else, it's not just log, but directly it's not just log. It's pg-understore-log. So that is a change. So if you go back to your own systems and you can't find the log directory, that's why it's pg-log in Postgres prior to 10. Frankly, if Postgres doesn't have access to logs, would you bet? Yeah, you don't want to do that in Postgres due to some local things on your database. There's no need for it. Yeah, there's no need for it. It's the largest privilege that the user can get. So either 8 gigs or 25% should be a good starting amount. Hopefully, most production systems you're not gonna be running with, you're not gonna be running a system where you can't do that on a production system is kind of the recommendation right now. So try to have a system where you can at least do that. But if you're like this VM, obviously, we can't set it to 8 gigs. We're not setting the VM to 8 gigs, so don't do that. It's not gonna work, but that's the recommendation. Yes? I'm sorry, say that again? It's basically like the, so whenever you read data, that data comes off a disk and goes into memory and it goes into the shared buffer section. So it's where the quick access to getting, so if you have frequently accessed data, it's gonna be sitting in shared buffer so it doesn't have to keep going back to disk. That's basically what, it's a cache. Like it's one of the other reasons I linked to this wiki, it goes into much more detail about what that is as well. And just to keep going along with what memory use is, there's also this other setting called workman. This is where people usually cause Postgres to run out of memory because what it is, so you can see it actually has a very, very small value to it, usually, it's by default it's four megs. So setting it to between two and five is usually a good starting point as a default. The more memory you have, you can start setting this higher. Basically what this does is every, sorry, every query that you run, every time it does like a sort or any kind of work that it has to do and like at that time in memory to work, that's what this setting is for. So and it's not just, it won't just use this value once in a query. If you have a query that's doing multiple sorts and multiple aggregations and multiple things, it will use multiples of this value. So if you have a very, very expensive query, you can have workman itself using up like 200 megs itself cause it's got like a hundred different sorts and stuff that it's doing. So that's why you kind of wanna be a little bit careful on setting this value too high. We'll look into some things a little bit later, how you can figure out how to tune this a little bit better. And that's distinct from shared buffers. So this is memory, so the shared buffers Postgres starts up, it immediately has that memory reserved for itself all the time. Work memory is additional memory that running queries will use later. So that's why it's tied to max connections. So if you have your max connections set really, really high and you're using all of them all of the time, all of those queries are at minimum using workman value and maybe multiple values of it. So the max connections value is also actually a way to control how much memory is being used inside the system as well. Like I said, there's whole talks on this. I don't wanna get too much more into this part of it. I highly recommend reading that Wiki link that'll probably answer a lot of the questions that are in your head right now about these settings. So I'm just gonna move on from that here and you can come back and talk about it later if we have more time. Maintenance workman is distinct from work memory. That's for things that we'll go over a little bit later for vacuuming and creating indexes and that kind of stuff that are like rare, not very often things that are running very often that need a little bit more memory to work with. One gigabyte's usually a good starting point for that and it may not even use all of that but that's up to how much it will use at a time. And that's also in addition to shared buffers and work memory. So the settings on here that are actually orange, these are settings that actually want you to go in and open up the PostgreSQL.com file and change these values. So we're back here in the data directory. So there should be, you don't wanna, there's, don't worry about this PostgreSQL.auto.com right now, just ignore that one. So we're gonna open up the PostgreSQL.com file and the file itself actually has a whole lot of explanation on what a lot of settings are. There's a lot of comments and stuff in it. So it's actually worth reading through that there. So see by default the max connections that are already set at 100. So the first setting we're actually gonna go down to here, change is wall, sorry, wall level. So Postgres, like most RDVMSes use is what's called the write ahead log. Every time you write to the database you're not actually writing directly to, you're not actually writing directly to the data file where the table is located. First it writes to something called a write ahead log initially and it's just a continuous serial file that's just files that are just getting added to it and then PostgreSQL goes back later and then actually moves the data to where it needs to go inside the data files themselves. What the wall level does is for replication it tells it what information to put inside those wall files for use in replication instances. If you're not doing replication you can set this to minimal and it'll actually make, it'll tone down the rate of wall generation if you don't need it to be that high. But by default now it's usually set to replica. So the default level right now is actually replica so you don't actually have to set it explicitly but uncomment it so it's explicitly set and that's because we're gonna make a replica in here in a little while. And just FYI I'm gonna plan on breaking around one o'clock for lunch because I don't give three hours before and three hours after so if anybody's looking at their clock and seeing it's noon. The other setting that I wanna actually go in here, before we go down any further this is other setting called effective cache size. I'm still not 100% on understanding exactly what this does to explain it well which is why it provides that link to the wiki but it's basically kind of giving the query planner a hint to how much memory may be available for doing things. If Postgres is the only thing running on the database you can set this up to like 75%. Doesn't mean it's going to use 75% that just gives it an idea of how much it may have available, a memory for doing query planning. But 50% of RAM is usually a pretty good starting point for effective cache size. Again that the wiki has a lot more detail on that setting. So the other setting we're actually gonna go down and change is called archive mode. What archive mode is doing is for, sorry. So when Postgres is generating the wall files if you wanna be able to do things like point in time recovery and other different kinds of replication you may wanna also have Postgres send those wall archives off to a secondary location so you can use them when we'll get into what detail more with that is a little bit later. I always, even if you don't necessarily know what this is or know if you need to use this I always recommend going and at least turning this on because changing this from off to on requires restarting the database but changing what command actually runs which we'll get into a little bit doesn't require a restart. So turning it on isn't really gonna affect how your system is running and avoids having to restart later and if you're going to be running a replica you're most likely going to want this archive command turned on. It's very rarely when you don't. So definitely recommend going in there and turning it on and when you turn it on then you also set what's called the archive command. That's basically telling Postgres in addition to its own wall files that it's writing when it completes writing a wall file also go run this command. So this you can essentially just you can put a copy command in here and it'll copy the wall file off to some other location to wherever you need it to do. If you're not actually doing anything and you're doing this on any Linux system I just said it to been true because the way Postgres works it'll run, if archive command is on it will always run the archive command and if it returns true it thinks it's successful and just continues on with its merry way so if you tell it to run been true it thinks it's working and doing what it needs to do and then since the archive command itself doesn't require a restart it only requires a reload you can just go back and change the archive command to something else which we will be doing later and you don't have to restart the database you can just change the command and reload the database without restarting and you're good to go. And the last setting we're actually gonna change here is called the archive timeout. So the wall files themselves are there are 16 megabyte file all the time as soon as they hit 16 megabytes it goes and creates the next wall file. And of course if there's no writes going on in the database no wall files are gonna be being created and you may end up having only partial wall files that never complete so you can have this archive timeout that if no archive command is called it will go and in this case every 60 seconds always create a wall file and it'll always be a 16 meg file it'll just be really really there'll be almost nothing in it and be very compressible. So moving on here the next setting we actually wanna change is called max wall senders. Max, there we go. So by default it's 10 but uncommenting here just to be explicit. So when you have when you have replica systems the master has to be sending the wall files to those replica systems or wherever your archive command is running and there's a limit to how many of those processes Postgres can do and that's what this is telling it. There's this is telling it at most we can have 10 replicas. So if you need to have more than 10 replicas you have to go in and tell it I need more things sending wall files out. Wall keeps segments? Yes. So whenever you write to Postgres it doesn't immediately write the data to the data location. It first writes it to the whitehead log this is for crash safety and crash recovery because writing immediately to the data file where the table is located means either has to seek around disk and find it it's rather slow but just writing immediately in a serial manner to a file immediately is very very fast so it gets your data committed to disk quicker in that sense and then it goes back later and writes it to where it needs to go. You can kind of a kind of thing like that way. It's like a transaction log file is basically what it is. All the right transactions are going that are being written here first and then going being and written too later. So if your database crashes you're less likely to have lost data is what it's used for. Sure, sure. And so Postgres is always writing those wall files and eventually it doesn't need it. Once it flushes the stuff how the disk where it's supposed to go it doesn't need that wall file anymore locally. It'll go back and it actually recycles them. It'll like rewrite over the same file so that they make new files. But if you want to keep a few around just in case for also for replication reasons and other reasons we'll see later. I usually recommend just setting it to a reasonable like 30 is a fairly reasonable value and only takes only takes 30 times 16 megabytes so it doesn't take up that much space. If you're doing enough right activity that is making more than that Postgres will certainly make more than that but if your right activity tones down it will always keep at least that many around. And the next setting we're gonna set is max replication slots. So this is actually a fairly new feature, relatively new feature, I think it's a 9.4 introduce the concept of replication slots. So the way replication, the way replicas work in Postgres is basically all the replica is actually doing is replaying the wall stream. So when you have a replica connecting back to Postgres it's actually reading that wall stream on the master to replay on the replicas. That's how replication works in Postgres. So if you say your replica falls behind like your replica loses its connection for some reason. And Postgres you only have the master set keeps 30 wall segments and you have a fairly high turnover of wall segments. If your replica is down long enough the master will have recycled the wall stream or the wall files and they won't be available for the replica anymore. So what Postgres 9.4 introduced is the concept of a replication slot so that the primary system is aware of where its replicas are in the replica stream. So if the replica loses its connection the primary will keep the wall files around until that replica reconnects back to it again. So it's a very good thing to figure out and get set up in your systems. If network latency can sometimes be an issue definitely want to look into the whole replication slot issue. Yes, so there's only one wall stream on the master on the primary system and it's in a folder that it keeps those in. There's only one copy of those on the primary system and then so but if you have multiple replicas connecting it will keep those wall files around for all of the replicas it's aware of. So your limitation is your disk size on your primary system. So that also means that if the replica disconnects the master's gonna keep all of those wall files around forever until you either turn the slot off or reconnect that replica system. So it's something to monitor as well and we'll get to some monitoring a little bit later. I can almost promise you I know a lot of you this is like going over your head very, very, it's a lot of information. So this is like a basic introduction of the config options to go in and try to investigate yourself more on how to set up a Postgres system. So if you're a little bit lost right now, it's fine. I was the exact same way when I first started getting into this stuff. Right, you can set it to do but that setting requires restarting the database to change. So if you are not sure and setting it to 10 is not doing anything that it's not really over. It's not doing anything of any significance to the primary system. So setting it to 10 is usually a pretty good just default to leave there. And if you need more, then you have to restart your database to do more later. But if you don't need that many, then they're just there. Yeah, not automatically. We'll get into replication a little bit later. You'll see. The one other setting we actually wanna turn on and this is actually a replica only setting but we're setting it in here on the primary. So I think it is on by default now and a lot of these settings aren't on by default in older versions. So what hot standby does, it tells the replica to allow connections, to allow read only connections to it. So that's what it means by hot standby. It's a replica that's allowing systems. The reason I'm setting it here now is because when we create the replica it's gonna take the postgresql.com file from the primary and use it there. So then this setting is already turned on and ready to go there. For postgres 10, thankfully, I think they've turned this on by default where this actually may, I think this actually may be sent us to this logging. So sent us change their system to automatically turn logging on by default for their system. If logging isn't turned on, this is where you go turn logging on in the first place. Just change this logging collector from off to on. That actually starts the whole logging process to actually work. And then I'll get into this a little bit later of what this actually means. But we actually wanna set, the default for this is the default value for this is very, very low for most production instances. And I'll get into what this is a little bit more later. So, but the thing is you only wanna set it this high if you're actually monitoring for transaction wraparound which we'll get into a little bit later. So monitor for transaction wraparound and set this value this high. So that's my caveat for looking into this setting. I'll get into what it is a little bit later. But while we're in here editing the file, you know the default value down here is 200 million. So just change it to a billion. Now set on. No, it's the default is, those are the default values. Yeah, so I'm just going through. It's like some of these values you didn't actually have to change because the defaults are there. But a lot of the older like nine, nine, two, nine, three, nine, four, those weren't the defaults. So this is what I usually go in and have to change. So at this point, all of these orange settings are the ones that I wanted you to go in to your PostgreSQL.com and change. You should have the internet seems to be working pretty well now. So you go to the homepage in Chrome and the VM you should be able to pull these slides up and not have to rely on them being up here now. These are some other settings I just kind of wanted to go over. None of these actually require restarting. They just require a reload to change. Yes, I can't, I will be getting to that. I will be getting to that. So these are just some other settings I wanted to go over. You don't have to change any of these. I just kind of want to go over them so you're aware of what they are. This is a new setting I think in nine, five this change. So if you're running nine, four and older, you won't see these max wall size and max wall, min wall size values. That will be something different. And those different settings are a whole other talk and why these settings were created. This is to make managing of wall files a lot easier. So what this is saying is basically, so the max wall size means once the amount of wall files reaches one gig, one gigabyte, that will force a checkpoint, which means it starts flushing things out the disk. And what min wall size means is how small that wall directory actually is. So actually, I'm going to go in here to the data directory here and show you. In nine dot 10, or in 10, it's actually the PG wall. Oopsie, PG wall. So this is the wall directory I keep talking about and where all these wall files are generated input. So max wall size means if, and if you do enough traffic that you generate more than one gigabyte of wall files, Postgres will more than happily make more than a gigabyte of wall files. It'll just means that if it goes over a gig and your right traffic goes back down, it'll try to keep that data, that wall file directory to be no bigger than one gigabyte by default is what it's trying to do. And min is just how few it'll keep around for doing recycling purposes. Checkpoint timeout and checkpoint completion target. So this is telling it that if a checkpoint hasn't happened in five minutes, do a checkpoint out. A checkpoint is basically just flushing all the data that's in shared buffers and memory that needs to go to disk out to disk. So it's telling it if a checkpoint hasn't happened in five minutes, go right to checkpoint now. What checkpoint completion target is, is instead of trying to, instead of at the point of checkpoint, just taking all of that stuff that's in memory and trying to immediately flush it all out to disk, what it'll try to do is whatever you have that, we have this checkpoint complete timeout of five minutes. This value is actually the completion target is a percentage. So what it'll try and do is spread that checkpoint out to 90% of five minutes. So it'll try to spread the writes out, so it's not doing a huge IO all at one time at checkpoint. It'll try to spread that checkpoint IO out. So it takes 90% of what the timeout period is. So in this case, it'll be 90% of five minutes. If you set checkpoint timeout to 10 minutes, it would try to spread out the right of the checkpoint out to nine minutes. So it's not doing a huge flush out. So by default, I think checkpoint completion target is set to point five. Point nine is usually what I try to do. Try to give it the most time available to do the IO that Postgres is trying to do. It's not an easy concept to wrap your head around at first, so if you don't quite understand, it's fine, but so the data that's in shared buffers that needs to be written out to disk, that's the checkpoint. It's taking the data that needs to go out to disk out of shared buffers and putting it out to disk. What's that? I wouldn't, that would give it, that means if you're, it would spread it out over that whole time and you would have no downtime between checkpoints. So you wouldn't give other things that are using the disk some time to do what they need to do. So yeah. Yes. No. No. Right. Yeah, just makes IO bad. Yeah. I think I always, I usually try to change too. This is the log file name. So you can remember back here when we were in the log directory, the log file name is, I think it's the percent A or whatever. That just tells it to whatever day it is. That's the name as it gives the log file. What this means is when next Thursday comes around, it overrates the file. So if you need to keep your log files for a long period of time, you wanna change what the name of the log file is because if Postgres finds a matching log file name, it'll just overwrite it the next time it comes back around. So my default that I easily change it to is year month day. So every day has its own log file. Another useful one that, if you actually wanna have the equivalent of trying to find flow queries, you can do this log min duration statement. This value is in milliseconds. So 5,000 milliseconds is five seconds in this case. What this means is if a query takes longer than five seconds to run, it will log that query to the Postgres database with a time of how long it took to run. So if you have a very, very busy database and you set this to like one millisecond, you will probably fill up your disk very, very fast. And we actually make Postgres itself perform worse because it has to write so much to the log file. If you really wanna get all queries and stuff logging, we can talk about other ways to do that. But usually this is usually something that if you want queries that are taking too long to run and you wanna know what those are, you can set log min duration statement and it'll log those queries that are taking that long or longer. If you actually wanna log when sessions connect and disconnect, which I highly recommend so you can see, that lets you see when a session starts and when a session ends, you can see when it logged in and when it logged off. You can log the connection and it'll log the disconnection so you can actually watch the whole session. And then this other option here is called logline prefix. So if you actually open up the log file, this is the logline prefix to there. That's just what Postgres puts before every single line in the log file. So by default it just has a timestamp. That's actually, I think older versions of Postgres don't even have that as the default, which is another big reason to go in and check what it is. But I actually add quite a bit to this. So this percent M is the timestamp. I'm trying to remember what. What is R? I'm blanking on what these are now. I can actually go look here if the internet works. Oh yeah, the remote IP. The IP address and port. So this can, this logs the date, the IP and port, the PID file of the system that's logging and connecting. This is like a line number. So when a single session logs in, every single query it runs gets its own line. Every single query that goes into the log file for that session gets its own line number. So with log connection and disconnection on and this line number, you can watch what a query does from beginning to end of when the session connects and disconnects. And then this also has the user that logged in, the database that it logged into, and E is at any error, the error code that happens. If an error happens or just zero, it's no errors. So if you have to go back and do log analysis and stuff later and you wanna know what users logging into what databases and how many queries they're running, that's what this log line prefix allows you to do. And there's a, you can see there's a whole ton of things you can add into here. If you wanna start getting down to transaction tracking, there's this X and V are really useful. They really need to get down in five grains in what things are doing. Postgres logging is pretty powerful. Yes, any query that's run. It won't have the individual, when functions in Postgres are single transaction, you can't have multiple transactions in single function. So a single function call will be one statement. Yeah, you would have to set, if you just set it to negative one, they won't log any statements at all. If you don't wanna log anything, other than that, no, there's no real, if you wanna try to differentiate specific logs from queries from being logged and not logged, specifically not by their time, by the query itself. And yeah, but that's not gonna stop it logging from the log, that's not gonna stop it logging in the log file. That's true, yeah, that's right. So this is actually, you can set this per session as well, so you can actually disable it for those long sessions. Thank you, yeah. Another useful setting to turn on is log lock weights. So if you have anything holding a lock for any long period of time, it's kind of a little bit hard to parse in the log file itself, but if you use like a log analysis tool to go back and look at your log weights, it's useful have. So I'll get into vacuuming a little bit later and what vacuuming and analyzing and stuff, all that stuff is. What these scale values are is, so it's actually easier to go over the scale factor first. So what that's saying is if, so it's a percentage value, if 10% of a table is written to, run vacuum on it. If 5% of a table is written to, run analyze on it. So I found the defaults a little bit too mild, I think they're 20% and 10% respectively, I usually bump it down. And then what the threshold is, is like a number of rows on top of that. So what this is saying is if 10% of a table is written to plus 500 rows, then run vacuum on it. That avoids vacuum going nuts on really, really small tables and running all the time. So I think the threshold by default is set to 10, so it's really, really low. Yes, we'll get to that. Yep, the next slide actually. So we've changed all these settings in our PostgreSQL.com file. We actually need to restart. So a lot of the ones we did here on the orange require a restart, like a wall level, archive mode, and all those other things. So as a training user says I have sudo, I'm gonna go back and restart the database. So sudo, system, CTL, restart, PostgreSQL, and hopefully everything worked okay. So if I log in here, you should be able to log in. If you can't log in right away, go back here to, or in the log file, and the Postgres log file should tell you why it wasn't able to restart for whatever reason. And one way you can tell if everything was successful, you can do show, archive, mode, it's turned on. Actually, I think that was on by default. Let me do archive command, because we set that. So any of the settings inside PostgreSQL.com, you can see what the current setting is by doing a show. Or there's a PG settings table you can check too. So give everybody here a few minutes to restart. The question everybody keeps asking. Yes, right, because sudo will kind of cache itself for a little bit. If you ran a sudo, you don't have to immediately have to do the password and do sudo again. You don't immediately have to type the password every single time. Yeah, if you log in to the database with either as training or Postgres, and see what the archive command is, do show archive command, says off. Yeah, we'll have a break at one o'clock for an hour. I'll just go over this here real quick, and then we'll break for lunch. The Postgres user doesn't have a, the Postgres user doesn't have sudo. You have to do it as a training user. Yeah, you have to do it as a training user. So that's why I have like two tabs open. I have two terminals. One terminal is the training user, one terminal is the Postgres user. So just open up another terminal, and you should be good. Well, you have to change to the training system user to restart the database. So just open up another terminal for a second. There we go. So now you should be able to run the command to restart the database. Anybody else? Let me go help somebody real quick. Excuse me? I don't know if the, the day that the post the com file maybe didn't save. So how do I go back to give you the other tab as the Postgres user? Or just have a Postgres user tab? Yeah, there we go. So open up the postgres.goal.com file. Setting a page in this, right? Yeah. Sorry, from the lowest level. That's right. There, go back one more slide here. So these, these orange ones are the ones we need to change. So you can try to try to find, try, let's like go find archive command. Here do a do forward slash. And then type, sorry type. No, yeah this one. Oh that one. Yeah. And then type archive. There we go. So it just turned on. Okay. Yeah, cause I did go through this and where you were talking and changing that. Let's go, let's go actually go out to the, there may be a, an error in one of your exit out of here. Exit out of the editor. So that would be. Cull on queue. Let's go to CD log. You know, oh we're not in the editor. Where are we right now? Yeah, just hit a hit, just hit do type pwd. Okay, so CD 10, space, space 10. Is that changing it to the 10 directory I think. Enter. And then do LS, CD data. One word? No, just change to the data directory. Change to the data directory right there, CD data. Yeah. And then go to CD log. Space log. And into the log directory. And then just do LS, see what the font should be. Postgres Thursday, yeah. Open that file up. Either Vim or Vim, postgres, go to all Thursdays. Vim, space. Just start typing, type pwd, just hit double tap tab twice. It should auto complete for you. There you go. Enter. Like this, uh. Did text there? Oh these are earlier ones, let's see what it is down here. Doesn't show that the database restarted. There we go. So just in, the database wasn't restarted. Yeah. Yeah, no problem. I'm just gonna be talking about stuff now, so if you want to continue working on trying to get things going, feel free, and then I can help you try to get caught up over lunch as well. When people kept asking, what is this vacuuming? What is auto freeze and all, auto vacuum freeze and all that kind of stuff. This is a very, very core part of understanding Postgres QL administration. And it is not an easy thing to wrap your head around it first. So if you don't, even if you still don't understand this, when I'm done there's, again, there are entire training sessions and talks about just this topic as well. Bruce in the back has done many of them, so you can go talk to him. But this is a very, very, if you're gonna be administering a Postgres QL database, I highly, highly recommend taking the time to try to get your head wrapped around this concept of vacuuming and transaction wrap around because if you don't understand it, you will eventually reach a point where your database will shut down and will not start up again. And then you're kind of in a panic mode because you didn't understand this part of it and didn't do the things to prevent this from happening in the first place. So a core part of the way Postgres works is it uses some, and the way it allows multiple people to be writing to the same tables and stuff at the same time. It uses something called multi-version currency control. So whenever a row is actually updated or deleted in Postgres, it's not actually, it's kind of like the same way most file systems work nowadays. It's not actually removed from the file system right away. It's just a flag is set to say, this file doesn't exist anymore. To most people looking at it. So Postgres works the same way. When you update or delete a row, the old value of the update or the old value before you delete it is not actually removed from the database. It's just marked as unavailable. It's still there on disk, it's still there. What vacuum, when the vacuum process runs, so that's initially you run your delete or your update, the rows still exist there in the database but it's marked as unavailable to anything else that wants to try and see it. When vacuum comes through and runs, what it does is it takes all those unavailable rows that are in a table and marks them as reusable for future updates and some inserts, not all inserts, but all updates will be event, so that space is available for future writes. And that leads to what people commonly know as blow, is that both those unavailable rows and those rows used as a reusable space are what's referred to as blow. And this is actually a link here to a lengthy blog post that I wrote for actually checking on bloat and what bloat is and how to monitor for all that kind of stuff. So what this means is vacuum does not recover your disk space in most cases. And in the vast majority of cases, if you run a vacuum on a table, you will not get any disk space back if or very little at all. And that's what confuses people a little bit. All of the normal vacuum does is mark the space available for reuse. The one instance where a normal vacuum will recover disk space, so tables and stuff are, they're made up of multiple pages. They're not all just one big file on disk. They're multiple pages in chunks. If the last page in the entire table is completely empty, that page will be removed by a vacuum. If there's pages like has any rows in it anywhere throughout the rest of the object, those pages won't be removed. So that's the very, very rare case where vacuum actually will recover disk space. But for the most part, don't expect vacuum to recover disk space. That's not what it's for. It's just for internal maintenance of making space available for reuse. If you run a vacuum full, that does recover all the disk space, but it does that because it completely rewrites the entire table. Takes the first couple to the last couple, completely rewrites them to new files, but also completely locks the table the entire time that that's running. So you can get, if you need to get your disk space back, and you can afford some downtime on that table, you can run a vacuum full on it and you'll get your disk space back. The thing to keep in mind though is you may use up to double or more disk space of that table because the old table is still there until the vacuum full completes. So you need, that's the other reason you really, really, really wanna monitor disk space on any database system. I would say if your disk space is going over 75% usage, somebody should be getting paged and things need to be fixed because if you start getting close to 90%, you start running out of space to even run a vacuum in the first place to recover your data because you need more space to run vacuum. Yes. He was asking if you can do like a temporary space in the meantime. I guess what I would recommend is so when you're doing the vacuum full, you're actually, you're still generating wall. It generates wall files. Any right to the database generates wall. So what I would suggest is maybe some extra storage on your wall space on the different space to do that. That's why I said it would not just double the space. It would probably be more than double the space while it's running a vacuum because of the wall that's being generated and the table, just in case. No, you can't put the results on the table. Yeah. Yeah. Yeah. Yeah. And then the wall, the wall of traffic will clean itself up. So that's why I'm saying if you're running a Postgres database in a production system, you wanna always make sure you've got at least 75% available free space. If not more, the more the better, but yeah. Are you doing RDS, you mean? You don't have to worry about this in RDS. Yeah. Well, yeah, I'm sorry. Yeah, you do. You have a limited number. You have a limited amount of space in RDS for your instance. So you have to make sure there's enough space in your RDS instance to do that. Yeah, I'm sorry. Yeah, it's the same problem at RDS. I'm sorry. Yeah. What's that? I'm not sure. It's a whole other. Well, the performance thing is your table's locked. It's unusable for the duration of the vacuum full. You can't query. A regular vacuum, you can still query the table and you can still use the table. I mean, that's what auto vacuum process that's running in the background. Oh, you mean after it's done? Yes, yes, yes. So you're getting a little bit of that later. Yes, depending on how much the vacuum full cleaned up, yes, there can be sometimes dramatic differences. So yeah. So there are tools out there to try to allow the vacuum full to not have to do a vacuum full. Is it a tool called PG-REPAC? I would be cautious on using PG-REPAC, especially in a very, very high concurrency environment, just because it's not a core tool and I have run into issues with it. My recommendation is to do a little bit better monitoring of your system and be able to afford some down times to clean things up if you can. Data corruption? Yeah, the kind you care about, yeah. So I really only run into it in really, really high concurrency environments, where the table you're trying to do this on, which is normally where you would want to use for PG-REPAC because you don't want to have an outage. But yeah, so it can be some issues there. Yes, yes. Well, it depends on the usage of your system. Like I said, there's whole talks I can do about this that I can go over. I'll try to get through this. I can try to go over a little bit more later, but yeah, and actually the blog post I wrote about goes into it a little bit more. There's a lot of tuning and stuff you can do to reduce the bloat. And actually, my point here too is you don't want to overuse full in PG-REPAC either. That reusable space, bloat isn't always a bad thing. There's good bloat and bad bloat. That reusable space means Postgres doesn't have to take the time to allocate a new page for a table. It doesn't have to, it can just reuse space that's already there, which is tremendously more efficient. So bloat is not necessarily a bad thing. Out of hand bloat is a bad thing. Like if you have a table that's data is normally a gigabyte, and you've got 70 gigabytes of bloat on that table, that's a bad thing. If you've got two or three gigs of bloat on it, that may not be a bad thing because that's reusable space that further future transactions can use on that table. So it's something to monitor for, and it's very, very situational for whether it's good and bad for a lot of systems. You know, there's ways to tune this to make it so you don't have downtime. You can tune vacuum and stuff to work that it doesn't build up bloat long-term in a bad way. There's ways to tune it so that doesn't happen. Most of the time, yeah, sometimes, yeah, yeah. Yes, partitioning. Well, and it also depends on, it's also kind of a loaded question. It depends on what you're actually doing. But if you're, if it's a table that's like constantly getting new data and you need to expire old data, that's where partitioning would come into play. If it's a transient table that you really don't care about the data that's in it, there's another thing called an unlogged table that doesn't even make wall and there's a whole bunch of other stuff. So there's a lot of answers to that question. So, unlogged, unlogged, logged. The vacuum and stuff will still be there. It'll just be a little minimized to, not really. Yeah, that's, yeah, that's true. Yeah, yeah, Truncate does, Truncate does not do MVCC. Truncate, all gone, everything gone. So, yeah, it's MVCC safe, but it's not leaving all those, it's not leaving all those dead rows behind. It's deleting things out down at the file system level and they're gone. So, yeah, yes, I'll get to that. Yeah, the file's gone from the file system. There's nothing left. Yeah, so, but if it's a transient table that you don't need to keep the data around and you want to avoid those kind of things, figure out a time to truncate it. And it'll be, a truncate is fast. As long as there's no other query that's running that's using the data from that table. Because if there's a query that's running and using data from that table and you go and do a truncate, the truncate will hold until that transaction finishes and then it'll just instantly finish most of the time, very, very quickly. So, yes. Well, there was, you wouldn't know whether you need to do it in the first place. The time estimate, no, there's no way to do that. There's, but there's a, I mentioned contrib modules earlier. There's a contrib module called PGSTATTUPPLE that if you run it on a table, it will show you the deleted rows and the available free space for that. There's actually also a table, there's also statistics in Postgres itself that aren't, PGSTATTUPPLE will give you a 100% accurate depiction of what the state of that table is. There's also statistics in the PGSTAT. There's PGSTAT all tables or something like that that tells you how many deleted rows that are estimated, deleted rows, estimated, all those kinds of things that you can look at immediately to tell you those kind of things. So you can run an analyze on a table. Analyze is a very, very lightweight operation on a table and definitely, and then go check the statistics table for that, for that, the statistics for that table, see what it says, see how many deleted rows and stuff there may be, but then to actually know how much reusable space there is, which is usually the bigger issue, you'd have to run something like PGSTATTUPPLE to get that kind of information. And bloat, and table bloat is distinct from index bloat. Indexes have their own set of all the new roles distinct from the table. Yeah, so let's go over the rest of this real quick then, so I can at least get through it before we head out for lunch. The other thing, it kind of goes along with all of this vacuuming, all that kind of stuff, is the way Postgres actually does multi-version currency control, so people know what things are actually visible to themselves, what rows are visible to users. So every single row in the entire database has a thing called a transaction ID value assigned to it, and every new right to the database insert, update, or delete, increments a cluster-wide transaction ID value. So that ID value determines the visibility to the transactions that are currently running at that time. So this is probably, I'm gonna go, it's went over my head for months, so don't worry about if it's kind of hard to wrap your head around, but this is something I really highly encourage you to go in and if it's not making sense to go in and try to understand. So that transaction ID value is a 32-bit number, and so once you reach, obviously there's 32 bits that's limited in what it can do, eventually you'll reach a wraparound point where the things that were all look new again. So every, so it's a 32-bit number, and that means, so after four billion transactions that'll reach a wraparound, that means the current transaction, there's two billion transactions that are allowed to be older, and two billion transactions are allowed to be newer. So when that row has that transaction ID value, it's, yeah, so when vacuum runs, it goes through and marks rows, either as, it marks them as what it calls frozen, which means it sets that transaction ID value to something that makes it so that row is visible to everything in the future. So that, there's a special reserve frozen ID value that when you run vacuum, it sets those rows to, so in the future, all transactions can see that data. So what the auto-vacuum freeze max age does is what's that value I told you about before. Once a table has any row inside of it that reaches the value of auto-vacuum freeze max age, auto-vacuum immediately kicks in in a higher priority version of itself to try to force that table to reset its XID values so you don't run into, you don't run into two billion, because if you actually run into whatever the max value of 32-bit is, it's 2.4 billion something, your database shuts down. So that avoids, because if Postgres was allowed to run after the wraparound happened, it would start seeing old things as being new and new things as being old and you would have data corruption. So just to avoid data corruption, Postgres shuts down and will not let you start it off in normal mode anymore. You have to start it up in a special mode to go in there and fix that. So this is, it's something, and I will give you some queries that you can run to watch for this and monitor for it, but this is the basic idea of how multi-version curtsy control and vacuum and all that kind of stuff work in Postgres to allow transaction consistency and that kind of stuff. Yes, row base. Row, it's, they're assigned per row, but the number, it's not. Each row has one. Each row has one, yeah. So if you run a transaction that changes a thousand rows, those all get whatever transaction ID gets assigned at that time when that transaction commits. So those thousand rows have that transaction ID value. And so that value's there. There's two billion values older than it and two billion values newer than it that are allowed to allow visibility of those rows. Yes, it's what the vacuum, it goes through and marks, it goes through and resets them down to that frozen value. Either the frozen value or whatever the oldest transaction ID that's visible to everything, it goes back through and resets them to those. So then any future transactions that happen, those rows are visible to all future transactions. Yes, they do change. Yep, so like I said, that's a, until you start like monitoring forward and going in and actually looking at what's happening, it's a little bit, it's a hard concept to kind of wrap your head around, but if you're going to be administering like any production Postgres databases, this is definitely one of those topics that it's very Postgres specific, but it's a very, very important administration topic to try to at least, to at least be aware of it. Cause if you're ever, if you're running a database and a table reaches the auto vacuum freeze max age, you'll go in and watch what current queries are running. You'll see a vacuum running and you'll see it'll say like, normally when auto vacuum is running, it'll just say auto vacuum and give a table name. If a table has reached this, you'll see it'll say vacuum is running and it says wrap around in parentheses after it. So if that causes it, vacuum is running in a much more aggressive mode at that point too. Normally vacuum does normally a normal vacuum run, you really doesn't interfere too much with normal running of a database. A vacuum running because of wrap around prevention can start affecting things, especially when you have all of a sudden have like a hundred tables all reach that value at the same time. That means auto vacuum is going to be working as hard as it can to fix those hundred tables to get back down. And then auto vacuum itself may never actually fix itself. It may be stuck in that constant state of running in a heavy mode all the time. So it's something to be at least be aware of. If you, even if you don't fully understand it, it's something to definitely be aware of. Yes, yes. So auto vacuum freeze age by default is set to 200 million. And that's for most production databases, you'll probably run into that within a few months. It's not a year. So it's a very, very low setting for most production instances, which is why I usually recommend, if you're not gonna monitor for it, setting it to a billion means you still have 50% more of them. It's only 50% of the way to actually being bad, but you still want to monitor for it at that point. Yes, even if it's reaching that, that means your auto vacuum isn't running as often as it should, or the setting is too low. A lot of times for most heavy production systems, 200 million is a bit too low, and even auto vacuum can't keep up with keeping the transaction ID that low. So that's why I usually recommend setting it higher, set it to a billion if you can, and then monitor for things reaching that, and then go in and usually that's the point where you can start tuning auto vacuum to run a little bit more efficiently in other ways. Right, so yeah, he was saying, if you force a manual vacuum to run on a daily basis, so manual vacuuming runs, again, runs with a higher priority than auto vacuum in the background does, it basically runs with the same priority that this wraparound vacuum does. So it runs a little bit more fast and efficiently. That's another alternative, but if you've got a two terabyte, 10 terabyte database running vacuum on the entire database every day, probably isn't gonna happen. It's probably not gonna finish. So, in all honesty, I'm not familiar with the internals of it too well, as Steve, do you? Every time you have to deal with it in some way, they all deal with it in different ways. So this is another note I wrote down on here too. So there's an option to the vacuum command to kind of force it to be a little bit more aggressive and marking all the rows as frozen. And what 9.6 Postgres did is, if all of, so when vacuum is running, if all of the rows in a page happen to be marked frozen, vacuum is allowed to skip over that page. Before 9.5, it would still have to scan through that page to see if it needs to fix anything or it's basically how, yeah. Hold on, it'll go more back. Yeah, yeah, so if you have partition tables or you're not writing to old tables anymore and you want vacuum to be able to, auto vacuum to be able to skip over them when it has to go back in. So even if you're still not writing to a table, the XID value is still incrementing for those old things. So vacuum does still need to run on those old tables eventually, but with newer versions of like 9.6 and later, if it's an old table that never gets any rights and you vacuum freeze it, the vacuum can pretty much almost skip over the entire table. So it's definitely another reason to get up to date on more modern versions of Postgres, is vacuum runs a lot more efficiently. Sure, there's a bunch of internal, yeah, that's, I'm gonna break for lunch here. After this, we're gonna start looking into replication and backups. Which is hopefully another fun topic. But yeah, thank you. Hopefully see you back after lunch. Be back here at one o'clock. I'm sorry, two o'clock. Two o'clock, yeah, be back here now. So thanks for coming back everybody. Still give people a few more minutes, but in the meantime, if anybody feels like they messed things up or feel like they're behind and they wanna catch up at all, I don't remember how we did before since we didn't have availability of downloading the packages and stuff. We restored the VM snapshot to be at a point where we had the packages already installed. So there are two other snapshots I have on here for the VM as well. So if you want, you can go in here and back to the virtual box control panel. And this configured primary DB before replica. If you go in here and restore to that snapshot, you will be caught up and everything should be working to the point where we are in the presentation now. So give people about five minutes or so if they wanna go do that real quick. Or if you need help doing that, let me know. There's still lunch. Yeah, you will have to shut the VM down to restore that snapshot. Like you can see on mine, the restore button isn't available because I have the VM running. So if you shut down the snapshot, the restore button should come up and be available. It's a configured primary DB before replica. This should be the one right underneath PG packages installed. All right, get the two terminals up here again. One is the training user and one is Postgres. We already should have added this line. So we're back to, we're gonna be setting up some basic replication in Postgres now. The first thing to do is you have to have a user defined, a role defined that's gonna be doing the replication for you. Technically any super user could do it. Any super user could be used to do replication. But it's best to have a user dedicated for that because there's also, remember when we were creating roles, we specifically gave our replication role the replication feature that allows that role to be able to do streaming replication but doesn't give it full super user privileges to the database. So you could log in as that replication user but not be able to do a whole bunch of other stuff. So it's best to make a dedicated user for replication. Call it whatever you want. I just keep it simple and call it that. But for replication users, there is, so if you remember back, actually I'll just open up here. This is the VGHBA.com. So we already have this line in here which is what the one I wanted to do. So you look at what these lines mean. This is also the type, the database, the user, the address, the method. So if a user is gonna be used for doing replication, there is a dedicated, it's like a hidden database inside Postgres that a user that's gonna be doing streaming replication will be connecting to. So what this is saying is basically give the replication role access to connect to the replication database is what it is basically what it's doing. And it's just doing it over a local PCP IP is all it's doing. So we have that, make sure that line is in your PGHBA.com. And then what we're actually gonna do is remember I talked earlier, talked about creating replication slots. So what that does is it makes it so the, excuse me, the primary database is aware of its replicas and where in the wall stream it is. And so if that replica disconnects, it'll keep the wall stream around for that replica so it can reconnect and not fall behind. So the way to do that is to run this command right here and you can name it whatever you want. So let's go back out here. And this command does need to be run by a super user to create these slots. So either as a Postgres user or as the training user that I'm logged in as now. So we'll do that. And then it just returns back the name of the slot that it created. And if you wanna see what slots are available, there's a special view. So here's another trick for, see how that looked kinda ugly when it returned back there. There's a backslash command in PSQL, backslash X like that. It turns what's called expanded display. What it means is if the data is gonna wrap like that, it instead gives it back to you like that in a vertical column looking things. So these are still the column names over here and these are the values over here. So this is the slot we made. It's a physical type slot. There's also logical type slots for people that are looking at a logical replication. There's also temporary slots. I won't get into that right now. We can also see that the active is actually set to F. So this is not an active slot. So what that means is we created the slot but we don't have any replicas around for it that it's aware of. So it's not gonna be holding on to any wall files around because there's none that it needs to keep track of. So if a slot is an active, that tells the, and say if you don't wanna completely delete the slot to stop it from holding on wall files, you can go and set the slot to inactive instead. So in this instance right now, we just have one inactive slot. Yes. So he's asking what F and S just meant to fall. So yeah, it's a Boolean column. So that's either true or false. So the command we're gonna actually use here is something called pgbasebackup. This is used to take a, what Postgres calls a base backup. It takes all of the data files as they exist and also some of the wall files along with it so you can stand up a whole new database. So it's basically take copying all the data files so we can make a replica of the primary. So this command actually needs to be running this as a Postgres system user. So I just wanna make sure you're the Postgres user right now and not the training user because what this is gonna be doing is gonna be creating a new folder in that same, like where the other data directory was. It's gonna be making another folder called replica. So and I'll go over here, I'll go over what all these things in this command mean here but we can go ahead and run it. So it's asking for, so remember before we're connecting as a replication user we set a password for the replication user before. I just set mine to password. So this obviously we don't have a very big database so it went very fast but if you have a big database it's got this progress meter here if you do. So actually let me just go over what the flags are here. So this is telling us it's connecting over TCP so it's connecting over to, with eight so this is the host so this is whatever IP address or URL to connect to your primary. So normally you would be running this on whatever your replica machine is. In this instance our primary and our replica are the same machine in this case. So normally this command is what you'd be running over on your replica machine. So it would be pulling this stuff across the network. We're logging in as a replication user. We're not actually setting what database we're connecting to because we're just logging in as, remember before if you don't tell it what database you're gonna log into it assumes it's gonna be something that matches the user that you're logging in as. So our user's name is replication so we don't have to set that. Dash D is the location of where this replica is gonna go. So if we go back here, so this is where our primary database was, was in this location. So I basically just made another, if this folder doesn't exist and the user you're running it as has permission to make the folder which it should in this case, it will make it for you so you don't have to set this up ahead. If you do make this folder ahead of time it has to be completely empty. There can't be a single file inside of it. So we made this replica folder and one other important thing to know is that the data directory must have only read, write, execute permissions by the user. It's not allowed group or world permissions as well. So it sets all that up for you. What the dash R command does and we'll get into this file in a little bit. Whenever you're making a replica there's this file called recovery.conf and that has the information for the replica so it knows how you're going to replicate from the primary. So if you give the base backup command the dash R option that makes that a basic recovery content file for you. So just make one step a little bit easier. The dash XS option and this actually is the default in Postgres 10 now. It was not the default before. So if you're on an old version I highly recommend setting this. So what that does is so while the database is running obviously you're going to be doing this on a live database that's actually running generating wall files as you're doing this. In order for the replica database to stand up properly it has to have all of the wall files that were generated during the time that you were running the base backup. So that the replica can replay those wall files and catch up and be caught up with where the primary database is. What the XS, there's actually two, you can either do XS or dash XS. What S does is it makes a secondary connection back to the primary to pull all those wall files down while they're being generated while you're copying all the data over. So when the base backup is done it has all the wall files it needs to be able to start up. If you don't copy those wall files if you just run base backup and you don't have the wall files that are generated during that time that the base backup was running you won't be able to start that replica up. Whether it's a replica or a restoration of a backup or anything you won't be able to start it up without those wall files. So what X dash XF does is it waits until the end of the base backup to go back and grab all of the wall files that it needs to start up. If the primary started recycling those while you're running the base backup you don't have all of the wall files you need to start your database up. So, but this dash XS option needs an extra member of max wall sender setting that we did before. It needs an extra one of those connections. So this actually makes two connections back to the primary database. One to pull all the data files over and one to pull all the wall files over. So that's another reason we usually I usually try to set the wall senders and replication slot numbers higher than they need to be to run a normal database. So it allows you to do things like this. The dash P option just tells it to show the progress. So if you do dash P, that's what gives you that little, where are we back here? That's what gives you this little thing that'll do a running count of how many bytes. It actually shows you a lot like how many kilobytes or megabytes are coming over and the percentage progress it is. So the P option is definitely useful. The dash S option and if you're running an older version of base backup does work on older versions of Postgres but only nine four has replication slot support. So if you want to have, and actually I think PG base backup I think doesn't even support itself doesn't support slots I think until nine six. So this dash S option is a very recent option but basically what that's doing is it's telling the primary that I'm making a base backup and I'm going to have it use this slot so make the slot active immediately. So start tracking on the master where this replica is from this point forward and that means it's going to start saving those wall files. If you don't do that and you wait like a day to go set up your replica, the master's not going to keep all those wall files around anymore and you won't be able to start it up. So this is turning the replication slot on so that the primary will start reserving those wall files until the replica actually connects and starts pulling the wall files over and then the master can start cleaning them up when it doesn't need them anymore. And then let dash V is just verbose to make it more verbose. You can tell where it is along the way. That's all that is. So those are all the basic options for base backup. Base backup will actually be using it again later to do backups as well. So it's a very versatile tool. It's the use for both both setting up backups and for setting up replication. Yes, right. The HPA, you probably actually want to set that on both because if you fail over, you want your new master to be able to connect back to your old one so you want to have that set on both of them. This one where you're set creating the slot, the slot is created on the master system and then the base backup you're running, you're telling, I'm making a replica over here. This command is being run on a replica. So you're pulling it across from the primary to the replica. So yes, thank you, good question. Let me, one second, you only want one more command here. So remember before how I selected this and it wasn't active? To actually run this again now, oops, why didn't that? Oh, it's not active, so because I don't have a replica connected. So it's not actually being used yet but it is starting to now track where in the wall stream, this replication slot is tracking. So from this point on until this replica connects, any rights and stuff I do to the master to generate wall, they're going to be saved until this replica connects. So there's another question somewhere. Yes, did you even create the replication role? Yeah, that's here. So yeah, back here on this one, we created this create a role called replication with those two properties, with login and replication. And then go ahead down here, log in as any either training or postgres do backslash password, space replication and set a password for the replication user. I just made it the word password in my case, so. No, you're doing it. So the base backup is run on the replica. When you're setting up a replica, you run the PG base backup on the replica system to pull the data over to the replica system. A little bit, not much, but a little bit. It's going to be reading a lot from this, it's going to be pulling that traffic over the network. So yeah, it does have a bit of an impact, but hopefully it's not a significant amount of impact, but it would be something you may not want to do at the peak of your activity during the day. It may be something like a weekend operation to set up a replica just in case, but it shouldn't be, I think the bigger impact will probably be on your network more than the master system itself. It doesn't do any locking on the master at all. Yeah, so everybody, cut up? Anybody need help getting to where we are right now? So now, let's get the replica started up. So obviously we're on the same system. We're running these on the same system. We can't have Postgres running on the same port. The default Postgres port is 5, 4, 3, 2. It's a pretty easy one to remember, but obviously we can't have a second, oops, can't have a second system running on that port. So everybody make sure and do this, make sure you are in the replica folder because we're gonna be editing the Postgresql.com file in the replica folder. So just on the command line, do PWD and make sure you are in the replica folder and then go into the Postgresql.com file. I say that because I edited the wrong file many times myself going through this training session to make sure things are running right. So, and we're actually gonna go down here too. It's right above where the max connections is set. This is where the port definition is. So in this case, I'm just gonna make it 5, 4, 4, 4 and then save that. The other file is the one that the dash R command did for us automatically. So normally you'd have to go in and create this recovery.com file yourself, but if you're just doing the base backup, doing the dash R command sets up the basic stuff for you. For a replica system, you're gonna wanna have, it's standby mode is on because it's a replica. The primary con info is the information you have to give the replica of where the master system is located. So in this case, the base backup was, we gave all these parameters when we ran PG base backup. We gave it the user it's connecting as. We gave it the host name, the default port. It just used the default port. We gave it the password when we typed it in. So it knew all these things and they needed to add into this file. Otherwise you'd normally have to add all this stuff in. These other things like the SSL mode and all these other things, don't worry about those. They're just things that it put in there automatically for you. They're kind of arguably optional at this point. And this also defines this rep, it tells this replica which replication slot we're gonna be connecting to. And it's actually also one other option. We don't really need it here, but I bring it up to add it in here. It's this option called recovery target timeline. So we'll see, I'll show this to you later when we do a failover. So the way Postgres knows, the way the primary and the replicas know that they're in sync before and after failovers is Postgres keeps track of a thing called a timeline. And when you failover, it increments that number up one so you're on a new timeline when you failover. If you have multiple, say you have one primary and like four replicas, you can have all of the replicas, you can use like a DNS or a hostname. So the primary always has the same hostname even after a failover. And what this will do is if you failover one primary over to one of the replicas, the other replicas will automatically connect to the new one and pick up and be replicas of the new primary without having to go do anything to them because you tell it, you always wanna be on the latest timeline. You can actually give it a specific number if you wanna be more specific about what is being going on, but that's a really, really handy option to have in there if you have multiple replicas with a single primary. And that only works if you have something like DNS or a C name that, so say you have your master O1 system, you failover and you failover in the IP behind master O1 changes, so then all the replicas know, they just connect to master O1 behind the scenes, everything is all magic and everything just keeps working. So if you don't understand that one, I'd recommend looking into that one a little bit more, but that's definitely a really handy feature, especially if you're running multiple replicas that's a very handy feature to know about. He's asking, do all the replicas have replication slots if you configure them? Yes, and they'll all have their own slot. So this is just telling it an overall timeline of which stream of the wall to be following. Yes, yes, it doesn't need to be manually configured. Yes. So yeah, you don't wanna have multiple replicas all using the same replication slot. I don't even know if that, I don't even think that works. So you can't have more than one connecting to the same replication slot, but even if you could, you wouldn't want that because if one goes down, you want the other ones to keep working without any issue, so. So that's the only change we really need to do to this file in this case because we use that dash R option to make this for us. And you don't really have to do that change in this case, it's just, I wanted to bring that one up because it's a handy feature to know about. So let's actually set up a new server or a new service. So this is a method specific to CentOS, specifically the CentOS 7 with system D. So we're actually gonna be creating a new system D service for the replica on here. So the easiest way to do that is to copy the service file that already exists for our primary. But if you go in here to the system D rules in the way it likes to work, so things are in system files that exist in user lib are the ones that are controlled by system packages. So this is the service file that the Postgres package created for us. If you're going to do custom services, it's recommended that you don't put them in user lib that you go over here and put them into Etsy system D system. So that's where we're gonna be putting our replica service file and if you go and read the system D service file, the way it works is system D will go, it will read Etsy system first before it goes read and reads the user lib one. So if you have things overriding other services, that's kind of the way it does things in a priority like that. So we're going to go here as, we have to do this as a training user because we need pseudo privileges. So Postgres, so that should copy that file over. And now we're going to edit the second file. Make sure very carefully you're editing the second file here, the replica service file here. And you'll have to be, you have to do that with pseudo or as root somehow. Oops, did I open it? I did something wrong. Oh, one too many commands in there. Okay, so in this file, there's only one thing you have to change. It's, so this is relatively pretty easy. Go down here and there's this line right here. We're setting the PG data directory. So that you can see that we copied this from what the master was. So that's where the master data directory was. All you got to do is go in here and change data to replica. And that's it. Sure? Yep, the file is so up here on this command, this copy command, it's the second file in the copy command. PostgreSQL 10 replica.service file. Everybody good? You have a question? Okay, I can wait a minute, that's fine. Okay, I'm actually gonna take a little bit, take a little bit of a break after here to talk about what's going on as well. So we edited that file. So now we have to go and tell, this is something to tell system D that we have a new scan all the configuration. Whoops, that's not good. Next thing up, my copy is between web browser and terminal, so this is the demon reload, is a command to system D to tell it to go through and rescan all the configuration files again. If you try to go right to this enable command here, system D will yell at you to go run the demon reload command because it sees new files in there, it kind of knows that they're there, but it wants you to manually go and run the demon reload command. Again, you have to do this as the training user with sudo. And these next commands you look familiar, these are the same commands that we wrote or we used to initially start up our Postgres service, except now we're getting at this new replica service name here. So there, should come back, not give any errors there, just tells you that it's doing its own internal thing to enable the service and then start, then get anything back here and then status. Hopefully should see green there and the way you would go and then check this to see if the first thing you usually always do is I go to log files on the replica, you look for this line. So actually go back up here. It's this started streaming wall from primary at that's a wall location, timeline one. Remember how did the recovery target timeline? It's telling you it's starting up on timeline one. So that's one way to know that your replication was working is to go check the log files. The other way you can check and make sure replication is working as you can run these commands. So if my replica is working from the primary database, you run these. If you get anything back from this command, it means you have a streaming replication replica connected to your master. So there's nothing in here to really look and see if anything's good or bad. This just lets you know, okay, I have a replica and for every replica you have connected, there will be a row returned from this table. The other one we can do is we can run that command we ran before and look in the slots section. Now we can see the slot is now active and the PID of the replica that's running right there. And we can also see that this number has also started to change as well because wall files are getting written. So I'll take a little bit of a break here and let people catch up. But those are ways you can tell. The other way you can tell is to go write something to the primary that wasn't there before. So we'll create a table and insert some data, do it again. What's that? Yeah, I just throw some data in there. Just trying to get to LSN to go through it too. So if we go back and check the replication slot again, you can see that LSN, that's just a, what's that stand for? LSN logical log sequence number. So it's just a number of tracking the things that see it's going up. But now if we go back and connect, so our replica is running on port 544. So you can tell PSQL to connect to 544. Actually, we can just see the table there. We have a table there. So what's that? Yeah, so many, many different ways to make sure your replica is working. So I'll let people catch up here and try whatever means you need to do. It's asynchronous by default and don't change the synchronous unless you really, really need synchronous and you understand what that means. What that means is if the replica is not available, nothing can write to the primary. No, that's not what synchronous, when you're telling it to be synchronous, you're telling it to be synchronous. So there's, yeah, you can set up more than one. You can have multiple replicas, one be synchronous and others be asynchronous and or have multiple synchronous replicas. And as soon as, and if you have multiple synchronous replicas set up, as soon as one responds, that's a default. As soon as one responds, it'll let things continue. But you can tell it to have like a quorum. So I want at least two of my replicas to respond before the master says things are fine. So if you have a replication slot set up, if you don't have a replication slot set up, the primary doesn't have any idea what the replica is doing. So that was one of the reasons replication slots were brought up so it would make those kind of problems easier to handle. So the other way that used, the way that kind of stuff used to be handled was, remember that I gave you the archive command where you could copy wall files off to another location. So what the other thing, now you'll see this a little bit later, you could set up the primary to copy the wall files off to another location. You could set up the primary to copy the wall files off to another server somewhere. So, and then they'll stay there forever. And then you can have your replica read those wall files instead of connecting back to the primary or do both. Read from, connect to the primary. If I can't connect to the primary, read from these wall files. So that's the way you can do, and we'll get into that a little bit later when we get to backups and stuff. It's what's called point in time recovery. Or it's what allows point in time recovery where you can actually tell Postgres, I want to restore my database to this specific transaction number or this specific time. So, and that's what having the wall files allows you to do. Any other questions? I'm sorry, I can't hear you. Anybody else need assistance? So yeah, the question was what kind of replication this is. This is a cluster or instance-based replication. This is all or nothing. It's replicating the entire database over. No, not one database, no. Yeah, in this case, there is logical replication, which is a whole other talk. Yeah, there's no automatic failover, but we'll actually do a failover at the end, and we'll see how to do it. Yeah. Yeah, I actually have a whole series of monitoring queries I'm going to go over that kind of gives a little bit of insight and a lot of stuff too. So, yeah. I'm sorry, I couldn't. Select PG is in recovery. That tells, if you run that, that tells you whether it's a replica or a primary. So, if it's true, it's a replica of it's false, it's a primary. So, it's a way within the database itself, a very simple binary way to say, is this a replica or is this a primary? Yeah, so if you have some kind of failover script that you want to know, is this a primary or a failover? If it's a primary, do this. If it's a failover, do that. It's a simple binary way to know about that. Yeah. I'll go over some monitoring queries for replication a little bit later. He's asking how do you know the replica is caught up? There's some monitoring queries we'll go over in a bit. Just give a little bit more time for people to catch up. This is another handy little query that I use a lot in here. So, I was putting data into the table I just created. That basically just tells it to, I made a thousand rows in the table. So, go select star from public. So, it just, I tell it to for the, so I said insert into testing for the ID column, generate values of one to a thousand. So, that was a quick way to just make a thousand rows in the table. You can do other things that generate series of like time. You can like say make five columns for every day, something like that. So, generate series is the handy little command for doing quick dummy data. Generate, it's a function. It's a function called generate series. So, in this case you just tell it one to a thousand and it makes one to a thousand for that column. Yeah. Yes. You can, so he's asking about, he's talking about writing to the replica. You can't write to a replica. Yeah. So, yeah, if you actually, I'll show you what happens. Mind's on. So, where's our generate series command? There we go. Cannot, in a read-only transaction, because it's in read-only mode. But you can read from it. Because we turned that hot standby setting on before, so I can, I'm on the, so here's a select PG is, and was it PG is in recovery? Yeah. Recovery. So that comes back true. So I am on the replica and I can read from that table. I will say this while I'm here, you don't want to have your read-only replicas be your failover system. Mostly because the way Postgres works is it's that whole NBCC thing, NBCC thing where it knows certain transactions are a certain point. And the replica has to keep up with that on the primary. There's ways you can, so if you have the primary, you always want the replica to be always caught up with the primary and you start running long running transactions on the replica, the replica can force those to stop and force those to cancel because it needs to catch up with where the master is, that all the data is gone. Well, you can tell the replica to wait and not replicate and let the queries finish, but then if that's your failover and all of a sudden your master disappears when you've told your slave to wait a half hour for that query to run, you just lost a half hour of data. So if you're looking to have read-only replicas, which are a great idea, but you also want to have failover, you want to have two different systems for that. So not everybody can do that all the time, but then you just kind of have to know the trade-offs of what that means. Yes, what do you mean when the replica goes down? Right? Yeah, he's asking about if you have your queries, you have reporting queries running on your replica and your replica goes down, you want them to go to your primary, that's something you'd have to set up yourself to reroute traffic. Yeah, that's not something close-grass is gonna know to do for you. Yes, yes, yeah, if the primer, if you failover and you had connections on your primary, it's not gonna automatically reconnect them to the replica. What it can do is if you set your network up right with like a load balancers and connection pulling all that kind of stuff, the new connection will automatically go to the replica, but it can't continue that same query that just fail on the primary, it can't just go continue that query on the replica, it'll have to reconnect and start it over again. Yeah, but that's something you have to set that up at your network level that if your failover happens, change the DNS, change the load balancers, change your pullers, do whatever you need to do, but there's definitely ways to do that, so yeah. All right, move on here a little bit. Get up to backup. So we have our first replica set up. That's really all it takes to set up a replica in Postgres. No matter how big your instance is, you just do those same exact things and that's how you would set your replica up. It'll just take longer, but that's really that easy and that's really been since 9.0, which is quite old at this point, but before 9.0 there was no built-in streaming replication. You had to set up those wall archiving commands to copy wall files over to other systems and let them replay it. Since streaming replication has come around, replication in Postgres has become this easy, so it's a lot better than it used to be. Yes, and yeah, there's still reasons to set up archive commands. We'll be going over that in a little while, but it's not something you have to do to set up replicas anymore. If you just need to get a streaming replication set up and running, you just want a one-off replica to run some reporting queries and stuff on. You can do that here. On to backups. These are the basic backups that have been around in Postgres forever, which is basically the same thing that exists in MySQL and Oracle as basically the dump commands. This is just dumping all your data out to flat files on the file system. So there's actually two dump commands in Postgres, and they do two very distinct things. So pgDumpAll, it always dumps in plain text format and it dumps, if you run it with no options, it dumps everything, it dumps the entire instance. So this is for doing, you just want to back up the entire cluster all at once in one command. This is how you would do that with the pgDump, and you want it to go to text file. You don't want to do binary. You want a dump file to restore later. I'll go, what's that? It's a consistent backup. Yes, it's a consistent backup. Yes, but the one thing that pgDump is, so honestly, there's very, very rare occasions where you'll actually use pgDumpAll to back up your database. It's very, very uncommon to actually use it for that. But what it is useful for is, so we're talking about the distinction between the instance and the databases within the instance. The way Postgres works, there are cluster-wide objects that are relevant to the entire cluster or the entire instance. Those, the two big ones are roles, which is the roles we logged in, and table spaces. The roles themselves don't exist in any individual instance in the database. They exist for the whole cluster. So if you back up a single database in the cluster, you're not backing up the roles. You're backing up the permissions for those roles on those objects, but you're not backing up those roles, and you're not backing up the table spaces because those are cluster-wide as well. That's what pgDumpAll is actually still useful for, and you can actually give it the, there's two flags for pgDumpAll. DashG grabs all of the global objects, which is all the roles, all the table spaces, and everything. Or you can actually, if you just want the roles and you don't want anything else, you can do the dashR command, and that'll just dump out the role information for your cluster. So we're actually gonna run that command now. Run that as a training user, and that's it. It's very small database, so it's very, very quick. But if you actually look at what the internals of that, it's a plaintext file. So it just has the create role statements in there, and it has hashes of any passwords that exist. So it doesn't actually put clear text passwords in there, but those hash passwords can still be used to log into the database, so don't go committing this on GitHub or anything like that. So wherever you tell it, so it's wherever you, the command that I ran, the dashF option is telling it where to create the file. So it's just, it's making it in the home directory of the training user. Yes, it's not that it's reversed, it's not that it, so you say MB5 can't be reversed, it's not that it's reversing it, it's just it's matching the hash. So that's how Postgres authentication works. It matches the password hash, so yeah. Not anymore, yeah there's a new scram authentication method that fixes all this stuff, but yeah, so basically don't commit this to your repo, but this is what you do for public repos, but it's like any backup, protected as you would any backup of your database basically. So the other backup command is just pgdump, and this is for doing, this is primarily what you're probably gonna be, if you're gonna be doing a dump of your database, this is the command you're gonna be doing. Mostly because it backs up, it doesn't back up the whole cluster, you have to tell it which database you're gonna back up, but it backs up individual databases in the instance, and like I said, it doesn't back up the roles or table spaces, but it does back up the privileges for those roles, so if you look at a dump file, it'll have all the grant and revoke commands and all that kind of stuff for those tables. And what this also provides is, so what pgdump all does is a flat plain text format, and if you dump out a terabyte with pgdump all, you're probably gonna have a pretty large text file which is nearly useless to use for anything, and then if you wanna restore that back in, you have to restore the whole thing all at once. So what pgdump provides is a binary dump format, which compresses it, which compresses the file, and also makes it so if you use pgrestore, you can take individual objects that you wanna restore instead of having to restore the entire thing. If you restore pgdump all, you're restoring all or nothing. If you do a binary dump with pgdump, you can pick specific objects you wanna restore. So, well, we'll go over file system level backups, and those are file system level backups or you're gonna be your primary thing you're doing most of the time, but pgdump is still useful for maybe once a week kind of a thing that if you accidentally drop a table out of your 10 terabyte database, you don't have to restore a 10 terabyte backup just to restore one table. You can go to your dump backup and just restore that one table and then you'll be good. No, he's saying it doesn't lock. This doesn't lock anything. It doesn't lock reads, it doesn't lock normal operations. It will, what it was, it can lock, it'll block dropping tables and it'll block certain DDL commands from changing the schema, but it won't block normal read and write operations of the database. Thank you, Steve. Yes, I will show you. I will show you how to do that, yep. He's asking about doing the restore, which we'll get to in a second here. Yes, he's asking if the dump ball and the pgdump don't have to be done atomically. The cluster-wide data is pretty, it's pretty small, it's just the users. So I bet I mean if you go and do a pgdump ball, create a user and then go pgdump, then you're inconsistent because you may be missing permissions and stuff. So that kind of thing, you do have to be a little bit careful of. But if you're worried about that kind of consistency, we'll get to the file system level backups in a bit that's primarily probably what you're gonna be doing. But the other, so like I said, this can be, you can actually restore a schema as well. You can just tell it, give it a schema and it'll restore all the objects that are in that schema as well as individual tables. And this is actually a really interesting option that was added more recently. So the way, is it the section for the dump or was that the restore? I think that's the restore option. I think that's actually a restore option with the dump option. So we'll get to that. So we'll get to the restore then. So down here at the bottom, we're actually gonna run the pgdump command. So by default, when you run pgdump, it does, like pgdump ball, it does a plain text format, which is not really what you usually want to do in pgdump instance. So there's the dash capital F option or dash dash format option tells it what format you want to dump, do the dump out in and see it's for custom binary format. So we actually want to do that binary dump option. And I usually give the dump option the dash V because then you can actually watch which section of the dump it's on and just kind of keep track of the progress of the dump on a bigger database that can take a while. So everybody caught up with running the dump commands. What's that? Sarah, can you? Thanks. Anybody else? Pretty much. And then when you give it the custom option, it's a binary format. So it puts extra things in there. So the restore command knows how to use it. Well, remember how that whole MVCC thing works? When the dump starts, it has a consistent snapshot of what things are. It'll, the dump will look like what the database looked like when you started the dump. So anything that happens after the dump starts won't be in the dump. Yeah. If you actually open the training PGR, you can do that. It's mostly junk, but you can see some plain text stuff in there. So now do PGRestore. And we're actually gonna restore the database. We're not gonna be doing this as, so this is kind of a way to show you that any user on a system can run a PGRest database. You don't have to have pseudo privilege. If you wanna be effecting the production systems and stuff, yeah, you may need pseudo privileges, but if you just wanna start up a PGRest database as your own user in your own folder, that's basically what we're gonna do here. So just as a training user, we're gonna make a folder in the home folder called myDB. And remember, like I said before, Postgres expects the top level data directory of any place that's going to be a Postgres database can only be read, write, execute by the user. If you have any global or world permissions on that folder, the cluster actually will not start. And it'll tell you bad permissions on the data directory folder. So we wanna make that data directory only, so changing the mode of the file to only be, so you can see the rest of my directory. The directory's in the home folder are all kind of normal, but we made this one only usable by our current user. And I see, yeah, that's a whole other talk. We can talk about some other time. So, and now, so the command we used to initialize the database before was like a special initialization for the package that CentOS provided. This command here, so these are the binaries that Postgres comes with. So they're an user, pg, SQL 10, and oops. So any Postgres database you install from source for whatever, these are a lot of the binaries that Postgres comes with. So the binary that Postgres comes with to initialize the database is called initdb. That's kind of where the other file got its name. And when you wanna initialize the database, you give it the dash capital D and the path to the folder that you wanna initialize the database in. The whole bunch of stuff flies by your screen, but it should come back with a, should come back to your prompt. And then at the bottom, it gives you a command for actually starting this cluster up. But again, this is a basically a clean, brand new initialized Postgres database. Never been started up, never been connected to. If we go in there, into that folder, oops. You can see there's a pghba folder file, a Postgresql.conf file. The thing is, that's a default Postgresql.conf file. So guess what port it's set to? The default port, so we can't run another database unless we go in and change the default port again. And actually we have to change, we actually have to change two things in this case, just because of the way the Postgres was compiled for CentOS. So in the Postgresql.conf file, go down to port. And what am I setting this one to? So just 5888. And since I'm, the training user does have pseudo privileges, but by default, it doesn't run things as root unless you do pseudo. So we're not gonna, we don't wanna start the database up with pseudo, we wanna start the database up as us. If you try to start the database up, it tries to create a local socket file where the system has permissions to do that, which this user doesn't have. So just copy and paste this option here. You can see it's actually commented out. And you can see what it tries to do is it tries to create the socket file in that far run Postgresql folder. Only the Postgres user has access to that folder. This training user doesn't have access to that folder. So if we wanna allow the local socket connection, we just have to set that to our current data directory. So just copy and paste that one in there if you're not sure what's really going on there, just trust me, you need to do that. And then you should be able to start up the database with that command it showed us before. Yep, server started. So there. So remember the other thing I said, when the system created the Postgres instance for us, it automatically created a Postgres role because the Postgres role, but the Postgres system user is what created that instance. This case, the training system user created this instance, so it automatically made a training role in the database. But the only database that exists in this default one is Postgres. So if you actually wanna connect to it, you have to give it the, so psql-p5888 says, oops, I started it up. Oh, that's right. Did tell it to log in with TCP, not go host. So it's trying to find the local socket there where it doesn't exist. So if you give it the host directory, so the training database doesn't exist because it created a Postgres database. So what we're actually gonna do here is we're gonna restore our backup. So first, you have to restore, if you're doing dump restore and backup, you have to restore the users before you restore the objects. If you restore the objects first, it'll create them, but it'll just throw a whole bunch of errors because it can't grant the permissions because the roles don't exist. So what we wanna do first is restore the global information. So the table spaces and the roles, and that's what this command is doing here. And I turned, was it, I did the little dash A option at the end just tells it to echo back all the commands that it ran. So you can see it actually threw an error here because the training role already existed, but it just skipped past that and keeps going. And then, so was it, somebody asked the question that you wanna be able to restore the database to a different name from what the dump has. So the dump file, the dump file is aware of the database that it was in, but the way the restore command works is you have to give it a database that you wanna restore to. So normally you have to go in, you have to either, you have to go in and create the database first before you restore into it. There is an option dash capital C. If you give it to PG restore, it'll restore the database that it was dumped from, but in a lot of cases when you're doing disaster recovery, you wanna restore the database to a different database than you did the dump from. So that's actually what we're gonna do here. So we actually wanna connect to, we wanna connect to the new instance and the, let me just do dash D to be clear here. We're connecting on local host port 588 to the Postgres database, and we're gonna create a database called new training. So it's not the same database we had before. And then the PG restore command is down here. And basically all you have to do for PG restore is just say PG restore, give it any flags you need to so it knows how to connect and then just give it the file that you want to restore into the database. So in this case, I'm connecting to the new one that I did. I'm connecting to the new training database and restoring that dump file that I did. And we should be go. And if we connect to new training, there's our table that we had before. So I'll wait here, let people catch up. Or if you have any questions, I'll go back to the slide here. I know I moved a little bit fast here. I just wanted to get through everything. So in case anybody had any questions along the way, I may have covered him, but I'll wait here and let people catch up. I'm sorry, I can't hear you. Well, so she's asking about where the data file location. So I created a brand new cluster from scratch in this MyDB folder. So this has nothing to do with the old data files and the dump, when you do a dump, it doesn't care about data files anymore. It doesn't care where they are or anything. All it cares about is the objects themselves. The paths to where all that stuff is isn't relevant for a dump. The dump backups are just worried about the objects in the database themselves. The only place that does matter is on that global section about table spaces. Table spaces have specific paths because they have to know where on the file system they live, but that's more global information. So that is one case where you do have to worry about file paths, if you use table spaces. But you don't have to restore a table back to the exact same table space it was in before. Like the PG dump file doesn't care about the table space. You're just telling it which database you're restoring it into. Does that make sense? Okay. Well, in the case of PG dumps and all that kind of stuff, you would be setting up the cluster and the table spaces all beforehand before you even got to this step. So you would be defining the paths to all those table spaces and everything as part of the initial setup of the cluster before you even got to this step of putting data back into it. Not in, like I said, in the case of dump and restore, that doesn't matter. The only place that matters is that global restore where you're restoring the table spaces. That matters there. In the case of just restoring the database with PG restore, the paths don't matter. You're telling it which database to connect to that database already exists. You're telling it to put the data in that database. All of the paths and all that thing are all taken care of. I think I may know where you're going. We'll be doing some file system level backups later and that's where things start mattering is where the paths of that kind of stuff is. When you're doing, you're backing up the entire instance and the whole cluster all at one time. Yes, the PG dump does specify which table spaces the objects live in but you can set which table space the restores are going into as well, I think, right? No? Yeah, yeah. You can store tables in the right table space or whatever. You can exclude table spaces. Yeah, that's what I was thinking. You can exclude them, yes, yeah. Yeah, yeah. So you could essentially tell the PG restore to ignore any of the table space information that was in the dump file and then that won't matter. It'll just dump it into the default table space. Any other questions? Anybody else need any help getting caught up with where we are? Yes, okay. So the way I did the way, so he's asking why didn't go creating services and stuff for this instance. When you're setting up a replica, that's something you want to be starting up when the database, when the system starts up. You want that database managed on a service level to the operating system. This, I'm just doing a restore of a database into my home folder, just because that's where it's convenient for me to, this is a thing that I've run into a lot of people wanting to know. I have a production system and I just want to start up a database in my own laptop to start it up and run it. And this is a very simple way. You can just do it in your home folder. You don't need service level permission. You don't need system-wide service level permissions to just start up a Postgres. You need the permission to start up a TCP SOC and all that kind of stuff. But it's all local inside your system, inside your home folder where you're doing it. So does that answer your question? Right, yeah, right. So in the case of the replica system, you could essentially initialize the database the way you did on the primary and then just delete the data in the data folder and then run the pgbase backup in that location on your replica. And then basically your primary and your replica are exactly the same. The file paths, everything are all exactly the same. Yeah, I didn't want to... It would be nice if I could run... And I really wish I could. I would have liked to run multiple VMs and have them all connecting to each other. But this was a challenge enough, I think, to try to get a training session going. But it also... I think it also gives you a better idea of how if you're trying to run the same service on the same system, you have to have a better understanding of how all of the things work so they don't collide. So it kind of gives you a better insight onto that kind of stuff, too. So anybody else have any... Or need any help with getting past this point? Okay. So benefits of doing the pgDump and pgRestore is the backups are significant. As you'll see if you ever run this file system backup, these backups are significantly smaller. The main reason being that your data exists in these dump files, of course, but only the index definition exists. Your indexes don't exist in this dump file. Only the definition of the index exists. When you're running the restore command, it's actually recreating all of the indexes. So these are actually... pgDumps are good, but they're not good for disaster recovery because if you're trying to do a restore on a 10 terabyte database, you're probably gonna be waiting a few days because it has to rebuild all the indexes, has to rebuild all the constraints, all the foreign keys, all of that kind of stuff. And that's where this option I had mentioned that should have been actually on the restore. So when you run a restore, it actually, the way Postgres does things in stages, first it goes and creates a schema, then it goes and adds the data, then it goes and validates constraints, and then it goes and creates indexes. It does things in stages like that. So you can actually tell it, if you only wanted the data, if you actually just wanted a data restore, you didn't want it to rebuild all the indexes and validate all the constraints, you can actually tell it what section up to you want to do the restore to, so you only get your data. And then you can go make any indexes you need, you don't have to recreate all of your indexes and all that kind of stuff. So that's a handy new feature that was added in a more recent version. So like I said, yeah, you must recreate all the indexes and constraints. And now we're just gonna destroy, we made this nice new database, now we're gonna destroy it. So we're gonna use the same PG, so the normal binary in Postgres, so we're using systemd to start and stop the service level Postgres stuff, but if you just wanna control Postgres itself, what the commands it comes with, there's the PG underscore CTL command, and you can actually use that command to start and stop the service, it's just you'll make systemd mad, cause then it won't know what's going on behind the scenes, so don't do that. But in this case, this is our little database in our home folder, and more recent versions of Postgres, so actually let's move out of that folder there, it should just stop. So there's this extra option that we actually didn't do when we started the database. So when we started it, all we did was say start-d and then the location. This mode option, if you're running older versions, I think, Steve, do you remember when it changed to default past? Was it 95 or 96? Yeah, so there's different modes. So what are the other modes? Besides fast, immediate, and remember, I can't remember the other one. So there's basically three different modes. What used to be the default one would actually, before the database would stop, it would wait for all connections to disconnect. So if somebody was running an active query, that old default way of stopping a database wouldn't actually stop the database until that person just stopped the query and disconnected from the database, which was not good. And it used to be the way the default way, a lot of services and stuff Postgres would run, and you would sit there and wonder, why isn't my database stopping? It's because it was doing it the slow way. If you do this fast mode shutdown, that forces any current connections that are there to disconnect and then shuts the database down. So you pretty much always want to do that. I know in 10, the default is fast, but I just wanted to make mention of that, the older versions, that is not the default. So you want to make sure that your stop command is doing that. There is actually another mode called immediate. Never do that unless you don't care about your data. But if you need the database to stop and you're willing to risk data corruption, you can do that and that will force it to immediately, that forces it to immediately stop any running transactions and doesn't flush to this completely and just immediately stops the database. It's like an emergency mode, but so don't ever do it, but it's there. Oh, they do. Committed transactions, yeah, yeah. We stopped the database and now we're just going to get rid of that folder because we really don't need it. You don't have to get rid of it if you don't want to, but I'm getting rid of it. We don't really need that anymore. I'll go over upgrades a little bit later, yeah. So here we are, file system backups. Highly recommend this as your main way of doing backups for your database is to do the file system level backups. Dumps are always good to do as well if you have the space and the time to allow them to be done to allow finer grain restoration, but for disaster, you're mostly doing backups for disaster recovery, you might crap my database is downing to bring it back up again. This is the primary backup we're going to be doing. And technically, we already did this. This is what PGBase backup is for, which are going to give us some different options, that's all. So PGBase backup is for getting a consistent file system level snapshot of your database. Whether you're using it for backups, whether you're using it to make a replica, that's what base backup is for. So we're going to create a compressed file system level backup using PGBase backup. So first we're going to create a folder. Doesn't need any special permissions because this is just a backup folder. So just creating my backup folder in the training home directory. And because the training user is a super user, I'm allowed to connect to the replication database to do backups and all that kind of stuff. So you can do these PGBase backups. Usually you'll make a dedicated backup user, or you can just use the replication user as well to do these backups, but usually don't want to be doing it as a super user. So usually make a backup user for your database and do that. So what these options are actually doing, if you just run PGBase backup by itself, you saw what we did before, it just copied all of the files over as they were and just put them in the file system the same way they were before. This is making, we want to actually make a backup though. So you want to kind of compress things and tar them up and make a smaller backup. So that's what this capital S is telling you the format of the PGBase backup that we're gonna be doing and we're doing a tar. So putting all of the things into a tar file. Dash Z, so if you don't give a dash Z, it just makes normal tar files not compressed. Just putting all the files together in a tar file. So you give a dash Z to tell it that we want to compress it. And again, we're using the dash XS option. Remember for a consistent backup to be usable, you have to have all of the wall files that were generated during the time PGBase backup was run. So when we're running this, we want to have all of the wall files that are generated while the backup is running so we can actually start up the new database. In this case, this is a very small database with only got like one or two wall files. It's not a big deal. But for bigger systems, you're gonna have a pretty large wall file backup as well. And again, we're showing the progress again and then dash V just to be verbose. Now there we go. And if you go into my dv or my backup, you create, actually created two files. So for every table space that you have, it will actually create one tar file. We only have one table space, the default one. So it's got what it's called base here. That's our primary, that's our database backup. And then this extra other zip file is all the wall files that were generated while the backup was being taken. So to restore this backup, you have to restore both of those things. But just to go over things a little bit more here. So like I said, a separate tar file is made for the wall files. Additional table spaces go on their own. But again, remember the last time we said the dash R command? Because we were making a replica with PG based backup. We're not gonna be using this as a replica. So you don't do dash R because we don't want there to be a recovery.conf file in there. Because if you start up a database with a recovery.conf file existing in the data directory, that tells Postgres it's a replica. The existence of that file means to Postgres that that is a replica. So you don't want that in your backup. And then I wanted to do a little bit more detail of what the access option actually is here. So anybody have any issues running that backup? Do I have any other questions? Yes, I'm sorry, I'm sorry I couldn't hear you. Yes, he's asking if you could do point and time restore. You can't, I'm not gonna be doing that today but doing important time recovery is what doing these kinds of backups are for. So what's that? Right, so in this instance, so say we just did a base backup and all we have are the wall files for during the time of that backup. You could do point and time recovery for any point in time and the wall files that you have, but not outside of that. So if you wanted to point and time recovery, that's when you start having to need a secondary, we'll get to that a little bit later. I have a secondary source of wall files for the entire day or whatever you needed them to be. Yes, yes, this is for the entire cluster. Yes, yes, it's grabbing, this isn't, I mean it is connecting to the database but this is grabbing things on the file system level. It doesn't really care about individual objects in the database, it's just grabbing all the files. Yes. No, it will create one file. He's asking if it'll create multiple files depending on how large it is. It'll just create one file. Yeah, I don't know if there's a way to split it at certain points or something like that, but yeah. Right, he was asking about size, depending on the size, yeah, it's just all one file. You could just, you could leave the, leave, he's asking if you wanted to use your own compression algorithm, you would leave the FT, well actually you could probably leave the FT because that would put it in tar mode, but you could leave the dash Z off and like maybe like pipe it to B zip or pipe it to whatever else you wanted it to be. Yeah, yeah, or if you just wanted to use something besides tar altogether, you could just run the base backup and put it in a directory and then go run your compression on that directory and do whatever you wanted to do with it. Yeah, so now we're doing our file system restore, so we did our backup, now we're doing our restore. We already did this. Basically it's basically when we ran the, when we made the replica, we kind of did a backup and restore without the whole backup and restore step in the middle, we just went right from, we pulled it from the master and we made a replica. We kind of already did a restore there. Basically we're doing the same thing here, so we're gonna make another directory just in case you didn't delete your old directory, I'm just making another one called mydb2. Again, you have to make sure of the permissions on it. Basically we're just gonna un-tar, uncompress and un-tar the files we just did. So first we'll un-tar the actual data files and the dash C option to tar is just giving it a target directory of where you wanna do the back, the restore the backup to. I just put the V on there so we can watch it all explode. And then we also have to restore those wall files. And you actually have to tell it to put it in the pgwall file, though the tar file has the wall files at the top level inside of it. So if you un-tar it, it doesn't put them in a pgwall folder, it puts them at the top level. So you can see my C command is actually putting them in the pgwall folder in the restore. So that's why you don't have to do them in that order. I'm like, you don't have to do the base restore before the walls, it doesn't really matter. But if you do the base restore first, it creates the, so mydb. It created the pgwall file directory for you. So that's why I did that one first. So now we can just un-tar this here. It's only one wall file, so it's not too big of a deal. And do the exact same thing we did before. Go into the mydb folder and change the postgresql.conf, port, hit eight. And, oh, thank you, that might still have worked. Actually, no, yeah, maybe not wouldn't have worked. But so set the socket directory again and same as before, then we just start the database up. Actually, I think. So I'm not gonna go into too much heat, so this is a puzzle for you to figure out. Connecting like that won't work in this case because of the way the HBA file is set. So if you can figure that out, I'll let you do that. I'm not gonna go into it too much. But you can, the dash h option for psql accepts not just a host or DNS name, it also accepts the socket directory. So that's actually what we're gonna, we're telling it to connect to the, we defined the socket directory up here. So we're gonna tell it to connect that way. There we go. Two-day basis. So I'll let people catch up here or have any questions. What's that, I'm sorry? No, you don't really take back up. Well, I'll get into that a little bit later. This is like a basic file system backup and stuff. I'll be getting into a little bit more advanced stuff later. There's, you'll actually be using the, remember the archive command I went over, which copies, does a secondary copy of your wall files over to another location. That's how you back up your wall stream. Is that why you don't like do a copy of the wall directory as your backup? You tell Postgres to do that for you. And then, so in the meantime, so base, and we'll get into this. So what you will be doing normally, and you'll see how to do this later, you'll do like daily, you'll do a base backup of your system. But then you'll also have your archive command copying your wall files over to another location all day, all the time. You can set the retention on that to be whatever you want it to be. So then, if you do a base backup every day and you have two days with the wall files, you can do point in time recovery for any time, any individual transaction or time in that entire time period is what that allows you to do. So. Yeah, yep, yep. Right, yeah, so yeah. And unless you have another backup prior to that and the entire wall stream after that one between the two backups. So normally people, like you'll set up, if you wanna have point in time recovery for a while, you'll have three or four days where the wall files and a daily backup every day and then you can do point in time recovery for a full three day period. Anybody else have any need any help getting caught up here? All right. So yes, this is file system backup. Much faster disaster recovery because you're backing things up on the file system level. All the indexes are in place, all the constraints are in place. Everything is as it was on the file system at that time you took it. So you just, as you saw here, all you do is unzip the data directory and start it up. That's it, that's all you have to do. Obviously it's a lot slower if you have a huge database. It's gonna take a while to decompress. It's gonna take a while to, if you have a lot of wall that was generated after that backup while that backup was running, it's going to have to, so it'll start the database up and it'll have to replay all of that wall to get to a point where it's consistent and will start up and you're allowed to connect to it. So it can still take a little while, but it will still be significantly less time than doing a dump restore. And you can actually tell it to stop, you can tell it to stop at certain points in that wall. Actually no, I don't think you can tell it to stop the backup restore, but yeah, sorry, never mind. You had a question? You can do that and I know in ZFS file systems can do that, the thing that comes into, so he's asking about doing file system level snapshots. Oh no, what's that? It's the same idea. The thing you have to be careful of is if it's just a simple database like this, that usually works. If you have your wall file or your table spaces on different file systems, that's when that stops working because all of that stuff has to be consistent. Yeah, yeah. No, I wouldn't rely on that. This is from personal experience. I've used ZFS a lot. It's an extremely, no, no, but even. But it's the same idea. ZFS is an extremely, extremely reliable file system, but you can't do snapshots across different file systems of a Postgres database in a consistent way. Be very, very careful. Yeah. It will. A lot of different sources. It will work. I think it will tell you exactly what you want to hear. Yeah, it will work and honestly, it will work a lot of the time, but then one time it won't. Yeah, it'll work and deal with those. Yeah. And there's, when you understand the reasons why, it's more like why you're cautious of systems like that, because for the most part it will work, but then that one time it won't work, it's not Postgres' fault. And there's nothing Postgres can do about that to fix it because you did something inconsistent with the way Postgres is supposed to work. So yeah. Yeah. And again we're gonna, yeah, yeah. But you have to coordinate with Postgres to be aware of doing the start and stop of the thing. Yeah. Yeah. You can use the archive command to do that. Yeah, yeah. So we're gonna stop our instance there and then again delete it. So, I only need it here. Oops. And then I should take a little five, six minute break, let people go to what you need to do and then we'll come back here. Not too much left, even though it looks like I'm only halfway through the slides here. After the backup it's a lot of stuff about monitoring. So, and that's nothing you have to do. It's just examples of queries and all that kind of stuff to do. Yeah, meet back at four o'clock. Yep. All right. Back here to this point. So, I am gonna be running through the demo of this but I'm probably not gonna be waiting for people to keep up because this part is sometimes a little bit harder to get right the first time you do it. So, if you don't get it and you fall behind, don't worry, everything's fine. You can always go back and try to redo this thing yourself. This part is just mostly for me to show you the more advanced ways that are out there and this is just one backup tool. So, the base, normal base backup is great and you can get started with your production systems using that to get your backups going and you're usually farther ahead than 95% of the people out there that are running Postgres. But sometimes you want to have a little bit more to your backup system. You wanna be able to do point time recovery. You wanna be able to monitor that your backups are working. So, PG Backgres is a tool that we at Crunchy have developed and worked on. It provides point time recovery. It also provides, sorry, all right. I don't know what I just hit there. How would I do? Oh, there we go. Try again, there you go. There we go. There's commands in this slide stuff that I don't even know about. So, what PG Base Backup can't do is things like if you wanna run a backup, you have to run the entire base backup every time, all the time and keep doing that. What PG Backgres allows you to do is do incremental and differential backups. So, incremental, well, differential would be every time you would run a full base backup and then all your differential backups would be, you could keep running the differential but it would always be based on that first master backup that you took. And then you can also do incremental backups which is you take your full backup and then you keep running incremental backups but each of those increments requires all the other increments before it to be able to restore everything up. But it makes for, if you wanna take the backup 50 times a day for whatever reason, this would allow you to do that kind of stuff and have something restore. It also allows you to run, so PG Base Backup is single threaded. It'll just run as one command. So this allows parallel backup and restore. It'll automatically manage the rotation of your old archive files. You only wanna keep three days of them. It'll automatically manage all of that kind of stuff for you. It does check sums as well so you can be a little bit more sure that your backups are consistent and good. So all the things that you start to realize you need in a backup system after you get your initial base backup done, a lot of tools are out there in the PostgreSQL to do that. And this is just one of them that we've been working on at Crunchy. So actually, I don't know if this is gonna work because of the internet. We'll see here. I didn't prepare for this step. Yeah, we'll see how this goes. It's got a lot of, it's got a lot of Perl packages to download, so we'll see. So just to go over things a little bit. The way PG Backrest works is it works, what it calls a stanza is the configuration for a cluster to backup. It's telling it where the data directory is, how many backups you wanna keep for full backups, differential backups, archiving options. All of those configurations for your backup is what PG Backrest calls a stanza. Usually you have one stanza on if you're just running the backup on, you're running the backups on the database server itself and then archiving them all somewhere else. You'll have one stanza, but you can actually also use PG Backrest as like a dedicated backup system where you have one backup system that's connecting to many databases to backup all of them. So then you'll have many stanzas in a single setup of PG Backrest. And we'll see how, oh hey, look at that. I guess most of the people I've left for the day, so it's working to our advantage. It's a lot of pearl. Oh yes, sorry. I'm not sure what you mean by PG Backup. Hey, we're done. So this is the main configuration file on CentOS. It puts it in Etsy, PG Backrest. This does require, actually I gotta get out of here because I didn't, oh yeah, did do that as root. So this is where this main file, so these are the brackets here, the labeling what the stanzas are. So obviously there's a global stanza that will apply settings to all of the stanzas. And then we just have one called, it's just the name of the stanza and this one is just called main. And since it's doing this on CentOS, it knows where its default path is. I'm just gonna throw this other extra option in here saying I just wanna keep two back full backups as retention whenever PG Backrest runs. So all these folders should exist. I just made it main note of them in my notes in case I ran into issues, but the installation of the package takes care of setting up. This is where the PG Backrest calls the repository of where it keeps all the information about the backups all go in there. And of course the log files and all that kind of stuff go there. So one thing, now we actually have to go set an archive command in our postgresql.conf file. I did it again. As the Postgres user, double check where we are here. So we're in the replica, so be very careful of where we are. So CD10, data, WD. So we're in the prime, we wanna be editing the primary databases, postgresql.conf file. We've already set all these other ones, we already turned archive mode on, we already have wall sender set properly, we already have wall replica set. So the only thing we have to change is the archive command. So I usually just leave, I leave been true in here all the time, comment it out. In case for some reason you have to go in and turn the archive command off for whatever reason, you can just go in there and comment out your old one and uncomment been true and reload the database and then you're all good to go. So I'm not gonna get into too much detail about what this pg-backrest command is actually doing, but what it's actually doing is, it's telling pg-backrest to, on the main stanza, push the archive file to where its repository is located. So this is basically just telling Postgres, every time you make a wall file, run this command, which tells it to go put the wall files over into wherever pg-backrest is managing them for you. It's basically all it's doing. Yes. I mean, you kinda gotta run it once and see how much space it takes up. The repository itself is kinda small, but it's all dependent on your database, how much wall you're generating, how big your database is, and how long a retention period you're going to want to keep things for. Yeah, it's kinda gotta run it and see it at least once the first time and see how big your backup is and then kinda go from there. Yeah, the way the archive command works in Postgres is if that command doesn't return true or good, Postgres thinks the archive command failed and it will not remove that wall file, it will not remove any wall files until it gets a successful archive command. So if your archive command is failing, you wanna know about that and monitor for it, but, cause then you'll fill up your disk cause your wall files won't clean themselves up. So, this is the one reason, like I said, I usually, if I'm gonna reload the database, I usually log in to do it. So if I do show archive command, shows there, if I know what to do, select pg reload cons, show archive command. So now I know my setting worked. So that's one reason I like logging in and doing the reload command myself cause I can check my settings or change successfully and then tell pg backrest to go actually create the, actually tell it to go create the stanza and the repository here. So, oh, what is that? What is, does what overwrite what? Oh, we're gonna kick it off. It makes a new backup, a brand new backup. Yeah, yeah. So, and if you only do full backups every time you run it, you create a new full backup. And then if you set retention, every set retention to two, as soon as you go make a third backup that goes and deletes the oldest one and only keeps two. Completed, right. So this is just making sure that our, archive command is working. Yep, so, look for that kind of fun stuff there. So now we're actually gonna go perform a backup. So it's running as the Postgres user, running pg backrest using, backing up the main stanza. And I'm just giving it this log level console command just so it spits more information out for us. And then this is the actual command backup. So, see the one thing it says up here, it'll go through that real quick, backing up all the files, doing a bunch of checksums. Yep, there we go. So you can see if we go back up here a bit. If you don't tell pg backrest a mode to do the backup in, oh, that was a lot of stuff. Yeah, so by default, if you don't tell it one mode to do a backup in, it's gonna do an incremental backup. But if you don't have a full backup done in the first place, it can't make an incremental backup off of that. So, I ran it without telling you what mode to run in. So I tried to do an incremental, there was no full, so it did a full instead. So if I actually go and run the backup command again, it's very quick and done. Didn't go through all those files again. Cause nothing changed in the database. So, but like in the other options, like if you actually wanna tell it to do a diff, so actually let me log into the database here and see, add some more data in there. Two, uh, thousand, one, two, oops. And go out here and do a diff backup. You can actually see it found data files that are different, so it backed those up. And that's all it had to do, which is back up those different files. And then if we actually go here and to see var lib pg backrest. So there's two folders in there. One's the archive, one's the backup. So we go to the backup folder. Yeah, I know, I'll get there. I'm gonna show you where things are and what they look like. So there's one full backup that's in here. And if you actually go in there, it looks basically like what pg-based backup did. A bunch of TARS and zips of the base of the thing, and also along with a bunch of checksum, a bunch of other information too. So, but also is archive folder main. So, 10, one. There's all the wall files that have been generated and the little backup folders. So it's keeping track of all the wall files that are generated and all this stuff. So, like what Steve was complaining to me about, if you actually wanna see all of your backup information about what pg-backrest is doing, there's info command. So you can see we have one full backup here and a timestamp, and then we did an incremental backup and then we did a differential backup. You can see that there's not too much difference and you can see the wall start and stop times there. So, pretty useful stuff. And then if you're gonna do a restore, so by default when you run a restore with pg-based backup in disaster recovery, you're normally restoring this to where your failed database and stuff was. So you're not restoring this to some other location. What you can do in a disaster recovery scenario, it actually has a delta option where it won't, if you have a really large backup that you're just restoring over, like a corrupted database or something, it'll only overwrite the things that went bad to get it back to the consistent state that that backup was. So it can make for extremely fast disaster recovery scenarios because it only has to copy back what is different since the last backup. But in this case, we're just gonna make a new folder here, set the permissions, and then we're telling it to restore the data. You can do restores to any other location. You just have to give it the path to the pg-backrest restore command. And I didn't turn the debug level off or on, so it just didn't give too much information back. We go in here to, as we've done many times before, go in here and change the port to, this is our disaster recovery, so 911. And because this backup is being run by the Postgres user, so all of the other stuff we had to do with changing the socket directory and all that kind of stuff doesn't really need to be done anymore, so you should just be able to start it up and connect to it. P5911, there we go. And then do a count from, it has the extra 2000 rows I had added in there after I'd wanna remember why I added to the 2000 extra rows in there between the incremental and the differential backup I did. So I just told it, by default, restore the last backup that we had to completion of what we had, which has all of our data in it. So that's, I know I ran it through that kind of fast, but run a little bit short on time, so just wanted to show you basically how PGBase backup can make a lot of the stuff a lot easier for managing on a lot of larger clusters and that kind of stuff, so. Any questions about that? Okay. You can, there's options you feed in the back in the backrest to tell it to, I wanna restore it to this point in time. And as long as it has the whole wall, it has backups and the whole wall stream in that time period, you can restore it to that specific period of time. It's not quite that simple. You have to get the selection, so it's possible, recovery.com, start post-gres, what'll happen is that it'll start pulling all of the wall files between the end of the last backup and the point in time, and then it'll roll forward all the way up to that point in time. It's pretty free for us again. It always goes back to the full backup. Yeah, so if you ran multiple differentials, they all rely on that one full backup that happened since the last time. The incrementals are based on the previous incrementals, so it's only doing a little bit at a time. Yeah, the prior back, yeah, yeah. Okay, you need the full, and you need the different, and that differential, if you're doing a differential backup. If you're restoring an incremental, you need the full and everything in between, from that full to that incremental. So those are the backups that you need? Yeah. So it will go and restore them all, right? And then that'll be all the way up to the point of the end of the incremental backup that you started. And then you can, and then it's from there that you would replay while full. Yeah. The incremental is based on whatever the prior backup was, whether it's an incremental or differential or full. So you could have like full, incremental, differential, right? And then that differential is based on the full, and then another incremental, and then another incremental, right? And that's based on the, yeah. So the incremental after the differential is based on the differential, right? The incremental after the incremental is based on the incremental, the differential, and the full, right? You know? All right. So I mean to say, based on, it's just a matter of, and that's like, if you can't, I mean, if any differential, incremental, whatever, is gonna, you know, they all roll back to the full, right? You have to have that full backup in order to be able to do it. So you can't like get rid of the full backup or something. You don't need that, honestly, right? So what that allows you to do is do a restore, and we can have less wall requests, right? Because that way, you know, if you do incrementals every day, you avoid the risk of some kind of laden, because we re-copy stuff, right? So if all you ever did were incremental, then if you got corruptions in your backup system of any of those are instrumental, like, you know, you're all re-alien. It could be, you could lose everything, right? You have one of the reasons why you want to regularly take full hands of rentals, but you don't want to have everything based off of one full backup at the beginning of time. Thanks, Steve. Chris, yep? Yeah, that's a whole, that's a, I'll, we can talk about that later. Cytus is a, yeah, yeah. And if you do C-Store, it's not there, yeah. If you have a primary and replicas? Yeah. Somebody asked about, what, it says? Yeah, I, I can't, I can't speak to that, yeah. That's, that's, yeah, yeah. So a quick slide here about doing upgrades. I'm not actually going to do an upgrade because we're already on the latest version. So, but for upgrading the basic way that's always worked from the beginning of time and will always continue to work, probably, hopefully, probably forever, is doing a, there it is again. Yeah, it's basically what we did before, dump and restore. That works across major versions and will, and probably always will. The, some advantages of doing the PG dump up, PG dump binary. Oh, I always want to do the, use the PG dump of the target that you're going to. You don't want to, if you're upgrading from nine, four to 10, you don't use the PG dump from nine, four. You use the PG dump from 10. Cause it, it takes care of all of the things that are different going from the major version, one major version to another. Eight dot four introduced, so the pets for the PG restore command introduced a parallel option so you can restore multiple objects at the same time. And nine dot three actually introduced parallel dump so you can actually dump multiple objects at the same time. So you can do a fairly quick PG dump and restore compared to the way it used to be but it's still nowhere near as fast as, as a doing the later thing we'll talk about here is PG upgrade. Same caveat. So PG dump has the same caveats as it always had before. It's got a, it advantages a smaller dump file but a much longer restore period to get back up again. So PG upgrade is a binary that's included with Postgres to allow you to, to do in place upgrades to major versions but right now it's not so much of an issue anymore but it used to be an issue, big issue. It only works from eight dot four got seven and higher. If you need to upgrade from a previous version of that you need to dump and restore. There's no, or do some other fancier things but that's another talk. So basically just do a dump and restore. And what PG upgrade allows, so it doesn't in place upgrade on the data files depending on what you tell it to do it'll either, it takes the data files from the old folder and copies them to the new folder and does all the changes. So it's just doing, basically doing a file system copy to do the base backup. And if you're doing this on the same file system you can take advantage of a, it's called a dash dash link or K option. It actually does hard links from the old files to the new files so it can be extremely fast. And in my experience I did a 700 gigabyte upgrade in five minutes which is pretty quick but that does not include the analyze you have to do after you do the PG upgrade because statistics are not, all the statistics are not carried over during the upgrade. So you have to go back through and run an analyze and that can take a long time. There is an option to the vacuum command to the binary vacuum DB command called the analyze and stages where it actually it'll do like, it'll gather a smaller amount of statistics in a quick pass through once, a little bit more in a second pass through and then a full analyze in the final pass through. So if you need to get your system up a little bit quicker you can do that. In the case of the 700 gigabyte upgrade that I did the second stage of the analyze took 10 minutes to get to. So this was essentially a 15 minute outage for a 700 gigabyte table. The full analyze took 45 minutes to analyze the entire database. So there are other ways to upgrade and I mentioned down here at the bottom and it's getting easier to do some of those with Pgeological but they take significantly more prep time to get up and to get up and running. Some of those, you can, like I said, you can even have up to almost a zero downtime upgrade but they take a lot of prep work and you have to be very, very careful with what you're doing. And so there is the native Pgeological that's built in. Pgeological is still actually a third party tool maintained by second quadrant. The third party tool has a few extra features and haven't made it back into Core yet and then there's some other logical replication tools that are out there. I wrote one called Mimeo and Bacardo and Sponi or two other big ones that are out there for doing logical replication. So any other questions about upgrading? Okay, there's a bunch of slides. I basically did write a bunch of queries for a lot of them are gonna look like a foreign language or matrix code up on the screen because of all the weird system catalogs and stuff that I'm querying to get the data out but the slides and stuff are available to you. You're feel free to go back in there and look and see what they're actually doing. But at a minimum, if you're gonna be setting up a monitoring system, these are the kinds of things that I would really recommend making sure your monitoring system is taking care of and has a means to get this information. Kind of the two most important, the important things that are really important, of course, is your replication status. That's very important so you gotta make sure that that's working. And active and idle connections on your system. Idle and transaction session time is something that's very critical to watch for because you remember how multi-version currency control works in Postgres. If there's an active connection running or an active transaction running, Postgres will keep all and then there's other stuff that's coming afterwards, deleting rows, updating rows, all that kind of stuff. Postgres keeps all of those things that are visible to that transaction around that all of these new things are doing until that transaction commits. So long running idle transactions are a very quick way to make your system extremely bloated because it has to keep all of that, it has to keep all of that old data around and then auto vacuum goes back and through and marks all of that space as reusable and all that kind of stuff, but it may take longer to catch up using all that reusable space than your system actually needs. And then of course I say the other really, really important thing to monitor for is transaction ID wraparound because that is a outage level event if you get to that point. So very important to monitor for that. Some other things to monitor for, to table statistics, commits, the size of your database, wall counts. If you want a really, really fantastic tool for doing log and query analysis, it's called PG Badger. That's where if you set your username and database and all that kind of stuff in the log line prefix, this will give you statistics out of like the top 20 longest running queries, the top 20 queries that took the most time, how many times this user logged in, how many times you did index scans versus sequential scans and all those fun kinds of things that are, you can pull out the logs instead of having to try to read through all the logs yourself. It will put them in some very nice HTML web interface that you can go through and look through them. And you can easily set it up to run once a day. So have a whole day's worth of query analysis to go through and go in there and see what the long running queries and stuff are. I wrote another tool called PDJaman. If you have critical fun, you need to know that I have a function in my database. I need to know that it ran successfully and where it failed during the process of that function running, if that ever happens. So I wrote a tool called PDJaman for doing that and making sure critical functions are running properly. And we've been working on a tool called PGMonitor to kind of provide a suite of tools for doing monitoring and it's based around Prometheus. Collection Engine, it allows you to do trending of things with graphs and dials and all that kind of stuff and monitoring to watch for critical events. So there's a link to PGMonitor down there at the bottom and Prometheus. So go through some of these queries here. So replication status. This first query is you run it from the replica system itself and it tells you how many seconds, the last time a wall file was run. The disadvantage of that is if you have no rights on your master, there is no wall being replayed on your slave, on your replica. So this might give a false sense that your replica is falling behind just because it hasn't had any rights. But if your database is always writing and your replica should always be caught up, this is a very, very good query to run on your replicas to make sure that they're replaying wall properly. And if you run the command the way it is now, it just gives you a value in seconds of how many seconds it's been since the replica replayed a wall file. To get a more accurate means of whether you're doing rights or not whether your replica is behind is you can actually run this query from the primary and it will tell you how many bytes behind any of the replicas are. Can't really give you a time period of how far behind they are because there's no real way to know that. But it's a very useful, I can go ahead and run that here. See it's, oops, I was not connected to the real one. That'll help, there we go. So zero byte lag, connections. So there's a view called PGStatActivity shows a tremendous amount of detail about all the current sessions that are currently connected and running to the database. The thing about this one though is it only shows all activity to a super user if you don't have permission. So if you're logging, normally all the system catalogs and stuff are visible to every user in the database. There's nothing really stopping people from doing that but you don't want one person logging in to see what queries other users are running because that's essentially what PGStatActivity shows. So sensitive information like that is not shown to other users unless you are either a super user or the Postgres 10 actually added a new built-in role called PGMonitor that if you grant that role, it's like a group role, if you grant that role to another role, it allows that user to look at PGStatActivity and see what's going on. So you don't have to, the problem with the way things used to be is you kind of had to have a super user or you had to set up a whole bunch of special functions to be able to have a monitoring role in your database that's able to see everything. You don't want your monitoring role to be a super user. It's not ideal. You wanna have things only need the permissions they need to be. You don't want your monitoring role to be able to go in and drop databases. So thankfully PG10 added this PGMonitor role in there and starts making things like that more visible. This big, looks like a big pile of stuff here is a very useful command that we use. Actually make that a little bit easier to view here. So gives back the max, whatever the max connection value actually is and then it shows you how many connections are actually being used. The percentage of them that are actually being used. So the max connection is 100, there's seven, these are all background postures and the stuff that are actually connected and using too. It's taking those into account. So 7% of the connections are being used. And also these are actually, how many idle and idle and transaction sessions there are. Thankfully none. And it also tells you how long the longest transaction has been running and the longest idle and transaction session has been running. Very useful query to just get all that kind of information and simple numbers that can be exported into monitoring systems. Yes. So the question is what's the difference between idle and idle and transaction and is there a way to have Postgres automatically clean that up? So idle is just a session that's connected, hasn't started a transaction, isn't running anything, it's just an active connection that hasn't disconnected. It's really not doing anything in database, it's just using up a connection slot. Idle and transaction is something that started a transaction and stopped doing whatever it's doing in that. So you see like you type begin, select star from something and then don't do anything after that and you never commit or roll back. Sits around transaction. The problem with that, like I said before, is that causes that leaves the transaction open, makes Postgres start keeping all of the data that it needs to keep around for that session. So that's why you don't wanna have a lot of idle and transaction sessions going like that. And then what was the, yeah, automatically there's, I think there was a, there's a, I think there's a statement timeout command that I really don't recommend running. It's basically any statement that takes too long to run, just disconnected after this long. I think there's a new, there's a new option that you can set how long idle sessions can sit in Postgres and it will disconnect them after, and idle and transaction. So I think, I don't know if they're new in 10 or new in 9.6. I think one of them is new in 10 and one of them is new in 9.6. So they're very, what you're asking for in that second question, there is a way to do it, but that's in very, very new, very new versions of Postgres. Nine, six, yeah, okay. So this is the query that looks like, make your brain explode, try to look at, figure out, figuring out what it's doing. But this is the thing to monitor for that idle, that transaction ID wraparound. And actually, I can show you what a very non-busy system looks like. So that's a very low transaction. It's got, it's got four billion more times it can go out there, 17. I've only run seven transactions on the system since it started. And so, and then the other two values are the percent towards actually wrapping around and shutting down the database. And the percentage towards emergency auto-vacuum, which is what that other session thing I told you about was. So, this example here, it's actually from a client that I've kept anonymous, thankfully, but there, you can see that their oldest transaction ID is, 496 million. And they actually had, they have their auto-vacuum freeze max age set to 500 million. So they don't have it at the default. They upped it from 200 million to 500 million. But then the system has been busy enough that it's gotten close to what their new max value is. So you can see it's only 25% towards wraparound shutdown, but it's 99% close to the emergency auto-vacuum. So, their system right now, if you go, if you went in there and looked at it, it's got sessions in there that are running in that emergency vacuum mode right now. So, this is the kind of query you would put into the monitoring system to keep an eye on what the oldest transaction ID actually is and what the percentage is towards those values are. So, if you, as long as you're acting on, actually just realized, is that highlighting? Yeah. As long as you're acting on that, like and you set like your emergency alerts and all that kind of stuff to go off when that reaches 99% value, you will never run into wraparound and you'll never be in danger. So, that's why it's good to monitor for things approaching emergency auto-vacuum wraparound, not the big wraparound problem. So, as long as you're acting on that second value, you should be good. And that's what I'd recommend monitoring for. Any questions about that? I, he's asking about PG Badger. PG Badger only analyzes your log. PG Badger doesn't even, but never logged into the database. You just feed it log files and it just bases it on that. So, PG Badger is a log analysis tool. It's not a monitoring tool. The PG Monitor thing that we've been working on does. Yeah, we have that exact query. All of these queries are the exact queries we use in our monitoring tool. So, these aren't queries that I just pulled out of a hat to show you things. These are real world monitoring queries that we are using right now to monitor systems. Yeah. So, table statistics, they're fun, but they are not cluster-wide. So, if you need to collect this kind of data, you do have to have whatever your collector is connecting to each individual database to get all the table statistics and stuff like that. So, and these are, here's all, I'll run this here real quick so you can see what it actually looks like. So, there's actually two different things. This is querying user tables. So, those are user-created tables. That's usually all you really care about for a lot of this stuff. But there's another view called all tables. So, basically, this is keeping track of the number. It's an incremental counter. It inserts, updates, deletes, sequential scans, index scans. It's an ever-increasing number that's always going up on that table. So, what I recommend if you're grasping this and that kind of stuff is to graph it as a rate. If you just graph the straight number, all you see is it's not aligned, just going straight up all the time. And that's not really that useful. So, if you graph it as a rate of change, you'll actually see, like, if you have a batch of inserts go out, you'll see the thing go up, and then you'll see it go back down. So, the table statistics and this kind of stuff is recommended to do, graph them as rates of change if you can, if your monitoring system supports that kind of stuff. Yeah, you can reset those stats, too, if you want, but that's not really useful. So, table sizes, again, it's another thing to keep track of. Again, you have to run it on each database in the cluster. This query actually includes the indexes in the size of the table that's being returned. There's other queries you can run to get the table size versus the index size. So, this is just the table sizes overall. Well, that's included in that. Yeah. So, these are database and cluster wide statistics. So, it's a lot of the same stuff. It's just the overall, so the overall number of inserts, updates, and deletes. So, there's a training database you can see we did. 2000-some inserts, all kind of stuff, but this also keeps track of how many transactions have been committed and how many transactions were rolled back, how many deadlocks are in place, how many temp files are being generated, you know, all that kind of stuff. These are cluster wide statistics. A lot of these are, again, constantly increasing numbers. So, this is kind of the thing you want to graph as a rate of change with conflicts. I'm blanking on what conflict is yet now. The conflicts column and the PGSTAT database. Yeah, I forget what that one is. It's not in my head. So, here's how to get the overall database sizes. So, it returns the size of each database in the cluster. And this is something you'd actually want to graph, both as a always-increasing line, but also it's valuable to graph this as a rate of change so you can see the overall database size line may kind of drown out big spikes of size increase, but if you actually also graph the rate of change, you can watch for big spikes in size and all that kind of stuff. And typically for these kind of things, I always return them as raw bytes. I don't try to change it to gigabytes and megabytes and all that kind of stuff. That should be something your monitoring tool takes care of interpreting, changing things from bytes and other things. Because then if you just get the raw byte value, then you can do whatever you want with it. And it's easier to monitor for that. Here's the query I did. So, you can look at table sizes versus index sizes. You'll see a lot of times, a lot of times the size of your indexes is well more than twice the size of the data. Like each individual index is more than twice the size of the data in the table. So, indexes can make your tables hundreds of times bigger than your table sometimes, big. So, it's a good idea that it's a useful trend to kind of keep an eye on. It's not necessarily a bad thing, but it's something useful to keep an eye on. Oh, okay, so yeah, that thing before I said, where if you try to run a long running query on a replica and it cancels it because the master force to the catch up, that's a conflict. Wall metrics is a good one to do. That a lot of tools I see don't actually monitor this, but I think it's really useful to do. Basically, this just gets an LS. It does what the function says. It does an LS on the wall directory and returns all of the wall files that are in your wall directory. So, you can just go and select GLSWALDER. So, actually it has a little bit more, it has some more useful information there. Yeah, it's got the size and when that modification of that file was. So, it says modification because the Postgres recycles wall files. So, you may see things that look weird like newer file timestamps, but older file names, because it re-use the, it recycles the file, so it doesn't have to keep doing it, using the recreating new wall files over and over again. So, my recommendation for this is if you see it, so this query kind of watches for it to be bigger than you expect it to be. So, that max wall size PostgresGL.conf value, that one gigabyte value, this essentially will, if you say your wall directory essentially got to like a hunt, 1.5 gigs, you might want to investigate what's going on there, because that's more than normal, and I think it's usually worth investigating if your wall file size is 300% bigger than normal. That may mean you need to actually change your configuration values, because that's your new norm. So, it may not be a bad thing that that's happening, but it's something to be aware of, because if you increase that other file, that increases, if you increase that value, makes Postgres itself run a little bit more efficiently. I'm sorry, I didn't hear the beginning of that. We don't only have a fork, we don't have a fork of it. We use the Postgres exporter, and we provide our own query file to it. Yeah, yeah, right. So, is that there's a, there's a, Prometheus works with exporters to actually provide the data coming into it. So, there's a Postgres exporter that somebody else wrote. It has some built-in queries, but I like using my queries, so you can feed your own queries into it, and then it'll provide those metrics back to Prometheus, so, yes. Yeah, so all the queries I've been showing are in the query file that we use for our Postgres exporter on Prometheus. So, those are all the monitoring things I had, queries I had that I found pretty useful. With eight minutes left, just do a quick fail over here. So, show you how easy failover can be for the most part. So, if you need to do it. So, I'm going to stop the mat, I'm gonna stop the primary system. Hopefully, we should see on this, on this end, yeah. So, I'm tailing the log file of the replica right now, and the replica's trying to connect to the primary and can't connect anymore, it's because the primary's down. So, yeah, check the log file there. So, to promote the replica to be the new primary, you can use the pgctl command. So, I have to run this as a Postgres user. So, I'm gonna leave this tail running here so we can see what happens. That's it. See, receive promote request, replay a little bit of wall files there for it. You can see here's the timeline IG. It's now on timeline two. So, archive recovery complete, database is up and in setting connections. So, now I can connect to p5444. So, now I'm on the replica, and I can create table new ID, I can write to it. That was failover. You can, I've always been of the mind that I don't like auto-failover. I like having a robust enough monitoring system in place that lets me know when there's problems fast enough that I can go in and do what I need to do to fix the systems. Because the problem with auto-fail, the problems with auto-failure is you can very, very easily run into, what's it, a split brain. Thank you, that was what I was thinking of. I split my own brain. I couldn't think of it. You very, very easily run into split brain. Like your network goes down, it sinks the masters down, it promotes the replica over here, then your master comes back up, your old master comes back up, writes start happening over here, other systems start writing over here. Now you're, now what do you do? But there are valid reasons to have auto-failover that I can't argue against. For the, to me it's very rare that it's actually needed in that place, in that instance, and I would much prefer to get the other monitoring stuff set up in a way that alerts people the way they need to be alerted. So the thing with auto-failure, what auto-failover needs to, so in this instance here, I don't have the time to do it now. If I had extra time, I was gonna go back and rebuild the old master and get the new replica connecting back to the old one. So that's what you have to do is you have to go, it'll failover and you have to go, if the old master's still there, go rebuild it and then reconnect the two back to the way it was. That's hard to automate. Doesn't sound hard because it doesn't sound because it's a very easy explanation in practice. That's very, very hard to do. Well, I mean, you can get to a point where you get it down pretty well, but then the one bad automatic failover happens and then I would recommend looking into systems that people that have already made them and have been well-tested to do. I wouldn't go trying to roll your own automated failure system, just because it's been a long-running issue and there are tons of tools out there that have started to deal with it. I know that PG Pool, I think PG Pool has an automated failover system that's in it, it's pretty robust, but again, PG Pool takes a significant amount of setup to get up and running. It's not only a connection pool, it also does automatic failover and that kind of stuff. So if you're gonna go down that route, I would look to see what people are already doing and not try to roll your own. Whatever RDS is doing behind the scene, that's magic, I don't know what's going on there, but I can say that their automated failure works pretty well, but that's not something you have to worry about, that's something that they've solved in their system, yeah. It's not built-in, there are third-party tools to like to do it, and I would just be, I would be really cautious of your need to do automated failure. I would first get a system in place that alerts you when there's problems in a way that works well, and then if you still need automated failure after that, then go down that route. That was it. It depends on, yeah. Yeah, so he's asking about what's a good load, what's a good load, things to monitor for you? Yeah, it's not a bad, so load isn't a bad metric to have as a generalized thing in your monitoring tool, but it's useless without also individual CPU information and IO information, and all that other stuff that goes along with it, so you know what is actually causing the load, because if you just look at load, and you don't know anything underneath it, and it's like, well, maybe I need to put more CPUs in it, but it's actually your disk subsystem is not fast enough, so you have to have other statistics on top of that to see whether, because if it's just utilizing all your CPUs, and that's fine for you, and the IO is next to nothing, then your load, whatever that load value is, is normal for your environment, but if the load value is high and it's all about IO, that may be something to be more concerned with. Yeah, any other questions? It's an always, the number itself is an always increasing number, and when that, no, no, Steve can probably explain it a little bit better, but. What has to happen, what happens is that because the transaction is out on disk, it's only 32 bits, okay? So one, you know, and so transactions keep moving forward with that growth data, eventually you want that growth to be frozen, right? So it goes from having an actual transaction that you have to specialize in, that's just normal, right? If that doesn't happen, no vacuum is happening for whatever reason, transactions cover, well, transactions, a single transaction ID can show up in multiple rows. None of this has anything to do with the number of rows in the system, right? None of it is all about the transaction ID, right? It's the number of transactions running in the system, it has nothing to do with the number of rows in it. Yeah. That one query that I did is basically a very, I took a while to figure out the right kind of query to get that data out the way it is. As long as you run that kind of a query and it gives you that percentage value and you just monitor that percentage value, it's doing what you ask. It's watching for that value approaching that. So if you actually go in and look at that query and see what it's looking at, what it's pulling out, where it's pulling this stuff from, that gives you the more detail that you're looking for. There's, you can see what the query will look like, it's pulling from quite a few different places and doing a fancy math and all that kind of stuff on the XID to figure out how close it is to those wraparound values. It's gonna slowly creep up and then it'll go back down and it'll go up and then it'll go down. Yeah, so the graph that that query will produce and also the XID value, you'll watch it go up and then it'll sharp drop down like that and then it'll go up and you'll watch a sharp drop down. So it's a big sawtooth graph in the end. Yeah, I actually, we don't actually have a graph for it. We actually make, it's like a single value gauge, it's just a percentage and when it reaches, when it reaches 75% it's a warning and when it reaches 90% it's a critical and sends a page out is what we do. Right, yeah, so that's why I have the metric is being put into Prometheus for you to pull out and graph if you want it to be there. So we collect it but on most of the time the basic usage that we have is people just wanna know is my database close to wraparound? I just wanna know that. That's the basic info that's provided in the dashboard that we make by default but the extra info that he was talking about is in Prometheus to get out if you wanna watch the trend of how vacuum is running and what it's doing. Yeah, yes and if there's a feature you want that's the only reason I can see 96 is fine as long as you're keeping up with, so he was asking if there's a reason to upgrade from 96 to 10. As long as you're keeping up with the minor release like you're keeping up with the minor releases of 96 that's much more important right now at this point than worrying about doing the major version upgrade. Whether you need to do a major version upgrade is more a consideration of I mean how long it's been if your version is going to be going out of support soon you wanna keep up with that but also just keeping an eye on the feature set. You don't, I don't really see a huge need to always upgrade to the newest major version every time it's out. Every two or three major versions is usually what I see people doing and that's usually fine. You don't wanna let it go longer than three versions behind is then you start risking running out of the support window for what year it is. Like 9.2 is, so there's 9.2, 9.3, 9.4, 9.6, and 10. So there's been four, there's four major version releases that are out and supported now and 9.2 went out of support last September. So once you start running, following about five major, I'm sorry, start running about three major versions behind, that's when I start being a little bit worried for reasons other than features that you need. But like if you need logical replication, if you need partitioning, trying to think of other, those are the two big things that were in 10. If those are features you want, that would be a reason to upgrade. But if you don't need those, I don't wanna worry about it right now. It's, that's a bit of a lot of ways she's asking about doing manual vacuuming. It, I mean, if your database is small enough that you can run a manual vacuum every day, that doesn't hurt anything. Certainly that certainly doesn't hurt anything. It just starts to become the larger and the larger and more busy your database becomes running manual vacuum. A manual vacuum is much more intrusive than auto vacuum running. Auto vacuum runs with a little bit lower priority on things, it'll slow itself down on how much IO it's doing. A manual vacuum doesn't, it's just flat out run. So you'll actually see that. I've logged into people's systems and seen like old really bloated tables will have like an auto vacuum running for seven days. Just because it can't keep up with what, it wasn't tuned right, it wasn't keeping up. But then you go and kick off a manual vacuum and it finishes in two hours. Because it runs with a little bit higher priority. But if you see something like that, that means there's some other problem going on that you need to look into that. So I would be, I would put more of a priority into learning how to tune auto vacuum to run right so you don't have to run manual vacuums. Yeah. And what happening? No, no, he's at dead locks and auto and vacuum now. There's dead locks aren't related to, a dead lock, are you talking about actual dead locks? Or are you just talking about locks? Just locks. Oh yeah, just locks. Yeah, that'll cause it because if there's a lock on something, it's an open, that means it's an open transaction that's holding a lock on something. So it's going to be keeping old rows around for that transaction. No, not the lock, the transaction. The lock isn't doing it, but a lock isn't gonna happen unless there's an open transaction. So yeah, does that make sense? Yes. Any other questions? Yeah, thanks for sticking around. Yeah, he told me, he showed me what you guys are trying to do.