Postgres Architectures in Production

welcome to postgres build 2020 i am dimitri fontaine and we're going to talk about postgresql architectures in production before we get there i wanted to let you know that i've written a book it's named the art of postgresql and the book is intended at application developers who are using postgresql in the context of their application and they want to do maybe maybe you want to do more with the sql and understand how much you can go with postgresql and it's a sql support and more than that so you can use as part of this conference benefits this coupon code that i've made just for you guys with a 20 discount so folks have a look if you want to now let's go to postgresql architectures in production what do we mean when we say that title so what i like to do is go over each single word of the phrase postgresql architecture in production first postgresql of course the world's most advanced open source relational database what does it mean many thing what does it mean for your application that's more important your application might be written in java python with jungle ruby on rails php or go or another technology why are you using postgresql within your application stack some people would say to solve the problem of storage because you have things in memory you want to store them so for storage you use postgres well i'm not sure if you want to do storage you can write to a file and have that file stored for you on maybe azure blob storage or maybe s3 or something else we use postgresql because it implements all that is needed for concurrency you need in your application to serve multiple users at the same time and so you need to do multiple transaction with inserts update deletes all concurrently and postgresql is there to handle concurrency access to the data a single source of data with concurrent access that's why we use postgresql what is a postgresql architecture so postgresql fits in a software system where you have the code you've written in your application java python ruby go php any technology you use you have code written in that technology and served on an application server and then when you need to access concurrently to the same that i said there is postgresql behind it and having several components that talk to each other that that is an architecture that is a software architecture and the architecture we are going to focus on today is the postgresql part obviously and what does it mean to have a postgresql architecture for your application needs and one aspect of it that i want to focus on today is what happens in production what does it mean to be in production in this image on the slide you can see people handling a sport event so some people are having a sport event in a stadium and some other people are watching the event live from their own home in the middle in between them people are working on the console to video stream the content that is captured live to the home of the watchers that's a way to do production when we are running let's say a web application in production we are lucky enough that we don't need to actually be at the console for our users to be able to enjoy our application online so our idea of a production is still that people are using it now live the difference is we don't want to be there and we have the abilities not to be there and while we are not there we still want the application to be available to the users and if you're deploying code in between releases new releases of your code and new versions of it the code is static it's stable it's not changing so it's pretty easy to maintain high availability or availability at least to begin with for the code for the database that's different you need the availability of the service you need to be able to connect to postgres in a meaningful way and then have access to all the data so you need availability of the service and the data when you have postgresql in production and no one sitting at the console to make sure that everything is right the service needs itself to be available at all time and when we have availability we can also talk about high availability what it is we talk about high availability when we measure availability in terms of how much time a year is the service not available and you decrease from 100 percent if it's always available always on it's 100 right and if anything happens and the connection is not able to access your service your postgresql instances then you lose some percentage of availability on the year and high availability there is a threshold that you need to define depending on your slas and your application needs and your customers and users but let's say it's 99.99 of availability a year the ballpark i i try to remember is usually the the high availability threshold means that you are loaded around five minutes of downtime a year so if you install kernel security upgrade and the fact that you need to reboot your server is going to take you at least five minutes well then maybe you're not highly available anymore that's it and maybe something else need to needs to happen because rather than this kernel security upgrade that was planned for maybe one node either physical in your own data center or maybe a vm in your data center or maybe a vm online in the cloud maybe one of those nodes is down not available anymore what happens then if if all you have to run postgresql is a single node it's easy to figure it out what happens is you lose availability and you might have lost data too so for it availability we need to maintain the service even in the case of a faulty hardware or a missing vm and so we want to automate the failover so that we don't have like in the picture before to actually go on the console to be able for our users to use our service and we need to do that to implement automated failover in a way you know that you don't lose data we need to maintain both the service and the data and to maintain the data we need online backups consistent backups and we need disaster recovery and we're going to see about streaming replication to implement all of that the classic postgresql architecture looks like this if you want to have a cha with positros you need three nodes basically the primary and two standby nodes because what we want is to ensure that we still have data even if we lose any one node if you lose one standby you need to be able to say that you still have an extra copy of the data the data is not just only on the primary it needs to be somewhere else to maintain availability of the data whatever happens so we need three nodes so that we can use the standby and still have a copy of the data that is extra to the primary so if you want to have a good trade-off of availability of the service and the data with postgres three nodes are needed one primary to standbys and also in case another kind of error happens in production typically you know delete with a word close or maybe a truncate but it was not on the server for development or staging it was on the server for production this time this is something that happens a lot it happened to me and my teams when i was working as a dba and then i saw that happening when i was a consultant and i still see it i see that happening sometimes so sometimes truncate or drop table or something is going to happen in production that was not planned for it was not part of the schedule but it did happen still so what's next next is point in time recovery disaster recovery and that is only possible to implement if you have an archive we'll get back to the archiving later so now if you have an architecture for postgresql in production that looks like the following with the primary to standby the archiving etc do you want or do you need to go to the console when something wrong happens or something unexpected happens and or do you not so if you want to avoid that and maintain a very high availability so if you have less than five minutes a year to react maybe you want to automate everything so how do we implement postgres failover and how do we automate postgres failover in order to achieve high availability that's that's the talk today postgresql implements almost all you need for failover included in postgres is streaming replication it actually is streaming so if you update a million rows on your primary instance of postgres while the update is happening for every row one after another postgresql is going to record the binary changes that are made in a system called the wall the right red log this system is trimmed to the secondary in a way that during the one minion rose update every raw update is going to be streamed concurrently to the standby servers and when the transaction commits it's only the transaction commit message that needs to be synced on the standby because the standby hopefully already received the 1 million rows of dates in the world stream when we say sync in streaming replication what we mean is something different from what the world would usually mean sync you would expect that two events are going to happen at the same time at different places well that's not physically possible i'm sorry speed of light is not infinite so if you have two events that you want to happen at different places they're going to happen in different times sync doesn't mean we are we have a magic wand to make it happen at the same time sync means we're going to pretend they happened at the same time by waiting until it happened on the secondary and sync means i am happy to wait until it did it did happen what's in postgresql included in postgres that you don't have in many other technologies is the ability to say that every transaction has a way to choose if it's going to be sync or not sync async usually asynchronous so given a transaction it's going to have the ability to choose between sync and async one way to do that as on the slide is to use alter rule so that you have a vip role that is happy to wait because it's a vip and everything it does is important enough so that it's going to wait and you can have other kinds of rules and you can have a default setting in alter database so that's one way to do it included in postgres also is online streaming changes it's possible to do a concurrent consistent backup online so while the application is accepting inserts and deletes and update and postgresql is doing that concurrently for the application you can also do a pgbas backup to prime another standby or just to make an archive it's possible also to use tools just such as pg rewind in the case when there is a failover happening you have a new primary a was the primary no b is the primary and c needs to reconnect its streaming connection from a to b so it's going to disconnect from a with a restart and connect to b now and to make everything right again it might need to do a pd rewind that's included in postgres what's included also is if you need to fetch some wall bits that you missed you can fetch it either from a primary that's classic replication or from another standby and that's cascading replication if you connect to a standby and that allows to implement fast forward right so that's included in postgres and also in postgres the standby is a hot standby because when when a standby is promoted if read-only traffic is happening the traffic is not interrupted by the promotion of the standby the standby is promoted online everything that was happening just continues and now it's not a standby anymore it's a primary included in postgres also is a limited aspect of point-in-time recovery um we insist on the recovery side of things because to be able to implement point-in-time recovery you need an archiving system but archiving is provided separately you need to find the system to implement archiving usually people will use tools that are part of the postgresql ecosystem at large such as pg backrest or woolly or world g or maybe barman and please use those existing software rather than implement your own facility because it's full of traps and you want to avoid traps included in postgres for aha is also client-side aha postgresql supports multi-host connection strings it means that when there is a failover your client is still going to lose its connection right and when your connection is lost at the client side as an application developer you need to reconnect there is no magic around it you lose the connection you connect again the thing that is magic and provided for by postgres is that you have a single connection string with multiple hosts in there and the way that each time you connect you magically connect to the current primary and not to another server so please use the multi-host connection string and make sure that your application connects again when the connection is lost because the failover did happen what's not included in postgres though it's the archiving itself archiving in postgresql is a concept with an archive command and other things around it but the concept is not implemented in postgres so you have to implement it yourself again please consider using pre-existing software offered by the community as open source and i named before a list of software so you use something that exists already but what i want to stress and we'll get back to that is archiving is not part of postgres and we will see what what it means later what is not included in postgres also is what some other systems call online membership changes membership is the fact that you know of a list of servers that are participating into the service and you maintain the service around that postgresql doesn't have a list of standby that are supposed to be connected at any point in time and you cannot make the you know the difference between what's currently connected and what's not there at the moment there is the notion of replication slot that allows you to take care of some of the details around that but it's not the same thing as saying i know a list of servers that are supposed to be there but granted or not and some gossip protocol and some consensus protocols such as paxos or maybe raft can be used to implement online membership changes postgresql doesn't do that but graysquarel doesn't have at the moment the idea that the role of a node in terms of being a primary or a standby a secondary the idea of a role is dynamic it will change while live because something might happen to the primary that causes a failover and when a failover happens the primary is no longer a primary the configuration just changed the role of the server is not the same anymore and that idea is not part is not included in postgres what's not included in postgres also is configuration management say you need to change postgresql.com maybe for performance reasons maybe for something else you do that on the primary and the failover happens well if you didn't take care of shipping the configuration changes to the secondary two the secondary is going to be promoted and it's a new primary now with the old setup so that might be a problem for you also with pghba.com if you have a new application node you deploy in production you need to change hba.com to make it granted to connect to postgres when you do that remember to do it on every postgresql node on your system if you do that only on the primary as soon as there is a failover then your new node for the application service is going to be unable to connect so that's not included in postgres same with extension management extension management in postgres is a two layers story there is the operating system part and there is the postgresql catalogs part if you install say post gis you need to have the package ready on the operating system before you can do create extension post gis but when you do create extension for gis the changes that happen on the catalog of postgres the sql visible changes are going to make it through the wall and the streaming replication to the standby nodes so you need to make sure yourself that you also installed you know the operating system level package for the extension because otherwise when you fail over to the standby then all the queries that are using pos gis are going to fail for the lack of gis.so in case you you know missed the deployment of the extension on all the servers so that's not included in postgres extension management for aha it's not included and also one very common way to lose some of the nines in your aha setup is with postgresql upgrades because either minor or major upgrades both of them need a restart of postgres and scheduling the restarts of postgres in a smart way to avoid downtime for the application well it's not included in postgres so with that in mind i wanted to show you a system that i have been working on to implement automated failover the system name is pidgeotto failover and the basic architecture looks like this with one primary and one standby as we mentioned before over and over again in this presentation you you may not have ha of both the service and the data with a single secondary node in pidgeotto failover a standby node is named the secondary when it's ready to failover to accept a failover when it's a failover candidate when the primary can be removed and replaced by a standby we call the standby secondary so if you have only one secondary you don't have a cha but already you have a failover system that you can use where if you lose the primary then you can use the secondary instead so maybe that's good enough for some applications and maybe it's a good starting point and to get started with that here is how you do it create the monitor the first postgres instance and then the second pidgeotto ctl create postgres allows the monitor to decide if the node is going to be a primary or secondary and remember if you're using pidgeotto failover it's because the role of primary secondary that will change over time automatically so you don't create a primary node you create a node that happens to be currently a primary and that will change now let's say you want to switch to a model an architecture where you have both a primary and also two standby nodes so you have three nodes total and then you can have h a because you have two standbys and if you lose a standby you still have a copy of the data that is not just on the primary to implement that with pidgeotto ctrl given what we did before all we have to do is create a new node again create postgres is all you need to remember so it's exactly the same command line as before copy paste and then you have a third node running and the monitor is going to sort it out and sometimes you want a third node so either that way so now you have three standby nodes and that's how you do it again same command or more often it's called the disaster recovery architecture where the third node is either if you're on prem in your in its own data center somewhere else or if you're on the cloud in a different availability zone so you have a primary and to standbys in the main availability zone and maybe you have another secondary faraway for the worst case you know disaster recovery if there is a disaster that happens on the primary zone maybe you still want an extra copy of the data and for this copy of the data maybe you want to have a different set of settings for the replication so this node is not participating into the replication quorum and it's not a candidate for failover so you make that happen with pidgeotto failover by simply saying replication quorum false and candidate priority zero either when you create the node if you think about it at the creation time you can do that already and if you forgot to do it or maybe if the node has moved from one data center to another or from one availability zone to another well maybe you want to change the parameters online and then you can do that with a pidgeot ctl set node dynamically while the system is live you can do that so included in pidgeotto failover is the handling of streaming replication with replication slot maintained both on the primary and the secondary nodes so that anytime the topology changes the primary is failed over to a new node well then every node can reconnect and everything goes fine because we maintain replication slots for everybody from the get go that's how you do it with possibilities and it's included in pidgeotto failover we even include easy to set up ssl either self-signed or fully signed certificates and you can change your mind online of course again so you can you know install certificates as you go well while the nodes are online all the settings in pidgeot failover they may change online what we include in pidgeotto failover is a registration of all the nodes so that we have membership and we have a network topology and we know about things and we implement with that the idea that nodes are have a dynamic role roles are dynamic that's part of the membership if the node is found unavailable no node is able to connect to it then it's demoted if it was the primary well then another primary is elected if it was a secondary well then it's not a secondary anymore it's a catching up standby instead and that's all implemented in pidgeotto failover and it responds dynamically to what happens on the network you also have commands shown in the slide to show the current multi-multi-host uri and the current state of the cluster and the formation settings of course and all of that can change at any time and with membership changes we can also do uh settings changes like we saw before changing the priority of a node or the fact that the node is participating into the quorum and some other you know settings for your production architecture can be changed online that way we even include a facility to mark a node as ongoing maintenance so that pidgeotto failover knows that this node is not a secondary anymore you cannot failover to a node that is currently in maintenance auto failover finally also includes network split detection and protection protection against split brain so the the last ring last thing you want is to have two primary nodes at the same time online you want one primary whatever happens and sometimes sometimes you might have zero but one is okay zero is temporary it's transient and it's not a happy case but more than one it's the end of the world because now you have to reconcile data that have been modified concurrently on different nodes and that's that's very complex we implement in pidgeotto failover we include active monitoring and role changes we intend that the role of a node is dynamic and you can also do it manually you can perform a failover or perform a switchover within a single command line very easily on your system and you can even go as far as choosing which node is going to be promoted because we implement both pg rewind and also fast forward thanks to cascading replication so you can choose which node is going to be the next primary if you need to for example when you move from one easy to another you can just you know fail over to the node in the new az that's that's included in pidgeotto failover what is not included yet in pidgeotto failover is the implementation of an archiving and disaster recovery solution wherein when there is a failover that happens you have the guarantee that you don't need to change the setup of the archiving solution and also we check and verify that every wall bits that we have is the right one we didn't lose any during the failover and if a bus backup for example what's happening during the failover we can you know redo it because it's not worth anything now um this is not included as far as i know in the in most of the classic solutions for postgres all of them in the documentation are telling you how to set up archiving in a static way they don't tell you what you need to do when the primary changes so that's why i think pidgeotto failover needs to implement that also we would like to implement configuration management tracking and extension management and and some more in a way that makes sense so that you can fail over at any time with a strong guarantees that you remain available both at the service level and at the data level and that's it for my presentation about postgresql architectures in production and now it's time for asking me questions ask me two questions

Channel: EDB

Views: 891

Rating: 5 out of 5

Keywords: EDB, EnterpriseDB, Postgres, PostgreSQL

Id: trDjmfcGTSs

Length: 28min 42sec (1722 seconds)

Published: Mon Feb 01 2021