Spinning Up a PostgreSQL Database on Azure Using Bash

I’ve spent the last 3 weeks in a bit of a daze, getting to know Microsoft once again, after all these years. If you didn’t now: I managed to get my job back. That’s a longer story, one that I’ll talk about at a later time. Today, I wanted to tell you about one of my “focus areas”, which is helping to improve Microsoft’s hosted PostgreSQL offering. Along the way I get to learn what Azure is and how it works!

Note: if you’re curious about how this will effect the completion of The Imposter’s Handbook, Season 2 – it won’t. I took this into account when I set the schedule many months ago. I write on weekends and nights, which is fun for me, so nothing changes.

There are many reasons I took this job. The primary one is that my boss is someone I admire and trust (Tim Heuer). His boss is one of my personal heroes (Chad Fowler) and the team I work with are people I have tremendous respect for. In the last 3 weeks I’ve been reminded just how much I used to love working here, and then some.

For instance: in my interview with Chad, he point-blank asked me:

So, how well do you know Azure?

I laughed in response. It’s not that I dislike Azure – I’m just a simple DigitalOcean VM person. I have my scripts and I’m not afraid to use them! Anyway – my answer was simple: “I used it 9 or so years ago, got confused, and haven’t used it since”.

His reply is why I took this job:

Perfect.

Since getting (re)hired, I’ve been digging into the documentation and pushing Azure’s PostgreSQL offering as hard as I can. There’s a lot I could write about – but let’s stay focused on simple deployment and why you would want to do this in the first place.

Why Azure, and Why PostgreSQL?

I’m still learning what Azure is and how it differentiates from other offerings out there (Heroku, AWS, Google Cloud etc.) so I’m going to sidestep that discussion (sorry – best if I know what I’m talking about). If you’re already on Azure, however, and are using a database system like SQL Server and want to know what else is out there – this post might be helpful.

You can spin up a PostgreSQL service in no time, so let’s get to it. You could use the GUI, but where’s the fun in that! Let’s put our bash skills to the test.

If you want to know more about what we’re going to do, you can read over the tutorial that I followed. I’ll show you the entire script, and then step through it line by line.

The Deployment Script

Here’s what we’re going to do:

  • Setup an Azure Database for PostgreSQL. This is a bit of an odd name because you actually get an entire service that you can create multiple databases with, but I don’t have any influence over that.
  • Create a firewall rule so that we can access our database
  • Setup credentials that are reasonably secure
  • Push the Northwind database. Yes, Northwind :trollface:.

I know, I know! But it seems so fitting doesn’t it? Kind of throwback Microsofty… like me?

Here’s the script that I used to deploy this database:

When you run this script (./deploy.sh), you will be asked a single question and then you can sit back and watch Northind gloriously come back to life:

Let’s step through it.

Step 1: Logging in and Setting Up

To run that script effectively, you need to

  1. install the Azure CLI. This is how we’re going to tell Azure what to do, as opposed to using the visual portal.
  2. Have an Azure account, which you can create for free.
  3. Login using az login with your Azure credentials.
  4. Download and unzip the SQL script. This is good old Northwind, but tweaked to work nicely with PostgreSQL.

Create a directory somewhere on your computer, and save that shell script in there along with the northwind.sql SQL file. I’m calling my script file deploy.sh and I’m assuming you can run bash. If you’re on Windows, you’re on your own as I don’t know how that works.

Next, we need to make the script file executable as bash won’t arbitrarily execute files unless they’re explicitly marked that way. To do that, open your terminal, navigate to your new directory and type chmod +x deploy.sh (assuming you named your file the same as me).

Your’re ready to go! If you have an Azure account and have done all of these things, you can execute this script by loading it directly into your shell with ./deploy.sh. If you want to know more, read on!

Step 2: Setting Up Variables, Reading Our IP

In order to spin up PostgreSQL on Azure, we need to tell it a few things, namely:

  • What region we want the server to be in
  • The Resource Group we want it to be a part of
  • The user name and password for the system

A Resource Group in Azure is simply a way to logically put things in buckets. Azure has a ton of moving parts, all of them with cryptic names (at times). Putting them into a logical bucket helps keep things organized. It will also help when we’re done here, because we’ll delete the Resource Group as opposed to each thing we create.

To be honest, I’m not a big fan of letting the user define the system administrator account and password. Developers tend to be lazy and will pick the simplest possible password just to try things out. Fast forward 8 years and that “try out” has become a production machine, with the same password.

The first lines of our script address that:

USER=admin_$RANDOM #set this to whatever you like but it's not something that should be easy
PASS=$(uuidgen) #Again - whatever you like but keep it safe! Better to make it random
LOCATION=westus
SERVERNAME=northwind-$RANDOM #this has to be unique across azure

#resource group
echo "Specify a Resource Group:"
read RG

You can use $(uuidgen) for the admin name if you want, but this will do just fine for testing things out. I’m also using $RANDOM to create a random integer that I can tack on to the name of the server. It needs to be unique across Azure as you’re given a URL to access it.

Next, rather than having to hardcode your Resource Group (which is just fine), you’re asked to enter it in an input. I wanted to make this script as reusable as possible, so that’s what I chose. You can remove that by hardcoding your choice, if you want.

