 Well, hello, and welcome everybody to another OpenShift Commons briefing. I'm really pleased today to have with us Jeff McCormick and Paul Lawrence from Crunchy Data. And we've got an interesting talk, it's one of our favorite databases and to work with. And now that it's running up and wonderfully with OpenShift 3 and in containers and Kubernetes, we've got Crunchy Data folks to come and talk about how they made it work so wonderfully and to do some background on how to use Postgres with OpenShift as well. So with that, I'm going to let Jeff introduce himself and his team. If you have questions during the presentation, ask them in the chat. And after the presentation and the demo is done, we'll open it up for Q&A. And there's a couple of other technical folks from Crunchy online, so they may get answered in the chat, but we'll try and reiterate them for the recording as well. So without further ado, Jeff, why don't you take it away? Yeah, well, thank you Diane. We're happy to present this material. Paul Lawrence is going to kick it off and give you a background on kind of Crunchy and what we do. And then I will pick it up with a demo and talk about some of the technology bits here. Paul, take it away. Yeah, thanks, Jeff. This is Paul Lawrence with Crunchy Data. And so Crunchy Data is a provider of trusted open source Postgres and Postgres support and technology. And we've been working with the Red Hat OpenShift team for about two years now, initially in version two and then when the migration went to version three and all good things with Docker and Kubernetes, we kind of jumped on board and have been working with them since specifically to bring enterprise Postgres capability to the OpenShift platform. And they've been doing a lot of interesting stuff there that we're excited to show the comments group today. On the, at its core, Crunchy Data has a distribution of Postgres called Crunchy Certified Postgres. It's 100% open source, Unmide-applied Postgres 9.5. Included in our distribution is PostGIS, that's for geospatial data types, as well as enhanced audit logging capability through an open source project called PG Audit. And then we also have an advanced SE Linux integration as an extension to Postgres that enables multi-level security. And this is, again, all 100% open source and as noted in the bottom in evaluation for common criteria certification. What we've been doing with OpenShift is bringing not just Postgres, but extending Postgres with an integration into OpenShift, as well as providing some tools and utilities that really provide kind of a Postgres as a service experience on the OpenShift platform. And as Jeff will talk about here in a second, we've done that through a series of additional containers that we've developed. So not only do you have a Postgres database, but you have the kinds of enterprise capabilities that you'd expect like high availability, backup or store, failover, monitoring and some other DBA utilities. And so, Jeff, with that, do you want to kind of take folks through the various pieces to what we've done here? Sure. So what I'll do is take you through the containers, give you a description of those. And then at the end of this, I'll show you some of the demo material and some of the screens that are part of that. So, but first of all, just a background. The number one container we started with was just taking Postgres and putting it inside a container and using that as kind of the foundation. So what we have is a container, base container called Crunchy Postgres. It includes Postgres 9.5. We also have a 9.3 version and can spin up other versions depending on what a customer needs. But that's the main container that runs our Postgres. And when we do a restore job, we're basically spinning up that database container. So a lot of the other containers I'll talk about are all interacting or dealing with management or monitoring of these Postgres containers. And whenever we create a Postgres cluster or master slave, it's all basically just this crunchy Postgres container. So that's a good place to start. One of the first things I noticed was I wanted to kind of have an ecosystem of containers around Postgres after kind of figuring out OpenShift and Kubernetes and how to make this work. So one of the very first things, I wanted to be able to create a full backup of one of these database containers and created a container called Crunchy Backup that literally just runs a PG based backup, which is a full database backup against a running database container. And the diagram at the bottom kind of shows you some of the major pieces of that. And you execute a backup container. It literally runs as a Kubernetes job, which is kind of a one time only execution of a container. And it performs this backup against a running database Postgres container. And it takes the backup archive files and stores them off on an NFS volume so that you always can go back and have a persisted copy of your backup. So when you look at the examples we do or ship with these containers, you'll see a whole bunch of persistent volumes and persistent volume claims that get built and used whenever you do these backups. The examples that I ship, I'll use NFS, but you could certainly translate that into SEF or Gluster or some other storage capability. And the CUBE OpenShift guys did, I think, a good job of abstracting that choice away. So it's pretty easy and straightforward to switch from NFS to Gluster with not a lot of changes. Now, right after I did backup, obviously I wanted to figure out a way to do restore from one of those backups. And that's really performed by that same crunchy Postgres container. And just by passing in a backup path into that container, the Postgres container, and passing some other flags, environment variables, it's enough to tell it to, hey, I want to do a restore. And I'm going to pull from this particular backup path, which is a kind of a timestamp directory structure. It'll read the archive files basically off of NFS, pull those into the running Postgres container. And it uses R-sync for that. So it's basically doing an R-sync from that NFS path back into the container. So that's one way to do a backup and restore that I have today. Some other containers. One is called Crunchy PG Badger. And I was asked by a DBA if I could run PG Badger against, which is kind of an analytical Postgres log analysis tool, pretty good one that a lot of people use in the Postgres community. So yeah, what PG Badger does is it has to read the log files from Postgres. So I created a container that if you drop it inside a pod with a Postgres container, it basically lets PG Badger read those log files through a shared volume mount because it's inside a pod. You're able to do that sort of thing. And I wrapped PG Badger with an HTTP wrapper essentially around it so that you can do web calls basically to PG Badger. You can execute it and it'll spit back a HTML report. And I'll show you an example of that in a minute. But that's a case where I'm leveraging the kind of unique nature of what a pod lets you do by combining a bunch of containers together. I'll give you an example of that here in a second. But that's a Postgres utility that's been out there a long time. It's written in Perl. I think it's mostly a command line tool. But by containerizing it and wrapping it with a little bit of a web container, I think it adds some value. Crunchy PG Pool is another case where I've taken a pretty widely known Postgres clustering utility and wrapped it inside a container. And what PG Pool basically does is lets you proxy Postgres connections from, like let's say you have a master and a slave connection. PG Pool is set in front of those running as a standalone container. And if your application connects to that PG Pool container, it looks like you have just one single connection to your application. But PG Pool is actually managing two separate connections internally. And it's sending SQL inserts to the master and it's sending selects to the slaves. So it knows kind of where to direct certain types of SQL statements. And it's kind of a nice utility for apps that really just want to manage a single entry point into a cluster, as opposed to managing separate connections to a cluster. So in Postgres, a master is read write and slaves are read only. So that's the distinguishing characteristic there. And that's really the value I'm getting on a PG Pool today is that unique feature. PG Pool lets you do a lot of other things and I may expose those features in that container over the coming months. Crunchy Watch is a container that really right now is the starting point for performing automated failover. So what Crunchy Watch does is looks at a master when it's running. And that's just a Postgres node. And it basically just pings it uses a Postgres command called PG is ready to just basically look at the master and do a health check on it every configurable. I think the default is like every 20 seconds. So let's set there and ping it. The master goes down. What Crunchy Watch does is it attempts to perform a failover on one of the slaves and it will perform the slave or it'll trigger the failover on a slave. And it changes actually the Kubernetes labels on that slave to become a master. And at that point when that slave becomes a master, Kubernetes will spin up another slave to keep the number of replicas in sync with the definition that you've given. And it's kind of a complicated subject. And I could talk probably just about an hour on why this is just the starting point of an automated failover strategy. But what I'll suffice to say it's a simple way to at least keep your cluster up and running in the event that you lose a master. Another container is called Crunchy Collect. And this gets into the idea of wanting to collect Postgres metrics from all of your database containers. Metrics that are specific to Postgres in particular are the ones that I've targeted initially. So there's lots of different Postgres monitoring tools out there and I just basically pick some basic one metrics that people are familiar with. And the purpose of Crunchy Collect is to collect those metrics from a running Postgres container and then pushes them off to a Prometheus time series data store for storage and retrieval. And that occurs on a frequency as well that's configurable. So if you have a database that you want to collect metrics from, you basically would just take this Crunchy Collect container, add it into the pod that your database is running, and it will automatically begin collection of those metrics and pushing them out basically. Again, it's another example where I'm leveraging that capability of a pod to have multiple containers in it. Crunchy Grafonym is part of this metrics collection in that it gives you a graphical dashboard capability to graph those collected metrics and push them out until web app and I'll give you a demonstration of that here in a minute. Crunchy Prometheus is the data store that I chose for storing these metrics. There's other data stores out there but Prometheus, I've had good experience with it so far and it really works I think well for this kind of problem to solve. It's nothing but this Crunchy Prometheus is nothing but a wrapper around the Prometheus database. My base images run typically on rail seven base images so this is Prometheus running on a rail seven base image and it has a volume mount so that you can obviously mount the data out to some NFS or whatever persistent storage you want to use. Crunchy Gateway is literally just a piece of Prometheus that they have which is a push gateway so it enables Crunchy Collect to actually push metrics to this gateway and then Prometheus scrapes or pulls those metrics from this gateway so it's kind of a bridge in between collect containers and the Prometheus back in. This is a diagram that shows all of those components and kind of how they're mapped out and what's going on. To the left you see the pod that contains both a Postgres container and the collection container, collection containers pushing to the gateway Prometheus containers out there scraping that gateway and pulling metrics storing them in the its data store and then Grafana simply just a client to Prometheus pulling those metrics. Moving on there's another task other than just backups that a DBA would do a DBA might want to do perform Postgres vacuums on ether databases the entire databases are tables in specific tables and so what I did is I wrote a container that's just nothing but a Postgres vacuum and whenever you run that container as a job it's a one-off execution of that job and that you can configure that job through environment variables to do various types of vacuum analyze on either a full database or on a specific table as well so a very small microservice type of DBA related task that is an example again of something that routinely people would want to do against a production level database. So I started with backup job and vacuum job was the second to that and then I was asked by another client well obviously we would like to be able to schedule these things and we don't really want to get into cron schedulers on the Linux host themselves we want to have a containerized way to execute and schedule these backup and vacuum jobs so I created a another container called crunchy DBA which has a one-to-one relationship with some database container and that DBA container is a cron scheduler and it just sets out there and says what frequency do I want to do these various DBA related tasks now the ones it understands today that you can schedule are backup jobs and vacuum jobs but I wanted to point out that you if let's say you have 10 Postgres containers you would in this case attach you know 10 DBAs essentially to it so it's designed right now where there is a one-to-one relationship between these two so it's kind of like a dedicated DBA being attached to your database and its job is just to kind of do these routine tasks so that's the structure which I've set up here and I would envision customers using this to write their own DBA jobs and just using backup and vacuum as kind of like examples that they could write you know their own custom backup jobs and then there's just a very little bit of integration they have to do in the DBA container to be able to schedule those things out so when I talk about a Postgres kind of container ecosystem this is really a good example of that where we are trying to containerize all the things that we think would be useful to containerize around that Postgres container so that you can do you know management help and kind of the management of your Postgres environment so in order to do all this what kind of OpenShift concepts did I run into and really need to do this so I've talked about the Kubernetes job that's a relatively new thing where it's a one time batch execution almost like a batch job that you're executing and so I create these jobs on the dynamically within that crunchy DBA container to go off and create and fire up these backup and vacuum tasks well that's a part of that Kubernetes I'm using Kubernetes jobs essentially to do that and then in order to be able to dynamically create things from Kubernetes objects from within a container that's where I'm using service accounts so if you look at an OpenShift and Kubernetes how service counts work I'm setting up a service account so that I can execute OC commands from within that container and I'm using that in two situations one is crunchy DBA to spin up and create new jobs and then I'm also using it in the automated failover crunchy watch container to go out and touch files in the slaves within that slave container and also to change the label on a slave pod from a slave to a master so that's where service accounts are vital in able to be in order to be able to do that kind of internal API type or NFS volumes are used for backups and restores again I could switch that out with Cephar Gluster but the key there is I need persistent storage for holding backups and it's vital to have that because you just wouldn't make sense to to have any other you know a non-persistent backup type mechanism now I can use NFS volumes or choose not to use an FS volumes to run my Postgres on and that's a key point but it it's up to you if you want to run Postgres not using persistent volumes to manage you know doing backups and being able to withstand losing your your database but if you do use like empty-door volumes you do get a nice performance increase of using local IO as opposed to network so you as the deployer of this technology you know you want to really think about your use case and whether you what types of volumes you want to use and we could do an entire talk just on that subject and we've learned I've I've learned a lot about I guess the hard way of how to deal with volume management but I give you the option in a lot of the different examples of running these containers of using either NFS volumes or empty-door volumes and if you were to look at the examples that I ship with it you'll see where I make make reference to that now where are we going with this there's a whole lot of containers that have kind of exploded here but there's probably five or six more on our roadmap to do in the next coming months and they're all Postgres related microservices that either help or augment kind of the experience of managing groups of Postgres containers and clusters so there's a whole bunch of different types of tasks that that I we envision adding to help a DBA manage this and monitor and then there's several more Postgres utilities that we think would be useful to wrap as containers and add in here so that again you have lots more the goal here is to bring in a lot of more valuable Postgres utilities into this container infrastructure and like I say there's already five or six that we kind of have queued up to do now if you have questions about any of this you know our email addresses are posted there at the bottom and also our website and then the github now github's marked private now but we're about to release this so at some point that's going to go public but that's the where everything is at in the github so let me switch over I'll show you a few screens for demos and I think we're on target time-wise yes indeed excellent so I talked about Grafana this is Grafana it's a pretty nice web application that lets you graph and create dashboards of different kinds of collected metrics and I am no Grafana expert but this is at least give you an example of what you can do in this particular dashboard graph that I've got here is showing you know one metric on one database and it's essentially the number of connections over time and I've been doing a few tests this morning and running different connections on this and it just a one really simple way to to be able to graph and I could add more metrics over this if I wanted to just by selecting you know another adding another query in here these crunchy underscore metrics are all of the collected metrics that we pull and I think there's like 30 or 32 of them depending on how you've exercised your database some of these have values some of them you really need to run some load against in order to get something to show up like that's a good example of a metric that I'm just not exercising but it is also an example where you can combine metrics on a single graph and Grafana lets you build a number of different dashboards with multiple graphs on it and again I could go off the deep end I guess and talk for hours on Grafana but that's another obviously another session behind that is Prometheus Prometheus is really gives you a very simplistic graphing and query console I go ahead and expose that because it's a nice utility just for people to do but this is a very similar graph of that same connection metric but it's through the Prometheus dashboard but that's a good way to test your metrics that you're collecting from a base raw level to say well is it even stored in my in my Prometheus database and yes it is that's a this tab is that gateway and that just shows you the collected metrics and you can drill down into those metric values here as well and it'll show you metadata probably you would never use this other than just if you're debugging your setup but it is out there and you can kind of poke down into individual metrics as they're being queued up and collected. PG Badger this is the output of the PG Badger container and you know this is a very interesting utility and I can see why DBAs like it because it packs a tremendous amount of log analysis into one single web page and you'll see up here in the URL that's the HTTP wrapper that I've placed around it this utility so when I execute this URL it basically comes back with this but this is really pretty nice for you know a free open source utility it gives you a tremendous amount of of information in a small area and with that I will show you a brief one second you can see my screen just this is going to be hard to read probably and it certainly command line stuff doesn't demo particularly well but I will show you a few things of interest from the command line so I mentioned a vacuum job I wanted to show you basically if you say OC get job a lot of folks aren't familiar with jobs and they've never run OC get job you know they're not building jobs but it's pretty nice and with a job there's an associated pod so if you do if you look here this is the associated pod with this job okay so if you look at the output of that vacuum job you will see that it's really nothing but the output of a normal postgres vacuum sequel statement but that gives you an example a little bit of how jobs look and work and it's it's really you know kind of a microservice very narrowly focused it does one thing and that's it you know that's kind of the the goal of these DBA tasks the other thing I wanted to show you is just some of you have not seen how to scale up things let me show you how that works so I've got a master slave out there running and basically if I issue this OC scale RC command like this with replicas equals two it's basically going to tell Kubernetes in open shift hey I want two slaves instead of just one and that's a very simple way to manually scale up a cluster and you'll see here these three lines right here that I've got one master and then I've got two slaves and that master is replicating to those two slaves and you can just continually scale up these kind of postgres configurations like this using that OC scale command something I don't have to demo today but I will in the future is is really where based on CPU load Kubernetes can actually perform that scale up for you and that's something I'm working on but not quite ready to show that yet and some people may want to be a little bit more have their DevOps in control that scale up and scale down as well but that's really as simple as you can also do that scale up command through the web console as well there's like an up arrow and you just click on that and it executes that OC scale command but that's really I think what I have to show today given the time that we have so I guess at this point I would open it up for Q&A if there's if there's questions yeah thank you very much that was actually quite amazing to see all of those different pieces and parts containerized and available there is one question Diego Castro from Get Up Cloud is asking his first state there is no scheduler jobs and open shifts how do you manage crunchy dba running as crone and he would just like you to dive a little deeper in how that yeah I will mute him so you can ask questions follow up sure sure I can explain that so crunchy dba executes a goaling version of cron inside it okay so it's not hooked into any external scheduler either on the Linux host or within OpenShift at all it's actually taking a open source version of of cron again written in goaling just because I code in goaling and it's actually executing that okay so when you run crunchy dba it actually starts up a cron scheduler inside the container and it's using that scheduler inside the container so it's all self-contained it has no hooks into anything outside to fire off this and it's and it's doing really just two things today it's looking for the scheduled vacuum jobs and it's also looking for scheduled backup jobs and you interact with crunchy dba through environment variables there's an environment variable let me see if I can show you an example real quick of that sorry yeah you can still see my screen but there's an environment variable you'll see call backup schedule and the value you pass into it is literally a a cron you know expression and it's it supports all the valid cron expressions interestingly as well it's pretty full cron implementation so hopefully that answers your question very good but Diego you you had another one regarding the backups oh yeah yeah how do we manage the backups are they done for binary backups yeah great question today the backup that I have implemented executes pg-based backup so it's actually just executing a pg-based backup against your database and it's doing a full just a full database backup so yeah I don't use pg-dump currently that's a good question though because what I'd like to do is be able to support uh some kind of incremental backup and that's uh definitely on the roadmap I'm looking to be able to execute incremental backup jobs against you know very large databases where pg-dump or pg-based backup wouldn't wouldn't be what you wouldn't need so that's in the works and it's literally uh high on my priority is to implement some form of an incremental backup looking for the right solution there it's probably getting maybe uh pg-backrest or something similar to that looks very promising so great question though okay thank you so I know you're using nfs um and Diego I'm pretty sure you guys are using stuff so it sounded like you could use you could switch off pretty easily um or there would be some work but it's not impossible to do that won't be interesting to see this done again on sale yeah other other questions out there right now um has one so our I'm gonna ask when you put a link up earlier to your github repo that's private um the containers are the containers there or the templates there um and where are you hosting the images themselves are they yeah they're gonna be available yeah that's that's a good question our work and one reason why we're still kind of working on this is is exactly how to release it so you know one thing we're considering doing is putting these on a private for for like enterprise customers that want you know paid support what we'll probably end up doing is putting them on a private docker repo that we host and you know enterprise customers would have you know access to that repo and they'd be able to pull down pre-built images now in the github um you would have access to all the docker files and you'd be able to build all of the images yourself directly on your infrastructure if you did not want to pull down pre-built images the pre-built images we will probably build will be rel 7 base images for example with uh the crunchy version or crunchy built postgres uh 9.5 in it now in the github you will see there's other options uh of what base images and what versions of postgres that you want to build into your containers so i've tried to abstract those kinds of things out into just build um environment kind of configuration options but the public versions of these will probably of these containers um would like perhaps they'll be based on senos as the base image those would probably be hosted just out on docker hub so when you uh when we make an official release of this you'll probably see these two separate places but paul you may want to jump in and clarify that there we go muted bubble so yeah just so you know i think that was the one that popped open there but anyway um stay tuned we're going to make some uh releases of this and announcements and there's some things we're working on with making a lot of this stuff available out there and like the open ship dedicated is something that we're seriously looking at so we're going to make a lot of this technology available just pure open source free for everybody and then there's going to be some ways in which you can get this uh from like a paid subscription model or as or something like open ship dedicated is something we've actively been working with red hat on so um you know we're anticipating here pretty shortly like i say q2 of this year to have this ready for people to pull down and consume and there's five or six more containers i'm trying to put as much in here as i can before we release it but what i also want to do is make sure that it's uh documented to you know to a standard that everybody's going to expect and you know full suite of tasks and hooked into you know the ci builds system that you know that that we have as well so there's still a few more things that i'm tweaking and tuning before we we put it out there for everybody but um i also wanted to put this out here now and get a little early feedback to help us kind of tune and tailor our roadmap going forward as well but i see this growing again as a as an ecosystem of of containers and microservices that for people wanting to run postgres you know open shift postgres on it or postgres in an open shift you know container environment we're trying to give them really a lot of tools at their you know at their fingertips as opposed to just giving them a container and saying well good luck you know so we're going to try to automate a lot of tasks and we're going to try to do it in a real open shift container way and that's kind of our our philosophy with us so um any other questions i don't see any right now but i actually think that this is this has really been very good and very insightful i think the way that you wrap things up in the tooling into containers is really helpful for people to see i mean there's a lot of other folks out there who are trying to do similar things with their databases and their service offerings and you know usually we do see someone just create a container with just the database in it and not really um anticipating the need for all the tooling and this is very well thought out so much appreciated can um i know this was there were a lot of probably interesting war wounds and things like that learning in the learning curve to kubernetes what was biggest surprise or the biggest learning that you had to do around um a kubernetes based containerized process like this if someone was yeah good questions uh for sure i started on this probably um really early i mean probably like almost two years ago right as right as the docker uh technology was kind of chosen for the next generation of open shifts so i started out you know pre like really early with kubernetes so uh i was watching and testing the growth of kubernetes and an open shift to to see you know how could i how could i implement some of these things i wanted to do and you know a year and a half ago two years ago it wasn't possible there was not things that are like service accounts that enabled me to do a lot of of this really low level integration and dynamics stuff you know it was all very manual year and a half ago so and then i kind of went through uh needing volume management and saw that kind of evolve as well so volume management right now is is really um working good enough and well enough for us to do some of this database related stuff we think where people could deploy it you know really solidly as well so service accounts the development of that the the maturing of things like the job concept where you have a one-time thing you want to execute that's been really helpful um and then the service accounts jobs volume management all those things have really made it possible to go ahead and add a bunch of these features in there now somebody asked a question here it says could other database systems kind of do some of these these same patterns and yeah i would think so you know to me it's just logical that if you have a database in a in a particular deployment model like this of containers that you would want to try to get those same advantages of running a database in a container from some of the you know all of the task around managing and and keeping a database healthy so to me there's some of these patterns and some of the diagrams i put in here specifically are very you're going to see those same patterns and other and other databases and i was asked the same question from another for another company that was doing something similar and yeah i would see databases in open shift working a lot like this where not only do you get a base container but you get a lot of stuff around it that helps you kind of treat it as a database as a service and so things like building a web user interface that helps you manage all of these database containers i mean that's something definitely that we're thinking about doing because if i deploy and spin up say 50 databases and have them all out there now i'm going to be looking for tools that manage you know that entire you know collection of database containers in some smart way and i think that all the tools that are now there in open shift uh in kubernetes that allow you to to do pretty much anything that you can think of you know i think you know long term there's i'm looking for more advances in the volume management side that might make life a little easier as well but right now i do think you could you could deploy it and be successful with it the way it works so a whole lot of work has gone into this and it's really much appreciated Jeff and Paul thank you very much for this effort and i'm looking forward to seeing a number of other database systems come into this containerized pattern not in the not too distant future too so i think this is really the crux of why open shift commons is such a nice peer-to-peer network because we're able to share these lessons learned and inform each other and this has really been a very good example of that so thank you very much for joining us today thank you diane i don't see any other questions um i will share this recording out um through the mailing list and on the website shortly and um we'll be up next week with some a talk by google on kubernetes the future and the present and the upcoming roadmap for that so that's be a good one to look forward to next week well thanks again everybody and we'll talk to you all soon