Azure PostgreSQL Flexible Server
Azure PostgreSQL Flexible Server is a fully managed database service designed to provide more control and flexibility over database management functions and configurations.
Made by
Massdriver
Official
Yes
Clouds
Tags
Azure PostgreSQL Flexible Server
Azure PostgreSQL Flexible Server is a fully managed relational database service provided by Microsoft Azure. It offers flexible server configurations and scaling options tailored to meet the needs of diverse workloads. This service is designed for high availability with fast failover capabilities, providing options for geo-redundant backups, and a rich set of compliance certifications ensuring data security and integrity.
Design Decisions
- Private Network Setup: The PostgreSQL Flexible Server is deployed in a private subnet within a specified virtual network to ensure network security and isolation.
- High Availability: High availability is guaranteed by optionally enabling zone redundant configurations.
- PgBouncer: Integration with PgBouncer is supported for managing idle connections, which can be enabled based on user preference.
- Monitoring and Alerts: Auto-configured CPU, memory, and storage alerts to proactively manage resource utilization and ensure application performance.
- Backup Policies: Geo-redundant backups are enabled to ensure data resilience and availability in the event of a regional failure.
- DNS Setup: A private DNS zone is configured for simplifying database connection management within the virtual network.
Runbook
Server Connection Issues
Note: PostgresQL bundle is configured to be isolated on the virtual network. By design, it refuses connection attempts made from outside of the virtual network. To connect, you may need to configure VPN or a jump box.
If you are having trouble connecting to your PostgreSQL Flexible Server, you can troubleshoot using the following Azure CLI commands:
Check the server status:
az postgres flexible-server show --resource-group <resource-group-name> --name <server-name> --query "{status: userVisibleState}"
Verify that the server is in the Ready
state. If not, further investigate why the server is not ready.
PgBouncer Connection Issues
If PgBouncer is enabled and you are experiencing connection issues, verify that the settings are correct:
Check PgBouncer configuration:
az postgres flexible-server parameter show --resource-group <resource-group-name> --server-name <server-name> --name pgbouncer.enabled
Ensure that the value is set to true
. If not, you may need to enable PgBouncer.
az postgres flexible-server parameter set --resource-group <resource-group-name> --server-name <server-name> --name pgbouncer.enabled --value true
Database Performance Issues
To check for performance-related issues such as high CPU, memory, or storage usage:
List all metric alerts:
az monitor metrics alert list --resource-group <resource-group-name>
Review any active alerts related to CPU, memory, or storage usage.
PostgreSQL Command-Line Troubleshooting
If you need to troubleshoot at the PostgreSQL level, you can use the following commands:
Connect to the PostgreSQL server:
psql -h <hostname> -U <username> -d <database-name>
Check the database connections:
SELECT * FROM pg_stat_activity;
Monitor the lock conflicts:
SELECT * FROM pg_locks;
Inspect slow running queries:
SELECT * FROM pg_stat_statements WHERE total_time > 1000 ORDER BY total_time DESC;
Ensure that you replace placeholders (<hostname>
, <username>
, <database-name>
, etc.) with actual values relevant to your PostgreSQL server configuration. This will help in diagnosing and resolving performance-related issues more accurately.
Variable | Type | Description |
---|---|---|
addons.extensions[] | array(string) | No description |
addons.pgbouncer | boolean | PgBouncer is a connection pooler for PostgreSQL. It reduces the number of connections to the database, which can improve performance. |
backup.backup_retention_days | integer | How many days to retain PostgreSQL database backups (minimum of 7, maximum of 35). |
database.high_availability | boolean | No description |
database.postgres_version | string | The version of PostgreSQL to use. The version cannot be changed. |
database.sku_name | string | Select the amount of cores, memory, and iops you need for your workload (D = General Purpose, E = Memory Optimized). |
database.storage_mb | integer | The amount of storage capacity available to your Azure Database for PostgreSQL server. Storage size cannot be scaled down. |
database.username | string | The administrator login for the PostgreSQL Flexible Server. Username cannot be changed after creation. (Username cannot be 'admin', 'root', 'administrator', 'username', 'azure_superuser', 'azure_pg_admin', 'guest', or 'public'.) |
monitoring.mode | string | Enable and customize Function App metric alarms. |
network.auto | boolean | Enabling this will automatically select an available CIDR range for your database. Unchecking will require you to specify the CIDR. |