Azure PostgreSQL Server Inventory Automation Guide

Amaresh Pelleti

Introduction

Managing multiple Azure PostgreSQL Flexible Servers across various subscriptions can be challenging, especially when you need to track resource utilization, database distributions, and storage metrics. In this guide, we’ll explore a powerful shell script that automates the inventory process and provides detailed insights into your PostgreSQL infrastructure.

The complete solution is available on GitHub: azure-postgres-flex-inventory

Why You Need Automated PostgreSQL Inventory Management

Managing PostgreSQL servers in Azure comes with several challenges:

  • Tracking databases across multiple subscriptions
  • Monitoring storage usage and growth
  • Maintaining an up-to-date inventory of server configurations
  • Excluding system databases from reports
  • Converting storage metrics into readable formats

Our script addresses these challenges by providing a comprehensive, automated solution that generates detailed CSV reports of your entire PostgreSQL infrastructure.

Key Features of the Inventory Script

  1. Cross-Subscription Support
  • Automatically scans all accessible Azure subscriptions
  • Consolidates data into a single report
  • Maintains hierarchy of subscription → resource group → server → database
  1. Detailed Server Information
  • PostgreSQL version
  • SKU name and tier
  • Storage configuration and IOPS
  • Resource group association
  1. Storage Metrics
  • Current storage usage in bytes
  • Automatic conversion to MB and GB
  • Provisioned storage capacity
  • Storage performance tier
  1. Database Management
  • Lists all user databases
  • Automatically excludes system databases
  • Individual rows for each database for better analysis

Implementation Details

Prerequisites

# Azure CLI
az login

# Required utilities
sudo apt-get install jq bc  # For Ubuntu/Debian
brew install jq            # For macOS

Key Script Components

  1. Storage Metrics Collection
az monitor metrics list \
    --resource "$resource_id" \
    --metric storage_used \
    --aggregation Maximum
  1. Database Filtering
# Excludes system databases
grep -v -E '^(azure_maintenance|postgres|azure_sys)$'
  1. Storage Unit Conversion
convert_storage_size() {
    local bytes=$1
    local mb=$(echo "scale=2; $bytes / 1024 / 1024" | bc)
    local gb=$(echo "scale=2; $bytes / 1024 / 1024 / 1024" | bc)
    echo "$bytes|$mb|$gb"
}

CSV Output Format

The script generates a detailed CSV file with the following columns:

ColumnDescription
SubscriptionAzure Subscription ID
Resource GroupResource group name
Server NamePostgreSQL server name
PostgreSQL VersionServer version
SKU NameServer SKU configuration
SKU TierService tier
Storage Provisioned(GB)Allocated storage
IOPSProvisioned IOPS
Storage TierStorage performance tier
Database NameUser database name
Storage Used(Bytes/MB/GB)Current storage usage

Best Practices for Using the Script

Regular Execution

    • Schedule weekly runs for trend analysis
    • Keep historical reports for capacity planning
    • Compare usage patterns over time

    Storage Monitoring

      • Track storage growth trends
      • Plan capacity upgrades proactively
      • Identify unused or oversized databases

      Resource Optimization

        • Review server configurations
        • Identify underutilized resources
        • Plan consolidation where appropriate

        Use Cases

        Compliance Reporting

          • Generate inventory reports for audits
          • Track database proliferation
          • Monitor resource utilization

          Cost Management

            • Identify oversized servers
            • Track storage usage trends
            • Plan capacity requirements

            Operations Management

              • Maintain server inventory
              • Track database distribution
              • Monitor storage metrics

              Future Enhancements

              The script can be extended to include:

              • Performance metrics collection
              • Cost analysis integration
              • Automated recommendations
              • Custom alerting thresholds
              • Historical trend analysis

              Conclusion

              This automated inventory script simplifies the management of Azure PostgreSQL Flexible Servers by providing detailed insights into your infrastructure. It helps operations teams maintain accurate records, plan capacity, and optimize resources efficiently.

              Get started with the script today:

              git clone https://github.com/littleworks-inc/azure-postgres-flex-inventory
cd azure-postgres-flex-inventory
./list-postgres-servers.sh

              Additional Resources