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:
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
- Go to the Azure Portal to create an Azure Database for PostgreSQL Single Server database. Search for and select Azure Database for PostgreSQL servers.
Click on Create > select Flexible Server.
-
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 Click Next: Networking
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)
Scroll down to Firewall Rules > select Add current client IP address
Select Review + Create > Create to provision the server. This operation might take a few minutes.
And you're done! PostgreSQL database is successfully created, click Go to Resource to view the database created.
Connecting to the Database with pgAdmin
2 common methods to connect to a PostgreSQL database is using either pgAdmin or psql command line.
Click Overview in Azure Portal to copy the server name and username for our connection later on.
Open pgAdmin > right click on Server > Create Server
Create a name for this connection eg. azure-postgres-tutorial
-
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 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.
Perfect! Now you are connected to the PostgreSQL database running on Azure and ready to start adding data into your database!
Right click on Database > Create > Database. Enter any name as your new database name eg. mypgsqldb.
Click on mypgsqldb and the Query Tool icon at the top window.
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;
The results from the Inventory table will be shown as below:
Connecting to the Database with psql
Alternatively you can use psql to connect/query the database.
- Open Terminal/Powershell
- 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.
In my example, the code will be:
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:
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
CREATE DATABASE mypgsqldb2
Switch the connection to connect to this newly created database instead of the default postgres database that we initially connected to.
\c mypgsqldb2
Create a table in this database
CREATE TABLE inventory (
id serial PRIMARY KEY,
name VARCHAR(50),
quantity INTEGER
);
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);
Query the results from the Inventory table
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
Top comments (0)