PostgreSQL Database Deployment with Knowledge Transfer to On-Premises DBAs Team Using Microsoft Azure Database for PostgreSQL Servers

Microsoft Azure Database for PostgreSQL Servers

In today’s rapidly evolving tech landscape, companies are increasingly migrating their databases to the cloud to leverage scalability, flexibility, and cost-efficiency. With a high demand for professionals skilled in cloud database management, I spearheaded a project that bridged the knowledge gap for a team of on-premises DBAs by demonstrating how to provision and manage a PostgreSQL database on Microsoft Azure. This hands-on project not only reflects real-world scenarios but also aligns perfectly with the current job market’s demands for cloud expertise.

Solutions Architecture

Summary Overview

Steps Performed:

Step 1: Created a PostgreSQL Database Using Azure Database for PostgreSQL

• User Admin: xxxxxx
• Password: xxxxxx

This step involved navigating the Azure portal and deploying a PostgreSQL database instance.

• Created a PostgreSQL database Instance.

Created the Azure Database / Server Instance for PostgreSQL

Step 2. Downloaded pgAdmin:

• URL: pgAdmin 4 Download Link
• pgAdmin is a popular open-source management tool for PostgreSQL. I downloaded and installed version 8.10 to manage the PostgreSQL database.
• Please note that depending on the version of pgAdmin you are using, you may need to upgrade it if you encounter connection issues.

Step 3. Added the Public IP (Laptop) to the Azure PostgreSQL Firewall:

• A quick way to verify your public IP address, visit What is My IP Address. Additionally, and once verified, ensure the public IP address is allowed in the firewall settings of your PostgreSQL server to enable successful connections.
• To allow access to the PostgreSQL database from pgAdmin, I added my public IP address to the Azure PostgreSQL firewall rules.
• Verified & confirmed login credentials for PostgreSQL via Azure CLI

Verified Login Credentials for PostgreSQL via Azure CLI

Step 4. Connected to the PostgreSQL Database in Azure Using pgAdmin:

• Using the connection details provided by the Azure PostgreSQL service, I connected to the database through pgAdmin.

pgAdmin General Tab.

Register PostgreSQL Server Name:

Postgres SQL Server Host name /address, Port:5432, Maintenance DB, Credentials

• Successfully connected to PostgreSQL Server.

Successfully connected to Postgres SQL Server

Verified Session for User: sql

Step 5. Created a New Database:db01

• In pgAdmin, I created a new database to store the project data.

New Database:db01

Step 6. Created New Schema:

• Schema Name: app01
• Within the database, I created a new schema named app01 to logically group database objects.

Schema Name: app01

Step 7. Created a New Table:

• Table Name: employees
• In the app01 schema, I created a table named employees with appropriate columns to store employee data.

Created a New Table: employees

Step 8. Added a New Record in the Table:

• Insert Query:

Inserted into app01.employees VALUES (1, ‘Lionell B Jenious’);

• Inserted a new record into the employees table.

Inserted a new record into the employees table.

Step 9. Querying the Data Added:

• Select Query:
• I showed how to query the data from the employees table to verify the insertion.

Queried the Data Added and Selected Query:

• Data Output

Query — Data Output

Conclusion:

Completing this hands-on project has provided me with practical experience in cloud database deployment and management. This real-life use case is crucial for companies migrating to the cloud, ensuring they have the necessary skills to onboard successfully. The knowledge and experience gained from this project add significant value for customers seeking expertise in cloud solutions, highlighting the importance of being well-versed in modern cloud technologies.