Course info
Apr 5, 2016
2h 35m

Developers often inherit the job of Database Administrator as their companies grow, and the database needs someone to look after it. As a new DBA, you need to quickly understand how to secure your server, optimize performance, and safeguard your data. The modules of this course, PostgreSQL Playbook for Developer DBAs, stand alone and do not require you to go in order; each one will teach you how to do a different kind of task in PostgreSQL that you'll need to know as a DBA. You'll cover tasks in areas such as hardware and security, backing up and restoring your data, as well as fixing slow queries by adding indexes, and more. Finally, you'll learn about three hosted solutions for database management. By the end of this course, you will have gained several skills that you'll be able to use to perform everyday tasks on the job as a DBA.

About the author
About the author

Rob Conery co-founded Tekpub and created This Developer's Life. He is an author, speaker, and sometimes a little bit opinionated.

More from the author
Electron Playbook
1h 38m
Mar 2, 2016
More courses by Rob Conery
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hi everyone. My name is Rob Conery and welcome to the PostgreSQL Playbook for Developer DBAs. Pluralsight's Playbook Series is a little bit different from typical courses that you find here at Pluralsight. Instead of focusing on concepts and progressively building up a demo, in this Playbook course we'll focus on individual tasks. Each module is designed to stand on its own but can also be watched as a whole. In this playbook you'll learn the essential tasks you'll need to know as a brand new database administrator otherwise known as a DBA. Being a DBA is one of the most rewarding jobs in the tech industry if you know some essential skills. Your boss and coworkers will think you're a miracle worker when you speed things up by orders of magnitude simply by applying some much needed, well targeted indexing. Perhaps you can save a few lines of code while at the same time securing your projects data by wrapping a few commands into a function. This job can be exceedingly fun. You just need to learn a few skills first. That's where this course comes in. We'll start you off in your new role by discussing issues such as hardware, security, backing up and restoring. You'll benchmark your server and teach it how to tell you what's wrong and how you can fix it. You'll learn how to spot troublesome queries before they drag your database down. Even if you're not planning on becoming a developer DBA these skills are essential for anyone working with PostgreSQL. So I hope you join me and watch the PostgreSQL Playbook for Developer DBAs.

Optimizing Your Hardware
You've just inherited the job as the go-to database person. Congratulations. You watched a course here at Pluralsight, and the CEO of your company thought you would be the perfect candidate to make the database fast. And you know what? You can. Let's do this. The first thing to consider, now that you're the new DBA, is where your hardware is located. Believe it or not, in 2016, quite a few companies put their database on a server under the desk or in a closet. We'll discuss this one first, as it's likely the most important. Next, we'll discuss operating systems and hardware. These choices are crucial for squeezing the best performance from your server. And finally, I'm sure this will be no surprise, opting for a super-fast VM, maintained by someone else, is often the best choice. And I'll show you how to get set up quickly with DigitalOcean.

Locking Things Down
In your new existence as database magistrate, you now have two priorities above all others. Performance of your database and security of your database, both of those are very important. It's kind of hard to say which one's more important than the other. Obviously, the security of your data is incredibly important, but if you don't have a performant database, then your application tends to fail, so let's just put those on even footing, shall we? So here's a fun little test. If you go to GitHub. com, Search, and you do a search for something like, oh, I don't know, discountasp, that is a service provider for ASP. NET and ASP applications, and you run this and you take a look at the code, oh, look what you get to find. All kinds of fun things. Passwords, hooray! You wouldn't believe how many passwords, "Zimbabwe", there's one. Well, keep going through here, you'll find a bunch of people and access (chuckles) to their DiscountASP databases. What I'm trying to get around to saying is, it's surprising how many people put data access certificates, or whatever you want to call them, code, into their repositories. Into public repositories, no doubt. That's unforgivable in itself, of course, publicly, but also privately. You've got a private repo and you have other developers accessing that repo, they're going to have access to your production system. This is obviously unforgivable stuff, quick way to get fired, and we don't want to get fired, do we? So let's make sure that Postgres is locked down. That's what we're going to focus on in this module. Right now, I need to make sure that no one logs in with root, and that our application has its own account. Let's do that now.

Backing up and Restoring
Your server is set up and secure, and now we need to make sure that the data is safe and cared for. In this module, we'll move the data from our old machine to our new one up at DigitalOcean. And then we'll set up nightly backups with cron, a Linux utility that will run any command you give it on a repeated schedule. Finally, we'll have a look at storing our backups up at Amazon S3.

Tuning Your Server
In this module, we're going to tune our server, upgrading from the somewhat hobble default installation. Postgres doesn't know what kind of machine you have when you install it, so it has some conservative settings initially. They are easy to fix, and that's what we're going to do in this module. What I'm going to show you is a reasonable first step. In fact, it's quite common. And often, this will be all you're going to need. Upping your cache size, optimizing your write speeds, that is at the core of it. I encourage you to explore a little bit more if you want, but this reasonable first step will get you moving right along. When it comes to performance, cache is king. It's likely that your applications will be implementing some caching, but if you've implemented your own data caching, don't bother. Postgres will almost certainly do it better than you, and you should let it. Finally, I hate to spoil the surprise, but if you run pgbench in your tuned system, it's likely you won't see that much of a benefit. In fact, a lot of people complain that it seems actually worse. This has to do with the way pgbench works. If you want to measure the performance gains exactly, you should try and use your own scripts before and after. But just know, the tweaks that you're about to see will indeed help. But unfortunately, it's going to take the long run for you to notice it.

Fixing Slow Queries
In this module we'll speed up some slow queries, choosing our indexes carefully to squeeze the biggest wins possible. We've tweaked our service configuration, made sure we have a box big enough to run our massive system, and we've even run some benchmarks. Woo hoo! Our boss has been patiently waiting for us to make Slow Queries faster and this is where we're going to make our mark. In the last module we used one of Craig Kerstiens' queries to have a look at our database to see what's running the most and for how long. This is his blog post right here in which he details that query right there at the very top. That query's available for you in the downloads. We'll use this query once again as a starting point for optimizing the tables that need it the most. Let's get to it.

Hosted Solutions
In this final module we'll take a look at popular hosting solutions, three to be specific. That will host your instance of Postgres so you don't have to worry about maintaining a server, which can be scary. The first one we'll take a look at is Amazon. With AWS RDS or Relational Database Services, they'll host your Postgres instance for you and allow you to scale it as you need in a liquid fashion All hosted and maintained on their hardware. The next one is Heroku Postgres where Craig Kersteins works. This service is ideal if you host your application up at Heroku, which is a great service and it's simple to use. It will also do liquid scaling, just like Amazon. And finally we'll take a look at Compose. io, who used to be MongoHQ. They create and maintain your database servers up at Amazon and give you a nice admin interface for each one. They'll do Redis, MongoDB, RethinkDB and Elasticsearch. So if you have more than one data need, they're a great choice. Alright, we have a few services to look at, let's get started.