Azure Database for PostgreSQL - Commands & Configuration

Introduction

Azure Database for PostgreSQL is a managed service that lets you run PostgreSQL databases in the cloud without worrying about hardware or maintenance. It solves the problem of setting up and managing a reliable database server by handling backups, updates, and scaling automatically.

When you want to host a PostgreSQL database without managing the underlying server.

When you need automatic backups and easy recovery for your database.

When your app requires scaling the database resources up or down based on demand.

When you want built-in security features like encryption and firewall rules.

When you want to connect your cloud app to a reliable, managed PostgreSQL database.

Config File - azure-postgresql-deployment.bicep

azure-postgresql-deployment.bicep

param serverName string = 'mypgserver123'
param adminUser string = 'pgadmin'
param adminPassword string
param location string = resourceGroup().location

resource postgresqlServer 'Microsoft.DBforPostgreSQL/servers@2022-12-01' = {
  name: serverName
  location: location
  sku: {
    name: 'B_Gen5_1'
    tier: 'Basic'
    capacity: 1
    family: 'Gen5'
  }
  properties: {
    administratorLogin: adminUser
    administratorLoginPassword: adminPassword
    version: '13'
    sslEnforcement: 'Enabled'
    storageProfile: {
      storageMB: 5120
      backupRetentionDays: 7
      geoRedundantBackup: 'Disabled'
    }
  }
}

resource firewallRule 'Microsoft.DBforPostgreSQL/servers/firewallRules@2022-12-01' = {
  name: '${serverName}/AllowMyIP'
  properties: {
    startIpAddress: '0.0.0.0'
    endIpAddress: '0.0.0.0'
  }
  dependsOn: [postgresqlServer]
}

This Bicep file creates an Azure Database for PostgreSQL server with basic SKU and version 13.

The administratorLogin and administratorLoginPassword set the admin user credentials.

The sslEnforcement is enabled for secure connections.

The firewallRule allows connections from all IPs (0.0.0.0) for simplicity; in real use, restrict this to your IP range.

Commands

This command deploys the PostgreSQL server and firewall rule to the specified Azure resource group using the Bicep template. It sets the admin password securely.

Terminal

az deployment group create --resource-group myResourceGroup --template-file azure-postgresql-deployment.bicep --parameters adminPassword=MyStrongP@ssw0rd

Expected Output

{

  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/myResourceGroup/providers/Microsoft.Resources/deployments/azure-postgresql-deployment",

  "name": "azure-postgresql-deployment",

  "properties": {

    "provisioningState": "Succeeded",

    "outputs": {}

  },

  "type": "Microsoft.Resources/deployments"

}

→

--resource-group - Specifies the Azure resource group to deploy into

→

--template-file - Points to the Bicep template file to use for deployment

→

--parameters - Passes parameters like admin password securely

This command retrieves details about the deployed PostgreSQL server to verify it was created successfully.

Terminal

az postgres server show --resource-group myResourceGroup --name mypgserver123

Expected Output

{

  "administratorLogin": "pgadmin",

  "fullyQualifiedDomainName": "mypgserver123.postgres.database.azure.com",

  "location": "eastus",

  "name": "mypgserver123",

  "sslEnforcement": "Enabled",

  "version": "13",

  "userVisibleState": "Ready"

}

→

--resource-group - Specifies the resource group where the server exists

→

--name - Specifies the name of the PostgreSQL server

This command connects to the PostgreSQL server using the psql client and lists all databases to confirm connectivity.

Terminal

psql "host=mypgserver123.postgres.database.azure.com port=5432 dbname=postgres user=pgadmin@mypgserver123 password=MyStrongP@ssw0rd sslmode=require" -c "\l"

Expected Output

                                   List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+----------+----------+-------------+-------------+-----------------------

 postgres  | pgadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | pgadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgadmin           +

           |          |          |             |             | pgadmin=CTc/pgadmin

 template1 | pgadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgadmin           +

           |          |          |             |             | pgadmin=CTc/pgadmin

(3 rows)

Key Concept

If you remember nothing else from this pattern, remember: Azure Database for PostgreSQL lets you run a managed, secure PostgreSQL server in the cloud without handling hardware or maintenance.

Common Mistakes

Using weak or simple passwords for the admin user

Weak passwords can lead to unauthorized access and data breaches.

Always use strong, complex passwords with letters, numbers, and symbols.

Setting firewall rules to allow all IP addresses in production

This exposes the database to the internet and increases security risks.

Restrict firewall rules to only trusted IP addresses or ranges.

Not enabling SSL enforcement for connections

Without SSL, data between client and server can be intercepted.

Enable SSL enforcement to secure data in transit.

Summary

Use a Bicep template to define and deploy an Azure Database for PostgreSQL server with admin credentials and firewall rules.

Deploy the template using Azure CLI and verify the server creation with 'az postgres server show'.

Connect securely to the PostgreSQL server using psql client with SSL enabled to list databases.

Practice

1. What is the main benefit of using Azure Database for PostgreSQL?

easy

A. It only supports PostgreSQL version 9.6.

B. It requires manual installation of PostgreSQL on virtual machines.

