PostgreSQL database deployment with knowledge transfer to on-premises DBAs team using Microsoft Azure Database for PostgreSQL Servers.
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 the step-by-step 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.
Implementation:
1) Creating a PostgreSQL DB using Azure Database for PostgreSQL.
User admin: sql
Password: your_password
2)Downloading pgAdmin 4 v5.7
URL : https://www.postgresql.org/ftp/pgadmin/pgadmin4/v5.7/windows/
Just follow the wizard. After installation, search for pgadmin in windows start menu. Enter master password.
3)Adding your Public IP in the Firewall of the azure PostgreSQL.
Checking your Public IP: https://whatismyipaddress.com/
Azure PostgresSQL automatically detects your IP address and suggests to add it to the firewall rule. Only added IP addresses will be allowed to connect to the server. On the PostgressSQL server Networking section, you can configure your firewall rules.
Another important setting is connection strings section. To connect your application to the PostgresSQL server, you need connection string in various languages. Below is the option you might want to share with your Developers to connect to the server.
4)Connecting to the PostgreSQL DN in the Azure using pgAdmin.
We need below information to connect to the SQL server using pgAdmin.
Go to your pgAdmin, right-click on the server and add your server’s name.
After entering the credentials, you will get connected to the server.
5)Creating a new database: db01
On the Databases, create a new database with the name db01. On the SQL option, you will see the T-SQL script showing database creation.
6)Creating a new schema: app01
7)Creating a new table: employees
We have a table with two columns created.
8) Test the database.
# Adding new record in the table:
insert into app01.employees values(1,'ZR)# Querying the data added:
select * from app01.employees
Conclusion and Key Insights:
PostgreSQL is a widely used database in many organizations. Mastery in deploying, testing, and training new team members is essential. Through hands-on experience, I have learned about the capabilities of Azure PostgreSQL server, including provisioning an instance for testing purposes, using the pgAdmin client, and creating databases, schemas, tables, and entering data.
Attention to the administrative aspects of a PostgreSQL server is also crucial. This includes scaling, security, monitoring, granting developer access, and managing the number of schemas required for applications. Practicing these tasks hands-on has given me confidence in provisioning and managing a PostgreSQL server effectively.
