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.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Made by

Massdriver

Official

Yes

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

  1. Private Network Setup: The PostgreSQL Flexible Server is deployed in a private subnet within a specified virtual network to ensure network security and isolation.
  2. High Availability: High availability is guaranteed by optionally enabling zone redundant configurations.
  3. PgBouncer: Integration with PgBouncer is supported for managing idle connections, which can be enabled based on user preference.
  4. Monitoring and Alerts: Auto-configured CPU, memory, and storage alerts to proactively manage resource utilization and ensure application performance.
  5. Backup Policies: Geo-redundant backups are enabled to ensure data resilience and availability in the event of a regional failure.
  6. 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.

VariableTypeDescription
addons.extensions[]array(string)No description
addons.pgbouncerbooleanPgBouncer is a connection pooler for PostgreSQL. It reduces the number of connections to the database, which can improve performance.
backup.backup_retention_daysintegerHow many days to retain PostgreSQL database backups (minimum of 7, maximum of 35).
database.high_availabilitybooleanNo description
database.postgres_versionstringThe version of PostgreSQL to use. The version cannot be changed.
database.sku_namestringSelect the amount of cores, memory, and iops you need for your workload (D = General Purpose, E = Memory Optimized).
database.storage_mbintegerThe amount of storage capacity available to your Azure Database for PostgreSQL server. Storage size cannot be scaled down.
database.usernamestringThe 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.modestringEnable and customize Function App metric alarms.
network.autobooleanEnabling this will automatically select an available CIDR range for your database. Unchecking will require you to specify the CIDR.