Microsoft Azure provides us with various services to help us easily and quickly migrate from on-premise to the cloud without making significant changes. Therefore, in addition to the Azure SQL services, Azure provides us with data services for many popular relational database systems including MariaDB, PostgreSQL, and MySQL.
PostgreSQL is a hybrid relational-object database. Here we can store our data with custom data types with their non-relational properties and in tables with relational properties. This assists in storing geometric data such as circles, polygons, and lines and manipulating it.
PostgreSQL has its own query language called pgsql. It is a variant of the standard relational query language with additional features that helps us in writing stored procedures that run inside databases.
Azure Database for PostgreSQL: Azure provides us with a platform-as-a-service implementation of PostgreSQL in Azure with an Azure Database for PostgreSQL. This service provides us with the same administrative benefits, scalability, security, performance, and availability as the MySQL service.
Azure Database for PostgreSQL has 3 different deployment options that we may use:
- Single server: This deployment option for the Azure Database for PostgreSQL provides us with similar benefits as the Azure Database for MySQL. We may choose from the three pricing tiers: Basic, General Purpose, and memory-optimized based on our needs ( the load which we are required to support). Each of the tiers supports different memory, storage size, and number of CPUs.
- Flexible server: The flexible server deployment option for the Azure Database for PostgreSQL is a fully managed database service by Azure. It provides us with more control, server configuration customizations, and better cost optimization controls.
- Hyperscale: The hyperscaleCitus) is a deployment option available for the Azure Database for PostgreSQL that supports large database loads by scaling queries across multiple server nodes. The database is split across nodes and the data is split into chunks based on the value of sharing or partition key. We should use this deployment option when there are large database PostgreSQL deployments.
How to Create an Azure Database For PostgreSQL?
Prerequisites: You need to have an active Azure Subscription. You can avail a free 12-month subscription.
Step 1: Navigate to the Azure portal
Step 2: In the Azure Portal, select the + Create a resource option. You will see a list of resources we can provide in Azure.
Step 3: In the search type Azure Database for PostgreSQL.
Step 4: In the resulting page select create option.
Step 5: Review the different options available for the Azure Database for PostgreSQL. Choose the one that you need as per your requirements. For now, select the single server tile and select create. Decline the offer to switch to a flexible server if asked.
Step 6: You will be prompted with a create SQL database page. Enter the details :
- Subscription: Select the Azure subscription where you wish to create the resource.
- Resource group: Choose a resource group where you wish to create the resource. If you wish to create a new one click on create a new option.
- Server name: Give a unique name to your server.
- Data source: Choose none.
- Location: Choose a location from the list.
- Version: Leave it to the default value.
- Compute + Storage: For now select configure server option and then change vCore to two cores. Select OK.
- Admin username: Give a username.
- Password: Give a complex password for your database.
- Confirm password: Retype the password for confirmation.
Step 7: Select the Review + Create option.
Step 8: After reviewing all the details click on create to create your Azure Database for PostgreSQL.
Step 9: Wait for the deployment to complete. Then click on go to resource option to go to your deployed resource.
This is how we can provision an Azure database for PostgreSQL in the Azure Portal.
Benefits of Azure Database For PostgreSQL:
- It contains built-in failure detection and failover mechanisms which makes it a highly available service.
- We can use the pgAdmin tool to connect to the Azure Database for PostgreSQL to manage and monitor it. However, some of the server-focused functionalities such as server backup and restore aren’t available.
- It records the information about queries run on the database in the server which is saved in a database name azue_sys. We can query the query_store.qs_view to get the information and use it to monitor the queries the users are running.