 All right, well, it's 4.30. We'll get started here. Thanks everyone for being here. My name's David Hollenberger, and we're going to talk a little bit about deploying and managing Postgres QL environments using Ansible. I know it's late in the day, it's the last session. Thanks for hanging in there, and it's really been a great conference. I've really enjoyed it. So a little bit about myself. I'm a DBA. I work at Crew, and we have been managing Postgres environments for maybe two or three years now, so we're still relatively new. It's been a great learning experience and I really enjoy Postgres in general. It's just a really good platform. So an agenda, what I'd like to cover is first, why we need Ansible, why we're using it, how it helps us, and then a little bit about just some of the introduction to Ansible. We'll call it Ansible 101. And then look at some more practical examples about how Ansible can help you configure and manage Postgres QL and some extra utilities that are available. So a little background. Like I said, we're fairly new to Postgres. Me and my team of the DBAs, we have traditionally managed Oracle databases. But as we have wanted to start migrating maybe some applications over to Postgres, as well as we've started using AWS for more and more of our web apps or mobile apps that we run in-house, that Postgres really became a good option. We've really enjoyed the simplicity around setting up and managing Postgres compared to what we've been used to with more of the enterprise type databases. It's really a breath of fresh air. We started out very simple. Single instance database running on-premise, and life is good. There's not a whole lot of configuration there. We had our manually configured database. We had shared documentation and some scripts to kind of provision and get everything up and running. But of course, like most environments, complexity increases, requirements increase. So we started looking at streaming replication using Master Standby and also looking at Barman to help manage the backup and recovery situations and scenarios that we wanted to really just ensure that we have a stable and available platform. And again, thanks to really a lot of the simplicity around Postgres, it wasn't terribly difficult to really maintain this type of environment, manually provisioning, copying and pasting scripts and commands. It wasn't too bad. But as we started looking at even adding more layers to our infrastructure, looking at PG Bouncer for session pooling and simple load balancing as well as not just we don't need just one Postgres cluster anymore, but there's multiple because more and more requests come in. Our infrastructure in Amazon is growing rapidly. We were quickly coming to the point where we were realizing that just this manual provisioning was going to start just bogging down, being able to handle additional requests or look at other utilities that are really one of the key components to Postgres is being able to try out all this other great utilities and extensions and resources that you have as part of this open source environment, especially when you start looking at it's not just a single environment, but we've got dev and staging and production and lab environments and local environments. When you look at the operations side and just the overhead of provisioning, installing, and managing this as well as troubleshooting things and ensuring that the system in dev is at a consistent state and the same settings have been applied that are in our production database and knowing that things are consistent across the board, it really becomes challenging. And when you start looking at maybe troubleshooting an issue or adding a new utility or something, you start looking at the documentation and you realize, man, this has become more complex and I guess I realize one component in and of itself is fairly simple, but when you start adding everything else, it does become more complex and you'd really like. So we started looking at some options and there's a lot of options out there as far as configuration management utilities to help with managing this environment. We've already heard earlier today a great talk about Chef and Puppet and those work really well and we evaluated those for a period of time, but my team, we are not from a development background, so we really felt like learning another language or managing some sort of central server was a little more work than we wanted to do, at least for just testing something out and getting started. So we ran across Ansible and Ansible is an open source configuration management utility. We were drawn to it immediately just by the simplicity that it gave us as far as being able to define our infrastructure and code. And so as we looked at why we ended up choosing Ansible, like I said, there's a lot of other options. Chef and Puppet and Saltstack and many, many others and they all really do a good job. I wouldn't say there's really a wrong choice, but there's a few things that at least in our case made Ansible kind of stand out from the crowd. First, it was easy to get started. Like I said, we don't have a deep programming background on the DBA team. They were primarily from more of an operations background. And so being able to kind of read the Ansible plays and the scripts that you create and understand it and even be able to hand it off to someone else who may not have a lot of experience in it either, but have them be able to read it and at least at a high level kind of understand what's going on was really great. So Ansible is based on YAML. It's a markdown language in JINJA. So instead of a programming language, it's a markdown language that just organizes it very clearly, I think, and it makes it easy to understand. It also adds in, as you write your scripts in Ansible, your documentation is kind of built into these YAML files. And JINJA is used for templating configuration files. It adds in just an ability to dynamically create a lot of config files that otherwise would be a lot of, instead of statically defining IPs or other settings, it's able to create them using the variables that are provided from Ansible. So like I said, Ansible is also open source. This is another great thing about just being in the open source community, like there's a great community around it. They're actively developing it, much like Postgres. A lot of people using it to do really neat things. And also Ansible is agentless. So we don't have to have an agent running on the instances that we're trying to manage. It uses SSH to connect into those instances and manage them. So another concept in Ansible that wasn't really a reason why we picked it, but we kind of learned about it afterwards. And it's really, I think, a key concept to understand, especially when managing databases, is that you really don't want to screw up. They're not just instances you can throw away and recreate because there's real data on them, but this idea of item potency. Item potency comes from a mathematical or computer science term. But Ansible kind of describes it as you want to define the state that you want your system to be in, not necessarily the steps along the way. And what Ansible comes with is the knowledge to know if you need to make any changes to get to this state. If you don't need to make changes, Ansible isn't going to run any extra commands or any extra tasks that don't need to be run. You're not going to end up with duplicated databases or users if you rerun the same playbook again and again. It's just going to say, well, that database or that user's already there. It looks good. It's at the state that you defined it, and we can kind of move on. So before we look at some of the Ansible 101, just to kind of show you that idea of item potency, we're going to run a simple playbook. And if the wireless holds up, we should be able to see. And this is the actual commands that are running on the right. What it's going to do is install Postgres, initialize a database cluster, create a database and a user as well. And one thing to notice is that it's actually running against three instances, and just the highlight that Ansible can run things in parallel, but it's also going to ensure that every task is complete before going on to the next one. This is another key thing to make sure, especially with databases and setting up any kind of streaming replication or looking at PG Bouncer or Barman kind of things. You want to ensure that any prerequisite tasks or that just the order is preserved. So you'll see in the output a lot of things have changed. And just to highlight item potency a little bit, if we rerun it again, you'll see everything's just OK. So when that task is run, Ansible is seeing that, well, when we look to create the database, it's already there. We don't need to do any more work. So you're not going to drop and recreate anything. And this is just something that's provided out of the box with Ansible. So let's look at a few kind of core concepts of Ansible. And first off, is anyone here using Ansible right now? Just show of hands? Great. So some idea of what it is. And that's great. So hopefully, this will be a good foundation for those of you who have not heard of it or have not used it to kind of at least have a general understanding of the basics. So Ansible uses an inventory to define all your hosts that you're managing. An inventory actually contains you can set groups of hosts highlighted here in blue. So we have a group called Postgres. And there's two instances there, a bar man group and a PG Bouncer group. And then within those groups, we can define the hosts. And then Ansible uses modules to actually do the work that you want it to do. These are included with Ansible out of the box. Last time I checked, there were 523 modules. And they do a range of tasks, anything from creating EC2 instances at AWS or load balancers or managing Cisco network hardware, a lot of system utilities to manage services or other things. And there's also a number of Postgres modules. Here are a few that come with Ansible. You can do things like create and manage your databases, set up privileges or users, and some other ones that I think are pretty helpful. Template, line and file, shell and service. And we'll kind of look at those and how they can be used when you're configuring Postgres. So here's just a simple example, kind of like the previous demo, just to give you an idea of how the modules would be laid out in more of a YAML format. And again, that top line of every module, that name, I think that's a key part of being able to document consistently. So if you have that line in there, you kind of know at a high level what's going on, which is great just to be able to read and document fully what's going on with your Ansible scripts. So Ansible organizes these modules and the things that you actually want to do into this playbook. And they kind of use this sports analogy to define a playbook. It's not just an individual person or an individual task. It's the orchestration of all these tasks and modules that go into defining a playbook. So it's defined in the YAML format, denoted by the three dashes at the top. Playbooks contain one or more plays. So this is highlighted here. This is a single play where we're saying the hosts that we want to run against are called PG. The connection in SSH, which is the default. And then within that play, there can be a series of tasks that you want to run against those hosts that you've defined. Ansible also uses variables. Variables are great for being able to account for differences between different hosts. So not every host that you manage has the same version of Postgres or has different types of software that you want to run on it or different extensions or different databases even. So a great way to define variables, and one of the many ways you can define variables are in your Ansible inventory. So in this example, we have, within our inventory directory structure, these are called group variables. So we've named the file to match the group name of the hosts. And so we can have a group called cluster A and a group called cluster B. And cluster A is assigned PG cluster value of A is running version 9.4. And cluster B is running 9.5. So this is a way where you can run the same set of commands against all these hosts. And you know that you're going to install the correct version of Postgres and you're not going to mix up different variables because Ansible is able to keep track of which host belongs to which group. And it's a really powerful way to define the differences between different hosts. So you can also set variables in your playbook itself in a vars section. And this variable will be in scope for this entire playbook. Another really great thing with Ansible is that by default it gathers what they call system facts. And this is just a short snippet here. But there's a lot of things that returns anything from network configuration to the available memory on the host, the distribution of the operating system and the version. And these are all at a host level. So you can check for each host that you're running, even if it's different operating systems, you can know and it can account for those differences. So one way we can use variables, when we look at being able to set different memory configurations, we can set by, we can use the Ansible mem total to calculate the value of different parameters that we want to set. And we've used PG2 and it's kind of a baseline for this. It seems to be a good starting point at least. And also, if you use a variable and a task, you can use this curly bracket kind of format to use that variable. So this is just another example of using that PG version to, so we know exactly which version of Postgres we're managing. So another concept Ansible recommends that people use is this idea of roles. Roles are a logical grouping of tasks that encourages reuse. So you don't have playbooks with just a long list of tasks, but you can kind of encapsulate like a group of tasks that kind of all do one thing within a role. You can also set like default variables for the role and it could have its own set of files or templates that you can use when configuring your systems. So this is an example of a playbook that uses roles. So you can see it's a lot cleaner than just a long list of tasks, but also like that common role, you might be wanting to run that on every single instance that you manage. Setting security settings or updating kernels or other software packages that you need on your systems. But that Postgres role you could, you probably just want that to run on hosts that you want to set Postgres up on. So it's just a way to have just a simpler layout on your playbook. And one thing, just a note, I'm using Vagrant for all the local demos on my laptop. If you're not familiar with Vagrant, it's a great way to manage local VMs on your laptop. It can use a number of different hypervisors. I'm using VirtualBox, but you can use VMware and a bunch of others. It works great with Ansible. It can actually manage your inventory for you depending on how you've defined your Vagrant file. I'm gonna take a quick look at this Vagrant environment here. So just a Vagrant status and you can get a list of the different VMs that are running. Vagrant also makes it really easy to SSH to these hosts without setting up any kind of extra SSH keys. It'll create a local Vagrant user. It just simplifies the whole process of managing like a small set of VMs on your laptop. So let's look a little deeper, kind of past the basics. And this will be just some quick examples, but maybe a little more practical about how we've decided to use Ansible to configure Postgres. So one thing when you set up Postgres, you've got to do something with Postgresql.conf. There's a number of variables that if you change them, you'll need to either restart or reload Postgres to actually have those changes go into effect. So to be able to handle that, we use this line in file module from Ansible. So this is going to look at this variable down here, this Postgresql.conf restart variable. So these are all the items that if we change those, we know that we'll need to restart Postgres for that to go into effect. This line in item is gonna loop through those variables. And if there's a reg xp part of that variable, if it matches, if the line matches that regular expression, then it's going to ensure that the line is what we set the value to be in the variable itself. And one thing to notice, part of Ansible and the idea of idempotency as well, is that if a change has been made, Ansible will know that a change has been made, obviously, and it will notify a special kind of task called a handler. And the handlers are only run when a change has been made or when a module has made a change. If no change has been made, it's not gonna call that handler. So it won't actually run like the restart or reload of Postgres. So this is the handler in a role. You can set it in its own directory and Ansible will find it automatically. But again, it's a really powerful way to know, if nothing's changed, then we don't need to do any extra work. We don't need additional downtime. So looking into streaming replication. The one, there's a few things, a lot of other things that you need to do to get this set up. But just for the sake of time, we're gonna look at just this one way that Ansible really helps when running long running commands. So if you're running a PG-based backup command, depending on the size of your database, it could take hours or even longer. But Ansible has a great way for long running tasks to try to avoid SSH timeouts. You can set async and pull. And what it'll do is async will set the amount of seconds that you want to give this task and allow it to run for. And pull will set, we'll just check in that many, every 30 seconds in this example, it'll check in on the task, get a status update on it and say, okay, it's still running or hey, this failed or it's finished and let's move on to the next thing. So it's really nice as a way to kind of avoid any SSH timeout or kind of temporary network connection issue from the machine that you're running on to the host. So looking at now, Barman. Barman is a great way to manage your backups and recovery scenarios. And it's very easy to use when you're configuring it with Ansible. For Barman, we use a template module with Ansible to template out the various files that it needs to be able to know about the different instances and hosts that we want to manage. In this example, we're actually creating a config file for every cluster that we want to, every Postgres cluster that we want to manage. And this is gonna start using the system facts that have been gathered. So we don't need to go to each system, find the IP address of the master instance and then copy it and write it down and manually set these files up anymore. This is actually gonna loop through every host that we've defined to be a master instance and run this template file. And this is what the template file looks like. This is in the ginger to templating format. So you can see we're not naming, Barman doesn't know about the actual host name. We're just gonna name it the name of the cluster. So then if we do some sort of failover scenario, we won't have a new host that Barman then doesn't have any backups for. And also you'll see that we're taking advantage of this default IP address and using this host vars item when you loop through like a list of hosts with Ansible, you can actually grab information about other hosts from another host. So it's kind of, they call it magic variables I think in Ansible language, but it's a really powerful way to be able to say, well, we already know the IP addresses of all the other hosts. So let's be able to use them and create dynamic templates on the host we're managing. So PG Bouncer, like Barman, it is not terribly complex to set up. There's a number of configuration files. And again, the template module with Ansible works really well for this. One thing that PG Bouncer needs, just one of the configuration files, there's a few others, but it needs a list of usernames and passwords that PG Bouncer uses to connect to those other Postgres instances. So in this example, and this is a little more advanced example of just the Jinja templating format, we're able to loop through all the hosts in this PG Master group. And then for each host in that group, we already have defined every database with a username and an MD5 password hash. So the end result is, when you look at the user list file in PG Bouncer, that we have a list of usernames and passwords that are able to connect to your different hosts. So let's look, we'll do a demo here.