C. It is only available for on-premises servers.

D. It manages database setup, scaling, and maintenance automatically.

Solution

1. Step 1: Understand Azure Database for PostgreSQL service

   This service is managed by Azure, which means it handles setup, scaling, and maintenance for you.

2. Step 2: Compare options

   Limited version support, manual installation on VMs, and on-premises availability do not match the managed nature of this cloud service.

3. Final Answer:

   It manages database setup, scaling, and maintenance automatically. -> Option D

4. Quick Check:

   Managed service = automatic management [OK]

Hint: Managed means Azure handles setup and scaling for you [OK]

Common Mistakes:

• Thinking you must install PostgreSQL manually
• Assuming only old versions are supported
• Confusing cloud service with on-premises

2. Which Azure CLI command correctly creates a new PostgreSQL server named myserver in resource group mygroup?

easy

A. az postgres server new --name myserver --resource-group mygroup --admin-user admin --admin-password Pass@123

B. az postgres create server --resource-group mygroup --name myserver --admin-user admin --admin-password Pass@123

C. az postgres server create --name myserver --resource-group mygroup --location eastus --admin-user admin --admin-password Pass@123

D. az create postgres server --name myserver --resource-group mygroup --admin-user admin --admin-password Pass@123

Solution

1. Step 1: Identify correct Azure CLI syntax

   The correct command to create a PostgreSQL server uses az postgres server create with required parameters.

2. Step 2: Check options for syntax errors

   Other options use incorrect command structures, order, or omit required parameters like location, making them invalid.

3. Final Answer:

   az postgres server create --name myserver --resource-group mygroup --location eastus --admin-user admin --admin-password Pass@123 -> Option C

4. Quick Check:

   Correct CLI command = az postgres server create --name myserver --resource-group mygroup --location eastus --admin-user admin --admin-password Pass@123 [OK]

Hint: Use 'az postgres server create' to make a new server [OK]

Common Mistakes:

• Mixing command order or keywords
• Using 'az create' instead of 'az postgres server create'
• Omitting required parameters like location

3. Given this Azure CLI command:

az postgres server show --name myserver --resource-group mygroup

What output should you expect?

medium

A. Details of the PostgreSQL server named 'myserver' in 'mygroup'.

B. Creates a new PostgreSQL server named 'myserver'.

C. Deletes the PostgreSQL server named 'myserver'.

D. Lists all PostgreSQL servers in the subscription.

Solution

1. Step 1: Understand the 'az postgres server show' command

   This command retrieves and displays details about a specific PostgreSQL server.

2. Step 2: Compare with other options

   Creating, deleting, or listing all servers require different commands. This 'show' command displays details of the specific server.

3. Final Answer:

   Details of the PostgreSQL server named 'myserver' in 'mygroup'. -> Option A

4. Quick Check:

   'show' command = server details [OK]

Hint: 'show' means display info about one server [OK]

Common Mistakes:

• Confusing 'show' with 'create' or 'delete'
• Expecting a list of all servers
• Not specifying resource group or server name

4. You run this command to create a PostgreSQL server but get an error:

az postgres server create --name myserver --resource-group mygroup --admin-user admin --admin-password Pass123

What is the likely cause?

medium

A. The admin user name 'admin' is not allowed.

B. The admin password does not meet Azure's complexity requirements.

C. The server name 'myserver' is already in use.

D. The resource group 'mygroup' does not exist.

Solution

1. Step 1: Check password complexity rules

   Azure requires strong passwords with uppercase, lowercase, numbers, and symbols. 'Pass123' lacks symbols.

2. Step 2: Evaluate other options

   Resource group existence or server name conflicts cause different errors. 'admin' is allowed as admin user.

3. Final Answer:

   The admin password does not meet Azure's complexity requirements. -> Option B

4. Quick Check:

   Password complexity error = The admin password does not meet Azure's complexity requirements. [OK]

Hint: Check password has uppercase, lowercase, number, symbol [OK]

Common Mistakes:

• Ignoring password complexity rules
• Assuming resource group or name errors without checking
• Thinking 'admin' username is invalid

5. You want to enable high availability for your Azure Database for PostgreSQL server. Which configuration should you choose?

hard

A. Create a server with the 'Zone-redundant HA' option enabled.

B. Use a single server with no replicas.

C. Manually set up replication between two separate servers.

D. Deploy PostgreSQL on a VM and configure failover clustering.

Solution

1. Step 1: Understand Azure's built-in high availability options

   Azure Database for PostgreSQL offers a 'Zone-redundant HA' option that automatically replicates data across availability zones.

2. Step 2: Evaluate other options

   A single server with no replicas lacks HA. Manual replication between separate servers requires manual setup and is not recommended. Deploying on a VM with failover clustering is outside the managed service scope.

3. Final Answer:

   Create a server with the 'Zone-redundant HA' option enabled. -> Option A

4. Quick Check:

   Zone-redundant HA = automatic high availability [OK]

Hint: Use zone-redundant HA for built-in high availability [OK]

Common Mistakes:

• Choosing single server without HA
• Trying manual replication instead of managed HA
• Using VMs instead of managed service for HA