 Well hello everybody and welcome to another OpenShift Commons Briefings. We're really pleased that you've joined us today and I want to welcome the folks from Crunchy Data today. They're going to regale us with some wonderful cloud native GIS applications using Crunchy Data on OpenShift and we have with us today Steve Pusty, Paul Ramsey and Adam Tim. Steve is one of my favorite people. Long time Red Hatter and fellow evangelists back in the day so we're really thrilled to have him back here and we've always very much loved our wonderful partners Crunchy Data. So I think this is going to be a lot of fun, some really great demos are in the offing. So Steve why don't you introduce your team, take it away and we'll have live Q&A at the end. So thanks again for joining us everyone. Thanks Diane, it's really great to be able to hang out with you again too, I've missed it. So I'm Steve Pusty, I'm the lead developer relations at Crunchy. Paul Ramsey is the, I think it's executive engineer, senior engineer, something really high up in the, and for the purposes of this talk the important part about Paul is he is one of the founders of post GIS, he's the lead committer and he and Martin Davis are the two that built the Crunchy Data's Crunchy Spatial platform which we'll be demoing today. Adam Tim, he is a wonder guy who goes between sales and consulting and grant writing and PMing and doing all sorts of stuff. He's a former NGA, I don't know if he was an analyst or not, but he was at NGA so he cares very deeply about spatial stuff but also being at NGA he also was on the beginnings of cloud native stuff that the government agency was doing. So today we're going to talk to you about some cool stuff that we've done from actually from soup to nuts with Crunchy Data, Postgres, some cloud spatial stuff that Paul will introduce in a little bit and we'll be running it all on OpenShift. So let's go ahead and get started. I got to click on the actual window. All right so there's us, oh sorry Paul's the one in the middle, Adam's the one who looks like he might be an NGA agent anyway on the far right and that's me on the far left. Actually it's no hair framing the hair guy is what we're actually aiming for with that effect. What we're going to do, we're going to talk about some cool stuff with Kube, PostgresQL and Spatial Micro Services and of course since we're on the commons one our version of Kube is going to be OpenShift and then we're going to use these pieces to build some cool stuff in real time. So we're going to spin up all well part well you'll see but it's almost exactly real time loading data is slow so we're not going to show you loading data other than that everything's going to be real time. Who's Crunchy Data? Just for a quick overview the leading provider of trusted open source PostgresQL technology support and training. Basically if you want to think of who we are we're the red hat of PostgresQL right that's our business model that's what we aim to be we have we have some of the leading contributors to PostgresQL code like Paul but also Stephen Frost, Tom Lane, Joe Conway approximately one-third of the contributors to Postgres right so we can both influence we can do stuff in the code but we can also you know we're deeply connected to the community where everything we do is 100% open source and runs on Postgres right so no open core everything we do is fuel open source. Our claim just like red hats is engineering quality support right so that's one of our big ones we have you get calls with skilled PostgresQL engineers 24, 7, 365 usually 20 minutes or less and so one of the examples would be like if you had something going on with Postgres and you were one of our customers you probably wouldn't get Paul on the immediate call but you get Paul probably on the second call after they figured out what the problem was and then one of the other big things for us is commitment to security conscious enterprises you know we have a lot of security clearance and we built we worked on the STIG for Postgres so we've done a lot with security and we're cloud and platform agnostic and we've been working with containers since day one with OpenShift actually with OpenShift we were building containers back when OpenShift was not running on cube but we quickly made the the conversion to cube and OpenShift and then we have been doing an operator for quite some time and you'll see it in action today so here's just some stuff about how we've worked with Red Hat before Ansible Tower now uses Postgres behind it and satellite also uses Postgres behind it and we are one of the preferred partners if you want HA Postgres behind satellite or Ansible Tower right and we anywhere else you want to do if you're running like Jira or if you're running what was the Jayfrogs I don't remember Jayfrogs but behind Jayfrogs we can run behind Jayfrogs as well for HA so here's what Ishesh the GM Cloud BU I think he's a senior vice president now for Red Hat and Cloud in general and here's what I'll let you read it I'm not going to read it out loud well actually I should in case there's someone that's hearing that's vision impaired we are very excited to see the great results of the work that Crunchy Data has put into containerizing and operationalizing Postgres in the OpenShift environment having effectively built a database as a service infrastructure Crunchy Data has made it easier for application teams to use the power of Postgres QL in their modern architectures that's from Ishesh and then Chris Morgan the lead tech marketer and one of my good friends on Red Hat Cloud this is about our operator certification and this is just to attest how long we've been doing the operator on-cube with Red Hat so as they and we're actually in the catalog as well as they have for several years Crunchy Data has been a first mover in their collaboration with Red Hat being one of the first partners to embrace the operator frameworks demonstrates this and we're excited for Red Hat OpenShift customers to be able to take advantage of this technology to help address their Postgres QL needs in a scalable and repeatable fashion and quickly if you know about operators which you should since you're on the comments call we are actually at phase five so we're on the autopilot mode for our operator and you'll see a demo of our operator Adam's going to demo that in a little bit after Paul does his thing so I tried to go through that relatively quickly because I know most people don't want a lot of marketing so if you have questions you can put it in the chat or reach out to we have a slide at the end of how you can reach out to us I am now going to turn it over to Paul. Great and I'm going to talk a little bit about the motivation behind behind Crunchy Spatial so it's not uncommon in our kind of world for you to want to put a map on a computer but if you're coming to the problem for the first time it's all too common to be sucked into the tar pit of being told you need a great deal of different new extra complex software to do it you need a geographic information system my friend you need a GIS but actually if you're building an app probably you're building an app using a database underneath it you've got Postgres in your app which means you can easily have PostGIS in your app which means you already have the GIS effectively PostGIS gives you all the functionality of a GIS without having to bring the GIS in your infrastructure you already have the functionality in your database in your spatial database Postgres or PostGIS that means you can answer GIS questions things like what parcels within a kilometer of the file that's a GIS question it's a question you can visualize in a mappy sense what things are within a thousand meters of the fire and you can express that we answer that question in SQL if you have a spatial database and not very much SQL either this is one SQL statement it's five lines it's a few enough words I could count them on two hands how far did the bus travel last week that's also a GIS question you can think about it in a map you can express it in spatial SQL find me the nearest truck to the transformer that's actually a somewhat more complex question because I got two things going on there I got transformers and I got trucks can I find the closest thing using just spatial SQL yes I can not even very much this is an index nearest neighbor query what trucks are in the service depots trucks and service depots two layers this is a an example of a spatial join again you can express it in SQL and hardly any SQL at that so you can get all the functionality of a GIS without the GIS when you've got PostGIS in your Postgres QL database so you're building a web app you haven't sold you think how do I get this wonderful thing I'd like to use this this GIS without a GIS that you speak of Paul how can I get this wonderful thing and it's in the Postgres database so you just have to connect to port five four three two and you're building a web app you're building an app that runs sides and runs inside your browser the things you connect to you from that web app are web things you're connecting on port 80 or port 443 for SSL so you've got this disconnect here you've got a web browser sitting out there I want to connect things on HTTPS you got the database back here which likes to talk to its clients via the Postgres query query protocol on port five four three two how do you join these two things together historically again you might say I need a GIS or rather I need a GIS middleware and we looked at that and we said you know you can do that but those the GIS middlewares that are that existed and still exist are are quite heavy they do a lot of things and we already have all the things back in the database so we need the smallest thing that can do what we need in order to bind a web app to the spatial smart supposed GIS that is we want to be able to send a query over HTTPS and want to get back one of two things either map tiles so you can make a visual display or Geo Jason so we can get a little bit more interrogation into the features and make something a little more interactive with features those are two things you want back to be able to put into a map panel to be able to build into a spatial application so we asked ourselves looking at sort of the big weighty spatial middleware what is this what's the smallest piece of software that can do the thing we want and the advantage of small is that it's easy to deploy it's easy to take a small thing and put in a container because it doesn't have a lot of moving parts it's easy to reason about a small thing it only does one thing so you can figure out how it works and because we're doing all this in open source another real big advantage of a small thing is it's easy to contribute to contributors can come in they can look at the relatively small code base and say I need to do this one extra thing here's a contribution whereas it's a very large substantial project it can be a very big problem just to figure out how it works before you can contribute so what's the smallest piece of software that can solve this problem and so we started building these small pieces of software we built two to start with we're calling it post just for the web post just for the win pg tile serve is a very small piece of software that provides hdp endpoints so the web browser can talk to it and exposes the tables and functions in the post gis database returning vector tiles and vector tiles which you'll see in the demos that Adam's going to provide give you a rich attractive and also potentially interactive way of working with the with spatial data in a web map pg feature serve same idea except that it returns geo json collections as the response gives you endpoints for tables and functions and returns collections so post just for the web is gis without the gis it'll let's you take your web app bind it to all the smarts the gis without the gis and post just and build rich web applications without having to commit idea to huge chunks gis returns geo json collections and that's the motivation for use the endpoints for tables and functions and returns collections take it back over to whoever's next so post just for the web is gis without the gis and it'll let's you take your web app bind it to all the smarts the gis without the gis and post just and build rich web applications without having to commit ideas to huge chunks gis returns geo json collections and that's the motivation for use the endpoints for tables and functions and returns collections take it back over to whoever's next so post just for the web is gis without the gis and it'll let's you take your web app bind it to all the smarts the gis without the gis and build rich web applications awesome job given a brief review of kind of the motivation behind crunchy spatial and the motivation for future tables and so what i'm going to walk through now is how we actually put that into post just for web world practice so the demo architecture that i'm going to show you here starts obviously with post just as our just a layer awesome job in our microservice layer we do have a raster tile map server to serve up a bit static base maps so you have something nice to look at underneath your your vector tiles and all that is going to be served up into our custom written demo application which is a react application so i'm going to do that in real time here in just a minute and then once i walk through how to build that up in real time in open shift steve and i are going to show how you can do editing in real time in a cloud-based database and it's actually going to be leveraging another feature found in our postgres operator for kubernetes that allows for multi cluster replication between two kubernetes clusters so when we get to that part what you're going to see is essentially the same application but now we have two separate clusters of open shift stood up and we have two two versions of the application one running in each cluster and but only one of the applications is the primary application but data is being automatically replicated and fed over to our standby cluster so if for whatever reason something happens to your your entire kubernetes cluster you have the ability to fail over and restore your your application in very short order in your standby kubernetes cluster and also some of the inherent features built into our operator that that come out of the box are high availability for your postgres database and we're going to we're going to show how an auto failover works over just in in our primary cluster as well so that's what i'm going to show you here in just a second let me go ahead and exit this view all right so uh what you should what everybody should be seeing here is i have my two terminal windows where i'm going to be doing the majority of my work and then i have my open shift cluster ui here on the left side of the screen and as i'm working in my terminal you're starting causing me to jump in yes dave we don't see it we're still looking at the slide yep yeah correct let me uh let me just share nice nice well done we have the technology surprisingly so all right so like i was saying we've got uh we've got my two terminal windows where i'm going to be doing all the the main work but you're going to start seeing all of our pods drop into my open shifts ui over here as things start getting built so uh what what are we going to end up building uh so this is this is a view of the end user application that we're going to end up building it's a it's a demo application that we we built just to show off the power of crunchy spatial and post just and and kubernetes and what you're going to see is it's a purely fictitious uh application that we thought might be representative of how a fire administrator in santa cruz county would be managing parcel information uh to denote whether or not a parcel is considered a fire hazard or not a fire hazard so what you're going to see is all these green and red lines are tax parcel information that we actually downloaded from the santa cruz county website uh gis website and we loaded it all into a database and then we fed it out through pg tal serve and we interact with it through pg feature serve and what uh what allows you to do is select on a particular parcel and then uh it allows you in this view it's uh meant to represent a fire administrator being able to change whether or not a parcel is considered a fire hazard save that off and then to say the the update is actually done directly in the database and then the next view is uh same map same uh same view but only in this case a fire administrator may want to let a a number of residents in a particular area know that a parcel is being updated to be considered a fire hazard so it allows you to do a geospatial query again directly against the data in the database and none of this is being done in the ui and turn all the parcels that interact or intersect with that query radius so obviously we've already got this application set up and running uh just wanted to let you get a sense of what we should see working uh when the when the demo is completely done here so with that let me go ahead and drop back into this view and we'll go ahead and get started so the first thing that I need to do is I'm going to make sure I'm actually in the demo project space uh so I'm building these things uh in the right space so you can see it working so I'm going to go ahead and switch to my demo project and I'm going to start by actually building our ui first since the ui is actually the part that takes the longest uh to to build in this because we are actually building from source using the source to image uh feature and open open shift so I'm going to go ahead and drop that command in here and you can see I'm just grabbing it from my own get repo a particular branch and telling it to use the source uh strategy and I'm going to go ahead and kick that off and if we go over to our builds we'll see that is actually or well wait for it might take a minute there we go uh so now it's actually going we'll we'll see we should see a build config pop in here there we go there's my build config that popped in and we should see the build start going here in a second so while that's while that build is going this react application needs a couple more a couple more things to make sure it's running appropriately so I'm going to go ahead and set a the environment variables in the deployment config for this particular application that's necessary so this is in particular your domain to space learn anything like that this is in particular a more of a react function so I'm updating the deployment config for this and you can see that just got updated so if I go here I can take a look at environment variables yeah um your screen is well behind your voice um what we're seeing on the screen just to let you know is that fire notification it's still showing that screen we have not yet switched to showing all the work that you've done on the other terminal and Adam so it might be that your your wi-fi is a little lag time so maybe turn off your video if you don't mind um and that might be the load on your computer yeah so I'm going to do that so if you don't see his face now that's my doing we look very Adam and I look very similar so you can just look at me and pretend I'm at him okay Adam well hold on you're just resizing your stuff um you can now you can start over with uh from the part where you entered the there's Adam entering the command to you he's logging in to the demo project and he's building this uh oc new app which people should know he's building this this is a um a node a javascript application that we built using react on the front end and then you can see that like Adam was saying before there's the build config coming up and that builds going to be kicked off um so that's going to be doing the whole um source to image process inside of the open shift build and so that builds running Adam where did you leave off oh no now he's setting um he's setting some environment variables right for that app you can see that there on the right side he's just doing the oc setting env for the deployment config for spatial web demo and that's updated and that should be where i stop i left off um now you're exposing the service yep so we'll go ahead and now we're caught up to real time so i'll i'll start walking through here hopefully me turning off my video is is going to help keep things a little bit closer to real time but steve please let me know if it starts lighting in any way shape or form perfect so i'm on the next thing i want to do in this project space let me go back over to my pods view here is uh and you can see every time i made an update it uh it created a new deployment uh for that and so the the next thing i wanted to be able to do is add in that static map tile server that i mentioned we needed to have so again this is just a custom map tile server that we built for this this demo uh you can replace it with your own uh map tile server there's a bunch of different map tile servers out there again not particularly germane to anything uh related to postus or the spatial product but something necessary for the overall application all right so now we've got the map tile server and we've got our ui but we don't have any data actually um up we actually don't have a database so if i went to the uh just the view real quick here we can see we've got uh the spatial web demo which is our our front end but uh but i actually won't have anything in it so i won't even bring it up because you'll see just a a blank app there with no map uh on there so now we actually want to get into the meat of the demo and start deploying our back end postus database so i'm going to switch to a the project where we have our operator deployed and that's called pgo so now i'm in the pgo space and i'm going to log into our pgo client which you can have uh you can have the pgo client uh it's just a cli tool uh you can have that loaded on your local machine we also do offer oops the ability to deploy it actually into the cluster where you have pgo deployed so uh that way you don't have to be dependent on anything locally on your machine so now i'm uh remoted into the client itself and here's where i can actually issue the commands to uh deploy uh the cluster that we need so first off let me find where i'm at here there we go so what i want to do here is using again using the pgo operator which is crunchy's operator for kubernetes to deploy postgres clusters i'm going to issue a command to create a cluster i'm going to tell it to create it in the demo name space where i'm building this application i'm going to give my postgres cluster a name called fire app i'm going to tell it which uh particular image of the crunchy containers that i wanted to use and that is the crunchy postgres gis so that that means that it comes with post just pre-installed and our ha container i'm going to create a database as part of this command i'm going to call that database fire data and i'm going to tell uh kubernetes that i want uh one replica uh created in uh automatically on this and then i can define my password for postgres as part of this if i left this off then kubernetes would go ahead and automatically create a random uh a random secret and uh in the the cluster and uh i wouldn't have to specify this i'd just uh specify the password for the sake of this demo just to make it a little bit cleaner so i'll go ahead and kick that off and you can see it went ahead and uh kicked off a workflow and if i do a pgs show we can see that it's uh in the process of creating uh the initial cluster and then it has uh the uh the replica running or starting to run as well and you can see over here the first container just created we've already got our backrest repo uh created here and now it's spinning up our our primary uh cluster node and once this is done we'll see another cluster pop in here as well so i'm going to give that a second to run and then the other thing i need to do is uh again based on the way we have this data structured i need to create one user um again this is all being done through our our postgres operator and i need to create a user called group and i'm just going to have kubernetes manage the the password for for us here but again telling it we're what namespace to look in and what uh cluster name to create the user in so if i do that you can see uh created our user there and so now we have a postgres cluster or a postgres cluster we have a user created in our space um and so now we need some data in that space now as Steve said we're not going to go through all the painstaking steps of actually downloading uh shape files and loading in we do have a database uh backup pre-staged so i'm going to switch down here real quick and switch to the demo project just so i can say logged into um pgo up here and then you can see now we've got a primary cluster primary uh database running and then we have a backup still in the process of being spun up here so i'm going to go ahead and remote into this one so now i'm actually in the inside the pod in in uh openshift so i'm in this pod and i want to load data into that fire data database uh that we created there so i'm just going to go ahead and issue a a p sequel command so like i said we've got this uh this this postgres backup uh pre-stage and then amazon s3 that's going to pull it down and it's going to load it directly into the database in real time so you can see just that quickly pulling it in and restoring the database backup and we're done there so now we have we have a front end we have a map map server we have the database created with the data in it um and the post just uh extension created in there by default because we use the post just container but we still have not connected the back end to the front end so now i'm going to deploy pg tile server and pg feature serve and connect that to the database and when pg tile serve and pg feature serve are created they automatically expose the data in inside the database and make it available which those rest apis are already part of the web front end so when we uh when we actually connect that and now i'll actually jump over here so you can see where we're at um so real quick like i said so here's our here's our demo front end that we actually just built so it looks very similar to what i i showed you earlier right there's no changes the only difference is we don't see those green and red lines here right we've got the base map uh but and we've got the overall ui framework but we don't actually have the data so just uh just to show that i'm not uh don't have any tricks hitting up my sleeve here so and for those who are wondering the another meaning for that base map is the raster tiles so when um when adam was talking about that before in the architecture that that's basically just a pod that's just serving up raster images that mosaic together to form that base map layer yeah and maybe an easier way to show it right is um so here is the demo front end that that i just had an open shift bill for me right like steve was saying here's the raster base map and here is the one that we're going to be doing the real time editing in a little bit so you can see you got the green lines and the red lines those are the actual vector tiles being served back up directly from the database which we're going to actually implement those services uh now and here's our demo front end you can see those those lines aren't there so hopefully that that makes a little bit clear all right so jumping back here go back to my view here all right so i exited out of the the database pod uh in this window and now i'm actually going to deploy pg tile serve and pg feature serve so again using the new app flow we pull down tile serve and again just that that quickly it pulls down the container image and to connect tile serve to the database we need to set a couple of environment variables so the first one i want to be able to set actually here so i can make sure and pull this back up a little bit make it a little bit easier because it's hard for me to see at the bottom as well so the first environment variables i'm going to set is i'm going to set the username and password uh that pg tile serve is going to use and i'm going to grab those directly from the kubernetes secret provided by open shift and i just do that um through this uh oc set environment command you can see i'm grabbing the postgres secrets and i'm pending the pg uh prefix to it so there'll be pg username and pg password in there and i'm adding it to that deployment config and then once those are actually set the other environment variable that i need to create is the actual the database url environment variable and so this is what pg tile serve actually uses to connect to the postgres database and you can see i'm actually defining what that environment variable is there but again using pg username pg password uh grabbing the fire replica the fire app replica service host um and so that's the actually provided um from kubernetes and notice i'm using the replica so when i created the postgres cluster it created a primary and it created a replica database i'm actually connecting pg tile serve to the replica database because pg tile serve is a read only transaction it's not actually doing any updates to the database so we can use the replica service host on that because the replica database is a read only database so we want to offload any sort of workload from uh from the primary database uh and put it on the the read replica so we'll go ahead and create that so update the deployment config and you can see because i didn't have the deployment configs completely correct you can see pg tile serve actually aired out because it can actually connect to the database but now that i've actually got to connect to the database it's completed it's uh actually running and it's up and the last thing i'll need to do is just expose that service so we have an available route to actually access it from our application so go ahead expose that service so if i jump back over here to my routes now we have pg tile serve and just to give you an idea of what that looks like here's a basic ui out of the box from uh that that comes with pg tile serve and we can go ahead and click on this preview and again this is just a it's a memory limited ui so you're going to see these vector tiles come in in a very spotty way because there's just a lot of them but you can see all of the the vector tiles returning back here and it returns all the attribute information contained in the database we are not going to be using all that attribute information in our in our end ui but just let you know that it is made available to you through the pg tile serve all right so i'll close out of that so we've got pg tile serve there and if i actually actually wait one more thing adam so yeah this is what paul was talking about before was why a vector tile is better than a base map tile so if you looked at if we went to the base map tile and clicked on the map you wouldn't be able to get all this information returned for each individual parcel right if this was just vector if this was just raster tiles but because it's vector tiles we can actually tag information to all the vectors yet allowing us to not have to necessarily go back and forth between the web server and the database server just to get display of information and you can also style on the fly right the raster tile server the one that we had before those are raster images i think jpegs or png something like that so once you make that styling you're done here you can dynamically style the tile the vector tiles and they're the parcel syndrome based on any of those attributes that you see they're associated with them so it gives you a lot more flexibility in terms of um display and cartography yeah thanks eve and so now i brought up the demo application that we're building in real time and by comparison now you can see it looks very similar to uh this uh the the the application i brought up initially but now i can i can click on this but the ui doesn't let me do anything and that's because all these interactions uh through the you're behind again adam hold on you're behind again oh sorry are you look i'm assuming right now you're actually looking at the demo app right yes yeah yeah we're not okay we're still seeing the vector tiles we're finding out that that the internet is not evenly distributed today um no and in girl wisconsin wherever you are the digital divide exists then it is it is very very real believe me yes i can imagine and uh there's much more work to do yeah um so let me know when it's caught back up to kind of the base map that is steve okay the waiting the waiting that's okay i was joking with steve a minute ago is that a long time ago i um i was doing demos on open shift and i was demoing deploying wordpress with open street maps and things like this and we there we go we've caught up now and it we've come a very very long way so now we're on your screen okay all right so what i what i uh was starting to show is um so even in the in the demo ui now we've got we've got the parcel layers here right you can we can see the green lines the red lines we can even click on them to select them but when i actually try and use the interface here it it doesn't do anything right you can see an error has occurred and that's because we haven't deployed pg feature serve yet pg feature serve is actually what drives the interaction that allows the interaction with the database um and that's because all that interaction is being done through functions that were created in the database and we'll we'll walk through that all as i build out pg feature serve so go ahead i'll go ahead and reduce this back down all right let me hear this talk slower adam we gotta wait for it to catch up okay there good we're back i'll i'll take longer pauses there we go nice nice be very contemplative good yes all right so first i'm going to go ahead and same workflow that we did for whoops not uh i don't want to expose the server's hit because it hasn't been created so same same workflow for um that we did for pg tile serve i'm going to do for pg feature feature pg feature serve or maybe i'll just uh stammer instead or a lot and that'll take a lot of a lot more time so same thing grabbing our pg feature serve container pulling that down we're going to set the same environment variables that we did for pg tile serve and then add the uh at the database connection url as well and actually before i post that in there i'm going to go ahead and clear this up just to make sure uh it's a little bit easier to see here so the main difference though is because pg feature serve is actually going to be updating data in the database we don't want to connect it to the replica service host we're connecting it to the primary so you can see that right there and that's because the primary is what allows us to actually make changes to the data in the database so pg um pg feature serve is connected to our primary database pg tile serve is connected to our replica service host and that's actually going to be something important to keep in mind a little bit as we show the autofailover workflow uh between the two here so i went ahead and updated the deployment config there and then last but not least expose the service out and so now jumping back over to this ui i can go to our routes and you can see we've got pg feature serve as an available route now bring up the pg feature serve uh just kind of stock engineering ui and if i go to collections you're going to see the same uh layers that that we had available through pg tile serve these are being returned back as uh ogc apis uh though so there's there are a lot more rich a lot more um uh feature dense if you will um and we also have the ability to view functions now if i click on here we don't actually have any functions available to us yet we are going to create those in just a minute and as i said before the interaction for our application is actually being done through functions that reside in the database that are accessible through pg feature serve and so even though we have pg feature serve deployed now it is actually available through an api if i go back to our demo application here and refresh i still don't have the needed interaction um i can still click on these i can highlight them but when i try and do anything it doesn't actually do the update again because we're missing those there's a couple of key functions so the functions that we're going to add are two uh two pretty simple ones we're going to add a parcel within distance that's going to be able that's going to allow us to do our distance query and then our parcel set fire hazard function and that's what's going to allow us to set the attribute information in the database from the fire hazard attribute from either yes to no or no to yes and so you can see here's here's the code that is required to create those functions and so now i actually need to create those in a database well we've got this saved off as a data definition language or library in the sql format and i've got that pre-staged so in the same way that i was able to restore our database backup i'm going to do the same thing down here go ahead and just rsh back into that database the other thing i should add in here as uh so right now pg feature serve has to look for the post just ftw schema so you can see over here that's the first thing that happens in this uh in this ddl is we actually create that schema then we create the functions within that schema and once uh those are both created we'll be able to see them in the in the pg feature serve ui and i should probably stop trying to multitask because they keep messing things up here all right so now i just remote it back into the primary database pod and now i'm just going to go ahead and grab the command here to restore that ddo so again in the same way that i was able to do the database restore just go ahead and grab um the pre-staged um oh that didn't work what didn't that work didn't work because it's live of course that is exactly why it didn't work so i can hear you want to try curling it directly from um github no hold on i gotta be in a temp directory there we go okay that time there we go helps if i have the uh have it in the right place to actually be able to write the file so there we go we can see that pull down that that ddl and then just load it directly in it's got a whole bunch of awesome coordinates in there as part of the test and so now going back over to our interface here let me go full screen go back to my pg feature serve ui and if i view the functions now we can see we've got the two functions that we need here the parcel set fire hazard parcel within distance and jumping back over to our demo ui now we'll see that we've got a fully functioning interface here we've got all of the uh the attribute information back here that wasn't present before and i can go ahead and hit yes and you can see it changed to red because the attribute information was actually updated directly in the database and i can go over to the active fire notification and select that and now i can do my distance query we'll do a different distance this time you can see i'm getting all that that information returned back so now we have a fully functioning web application and it was all built out and for real time that took me about 20 to 5 minutes including time to actually pause and let the uh the system uh join up here and so i'm going to go ahead and make a couple more changes here and we're going to do just another quick live demonstration on the failover capability so you can see i'm making a few changes here and the reason why i want to do this is to show you uh that it's actually being stored off in real time in the in the backup and it's going to be restored from failure so we can um so i change this this parcel this parcel and this parcel uh to red uh from green and if i go back into uh let me shrink back down here real quick so remember this view i'm still in my primary postgres pod and so i can see uh pg data is where all of the primary data is actually restored and let me just do one more quick pgo show up here so you can see so here are my my uh database is running in my cluster so we've got my primary here so remember uh w7 cj2 is the primary and the replica is gntsb so i'm going to go ahead and intentionally crash my database which is always something fun to do in a live demo right because that's that's what you hope doesn't happen but we're going to go ahead and do it just to show how auto failover works so i'm going to go ahead and remove the pg data file and uh lowercase r oh thank you and it said permission denied but actually if i go back up here and show um you can see now our primary cluster is in an unknown state because i corrupted the the data file and now it automatically in just that amount of time it switched the gntsb to primary so it actually tore down and it's in the process of restoring the former primary back to a replica and you can see just that quickly now that's back up on replica now you can see well obviously nothing happened here if i go back here um all the data is still the same uh as we had it before but remember when i when i connected pg tile serve it was connected to the replica service and pg feature serve was connected to the primary service and so i can still go in here even though the cluster has failed over and the nodes are not that or the pods are not the same anymore i can still go in here and make updates uh to the database let me select that one let me change that back to no and you can see the the interface is still fully functional fully uh fully working and just that quickly um the operator went ahead and failed over from the replica to the primary and there was no impact to our user application so when you're talking about a mission critical application a business critical application data assurateness you want to make sure your data is always up always available having a highly available postgres database driving your back end application is a critical piece of that and really helps make sure your um your your application is always up and your data is all of it always available to your end and users uh so steve that that wraps up the real time build of the single node is instance do we want to switch over and show how you can remote in uh using qgis and doing data updates and then we can also show how it's being replicated between the two clusters sure before i do that though can i put you on the spot sure um you remember the command to scale up the replicas oh uh yeah so what the reason i wanted to ask adam this is so we showed um that the failover happens but also you can set auto tuning for um the uh you can set auto tuning to let it scale up on its own we don't actually enable that from the most part because databases are you don't want to be spinning up replicas and creating all that bandwidth in general but you can manually go manually go in and if you do this again you'll see that we can actually now actually just create another database replica on the fly so if we were serving up a lot of tiles and it was really there so it just created two more replicas right yep so we just increased by two more so now we're running three replicas and this is also the part that shows um how pg tile serve and pg feature serve running inside of open shift are cloud native right because this you know there's no state stored in pg tile server pg feature serve right there's no state stored in there all the state is in the database so if you really actually need it we're now reading off a more replicas but we can also take tile serve and we can just go ahead and into the just scale it up and it doesn't matter right like that's it's always talking to the service so it'll know how to route to the rep to the replicas that are behind that service and now we have three and so they're load balancing to the replicas in addition to having three new services coming up right this isn't something where there's state stored somewhere other than like there's some other um map servers out there where they store state actually in the application itself and those become very hard to scale up and they're actually quite a hog in terms of resource usage if you look at feature serve right now so if you click on do we have metrics for them uh i can probably yeah let me help over i think if you just click on the um oh yeah you can click on that too you can well that's the larger view but if you if we look at it later um we're gonna actually be able to see the metrics for the specific pieces and you can see that they're basically consuming almost no resources to do their work because they've built in a lightweight cloud native format rather than a one app server to rule them all kind of format so i just wanted to make that point clear while adam was doing his demo so i'm gonna switch take control now yep and so let me make sure people can see you can see my map absolutely yep we can okay so this is the exact same app exact same map um coming off of adam's space but from before right and you can see we've got the let's see if i don't know if we're serving any exact same let's check let's go here to refresh the state on the map we come back here this is the one of the demo because this is actually in the failover cluster that um you know that replicate we had where the data was being replicated between actually two distinct open shift clusters i'm attached to that data set right now right and that cluster this is the primary in that cluster um that's why you don't see adam's changes because it preconfiguring all that cluster change is not something we can do easily on the fly i mean we probably could have but it's in in the essence of time we preset that up beforehand so what i'm going to show now though is it's quite often in gis frameworks that um the users are desktop application users not web users right like so that web administrator view that we showed before that might be good for someone who's not into the gis stuff but for the gis people they actually usually use a desktop application so i have a desktop application up i did a port forward so here you can see me oh it's not on the screen here you can see me port forward to the primary right the primary doesn't have any letters there's like a the replica has some letters another set of random letters in it so we're actually talking to the primary and i've port forward five four three two to five four three two on my local machine which is um the port for postgres right so we're connected to that move that back out of the way and then i'll bring up the desktop app so inside of here i had already made a fire spatial connector right and that is actually connecting to the database in the in the primary right and we can see all everything in there i've already dropped assessor parcels on here if you we're going to actually update this parcel here this one was already fire so if i click on it and we look all the way i don't know if this is actually viewable on your screen but if we look all the way let me see if i can find it uh so fire hazard oh we click two we were too far out i need to click that one yeah let me scroll down again and went too far where is fire hazard anybody see it then you might have just there it is there it is i think there it is so you can see it said to yes and if i go back to my map view quickly you can we're looking at this parcel right here so we're going to do the use case where there's a gis analyst a desktop analyst back at the the main spatial processing center for the fire department or for the county and they said oh you know what we've gone out and inspected this one and this parcel is actually now high fire potential so what i can do here is i'm actually going to select this parcel right and we can look down at the fire hazard again and it's uh where to go where to go where to go there's a lot of attributes but you can see this one says only yes portion so not the whole thing we want to change that so what we're going to do is we've selected this we can bring we can start editing on this the toggle editing now this allows us to make changes through here and then we bring up this form for changing things and we scroll down to there oh no that's timber it always get that one always catches me here fire hazard so we're going to go and edit it right so we're editing on the primary there we go it's all done you don't see anything in the gis view because you wouldn't um did i toggle editing yet nope i got to turn off the editing so that saves so this is basic toggle editing basically opens up a transaction and when i save that actually commits the transaction to the database and now if i go back to my view and i refresh the page i mean if we were really fancy cool we would have some sort of web sockets but it doesn't happen often enough to do web sockets but now you can see that the it's gone out to the replicas right because the replica is controlling the um the tile serve and you can see that tile serve is now saying this is a fire hazard map right this is fire hazard so that was right to the primary replicate the data to the replicas and then serve it up and change the map and so you can do this whole you can do a dual um a dual functionality or dual user type role given that we can have stateless servers and that we have access to this database with its all its replication set up for us out of the box and so you can have desktop people changing data and analyzing data and they're the only ones that have permissions instead of doing it maybe through here or you can have web users do it and it's all synced up together the under the benefit that would have happened is uh in here if i wanted to run those functions in the ftw post just ftw schema i could have actually run those functions as a call inside the database here so like paul said in the beginning we believe like not surprisingly that the database is the center of all the knowledge and this way any client that connects can actually use the same functions change the same data everything's replicated and synced up and the database is taken care of by the operator so with that i'm done with the changes and now comes the part like the 1812 overture where the cannons go off at the end where adam hopefully fingers crossed adam was going to demonstrate failover between clusters so let me adam you can take back the go ahead uh take back the presentation if you want i'll stop sharing or you got it so um yeah the the 1812 overture is going to be a little less dramatic um i'm not going to demonstrate demonstrate the the full failover but what i i do want to demonstrate is just the the replication between um the the failovers because as i as i was showing before um in a single cluster you can do automatic failover on a on a single pod um between two clusters it is a little bit of a manual failover process um when your primary cluster goes down uh you switch over to your standby cluster you have to bring that down and then you have to promote it back to a primary it can happen very quickly um but it's not automatic um and so and you also want to be very deliberate about that because maybe um maybe your primary cluster uh you want to just go ahead and spend the time to bring your primary cluster back up your primary Kubernetes cluster and not do the full failover so you want to be a little bit more deliberate between failover between clusters but what i do want to show is so here we're back in um we're back in open shift obviously and i'm going to go ahead jump to um so instead of the demo project i'm going to jump down to the project that steve was just showing and so here's where where we have um all of the uh we have the multi-cluster version of it stood up you can see we've got our the same version of the demo here so i'm going to go ahead and clear all these out make sure i'm using the right one and so we have this version of the application running in the primary cluster here's the the version running in the standby you can see there's a little number two right here that tells me i'm on a different one but uh they they should look identical and what i'm going to do here is you can see here the changes that steve just made i'm going to go ahead and change this back i'm going to say steve you messed up no that's actually not a fire hazard and turns out this one wasn't a fire hazard either and so i'm going to go ahead and change that to no so you can see these two have been changed no no again how weird back to our our standby cluster you can see these they're still showing up as red that's because uh to replicate over there uh it does have to go through an s3 bucket and it takes about 10 seconds or so for that that replication to actually happen in full so i'm going to go ahead and just give it a couple more clicks and you can see already just in that a little bit of modern time these two have uh have changed but again because this is on the standby cluster i can't actually make any changes to this one because this entire database including the primary one is marked as a standby so i we're not going to run into any sort of um transactional conflicts uh by somebody accidentally going to this one and trying to make an update to this one it's not going to let me because the entire database primary replicas are only in read only and they're they're uh they're pulling from the primary cluster uh in the the primary kubernetes instance um but i still have the ability to do my radius search here uh just because that's that's a uh just a basic query there so i can go ahead and do that so um hopefully that uh that highlights the ability to not only do a highly available postgres instance in a single cluster a single kubernetes cluster but also being able to do replication between kubernetes clusters and again uh there's all sorts of interesting different architectural things that you can you could do with that now again when you're replicating at the database level and you're making sure that all your data is consistent available throughout your architecture and if any one part of your overall enterprise architecture goes down you have your data available in in different instances and you can quickly restore and bring that back up and and replace your active application and so with that um i'm going to go ahead and bring slides back up here hopefully and so as steve mentioned uh if you want to learn more uh you can always uh go to our website and learn more about uh crunchy postgres for kubernetes on the documentation we do have a learning portal so if you go to crunchydata.com go to our learning portal or learn dot crunchydata.com uh steve's got a great site there uh full of kota kota courses not only for the postgres operator but a bunch of other postgres courses you can learn more about postgres obviously crunchy spatial or you can email us at info at crunchydata.com so with that steve i'll hand it back over to you any any closing comments no i just i hope people got a sense of the way that we've moved into a cloud native architecture for spatial right um if you look back to the can i just show my slide just for a second i'll show my slides here we go so if we look back at this architecture diagram i don't need to do the two different ones together all of this is made possible by open shift right this high level of automation if you notice there was basically we had similar commands for every single thing we did right there was a common operating plane coming through open shift so if you were to stand up this cluster yourself you don't have to learn how to install postgres right you don't have to learn how to install pg feature serve you don't have to learn how to scale it up you don't have to learn how to put the load balancer in front you don't have to do any of that stuff right and or even the tile map server which you could scale up it's the same um paradigm you would use for anything else like engine x or uh jbos eap it's all the same paradigm for how you work with it and the other nice part is we've got tons of automation going that is just taken care of by the platform itself you saw the power of the operator right so the operator knew how to do failover on its own we didn't have to read through and configure and do all that stuff that we would normally have to do to set up um primary and read replicas or the failover itself that's like that's years worth of work that all comes out of the operator framework and what we've done like crunchy to build that postgres operator and then you saw we build pg feature serve and pg tile serve to be cloud native they're small there's no state they scale up they they respect the way that kubernetes is expected to work right you just set a couple environment variables for the password you can actually even set them as um in the deployment config directly we built the configuration of it to work with kubernetes and open shift right and so then you can build your ui layer but all of it's taken care of and you're not learning some new paradigms so we believe this is the way forward i mean kubernetes the cloud all that stuff has really made it nice for us to do as paul said post just for the win or for the web both of them together right um you can it makes it much easier for everybody and in a scalable way so that i just want to end and we love talking about it so any questions shoot them our way absolutely and i'm sure we will um and this really i think you really have shown us the path forward for cloud native and the future of gis being a cloud native um serious offering here this is really amazing um i know you know the internet may have failed us a few times here but if people really clue into this uh replication between clusters and the different things that that we are showing here this is um light years from where we came um you know uh jokingly with you know plugins the different things and you know the the ability to drill down and scale this stuff now um is just amazing so i really appreciate you guys taking the time here we will definitely have you back um and do more live coding demos because this stuff is just great um if you could throw up your last slide there with the resources as well because i think that would be a great way to end and i don't know if paul is if paul's still hanging around there i'll put up the last slide but paul's still here yeah if if you want to just say um where if people want to get involved in the open source side of this the post gis where where people should go to find you to um participate in that community yeah i want to get involved in post gis the community site is post gis.net and everything that's from there all right awesome wait hold on hold on i'll bring it up and i'll show hold on hold on let's go back to where is it no that's not what i wanted don't want this one hold on let me get rid of this there because that's that's probably steve's homepage exactly i just have q gis of all the time um where is oh here's what i wanted here we go right so here um post just is post gis is it post dot net net yes so this is the post just site and then there's a whole part on it about development right so here's how you get involved they've got a nice list they're very friendly people paul i think it's paul mainly paul and regina that run it is that right pretty much paul uh me and regina are the most consistent people around but there's a there's a development community of about a dozen regular contributors okay but paul and regina are the i don't know about the other i don't know the other people i'm sure they're very nice too but paul and regina are some of the nicest people i know and then if you want to get involved with our other projects they are in github.com slash crunchy data right and their repositories are here's pg tile serve right on the front page right and as paul said it's really slim code like if you dig into this i think the biggest thing in here is probably the documentation in hugo right or the examples uh most of it's happening in a he's doing it because the database is so powerful you can basically this is just a very small shim layer that just handles the hdp the database transactions and that's all it's doing the database is doing all the work so you don't have to do all this complicated calculations or writing inside the code and then the other one is the exact same way this is the one that martin works on which is feature serve and it's the same one thing we didn't get to show that i think i would like to show since apis are the future if we go back to uh not have to find it three deployments i'm confused i mean i'm so used to the three dot one x open shift interface that i get very confused with this new um this new uh interface that you guys have going we're currently running a um customize your ui contest so um if you're into that and you want to customize it backwards so it looks like the three three dot x console yeah you could enter the constant that's what i'll do i'll just basically take the code base from the three dot one i customized it look um so i i wanted to go to the routes is really what i wanted to do i was trying to find the routes so here's the feature serve right i'll bring up feature serve again and that the thing i think that's really cool is features the gis world is usually a couple years behind catching up the rest of the programming world and but this time it's really great um adam mentioned where ogc compliant for feature serve ogc is kind of like the w3 is it w3c right is that the working group for the web right the ogc is that for spatial and they're doing a home you're excited they're doing a whole new um they're doing a whole new configuration of it of their rewriting their specs so that they're actually modern web so the new feature serve spec is actually open api compliant and so as we bring this up wait for it to wake up a little bit it should be bringing up a swagger interface to the feature serve but the internet is not with us today so it's waiting for unpacking.com there must be a third party library that it's waiting for that it can't get so if i stop it maybe there we go so now you get the you get the swagger spec for the feature serve so you can interrogate it just like you would any other api right and it behaves exactly the same way and again it's stateless right so it's restafarian or rest like in what it does so you can look at get the collection right how do i make that call there we put all the the documentation in it so i i'm really excited about how we're pushing spatial forward focusing on the database storing state in the database but then making the other layers lightweight on top and cloud native so now i'm done growing about our site and now is is restafarian a thing or did you just i i say that just because you know there's a guy in um in the spatial community Sean Gillis who was very early in rest and there was a bunch of us who were like tracking rest and Sean was very restafarian and i think restafarian are the people who find fall follow fieldings original dissertation and say unless you're doing the original dissertation version of rest you're not doing rest those are the restafarians and Sean was one of those people and i was not i was just like a i'm just going to do what makes my life easier and programming easier and if i put some function calls in there or something so sue me i don't care i'm just going to make life better i protects is the engine of stake and i will fight you on that i apparently paul is also a restafarian too oh this i can see there's there's a debate coming here uh oh goodness well thanks again everybody for coming today um you know as we said the internet's not distributed equally but i think we we got the gist of everything here and um i'll put this up on youtube shortly probably end of day today sometime and you will see it um and we can annotate it put links into some of these things and if you share me share with me your slide deck i'll also um link that into it too as well so um great work um amazing partners um at crunchy data i love what you're doing um with both gis paul so keep keep doing the good work there um and anything we can do to promote this um i'm thrilled with it because they i don't know anyone on the planet who doesn't like good maps and um that yeah really and we use only people who have problems don't like yeah yeah who anyone who doesn't i don't know if you can see it but like i'm i'm a huge map globe enthusiast i even have my light bulb out there in the background is mine um is is how hooked i am on mapping so i so appreciate the work you guys do so take care um we'll talk to you all soon um someday we'll see you all again soon in person hopefully and so stay safe um keep doing the good work um and um we'll talk to you all soon take care guys thanks thanks thanks