Create a PostgreSQL Database on Azure in 5mins
Tags: azure, postgres, cloud
Published: 2021-10-19 13:46:04


## 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lyfxdobmh2j24s8upe1q.png)
 

## Prerequisites
* An Azure subscription is required. If you don't have an Azure subscription, create a free [Azure account](https://azure.microsoft.com/free/) 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](https://portal.azure.com/) to create an Azure Database for PostgreSQL Single Server database. Search for and select *Azure Database for PostgreSQL servers*.
![Azure PostgreSQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pbm4qjvwuv7tv2crnrsv.png)
2. Click on *Create* > select *Flexible Server*.
![Flexible Server](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fhd0rc4dtnah4kanleds.png)
 

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/amg000s9107xpztv1jn2.png)
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hfm1o2od4abde9u0tawz.png)
 
---

## 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0p5tixw40map7hot66uj.png)
 
2. Open pgAdmin > right click on Server > Create Server
![pgadmin](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/80ony3t0xyoftzf0wjeo.png)
 
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eyipqwk9jtc67xekd2jo.png)
 
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lspa2fno53z1izn7as0i.png)
 
8. Click on mypgsqldb and the Query Tool icon at the top window.
![pgadmin-query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y6pzxhusak1gqybyn04a.png)
 
 
9. Copy and paste the commands below into the Query Editor and select Execute Query.

```sql
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;
```
The results from the Inventory table will be shown as below:
![pgadmin-results](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5bcnmhxk8smqvu89ebvd.png)

## 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0p5tixw40map7hot66uj.png)

In my example, the code will be:
```bash
psql --host=postgres-tutorial-server-22102021.postgres.database.azure.com --port=5432 --username=postgresadmin --dbname=postgres
```

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

```bash
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=> 
```

Create a database called mypgsqldb2

```sql
CREATE DATABASE mypgsqldb2
```

Switch the connection to connect to this newly created database instead of the default postgres database that we initially connected to.
```sql
\c mypgsqldb2
```
Create a table in this database

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

Insert some data into the Inventory table
```sql
INSERT INTO inventory (id, name, quantity) VALUES (1, 'banana', 150); 
INSERT INTO inventory (id, name, quantity) VALUES (2, 'orange', 154);
```

Query the results from the Inventory table
```sql
SELECT * FROM inventory;
```

## 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