Next is something I’m quite proud of: figuring out your external IP address. You can hardcode this if you want, but I’m lazy (have I mentioned that?) so I’m just using curl to go out to ipinfo.io and then setting it to an IP variable:

echo "Guessing your external IP address from ipinfo.io"
IP=$(curl -s ipinfo.io/ip)
echo "Your IP is $IP"

Step 3: Creating Azure Resources

We need to tell Azure the size of our database using a “SKU” setting. If you use the web-based portal, there’s a lot of clicking involved to figure out the SKU you need (how many cores, disk space, how much RAM). These PostgreSQL servers start out pretty beefy (relative to other hosted offerings) and I’m still getting the hang of the size/pricing bits so once again I’m going to sidestep that discussion. For now, the cheapest, most basic starter service that you get is 5G of storage, a single virtual core and 2G of RAM, identified by the SKU B_Gen4_1.

That SKU is derived using a naming scheme: PricingTier_ComputeGeneration_Cores. I don’t know what those things mean yet, but I do know the smallest is the cheapest. If you want to go higher, you can refer to my comments:

# The sku-name parameter value follows the convention 
# {pricing tier}_{compute generation}_{vCores} as in the examples below:
# --sku-name B_Gen4_2 maps to Basic, Gen 4, and 2 vCores.
# --sku-name GP_Gen5_32 maps to General Purpose, Gen 5, and 32 vCores.
# --sku-name MO_Gen5_2 maps to Memory Optimized, Gen 5, and 2 vCores.
SKU=B_Gen4_1 #this is the cheapest one

Now that we have that, let’s create our resources. We’ll create a Resource Group and a Server:

az group create --location $LOCATION --name $RG

echo "Spinning up PostgreSQL $SERVERNAME in group $RG Admin is $USER"

# Create the PostgreSQL service
az postgres server create --resource-group $RG \
    --name $SERVERNAME  --location $LOCATION --admin-user $USER \
    --admin-password $PASS --sku-name $SKU --version 10.0

We create the Resource Group first, then the server, passing in all of our arguments.

Step 4: Accessing the Service

Once the service is created we’ll want to access it so we can create the Northwind database and then send in some data. To do that, we need to poke a hole in the firewall. This is where our IP address will be used:

# Open up the firewall so we can access
echo "Popping a hole in firewall for IP address $IP (that's you)"
az postgres server firewall-rule create --resource-group $RG \
        --server $SERVERNAME --name AllowMyIP \
        --start-ip-address $IP --end-ip-address $IP

I named this rule AllowMyIP so feel free to change it. It’s specific to your service.

Once that’s done, we need to login so we can create our database. I’ll be using psql to do that (the PostgreSQL CLI), passing it the connection information to our new server in the form of a URL. I can do that because I set all of this information above, using $RANDOM and $(uuidgen):

echo "Your connection string is postgres://$USER@$SERVERNAME:$PASS@$SERVERNAME.postgres.database.azure.com/postgres"
echo "Creating the Northwind database..."
psql "postgres://$USER%40$SERVERNAME:$PASS@$SERVERNAME.postgres.database.azure.com/postgres" -c "CREATE DATABASE northwind;"

Your new PostgreSQL server is given a unique URL when it’s created, with all of the ports closed off to the external world (which is good). We can access it, however, because we opened the default port (5432) for our IP address alone.

It’s nice to let yourself know the connection information in case you want to store it securely in 1Password or something (please don’t write it down). Finally, I’m passing the CREATE DATABASE command using -c.

Now that we have a database, let’s push Northwind to it! We just need to reconnect to the newly-created database, and send in our SQL file using -f:

echo "Connecting... and loading up Northwind..."
psql "postgres://$USER%40$SERVERNAME:$PASS@$SERVERNAME.postgres.database.azure.com/northwind" -f northwind.sql
echo "....."
echo "You can now connect to the server by entering this command: "
echo "psql postgres://$USER%40$SERVERNAME:$PASS@$SERVERNAME.postgres.database.azure.com/northwind"

To connect and play around, just copy/paste that last command and you’re off and running, just like that. Or, you can modify the script here to do it for you.

Step 5: Destruction

When you’re done playing around, you can use a simple command to drop everything you’ve just created:

az group delete --name=[resource group]

You can enter this command in the shell and everything will go away (make sure you enter the right name!). You can also put this into a reusable script:

#!/bin/bash

echo "Confirm by entering the Resource Group."
echo "This will wipe out everything - you sure?"
read RG

az group delete --name=$RG

Or you can hard code it. Either way, running this will drop everything we just did.

That Was Fun. And Surprising.

I made a quip to my Microsoft friends 3 or so years ago:

When you guys support PostgreSQL as a first-class citizen, that’s when I’ll believe your ‘big ship turning’ mantra.

I used to ride my bike out to Redmond and meet them after work at PostDoc (a local brewery). One of them got right back in my face:

Right, so when we do you’ll be back to help us with it, right Rob?

We had a good laugh over that. “Yep! Make it happen and I’ll be there”.

Here I am. I am surprised and, more than that, having a lot of fun.

Tagged as: