Azure Database for PostgreSQL Deployment [Step-By-Step]

Getting started with Azure Database for PostgreSQL Deployment

PostgreSQL is a powerful, open-source object-relational database system. PostgreSQL (pronounced as post-gress-Q-L) is an open-source relational database management system (DBMS) developed by a worldwide team of volunteers.

Azure offers PostgreSQL PaaS service with a Single Server and Flexible server. Azure Database for PostgreSQL has managed service provided use to run, manage and scale as per need. Its managed database service provided by azure to focuses on business and less administration for databases.

 

Prerequisite

• Azure Subscription
• Basic understanding of PostgreSQL
• PSQL installed on the machine

 

Step 01: - Azure Databases for PostgreSQL

Azure offers multiple options for PostgreSQL according to customer requirements. Currently, we have a Flexible server, a Single server, and a Hyperscale (Citus) server group. In this article, we will understand Flexible Server as it fits most of the requirements before that will just have a quick overview on all options.

Flexible server

Best for production workloads that require zone resilient HA, predictable performance, maximum control, custom maintenance window, cost optimization controls, and simplified developer experience. Enterprise-ready fully managed community PostgreSQL service.

Single server

Best for existing applications already leveraging a Single Server. Enterprise-ready fully managed community PostgreSQL service with zonal high availability.

Hyperscale (Citus) server group

Best for ultra-high performance and data needs beyond 100GB.

Ideal for multi-tenant applications and real-time analytical workloads that need a sub-second response. Supports both transactional/operational workloads and hybrid transactional analytics workloads.

 

Step 02: - Deployment of Azure PostgreSQL Flexi Server

Search in the portal for Azure Database for PostgreSQL as per the below image you can see results.

 

Select the first option Azure Database for PostgreSQL flexible servers. As per the below image you can see estimated costs on the right side of the configuration.

 

Next, provide Server name for the Database Server and the Region. An interesting option here is you have the option to choose instances based on your workload, you can see in the image Production and Development.

Accordingly that you can select Compute + Storage likewise we do for VM creation.

 

If you click on Compute + Storage you can select it more granular level. also, you have the option to select storage and it can go up to 4TB.

 

As part of managed PaaS, you can get other benefits like selecting the latest available version for DB and then High availability. you can set this up later also but setting up the Admin username and Password is mandatory.

 

Next, tab we have to select the network connectivity and its recommended to keep database connectivity Private with VNet Integration.

The trade-off here is once you select the Networking method you can’t change it later. For easy accessibility we will make it Public and will show you how to access it.

 

You can add your public IP in the firewall to whitelist. It's easy for organizations to add VPN IPs to a firewall and give access to users.

Last review the changes and click on create.

 

Step 02: - Overview of the feature of Azure Database for PostgreSQL

Once your Database is deployed you can see the Overview page as per the below image.

The best option here is you can Start and Stop your PaaS DB Server. It's really cost-effective when you want to use it in Development or in a test environment.

 

Next, will see about the networking pane. You can download an SSL certificate to connect DB programmatically but as earlier discussed you can’t change the connectivity method.

In firewall rules, you can Add IP to the whitelist.

 

It provides us with the built-in connections strings to use in our application. As per the below image you can see the connection strings.

 

You can list, show, and update configuration parameters for an Azure Database for PostgreSQL server through the Azure portal as per the below image.

 

Next, as mentioned in the overview PostgreSQL managed service supported Hight Availability for your database.

 

You can enable this with a single click. But you have to pay double the price of course as it will create another copy of DB.

 

Step 03: - Connect to Azure Database for PostgreSQL

Here we can use Azure Cloud Shell to connect our database. you can use the below command to connect DB.

before that, you must check to Allow public access from any azure service within Azure to this Server. this option will whitelist azure services in the firewall.

 

You have to run the below command with your PostgreSQL Server details. and you would be able to access it as per the below image.

psql --host=<servername> --port=<port> --username=<user> --dbname=<dbname>

 

Next method we can use to connect using local client here I am using DBeaver for this. Fill required details here Hostname, Database Name, Username and Password.

 

You can Test Connection and click on Ok to save the connection details. As per the below image you can see it's connected.

 

Step 04: - How to monitor Azure Database for PostgreSQL

Monitoring the database is the key responsibility of every Database Administrator and azure makes it easy to monitor and understand the behavior of the application.

In Monitoring Pane, you have multiple options to monitor performance.  We are using Metrics to understand the behavior of our Database.

 

You can check free storage with build-in metrics.  On the basis of these metrics, you can create alerts for Storage utilization.

 

Summary

If you follow this article and the end of this, you would be able to successfully deploy and configure Azure Database for PostgreSQL Database. The Database PaaS is used to store volumes of data at a scale according to the server capacity chosen by the user company. Cost reduction is also the primary consideration for the company to move toward the managed database.

 

References

• Compare Azure Database for PostgreSQL - Single Server and Flexible Server | Microsoft Docs
• Overview of zone redundant high availability with Azure Database for PostgreSQL - Flexible Server | Microsoft Docs