 Okay, yeah. Hello everybody. I'm Michel Bank. I work at creative which is one of the sponsors and We're doing a lot of open-source stuff since 1999 Including 24-7 support break fix support supporting open-source projects Like a vendor if there would be a winner We're based in Mönchengladbach and we're always looking for good people. So Come talk to us We have about seven people in a database team, which I'm one of them And database team means postgres for us. We are not super happy if clients come with my SQL problems But we will try to help them If possible So then Debbie and there is the package postgresql team We're packaging I'm also part of the team we're packaging all the postgres core server, but also all the associated projects and that's a Statistics you probably can't see a lot here, but Uploaders per year and the point is that it got started by Martin Pitt who also started with the infrastructure for postgres and He was the blue line So he was doing a lot here, but then in 2011 2012 the orange line Which is my colleague Chris of Burke took over and he did most of the uploads in the last couple of years and Also the yellow orange line or light orange line. That's Arjan von Andries who's a devian maintainer He's also working a creative. So We're doing a lot of work in the postgres Debbie and and Ubuntu basically teams is my point. I'm the Brown one here somewhere that I did a couple of uploads not not so many though So just to give an overview about postgres, I guess most people know about it It's an extensible object relational database system created at Berkeley in the 80s But it's been open sourced in the 90s and there was some unfortunate name changes from Postgres to postgres 95 to postgresql when they added SQL it didn't have SQL in the beginning And it's a BSD licensed project. So there's a lot of Also proprietary forks. There's actually also a couple of unforks these days. So The extension system is so good that some Wenders were able to unfork their their fork and it's now just in a regular extension So you install vanilla postgres and you install the extension and you have to product Know the open core node you're licensing Compared to and there's also no copyright science. So it's very very open very free not not very copy lefty, I have to say but on the other hand all these companies who were doing a lot of proprietary stuff with postgres are now Kind of regretting that they were forking 10 years ago and postgres has evolved and they are trying to get back And it's it's a lot of work for them so they figured it out and they're trying to do the work upstream I guess most of the time and It's also probably was the first open source transaction safe database in the mid 90s. So my SQL was very good at web Interface a web browser web server kind of stuff Quick queries, but if you were transaction based Postgres was probably the one to choose and also Debian was actually choosing it for quite a few of their projects internally So the just as a quick overview the postgres community It's called the postgres global development group, but it's not really a legal entity in a sense of SPI or something It's more like Debian But they this the development group and there is a core team of five members right now. I believe and Important part is that they have a release team now They didn't have it a couple of years ago and they were slipping and their releases But now it's there and they're keeping track of it. There's around 20 committers One of our one of our president is one of the committers Michael Mascis He's he's actually committing quite some stuff, but not a lot and It's it's when a neutral Basically, yeah, no company owns it. No company is dominating it There's a few companies doing quite a bit of development on it But it's not owned by any particular company and you can get support from basically Different sources if you want, you're not Tied to one company they have now again a yearly release cycle as I said I think the 95 release three years ago was slipping to the next year But they used to release an autumn and now they're back on track and it's a bit like Debian So they have a time-based freeze feature freeze, which just happened for the next release It happened in the end of March or beginning of April And now they're in the better or that it's going to be a beta release in Next week or in two weeks and then they will consolidate their debt fix all the bugs and it's the next major release is supposed to come out in August or September somewhere third quarter and Also, it was somewhat remarkable They have five years of maintenance per release. So they're also always really basically supporting five different major releases and They also moved to a quarterly patch release cycle. So you can go on the Postgres website and there's a column that says the next patch release will be out Blah blah blah, I think it was actually last week that the last one was out so the next one will be out in three months and They are very conservative. They're only fixing bugs or Security issues and so they're probably basically whitelist, but it had been stable release managers They could just upload it as is you don't need to back patch The patch release. They're just like they're just uploading the patch releases as a new upper new upstream patch release basically I Talked about it that there is quite a few of proper edge of Fox. That's That's how it goes with the BSD license so main features Quick overview. It's packaged in Debian. Yay. It's rock solid and I think they are a bit proud that they were first Going for the rock solid part and then for the speed part So there's there's some issues, of course. There has been some bugs over the years They add new features they have bugs but overall you can be pretty certain that your data is not being eaten all the time by the database server and As a SQL server it has a pretty good and all-in consistent and easy to figure out Use of the SQL standard So there's no real surprises And it has a couple of useful modern extensions You said Jason support pretty early on and that kind of stuff even had key value stores much early on It has a cost-based query planner that means that it tries to figure out. Okay, how do I run this query? What's the best way to run it? What's the least costful way and it's just trying to run that that plan and Pretty nice for administrators. It has transactional Changes to the database structure so you can start a transaction you can drop a table and then you can roll back and the table is still there And not every database product has that And then the extensibility is pretty good. There is drivers for many Programming languages for many procedural languages so you can run server procedures in are or in python or in pearl or in shell if you must and And In the last couple of years the foreign data wrapper thing has also been extensively used so you can Have federated access to other data sources or other postgres servers That's that's really something that that's being exploited more and more also for bigger Features like trying to implement sharding via Federated access over remote data sources and there's a large number of extensions and associated product. So this is just the kind of the Slight overview about what what's kind of in core and then external extensions you can have there's really quite a few we have Lots of source packages in the package postgres QL team But these are a couple of them which have been uploaded or new since stretch so Just as a quick overview. I don't want to get into too much detail here and If you're interested in the new feature so stretch came out with the 9.6 And then they actually changed the major version scheme to 10 So there is only one number now and the next one will be 11 and 12 so every year there will be an increment of one and Since then so 10 and 11 gave us Oops, sorry logical replication So we had physical replication before but now there's logical replication and we have native declarative partitioning There was not there before you had to come up with fancy schemes And we'll have just in time compilation of expressions. So this is an 11 This will be out by the time Buster will probably freeze if we have we hope that this will be an 11 11 will be in Buster And a couple of other things. I'm not going to into very much detail But there's new features coming every year basically So postgres and Debian, how is it working? So Debian packages only one postgres release per version and The problem then is obvious that we have several Debian releases and we have several postgres releases and if you have some project which relies on a particular postgres version or You want to upgrade your Debian one, but you want to keep the postgres one that there is a problem there So what people came up with in the package postgresql team They were trying to make or that they made all the versions co-installable so you can install the nine six and ten postgres servers On the same machine. They're debut their different source packages and they live in different sub directories And there's a postgresql come infrastructure which handles takes care of figuring out where the different Database instances should go And they're all listening on a on a different port so you can address them from the outside differently But then still you have only stretch in nine six so in theory you could install both But how do you get the other one and that's where the postgres upstream team came in and it's basically my colleague Chris of Burke who is also affiliated with the upstream team He came up with app postgresql.org So that's a project by the postgres global development group, but it's basically the same source packages as Debian uses But they're recompiling or rebuilding All the postgres versions for all the Debian and Ubuntu LTS versions So if you need nine four for bionic, you can get it there. You just have to put the right sources list and there's like Obviously a pretty large number of sources list 144 oops So we have three architectures that we're supporting a power PC 64 is pretty recent, but we had AMD 64 and I 386 before and Currently it's a Debian and you would release so bionic should be there now and the 11 releases is testing Basically, but the other ones are there there. Those are the ones who just still are the supportive upstream 93 will be Supported in September when when 11 comes out so there so if somebody uploads a new Version of a new source package from any of the package postgresql team There's a Jenkins build server run by DJI.net actually so people are also sponsoring this conference and This will rebuild all the binary packages for all the distributions and all the well If needed postgres version some are version independent, but some actually need a proper postgres version And it's also running the auto package test, so we're trying to run the auto package test If possible to see that it's actually working and install versions So how is postgresql common working? It's it allows multiple versions to be consul already said that Every instance identified by a version and a cluster name So the default name is main and the default version would be 96 for example, and every new instance get a new port So if you get a second Cluster from the 96 version it will be running on 5 4 3 3 and you need to have another name and there's wrappers where you can Say, okay, this is the binary the client and you tell it cluster 9 4 main So that's a debium Edition how to address that particular cluster if you don't want to know the the port number for example And then there's a pgconf tool where you can read and edit parameter files easily and there's a couple of other things For example, you can create We have this create cluster configuration file Which you can get a couple of options here and also you can get an additional conflict parameters So they will be covered into the postgresql.conf main configuration file So great. So this is how You can run several postgresql versions, but if you're actually running postgres in production You just you don't just run the postgres server. You know you have You have to Well, they have to operating system and all that kind of stuff that's clear But you also have backups you need restore you need retention time You need monitoring usually if you're running in production performance alerting Also reporting and maybe you also need high variability so how does that work we we had creative came up with a way of gluing together several open source projects based on postgresql common basically and some system de-glue and We're trying to make postgres as easy as possible in using By having a it's kind of like an appliance so you can download it and you can run it And it has a complete environment where all these things are just mentioned already set up for you And it's modular and extensible if you want It's just devian packages and we have a couple of clients who are interested only in the monitoring stuff so they can just take that and Use it for their already Maybe they're running it on redhead or something so they can just take that part of their interest and It's over 100% open source. It's on github We're trying to get all the packages into Debian. We're not there yet But at least the glue code is is open source and we're committed to having it long-term maintained and commercial support So those are the things that we're doing right now Getting into more detail right now. So there's a It's web-based. I didn't mention that maybe until now. So you have a web-based interface um This is the dashboard where you can get to all the the other parts of the thing and you get a Overview over all the clusters running on that local machine So I have to say that it's mostly useful if you have a local machine where you were able to run a web server and expose that stuff To yourself and for easy maintenance Yeah, postgres The main interesting part I would say is one of the Things that we actually put a lot of effort into is the monitoring which is based on Prometheus and Grafana So it's using a SQL exporter thing to Prometheus, which is an upstream project we forked it for some performance stuff, but it's not written by us And it there is a note exporter and SQL exporter and those metrics are then exposed to Grafana For dashboards that we also set up so you can easily monitor your postgres instance And it looks like that There's some more pictures or maybe I'll have time for a live demo in a minute But basically we set up all the dashboards and we also put in all the the metrics that we SQL metrics that we wanted Out of the SQL exporter because that SQL exporter is actually a database agnostic thing It doesn't have any metrics itself. So we came up with a metrics The database administration is done by pg-admin 4 which is now a web-based Dba interface This one was the biggest problem because it's flask-based python. There's a lots of dependencies and After infonetry has been trying to get it into debian fully. I'm not sure the current status, but it's mostly there It's certainly needed because the the old pg-admin 3 interface which was written in C or C plus plus is it's deprecated. So we need this now And you can use it for either ad hoc SQL queries or just to get an overview over all your tables For schema management and user management and dump restore stuff we're doing backups using pg-backrest which is an external project it's Can do it does physical backups so you can It archives your transaction logs and and you can go back in time You can can restore it's actually right now. I have to say we're doing the backups for you with retention times But restore has to be done manually for now. This is only to do this Yeah, we have a preferred postgres go com this we're taking advantage of the postgres go com framework here Where you can drop in the right configuration parameters for pg-backrest in the template from postgres go com And it so it can work. There's a prepared system D timer. So every time you actually create a new cluster There's there will be a system D Service or timer prepared for backups being done So you don't have to set it up yourself Or you can do it ad hoc if you want there's a button on the way on the interface We're doing some log file analysts not going to go into detail here a lot because it's It's pg-badger. It's pretty standard log file analysis Also having a HTML HTML report of it And we using cockpit for system management basically mostly so you can see the log files or you can start and stop the instances But you can also use it for all the other stuff. So cockpit is a system written by red hat But Martin Pitt now I think is working on it also You can use it for for the system-wide administration and in theory also to actually update your your system We're supporting right now Debian stable mostly, but since the next release We're also supporting bionic for the server For the appliance I would say and the server can be It's more not not a big problem. You need to have the Prometheus exporters That's basically it. We have packages for both of those xx AMD 64 and power Right, so further development There were one two zero minorities just happened two days ago and this one we managed to update to Grafana 5 and Prometheus 2 So that so now is a good time to try it because before you all the all the Prometheus metrics would have to be thrown away because I'm not sure it's it's upgrade Or at least we I'm not a Prometheus expert But as far as I understand you couldn't use the the old data with the new Prometheus You cannot right so now's a good time to try it because we just made the move to Prometheus 2 and now hopefully this will be More stable going forward and we have a overall monitoring dashboard and as I said there's bionic support What we want to do in the future is get red head and central support. So some customers are asking for that there's quite a few and And also I'm Adrienne Fondentrich has been working on a PostgreSQL common REST API So it would be much easier. So this is a good top repo So you can do configuration changes or instant management via the REST API So right now actually creating an instance means you have to create the instance on a command line It will be there Automatically on the web front end, but you cannot create an instance But then you will be able to actually create or destroy instance as we have a web interface And we're also looking at a multi-host administration if possible So we can use you can combine the view of several servers But this is a slightly bigger project which we're not having a date in a real Timeline red. So if you want to try it out you can do it. There's a vacant box It takes a bit of this space, but okay, and you can just run record in a record up And then you have all these things and basically the the web GUI is here and you you're basically good to go and The website is elephant shed.io if you want to check it out And I have to say that most of the work has been done by Kristoff and Alexander and Atrium so shout out to them and For the information elephant shed.io. There's the github. It's on github. We don't have an internal repository anymore. It's all on github and Some information if you're looking If you're interested in then working for us or interested in then as a client or in anyway, you can You can go there and my content address is there. So I think I'm mostly out of time anyway Anyway, I'm done and if there's any questions Hey, I was curious if You have packaged all this stuff because I would be interested Especially for Grafana and the changes done to the permitted sport and all that So we have a we have a local Debian repository, which I Didn't put the link on I'm I can give it to you I mean, it's it's it's public and it should be actually on the elephant shed. I'll wow website. I'm pretty sure And I don't think we have lots of fork like we're using the Grafana packages. This might be slightly adopted, but It might be a pre-release or something. So we we are obviously not using stretch in that sense, but you can look at there and then If there's any questions you can that we can also discuss later I cannot say it right now because I didn't upload the for me first packages to that repo But I was mostly interested in Packages are there and we're trying to move them into Debian If that's package post-crest scale stuff, we do it directly because we have the upload stuff If not, we're trying to talk to the people or not Thanks is when talking about Updating versions you said that for updating it is retaining the data you will need two versions of post-crest scale Why is that and how does it how the process looks looks like? Well, that's a good question. It takes So they're on this format general between major versions is not comfortable You can use an in on there's in place upgrades using it's called PG upgrade But you need two data directories for that You don't necessarily need this you need to copy all the data, but at least you need two places And then there needs to be some transformation done So it's not a you cannot just so for for minor patch releases You just put in the Debian packages and restart the server. That's not a problem But for major updates the system catalogs change and then other things change So either you need to dump all the data to a backup and restore it or you need to Do this in-place upgrade thing and then you need both versions installed obviously Because you need both servers running at the same time one in two different data directories Is that basically answering a question? But it's I mean upgrades is a huge huge Topic for the post-crest so I could go on for half an hour This seems like a very turnkey solution. You just put it on and it works, but who who we look the To who are you selling this or who who installs this because I I mean it's a bunch of things It's really great and all works together But if I wanted to put that in my system it wouldn't work because I already have monitoring only have backups It's right if you already have all of that then maybe you don't need it Some people as I said I interested just in the Rafa dashboards and Prometheus metrics if you already have that they might be interested in that and It's I think it's an interesting Way to come up if you have a new if say you're you're a database Guy and you want to check out post-crest But you don't want to come up with a huge project Then you can install it locally at first and then see okay everything's working and then maybe also deploy it somewhere else Certainly, I wouldn't recommend it for multi terabyte databases stuff like that, but I'm just to try it out It's it's very easy man. It's just a waking box and You can take parts of it if you're any way running on Debbie and you can just take parts of it And you get to go Thank you