PostgreSQL Tips, Tricks, and Gotchas

so welcome to our session on Postgres tips tricks and gotchas this is the third iteration of this presentation first time I gave it was for the LA Postgres user group they requested a presentation that would kind of cover people who had been using Postgres for a while and could use a few tips to make their lives easier as well as people who are brand new so right now this is a good spot to be if you really wanted to be in the other room and go through the full 3-hour toriel but couldn't get it downloaded couldnt get Postgres download on the Wi-Fi or whatever so we will go through a number of those pieces also if you have the paper schedule this is not the originally scheduled presentation is a fill in because of some cancellations that we had so really quickly I'll introduce myself my name is Darin Douglas I'm a chief instructor of Postgres course comm that's basically me and two other guys that teach Postgres and we really enjoy doing so I've been using Postgres for since 2008 roughly so that's 12 years of using Postgres as a primary database that I work with like a lot of people people end up as accidental DBAs you know you're the only person in those linux and that's the only platform that this particular database runs on so now it's your job I see a lot of people nodding and smiling so that's basically what happened to me several years ago so the point of this presentation is to essentially over the past several years of teaching Postgres I found that over your typical three to four day Administration course there's a certain point where you can tell people have some type of aha moment there's they built enough of the core basic concepts that now everything else is smooth sailing basically through the rest of the course so I'm covering a few of those things and that we normally do in about a day and a half we're gonna track cram it into about 45 minutes that's the goal so more stuff about me a few places where I've used Postgres is specifically I first used it in mining so when I say mining I work for a company that was a technology startup in the mining industry so goal coal gold copper mining right so when things like that you have a lot of operations optimization data a lot of machining machine communication as well as a lot of GPS signals mineral qualities and things like that and since then I've continued on to do software development consulting right now my consulting business we're doing a lot of Shopify backend work for e-commerce sites doing transitions from other platforms to Shopify a lot of API work things like that in training trained people from a lot of companies I highlighted one because it's kind of ironic but we have trained at least three people from Oracle that need to learn about Postgres to support clients as well as every DBA at the Library of Congress went through our course so a point of presentation now I'm done talking about myself and introducing myself is this there's a few things that sometimes we're just so busy getting into a production issue or trying to get this new database that we're going to use for this project working that we're doing things the hard way and we don't know it and so I'm going to try to eliminate some of those things that we're doing the hardware or some things that once you understand these core concepts will make Postgres a lot more pleasant to work with and you'll become one of us who find it our favorite database to work with alright so we're gonna break those down to the few three categories and the title of the presentation the first is tips so a few things about Postgres DBMS architecture and operations when I say architecture I'm not talking about real internal architecture there are people in this room that are qualified for that I'm not one of them what we're talking about is the structure of a Postgres database Postgres of the structure of a Postgres cluster was the concept of cluster mean once you understand those things and once you start reading the documentation a lot of those concepts become more clear and it's a lot easier to work with Postgres once and get that baseline the second thing is tricks so there may be a way that you have been working every day if you're new to Postgres let's say you've been working in sequel server for years there's a way you're used to working that you may try to bring to the Postgres environment that may actually cause you a little bit more pain necessary so I'm going to show you some things that Postgres especially the piece equal client can do they'll make your life really easy the last thing is a few gotchas a few challenges that you're going to frequently encounter just one or two things that if you knew this or if I had known it ten years ago will save me like half a day of debugging because I didn't know what I was doing so those are the things that we'll go through just a quick show of hands how many of you're relatively new to Postgres been using it for a year or less awesome well welcome to the community let's get started so what I'm going to do is I'm going to just demonstrate some of these concepts and topics and introduce you to a few the executables that you'll find on a Postgres system so we've got two terminals open on the Left I'm gonna call it node one that's where I'm gonna do most of the work on the right is no two and there's a few executables that you may be used to that you use with Postgres one of them would be P sequel that's our client utility and one thing that is really good to understand about the Postgres environment is this so I'll just do this I'll just determine where my P sequel client is living as well as mine it DB all right so let's go to the path we're in it DB is all right so let's take a look at that path so I'm gonna switch over the to the Postgres user and I will go to the path where those executable soldiers all right so this is a directory that has all of my Postgres executables and I'll actually make this window a little bit smaller so that we don't get problems seeing the back alright so one core concept to understand is that most of the things you're going to install with your typical Postgres installation if you just sit down Linux server and say install Postgres you install the wait as directed on the website there's going to be primarily a number of applications that get installed the biggest thing you can understand about these is that almost all different client applications the one that is actually running the server you can kind of tell just by doing a quick listing so this executable right here it's the largest file in the directory it's named Postgres that is your server executable almost everything else in here is a client almost everything else in here is a utility that's used to either interact with the server to initialize a new database cluster or to perform other operations now you might say why am i emphasizing that so much once we get down the road a few slides that's going to make a huge difference so I understand that all right so that's our Postgres executable right there so if I were to try to use this executable it's not something you're gonna do a lot but you could actually call this directly a lot of people don't don't know that so if I were just to type to execute the Postgres executable immediately it tells me why it can't run so the reason why I point this out is that because a lot of people don't know I could actually directly call the POSIX Postgres executable there are a few rare situations where I may have a startup issue as my cluster and things are not showing up in the log so if I end up in that rare situation where I'm having a startup issue as my database cluster and things are not showing up in the log I could simply execute the Postgres command and then everything's gonna come to standard out so in this case my issue is that I RA have another cluster running on the same data directory now I'm attempting to run right now so in day-to-day life that's a good technique to know um I keep on using this term cluster so typically when we use the term cluster throughout technology what are we talking about how many machines typically always more than one right and that is a huge thing that some people have to try to break once they come to this environment when we say cluster when we're talking about Postgres we're not talking about more than one machine we're not talking about more than one instance necessarily instead a Postgres cluster is defined here if you look at the documentation there's three things that define a cluster it's a unique combination of IP address TCP port and a data directory so essentially we could take the word cluster anytime you see that in the Postgres documentation and replace that with instance think about an instance of Postgres so a cluster is not multiple Postgres working together this one alright so there's really cool implications to this concept that we're gonna get to you as we move forward all right well let's just go back a slide I have a few commands that I can use to work with a cluster and it DB is the one that I used to make a new cluster so remember those three things we need a tcp port t + ipu tcp port and a data directory so I can take essentially any directory on my system and by running the NDB command I can initialize it and prepare it for Postgres to run a cluster using that directory so I'll demonstrate that actually let's just do it right now so notice I just tried to start Postgres by calling the post-crisis executable it said hey there's already a cluster running so what that would mean is that I already have an instance of Postgres running on this machine let me try to connect to that real quick and there it is so I type in a piece equal client that's the client utility it connects to my cluster in terms of connection information there's a lot of ways we could connect to multiple clusters whether on this host or another well in this case I'm just accepting the defaults try to connect to a cluster on the local so a local host address try to connect to the default port 5432 and try to connect as the current system user which is Postgres if that system user exists inside the database which it does because of the initial is it the first initialization then I will be able to connect so you can see I'm connected there I do a quick listing and I have a few databases which we'll talk about in about 10-15 minutes and I have one that I created called Baltimore which will also discuss ok so that's my cluster that's currently running one way to understand the concept of cluster is this analogy that I've heard a lot of people use I've used before is that to understand this idea of cluster not being multiple machines it's just one instance of Postgres think about cluster of grapes a cluster of grapes is a single entity right but attached to that one single cluster of grapes you have multiple pieces of fruit so the cost of the cluster really comes from the concept of a database cluster the Postgres instance contains multiple databases that's the cluster we're talking about it's a cluster of multiple databases not multiple machines or instances there are multiple databases there can be multiple table spaces as well so those are three separate concepts in Postgres alright so let's go ahead and initialize a brand new cluster so just to make my life easy I'm going to become my root user make a directory called opt cluster a and we're gonna make the Postgres user and group the owner of that new directory alright so if I look inside my opt directory right now I have a single directory cluster eight owned by the Postgres user look at the permissions we're working with 755 permissions at the moment so let's see what happens when I run into TV so I'll switch back over to my clothes dress user and I'm gonna run an it DB remember an it DB is the command we can use to initialize a brand new cluster so essentially right now this directory is nothing but an empty directory right that I created by the root user on the system and assigned it to the Postgres you assign ownership to the Postgres user and group once I run it DB this command attaches to that directory changes some permissions which we'll see create some sub directories which we'll take a quick look at and then does some additional configuration so let's go ahead and take a peek at what we see inside there all right so that blank empty directory that I previously had just created now looks like this and I have a bunch of stuff a few things that are critical for you to know about which we would go through every single piece if we had the full four days to teach you Postgres our two files and then a common question that people ask the two files that you really need to be super aware of is number one PostgreSQL Kampf that is where the majority of all of your configuration goes so if you're talking about replication backups all kinds of other things those are going to be configuring in PostgreSQL com that is a line that's a file with hundreds of lines that do configuration if you ever have opportunity to attend a session that is this PostgreSQL comp line by line those are awesome presentations I highly suggest you you attend one but that's the majority of your operational configurations the other file to know about is PGH PA com HBA stands for host based authentication so this is where we are able to configure which clients can connect to Postgres and how they can connect and also where we can deny those types of those connections also if we're going to connect to some type of directory service this is where we would define how that directory service interacts with Postgres so we'll take a look at both of those we'll make some some minor changes to those today then of course the biggest question that people ask when I start to introduce this as the data directory is okay where's the data so this sub directory you see here named base was in there once we start to create databases beneath there there will be individual directories one directory per database that the actual data is stored in okay but the really cool thing that I like about this is actually an elegant design point which is what I said we're going to have talked about the implications of the definition of a cluster because my configuration am ID to live in the same spot let's say I have some catastrophic failure in my system this is the concept of physical backfoot with posters as long as I backed up that entire data directory right and it's off the machine somewhere else then if I need to restore that database along with all the settings of how it operated along with all security settings things like that I basically just put that data directory back on another machine and I'm back not just data data and configuration that was in that data directory that concept make sense that's correct that's great good all right let's talk about those databases I said we connect to a cluster we would look at what's inside the database so let's connect to a cluster your database hosted on faster yeah so there's a number of ways to do that either at the Linux system level or you could do that by putting data in various table spaces and assigning those to the type of stores okay so let's take a look at this right now I have I have not actually yet started this cluster all right and so let me just do that before we start to look at those databases so another client utility I told you that exists is called PG control so just tell PG control look for the data directory called opt cluster a and let's start and I get some failures the reason why I get the failures is that I actually already have a Postgres cluster running on another port so let's go ahead and fix that real quick remember I told you that the majority of your configuration is going to live in that PostgreSQL comp file so let's see if it has a setting for which port is running on it does by default it's running on port 54 32 so I'm going to change that and tell it to run on 54 33 and then I'll try to start my cluster again and successfully it says server started so again just very simple configuration changes like that such as what TCP port we're running on all the way to configuring things like string replication or both all within that file I'm going to go ahead and connect to that default cluster I discussed all right and here are my databases in some DBMS is you have some databases that contain a lot of configuration and that you're never supposed to touch because they are sacred alright that's not the case with these databases when we run in an it DB on a brand new cluster we're gonna see three databases that show up all the time those are the Postgres database template 0 and template form for years I worked with folks gross and never asked myself what are those for and you're laughing because you are too right so Postgres let's start without them we might think coming from other database environments okay that's got to be super important that must be where all the users are there's got to be super critical stuff in here that I'm never going to try to connect to that because it's named Postgres and I'm running Postgres so it's dangerous that's not the case what it is is according to the documentation this is essentially a place for a client to connect that's the number one purpose to that database remember that the cut the concept of cluster because we have a cluster the cluster is the database cluster so the concept of when we're working as a client in Postgres is we never actually connect to the server or to the instance instead we're always connecting to a database so because of that concept of how it works we need an initial place to connect and that's the purpose of the Postgres database it gives us our initial database to which we can make a connection and then from there we can issue commands that will create other databases we can issue commands that will create global cluster level objects like users but we still need a place to connect and that's the primary purpose of the Postgres database now some people get started and then start putting data in the Postgres database I wouldn't but I have seen production instances where people didn't know what they were doing and they kind of started putting database data in there you can work with it it's a regular database but then in someone who's used to Postgres comes along and they don't expect anything important to be in there they could do something that would not be good to your production data um another thing about that is you know I've actually experimental with this if you drop it nothing bad happens your default connections won't work because it won't have that default connection point but again there's nothing critical in there that you can't live without if something bad happens to it you can just create another copy from a template all right so let's talk about those templates template one and template zero all right we'll talk about template one first template one is a really cool place to put common configurations that you would like to show up on any new databases that you create I'll demonstrate that a moment but what if you take a look at the create database command documentation on the Postgres website I refer to the documentation so much because the documentation is actually excellent what it'll tell you is that under the covers what's happening when you do a create database command is without manipulating other options which we have if you just type create database then essentially what's happening is a copy of template one is made and then is renamed that's all this happened so let's try that so I will say create database test one all right I'm gonna connect to test one and it's an empty clean database right now right nothing there that's because at this point that's what template one looks like if I connect to template one and make changes then those changes will occur in any subsequent databases I create so let's demonstrate that so I'll connect to template one and I'll say create create table single silly trivial table with my name on it all right so now within the template one database there is a simple table name dear all right so now if I create another database and let's just connect back to Postgres database just so it's clear that this is not being inherited because we're inside temple 1 let's say create database just you if I connect two tests to that table shows up all right yes yeah so that's gonna take me a little bit off track if I address that now so let let's talk about it after it's a good question it's a very common question especially with Oracle experts so here's my list of relations for test two that Darren table came from the instance I created in template one all right so the idea here is that let's say that there's a number of utilities that we've built once you get used to looking at a lot of the system tables and system catalogs you may build certain utilities or views that you commonly need to know for your data set that are good for various measurements or development activities you could start to load those up on your tester development server and put them all in template one so that anytime you guys create a new database those views or utilities are always there that's the concept of template one so again database objects that we want to show up in any database that we create from this point forward we can put them in template one now let's say that I really really mess up template one what do I have that's the whole purpose of template zero template zero is not for us to touch so all these I would not have expected that that would be the one I shouldn't touch but the idea of template zero is that it is truly a blank database so when we start out with a new cluster template one and template zero or identical template one is the one we can modify with our own customizations and template zero remains that empty blank pristine database one production type usage that you'll see and you'll also see this in your backup and recovery utilities quite frequently is that because template zero is blank then walk with me through this scenario mentally let's say we start building a database so we put some objects in template one we create a new database and so those objects show up in my new database right then we start putting more data in there I take a logical backup of it and then one day I need to restore it well if I say create database new database to restore to if I say create database and it's gonna still have those objects from template 1 and then when I restore it from my backup because those objects exist in both places I'll start to see errors and collisions and you may have seen that before so that's why the best practice is if we're going to create that database for restore purposes we create from template 0 there's a clean blank slate with nothing there incidentally there is a way to force ourselves to connect the template 0 but it's not a good idea just don't do it um this is a good spot to just show you a little bit of what T sequel can do my first question when I started to sit down and work with Postgres was okay where's my gooey umm and that that's very common depending on what database environment you're coming from at the time I spent most of my time in sequel server I'd done a lot of Linux administration from a database perspective most of my time was sequel server so I was looking for a big set of utilities and development environment and all that because a lot of people have that tendency they tend to overlook P sequel and don't realize how much it can do so I just want to demonstrate a few things they can do they're really really cool to see once we get the idea that it's really primarily a a client application so one thing I can do is I'm gonna connect to the Baltimore database and really quickly I'm going to import some data right so now I have the single table in the Baltimore employee Baltimore database called employees this is public data I pulled actually from the city of Baltimore it's really nothing impressive we take a look at employees I'm just I don't need my no to yet so I'm just gonna widen this so it's a little bit easier to see everything alright so basically you have general HR information we have people's name their annual salary their job title their agency and so on alright so let's say I wanted to find all employees that work for the city of Baltimore who have my same name click star from employees so I should be a list of all the Darren's that work for the city of Baltimore one thing that we can do really easily was Postgres is manipulate this interface for not used to working with databases using a text or command line based client we might not realize that there's a lot of ways we can change how its display there's a lot of options in here we could actually have it take this table and create an HTML table straight out of this output I've done that once or twice but what I do more commonly is just use some of the switches and toggles that exist within the P sequel interface for a complete listing of them it's back slash question mark and then also next door Jennifer has a lot of nice resources for some additional switches like the most commonly used ones but if you just type back slash question mark it'll show you all P sequel command-line options and interactive commands so I'm going to just really quickly demonstrate a few so let's pull back my selects when I select and then I'll do back slash a as humans we like to see stuff lined up right so I if we take a look at this display all this white space is artificial of course right this is not part of the person's all the white space has been inserted so that the lines line up if I type backslash a the output is now going to be unaligned so if I issue that same query not everything's squished together right I don't like how that looks but now let's think about a con where do we want the whitespace to disappear let's say we're going to manipulate a flat file and we're gonna export this data into a flat file I don't want whitespace unnecessarily right so by doing the backslash a I could turn that off many of the switches that you'll encounter in P sequel are actually they actually behave as toggles so if I do backslash a again it'll just switch it back now also at the end of each of my each time I've issued this query I have a little bit of metadata up at the top I've had the column name and down at the bottom I've had a summary of how many rows are returned right it's also possible that I could turn on timing and I could have timing information for how long it took for that query execution let's get rid of that so backslash T will now only return me the actual row results so now you can see there's no header on this and my summary at the bottom is now gone if I tell you backslash a again now it's unaligned oops it's unaligned and now I have only the data all right okay so let's talk about flat file what kind of delimiter do you guys like tabs okay I hate commas because commas occur a lot of my regular data what never occurs in my regular data especially on e-commerce systems is a pipe I never see them on Shopify backends or WooCommerce they're just not there ever all right so let's say I needed a flat file export of this if I do backslash oh I can tell Postgres stop sending orkut LP sequel client stop sending my output to my screen let's send it somewhere else in this case I'm gonna send it to a file which will be temp out txt so now every query is you from here on doesn't come to my screen it just goes to the output location I told it to send it to all right so let's see if it ended up there and there it is so just think about that if I'm shying away from using this piece equal you know this weird little command-line utility to interact with my database you can see that just with three quick switches I can do something pretty powerful three quick switches even though there's other better ways to pull larger database dumps or larger flat file dumps such as the copy command with three switches which was backslash a backslash T and just leaving the default delimiter of the pipe I have a pipe delimited file output to a directory I doesn't he pretty something right how am i doing on time about half hour also remember what we said about the create database command so really quickly one other thing I wanted to mention when it comes to kind of giving yourself a safety net during your development operations could be a couple of things if we're working with a small development database and pg dump is more than adequate just to pull a copy of a database make it safe while I continue doing my development efforts another way I could do that though would be with the template command and that looks something like this so remember create database makes a copy of template one and then renames it alright now I've only talked about configuration and structure so far because I just had the empty table inside template one at this point but if template one actually had a full load of data that would be copied along as well alright so in development what I will often do is something like this so let's back create database Baltimore - template I'll connect Baltimore to so essentially I said the create database command instead of using template one is the template use Baltimore as the template and what it will do is they'll create an exact copy of the Baltimore database now the only reason I don't do this in production is because the documentation says not to but for testing and development I do this all the time alright it's a really quick way to get a quick copy of a database and just start working with it as for development purposes now remember I had to load the database the data into the Baltimore database and if I just do a back slash D there's a table for employees and there's a data alright and that was just by me saying database Baltimore - template Baltimore it's almost like saying create a new database name this use the source or your origin is the one designate just an interest of time I'm gonna come back to you transfering databases between instances without file transfer because I think that one's less useful than it used to be just in a world of a lot of virtual storage so I will come back to that one if I got time I wanna get you to these gotchas alright so we've covered the tips which is the basic organization of what you'll see when you're working with a cluster understanding the concept of cluster the term of cluster how to create a new ones give you some tips now let's talk about some of the gotchas how many of you have touched the PG HP comp file alright how many of you like banged your head against a wall for a while the first time you touched it yeah all this if you just remember the simple rule that is in the documentation that I ignore - the first time I worked with it is that the PG hp.com file is read from top down if you remember that then i'll eliminate probably 50% of any issues you have with that file as you're configuring it let me demonstrate that real quick alright so at this point this is what we'll talk about tomorrow in my presentation about just simple security things we can fix I'm go to my PG data directory and let's take a look at my PGH be calm so if I just do a basic and knit DB then this is the type of file that's created from for my initial PGH be calm notice everything is wide open everything's set to trust that is not what I want so again tomorrow if you would like to get in that presentation I'll do we'll talk about all the things you need to do to get a cluster from an it DB to something that you won't get fired for so let's go ahead and change this let's just demonstrate this concept real quick so let's say I just wanted to don't tell Jonathan cuz I know he just told you he's scram but I know how these clusters are configured so I'm just gonna say md5 so now by that rule I'm saying I want all my local connections to connect to be authenticated through md5 authentication so I should get prompted for a password now so I'll say peace equal I'm not prompted for a password right what's that oh this truth I see no you're right no you're absolutely right thanks I got that reloaded Eric's on the reload I can still get in which is problematic so let's take a look back at that file and determine why remember is read from top down so the very first match for any connection attempt will be honored so if we start with this first line is this a local connection yes okay trust it let it in it's over so because it's reading from top down the first match is honored stops reading the file and then whatever action is defined by that line is is on so let's go ahead and move these around let me just get rid of that one make another copy I'll put the trust below now I will reload my cluster and now let's try to connect and now I'm prompted for password all right so it's just something that simple to order the lines it's not a matter of whether or not the line exists in the file order is very important all right so that's one thing that can eliminate a lot of headaches if you're trying to to get that configured in the cape in the case of this trivial example that we would never hit that right because it's trying to match these first three so local a local connection through the local socket by all databases by any user because this is exactly the same it would never hit that second whatever the first match is is honored good question um one other thing that I think is a interesting gotchas I told you that primarily I'd done some my sequel by I worked with a good amount of of Windows sequel server before coming to Postgres and one thing that I noticed some developers did in ante sequel would be to enclose object or table names and quotes also I noticed that a lot of utilities for migrating out of windows sequel server to other databases also enclosed object or table names and quotes not a good idea in Postgres so let me show you why or least let me change my HP compaq so I don't have to login so let's connect to our test database all right so right now I have no tables in there let's say create I'm just gonna do those same create table commands you saw me do before so create table I'm gonna put a capital D here right for my name Darrin and I'll say a int alright so one single column for your table during exercise d notice what I deal with this the key sensitivity it's just discarded it's not honored let me put some quotes around that so in this client by putting object names in quotes I'm essentially telling Postgres that I do want case sensitivity to be honored there so by doing this then anytime I query of against that table now notice so you would think there might be some confusion if I say select star from Darrin you might think well it's gonna say hey which one but it's not because it's going to the one that is not defined as case sensitive or even if I do this and I put a capital D on it again it has no confusion about which one to select because by this by not enclosing this in quotes I'm not telling it to honor case sensitivity all right and we can demonstrate those just toss some quick data into it so insert and then I'm going to do a mix of capital and lowercase and then I want to do this the one where case matters all right so in the first two because I'm not including quotes in my in my sequel syntax select star from Darren everything's down cased let's grab the one from the one where we weren't told to honor sensitivity in the object name right but in the one where I include the quotes that still remains an empty table yep just with dodging a minute don't need to quote which tool is up okay like I say there's a reason why I mention it in a number of presentations because a lot of utilities end up doing that just putting quote names around objects and it can create a nightmare because I had a have a project where another developer was new to Postgres and he started creating tables and like I'm still living with a table that I have to always include quotes because it irritates me it really bothers me that next point debugging startup issues if you don't like tailing logs that's basically why I showed you at the beginning it is a cool little trick to just call the Postgres executable if you have access to it and then if there's anything that would not show up in a log or you just want the immediate output you can everything's gonna come to standard out by default let's play around with one more Postgres setting and that is just log settings how many of you are working with some environment where you have a m framework that is actually doing the real communication with the database yeah so sequel alchemy active records stuff like that sometimes we'll have some code that we wrote and under the cover sequel alchemy active records doing its thing and it's querying the database and I'm wondering why is this taking so long what it actually tell my database to do did it tell my database to do it the right way so I was kind of working I do a lot of Python in flask so I was working with Python flask and sequel alchemy I'm banging my head against this particular particularly slow responding piece of the software and I'm like okay sequel alchemy tell me what you're doing tell me what you're doing I realize I don't have to ask sequel alchemy what is doing I can just look at the database right so really simply we could do something like this let me actually do this so I'm going to configure Postgres to log every single query which is not hard and that may not be what you need in production but in development this can answer that question so we have a section of the PostgreSQL comp file that is about error reporting and logging again in a full several day class I'll go with you through all these settings so let's just jump down to winter log and this setting called log min duration statement alright so this can be tuned to whatever my definition of slow is or it can be tuned to log everything so if my definition of slow in my environment is half a second I could just type in 500 there because it's defined as milliseconds than anything that exceeds 500 milliseconds will be logged all right if I'm looking for slow queries if I just need to know what that or M is doing under the covers I just simply change this to a zero as you can see in the comment to the right it logs all statements so anything that is issued against my database will show up in the log now so I will go ahead and save that also I'll just jump back in here also really well documented in Postgres is almost really not almost every time that there is a change that requires a restart of your cluster it will be noted so in this case says change requires restart that's not the setting I just changed because my setting and the comments did not say change requires restart I can get away with a reload and the constant to restart versus reload as restart actually stops and starts reload just tells Postgres to reread your configuration all right so I'm going to get away with a reload here and let's see if I can see if the Internet will allow me to open another connection to this machine since it's been really slow in here a little bit bigger so we can see my logs over there all right so here my logs over to the right right okay so let's issue a few curves so let's backslash see just from that command is you you could actually see Postgres hist the system catalogs because I was using the red line library to autocomplete so you can actually see where it's actually in the system catalogs and saying hey this guy wants something that starts with Baltimore where is it and now we can see the queries issue to do that once I connect to Baltimore select star from employees and now let's say again my Orem was doing something under the covers I don't know quite what it was doing because I'm logging every single query being issued against this database I would be able now to tell how it was executing stuff all right I think that's my time yeah so again thank you so much for attending if you guys have any additional questions I got a few laptop stickers and some pins and then I will see you tomorrow if you'd like to it's in the security session as well thanks for your attendance

Channel: Southern California Linux Expo

Views: 886

Rating: 5 out of 5

Keywords:

Id: V3mA1UCkHS8

Length: 49min 7sec (2947 seconds)

Published: Wed Mar 18 2020