 My name is Pyle and I'm going to talk about provisioning and automating high-availability Postgres on EC2. Just a quick question before we proceed. How many of you have worked with EC2 or RDS? That's a good number. Before we go on a little bit about myself, I'm a DBA at Omniti Computer Consulting. We are a group of Postgres consultants, amongst other things. That's my GitHub handle and that's where you'll find the repository where I've done most of this work that I'm going to talk about today. Feel free to go through my blog and follow me on Twitter and email me if you have any questions. The first thing we're going to talk about today is why would somebody choose to go with EC2 for Postgres as opposed to RDS? Especially that RDS is so popular and is doing very well. There can be some situations where EC2 might be a better choice for you. If that's the case, then we're going to talk about designing a provisioning tool around your Postgres on EC2 so that you can do a better job of administering a lot of things about Postgres that RDS does for you automatically, but EC2 does not. Consequently, we are going to look at the provisioning tools out there. There are a lot of automation tools and they each have their pros and cons, so we're going to briefly look at those and hopefully that will help you decide on what tool you want to go ahead with. And finally, we're going to actually look at how to automate all this stuff in a particular tool, Ansible, just as an example. The intention is that you won't be able to exactly replicate the code. You can use my code that's in my repository on GitHub, but the intention is that you will have enough understanding that you can pick up any tool and know what exactly you have to implement. So why EC2 over RDS? Firstly, because of the control it gives you. A lot of the times you need to step outside of Postgres and look back at it to see and understand what's going wrong with it. A very good example is the need for extended logging. You might want to log a lot more stuff than Postgres itself logs a lot of the times and there are tools out there that do that. If you're an RDS because you don't have access to the machine itself, it can be hard to place those logging tools in it. Similarly for third party extensions, RDS supports a lot of those, but there are a lot others that it doesn't. And so if you have to use an extension that is not supported by RDS, you have no way of doing that. Another major thing is the need for different resources. They can be more disk space, more storage, less storage. RDS does that, but then EC2 provides you the ability to do it yourself and take more decisions about those things. Secondly, flexibility is important, especially when you decide to migrate something from RDS. You cannot use PGDump all and consequently you cannot just dump out the roles and uses from Postgres. So you have to create those manually to the database you're moving to. Compatibility with non-AWS resources, one major example is let's say you have your master database in RDS, but you want to have a replica on a local machine or say even a replica on EC2. You cannot do that. You cannot have streaming replication because RDS does not support that outside of its own environment. And so these are just a few of the reasons why one might decide to host their Postgres databases on EC2, even though RDS is a great service. If you're going from RDS to EC2, you have to know there are a few things you're going to have to keep in mind. The first one of course is increased administration. RDS handles replication, backups, maintenance, upgrades, everything for you. When you're moving outside of RDS, you're going to have to handle that or find somebody who's responsible for that. Another thing is security. When you're creating an RDS instance, right there you're given the option to choose a VPC, a security group, and other similar security aspects of it. But when you put Postgres on EC2, you're solely responsible for security. There's very little hand-holding. It's just like hosting it on your own machines, except that it's rented from Amazon. And finally, basic monitoring. When you create an RDS instance, by default you get some level of monitoring in CloudWatch, which is another service by Amazon. You don't get that for Postgres when you install Postgres on EC2 because Amazon doesn't care what you're installing on EC2. So even a basic level of monitoring will have to be done and set up by you. Moving on to the automation tools, there are a lot of tools out there that do a lot of things. And the four main popular tools are Ansible, Puppet, Chef, and Salt. How many of you have heard of all four of this, please? Okay, that's great. How many of you have worked with Chef or Puppet and Ansible or Salt? Okay. My talk mainly focuses on Ansible, but I'm guessing you won't have much trouble since most of you have worked on one of these automation tools. So since most of you have already worked on these, you might know there's a push versus pull thing when it comes to provisioning tools. Ansible is the push type, and Chef and Puppet are pull. Salt is in both categories. One main advantage of push systems is, let's say if I want to run a bash command just once. I don't have to put it in any script. I don't have to make a file for it. All I can do is tell Ansible, go and run this one command on the subset of the servers, and it can do that. So that's one big advantage of push-based systems. And in general, push-based systems initially, at least, tend to be simpler. They can get complicated as you move up and scale, but in most cases, a push-based system is simpler. One tool that wasn't mentioned in there was Terraform. How many of you have heard of Terraform? Great. For those that haven't, it's a provisioning tool that specializes in cloud infrastructure management. And I have used it just to manage my EC2 resources. So while I've automated everything Postgres related using Ansible, I have used Terraform to create and destroy and maintain the EC2 instances. Why did I do that? I could have easily done that in Ansible. Yes, but not as easily as Terraform does it for me. Basically, all I have to tell Terraform is this is the information you need to log into my EC2 account and create an instance. I just tell it to create an instance and it can just do it for me. Because it has functions that are inbuilt in it for each of the popular cloud providers. Before we move on, just a quick tour of what Ansible is. Since most people here have worked with it, I won't spend too much time, but yes, the black arrows you see going to the servers are basically it executing its playbooks. A playbook in Ansible is just like a recipe in Chef. It's a collection of commands and tasks you put into that you want to execute on each or a subset or all of the servers. If there are certain modules in Ansible that you don't have, you're free to create it in Python. Yes, you just create a playbook, a collection of tasks and tell it to run, give the IP of the servers you want to run it on. And you're good to go. Why I chose Ansible? As I already mentioned, it's simple. It's clear because it uses YAML for all of its playbooks. So basically, you write every task in YAML format, which is very human readable. We're going to look at a lot of those ahead. And you shouldn't have trouble following what's actually going on there. There is also something called Ansible Galaxy, which is like CPAN in Perl. A collection, a place where people can go store their modules and roles and you can access it and reuse their roles. There's always some danger, but it's worth it. Let's move on to design. This is something you want to, when it comes to Postgres Automation, you want to design it beforehand. This is not something you start doing and then plan along the way because after a while you realize that it's doing completely different things than what you expected it to do as you add more modules and features to your automation tool. The first thing you want to design is planning out how to set up your EC2 instances, how to make changes to it, how to add new instances, and then how to manage Postgres installs, reinstalls. This also includes major upgrades as well as minor upgrades and the dependencies you might need, how to handle those. Tuning and customization because it's the next most important thing after setting up Postgres, right? And replication, this also will include slave rebuild as well as certain security aspects that you should keep in mind. Backups and replication, monitoring, and finally failover. In the failover section, I will go through semi-automated failover as well as fully automated. I would recommend to always have some level of human interaction when failing over, but again it depends on your needs and the choice is totally up to you. Such thing as a bad design in Postgres Automation? Yes, there is and it will hurt eventually. In the beginning it might not, but eventually, yeah, it's going to become more and more of a pain. You start with designing modules separately and try to make them work together and end up with a very ugly hack that barely works. You sometimes use tools that you shouldn't just for the heck of it. Like here, this bar graph actually could have been very well represented with a calendar. And for those of you wondering, the length of each bar graph corresponds to the day of the month and the x-axis is each state. So yeah, very easily could have been represented in the form of a calendar. In the same way, don't use a tool that doesn't make sense for the task you're going to do. Make sure your modules do not interfere with each other, they interact with each other. So a very good example of this would be, let's say you have two features in your tool, right, in your software. The first one is a slave rebuild feature and the second one is a normal replication. Let's say you have multiple replicas. If you're rebuilding one of the replicas, you don't want to break replication for the rest of them. And while you might think that that's not going to happen unless something very stupid is done, but if you do not design beforehand, that can very easily happen. You do not want replication of the rest of your replicas to break just because you're rebuilding one of them. You also don't want your fragile infrequent tasks to break just because of your routine automation runs. For example, if you're performing an upgrade, which is a relatively infrequent task, you do not want your routine run to run in between and undo all the changes that you did for upgrade and break that activity. So you want to take into account that you're not doing that. And the best way that I could find was to create switches, which made it very easy for me to make those interact with each other in exactly the right way. And we're going to look at a few examples ahead. And finally, clear documentation. Avoid ambiguity. Do not reuse code amongst different features. Just copy it, redundancy is good in this case, because as it is, if somebody wants to make a change in the replication feature, they do not expect that to also change installation. So do not, yeah, code reuse, redundancy is better than reuse in this case. Moving on to setup and installation. And from here on out, this is the next few slides are Terraform. And the way Terraform works is basically it has, in the simplest terms, it has a variable file and a main file. In the variables, you provide it all the things you wanted to have to be able to access and create or interact with your AWS objects. In this case, I'm giving it a key name, my keys, and the AWS region I wanted to go and create the instances in. And then in the main file, .TF is the extension you use to create Terraform files. In the main file, all I have to do is tell it what my provider is, in this case, AWS, and give it all the variables that I just put my key values in and tell it which region to go to. And what user I want to use, what key it should use if it wants to SSH, and the type of instance, the security group, and how many such instances do I want to create. In this case, the count is two, one for master and one for replica, the absolute basic level. Even though you don't need to do this for your Postgres automation, it's still good to know. There's some amount of limited provisioning that Terraform does provide. So in this case, you're basically telling it to SSH into the AMIs, the instances that it just created, and install a couple of packages, just as an example. And if everything goes well, when you literally type that command Terraform, apply in the directory where you created the previous variables and main file in, it's going to start creating the instances with the values that I gave it. And yeah, it should finally spit out the IPs that it just created. In this case, this is what you should expect to see in the AWS console. In this case, the screenshot I took was of a terminated instance because I didn't want to paste the IPs of actual instances that I was running, so you should see the instances running and the public IPs mentioned in this page on your AWS console. The next part is Postgres installation. Before we go ahead with that, and now this is all Ansible, that was Terraform. We are done with the non-Postgres stuff, and we are on to Postgres. This is the simplest main file you can have, which is basically, for those that haven't worked with Ansible before, you can group IPs into groups, so in this case, I have three groups. I have the DB group that's first, I have the streaming masters and the streaming slaves, and then for each of these, I tell it to run certain roles, regardless of any switches, and then there are some switches that have defined in the variable section of Ansible, based on which some other roles run. For example, in the first portion where all the IPs in the DB group, which include the masters and the replicas run, if I turn on the install switch, it will run an install role and make sure Postgres installs and dependencies installs and all the related stuff. Similarly in streaming master, when I turn on the failover switch, it makes sure that the IP that belongs to the master now is the master. Typically how I would go about it is swap the IPs that I want to failover and then turn the switch on and Ansible will take care of the rest. Finally, the streaming slaves, when I want to rebuild a slave, I turn that switch on and it checks to see that switch and based on it, it runs or ignores the slave rebuild role. So this is an overview of how the main file should look like. Any questions? Yes, I am using Ansible for the Postgres install. Depending on what OS you're using and how many types of OS you have on your infrastructure and your dependencies, it just would make sense to have a central location where you're managing all the installs. But yes, if you're using only a particular OS, it might make sense to do that stuff in there. Postgres installs, the very first thing you do is make sure you have the source key list added. In this case, in this example, the instances that I created were in Ubuntu and Ansible has modules for a lot of the tools and it has a module for the aptitude package manager, which is what I'm using to add the key. That key belongs to the Postgres package, the 96 package. So I add the key. This is all YAML by the way. For those that haven't worked with it, it's basically you're telling it what the task is doing and then telling it what actually to do. Once I've added the keys, I tell Ansible to install Postgres for me. Again, I'm sorry, how many of you have worked with Ansible before? So with items is a defined array sort of a structure in Ansible wherein you can put in elements, a list of elements and Ansible would know to iterate over those. So in this case, I'm telling the aptitude module of Ansible to iterate over everything that's listed in with items and make sure they're present. One property of Ansible is that it is idempotent. So when I tell it that the state should be present, it's going to go run itself and check. If the package is already installed, it's not going to do anything. It's not going to make any change. But if the package is not installed, it's going to make sure it is. So it's going to install it then. So there's no harm in always letting the state present equals to true in this case because if it's already present, nothing would happen. Oh, and I'm using a variable Postgres version to make it simpler for me to change versions of Postgres. So basically the last part is the version is defined as 9.6. If I just want to upgrade it, it becomes easier for me. I don't have to make the change everywhere so I can just define it in my attributes file and it will change it everywhere else. Reinstalls are simpler, but just in the case that you're managing dev instances or test instances where you might need to destroy or recreate data, a PG data, a good idea is to have flags. A lot of people use flags everywhere. I wouldn't suggest that. I would suggest to just use Boolean parameters, switches, and handle them in the main file when it comes to different roles. But if you want to have conditions within the roles itself and within the tasks, then flags come in handy. So as long as it's controlled, it's a good thing. So in this case, I have a flags that says uninstalled binaries and data and depending on whether that's set to true or not, it only uninstalls binaries or installs both. The next part is customization and tuning because what might work for me may not for you. So the first thing is defining, telling Ansible where to place the PG data, configuration files and basic settings like that. And creating, this is how I went about it, but there are various ways to do that, but have an array postgres extensions where I put in all the extensions that I want to be installed at any time and then tell Ansible to iterate through it like we saw in the previous slides. And it makes sure that they're installed. Just to give you an idea, this is what a snippet of postgres SQL config template looks like. So basically all the usual parameters in there that point to variables. The variables themselves are defined in the attributes file. That's also within Ansible itself. And so when I typically go about running Ansible, Ansible knows when it goes through the template, it searches for that variables value in its attributes and places it in there. Similarly for PG HBA, I can either have customized option to put in single individual entries. If I have more than one replicas, it makes sense. The first part that I have an array with all the replica IPs and loop over it to make each entry into the HBA conf. And to find my values, the file is going to start looking something like that. And finally I tell Ansible to configure HBA file where to place it, where to place it from, give it the template source and give it the required permissions, the file permissions that it needs to have. If everything goes according to plan, the command you will run is Ansible playbook and give it the main files name. And in this case, the install switch is turned on and you can see it adding the source list and installing PG data, placing all the configuration files, installing the dependencies and making sure the service works and starts up fine. There are also roles in Ansible for managing roles and databases within Postgres. So you do not have to use raw SQL to do that. You can just use the modules to create roles for you and databases for you. And that's what's happening in this. The first portion is the roles getting created and the lower half is the databases and extensions. And we're done with tuning and customization. The next obvious thing to do is replication. And again, just like we saw with the tuning, we have related parameters, variables being defined in our attributes portion. And similar to HPA and Postgres, we define recovery.config file. We tell it where to place it, the template, the permissions it needs to have. And again, the lower half is what the template of recovery looks like. It uses all the variables that you've already defined before. So you do not have to put your replica IPs hard coded anywhere except for the host file in Ansible, which is the file you have in the parent directory where you define, where you state which IPs are replicas, which IPs are masters, and all the database other groups that you might want to have. This is basically what I was talking about. So in this case, since I only have one replica in place and one master, those are my groups, aside from the DV groups. For those who are especially paranoid about security and not comfortable with placing plain text password in recovery.con file for replication, PG Pass offers a slightly better approach wherein the file itself is hidden in the user's home directory. So that's what I'm telling Ansible to do here is to put the contents, the login contents in PG Pass file and place it in the home directory. And this is what you will see for replication. When the switch is turned on, it's going to recreate the database from a base backup of the master. That's state six. And configure recovery, create log direct trees in the usual setup of service. And once that's done, you can see that the master now knows that it has a replica that's streaming from it asynchronously. So just make sure the slave rebuild or the slave build, whatever switch you define is false by default. And you have to turn it on if you want to set up a replication from scratch or after a failover. And in this case, I use PG base backup to recreate my copy my data directory, but there are a lot of other alternatives. So all you have to do is change that in that particular task in Ansible's playbook. So it's literally the command PG base backup command. In Postgres 9.5 and above, there's a great new tool PG Revind, which would save you the effort of rebuilding a slave from scratch, especially after a failover. What this does is synchronizes a cluster with a copy of the same cluster. So if you move to a new master, you don't have to rebuild the old master, destroy the old master and recreate it from scratch. You can just tell the old master to start copying and synchronize based on what the new slave is. And it's a great tool. It saves you a lot of time, especially if the databases you have are quite large. And so instead of the slave rebuild option after a failover, you're free to use PG Revind if your Postgres is on 9.5 or above. It does require super user access, so that's a prerequisite. Other prerequisites are it requires wall log hints to be on or data checksums to be enabled. Unlike wall log hints, which you can turn on and a restart applies the change, data checksums can only be changed when you're initializing a cluster. So if you don't have it turned on, you should because it also helps with avoiding data corruption. And the way you can do it, the best way is the next time you're doing a major upgrade or a migration and you're initializing the cluster, make sure data checksums are turned on. So we're done with replication and we are on to something that is often ignored, but very important is to always have backups. So in this case, I have logical as well as point in time recovery. Logical backup is very simple. I don't need a lot of variables for that, but for recovery, just a few basic variables to define you want to set up yet another switch. Make sure you're giving it all the required information. And the way I have done this here is I wanted to stream all the wall logs directly to S3 and not have to store them anywhere on EC2 or elsewhere. So in this case, I used Ansible's S3 module, which requires me to define a few other variables. Basically, where do I want to store them? What's my bucket name? What's my key name? What's my secret key? And that's, that's all the variables you have to define to enable streaming to S3. As I said, logical backup is fairly straightforward and just provided to the PG dump command. I have a few variables in there, but if that's something that you won't be changing except for the date portion, you don't have to have it. And then I use the S3 module to upload the backup file. In this case, the logical backup that I just took. The S3 module has a mode just like the AWS Clies S3 command, or if those of you have used the tool S3 command. So the put mode uploads it and the get mode downloads it. So while taking backup I used put and while restores, I will use get. But first, normally when you go about restoring, you want to see if the file that you want to restore from is already present. A lot of the times you have a few days worth of backups on your local machine. You don't necessarily delete it as soon as you upload on S3. So that's what I'm doing here. I'm first making sure that the backup file that's requested is not present locally. If it's not present locally, then I will ask Ansible to download it for me. So the way I'm doing that is with the help of a register. So the register backup file in the first section checks to see if that file exists or not. And based on what the value it returns, I decide whether to download that file from S3 or not. The second last thing I have to do is make sure there's a log file in place to log the PG restore output. In case it errors out or there's some useful information I need to see. But yeah, that's all I have to do for restores. For point-in-time recovery, the tool that I chose was Wall-E. There are a lot of awesome tools out there. OmniPitter is my favorite. But the reason I used Wall-E was because it has inbuilt integration with the cloud storage systems. What that means is if I want to stream all my wall logs directly to S3, I don't have to do anything extra. All I have to tell Wall-E is that this needs to be uploaded to S3 on so-and-so bucket with so-and-so privileges and permissions. And it will do that for me. It has functions that are within it. I don't have to deal with anything additional to be able to store something elsewhere, as long as it's a cloud storage system. So here I'm giving it all the access permissions it needs. And Wall-E is an open-source tool. It's on GitHub. If you look at the readme, you will see it tells you precisely where to place these files that it needs for S3 access. So that's basically what I'm telling Ansible to do. Pretty much in all of this slide is telling Wall-E where the files are that it needs to be able to access S3. A tidbit Wall-E does require a few dependencies, so make sure you have those installed, particularly PIP and Daemon Tools. So that was pretty much setting up point-in-time recovery as well as logical backups, storing them on S3. But if I want to do it every day, I don't want it to depend on a person doing it. So of course I want to set up a cron. And Ansible does have a cron module. Who would guess? So yeah, basically you give it a time and date to run, tell it what it's doing, and you're done. Another very important thing and often overlooked is monitoring. You want to set up good monitoring and tell it to alert you on time before it's too late. And the good thing about Ansible is it has a plethora of monitoring modules. And that's the link where you can go to look at all the monitoring modules it supports. And this is an example of a monitoring service called Serkonus. And pretty simple stuff, self-explanatory. So it's an annotation module. It is supposed to set up a metric on an app change. And for those who still want to go ahead and do it on CloudWatch, this is a snippet of how you would go about doing it. Basically just giving it a name, telling it what the threshold is to alert on and giving it a description amongst other things. And again, CloudWatch also has a module inside Ansible, so that's what it's using here. How much you set things up and make sure everything's in place. Sometimes you do have a fallout and you do require a failover. And that's what is happening here. The switch that I have for failover is Promote Slave, which is by default false. So of course when you have to failover, you turn it to true. And based on that, remember the main file we looked at a couple of slides ago. If this is turned to true, it prompts the Promote Slave role to run, which essentially promotes your slave. And so that's what you should see if you're doing it exactly the way I did or using my repository. For a fully automated failover, there are a couple of alternatives out there. I decided to try out PG Pool and Watchdog, mainly because PG Pool has been around for a long time. And this is basically PG Pool 2 and Watchdog. This is an overview of how it basically works. Watchdog is a monitoring module on top of PG Pool. You place it on every PG Pool instance you're running above every database. And Watchdog can communicate with other Watchdogs in its cluster and talk and make sure that everything's running as it should. If say the Watchdog that's placed above the master, the left-hand side just notices that the master is no longer responding. How it does that is it keeps sending pings every few time periods of nanoseconds. And when it realizes that it's not responding, it will tell the other Watchdogs to make a decision to failover to one of those. And so a quick look at what you'll have to do to configure such a system is first of all make sure the master slave mode in PG Pool is turned on. And the replication mode is set to stream by default that is set to slownie. And make sure your failover command is in place. In this case it's just a simple bash script that tells it to failover to touch the trigger file. Give it the IPs of the databases that are masters and slaves. The first one will always point to the master. The subsequent ones will be the replicas. For Watchdog, turn it on by default. It's not turned on. And give each respective Watchdog's IP to it and tell it what it has to use to make sure the database is responding. In this case it's just select one from Postgres. And if everything goes well, once you've initialized it, this is the log output of a Watchdog that's on a replica. So the first few lines you can see it realizes it's a standby. And then it finds out which one is the master node. And then it knows that two Watchdog nodes are running right now. One thing you should keep in mind is setting up elastic IP. So you don't want to change the IP that your app points to to interact with the database every time you're performing a failover. And the way you avoid that in AWS is using elastic IPs, which is a fixed IP that always points to the current master. It doesn't matter which actual box it is. And so this is a Terraform snippet to do that, just a one line command. And there you go. You have an elastic IP set up. Finally, avoid a disaster at all costs. If you're the person who deals with the Postgres database on a day-to-day basis, you should be the one to automate it, not somebody else. Because even though you might not be an automation expert, you're a Postgres expert, you know how the tasks need to interact with each other, how things can go wrong. So you're the best person for the job. Do it yourself. And finally, a few useful links, docs for Ansible and Terraform, MyRepository, and AnsibleGalaxy, which is a collection of roles and modules. And questions. So Watchdog, yes, it does talk to PGPool, but eventually if PGPool tells it that I'm not working, it's going to help you failover. It does help you with automated failover. It was with Watchdog, but I will have to look into that. That's correct. Yes, that's correct. So if anything goes wrong, if PGPool stops responding, if PGPool goes wrong, then Watchdog is going to make sure that it has a talking PGPool. Except for all the Terraform stuff that I talked about, because everything that I talked related to Terraform was related to EC2. But everything that I talked about Ansible, which is Postgres automation, you're correct. Yes, yes. No, load balance is not on. Any other questions?