DevToolHub

Managing PostgreSQL servers across multiple Azure subscriptions can be challenging, especially when you need to track storage usage and configurations. In this guide, we’ll explore a powerful Bash script that automates the inventory process for Azure PostgreSQL servers, making infrastructure management more efficient and reliable.

Introduction

As organizations grow, keeping track of database infrastructure becomes increasingly complex. Manual inventory tracking is time-consuming and error-prone. Our automated solution helps DevOps teams maintain an up-to-date inventory of PostgreSQL servers across their Azure environment.

The Solution

We’ve developed a Bash script that automatically generates a comprehensive inventory of PostgreSQL servers across all Azure subscriptions. The script is available on our GitHub repository.

For those interested in PostgreSQL Flexible Server inventory automation, check out our complementary guide on DevToolHub.

Key Features

1. Multi-subscription Support: Automatically scans all accessible Azure subscriptions
2. Comprehensive Data Collection: Gathers detailed server configurations
3. Storage Metrics: Includes current storage usage in GB
4. CSV Export: Generates easily readable reports
5. Authentication Handling: Manages Azure login process
6. Error Management: Robust error handling and prerequisites checking

How It Works

Let’s break down the key components of the script:

1. Prerequisites Check

check_az_cli() {
    if ! command -v az &> /dev/null; then
        echo "Error: Azure CLI is not installed. Please install it first."
        echo "Visit: https://docs.microsoft.com/en-us/cli/azure/install-azure-cli"
        exit 1
    fi
}

This function ensures Azure CLI is installed before proceeding. It’s crucial for interacting with Azure services.

2. Azure Authentication

azure_login() {
    echo "Checking Azure login status..."
    if ! az account show &> /dev/null; then
        echo "Not logged in. Initiating Azure login..."
        if ! az login; then
            echo "Error: Azure login failed"
            exit 1
        fi
        echo "Login successful!"
    else
        echo "Already logged into Azure"
        current_account=$(az account show --query user.name -o tsv)
        echo "Current account: $current_account"
    fi
}

The authentication function handles:

• Checking existing login status
• Initiating login if needed
• Displaying current account information

3. Storage Metrics Collection

get_storage_metrics() {
    local subscription=$1
    local resource_group=$2
    local server_name=$3

    local resource_id="/subscriptions/$subscription/resourceGroups/$resource_group/providers/Microsoft.DBforPostgreSQL/servers/$server_name"

    metrics=$(az monitor metrics list \
        --resource "$resource_id" \
        --metric storage_used \
        --aggregation Maximum \
        --query 'value[0].timeseries[0].data[0].maximum' \
        -o tsv 2>/dev/null || echo "0")

    local gb=$(echo "scale=2; $metrics / 1024 / 1024 / 1024" | bc)
    echo "$gb"
}

This function:

• Retrieves storage metrics using Azure Monitor
• Converts bytes to gigabytes
• Handles missing metrics gracefully

4. CSV Report Generation

The script creates detailed CSV reports including:

• Server name and location
• PostgreSQL version
• Administrative login
• SKU information
• Storage configuration
• TLS version
• Network access settings
• Server state

Best Practices Implemented

1. Error Handling: The script includes comprehensive error checking and appropriate error messages.
2. Resource Efficiency: Uses Azure CLI queries efficiently to minimize API calls.
3. Data Formatting: Properly formats and sanitizes CSV output.
4. Authentication Management: Handles Azure authentication seamlessly.

Use Cases

1. Infrastructure Auditing: Regular inventory checks for compliance
2. Capacity Planning: Track storage usage across servers
3. Security Reviews: Monitor TLS versions and network access settings
4. Cost Optimization: Identify underutilized servers

Implementation Guide

1. Clone the repository:

   git clone https://github.com/littleworks-inc/PostgreSQL_servers-/tree/main

2. Make the script executable:

   chmod +x postgres_inventory.sh

3. Run the script:

   ./postgres_inventory.sh

Understanding the Output

The script generates a CSV file with the following format:

Subscription,Resource Group,Server Name,Location,Version,Admin Login,SKU Name,Storage Profile (MB),Storage Used (GB),TLS Version,Public Network Access,Server State

Each row provides comprehensive information about a PostgreSQL server instance.

Required Permissions

To run the script successfully, ensure your Azure account has:

• Reader access to target subscriptions
• Access to PostgreSQL server configurations
• Permissions to read Azure Monitor metrics

Troubleshooting Tips

1. Azure CLI Not Found

• Install Azure CLI using official documentation
• Verify PATH environment variable

1. Authentication Issues

• Check internet connectivity
• Verify Azure credentials
• Ensure subscription access

1. Missing Metrics

• Verify monitoring is enabled
• Check server accessibility

Future Enhancements

1. HTML Report Generation: Adding support for rich HTML reports
2. Email Integration: Automated report distribution
3. Threshold Alerts: Storage usage warnings
4. Custom Metrics: Additional monitoring parameters

Conclusion

This automation script significantly simplifies PostgreSQL server inventory management in Azure. It provides a reliable, efficient way to maintain infrastructure documentation and monitor resource usage.

For PostgreSQL Flexible Server inventory automation, refer to our detailed guide on DevToolHub.

References

1. GitHub Repository
2. PostgreSQL Flexible Server Guide
3. Azure CLI Documentation
4. Azure PostgreSQL Documentation