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:
- 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
- Creating an Azure Database for PostgreSQL (Flexible Server) using Azure Portal
- Configuring the Firewall
- Connecting/Querying the Database using pgAdmin/psql
- Go to the Azure Portal to create an Azure Database for PostgreSQL Single Server database. Search for and select Azure Database for PostgreSQL servers.
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)
Select Review + Create > Create to provision the server. This operation might take a few minutes.
2 common methods to connect to a PostgreSQL database is using either pgAdmin or psql command line.
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
Perfect! Now you are connected to the PostgreSQL database running on Azure and ready to start adding data into your database!
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;
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.
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;
And you're done! You've successfully created a PostgreSQL database on Azure and connected to it using pgAdmin/psql!
- 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
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