DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Cover image for Create a PostgreSQL Database on Azure in 5mins
Lester Sim
Lester Sim

Posted on • Updated on

Create a PostgreSQL Database on Azure in 5mins

About Azure Database for PostgreSQL

Azure Database for PostgreSQL is a fully managed Database as a Service (DBaaS) offering. As compared to running it on-premises, Azure automatically handles all the fundamental database management tasks so you can focus on developing your applications instead of spending time managing databases. Here's a quick summary of what DBaaS refers to:
DBaaS

Prerequisites

  • An Azure subscription is required. If you don't have an Azure subscription, create a free Azure account before you begin.
  • Basic knowledge of pgAdmin/psql commands

Agenda

  • Creating an Azure Database for PostgreSQL (Flexible Server) using Azure Portal
  • Configuring the Firewall
  • Connecting/Querying the Database using pgAdmin/psql

Create an Azure Database for PostgreSQL

  1. Go to the Azure Portal to create an Azure Database for PostgreSQL Single Server database. Search for and select Azure Database for PostgreSQL servers. Azure PostgreSQL
  2. Click on Create > select Flexible Server.
    Flexible Server

  3. Enter the Basics form with the following information:

    Setting Description
    Subscription Select your desired Azure Subscription
    Resource Group Create a new resource group eg. postgres-tutorial
    Server Name Create a globally unique name eg. postgres-tutorial-server-22102021
    Region Select a location closest to you
    Workload Type Development
    Compute + Storage Use the default: Burstable, 1 vCore, 2GiB RAM, 32 GiB Storage
    Availability Zone No preference
    PostgreSQL Version 13
    High Availability Leave as unchecked
    Admin username Set your admin username eg. postgresadmin
    Password Set your password
  4. Click Next: Networking

  5. By default, the server that you create is not publicly accessible. If you are querying from your computer, you'll need to allow access from your IP address. In Connectivity Method > select Public Access (allowed IP addresses)

  6. Scroll down to Firewall Rules > select Add current client IP address
    Networking

  7. Select Review + Create > Create to provision the server. This operation might take a few minutes.

  8. And you're done! PostgreSQL database is successfully created, click Go to Resource to view the database created.
    successful-creation


Connecting to the Database with pgAdmin

2 common methods to connect to a PostgreSQL database is using either pgAdmin or psql command line.

  1. Click Overview in Azure Portal to copy the server name and username for our connection later on.
    overview

  2. Open pgAdmin > right click on Server > Create Server
    pgadmin

  3. Create a name for this connection eg. azure-postgres-tutorial

  4. Click the Connection tab and enter the hostname and username copied from the Overview page on Azure portal. In my example, it will be the following:

    Settings Value
    Host Name postgres-tutorial-server-22102021.postgres.database.azure.com
    Port 5432
    Maintenance Database postgres
    Username postgresadmin
    Password Your password
  5. Click Save. Once connected successfully, expand the server you have just connected to and you’ll see 3 databases already created by Azure. Do not delete these databases.
    default-databases

  6. Perfect! Now you are connected to the PostgreSQL database running on Azure and ready to start adding data into your database!

  7. Right click on Database > Create > Database. Enter any name as your new database name eg. mypgsqldb.
    pgadmin-create-database

  8. Click on mypgsqldb and the Query Tool icon at the top window.
    pgadmin-query

  9. Copy and paste the commands below into the Query Editor and select Execute Query.

CREATE TABLE inventory (
    id serial PRIMARY KEY, 
    name VARCHAR(50), 
    quantity INTEGER
);

INSERT INTO inventory (id, name, quantity) VALUES (1, 'banana', 150); 
INSERT INTO inventory (id, name, quantity) VALUES (2, 'orange', 154);

SELECT * FROM inventory;
Enter fullscreen mode Exit fullscreen mode

The results from the Inventory table will be shown as below:
pgadmin-results

Connecting to the Database with psql

Alternatively you can use psql to connect/query the database.

  1. Open Terminal/Powershell
  2. Run the following command to connect to the default database postgres. Replace hostname and username with what your actual server name and admin username set in previous steps. This can also be found on the Overview tab. overview

In my example, the code will be:

psql --host=postgres-tutorial-server-22102021.postgres.database.azure.com --port=5432 --username=postgresadmin --dbname=postgres
Enter fullscreen mode Exit fullscreen mode

Enter the database password created previously when prompted. Now, you're connected to the database with the following screen displayed:

psql --host=postgres-tutorial-server-22102021.postgres.database.azure.com --port=5432 --username=postgresadmin --dbname=postgres
Password for user postgresadmin: 
psql (13.1, server 13.4)
SSL connection (protocol: TLSv1.3, cipher: xxxxxxxx, bits: 256, compression: off)
Type "help" for help.

postgres=> 
Enter fullscreen mode Exit fullscreen mode

Create a database called mypgsqldb2

CREATE DATABASE mypgsqldb2
Enter fullscreen mode Exit fullscreen mode

Switch the connection to connect to this newly created database instead of the default postgres database that we initially connected to.

\c mypgsqldb2
Enter fullscreen mode Exit fullscreen mode

Create a table in this database

CREATE TABLE inventory (
    id serial PRIMARY KEY, 
    name VARCHAR(50), 
    quantity INTEGER
);
Enter fullscreen mode Exit fullscreen mode

Insert some data into the Inventory table

INSERT INTO inventory (id, name, quantity) VALUES (1, 'banana', 150); 
INSERT INTO inventory (id, name, quantity) VALUES (2, 'orange', 154);
Enter fullscreen mode Exit fullscreen mode

Query the results from the Inventory table

SELECT * FROM inventory;
Enter fullscreen mode Exit fullscreen mode

Wrapping Up

And you're done! You've successfully created a PostgreSQL database on Azure and connected to it using pgAdmin/psql!

What's Next

  • Use Azure CLI to create PostgreSQL databases
  • Explore the different Compute + Storage options
  • Create a Flask App connected to Azure PostgreSQL
  • Deploy a Flask App to Azure App Service with Azure PostgreSQL

Cleaning Up

If you don't expect to use this database in the future, search for Resource Groups at the top search bar of Azure Portal. Select the resource group name (eg. azure-postgres-tutorial) > Delete Resource Group

Resources

https://docs.microsoft.com/en-us/azure/postgresql/flexible-server/quickstart-create-server-portal
https://docs.microsoft.com/en-us/azure/postgresql/tutorial-design-database-using-azure-portal

Top comments (0)

🌚 Friends don't let friends browse without dark mode.

Sorry, it's true.