Azure Database for PostgreSQL

Azure Database for PostgreSQL is a Platform-as-a-Service (PaaS) relational database service that provides a managed PostgreSQL database engine. It eliminates the need to manage the underlying virtual machines, operating system, and PostgreSQL software patches. The service offers built-in high availability, automated backups, and scaling options. Microsoft offers two deployment modes: Single Server and Flexible Server. The DP-900 exam focuses on understanding these options, their use cases, and key differences.

PostgreSQL is an open-source object-relational database known for its extensibility, standards compliance, and support for advanced data types like JSON, geometric data, and custom extensions. On Azure, you get the benefits of PostgreSQL without the operational overhead. The service is suitable for enterprise workloads, including transactional systems, analytics, and geospatial applications. Azure adds features like automatic backups with point-in-time restore (PITR), read replicas, and integration with Azure Active Directory (AAD) authentication.

The exam expects you to know the key distinctions between Single Server and Flexible Server. Single Server is the older offering, now with a retirement date of March 28, 2023 (note: this date has passed; the exam may reference it as legacy). Flexible Server is the newer, more feature-rich option.

Both deployment options offer different compute tiers, but the names and specifics differ slightly.

Single Server Compute Tiers: - Basic: 1-2 vCores, 50-100 GB storage, no high availability SLA. Suitable for lightweight development. - General Purpose: 2-64 vCores, up to 4 TB storage, 99.99% SLA. Balanced compute and memory. - Memory Optimized: 2-64 vCores, up to 4 TB storage, 99.99% SLA. Higher memory-to-core ratio for memory-intensive workloads.

Flexible Server Compute Tiers: - Burstable (B-series): 1-20 vCores, up to 16 GB RAM per vCore. Suitable for development and low-traffic applications. Can burst CPU credits. - General Purpose (D-series): 2-64 vCores, up to 32 GB RAM per vCore. Balanced. - Memory Optimized (E-series): 2-64 vCores, up to 48 GB RAM per vCore. High memory.

