PostgreSQL database deployment with knowledge transfer to on-premises DBAs team using Microsoft Azure Database for PostgreSQL Servers.

Project description:

In another project based on a real-world scenario, I acted as a Cloud Specialist to show a team of on-premises DBAs how to provision a PostgreSQL database on Microsoft Azure.

I supported the deployment and showed step-by-step how to create the database service in Microsoft Azure. I shared how the connection to the database service in Azure worked, what were the steps of creating a new database, a new schema, and a new table. Also, I instructed them how to insert new records and query data in the table.

Create Azure Database for PostgreSQL:

In the console I searched for “PostgreSQL” and selected “Azure Database for PostgreSQL”.

On the next screen I clicked “Create”. Under Azure Database for PostgreSQL I selected “Single server” from the dropdown menu, and clicked “Create”.

I used the following to create the database:

Resource group: azurebootcamp

Server name: tcbpostgredb01

Data source: None

Location: East US

Version: 11

Compute + storage: Configure server: Basic (with minimum options)

Admin username: sql

Password: Admin123456!

PostgreSQL pgAdmin:

I downloaded and installed PostgreSQL pgAdmin File Browser on my Macbook Pro so that I could make a remote connection to the database server. If you are using a different operating system use the appropriate installer.

The installation and setup is pretty straight forward so I’m not going to show it here.

In order to make the remote connection to the server access must be allowed. To accomplish this I navigated to the “tcbpostgredb01” instance and under “Settings” selected “Connection security” and clicked “Add current client IP address” to select my public IP and add it to the firewall rule. If application servers need access this is where it’s done.

Then I clicked “Save”.

I then navigated to “Connection string” and showed the new DBA that these are all the different connection strings in different programming languages in order to connect to the PostgreSQL Database so he could share with the developer team.

Back in pgAdmin it was time to connect to the server. I right clicked on “Servers > Register > Server”.

On the Overview page in Azure Portal I copied the Server name and pasted into the pgAdmin Create Server “General” tab.

I then copied the Server name and Admin username into the “Connection” tab, with the admin password and toggled the Save password switch and clicked “Save”.

Once connected I right clicked on Databases and selected “Create > Database”.

Named the database “db01” and “Save”.

After I created a Schema. (one schema per application). I right clicked on “Schema > Create > Schema”.

I named it “app01” and clicked “Save”. I continued and right clicked on “Tables > Create > Table” with the following:

General tab: Name: employee

Columns tab:

Name: id , Data type: integer, Not NULL?: ON, Primary key?: ON

Name: full_name , Data type: character varying, Length/Precision: 200

Once created I then showed how to insert data into the table. I selected the “Query Tool” icon at the top of pgAdmin.

In the Query window typed the following SQL query to insert data:

INSERT into app01.employees VALUES(1,’Brett Stephen’)

Then selected it and ran the query by clicking the Execute icon.

I then ran the following query to read the data from the table.

SELECT * from app01.employees

That’s it!