PostgreSQL – Local on Docker and Cloud on Azure

PostgreSQL is one of the great options at developers’ disposal when implementing a object-relational database. It is Open Source and has been in the scenery for over 35 years. Its official website is PostgreSQL: The world’s most advanced open source database.

When I’m using a database usually I have a 2-step approach, first testing everything locally where it is easy to deploy and then move to the cloud, with a production setup. In this post, I will not touch on all settings for configuring and securing the database in the cloud, but I will show how to get started.

For the local development, usually I opt to go with an instance on Docker, given it is really easy to test, tear down and re-deploy the environment, fresh and ready to go.

Docker installation on Windows with Docker Desktop is really simple, which I mention in this post Windows 11 – Docker Desktop with engine running on WSL2 and Kubernetes.

Starting an instance of PostgreSQL on it is even easier.

Open a terminal such as Windows Terminal and make it happen with just one command:

docker run --name postgresql -e POSTGRES_PASSWORD=<REPLACE_WITH_YOUR_OWN_PASSWORD> -p 5432:5432 -d postgres

If it is the first time you run the command, you will not have the image locally so Docker will download the image and then create and run your instance.

If you open the Docker Desktop Dashboard, you should see your container with Status of Running.

A very nice way of interacting with your PostgreSQL database is to use pgAdmin4, available as part of the official installer. Go back to PostgreSQL website and click in Download.

If you are using Windows like me, you will click on Windows OS:

Then, Download the installer.

I’m using the latest version available at this time, 15.2.

Open the installer and allow for it to make changes to your device. In the main window, click Next.

For installation directory, I use the default and Next.

Since I use the server on Docker, I only leave pgAdmin4 and Command Line Tools checked, in Select components window and then, Next.

In Pre Installation Summary and Ready to Install windows, Next.

It will take some time to install, then click in Finish.

To connect to your local instance, open pgAdmin4. Since we usually store the database credentials in the configuration, pgAdmin4 will request a Master Password to be configured. Provide one and click OK.

Then, right-click in Servers, Register and Server.

In General tab, for Name we can use localhost.

In Connection tab, we fill out Host, Port, Maintenance database, Username, Password and optionally select Save password, then Save.

The server should be accessible and displayed in left menu (Browser). The tool is very intuitive. To create a new table, we can expand the different levels as hgihlighted in the image below.

We can, then, use Tables, Create, Table.

In the General tab, we give the table a Name and move to Columns tab.

Then we include our fields, data types and options.

Before we click in Save to create the table, we can inspect the SQL generated in the SQL tab, which can be used for code first deployments, a subject for another time.

The table will then be displayed in the Browser menu.

For the cloud deployment, I have used many options before, both serverless or manual server setup in a VM, in AWS, GCP, Digital Ocean, Linode, etc.

Nowadays, the most practical one, given I’m using other services which has been working pretty well for me, was to host my services in Azure.

The option I use for the PostgreSQL database is the Azure Database for PostgreSQL flexible server.

In this post I’m adding the steps on how to create the server manually using the portal.

If you don’t have an account or subscription yet you can apply for one at Azure website.

If you already have your account setup, navigate to Azure Portal and click in Create a resource.

Then, navigate to Databases and click in Create Azure Database for PostgreSQL Flexible Server.

In the first tab, Basics, we select our subscription, a previously create or new resource group, server name, region (usually East US or East US 2 for example have a bit better cost), PostgreSQL version which, currently, latest available is 14, then, for simple projects, we can start with Production small/medium as workload type.

It is always a good practive to start small and do some profiling/benchmarking in your infrastructure to not over pay for the services.

Now, we scroll down the page and can use Configure server in Compute + storage option to select best resource size.

If on a budget or are just trying a new idea or creating a proof-of-concept, Burstable instances are the best in cost savings offerings. Settings really depend on the project type and size. Pick what makes sense for you. Below example is the smallest and lowest cost instance. So we choose and click Save.

After the compute size is selected, we can continue with username and password configuration.

In this example I’m using PostgreSQL authentication only to be similar to my local instance, but for production workloads I usually use mix of Azure AD and PostgreSQL authentication.

After that, we can move into the networking tab.

In Networking, for the basic setup for the demo it is ok to use public access, but for a production, on more secure deployments, we would like to use a Private access network and allow access to the database only within the Azure private subnet, not directly exposed to public internet IP addresses.

Even in the current configuration we still have one more layer of protection, the Firewall.

Always follow the best architecture practices. There’s extensive guidance in Azure Architecture Center – Azure Architecture Center | Microsoft Learn so I will not cover the advanced details in this post.

To be able to do a quick testing, I will add my current client IP address to the allowed list, which will automatically create a Firewall rule.

Then we can move into security.

You can customize the encryption settings by selecting customer-managed key, but I will use Service-managed key for simplicity, which will allow Azure to auto-generate the key for us. Tag is self explanatory, so I will skip it. Then we can click in Review + create.