 test, test. One, two, one, two, check, check, test. One, two. There's a little remote switch that's not taped on top of the podium. It's a little black button if you press it. Test. One, two. One, two. Yeah, I guess I'll get started. I guess people trickle in first talk in the morning. If anybody can't see the slides, you can't see the slide. You can't see the link at the bottom, but the link on the bottom is the exact same slide that I'm going through right now, so they're available online if you need to refer back to them. So this talk is about, it's not about making packages for Postgres, it's about the different packages that operating systems actually provide, and I kind of made this both to help other people and as a reference for myself, because I kept forgetting where operating systems were, put different things. My name's Keith Fisk. I'm a database administrator on ETI. I've written a few Postgres skill tools, PG Parkman, PG Extractor, to some of the more popular ones that I've been working on. On ETI, we're an IT consulting company, specializing in very large websites and applications. We have a conference every year in Washington, D.C., called Surge, in September, and also we are hiring. So if anybody's a sysadmin or a web app developer, please come talk to me. So, downloading Postgres is where you start. For packages, for source, for application suites, demos, all that kind of stuff, the main website is where you can start for all of this. Be going over is Debian, and this covers Debian, Ubuntu, and all such derivatives that you have to get as their repository. Current Debian as of this talk is 8.7. So, for Debian, it's been better than it used to be, but most of the time the operating system for packages is not the most current one. Right now, in 8.7, the current is 9.4, which actually isn't too bad. Older Debian, it would lag extremely far behind, usually out of support range. But actually the best way to do this and to actually manage a lot of other packages is with the, if you just go to app.postgres.gov.org that redirects to the Wiki page, it shows you how to add the Debian repository that's actually maintained by the core team. And it has all current packages available. I think it also has things back to like 8.1 or maybe even 7 available in the repo. It also has a whole bunch of other third-party applications like Post-GIS, PG-Admin, they have my total PG part-man in there now as well. So, it's a very, very easy way to manage packages is to just set this up. And I'll go through that in a little bit just to give a short demo. Just the naming conventions. Any additional name on it is the name set of a package. So, you're actually going to run the database. That's the one you want to install. And that one runs, but if you're just running something, you just need PG dump or something like that, you can just install the client package. Contrib is a whole suite of third-party tools that are actually maintained by the core team. It has things like DB-Link, PG-Crypto, and all those kind of things that are pretty much essential. Instead of having to manage installing packages for each individual, one of those, you can just install Contrib and take care of that. If you're going to be doing any development or actually compiling your own packages, the Dev server package will need to be installed. Unique to Debian is both the PostgreSQL Common and LitPQ packages. Common is things that are common across stuff like PSQL and LitPQ is if you're doing application development and want to use PostgreSQL library to communicate with PostgreSQL. You can just install LitPQ library. The main server package will actually set up a default instance of the cluster. You'll have a full, just install PostgreSQL 9.6 and you will have a fully running PostgreSQL cluster up and going. One thing you can do, and it's actually seen as somewhat common in Debian because of the Common and LitPQ packages, you can version pin different things. So if you have both major versions, 9.5 and 9.6 installed, Common and LitPQ are associated with the highest version. So for some reason you need LitPQ with 9.5, but you want to install 9.6 for upgrades or something like that, you can go pin LitPQ 5 package to 9.5. You guys can pin it down to a minor. LitPQ packages, LitPQ and LitPQ Dev to 9.4 as soon as they're Debian convention. But the actual version is, and we'll get to what the priority of things is and a little bit that has to do with setting up defaults. So we've seen that happen a couple times for some clients where they had some issues when LitPQ upgraded to the next major version because it broke something. So this is an easy way to keep that from happening. This is one of the reasons I wrote this talk was to make this slide because I can never remember where the difference was put. So the default data directory is our lib and it makes a major version directory, major version number directory so you can have multiple major versions installed and it won't collide over each other. And when you have multiple clusters running, they'll each have their own different folders under each one. Same for the config files, Debian convention. Postgres default is to put configuration files and stuff in the data directory. Debian packages move all the way around to follow the Debian convention. So all of the postgresql.com to pghba.conf, all of those kind of things are in Etsy Postgresql and then just following the same name and convention as the database libraries. They're the same. If you install extensions, this is actually a really, really important folder. All the extension install, all the SQL stuff is put in this folder. Libraries for extensions are of course put up in this folder. And also logs follow the Debian convention. They're involved in the same thing. So for the package maintained cluster that's installed but with an ITDB and all of the other with PG control cluster, which I'll get to later, there's another control file called struct.conf. It's in the Etsy directory with all the other. It's a simple file that only has one word in it. And it's one of these three words, auto, which automatically starts the instance when the database starts up. Manual makes it so you can still use the script, which I'll go over next, but not automatically start the cluster when the database starts. Disable the wrapper scripts from working and automatically starting up the cluster. But you can still use like PG CTL, like any other system and start the database up. It doesn't stop you from being able to start the database. It just stops the cluster management from working. So I don't know. I usually either see manual or auto. Disable, but I guess you just want to make sure the cluster can't be controlled with... Actually, I'll get to that. Yeah, there we go. So first, the default system user makes this Postgres. The default role in the database itself is Postgres. That's your super user by default. This is the default pdhba.conf that it makes. HV is the host-based active system. Basically that controls. There's roles in the database and the grants and all that kind of stuff in the database for giving select and delete and all that kind of stuff are controlled within the database. The hba.conf controls logging into the database. It's the authentication file. So by default, only local system users can log in. What this one means is local system users can log in via socket with no password if their role and their database, if their role and the system name matches. So the Postgres user has a system user called Postgres. There's a local Postgres. So the Postgres user can log in the database without a password is basically what it means. Any newly created roles connecting via TCP have to use a password. That's what MD5 is the code for password authentication. All other access, all other users and that kind of stuff have to be set up, like remote access has to be set up manually. And by default in the Postgres URL.conf, this is commented out, a list and address. So it only lists on the local host if you want to allow remote applications in to connect to Postgres. You have to uncomment that and set that. This is why Postgres does not have a MongoDB problem. I don't know if anybody saw about a month or so ago where if you set up a default MongoDB cluster and you don't have a firewall or anything like controlled in a network, anybody can just log into the MongoDB database. So by default, you can stop after happening, but the default is to let anybody log into it. By default, Postgres does not allow any remote network connection in. So that's a good thing. It confuses a lot of people because they start the database up and they can't connect to their database and they wonder why. That's why. This is something unique to Debian and I didn't like it at first, but then I grew up to like it a lot when I had to start managing multiple clusters on a single database. It's actually very, very convenient. So this PG Create cluster is basically a replacement for the NITDB binary. It automatically creates new data directories, automatically takes the next available port that's open, manages multiple clusters and multiple major versions at the same time on the system for you. I'll show you a little bit more about that in a little bit. This is pretty much your replacement for PG CTL for starting and stopping things. I advise not using this ever, unless you really, really know what it does. If you're, this is for doing major, this is basically doing, your place is the PG upgrade binary. It's for doing major version upgrades, not minor version upgrades. Minor version upgrades can just do a restart. If everything just works, it's fine. If something doesn't work, you have no idea what wrong. It doesn't tell you anything. And if you don't know what PG upgrade does and how it works, you have no idea where to go and look and see what went wrong. So my advice would just be to use PG upgrade binary and do the stuff manually. And when you're really, really comfortable with that and you know where to go, look for things, you can try giving us a truck shot, but I would just advise not using it. It seemed to cause more problems than it helped. This is a wrapper script that just shows all of the instances that the wrapper scripts are managing for you. Just list some of our version cluster names, all that kind of stuff. If you want to rename the cluster, there you go. PSQL. It's actually a wrapper and I'll get to that. So I'm going to do a quick demo for this one because the Debian one is easier to understand when you actually see what it's doing. So all I do is I just went to app.pluscrushql.com. It takes you to this wiki page. There's a very handy command. You can just copy and paste right here that it will grab the app to get repository and put it in your app get repo and all that kind of stuff for automatically for your version of Debian. That's done. So that made that PGDG list. It just basically made that right there. Add the key for that repo. So if you do update the repo, basically you see all you have available is 9.4. That's pretty much it. Now you can see we have a whole bunch of essays. It's going back to 8.4 now. So you can see it has all the different versions of all the and a whole bunch of other packages available for you. You see after it finishes downloading here, you'll see the steps it takes to automatically set up a cluster for you. There you go now. It's setting up the cluster. If you try to log into the database now, it says there's no rule with Postgres. There's no rule with keys that exist. So if you try to log into the Postgres database, as Postgres. Since I'm not the Postgres system user, it's not letting me log in. Even trying to log in as another rule. So by default it's pretty secure. It's actually become Postgres. So the PGLS clusters, you see there's one main cluster there. So the way that works is it goes by main, like the major version from the name of the cluster, and the port and all that kind of stuff. PG create cluster. So you give it major version number, test, system D in a little while, others two. It takes a little while. It'll start up on its own eventually. It gets there. But now there's two clusters there. Now if I install all the major versions installed at the same time, you can see it's automatically picking the next available port. And I've got three Postgres clusters all up and running in less than five minutes. Yeah, actually I like the wrapper scripts for that a lot. Another reason I regret this clock was it made me understand how this works. So when I first installed mine, if you have multiple major versions of Postgres QL installed, there's actually like the PSQL and PG dump and all that kind of stuff for actually wrapper scripts. And there's a system called update alternatives. This is actually how Debbie manages multiple versions of any package that it has installed. So it does this for Postgres. So PSQL kind of suffer automatically associated with the newest version you have installed. If you need to change that, there's a system called update alternatives that you can use to do that. So for Debbie, it basically has these two alternatives that it manages. This is for the main database itself, like all the stuff there. And the PSQL actually manages all the binaries like PG dump, PG CTL, and all that kind of stuff. So you can see if I have multiple major versions installed, you can list what's managed by that as both 9.5 and 9.6. And if you want to see what the alternative actually manages, you can do dash display. It'll show you all of the binary set it manages for you. So if you need to change that, it's a command called update alternatives. And you do dash dash config in the name of the alternative you want to manage. And then it'll show you it has... So automatically, it takes the one with the highest priority. Postgresql's versions kind of go along to higher the number. That's just the priority numbers it uses are the versions of Postgres. So the higher the number, that's the one that's going to automatically use. If you want to set it to something else, you can just tell it to... So the current one's star is automatic. If you want it to be 9.5, you can tell it you want it to be 9.5 and it'll go through and change the wrapper for all the binaries to point to the version you want to manage. System D. Wrappers within wrappers within wrappers. And I'm not kidding. It keeps going deeper and deeper. So they six early versions of Debian. If you're on them, I highly advise you early versions of 8, version of 8 of Debian, not just early versions of Debian. Early versions of Debian 8 do not manage Postgres and System D well at all. If you start something with PGCTL System D has no idea what it's doing. And it will not manage the service for you. So it has no idea it's running. If you start it with System D, then it manages so. They did fix it so all the wrapper scripts are now automatically notifying System D of what's running. So it pretty much just works now. But only if you're updated. If you're not updated, they can be a real pain. One of the things it would do is you would stop the database with the wrapper script, then System D would still think it's running and you would not be able to start it back up again. So things like that. 9.4 and lower. The default to shutdown. And this is when you're using System D or the wrapper script. By default 9.4 and lower, it would use a smart mode shutdown, which would mean it would actually wait for everybody to log out before it stopped the database, which could be never. So there's an option for the mode called FAST that disconnects any current users and then shuts down the database. 9.6 and up. FAST is in full. I guess maybe it was 9.5 that they changed that. It used to be that System D would not ever use. And there was no way to tell System D that you wanted to do a fast shutdown. There was literally no way on a System D devian system to fast shutdown Postgres. So thankfully they fixed these things. And this is when I first wrote this talk. So this was actually two or three slides of explaining how to work with System D. And it was not fun. So thankfully they fixed that. That's it for the devian. If anybody has any questions on anything there. This is for all thread head based systems. Us is 7. I don't have any clients that run 7 yet. So I'm not super familiar with it. And of course CentOS 7 introduces System D as well. So I don't know how all of that is managed. So all of our clients are still using 6.8. All of the people I talk to that use CentOS are mostly still using 6. So I don't think change is too much in 7. Except System D isn't in the picture. So I have to see. The default even in 6.8 and I think even in 7 the default is still 8.4 for Postgres which is support. There are no new versions of that coming out ever. So it's highly recommended to the Postgres QL community makes RPMs available. There's an RPM for each major version. It's not like Debian was. Where there's one repository that has all of them. For Red Hat there's one repository that you install for each major version. So it even has packages for all the ELL versions. So if you want to the website for Postgres it has a list of those. So there's 96, while the Red Hat Flavors 95 and the Red Hat Flavors 94. So just for that. It's kind of the same as the way Debian does it. It has a whole bunch of third party packages available post-GIS and all those things that are like that are also available in the RPM packages. So this does the opposite of what Debian does. The one without any name on it is the client package. And the server actually has a server on it. So if you want to install the server package and then it will install the client package along with that for you. The rest of them are pretty much the same. This is if you're doing application development. There's no VIPQ package. I believe that's part of the server package. And there's no common package. It just doesn't exist in Red Hat. So the default instance is not created for you when you install the package. It just installs the package and does nothing else. The service command actually does have an initdb command for it. And that will set up a default cluster for you in the locations I'll give you on the future slide. So that will set up a default instance for you to have it automatically start up for you via services. It's the same as any other services putting in CentOS and Red Hat systems that use check and fig. And you turn that system on. And then you can use the service to start it up. You can use the service you can pass like the mode commands and all that kind of stuff to the service commands. The additional instances on the same cluster, you cannot use the service command. You have to use pgctl to set that up. I'm sorry. You have to use a default initdb to set that up. So file locations. Pretty standard for most CentOS stuff. Unlike Debian, it does do what the PutscriptQL default is, the config files PutscriptQL.conf and pghba.conf are in the data directory. Binary is in it. So as you can see, Ken Mann has mostly major versions just like Debian. The database logs are in the pglog directory in the data directory. They're not in raw log. And there's actually a unique thing for CentOS for if the database doesn't start up, you won't find that in the ptsputscriptQL.conf or the ptsputscriptQL logs. There's a special log file called pgstartup in the major version directory. So if your database isn't starting up, you look in that log file and it'll tell you why. Same as Debian for default for system user and role. The log in is still by default the same. It has to be the same. System user has to match a role in the database. But the difference between peer and ident, most people, it's pretty much the same unless you're actually running an ident server. And if you don't own an ident server, ident is the same as peer. It does the same thing as Debian does. Don't even worry about what an ident server is. So only local system users can log in when their role matches a role in the database. And for the same for TCP connections, it has to have a matching one. All other access has to be done manually. Thankfully, by default, it only listens locally as well. So CentOS also has the update alternatives feature, except that these are all the alternatives that actually it manages. Unlike Debian, where it had all of the things pretty much in two packages, one for server and one for the binaries, CentOS has an alternative for every individual binary for Puskris, which makes it a pain if you ever have to change all of them. So you can update each individual one one at a time to what you need it to be. But it does let you have an alternative, like you can have PGDump, BDump5, and PSQL B96. You can have separate things like that. So it's got pluses and minuses, but it basically works exactly the same way as Debian. The only difference is when you're looking at the config, you'll notice there's no options to pick in there that you want to do it. So the little star there, or the little plus there means. The last, sorry, the current exception is the plus. The star is the automatic one. You can actually feed the auto option to the config command to tell which one you want it to automatically be. So that's the other major difference. It still uses priority numbers, same as the other one. So before 9.0, you could only run one major version of Postgres at a time on CentOS. Unless you manage it manually otherwise. Starting with 9.0, it started putting things into major version directories, like you saw earlier. And it also has, and it used to only be one in it, defile for all the clusters. So now there's one for each major version. There's actually a whole lot more in that in it, defile if you go look at it. But these are the major things to look at. So it's at the major version. And then it uses those variables throughout the thing to set lock files and pids and all that kind of thing. If you set this, the other one thing that's confusing is, I think they changed this in 9.6. I'll have to go back and double check. But they used to set the port in the init defile. And that would mean it would ignore the port you set in the postgresql.com file if you started it with services. So that's one thing to be aware of is, if we can see if the port is set in the init defile, if it is, it will override whatever you have in the postgresql.com. The other thing that seems to be a CentOS or Red Hat convention is if you scroll down in the init defile, you'll see this section called Overlides. And a lot of people use this. What I've seen when I log into their client systems is they actually, they don't set the data. You'll see a data, a data, a pgdata and all that kind of stuff listed in the original init defile. But then it'll have this override file, which is another file that overrides what's in the first file. So that's where it's actually setting the port and the data directory in this file. So kind of confusing. It's just something to be aware of. I see that in a lot of init defiles in CentOS is this override section. So it's just something to be aware of. So if you want to manage multiple copies, multiple clusters on CentOS, it's simply make a copy of the service file. Call it whatever you want. So usually what we'll call it, like postgresql.com, 9.6, that dash main or dash testing or dash dev or whatever. Just call it whatever name you want. And then you use the check and fig, like you did before, this check and fig. So it will be check and fig, postgresql, 9.6, dash main, that will start the service, starting automatically for you, all that kind of stuff. So it's not as easy as the way Debbie and I did it with their whole wrapper scripts and everything, but it's still not too bad. It's just making copies of config files and then to point to the right data directory and all that kind of stuff. And like any other service, you can manage on CentOS. Any questions about CentOS? I don't run FreeBSD on any clients for work or anything, but I run it myself for my own servers. So figured it would be interesting to look and see how postgres is managed on FreeBSD. It's actually quite different. Both good and bad. So the version of FreeBSD is 11. So the way FreeBSD works is there's basically two ways to install software. There's the package command, which is pretty much the same as AppGet and Yum on CentOS and Debbie, and it installs binary packages that are maintained by the FreeBSD community. But the other thing that's unique to FreeBSD is you can, as the ports tree, which is kind of like Gentoo and all that kind of stuff, it compiles the package as it installs it. So it's not a binary package. It's actually downloading the source, compiling it and installing it. But the end result of either package or ports is the same. So you can do either one and the end result of the installed software should be the same. It may not be the same, but it should be the same. So one of the packages and stuff that I've maintained that are pretty much the same. Generally they're both kept up to date, but ports is usually ahead of the packages because ports is easier to maintain just download the source and that's it. Somebody has to go make a binary package for the package tree. As an example, I only just updated the FreeBSD 11 a couple of weeks ago. It's been out for a few months now. The package command on 10.3 only goes up to 9.5. But if you use the ports tree, 9.6 was available like the day after 9.6 came out from the Postgres community. I prefer ports myself. They take more to maintain, but they're more up to date. That's what I prefer. Everything's rolled down into four packages. There's a server package. You install the server package, you won't install the client package. There's the contrib package, like everything else, and the docs. I'm pretty sure that all the development stuff is contained in the server package, or if you install the ports tree, you get all the source for building your own software. And FreeBSD is kind of unique to each package you install. You kind of have to look up and see what flags you have to set, but all of them are usually managed at trc.confile. And there's usually a command that that software will give you to put in the rc.confile to automatically start clusters up and do whatever, to even allow the service system to start the software for you. So for PostgreSQL, it's this PostgreSQL label equals yes. That tells FreeBSD to automatically start the PostgreSQL service for you. It does have, like CentOS, the service command does have an itdb command that will set up a default cluster for you. And then you can start and stop it. If you want to install any other, set up any other clusters, you have to do it manually with your own itdb and all that kind of stuff. File locations. So up until 9.6, you could only have one major version of PostgreSQL installed. You can still only have the packet installed. You can still only have one major version package installed at the same time, but they're taking steps to finally get this. And I think it was because I did this talk at PGcon last year and Dan Langwell, he's a FreeBSD guy. He maintains the PostgreSQL package. He was on a few people that came by talk when I did that. And he saw that and he was like, what the hell? So he's trying to fix this. So right now you can only have one major version installed, but it's slowly changing. The config files are contained in the data directory like default. The one thing that I don't like that's default is it does syslog mode by default, which puts all the PostgreSQL logs in the syslog, which is, some people like that, if you have something that manages syslog for you and parses it all out for you, that's good. Most people don't. So I'd recommend changing the login configuration for the default to just do STDR and your own data, your own login directory. So FreeBSD, it actually does about the default version. This is, if you install some Postgres from Source, this is what PostgreSQL from Source does when you run in a DB. It has this trust flag. What that means is if the local user and the system user and the database have the same role, they will be allowed to log in. So that means it's pretty much the same as Peter, but it also lets other people log in as other users into the database. So it's a little bit less secure, but that's the default that PostgreSQL comes with. But thankfully, local network is the only thing that can still connect, so it's still pretty secure. So local minor and major version packages cannot be installed at the same time. But officially, the only way to upgrade PostgreSQL on FreeBSD is PGDump shutdown, install new package, which will uninstall your whole package, and then PG Restore back in, which is obviously not good. There's no way to do PG Upgrade managing packages in FreeBSD. So the other way to do it is just to install Source yourself, and then you can use the PG Upgrade and just do everything yourself. So actually, on FreeBSD until you, until major version packages are supported, I would actually recommend managing things from Source on FreeBSD. If you have to do this in production, you need to be able to run PG Upgrade and you need to be able to do things like that. So I wouldn't recommend actually using the packages on FreeBSD. So if you're using packages, I also recommend not using your service net DB. So there's a file in the Source that shows you other things you can add to the RC.Cost to tell you where the data directory is. So where I did that PostgreSQL enable equals yes. You can actually set these other things to tell you where the data directory is and other flags and stuff. The problem is, that still only supports a single cluster. You can't have multiple definitions in RC.Cost for multiple clusters. There is a way to have multiple instances running and it's basically the way we did in CentOS. You make a copy of the service file and when you have multiple copies of the service file installed, that PostgreSQL enable equals yes. It's telling FreeBSD to read all of the service files that are in RCD or RC.Cost. So all you have to do is make multiple copies of the config files, of the net files and it will automatically start with multiple clusters for you. It's actually not too bad. I haven't gotten to this point yet but the other thing you can do is you can also do jails in FreeBSD which is kind of like containers but it's not a container. It has PostgreSQL isolated inside its own environment inside the outbreak system you're running. So a lot of people run jails to run multiple versions of Postgres. If you see anything that's orange in my slides and you go look at them, there are actually links to go to other websites to see things. And lastly, if you want to do things yourself, on Debian to build PostgreSQL, you just have to install these three packages. This basically installs all the compilers and all that kind of stuff. It's a pseudo package for about 20 to 30 other packages. And then source also needs redline and zlib source to compile. So once you have those three packages installed, then you can download PostgreSQL source and compile for yourself. For CentOS, this group install is basically the same thing as build essential in Debian. So it's this development tool that installs a compiler and all that kind of stuff for you. And again, you need redline and zlib packages to compile it. For FreeBSD, the only thing you have to install is Genake. All right, Genake. This is from a base install. You don't need anything else. Just install Genake and then you can compile PostgreSQL on FreeBSD. And if you're actually compiling from the source from the Git repo, you don't have to do this, but if you want to be able to compile everything that's in the source tree, you'll also need Bison and Flex installed. But that's if you're just compiling from the Git repo like the PostgreSQL source that's updated every day. If you're compiling yourself, I highly recommend always setting with OpenSSL so you can do SSL connections to the database. If you have other authentication methods that you want to use, those are some common ones. Check the config flags to see if you have to set a flag to compile it. But those are some of the common ones like LDAP and TAM. There's some other languages that there's only three languages that actually come with PostgreSQL Core. Perl, Python, and Tickle. There are other Ruby and R and other languages that you can write functions and stuff in, but those don't come with Core. If you want to be able to use the ones that come with Core, you have to set the config flags for those when you're compiling. And then if you're doing development, I highly recommend enabling these flags as well so you can get some more. And if you run into a bug, you will need to have those set to report a bug. If you don't provide the information that those provide, nobody's going to help you. So if you're doing development, I highly recommend setting those. You can just use regular make, but that just makes the Core server. If you do make world, that actually makes all the contrib modules as well. All in one command. I always do make world and also makes a documentation and everything else along with it too. So you actually have to do make... If you just want to compile and not install it, it's make world. If you actually want to install everything as well, it's install dashboard. PGCrypto is a big one, a DB link, which allows you to connect to other Postgres database within the database. Trigrams for doing free full-text search. I can't think of any other stuff in my head else right now, but yeah, those are some pretty common ones. The source, I actually recommend looking at the service scripts that the packages make for you to see what they do and how they work for doing the start... and at these scripts. But the source does come with some basic examples for how to have start-up scripts to use, but I recommend more looking at the ones that the packages make just to see how they do them, even if you're not going to use them. So if you're... to configure flag to... first when you're compiling, you always do configure first. So if you want to tell it where to put the binaries, use the prefix grand. I recommend giving a prefix of a major version, like pgsql... I usually do it not. pgsql96i5, whatever. You can do it down to the major version, down to the micro version, but then if you have a lot of third-party extension installed, you have to reinstall them every single time because it puts them in the share folder that I showed you on the other location. They're in that directory. So this saves you from having to reinstall all your extensions every time you do a minor upgrade, which is at least every quarter usually, if not more often. So I recommend just doing pgsql major version. And then what you can do is for your net scripts and all kind of stuff in the app directory, you can just do a sim link. So you're always... if you want to keep your upgrade from 96 to 95 to 96, I can do this change of sim link. You have to change your net scripts, your path to where the binaries are located and all that kind of stuff. It's just automatically managed. And for the data directory, I recommend having one... like physical mount point for your data directory and then make folders for the major versions. Don't have each major version on that own mount point because then you can't do a link... if you do pg upgrade, there's an option you can give it called dash-dash-link. What that does is it does hard links from the old data files to the new data files. So it can be very fast. We did a 750 gigabyte upgrade in I think 15 seconds because it just said a hard link. But you have to do it. Yeah, you have to... as long as the major version is in a folder, then you can do that because it can't do it across mount points. You can't do hard links across mount points. That's it. A couple links there for you. The slides? Yeah. Pg upgrade? Yeah. The question was if there's any issues with pg upgrade. I haven't come across any issues with them. Let's say myself. The really important thing to do is obviously test it before you actually go do it on your production system. Pg upgrade also has a dash-c or dash-dash-check option that will do a basic check to make sure it can do the upgrade for you. It'll go through the... make sure the data file is okay. Your config file is okay. It does a very basic check. Don't rely on that to tell you that your production upgrade is going to go fine because it may not. There's other things that could happen. But the problems I've come across are not pg upgrade files. They're like... you're missing a package for your extension that you installed. You have an extension for 9.5 that you compiled yourself. You forgot to compile it for 9.6. So when it goes to start up 9.6, it can't because your library file is missing. That wouldn't be found by the check option. That would only be found when you try to actually go and do the upgrade and start up your cluster. So, yeah. But issues with pg upgrade is other than what you may see like looking at the patch notes of bugs that are fixed, I've not actually encountered any of them myself. Okay. Thank you. Hello. It works. I'm Sean Jitman. I'll talk to you guys about Postgres and ZFS. I'm familiar with ZFS. A handful of basics. And if you haven't used ZFS, I'm going to convince you, hopefully by the end of this, that if you're not using ZFS and you're not on a cloud provider, that you're probably losing data. So that's a diastric story. If you're not on a cloud provider, I'll get to some of that as well. Parts laws. ZFS is a big chunk of code. A bunch of gray-beard type people have worked on this type of code. And they've been doing so for a long time. And they did so based off of an enormous amount of research. And it turns out the people who were dealing with this were not so gray-beard. And they've just spent an enormous amount of time doing the development of ZFS based off of practical experience. And as a result of that, they came out of their lab after 10 years of kind of toiling with this with a decent file system. And it was released to the rest in 2005 and kind of declared production ready. And I'm here in 2017 talking to you about it. The size of the code base is north of 500,000 lines of code. So there's an enormous amount of work that's gone into ZFS. It's not magic. But it represents a huge culmination of effort from a bunch of different people. And its ongoing development is one of the important things that I also want to point out. Even though... And file systems are very difficult to get right. But at this point in time, it's very battle-tested. And the features and functionality there has come and been born out of practical need. So, postgres and ZFS. Why do we care? And what's the gist of this, right? So, political joke here. Is it too soon? You guys, like... Some of the internals... I'm spending more time on the internals, actually, than... So if you're familiar with ZFS, I'm going to apologize a little bit in advance. But ZFS was built with administrators with the file system. Of ZFS, because without that good understanding of ZFS, things will appear like magic. And that's not okay. We need to have... We're betting our business, we're betting our database on this. We need to understand what's happening under the hood. How we use this in some very suitable and best practices. So, I'm going to gloss over a bunch of the details. The ability to go in and recycle the pages. But for all sorts of purposes, like... This is where it really kind of looks like from the operating systems resources. DBA is, like, what are the handful of things that we actually need to know? What we do, right, is PG Start Backup and PG Stop Backup. And that's basically it. As far as the internals of postgres go, we don't have to know minus one caveat. Actually, when PG Start Backup backing up, this is universal. As soon as we run PG Stop Backup, we can go and take a snapshot. Because, like, we do actually want to control the I.O. And so you can do this in a couple... Handle it in a way. You can go and force a checkpoint before you re-develop this. This is a nice, smooth thing, but it takes potentially a long time. It's an hour. And you remove your, you know, or sink your database someplace else. So it turns out there's a lot of similarities between VFS and Postgres. Same thing, but we're now chasing that. In one second, it benchmarks... And also has something to do with the safety. Because everything is modifying never effectively, because it's a mutable application down to disk, VFS, one of the things that you can, should, and always enable is... In any workload and in any benchmark, we would see 10, you know, just basically zero. LZ4 is a nice compression algorithm because it has an early abort in a short circuit of practical progress. As a result, enable it for everything. For throughput and for day to density. Yes. It didn't used to be... LZ4 didn't used to exist. And it's basically... We didn't enable it because in order to enable it on the root, you have to go and update bootletters. And the only... If you're provisioning VFS, to begin with, we're going to enable LZ... Now shipping LZ... I know it's not on the root files, it's the whole ZFS implementation. Using LZ4 by default. Day job. We have a series of articles that will be coming out soon. Ubuntu and images. We'll reuse the ZFS. That's basically it, though. It used to be LZJ9. If you enable that because you're like, hey, I really want high density compressionized. It's latency perspective. So if you're going to use GZcopy on the right kind of structure coming in, if you're appending or you're making updates to data in related data like a user that blocks some place else on the drive head, or even on SSD, you don't necessarily have that performance characteristic. You have this high degree of locality and it relative to the same record. So you now have locality. But if you have like data, it typically compresses well together as well because it's the same data. It enables the same set of values. You repack. Row one. Row two. Likely the same. They may compress to basically nothing. If you have an entire table of sorted values because you're going to find that your tables come very fast to scan. So in one swing, the multiple there is a multiple based off of the compression ratio that you see. If you're doing LTP type stuff, well that's, VFS was a huge, VFS has a feature. If you're not using VFS, you remember the drive that you have on the server. But 100% a little close, right? Certainly that you're going to go to another, come back and it'll say, hey listen, I went and fixed like four and a half case, scanned 100% of your data set, a dozen servers. And then all of a sudden, I don't see you, even for a cloud object upset me. So, that's a lot about VFS. So, you're thinking about how to deploy. Even a VDEV, RAID-Z, let's say, like, so, you go and take five disks and you create RAID, you take another five disks and you create another RAID-Z VDEV. Take another five, the last five disks, and you create a third. You can, out of any one of those disks, right? So it means that in total, you can lose three disks for that particular server. As long as those three disks, those VDEVs, if you lose, that's a game over. If you lose a VDEV, there's the Z, ZPS, so you've got this, an object, because, I want to come back to this, but I actually found that there are ZIL devices in different configurations with the ZIL, because of some rock storage world. When I mentioned that ZFS was complex, there's a pile of other things here that I'm glossing over. Just know that it exists. Each one of those writers is doing something very important in the entire stack. What does it look like to actually be using ZFS? So, ZFS exposes this Z, this is an aggregate developer. So, ZFS, I'm going to create a data set in this particular case. I'm going to mount it to an arbitrary directory. You know what I'm saying? When I say that the positive layer, right, when I'm exposed completely independent of the pool, you won't hopefully ever have to run into, but if you do, you're going to have just like in XFS, or XDS, if you run out of disk space, free space on a file system, it'll take you to, you have this extra buffer where you can go to this extra reserve. Me two percent on the internal allocator, change the stride and go through some extra work to refine some available object that you have to do all of this yourself. Out of disk space, no quota setup takes up to about 84 to 85 percent. You're okay. You just set a quota. All right. So, we created this slash db data set earlier, and you can set these attributes onto the physical pool itself. I want to actually bound it, so you can see, right, it's not the same Byzantine ed quota user work after the quota add. Guarantees that you're not going to run out of space that you should join in ZFS, kind of by design, but it does most everything that you would want for you, with the exception of a handful of things. So, A times, I'm going to update the D node of the I node that says, hey, this particular file was accessed, right? You read something like you did a cat or like there's a read operation. But that causes it right. You don't want to do that needlessly. So, because it's a background for everything just like all the class systems, but to say, tune the record size. This one's interesting, because you would think that you'd want to set your, in theory, going to end up with higher cache utilization. If you were inside of memory, set your record size to 8K. If you're using a, if you're working set through your database, is larger than your available memory to 16K, which seems kind of counterintuitive. And there's one other thing that's also counterintuitive about our physical size so that gets written to disk so you actually want to, in order to have, like if you've got highly compressible data, you want it to be able to read as much as it can and have a load. It's really, really surprised us. We found that because we were basically pre-faulting pre-faulted the next page and so the amount of time that we, like we didn't have to rely on ZFS's pre-fetcher instead that the pre-faulted in from the previous. But it wasn't universally true because when you do that, you're going to load the amount so it's somewhat counterintuitive but that makes sense to this people. And the last one is primary cache. So primary cache, you can get cached inside of ZFS. So you would think you don't want to necessarily double-offer them. Turns out that that's also not so straight. Where does it look like? Going grab the records, change what you write. Everything in ZFS is heuristic towards the end. You don't want to spend any memory inside of the other cache on caching user data. If you wanted to free that memory up for us to the disk and we were similarly surprised right that way up set an explicit hard art default for lastly, when you do some of this to the disk I don't care what you and that's because snapshots where we would hold on to some snapshot delete mechanism you need to give ZFS a chance to breathe and actually do your work to go and delete stuff so you can stop your the seasonality of your workload during your rolling back is you know that's it it takes seconds you own because of the way that the block pointer rewrite and the changing of the pointers to the different D-nodes inside of ZFS happens like that's a very fast rollback rolling back a 30 gig delete operation where you manually actually zeroed out the data it's like 10 seconds and that's for like gigabytes of end-oops so we were able to take these developers that did a drop database because they were talking to the prod database for reasons that I don't want to get into we could restore and get things back up within minutes and we could get to you know without having to go to a point in time ask me whether I'm going to restore from point in time recovery or just typing ZFS rollback and getting it back to the minute and thinking the rest is good enough to be fine in a traditional block the intent log disk transaction log disk because ZFS is providing you you will be on a consistent state because ZFS has no inconsistent stuff with a functioning database every f-sync operational position because you would potentially recover the database mathematically check some like doesn't that like how you want to slice you want to get an increase in throughput 100x potentially right but really like conservative I'll say 10 where you much higher threshold to the point where you're going to be presenting back to group timeout to one second and now your risk exposure is potential beta loss of one second at the for my loss of vision perspective with the benefit of you don't have to worry about back pressure causing outage for the upstream so the things that I didn't talk about are the ability to send snapshots and to spin up and clone they're very simple things to do but because it's all copy and write hopefully you can and with that I actually just ran out of time so if there were questions going to the same data set because then your snapshot with this thing here it was sending low bias throughput to a lot if we have back pressure it never ended up being the case because this act it's just fantastic shock absorber to the rest of you you can't do a PG base backup from a slave you can only do PG base backups from the 1, 2, 3 yes you're good to go so hello everyone it's time to start right now my name is Ilyakos Medivianski I'm working for DataAgrit and I will present something really good for the launch presentation Linux internal funny and complicated thing and no one I know hope won't fall asleep so what is all about where this talk basically running a database on Linux today is not doubt a good idea because currently it's probably the best operating system for database specific workloads and modern Linux is a very complicated system actually and it has a lot of configuration possibilities and the default Linux configuration out of the box is designed for default work world and the default work world is a quite rare thing I think so basically that means your Linux out of the box is not properly set up for anything not for effective energy saving on that top and not for database server why specifically are your problems because for database problems usually is the main headache and people run into them and trying to figure out how to deal with that issues with CPU or memory or some specific problems with interaction with Linux scheduler is slightly different thing but actually everyone finally end up with IO problems and they are tightly interconnected with many other things like CPU issues and time so there are lots of information about that so you can read lots of information about databases lots of information about Linux kernel but there is one problem most of that information is written by kernel developers for kernel developers it's really to understand what do you need from that information and what do you not we need to turn this system catel parameter on something like that last year I actually presented that sort of talk here at scale and it was just then you prepare your Linux installation for database then some internal mechanism need to set this parameter to that actually helps better than just mechanically try to follow the advices of people you can listen or read so the checklist talk but still switch to tune and the main IO problem for databases is actually in this thing so it's quite difficult to figure out some sort of file source or something like that we just can speed up the storage and yeah we're done but one thing is that actually problems can be not exactly in the disk system because if you have lots of problems with CPU that's probably looks the same and some in case of that's it but if we want to write data we just write it as a complete page database work we call that to schedule the transaction algorithms they work with pages good but at the same time we have problems from pages because it's difficult for people to work with disks which historically do not operate with pages they operate with sectors the database the database we can run into later in terms of performance because at every stage of this layer it is very complicated one and we need to maximize throughput at every so today several bytes of are designed for small amounts of memory and for large amounts of today everyone likes big data if we like big data it costs huge amounts of write-to-head log and it can be also the performance issue and for durability things for databases we cannot write-to-head log so it can be also an issue and the system is involved so the first thing is memory that's a very schematic modern Linux works virtual addressing or CPU to operate because virtual memory is a very good abstraction so we think that it's infinite one and it's easy to operate at some point we need to translate that to the physical memory because we access the operating system accesses memory when we have not a lot of memory it's quite easy we have a special unit which we call memory management unit and it performs the translation from virtual addressing to physical addressing it's quite easy so if CPU needs some page inside your database it goes to memory management unit and it decides okay we have this page in the page table and this is the shortest way or we need to allocate that and it's slightly more long-term way but the problem is that if we have on our database server that can be difficult operation and to save the time when we have more memory we have some cache which we call that's a normal cache it actually caches the result translation operation and if we have 32 64 gigabytes of memory and for a database which operates with huge chunk of as every usually separate huge cache it has problems of huge cache cache misses the size the overhead you put the page on the cache it's not the cheaper operation then you never use this page like any other cache so the TLB can be very huge overhead and for reasons of database performance the concept of huge pages was introduced and the basic idea is quite obvious let's allocate memory not by default you know 4 kilobytes huge business and that works much better because TLB is smaller and all the problems theoretically will go on and many database companies are involved actually in implementing those things for Linux so the daily takeaways will be if you have huge hardware for settings and probably you want to have it if you have a large database better use huge pages it's quite easy to find how to enable them most of the database is currently more or less support huge pages Postgres Comma SQL and DB2 about the things that there are two so-called huge pages which you allocate not at the start of the database but dynamically if you need that but this is viewed only theoretically because this shrinking and this recombination of those pages can cause serious performance problems underneath the database because database is designed to have a huge chunk of memory as a chunk and if your shared buffers will be constantly migrating between shrinking huge pages that will be a huge performance overhead so if you run database on Linux better use huge pages but disable the transparent ones use the old school not modern huge pages we are going far with memory management in modern Linux that's actually a very simplified thing a simplified schema but there are a lot of confusing terms in Linux about swapping, pegeotin and so on and so on classically we understand the swapping the system 5 swapping and the interpreter goes to the disk if we have not enough of memory in Linux actually swapping is a slightly different process and actually swapping is more pegeotin in terms of classical unixes so how it works for example we have a page cache Postgres used buffered IOR and actually we have shared buffers and pretty much the same size of kernel buffers huge amount of dirty and clean pages we get actually we have lots of free lists complicated story and I don't want to stop on this a lot we need a page we get it from free list and then we don't need this page we return that in some way to this free list and actually then the page is returned to free list it stays pretty the same page if we do not read all the term reclaim so the kernel there are several things of that so if we run out of available memory for location Linux tries to page out to take the pages which are recently used and put it to a free list and if the database the user space application for kernel don't need those pages finally they will be cleaned but this process is actually quite successful if we have memory and databases does not use that pages extensively so at some point we can run out of the pages so the free list shrinks and we can hit these VM minimum 3 kilobytes parameter which we set for system and when this size of free list will be hit we try to page out the pages from page cache intensively there are two basically ways how to do that one is in background and another one is foreground so it's a high priority process and it actually itself can cause some problems because then your Linux flushes the pages to disk it's not a really simple operation and not really fast but another mechanism which is actually swiping in a Linux way is putting some separate pages to the disk if we do not need them and we need more memory to allocate so basically page out was a free list and swiping is the separate process so if we need some more pages for our database because we need to read something from the disk or something like that the first step is try to page out pages from that if we cannot do that database constantly uses all those pages we try to put that to swap and we have several parameters to make that to regulate that but finally if still it doesn't help comes or I'm killer and kills some random process which consumes memory if you can guess which process it will be most likely it will be your Postgres or your Oracle or your MySQL and that's not the idea you actually want so for those purposes all those parameters like this and the panic on you exist to regulate the ratio or how kernel behaves then it needs more memory how it works the page out then someone calls fsync for example checkpoints and Postgres if the theme is heated and too many dirty pages exceeded a dirty ratio theoretically it's reasonable to play with these dirty ratios because by default it's really huge parameter and they do not fit then you start flashing using those parameters they do not fit inside the cache on rate controller but nowadays it's actually if you can afford good SSDs better use good SSDs instead of rotating disks so today those parameters are slightly outflat and you probably need to play with different parameters to make those things better first of all do not use crazy settings for the Oracle Meet so if you try to make your Linux feel that available virtual memory is swap plus actual ROM that may be good for desktop but try to transfer folks or ROM with hundreds of tabs and you figure out that's a bad idea but for database it's by default a bad idea do not do that so better just reduce the swap usage in the Linux way to just setting the memory over commit to zero another thing minimum three kilobytes it's actually very crazy settings by default and if you remember those schema it's quite easy to hit it on the server with a lot of memory with default minimum three kilobytes because you constantly need a lot of memory for your shared buffers or system global area depends on which database you use probably use postgres if you're here but it's a good idea to increase this setting because then you have some more room not to hit the old killer another thing is to switch them swappiness to one before 3.3 I guess Linux kernel we usually suggest to set it to zero just to switch the enormous swappiness because by default it's 60 percent that 60 percent cases kernel chooses to put the pages in swap instead of page out it or read in some way so basically if you have a machine with huge amount of memory some portion of this memory will be permanently in swap and this is the quite bad behavior because swap is a real thing especially for database but since some versions of third Linux kernel the behavior slightly changed and if you put the swapping is to zero that means that sooner or later when killer comes and kill your postgres or any other database you are using so currently it's much better idea to set it to one because it is the amount of just swap pages without any reason and still your database works well you have some sort of insurance against the or killer incant you probably will afraid if it kills your database disable or killer by the mechanism which I exist and it can be much forced because you have a temperature panic on OEM then we out of memory we have a kernel panic which is better you have a database which is killed by on killer or you have a kernel panic it's not to have or you can for certain process just set some low priority as adjustment for on killer but actually just have an OEM killer enabled system it's normal mechanism of safety for Linux but try to figure out how it works and adjust respectively the settings which I mentioned before VM swapping actually is the important one and three killer bytes actually too so you need to increase that reasonably if you have a lot of memory because the number one performance issue with IOR after we have a journal 85 system which means basically we have some form and database inside because we have I0 and we have journal entries for database server because I never saw an effective barrier which are a huge workload of data so what actually happens for kernel finds out that we put down to the disk several I0s and we are lucky enough journal data for those I0s it starts the special system call which effectively blocks everything up until it resorts the kernel buffer to retrieve those journal entries and to put them to disk now we can imagine if we have a huge database of future buffers and the huge kernel buffer how long can it take to resort those things and this is actually a very bad thing because even we have a rate with NSSD and it's good and performs well basically these there are all those benefits today in progress actually since 9.6 there is a quite nice mechanism of resorting the pages before putting them to disk but still no one can guarantee that this sorting can be definitely successful so if you use a database server with proper server SSDs which mean they have a supercapacitor which allows to write the last data from the cache if everything crashes they are off the new bearer because basically if something crashes and you lose your data because of proper urgency battery or capacitor on your disk the journal will not the system will still think that okay we have a journal but probably will lose some journal data and yeah you can nothing do with that but actually slow down the system drastically so if you have these hardware possibility do not use them actually about the third point at this moment I should tell that there is a very good talk of Thomas Wondra who benchmarked different heavy rocker load mostly write works we actually see that XFS and X4 are quite good and the difference between where them is not so high and if we compare that to ZFS we fall the bells and whistles about the provisioning and resizing and so on the performance actually drops because nothing comes for free and if we have bells and whistles that falls things down and if you have a proper upgrade this is this it's better to switch the barrel off and this is probably this talk but it's a complicated one it's Linux IOS tech quite simplified you can Google Linux IOS tech and probably it will be a diagram of this is only important things for our database so we have some database memory it's shared memory process memory many things and to put the pages the data from the database to finally disks we need to go through all those layers so we have a standard Linux interface a virtual file system which provides the interface to interact with all known file systems like X4 for example and different things from our database are written in different ways Postgres mostly use it always use in productive environment buffer retirees so it puts the data to page cache and actually shuttle buffers today in Postgres are mapped by in-map not the system file memory allocation method so it actually goes directly to page cache and then to the underneath to the block wire to automatically Postgres can write the data directly using all direct but only wall and we have no replica and no backup so basically Postgres uses block higher most of time but the thing is that now we need to make the final transition so it's difficult to go from equal to the pages but it's even more difficult to go from the pages which are in the memory in the kernel and so on to the sectors and cylinders on the disks and was an issue for quite a lot of time what are you where if you just take a look into Linux kernel sources research structure and basically Linux collects simplified of course to some IO sectors and put that through the kernel to the disk because your disk drivers need to interpret that and put them to specific sectors so basically it's called the request wire specific Q as with many pages and finally it goes to the disk the problem is that for a long time the system was not quite I would say well designed because it was old and people have an old time probably to improve that but it's a long story the problem is then you have lots of pages from typical database so it's a bunch of unconnected data which goes to the disk you need to handle this request wire properly and for that purpose in Linux exists the part of scheduler which we call IOS scheduler or IOS elevator so the idea is that if we have lots of block layer we need to coordinate that in some way to put it most effectively to the block device interface actually it's some kind of keen align in the memory structures to put them better to before Linux kernel 2.6 things were pretty simple there was a called Rhinocelerator which performs some simple algorithm it's not a huge file actually you can find the old Linux kernel and find out how it works difference margin for example different request to write something from the closed cylinders on the disk write request into one vector and put them to disk that's theoretically more efficient but this had a lot of problems the first problem is actually quite common was so called starving so basically the scheduler performs well for specific write request and because everything is busy and scheduled to not reading at that time of any kind system is starting on reading so you can write effectively some piece of data but at the same time you cannot read effectively there was a lot of problems I really recommend if you're interested to read a new paper of Losi at all which raises the problems of those resorption and management schedulers and linux with really good tests and statistics about that and the name of this paper actually quite well summarized what was in the previous linux journals with scheduler and it actually has lots of sites from linux about that we have 10 years old and that's because it's a good scheduler that was actually not the response to say that so basically one endeavor problem about linux scheduler they prefer with some sites from linux about some things changed in linux kernel 2.6 we have now more linux scheduler but we have actually cfq was a further implementation of the same approach but much more efficient and much more complicated and you can see that between really the third version of kernels and 2.6 there were plenty of schedulers there was cfq there was deadline and it's a better scheduler which was actually a kind of deadline scheduler and finally noop non-emerged this diversity of schedulers actually says that none of them to cope with modern tasks it became actually a nightmare to work with that so basically this version and solution even in very smart manner was not enough to work with modern hardware the first reason was that all those schedulers were designed and all those mathematics was actually to put the data efficiently on the sectors of magnetic disks with SSDs particularly it's a bottleneck because SSD can afford lots of parallel data and those schedulers were so dealing with Linux kernels 3 6 7 8 we usually advise our customers to use noop scheduler than it appears because basically that's how much it can write just write if you have a good SSD probably it will have no problems but then in I think the first version was suggested in 2011 and it was first merged in 2014 in roughly 13 Linux kernel there was so-called bulk and coom scheduler which is actually magnetic disk scheduler on SSDs it simply doesn't work like that and actually about to utilize the parallelism of modern storages like multi-channel disk arrays like SSDs using more effective scheduler actually it's not the final thing so people do improve things adding some ideas to bulk and coom scheduler now exists but basically DA is much better the idea is that each CPU has its own it can manage that and disk scheduler is aware about the new architecture but regret to say not one in every database is aware about new architecture but still it's a huge step forward and actually here and in this year we put Apple scheduler to many customer installation if we find out that it's quite a good idea because you know the performance even in improved drastically so basically finally Linux switched to torrents quite better as a dba take away to try that first and then to use that up until now we didn't run into serious problems with bulk and coom and it performs quite well so I need to thank my colleagues Alex Alex and practically with every new version of Linux kernel about that and if you have questions don't just stay to ask them and we have around five between five and ten minutes to ask the questions so thank you because we have a record in that if the bulk and coom is faster than no oh yes in many cases then we have huge workload it's faster not so much but actually bulk and coom are quite good but right performance with bulk and coom to have it in virtual environment the problem is that grand macalister the next room in virtual environment is quite different thing and actually problems can't you hit the schedule of performance so I would not say that I suggest that you will have record performance improvement with bulk and coom instead of no if you have a virtual environment most likely you will unpredictable what and so on something like this quite early yeah I just need to clear if everything works like it's designed nothing should happen about that because as a database as a Postgres or in our database operating system actually has some mechanism to figure out what's used and if this is a dirty page in the memory which is recently Mojang itself can think that we do not need this page because actually page outing works only with pages which can be safely put to free list and can be safely freed but Mojang killer doesn't care about what's recently used or something like that kills the process and who cares what sort of pages was using by this process for databases or not I would say I didn't so actually if you have some analytics you need to put it's through bullets to solve the performance the data if you know and your queries and every part of fire stack is well turned probably it will work better than just to using some information to speed up with performance I would not say that because databases are specifically designed for high performance and more or less if you it's a highly optimized process in operating system especially on the traditional I-NOT it's non-specific design so it can be more it cannot be more effective like database on dedicated database servers but it is for device so this is on 473 clicks go ahead test okay Postgres backups and disaster recovery are safe let's talk my name is Biol and I am a DBA at RNETI we are a consulting company specifically Postgres there are any of the ways you can get in touch with me for any reason Postgres related whatsoever even if it's just to tell me how bad my talk was or if it affected you on a deep spiritual level whatever it is I promise I will listen to you the agenda for today's talk is to look at the types of backups we have if most of you are already using Postgres you will know majority of what I am going to cover here but then we are going to talk about backup validation what is validation what does it consist of is it really necessary how often do you need to do it what other things do you need to do to ensure your backups are safe to ensure your disaster recovery is just as it should be do you have to automate things not necessarily but would you want to if you are thinking about it what sort of effort is required to automate backups in disaster recovery there are certain file systems out there that can help you enhance your disaster recovery process hence VFS that's basically what I am going to talk about then some of the third party tools that are my favorites if you do not want to do everything yourself you can use any one of these tools to help you perform reliable backups and restores and lastly if you are interested in knowing the internal details and want to do everything yourself you can go for an in-house solution this is going to be totally up to you but I am going to provide a few skeleton of scripts to help you figure it out before we start talking about types of backups there is one important discussion you should have with your team whether it's your higher up management or your colleagues you need to set up first thing recovery point objective which in very simplistic terms is how much data can you afford to lose if you run into a disaster for example if you are taking back up say every four hours and the last pack you took was three hours away and you have a disaster your database gets dropped if you restore and the restore takes less than an hour you are meeting your RPO deadline and you are up with people's expectations you are not disappointing everybody the second thing you have to discuss with people is recovery time objective which is basically how much time will it take you to restore after a disaster now this does not only include the time it takes for you to actually restore your data but it also includes the pre-restore steps as well as the post-restore steps we are going to look in the slides ahead that these can make up for a good amount of time as well so yes if you haven't already please have these two expectations discussed amongst your company and then let's get started with actually the types of backups the first one is logical PG dump and PG dump all Postgres core provides these tools excellent and everybody should be doing logical backups on some level even if it's not of your entire cluster your entire database please have backups of specific important tables at least or certain database objects that are critical to your infrastructure and then the second type of backup is physical or file system level backups they can be offline but nobody here I'm sure is a fan of switching off your database once a day to take the backup so most people would go for the online option it requires no downtime and it's most commonly used we're going to look at why and what is the cost of taking back us online it just makes it a little bit more complicated in terms of more steps but really it's completely worth it the advantages of logical backups which is basically PG dump and PG dump all you get granularity a lot of granularity while taking the dump as well as while restoring you can pick and choose which objects you want to make backups off which objects you want to restore you have built-in compression especially with PG dump PG dump all doesn't provide that but PG dump does and PG restore can be used with compressed file backups to further speed up the process and it's easy to use this no extra setup required if you have post res installed you have these tools available the disadvantages is compared to file system level backups logical backups tend to be slower they are frozen in time that is there is no option for point in time recovery because logical backups only access the higher level data they do not have any details of the underlying cluster of the underlying data set it is difficult to know how to use the walls generated to restore it to a certain point in time which is why they are frozen PG dump specifically which is used by PG dump all needs a lock on the database objects to be able to grab the data in it and this happens on a per table basis so it's going to grab a lock on table A dump out all the data release the lock and go on to table B and because it takes locks on the database number one it can affect your other processes but more importantly your data your backup is spread in time so table A might have the restored data that belongs to a certain point in time X which is not same as the table B's restored data one advantage why PG dump all should be used is to backup the global variables the global objects it is the user information the group information in Postgres as well as the access information typically how you would go about setting up a good logical backup is to use PG dump all to only dump out the global objects there's an option in PG dump all to do that and then you would use PG dump to dump out a specific database that you use it requires super user privileges in most cases because the way PG dump all works is it connects to a cluster and dumps out every single database that's in it for people who come from other databases in Postgres what we define as a cluster is basically a collection of databases running on a single instance so when PG dump all connects to a cluster it needs a super user to be able to access every single database that it needs to dump out it is plain text only there is not as much granularity in it as PG dump all provides it cannot take advantage of PG Restore because PG Restore requires compressed backup files you can obviously compress it after it's being done but that doesn't meet the requirements for PG Restore physical backups almost always faster than logical backups if you have a snapshot based file system you can also snapshot incrementally and this is a very beneficial feature in terms of using less storage without the expense of more time during a Restore if some of you attended the earlier talk of Postgres on ZFS the person covered this and basically it's a great great benefit that ZFS offers I think in time recovery is possible using file system level backups because it has all the details required to be able to restore one whole at a time you can tell it up to which point you want it to restore and that's great by default some file systems have the compression option on ZFS doesn't so you will have to enable it on ZFS last granularity unlike PG dump where when using PG Restore or PSQL you can actually choose if you want to only restore a particular database object you cannot do that with file system level backups you have to restore the whole cluster why do you need both these are only two of the many scenarios in which different kinds of backups can help you save time so if somebody by mistake drops your whole database obviously you need to restore the whole database and in which case file system level backups would serve as a faster restore than logical ones if a mistake only drops a table in a rather large database if you have logical backups that's going to be faster because you can actually choose just to restore that one table and there can be a number of scenarios so having variety in your backups is always good and redundancy is good when it comes to backups this backup is another option that comes with core Postgres it is great, it is used for making base backups there is no explicit backup mode required so you don't have to manually put Postgres in and out of the backup mode to ensure that all the wall files are collected all the wall files needed for the backup to be consistent pgbase backup will do that for you internally multiple instances can run at the same time however this will come at a performance impact so if you can only run the one instance and then you can copy that backup created wherever you want to backups can be made on master as well as on the standby if you want to do it on the standby you have to have certain extra parameters to have certain values if you visit the documentation page I don't want to go into the details but it's fairly clear the disadvantage is compared to snapshot type backups this can be slower backups are always of the entire cluster which is just like any file system backup so this is more of a disadvantage compared to a logical backup not compared to any other file system or point and time restore backups poor amounts of pgbase backup not too many if you have a super user, use that or if you have a replication user which in most cases you will if you're using streaming replication you have to have any one of these to be able to carry out a base backup make sure your max wall senders it's a parameter in Postgres the migration file is set to at least one but I would recommend you have it set to more than one in case you have a slave running streaming replication and you need to have one extra slot for Postgres to have the sender process for base backup and of course Postgres should be archiving making wall logs right ahead logs another kind of backup that you can have because several kinds of backups of a disaster which you don't know how bad it's going to be better is delayed replicas as backups in situations where there is accidental damage so let's say someone comes to you and says I carried out an update and I want to see if you can get me the data the way it was 30 minutes ago and if you have a delayed replica let's say your replica is delayed by one hour and so that basically means if anything happens on master any transaction it won't be replicated until an hour passes by since it was run on the master so that way if someone comes to you asking hey can you give me how the data was 30 minutes ago you can say yes I can and this is going to be faster than the other two types of backups that we talked about this does come at a cost where you have to maintain a separate replica aside from a failover slave just so it is delayed but if you can please go for it the last kind of backup not exactly a backup traditionally is version control DDL OnETI has an open source tool called PG extractor which can dump out database objects in files of their own so if you're dumping out functions, triggers procedures each one will be in their own file and that way if you commit those changes let's say every night of several times a day in a version control system you can have a history of every function's source code so if anything goes wrong you can just get the earlier source code restore it back and you're done you don't have to touch the bigger backups at all so this is going to speed up that kind of a disaster that's it with types of backups if there are no questions we can move on to validation okay validation basically means continuous restores why is it important it is important because if you're restoring routinely or continuously you can meet expectations you know what others should be expecting how much time would it take what are the procedures what external factors can affect your validation and this in turn goes on to affect your recovery time objective one of the business continuity objectives we talked about earlier one thing about RTO is it's not a one-time thing if you set up an RTO five years ago and let's say your database grows at an average size five years from then you're going to have an RTO that is going to be more than what it was so this is something you should routinely revisit validation is important to know whether the backup even works development databases and reporting databases are a good opportunity if you do not want to have a separate test setup to validate your backups reporting databases more importantly require refreshes routine refreshes so you can use the backups that you create for this purpose instead of taking a special backup just for this purpose and it helps getting backups from different places so if you're uploading your backups let's say a cloud S3 you can bring the backup from there and use that why do you want to do that to make sure that your S3 backups work as restores a sample validation log just to give you an idea about how your logging should be basically it's telling me that the database was restored properly all the tests were okay and everything is alright you don't need to worry the sort of person who wants such a log file every morning in their email you can set up and alternate email alert which only emails you if something goes wrong and so that way every morning you come in you can be sure that yes the backup last night is a legit backup not so long ago I think less than a month GitLab has some issues and the intent here is not at all to criticize them the intent here is to be thankful and appreciate them for publicly putting out whatever had gone wrong during their outage and so that we all can benefit from it this is verbatim snippets from their incident response page just to see if where they've been wrong what should they have had in place to make sure these things wouldn't happen the first one is backups were taken for 24 hours not yet being able to figure out where they are stored they do not appear to be working that means they did not have point and timer stores they did not have alerts for missing backups if a backup failed they didn't know if it failed they did not have documentation since they weren't able to figure out where exactly they were stored and they did not have validation in place that shows that they weren't sure what their retention policy was and this is one thing you have to know for sure whether it's three weeks, three days three months, three years you have to be sure about what your retention policy is our backups to S3 apparently do not work either the bucket is empty if only they had some sort of monitoring and alert in place they would have known even if it was an email alert emailing every day that hey I'm not working you're bound to notice some day unless we can pull these from the past 24 hours they will be lost if they only had an RPO a recovery point objective in place they wouldn't have to worry about what they're going to lose because they would know exactly how much they can lose it is a humbling thought that yes all these things were broken with GitLab and all these things are not broken in my infrastructure maybe one or two of these is but a chain is only as strong as the weakest link if any one of these were broken there's a very good chance in the event of a disaster it would be enough to take you down why rely on luck that's it about validation next we have management in which I'm going to cover the first one is the retention period fairly straightforward although please notice what your retention period states does it state that you remove all files all backups older than 10 days or does it in fact detail that you delete all but the latest 10 backups because the two are not the same you might be taking backups several times a day in which case your backups don't go as far back in time as you think they do and then there's also the difference between off-server and off-site backups why do you need both of these why do you need to store your backups in multiple places redundancy if one place doesn't work if one place gets caught on fire the other one is still there if your off-server is basically where the rest of your infrastructure is stored just a different machine at the same location your backup site is cloud storage or anywhere else but where your infrastructure is kept so that way even if everything in your infrastructure gets destroyed you still have a place where you can get a backup from backup security you should look at the transfer security of the backups make sure most of us have password-less SSH accesses between our backup boxes and our database boxes they're not elsewhere they're not where they're not needed HTTPS for cloud upload S3 command a very popular upload utility provides that by default make sure it's turned on although that you didn't turn it off storage security basically are your backups encrypted or wherever your backups are stored is that place encrypted is that data set encrypted and who can access it to remove your backups we're going to look at something that happened a few slides from now but yes that's important CI compliance if you're storing credit card data you probably know about PCI compliance and if it is so you want to only take logical backups because in most cases you will have your private key stored on the cluster itself if you go take a physical backup it's going to take the backup of the whole cluster and have your private key you do not want that if you have a multi-tenant environment in which you have a single physical box in which you have virtual boxes for each of your clients if you're a hosting company for example you want to make sure that accesses are provided only as required that you're not violating security for example in most cases you would then you have to take a backup you would take the backup on the client on the database box and then send the backup to the backup server although if you have multiple clients on the same system you do not want the clients each of them to access your backup server because they will potentially have access to all the backups backups from other clients as well so in that case you wanted the other way around you only want the backup server to initiate your backups connect to the client take the backup and pull it back there are various kinds of alerts to set up when being sure that everything is in place the first is alert at runtime if your backup script runs from a cron job from a cron tab and say it fails you want your cron tab to email you telling what the standard error was and what happened if there is a log file email that that is immediate even as the failure happens another kind of alert and potentially more important is delayed it's secondary it basically checks your retention so after the backup and everything has happened this script will run at a later time periodically and check if all the latest backups are in place and how long those backups go if it notices that it doesn't have backups older than a day while the retention policy is three days if it notices that it hasn't had any backups in the last two days it's going to alert you this doesn't have to be too complicated it can be a few lines of bash script to do this and lastly make sure you have alerts on validation if your restores fail you want that log file to be emailed to you or page you if it fails since we mentioned S3 before I want to cover it in brief briefly it is incredibly popular for backups and for a good reason completely ignoring what happened to it two days ago it is cheap there's ample space S3 command as well as AWS Clive both allow you to transfer your file in parts backup files especially of databases tend to be very large you don't want to have to start from scratch if something fails so that's a huge bonus there are levels of security in S3 for those that are unfamiliar with S3 it has the concept of a bucket which is basically a location where all your files go to and the user that you provide access to be able to do that and to get files from it as well S3 is also very popular because it has mature tools provided for communication AWS Clive, S3 command I should mention that S3 command is a third party tool maintained by AWS itself but it's been around for a while and it is very popular and it is quite reliable and then make sure your backup is encrypted and the communication channel is encrypted just to give you an idea this is a sample bucket policy where the portion in red is the main important part basically it is telling that this user should be able to access these parts of my bucket of this bucket in this case it can access everything within the onETI testing bucket a sample user policy on the other hand which is a policy to tell what this user can access so the previous one was for a bucket and this one is for a user and this one basically states that this user can access these resources it is simpler than a bucket policy in most cases you only need one or the other but there is no harm in setting both up to avoid confusion and to avoid inconsistencies in case you have various buckets and various users having a policy for each will make things simpler for you to understand if you are a new person going in for backup management let's come on to automation why do you need to automate obviously there is a reduced chance of human error although there is an increased chance of machine error but yeah it turns out that backup isn't just about the one line command to take your backups it is as you have seen from the previous slide it is a number of things it is a lot to handle manually and make it reliable every single time especially when you think about migration or failovers where you have to move things around doing it every single time manually can take a toll on you and lastly it is more reliable it is uniform if designed properly in most of the cases all you need to automate are the scripts, the cron tabs what accesses which user has setting up passwordless SSH access can be automated easily and validation and restores as well as monitoring whatever monitoring and alerting is required for each of these the page looks complicated but the important part is the second half look at the number of files I have I have just about 5 or 6 files and they do everything I need to automate my backups in Chef Chef is a configuration management tool basically I have a backup file template to take file system backups I have one to take logical backups I have another one to upload I have a configuration file for the logical backup to tell it which databases to backup with which options and I have an S3 configuration file for S3 that's also in Chef in case I need to move things around I don't have to reset S3 reconfigure S3 every single time it is this is all I need to have to automate my backups and it's done for good another example of automation is Ansible configuration manager in this Ansible has the concept of playbooks in which you have tasks this is a very basic task the first one is just taking a PG dump off a particular database and the second task is the upload backup file task which uploads the backup file taken in the first task to S3 and that's it your backup is automated it really doesn't take much it's only a one time thing and it's done it is basically ZFS that I'm going to talk about because I love it it's got built-in compression it has protections against data corruption but the two main important things for disaster recovery is its ability to take snapshots and copy on write how can ZFS help you with disaster recovery let's look at scenario one you are using PG for a major upgrade with the heart link option or let's say even if you are using without a heart link let's say you are testing it on a replica and you have upgraded and everything goes well but then after you are upgraded you want to go back so most often way to do that is to just recreate the replica is to rebuild the replica but that takes time is there a faster way you are running postgres on ZFS there is ZFS has an option to roll back a snapshot and it is instantaneous it takes a fraction of a second to do that so the way you would go about this is right before you run the PG upgrade command you take snapshots of the PG data file system and then once you upgrade it you want to go back in time whether it's failed or it's on a test replica that you want to rebuild done with the PG upgrade all you need to do is run that last command sudo vfs rollback and the name of the snapshot and voila, it's like nothing ever happened no upgrade ever happened it's great scenario 2, you've accidentally deleted a large table in a very large database where taking full backups are infeasible, we are talking several terabytes or even petabytes is there a faster alternative to a custom restore VFS rollback will override PG data so you cannot use that because it's only one database object you don't want to roll back the whole cluster because you're going to lose data that you don't want to lose so VFS rollback because it's an in-place rollback is not usable turns out you have the copy and write option basically the command VFS clone name of the snapshot is the version where you want to copy it to this is just like VFS rollback instantaneous and it does not take up any extra space unless you write to it but in situations where you only need one table of data you don't have to write anything this works great within a few seconds you have a copy of the table depending on when you took your last snapshot and you just dump it out or store it into the current cluster and you're done so yes, if you're not running Postgres on VFS and there's an option for you to do that I sincerely urge you to do that third-party tools all of these tools are great but my favorite is OmniPitter not because it's by OmniTI but because I love how comprehensive its logging is among other things compared to other tools you need to know internal details fairly well to be able to use OmniPitter but the good thing is just by trying to use it you learn a lot you can take backups off of master as well as replica it allows for point-in-time restores it has built-in backup integrity checks built-in retention and it's ideal for infrastructures of all sizes, big or small it requires minimal setup and when installed you're good to go another very popular tool is Barman it also has all the features that I mentioned for OmniPitter before I would personally recommend Barman if you have a very large infrastructure where you have several databases running on several boxes and you want to manage all the backups and continuous restores from a central location because Barman requires its own server it's a great tool but it really shines if it's being used in a large infrastructure another thing about Barman is it's fairly simple to use you do not necessarily need to know the inner workings of point-in-time restores to do that in a way it's very beneficial because we do not expect every user who wants to take a backup to be able to do this but if you do have time on your hands it doesn't hurt learning about the internals the last tool I'm going to mention is Wall-E again just like the previous two it has everything all sorts of features one feature that Wall-E has that the other two do not is cloud integration it is integrated with AWS with Azure, Google and Swift what this means is let's say if you feel like you want continuous archival and your archive should be continuously going on to S3 is it possible for you to do that of course by having a few extra scripts you can take the walls that are created send them to S3 however with Wall-E you can do that without any extra setup so there are wrapper functions in Wall-E where you can just use the S3 option or the Google Swift option in your archive command and you can send all files that are generated directly onto the cloud so that's pretty great in-house solution if you're more of the do-it-yourself kind of a person the first one how you would go about having a logical in-house solution this is a confusing looking script but it's part of the script actually what it's doing is it's reading from a configuration file which has a list of all the databases you want to back up each having its own options excluding a schema including a table and stuff like that so this snippet is going through that configuration file each line pertains to a single database and it's using all of those options for that database and taking the backup if anything goes wrong it's letting you know and so you can place an alert if something gets spit out into the standard error alert me let me know that some backup databases backup failed physical pgbase backup is a single command you can set a wrapper around it to be able to give it more options if you are using pgbase backup for physical backups make sure to have the x option set to stream xs what that does is it makes while your base backup is getting created your walls are getting streamed as well just for it to finish also the c option is for checkpoint and setting it to fast ensures that checkpoint explicitly is run before pgbase backup starts doing its thing so pgbase backup doesn't have to wait for a checkpoint to happen it can do so itself so it just makes it faster if you do have Postgres running on VFS this is a skeleton for a script to carry out file system level backups you basically read all the command line options you put postgres in start mode start backup mode you take the snapshot and then you put it out of the backup mode and you do all your cleanup if you are uploading to s3 or any other cloud storage these are the functions in order that you can implement this is having a lock file in place so that multiple instances of the script don't run at the same time you take the backup you encrypt it you upload it where s3 upload function can have any awspy tool, s3 command tool or anything else that actually uploads it and then you move files around and you clean up moving files that again brings me to the topic of redundancy when I say move files it's basically once I take the backup remove them from local storage but I can also set it to only remove backups older than a few days from local storage so every time I take a backup I have one extra backup so I can remove the oldest one why I urge people to do this to actually have backup on your local boxes aside from sending it elsewhere is because this happens lately it was a news article on the register there was an SRE and he was very angry with his company so Sunday morning he comes into work and deletes everything he deletes all the accesses for every user, deletes the company viki pages and most importantly Thomas deleted remotely stored backups and turned off the automated backup system he made sure that he's destroying the app and that nobody can restore it the last sentence though basically created a huge mess that the company spent the whole one day sorting out it turned out that there were local copies of the deleted backups they did not even know they were keeping local copies but that's what saved them when cloud storage when everything else the SRE abused his power and deleted everything even he did not know that they had local backups lying around so yes redundancy is really really good when it comes to backups we really go about having a retention script for S3 or even generally but this is more specifically for S3 is all you need to do is two things you need to get the latest timestamp of all the files that are there in your bucket and once you have the latest timestamp of the latest file that was uploaded to S3 all you need to do is compare it with today's date, yesterday's date so that way you know if the latest backup in S3 is older than two days it's not from today it's not from yesterday something's wrong because I'm taking daily backups and uploading them every day lastly make sure your documentation is as it should be make sure you have all the details related to retention periods where your backups are going what types of backups you're taking and if they are going to separate places make sure you have all the procedures and steps required for automation as well as to initialize systems make sure you have at your analysis and estimation time everything detailed your business continuity objectives right on the top this is my RPO this is my RTO and what ways you can recover it in what's your validation process where should you go to see if validation is happening and if you want to change something make sure your documentation is up to date because it's really really important maybe not for you who's creating the documentation but the person next to you and any questions? it's a core Postgres tool it comes with Postgres to take physical backups what it does is it makes a base backup on top of which you can choose to take wall archives backups as well it is used for taking backups as well as for setting up new replicas it's basically an alternative to file system level backups but it is also a physical backup it takes backup of the whole cluster and it comes with Postgres so it's great you do not need any extra setup anything you can just if you have Postgres installed you have pgbase backup there OmniPitter yes I guess that's it thank you very much one two three oh my god is he just right after me? oh I have plenty of other talks I can give the uber talk happy to help weddings parties permisses anything that's me exactly just give me a marker and a balloon yeah because I knew Bergus was talking but I didn't think it was today so good skill okay hello everybody thanks very much for coming I will try and talk loud to overcome afternoon malaise my name is Christoph Pettis and we are going to talk about securing Postgres today this afternoon I'm the CEO of Postgres Kill Experts Inc. you don't have to pronounce that in one go just call us PGX we are a boutique Postgres consultancy based in the Bay Area my personal blog is thebill.com that's the only thing you have to write down because all the slides will be up there shortly after the talk and there's our company website hire us for something oh and that's my email address so we are going to kind of try to do the impossible here because talking about security is a good way to dive down the rattle and never return again because it's not really a single talk or a single practice because essentially everything you do on a computer has some kind of security implications and perfect security is impossible and all life is trade off followed by certain death so on that cheerful note we are going to proceed on to talk about security so you are probably already thinking so why is this crazy man yelling at me every installation makes trade off between utility security and convenience you could always write put all your data embedded in concrete and put it in an assault line in which case you are kind of maximizing security at the advantage against everything else one of the things to remember is I'm going to give you a lot of things to do here no one will ever do all of them and that's okay some things are important some things aren't but everyone has to make their own trade off between utility and security just make sure you understand when you are making that trade off what you are giving up in terms of security and what your exposure might be so you don't accidentally stumble into a hole so we are going to kind of work our way up the stack here talk a little bit about securing the host system Postgres itself particularly access to the database server the data in Postgres including encryptions and permissions and stuff like that and the application so let's talk about the host the host is kind of one of the if not the most important part people can get at the host nothing else we are going to talk about matters they can just pick up your data and walk off with it so one thing to remember is local privilege escalation will be a thing if they can get Linux shell access to the box just assume they can get at everything else always assume a local user can get root this may not be true but it very well might be because they probably can there's a lot of different ways of getting root and there's a lot of ways of accidentally allowing that everybody patched for this in Linux since virtually the day it was released like decades the important thing to do is you just minimize your attack surface attack surface is a fancy term for the number of ways people can get into your system so first always put your database server behind a firewall or inside a virtual public cloud just private cloud just do it never expose port 5432 to the public never ever ever do this do not build your application such that a client runs on the laptop and connects directly into the Postgres port just don't build your application that way and just remember if you're running on Amazon everything's the public internet that's one giant address space people familiar with this Google it up this is what happens when you put an unprotected database directly on the public internet as far as I know the company hasn't even responded to it yet so don't allow direct SSH into your database server just require a hop through a bastion host that's one more thing people have to hammer their way through in order to get to your server it's also one choke point that if you want to turn off somebody's access you only have to turn it off that one place and restrict access to the bastion host by either a virtual private network or IP don't trust bare SSH on standard port come on everybody knows about the 222 thing now we're secure we moved our SSH port by doubling the number of twos that's not how it works everyone can run Nmap and find your SSH port don't run unnecessary services on your database host don't run the application server there don't run an IRC server, a mail server or a giant mysterious VM that the previous system had been installed or a VM that's doing something on your host these are all real life examples by the way I have seen things that had the IRC put the IRC server on it because they just needed a box so run Nmap against it and see what ports it's advertising you could be surprised at the number of ports that your database server is advertising to the world and each one of those becomes an attack vector have you completely because what was last time you fully patched your IRC server use IP tables or whatever your local firewall whatever you like to run on the box just restrict access to expected servers don't rely on just pghba.com there have been bugs in Postgres that allowed data corruption or denial of service attacks against Postgres even if you couldn't successfully authenticate all you had to do was get it 543.2 so don't let host that don't need to get it 543.2 this is especially important in a cloud hosting environment because you're on the same net 10 the entire universe is on in a cloud host and just do the basic Linux stuff use specific uses in sudo never ever allow root login I hope no one does that always use a password manager always always always always have horrible garbage looking line noise passwords that you can't ever remember for critical passwords you split passwords with dual custody so generate the password split it in half and give it to two people ideally people who don't like each other very much and keep everything up to date always subscribe to pgsql announce so that you know that new versions are coming along and always apply any immediate security related updates these will always be minor versions that just require a restart you don't have to do a full major version migration so apply them no excuses and also make sure you're subscribing to security the security list for your platform so when something like dirty cow comes out you know about it right away and keep up to date with patches just this week I was asked to start doing an upgrade for a client server I logged in did a yum update just to see so where are we and 600 packages needed to be updated that is not keeping up with your updates and this was on red hat which doesn't update that all that much apply patches promptly make it someone's job part of their job is make sure all of our boxes are up to date are patched up to date and make sure they actually do it and look at them with that do you like working here look if they don't just never ever allow critical security patch to go unheeded engineers are really good at persuading themselves that a problem isn't really a problem don't short-circuit that little bit in your brain this is the most important thing this is a flaming tree just keep crashing through the window level important in a perfect world use multi-factor authentication for all your logins for VPN, for host and all that kind of stuff I just last week my phone died and the limitations of a multi-factor authentication became very clear to me but still I'm still good to say use LDAP for logins so that you can revoke all the credentials globally so that there's only one place you have to shut someone off when you fire them require password rotation if you're in a who handles any kind of credit card data as part of their application so if you're in the the four of us in the PCI world this is a requirement of PCI so you might have to do it at an absolute minimum don't reuse passwords don't have your shell login and all these things be the same password by policy by a piece of paper that says we will fire you if you do that that's reversibly effective actually Google code spaces this is a company that no longer exists because someone gained access to their AWS console and they didn't have multi-factor authentication so this is just a quick note I'm using LDAP as a generic term for centralized user control but you can also use Kerberos it's probably better than LDAP but LDAP or LDAP equivalents are much more common in the real world and LDAP is shorter to type on all these slides so I don't have to type Kerberos over and over again in your data center itself make sure the machines are properly secured a remarkable number of data centers have kind of security where if the person's out if the guard is out to lunch or is taking a bathroom break and his crew is on in it was kind of alarming the first time I did that I walked in and had the back in 5 minute sign I went over pulled open the door and walked in that was very secure and there are all the racks in front of me those locks are really not very tight so this means like real security like access control like video, like man traps like biometrics real stuff on your server room and if you're using cloud provider make sure the cloud provider is providing this to you when they provide you your cloud because how many people have actually audited this for their cloud provider good for you top marks, not many people though I didn't the first few times and this is a brand new slide based on the events of this week if you're going to use the SSL and of course you're using SSL terminate it locally terminate SSL local to the machine that will use the sensitive data do not use front end SSL termination or acceleration no matter how much the website looks appealing of the service provider because SSL is just not that computationally expensive anymore it's not 1999 it's not really expensive to do the to get enough horsepower to do it on your web server and interior networks are not that secure and this is why because this is what happens when you who's familiar with cloud lead yeah where a certain unnamed CDN sort of caching CDN proxy thing whatever they want to call themselves expose a ton of user credentials because they were terminating SSL for clients okay so let's talk about pghba.com this is the file that lists who can log into the server in order to get in so this is a very common setup you say local all all all trust basically all users can log in and they can specify any user they want do not do this there is no such thing as trust mode authentication if you see the documentation it's a hallucination it doesn't really exist forget it ever happened always require specific users even for super users don't just use the Postgres Unix or database user require specific users require cpedus not Postgres you can use LDAP is your friend here you know LDAP is nobody's friend but as much as LDAP is anybody's friend the LDAP is your friend so what about the Postgres user create a nasty password for it keep it in dual custody you may need it in an emergency but don't use it as part of routine operations don't allow non-local logins or any other super user they have to be in the box running on the Linux shell in order to get in a Postgres super user account and don't use it for routine system administration tasks yeah I know it's really convenient for backups so don't listen address set it to the specific addresses that you know are the right networks it's sometimes alarming this happens on cloudhost network connection will wake up and you have listen address equals star and suddenly it's listed on that one too it's really alarming when you go in and discover that all your others are net 10 and that one's the public internet this has happened to me so yeah don't use listen address equals star unless you're really really sure you understand your hosting environment like you built the data center out because in a cloud environment you can't always guarantee the dollar the cases are in the VPC sometimes by default adding a new one will add a public address not a private address so in a perfect world use LDAP to manage your credentials every user enroll should have its own Postgres role so every person who needs to log into the database should have their own specific role and only grant the permissions that role actually needs and if you're right now you're thinking oh my god this seems like a title system overhead you're right this may be more than you need for your particular installation but understand that this the benefits this gets you you know for example if you have a data analyst they don't need to drop tables so why do they have the permission to drop tables if you're not using LDAP Postgres passwords must be singleton the password that they actually use to connect to the Postgres server has to be unique across all time and space because Postgres source of all is MD5 which pretty much is like you know that's basically wrote 13 level encryption at this point don't use Postgres passwords anywhere else because if they acquire the passwords someplace else they can use it there or more likely if they can acquire the password out for Postgres they can use it to get into other things that you just don't want that and make them horrible and long you know one of those light noise style passwords so there's this terrible anti-pattern that a lot of systems have which is they have one user called web or app or something that can do any it has full permissions across the entire database and the web application uses it for everything don't do that this is unfortunately made worse because a lot of frameworks like Django for example kind of expect this is the way you're going to work so their migration framework uses the same uses the same user as everything else which is kind of not great because only migrations need to manipulate the schema everything else just needs to query tables and things so fight it you can work around this in Django for example so only give the app roles the minimum that they need to work so don't let them do things that they should they shouldn't need to do as part of normal operations like manipulate the schema and lock the logins for this user down to the app server APIs don't let just anyone from anywhere connect that address or using that role require SSL and CA certificates when you're connecting to the database especially in cloud environments anything besides that you're running the risk of a man in the middle attack you know and so for data security every database has sensitive information just customer order info is sensitive there's a remarkable amount you can figure out just from customer order information leaking leaking that kind of stuff onto the internet you're effectively exposing everyone's address this may not be what they want and some things are really sensitive like credit cards, health records, utility bills stuff like that utility bills are remarkably sensitive information I've had more trouble walking completely meeting all the requirements for walking down a utility bill than for any other single thing interestingly enough you have to protect this stuff against theft that's just any responsible business to collect this information needs to do its utmost to prevent it from being leaked so you say okay well got it got it Christof we're going to layer LUX on top of LVM on top of EBS and we're all set LUX for those who are familiar is a full disc encryption solution for Linux and we're talking about LVM and so the whole disc is encrypted we're in great shape no you are not in great shape full disc encryption is useless let me say that again full disc encryption is useless full disc encryption protects against exactly one attack which is the theft of the media if people are walking in and walking off with your discs you have other problems that's it this is about some vanishingly small number of the percentage of attacks you have to you really have to deal with because there's an easy rule if you log in with PSQL and get the data it's not secure because that's the way people are going to get your data not by going in and pulling the disc out of a running box doing crypto because theft of the media is a serious problem for portable devices that being said sometimes regulations or contracts require full disc encryption for example PCI requires full disc encryption HIPAA doesn't explicitly require it but it implies it I've seen contracts that say you have to do full disc encryption fine okay we'll do it if you do it make sure your key management is safe don't like put the disc unlock key et cetera sccl.com for something or fstab et cetera fstab because kind of why bother at that point so don't bake the keys into start-up scripts and things like that per column encryption is the way to go though always encrypt specific columns not entire database or disc you get better performance out of this and better security the biggest problem with this is key management is a pain key management is just a pain in life so be prepared for a certain amount of pain here this is the unfortunate fact automatic restart in a high security environment is essentially impossible because somewhere it has to get that key and every time you move that you have an automated way of getting that key you're rendering the system slightly less secure assume that a user is going to have to be involved in a system restart so that's why we all have cell phones that can take text to do full column encryption you can encrypt each column as a text or a by-day this is good for small items you know credit cards stuff like that or you can create a JSON blob encrypt that and store the whole thing as a by-day which is great for more complex things like medical records use a well-known secure algorithm AES-256 is considered the best the minimum acceptable certainly sometimes the only acceptable like for some reason PCI says you have to use this you can't use a stronger one okay whatever never roll your own crypto never ever ever remember MD5 which is like a joke and broken and horrible and all that was designed by really smart cryptographers smarter they're better at crypto than you are and it's broken like SHH-1 so use a well-known library designed by specialists don't use raw cryptography because if you do you're going to end up using ECB and that's a bad idea and if you don't understand what I just said use a well-known library designed by specialists and don't worry about it don't make keys into coders or them in repositories I forget the name of the site but there's a site that now crawls GitHub and all it does is it looks for credentials and makes them publicly available it's totally humiliating there are AWS secret keys are right there so if you need free computation resources they've got you covered indexing the problem is you have this complicated hash or encrypted thing but you have a customer service representative and they call up and say I do not recognize this charge on my credit card and they're going to say what is your credit card number they're going to type it in and hit search well you have two options you can do a full disk search and you can do a full table scan by encrypting every single row in the entire database or you can build some kind of index you often have to start a partial version or a hash of a value like you have to look up orders by credit card number there's nothing inherently wrong with this but be careful with hashes it's very easy to reverse some hashes even if you have partial data the like everyone I assume heard that there was SHA 1 I'm surprised anyone is still using SHA 1 for cryptography at this point in life but that's an example store the minimum necessary ironically PCI which is supposedly all about being so secure lets you store this first 6 and last 4 digits of a 16 digit number which is plenty to reverse a hash so only store the last 4 and use a strong hash SHA 256 something like that so who's ever seen or used PG Crypto PG Crypto PG Crypto is a contrived module that contains cryptography functions and they're fine cryptography functions there's nothing wrong with them if you like open SSL it's what it's built on and it's just sitting there why not use it to encrypt the data it's built in cryptography so what we're going to do is we're going to build this table with a credit card number and we're going to call PG this does a symmetric encryption on that number with my super secret password great I've just encrypted it and it's right in the database and then I look into the logs and there's my credit card number and there's my password yes so don't do that be careful about what you expose the text logs you know that diagnostic PG Badger run you're doing the same migration equal to zero where you pick it up every single thing the database does yeah, not so great always do the encryption in the application not in the database PG Crypto is the most interesting Postgres module I've never found to use for log everything connections is connections the ML changes if nothing else then you'll find if you're leaking secret information into the logs make sure the logs are kept secure and cannot be tampered with like use our syslog keep the stuff off the disk as fast as possible because intruders will of course immediately go and try and conceal their tracks by messing with the logs and ideally make sure the log record can be traced back to an individual person so you know who did this this is a good reason to have individual user accounts also if you have directly connecting users like you have a cpedes account that I use to connect in set log statement duration equal to zero for that account even if that user does is logged even if you're a general web user you might set log statement duration to a quarter of a second or a quarter of a second or something so your logs aren't swamped that's fine but if it's directly connecting user pick up everything but make sure you're not logging sensitive information into clear text this is another good reason to encrypt into the application not directly in the database don't give every developer production system access no matter how much they flying you know yes DevOps blah blah blah was that a question oh I don't stop for typos no fear here so identify and qualify the system administrators who need global system access don't set up a DevOps situation where every single developer has to have DevOps access to your production system in order to make the system run and if you scrub data that comes out of production for development testing you never know whose XSO will appear in the database this has happened so you don't want don't assume developers have absolute 100% best intentions especially in large organizations where you don't know everybody make sure your backup service secures your primary database because a recent backup is just as good as the primary database if it's one hour old database that'll do just fine if you're trying to steal the data if you're using a shared cluster like S3 assuming you can get into it make sure the contents are properly encrypted and private it may be worth doing your own encryption if you're feeling paranoid I always feel paranoid by own encryption I mean of course before it goes into S3 not rolling your own encryption if you're feeling really really paranoid and like using new exciting features of Postgres Postgres has row level security where you can access data by row rather than just by database object conceptually row level security is a mandatory view applied based on access controls so you can say this user can only see these columns or these rows so that's pretty cool it allows removal of sensitive columns the tendency in the table things like that application it is in fact orange book compatible that's for the reason we put it in so we could be certified and I've settled this this is not however where most breaches occur after all most breaches occur because the application leaked information you either through application breaches malware infected clients a grumpy employee walked off of the database point of sale till compromise user workstations point of sale till virus infected point of sale till is where most large credit card breaches come from target was running with malware on their till for years and didn't know so really the basic basic basic stuff always use proper parameter substitution whatever client library using use proper parameter substitution because if you don't look at your web server log sometime you'll see more SQL in there than you will in the database people try to do SQL injection attacks never build SQL by text substitution unless it's absolutely necessary like you're doing something to involve variable table names and I hope your API does not involve the user some extra user passing in a table name if you are please stop and rethink your solutions in life because all user input is hostile it wants to kill you all the time the stuff that's coming in for the web are knives and guns and bombs and you have to treat them that way they are not friendly nice things they don't like you for APIs since everybody builds APIs now always require TLS 1.2 for all remote APIs there's really no reason to use any older version of TLS if you have a client compliance 1.2 is required if you have dedicated clients like your mobile app that talks to your server always use proper certificate management because otherwise somebody you will be in hacker news in a week for somebody doing a man in the middle attack on your app and make API keys long, unique and random like there was one public app that was put out there where the API key consisted of a four digit hexadecimal number and log everything everything that comes in on an API make sure you're logging it somewhere so you can see when people are trying to abuse your API and harden it against that always detect the usual access patterns if one IP address starts querying with a ton of different API keys, check for that if one user has a lot of trouble remembering their password check for that do blocking and rate limitation admin alerts that kind of thing users will generally share passwords across systems humans if they're using public facing APIs will use trash passwords everywhere so use captures and things like that to avoid automated risk it's always annoying when something some service that doesn't seem all that important throws a capture ad but it is a good idea because users will in general find out their entire online life this is probably the most important thing of all make security testing part of testing and this is a cultural thing not really a technical thing but in so much as culture always write tests that deliberately try to get around access controls it really it's amazing how many integration tests and system tests against APIs I see they never try to log in with a bad password it's like come on do you know that you can't log in with a bad API key so get new engineers to try to hack your system this is because new engineers every engineer knows how something works and tends to kind of avoid the things they don't feel so great about and are a little worried aren't going to work just right new engineers tend not to have this they want to break things let them break things and if they do engineer X who has been with us for only 3 weeks found this big security bug what a great person this person is makes this part of the culture that finding these security things is a good thing not a bad thing and just do basic sex stuff email services use all the OS vendors antivirus tools generally third party antivirus tools now are becoming more bugs and features I have to say but make sure the antivirus the OS but OS level antivirus tools for desktop OS have gotten very good it's also swift on security especially if you're a windows admin you can learn an amazing amount from that if you are running a big service especially one that handles sensitive data hire an external penetration testing firm and encourage developers to poke at security qualify these based on ones that actually understand security and aren't just going to run a bunch of pentest scripts for example this actually happened to me I get a call I'm doing a PCI compliance run and the scanning firm calls me up and says we need you to disable your firewall why the penetration script is failing because this firewall won't let it through and sure enough I look at the log and it says there's like five requests and the firewall goes yeah right it shuts off that IP which is kind of to me what it sounds like a firewall should be doing I mean it's sort of like saying well we're not going to ensure your house because we walked up and tried to get through the gate and we couldn't so you might have left your door unlocked so by now you're listening to all this and you're going oh my god we're doomed it's awful security is a lot of work there's a lot of different ways the problem is you have to be vigilant all the time the hacker only has to be lucky once and you just have to realize that you're never going to be perfectly secure but even the most secure companies get intrusions and if you're you should be so lucky it's to be the target of a state level hacker and life is full of pain and despair but have hope if you do as much set in and forget it security as possible you can build in a lot of security in these systems just don't do the forget it part just do regular audits and instruction tests again this is a great thing for new engineers to do because it's very valuable they will be approaching it with fresh eyes just like a hacker will and it's a great way for them to learn the code and it inevitably wants every presentation this has I have better UIs on your clickers that's the other thing and also be sure the company from the top take security seriously I would say 90% of serious API level hacks happen because the company did not take this seriously and that trickled that was the root cause that ended up in something was exposed and just remember you will always trade off some security for convenience I don't do on every system everything I say on this list not going to tell you which ones I don't though just don't get complacent and have convenience become the most important thing which unfortunately I see a lot in clients just make security one of the things your corporation is proud of because this more than anything else if the corporation from the top the organization of any kind says the great thing about our stuff is we present an iron wall against hackers that will matter and any questions yes sir the better more interesting and more public your side is the more people are going to be pounding on it you can set as an attribute on the user I mean now of course if there are super users they can take it out but that line will be logged and then you can have a little conversation with them about that it's time for a what on what could you send me which one I've totally forgotten by now another question it's like the vast space is open before me of all the possibilities well unless you are prepared to build out your own data the reason is not to use AWS you have this box sitting there and you aren't using it for anything else this old Dell so you install previous on it and you boot it up and you play with it so that kind of trivial thing generally these days it's hard to argue against the cloud hosting environment because the advantages are pretty manifold it you there's no capitalization the box fails with somebody else's problem all this stuff so unless you are prepared to build out your own data center and actually do that it's hard to argue against the cloud environment because there's a lot of positives you know I used to be very anti-AWS running Postgres now I kind of like at that point at this point it's you know I might as well acknowledge the reality of this kind of thing you know now my hostility has moved from AWS to RDS is Grant in the audience? no good just joking so any company is going to use some kind of cloud services because why would you not go from super micro? who wants to do that? pull out their credit card the downside is you are in somebody else's data center and that same net 10 or net 172 address is everybody else's address as well and so you do have to realize that everyone's going to be pounding on potentially people are going to be pounding on your servers you can sniff other people's traffic you know so you just have to be prepared for that so it does increase your you are exposed to the world and you just have to do that but at the point that you're going to build your you're not that AWS or any other kind of cloud environments I mean there are plenty of cloud environments that are AWS believe it or not at the point that you're going to move away from cloud environment you're in a different world kind of thing than most smaller companies the other reason is it may be AWS or cloud hosting things may just be overkill for you you know if I was running a dentist's office I don't know would I host my database remotely and be dependent on the internet connection that's a decision you just have to make well if you're DOD you're running a GovCloud first of all and you have extra security options there specifically you don't have to be Gov to run in GovCloud although you can't just walk in and select GovCloud from a drop down but you in terms of security the the big upside is you do have a lot if you need when you want to deploy firewalls and you know front end set up a VPC it's a point and collect type operation and that's really nice because getting all that right if it's your own hardware you have to put it in the neck you have to order it you have to set it up configuring it can be kind of annoying but setting up a VPC with a front end firewall that kind of thing is pretty straightforward on Amazon and that's really really nice the downside you have to remember is you're in a hostile you're in a target rich hostile environment and you just have to be prepared to really lock stuff down that's why I say like use SSL everywhere even if you know even inside your VPC you know one more level of production that answer your question yeah yes sir no I love less encrypt it breaks the back of the of the certificate cartel yes fight the power there's nothing you know the the who issues the certificate is entirely matter browser acceptance it basically doesn't it doesn't matter that much in terms of security unless of course the CAS compromise like Komodo was and you start getting a bunch of bad you know somebody can walk up and issue a certificate for your domain name list yeah you could you could use your own CA and you know a CA is just a private private certificate that signs things you know so that's totally I love less encrypt anything else great thank you very much you find your you find your graphics where you can yeah well you know testing hello okay so thank you for joining me it looks like we're standing remotely come on in it's okay or don't this talk is PostgreSQL snap packages why we're gonna get into what snap packages are why PostgreSQL is a good fit for them how they may be a fit outside of PostgreSQL that is really loud they actually specifically asked me to wear it too I am Joshua Drake commonly referred to as J.D. in polite society and in polite society I won't say that because we're being recorded I am the lead consultant and founder of command prompt Inc. we're the oldest Postgres company in North America that I know of anyway I'm a contributor PostgreSQL.org I actually hold a position called the SPI SPI liaison for lack of a better description I am the treasurer I am also a chairperson of PG Conf US I hope you join us it's at the end of the month it's in Jersey New Jersey but we did it right we're right on the right in West New Jersey where it's only one stop from the main island of New York so it's not like you're going to be in some weird area of Jersey where people talk funny and bury you in a forest that's the effort just a couple things as you can see the picture in the middle that is New York from Brooklyn that was actually taken at last years the 2016 PG Conf US conference the picture on the left is myself in Vienna at the gardens of the oldest zoo in the world that I was in Vienna to teach or speak no just teach teach at PG Conf EU and then never thought I was a small dog man until two years ago I love my small dog I don't care if you give me shade for it I love her her name is Keely she's a French stint she's half Boston terrier half French bulldog and that's it for intro why is my thing about oh it needs to be turned on here's a disclaimer I usually put this disclaimer on for more filled talks and usually I go through a whole spiel about why it's up here the longest short is I tend to I tend to improv a lot and I sometimes say things that are off the cuff that certain people have a hard time with so this talk is for adults if you're not an adult okay but this talk is for adults and you might hear something that might offend your ears don't let them melt it's just not that big of a deal we're just y'all here to have fun hi Stella I'm pointing you out because we are being recorded so now like all over the inner tubes people are going to know who you are especially since you bailed on PG Conf US we did partially sponsor this talk succeeds based on your participation a lot of talks I do normally honestly you're filled a little bit more but I like to have participation with the audience don't hesitate to ask a question I'm not going to bite your head off I'm fairly social I can handle it and you're a dick we need to go back to this slide just for a minute I'm offended that you're offended seriously participate it's how we have fun it's how we learn I don't know everything let's figure it out together and see what we come up with so what are snap packages anybody here not know what snap packages are snap packages are basically a container for binary packages they run as a loopback file system on Linux it is a canonical open source project a lot of people have jumped in on it because it is very useful we're going to talk about where it is who it's available for now why we're not using flap pack flap pack is the quote unquote what everybody else is using but there's a reason not to use it why not the universal installers well I'm talking about the universal installers I'm talking about the postgres universal installers specifically building them I'm not going to go real in depth but I do show one of the reasons we chose to support them and whether or not you should use them some pros and cons where is it that's where you find it snapcraft.io it's available for most Linux distributions Ubuntu is the most obvious but it also runs on Debian Fedora which means it will run on CentOS it will run that openSUSE I actually know a guy pretty well who works for openSUSE and they disagree but it does actually run on it open embedded and this is actually one of the I'll bring this up more later but one of the interesting things about snap packages is open embedded and openwrt which is running embedded on like wireless routers things like that does anybody know what Yachto is what is it and then gen2 does anybody in here run gen2? okay is it the same version you installed 10 years ago and then you just never touched it again because that's what happened with me that's why I didn't update it I just left it alone okay why not flatpack it is a great open source project we were actually going to support flatpack and snap I'm talking about command prompt but it's specifically designed for desktop applications it's designed to isolate something like Thunderbird it's not designed for Postgres and choice is always good a lot of people are like why can't we just have one standard thing well because then people would get bored there would be no innovation and Microsoft would still rule why not the universal installers now if there were more community people in here they would get this joke big SQL and EDB already have cornered the page view market where does that come from if you go to Postgres and you click download there's a page it talks about all the different distributions we support and there's the quote-unquote one click installers well EDB has one and big SQL have one they do the exact same thing and you can if you install them you download a single image and you hit just like a Windows MSI kind of thing you click on it and you select what component you want and all that jazz but largely outside of helping the community the reason these two packages how are very popular is that it drives page views to big SQL and EDB because Postgres doesn't post them they're hosted on the remote servers of the companies and I didn't want a part of that we've got Postgres Russia or Postgres Consulting Russia having eight different companies up there just didn't seem like a good idea Universal installers have too much bloat because they're designed really for the new person they're designed to say hey someone wants to get in and just run Postgres and click their buttons and be off with it well that means it has everything under the sun installed with it it also doesn't follow native OS packaging or anything like that so it kind of becomes this step child over and off that you forget is there until you run into it because you install something from YAM or from AppGit or if you're running Mac OS 10 and people go why I don't know where it went and it's a real problem because they're managed outside the OS you can't just say AppGit remove should you use them talking about SNAP they have transactional upgrades that's huge who here has ever done AppGit Update AppGit upgrade and then it breaks and then you have to run DPKG-S or something like that and resets the package you don't have that problem with this or you're running I'll actually have another one you have simplicity and deployment I apologize this is my first time doing this specific talk I'm a little rough on the slides and then multi-art capability through automated builds these things are of interest to you you're going to want to look at SNAP the pros of SNAP are there's a huge vibrant community hands down it's got a mailing list which anybody can have but it's relatively active there's IRC, they're on I believe it's Slack or no, Rocket Chat more builds available all the time they actually have a portal where you can go in and see what SNAPs are available and it is backed by the largest Linux distribution in the world Red Hat definitely has cornered the enterprise but if you're doing cloud or you're doing small business people, that's what they deploy they're deploying a boot to every person I meet I never hear anybody except where I live which is because there's an office pursuits in my town except for where I live I don't hear anybody installing anything but a boot to and there's always that really odd person in the back running deviant but it's always a boot to SNAP cons, it is still new it's a moving target it's changing a lot they do have a pretty good development and revision policy so it's not like 1.0 and 1.01 is going to break but 1.0 and 2 might so you want to pay attention to that it's less than stellar and when I say that I'm being polite because Ubu Khan is here it is the build documentation is essentially this here's your API docs if you want to figure out how it works go look at one of a thousand GitHub SNAP projects that are trying to build too so you have to go in and look at their snapcraft.yml and say oh so that's how you do it that's undocumented that was fascinating that's unfortunate they'll get better they are still trying to get their feet I mean you're here because most of you hadn't heard of them you're just wondering what they were but they are very useful and it is moving quickly in fact some of the problems that we ran into with building the original Postgres packages for SNAP have been fixed in the new version of SNAP because of all of our feedback and other people's feedback deploy something like Postgres using these packages so why Postgres SNAP packages I don't know if you can see that but it really is that simple sudo snap install Postgres 96 Postgres 96 from CMD is installed for the record the CMD packages are all open source they're on GitHub you can grab them we're not keeping them proprietary in any way you say Postgres 96.initdb now there's a reason for this that that initializes basically a wrapper around your normal Postgres commands that you would see there's also a wrapper for PGCTL so that they don't conflict with the native OS packages and then just start it up PGCTL boom starting we're done it's running now this isn't all that much different than using app yet or young that's now it's a little more subtle as to why this is useful one you can build your own snap package that conforms to your specific requirements maybe you don't want integer date times do not turn off integer date times ever don't ever do that in fact did we for 10 did we deprecate it for 10 finally okay so just as kind of a side note can anyone here that's not a Postgres person tell me why float based date times would be bad unless it's AWS and then it sinks on these coasts go ahead well it has to do with nulls you don't know because of the precision of float two timestamps that look the same may not actually equal each other so you have to have integers fixed precision so you don't have that problem and we fixed that all the way back in I think it was 8.3 but then we kept it for compatibility purposes anyway so continuing so why the snap package simplicity, development throwaway, isolation and control first let's start with simplicity so to snap find Postgres QL all we want to do is what's available for Postgres and right now if you do that it will show a list of 93296 it'll show PG Bouncer and a couple of versions of PG pool 2 there are others coming sudo snap install Postgres 96 now what this has done is install Postgres 96 into an isolated loop back or loop file system it's a read-only file system and gets mounted it's actually isolated from the operating system itself although controlled by the standard operating system utilities being snapped I started Postgres it's starting, I enter Postgres I'm done now unlike the PGDG packages you actually can just enter Postgres here normally Postgres QL will block you unless you're the Postgres user by default through the pghba.com we don't do that because this is all about keeping it simple and frankly if you're installing it you want to be able to get into it I hope that you're smart enough to realize that you shouldn't be MongoDB and leave all your passwords open on the internet where people can get into it well I wasn't MongoDB let's fix that the people that were running MongoDB and boom there it is you're up and running development so I already have Postgres now a lot of people when they do development you've got a VM I've got a VM we've got a shared get repo but that's not always the case especially for smaller shops but in order to properly test you can't really run via two databases you need two full instances and you can do that with the app or the YUM packages but it's a little more complicated here all we have to do is say JD already has PostgreSQL Max needs PostgreSQL so we go in as Max initialize the DB, edit the port and start it that's it and you're in now in a situation where you're doing long term stuff like Max is going to be around for a long time he's doing his stuff, you're doing your stuff I would recommend a VM or a container or something like that but for quick throwaway type stuff for efficiency just to fire up it's not really good but Max said he would rather use MongoDB so he's fired and the only thing you have to do is stop it and arm it and it's gone it's like it never existed again the packages themselves are kept isolated from the OS entirely so you don't have to worry about accidentally deleting something as long as your paying attention to what you're doing that is going to affect the OS proper isolation here's an example it's installed, dev loop 5 it's mounted this is where it's mounted so you can actually go in it gives you a revision of each one to refresh this all you have to do is say snap, refresh PostgreSQL 9.6 and if you're on 9.6.2 and it's 9.6.3 it will automatically refresh 9.6.3 on top of it and if there's any failure it will roll it back and you don't get any package damage it's in state transaction updates, simple testable states so why binary image have you ever done this? has anybody ever had this problem? yeah you're running Wally or whatever something that depends on some specific Perl or Python package or Clib and you do a standard upgrade and then all of a sudden it doesn't work anymore you don't have that problem with this because the images are tested externally you know exactly what is in the image if you build it yourself you know exactly what's going to do happen during the refresh and you never break the OS there's also no complicated networking to do or anything like that it uses all the native OS infrastructure it's not a container like Docker or LXC where you've got to set up a bridge interface and you know those types of things you can even run snap packages in a VM control, it's simple to update like I said snap refresh Postgres 2.0.9.6 the ecosystem yes well you'd still have to use PG upgrade but see what would happen is if you installed Postgres 10 it's going to install it on its own snap it's not going to replace yeah so you can even have 93, 94, 95, 96 and 10 all running on the system and they're all mounted on their own little file system completely separate talked about this already snap refresh so if you're going from a dot release it'll just refresh the current you would still have to restart Postgres there's no way we don't have a whether Linux has been new live patching or whatever it is that'd be kind of awesome build system supports multiple architectures the fanatical community and new markets this is something that I really enjoyed when we built the Postgres packages to start with we just started with i386 essentially and 64-bit x86 and then we found out that all they had to do is upload them to Landscape yeah Landscape and they build it for ARM ARM64 we can select PowerPC if we want all of a sudden they're just available no work on our end except if the build fails in which case we might have to change the column for a little bit so the build succeeds now I mean my I'm running a Pixel XL I got 4G memory Postgres runs just fine in 4G memory in fact my company's database server doesn't even have that much memory it doesn't need it new markets IOT we had a guy post on the list just last week about how he's now using Postgres in an embedded environment had I think it was 2G of RAM his embedded environment he replaced S2Lite with it because S2Lite which is a fantastic database has a pretty significant limitation in that it can only write one at a time so if you have multi-user you get a lot of blocking you don't get that with Postgres so now he switched to Postgres and he's got an embedded device running Postgres now obviously if you're trying to run a better device to say 32 mega RAM that's not going to happen but what do you think 256 512 probably oh it'll boot but I mean between shared memory and everything else what like reduce the wall size or something yeah the page size I mean yeah and of course make it completely incompatible with everything else but 64 should just start right up yeah okay so on the I've got my slides make sure I apologize so the ecosystem fanatical community this is what I was talking about these are all snap packages and this isn't all of them this is just a screenshot that I just rectangular select on of all the snaps that are coming out and now they have LibreOffice you guys have ever heard of I think it's NextCloud it's either NextCloud or own cloud one of them NextCloud they have a a new basically snap distribution you just install it and you get their entire cloud suite including the web version or web available version of LibreOffice so you don't necessarily need like Google Docs or whatever Microsoft calls there I don't know what it's called are you from Ubuntu or okay so yeah and it's growing quickly because of that and so think about a scenario where you're a company building a product maybe it's a let's say a bookkeeping product like QuickBooks and you want Postgres to back to the product but you don't want to have someone go and say run you know install.sh which does an app get of a thousand different packages you're hoping they're running the right version and then they're going to contact you and say well I tried this on trustee and then you have to say well we told you it only runs on Zennial well I don't want to upgrade it to Zennial all that type of stuff you can actually build the whole package the whole distribution the whole application distribution into one snap you just install a snap and they're up and running that's in something that supports proper packaging that doesn't actually exist in Linux because majority of Linux communities vary into individual components individual packages individual dependencies that type of thing well I mean that's what a lot of the community is doing now with Docker they do it all the time now in fact it's annoying as hell someone like me who actually knows what he's doing I'm like I want instructions on how to deploy a server with this and they're like don't just double the Docker image right agreed agreed but you can do that with snap and set all your own dependencies and everything and what's even better about that is that if they upgrade their machine if they do an app get dist upgrade the only thing that's dependent is snap you don't have to worry about libc getting upgraded or some sys admin deciding he wants gcc5 versus 4.4 and libc gets trashed or whatever you always know exactly you can check some of the image and say you're not running a standard snap we don't have to support you what happened here that kind of thing the auto build ecosystem this is what I was talking about if you search you can do a snap bind it's hard to read but it runs on i386, amv64, armf and arm64 I can tell you for a fact that the only hardware we own is amv64 Canonical took care of all this for us automatically so now anybody can just use it for whatever they're doing Arduino or build board thing like that so why not snap packages they are new different but not that different and they are largely driven by one community but most of open source stuff now depending on what you're doing is driven by one community so it's new people either love or hate new software it's important to remember that the Linux you're running now it doesn't really do anything it didn't do in 1999 I can fire up Red Hat 6.2 right now with KDE 0.99 and outside of rendering issues with Conqueror my email still works my star office still works I can write papers I can the whole bit now it's new and it's fancy but really and we had openvz back then now we have Docker those types of things people don't like change system D right that's the argument why would I do this well it's different it's not really different you know binary images aren't new one of the funny things about this is when we were researching this package format I had already built the exact same thing for a client without all the management without like the snap find and snap install and snap refresh what had happened was is that their CentOS had been upgraded to 6 and 7 but they had very specific software requirements and libraries from CentOS 5 so I took a fresh CentOS 5 install and basically built all of their software into an image that we mounted on CentOS 7 to allow it to run and it was a loop image it was basically the same thing with all the management stuff they are a great way to control the distribution of software we've already talked about that especially like I just said especially useful if you need a legacy software to run on your platforms you have an old library something you don't want to conflict you want to be able to upgrade your OS without affecting that one guy in the corner that makes a whole lot of money for the company you don't know what he does but he's running some piece of software that you can't even stand to look at alright let's build this alright, I'm not going to run a live demo because my laptop is currently in all kinds of different states but this is actually a build that I did on my workstation at home it's literally a copy and paste with the exception of you see these dots here because I wanted to sit on the screen I didn't include all the software that gets installed but I said snap crowd what I did here is I downloaded the git repository for the 9.6.2 snap crowd and in there, in that directory there's a couple other directories specifically there's a snap crowd at YML and that's what snap crowd looks like and you say snap crowd in that directory it will automatically look at all the dependencies that you need, install them whatever is not already installed as you can see I wasn't logged in as root so I sudoed it updated, grabbed everything and suggested, recommended installed, tell me how much was going to be installed, staged the wrapper staged postgresql primed everything up, built it up snapped a package and gave me this there's my snap package it was literally that simple and the next screen I think it is it wasn't even that hard to build and anyone who builds packages will recognize the format a little bit yellow there we go this is an example snapcraft.yml it's not complete obviously but you name the package you give it a version sorry I'm not going there yes it just might summary plus just to let you know this is what it is and then the description now what will happen here is that when you upload to the snap repository and you do a snap find you're going to get this metadata but here's the deal we're being strict about our build it is considered a stable build here are the apps what you do is you initialize the various different wrappers and commands that you're going to that are going to be installed into the snap keep going here are the parts of the snap we've got PostgresQL to build PostgresQL we need this plugin plugin auto tools anybody here ever use configure or auto comf that's what we're talking about here and this is just a light wrapper around that to make it very simple we don't even have to have Postgres install it will grab it right for us right off the inner tubes we build this is the packages we need this when you saw this software being installed this is what it was saying we need these packages in order to build this Bison, Flex, ReadLine so on and so forth that's really hard to reach for the people in the back I'm sure but we keep going we stage these packages we need the libc bin we get to the build we're telling it we're going to go multi arch this is what's available if 64 do this and then it runs a configure just like normal and here's our install key we make install world that gives us contrast if any of you have ever messed with contrast that gives us some of the contrast packages next the next plugin is make which makes everything there we go and that's it that's all there is to it we can absolutely do that in fact feel free to grab ours right off of github and just add it into your SNAP you won't find examples with the postgres packages but like if you grab the next cloud stuff or if you grab say the libera office stuff you're going to see for example there's one for lamp and you know you're going to get mysql or actually the next cloud or own cloud that's the one own cloud you get patchy you get mysql things like that and it's going to show you how to break down the sapcraft yml so that you can get one yeah it's different parts and plugins and it actually basically just built a virtual file system right into that image with all the binaries and libraries that you need to launch it now here's there is keep in mind that all you're getting is the binaries and libraries so you still have to in it db right well you can create scripts to manage it but you can't install the database itself to the snap because snaps are read only so anything you're writing to is going to have to be on a different file system but it does use all the same OS infrastructure so you don't have to worry about bridging your network or worry about weird shared memory users what was that well know that you have to run it in a db to get the data directory is what I'm saying yeah absolutely well right because you're going to be running in a db from the snap you just have to make sure that you point where you're going to put the data directory to a read write file system and obviously if you're building like you said a medical software thing you just you'd have a wizard that did the whole thing no one's going to see that anybody else yes you would yes you would have to rebuild the snap in order to that is well right well and every snap is going to have their own version of open SSL in it yes that that's no different correct and the dead packages or anything else we stopped it yeah no that yeah no that's a good point that's a dependent that is would be a drawback is that you would have to every single snap would actually have to be rebuilt that's correct that's a good question I don't know I would think that the automated we would have to trigger it but we probably wouldn't have to do anything with it is what because what it'll be is that all this stuff relies on like a moon to core which is another snap and so if that gets refreshed then the build we might we would probably have to go in and trigger the build but we don't actually have to do anything beyond that and then people would just refresh we offer support good proactive response that is absolutely true and yeah as long as snap is running on the Linux it should just work and snaps itself is not all that complicated using loop file systems with a mount yeah that's true and luckily with a snap package even though you should update it no matter what if you install the snap package you did it for a specific purpose right and so you might have a business case for why you're running an outdated open SSL I can't think of one but you might anything else alright cool thanks for listening