Step by step guideline to install PostgreSQL in Azure cloud and Client tool to administrate the PostgreSQL

What is PostgreSQL?

PostgreSQL, also known as Postgres, is a free and open-source relational database management system.  The official PostgreSQL site mentioned, "The World's Most Advanced Open Source Relational Database".  PostgreSQL as Open Source database gained huge popularity in past few years, this article post will focus how to install PostgreSQL in Azure cloud and tools to interact with the database.

Installation of PostgreSQL in the Azure Cloud environment

At first, login to your Azure Portal and search for PostgreSQL, You will find different services to choose from, I have chosen “Azure Database for PostgreSQL flexible servers” from the below list as shown in Fig 1. This particular service will allow to add any extension you want to add to your database in future.

Fig 1: PostgresSQL services in Azure Cloud

As soon as you choose the option you will find below figure 2, which will allow to create the postgreSQL flexible server.

    Fig 2: PostgreSQL flexible server

After clicking  "Create Azure Database for PostgreSQL flexible server" as shown in above figure 2, you will have options to choose from four different plans as shown in figure 3. As per your need you can choose from anyone of them. "Single server" was best fit for my requirements since it's enterprise ready, fully managed and I can add extension to it.

Fig 3: Choose right plan for your database

 

As soon as you hit the 'Single server' as shown above figure 3, you will find details information to fill up as shown in figure 4.

Please follow the below steps, figure (4) indicates each step listed.

1) Choose the right subscription for your resource group

2) Please select resource group where you want to install the database server, if no resource group created then you need to create a resource group. Please find details how to create azure resource group

3) Put the server name for PostgreSQL

4) Choose the location where you would like to install the PostgreSQL, I have chosen Canada Central, however; you can choose which best fit for you.

5) Choose the version of PostgreSQL that you would like deploy in Azure

6) At this step fill up the administrator account information and save this credential; you will need this when you log into the database server.

Fig 4: PostgreSQL deployment config input

After filling up the above information, please click 'Review + Create'. It will take a few minutes to complete the installation and you will find below message when deployment is completed as shown in figure 5.

Fig 5: Deployment is completed

After the deployment if you click Go to Resource (as shown bottom link at Fig 6), you will find out more details about the resource that you just created. We will need these information when database server need to connect from On-Premise IDE.

Fig 6: resource details

How to connect PostgreSQL from On-Premise GUI?

PostgreSQL deployment is completed in Azure Cloud, however; Now we need to find out how to connect this PostgreSQL database server with a Graphical User Interface (GUI) and create any new databases. One of the popular GUI for PostgreSQL is pgAdmin.

Let's start installing pgAdmin to connect the database server and do rest of the operation. Please follow the link to install pgAdmin for Windows. You can choose latest version to of pgAmin, download it and then use wizard to install it.

When pgAdmin installation is completed, you will find below (Fig 7) if you search for the app from your computer.

                             Fig 7: pgAdmin installed in my PC

Now, we are going to use pgAdmin 4 to connect the deployed PostgreSQL database server. Open the app pgAdmin 4 and right click under server as below figure 8 is shown.

Fig 8: Create connection

And then you need to fill up the details to connect PostgreSQL database server which we deployed previously (fig 4). Details are shown in below figure 9, and fill up the information as suggested below:

1. Host name/Address: This is server name which can be found under the resource details (as shown in figure 5.)

2. Port by default should be set 5432, in case it's not then please put 5432.

3. Maintenance database: It's like master database if you are coming from SQL DB experiences, it should fill up automatically, if not then put: postgres

4. User Name: It's admin user name (see figure 4 or 6)

5. Password: The password you entered (fig 4)

As well as, under General tab, please give any name you like for the connection then hit Save button.

Fig 9: connection details need to fill up

Now you are connected your PostgreSQL database server in the Azure Cloud environment from PgAdmin GUI as shown in below figure 10. Everything is set, you can create new database, add new extension to it and whatever operations you want to make. 

Fig 10: PgAdmin GUI connected with PostgreSQL in the Azure Cloud

We learned how to deploy PostgreSQL in the Azure Cloud environment as well as how we can connect the database server from on-premise GUI called PgAdmin.