Storage: Both options support up to 16 TB of storage (Flexible Server) or 4 TB (Single Server). Storage is auto-growing in increments of 5 GB up to the maximum. The exam may ask about the maximum storage size: 16 TB for Flexible Server, 4 TB for Single Server (though Single Server can go up to 16 TB with certain configurations? Actually, Single Server max is 4 TB for Basic and 16 TB for General Purpose and Memory Optimized? Double-check: Single Server max storage is 4 TB for Basic, 16 TB for General Purpose and Memory Optimized. Flexible Server max is 16 TB. The exam may test this distinction.

Azure Database for PostgreSQL automatically performs backups and stores them in geo-redundant storage (if configured). The backup retention period is configurable from 7 to 35 days. Backups are taken every 5 minutes (transaction log backups) for point-in-time restore. The full backup is taken once a day. Restore operations create a new server with the same configuration. The exam may ask about the default backup retention (7 days) and the maximum (35 days).

PostgreSQL on Azure supports two authentication methods: - Password authentication: Using PostgreSQL native passwords. - Azure Active Directory (AAD) authentication: For Single Server (preview) and Flexible Server (GA). AAD authentication allows you to use managed identities and conditional access policies.

Firewall rules can be set at the server level (IP firewall) or via VNet service endpoints (Single Server) or VNet integration (Flexible Server). SSL/TLS is enforced by default. The exam may ask about the default minimum TLS version (1.2).

PostgreSQL's extensibility is a key feature. Azure Database for PostgreSQL supports many popular extensions, including: - postgis for geospatial data - pg_stat_statements for query performance tracking - uuid-ossp for UUID generation - pgcrypto for cryptographic functions - hstore for key-value store - pg_buffercache for buffer cache analysis

The exam may ask which extensions are available out-of-the-box. You should know that postgis is a common extension for location-based queries.

Scaling compute (vCores) and storage can be done with minimal downtime (typically a few minutes). For Flexible Server, you can also scale storage online without downtime. The exam may ask about the scalability limits: up to 64 vCores and 16 TB storage.

Azure provides several tools for migrating PostgreSQL databases to Azure: - Azure Database Migration Service (DMS): Supports online and offline migrations from on-premises or other clouds. - pg_dump / pg_restore: Traditional PostgreSQL tools for logical backup and restore. - Azure Data Factory: For bulk data movement.

The exam may ask which tool is suitable for minimal downtime migration (DMS with online mode).

Pricing is based on compute (vCores), storage (GB), and backup storage (GB). You can choose between Provisioned (fixed compute) and Serverless (Flexible Server only, which automatically scales compute based on workload). The exam may ask about the serverless compute tier: it pauses during inactivity (after 60 minutes of no connections) and resumes on demand, charging only for the compute used.

Flexible Server includes built-in support for PgBouncer, a connection pooler for PostgreSQL. This helps reduce connection overhead by reusing connections. Single Server does not include PgBouncer natively but you can install it on a VM.

Azure Monitor provides metrics like CPU percentage, storage percentage, IOPS, and connections. The exam may ask about the metric for tracking storage usage (storage_percent). Alerts can be set up based on metric thresholds.

While the DP-900 exam does not require CLI commands, understanding the following can help:

# Create a Flexible Server (example)
az postgres flexible-server create \
  --name mypostgresqlserver \
  --resource-group myResourceGroup \
  --location eastus \
  --admin-user myadmin \
  --admin-password <password> \
  --sku-name Standard_D2s_v3 \
  --tier GeneralPurpose \
  --public-access 0.0.0.0/0

-- Enable an extension
CREATE EXTENSION IF NOT EXISTS postgis;

| Feature | Single Server | Flexible Server | |---------|---------------|-----------------| | Compute tiers | Basic, General Purpose, Memory Optimized | Burstable, General Purpose, Memory Optimized | | Max storage | 4 TB (Basic), 16 TB (others) | 16 TB | | High availability | Built-in (99.99% SLA) | Zone-redundant HA (99.99% SLA) | | VNet integration | Service endpoints | Private DNS VNet integration | | Stop/start | Not supported | Supported | | Serverless compute | Not supported | Supported | | PgBouncer | Not built-in | Built-in | | Retirement date | March 28, 2023 | Active |

The exam may ask which deployment option is recommended for new workloads (Flexible Server).

A logistics company uses Azure Database for PostgreSQL with the PostGIS extension to store and query delivery routes, customer locations, and warehouse coordinates. They chose Flexible Server for its zone-redundant HA and ability to scale storage online. The database handles millions of spatial queries per day, such as 'find nearest warehouse to a given GPS coordinate'. They use the ST_DWithin function for proximity searches. The database is deployed in the East US region with a read replica in West Europe for disaster recovery. They monitor replication lag using the replication_lag metric and have alerts set for lag > 5 seconds. They also use PgBouncer to manage thousands of concurrent connections from microservices. A common misconfiguration is not enabling the PostGIS extension during initial setup, leading to failed queries; it must be enabled via CREATE EXTENSION postgis;. Performance issues arise when spatial indexes are missing; they use GiST indexes on geometry columns. The database is backed up with 35-day retention and geo-redundancy. They perform quarterly dry-run restores to ensure RTO is met.

A SaaS provider offers a multi-tenant application where each tenant has a separate PostgreSQL database. They use Azure Database for PostgreSQL Flexible Server with the serverless compute tier to handle variable workloads. During peak hours, the server automatically scales up to 16 vCores; during off-hours, it scales down and eventually pauses after 60 minutes of inactivity. This reduces costs significantly. They use PgBouncer for connection pooling to avoid connection limits. Each tenant's database is created dynamically via SQL. They use Azure Active Directory authentication to allow tenant admins to manage their own databases. A challenge they faced was the serverless compute's cold start latency (around 30 seconds) when resuming from pause. To mitigate, they keep a minimum number of connections open to prevent pause. They also use read replicas for reporting workloads. The exam-relevant point: serverless compute is only available in Flexible Server, not Single Server.

A financial services company migrates a 2 TB PostgreSQL database from an on-premises data center to Azure. They use Azure Database Migration Service (DMS) for an online migration with minimal downtime. The source database is PostgreSQL 11, target is Flexible Server with PostgreSQL 13. They perform a pre-migration assessment to check for compatibility issues, such as deprecated functions. During migration, DMS continuously syncs changes using logical replication. The cutover takes less than 5 minutes. Post-migration, they enable zone-redundant HA and configure automated backups with 35-day retention. They also set up diagnostic settings to stream logs to Azure Log Analytics for auditing. A common mistake is not testing the target server's performance tier; they initially chose a General Purpose tier but had to scale up to Memory Optimized due to high memory requirements. The exam may ask about the recommended migration tool for minimal downtime (DMS with online mode).