DevToolHub

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

2. Database Filtering

# Excludes system databases
grep -v -E '^(azure_maintenance|postgres|azure_sys)$'

3. 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:

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

• Azure PostgreSQL Flexible Server Documentation
• Azure CLI Documentation
• Azure Monitor Metrics