Azure Resource Manager Postgresql Dotnet

1---
2name: azure-resource-manager-postgresql-dotnet
3description: |
4  Azure PostgreSQL Flexible Server SDK for .NET. Database management for PostgreSQL Flexible Server deployments. Use for creating servers, databases, firewall rules, configurations, backups, and high availability. Triggers: "PostgreSQL", "PostgreSqlFlexibleServer", "PostgreSQL Flexible Server", "Azure Database for PostgreSQL", "PostgreSQL database management", "PostgreSQL firewall", "PostgreSQL backup", "Postgres".
5package: Azure.ResourceManager.PostgreSql
6---
7 
8# Azure.ResourceManager.PostgreSql (.NET)
9 
10Azure Resource Manager SDK for managing PostgreSQL Flexible Server deployments.
11 
12## Installation
13 
14```bash
15dotnet add package Azure.ResourceManager.PostgreSql
16dotnet add package Azure.Identity
17```
18 
19**Current Version**: v1.2.0 (GA)  
20**API Version**: 2023-12-01-preview
21 
22> **Note**: This skill focuses on PostgreSQL Flexible Server. Single Server is deprecated and scheduled for retirement.
23 
24## Environment Variables
25 
26```bash
27AZURE_SUBSCRIPTION_ID=<your-subscription-id>
28AZURE_RESOURCE_GROUP=<your-resource-group>
29AZURE_POSTGRESQL_SERVER_NAME=<your-postgresql-server>
30```
31 
32## Authentication
33 
34```csharp
35using Azure.Identity;
36using Azure.ResourceManager;
37using Azure.ResourceManager.PostgreSql;
38using Azure.ResourceManager.PostgreSql.FlexibleServers;
39 
40ArmClient client = new ArmClient(new DefaultAzureCredential());
41```
42 
43## Resource Hierarchy
44 
45```
46Subscription
47└── ResourceGroup
48    └── PostgreSqlFlexibleServer              # PostgreSQL Flexible Server instance
49        ├── PostgreSqlFlexibleServerDatabase  # Database within the server
50        ├── PostgreSqlFlexibleServerFirewallRule # IP firewall rules
51        ├── PostgreSqlFlexibleServerConfiguration # Server parameters
52        ├── PostgreSqlFlexibleServerBackup    # Backup information
53        ├── PostgreSqlFlexibleServerActiveDirectoryAdministrator # Entra ID admin
54        └── PostgreSqlFlexibleServerVirtualEndpoint # Read replica endpoints
55```
56 
57## Core Workflows
58 
59### 1. Create PostgreSQL Flexible Server
60 
61```csharp
62using Azure.ResourceManager.PostgreSql.FlexibleServers;
63using Azure.ResourceManager.PostgreSql.FlexibleServers.Models;
64 
65ResourceGroupResource resourceGroup = await client
66    .GetDefaultSubscriptionAsync()
67    .Result
68    .GetResourceGroupAsync("my-resource-group");
69 
70PostgreSqlFlexibleServerCollection servers = resourceGroup.GetPostgreSqlFlexibleServers();
71 
72PostgreSqlFlexibleServerData data = new PostgreSqlFlexibleServerData(AzureLocation.EastUS)
73{
74    Sku = new PostgreSqlFlexibleServerSku("Standard_D2ds_v4", PostgreSqlFlexibleServerSkuTier.GeneralPurpose),
75    AdministratorLogin = "pgadmin",
76    AdministratorLoginPassword = "YourSecurePassword123!",
77    Version = PostgreSqlFlexibleServerVersion.Ver16,
78    Storage = new PostgreSqlFlexibleServerStorage
79    {
80        StorageSizeInGB = 128,
81        AutoGrow = StorageAutoGrow.Enabled,
82        Tier = PostgreSqlStorageTierName.P30
83    },
84    Backup = new PostgreSqlFlexibleServerBackupProperties
85    {
86        BackupRetentionDays = 7,
87        GeoRedundantBackup = PostgreSqlFlexibleServerGeoRedundantBackupEnum.Disabled
88    },
89    HighAvailability = new PostgreSqlFlexibleServerHighAvailability
90    {
91        Mode = PostgreSqlFlexibleServerHighAvailabilityMode.ZoneRedundant,
92        StandbyAvailabilityZone = "2"
93    },
94    AvailabilityZone = "1",
95    AuthConfig = new PostgreSqlFlexibleServerAuthConfig
96    {
97        ActiveDirectoryAuth = PostgreSqlFlexibleServerActiveDirectoryAuthEnum.Enabled,
98        PasswordAuth = PostgreSqlFlexibleServerPasswordAuthEnum.Enabled
99    }
100};
101 
102ArmOperation<PostgreSqlFlexibleServerResource> operation = await servers
103    .CreateOrUpdateAsync(WaitUntil.Completed, "my-postgresql-server", data);
104 
105PostgreSqlFlexibleServerResource server = operation.Value;
106Console.WriteLine($"Server created: {server.Data.FullyQualifiedDomainName}");
107```
108 
109### 2. Create Database
110 
111```csharp
112PostgreSqlFlexibleServerResource server = await resourceGroup
113    .GetPostgreSqlFlexibleServerAsync("my-postgresql-server");
114 
115PostgreSqlFlexibleServerDatabaseCollection databases = server.GetPostgreSqlFlexibleServerDatabases();
116 
117PostgreSqlFlexibleServerDatabaseData dbData = new PostgreSqlFlexibleServerDatabaseData
118{
119    Charset = "UTF8",
120    Collation = "en_US.utf8"
121};
122 
123ArmOperation<PostgreSqlFlexibleServerDatabaseResource> operation = await databases
124    .CreateOrUpdateAsync(WaitUntil.Completed, "myappdb", dbData);
125 
126PostgreSqlFlexibleServerDatabaseResource database = operation.Value;
127Console.WriteLine($"Database created: {database.Data.Name}");
128```
129 
130### 3. Configure Firewall Rules
131 
132```csharp
133PostgreSqlFlexibleServerFirewallRuleCollection firewallRules = server.GetPostgreSqlFlexibleServerFirewallRules();
134 
135// Allow specific IP range
136PostgreSqlFlexibleServerFirewallRuleData ruleData = new PostgreSqlFlexibleServerFirewallRuleData
137{
138    StartIPAddress = System.Net.IPAddress.Parse("10.0.0.1"),
139    EndIPAddress = System.Net.IPAddress.Parse("10.0.0.255")
140};
141 
142ArmOperation<PostgreSqlFlexibleServerFirewallRuleResource> operation = await firewallRules
143    .CreateOrUpdateAsync(WaitUntil.Completed, "allow-internal", ruleData);
144 
145// Allow Azure services
146PostgreSqlFlexibleServerFirewallRuleData azureServicesRule = new PostgreSqlFlexibleServerFirewallRuleData
147{
148    StartIPAddress = System.Net.IPAddress.Parse("0.0.0.0"),
149    EndIPAddress = System.Net.IPAddress.Parse("0.0.0.0")
150};
151 
152await firewallRules.CreateOrUpdateAsync(WaitUntil.Completed, "AllowAllAzureServicesAndResourcesWithinAzureIps", azureServicesRule);
153```
154 
155### 4. Update Server Configuration
156 
157```csharp
158PostgreSqlFlexibleServerConfigurationCollection configurations = server.GetPostgreSqlFlexibleServerConfigurations();
159 
160// Get current configuration
161PostgreSqlFlexibleServerConfigurationResource config = await configurations
162    .GetAsync("max_connections");
163 
164// Update configuration
165PostgreSqlFlexibleServerConfigurationData configData = new PostgreSqlFlexibleServerConfigurationData
166{
167    Value = "500",
168    Source = "user-override"
169};
170 
171ArmOperation<PostgreSqlFlexibleServerConfigurationResource> operation = await configurations
172    .CreateOrUpdateAsync(WaitUntil.Completed, "max_connections", configData);
173 
174// Common PostgreSQL configurations to tune
175string[] commonParams = { 
176    "max_connections", 
177    "shared_buffers", 
178    "work_mem", 
179    "maintenance_work_mem",
180    "effective_cache_size",
181    "log_min_duration_statement"
182};
183```
184 
185### 5. Configure Entra ID Administrator
186 
187```csharp
188PostgreSqlFlexibleServerActiveDirectoryAdministratorCollection admins = 
189    server.GetPostgreSqlFlexibleServerActiveDirectoryAdministrators();
190 
191PostgreSqlFlexibleServerActiveDirectoryAdministratorData adminData = 
192    new PostgreSqlFlexibleServerActiveDirectoryAdministratorData
193{
194    PrincipalType = PostgreSqlFlexibleServerPrincipalType.User,
195    PrincipalName = "aad-admin@contoso.com",
196    TenantId = Guid.Parse("<tenant-id>")
197};
198 
199ArmOperation<PostgreSqlFlexibleServerActiveDirectoryAdministratorResource> operation = await admins
200    .CreateOrUpdateAsync(WaitUntil.Completed, "<entra-object-id>", adminData);
201```
202 
203### 6. List and Manage Servers
204 
205```csharp
206// List servers in resource group
207await foreach (PostgreSqlFlexibleServerResource server in resourceGroup.GetPostgreSqlFlexibleServers())
208{
209    Console.WriteLine($"Server: {server.Data.Name}");
210    Console.WriteLine($"  FQDN: {server.Data.FullyQualifiedDomainName}");
211    Console.WriteLine($"  Version: {server.Data.Version}");
212    Console.WriteLine($"  State: {server.Data.State}");
213    Console.WriteLine($"  SKU: {server.Data.Sku.Name} ({server.Data.Sku.Tier})");
214    Console.WriteLine($"  HA: {server.Data.HighAvailability?.Mode}");
215}
216 
217// List databases in server
218await foreach (PostgreSqlFlexibleServerDatabaseResource db in server.GetPostgreSqlFlexibleServerDatabases())
219{
220    Console.WriteLine($"Database: {db.Data.Name}");
221}
222```
223 
224### 7. Backup and Point-in-Time Restore
225 
226```csharp
227// List available backups
228await foreach (PostgreSqlFlexibleServerBackupResource backup in server.GetPostgreSqlFlexibleServerBackups())
229{
230    Console.WriteLine($"Backup: {backup.Data.Name}");
231    Console.WriteLine($"  Type: {backup.Data.BackupType}");
232    Console.WriteLine($"  Completed: {backup.Data.CompletedOn}");
233}
234 
235// Point-in-time restore
236PostgreSqlFlexibleServerData restoreData = new PostgreSqlFlexibleServerData(AzureLocation.EastUS)
237{
238    CreateMode = PostgreSqlFlexibleServerCreateMode.PointInTimeRestore,
239    SourceServerResourceId = server.Id,
240    PointInTimeUtc = DateTimeOffset.UtcNow.AddHours(-2)
241};
242 
243ArmOperation<PostgreSqlFlexibleServerResource> operation = await servers
244    .CreateOrUpdateAsync(WaitUntil.Completed, "my-postgresql-restored", restoreData);
245```
246 
247### 8. Create Read Replica
248 
249```csharp
250PostgreSqlFlexibleServerData replicaData = new PostgreSqlFlexibleServerData(AzureLocation.WestUS)
251{
252    CreateMode = PostgreSqlFlexibleServerCreateMode.Replica,
253    SourceServerResourceId = server.Id,
254    Sku = new PostgreSqlFlexibleServerSku("Standard_D2ds_v4", PostgreSqlFlexibleServerSkuTier.GeneralPurpose)
255};
256 
257ArmOperation<PostgreSqlFlexibleServerResource> operation = await servers
258    .CreateOrUpdateAsync(WaitUntil.Completed, "my-postgresql-replica", replicaData);
259```
260 
261### 9. Stop and Start Server
262 
263```csharp
264PostgreSqlFlexibleServerResource server = await resourceGroup
265    .GetPostgreSqlFlexibleServerAsync("my-postgresql-server");
266 
267// Stop server (saves costs when not in use)
268await server.StopAsync(WaitUntil.Completed);
269 
270// Start server
271await server.StartAsync(WaitUntil.Completed);
272 
273// Restart server
274await server.RestartAsync(WaitUntil.Completed, new PostgreSqlFlexibleServerRestartParameter
275{
276    RestartWithFailover = true,
277    FailoverMode = PostgreSqlFlexibleServerFailoverMode.PlannedFailover
278});
279```
280 
281### 10. Update Server (Scale)
282 
283```csharp
284PostgreSqlFlexibleServerResource server = await resourceGroup
285    .GetPostgreSqlFlexibleServerAsync("my-postgresql-server");
286 
287PostgreSqlFlexibleServerPatch patch = new PostgreSqlFlexibleServerPatch
288{
289    Sku = new PostgreSqlFlexibleServerSku("Standard_D4ds_v4", PostgreSqlFlexibleServerSkuTier.GeneralPurpose),
290    Storage = new PostgreSqlFlexibleServerStorage
291    {
292        StorageSizeInGB = 256,
293        Tier = PostgreSqlStorageTierName.P40
294    }
295};
296 
297ArmOperation<PostgreSqlFlexibleServerResource> operation = await server
298    .UpdateAsync(WaitUntil.Completed, patch);
299```
300 
301### 11. Delete Server
302 
303```csharp
304PostgreSqlFlexibleServerResource server = await resourceGroup
305    .GetPostgreSqlFlexibleServerAsync("my-postgresql-server");
306 
307await server.DeleteAsync(WaitUntil.Completed);
308```
309 
310## Key Types Reference
311 
312| Type | Purpose |
313|------|---------|
314| `PostgreSqlFlexibleServerResource` | Flexible Server instance |
315| `PostgreSqlFlexibleServerData` | Server configuration data |
316| `PostgreSqlFlexibleServerCollection` | Collection of servers |
317| `PostgreSqlFlexibleServerDatabaseResource` | Database within server |
318| `PostgreSqlFlexibleServerFirewallRuleResource` | IP firewall rule |
319| `PostgreSqlFlexibleServerConfigurationResource` | Server parameter |
320| `PostgreSqlFlexibleServerBackupResource` | Backup metadata |
321| `PostgreSqlFlexibleServerActiveDirectoryAdministratorResource` | Entra ID admin |
322| `PostgreSqlFlexibleServerSku` | SKU (compute tier + size) |
323| `PostgreSqlFlexibleServerStorage` | Storage configuration |
324| `PostgreSqlFlexibleServerHighAvailability` | HA configuration |
325| `PostgreSqlFlexibleServerBackupProperties` | Backup settings |
326| `PostgreSqlFlexibleServerAuthConfig` | Authentication settings |
327 
328## SKU Tiers
329 
330| Tier | Use Case | SKU Examples |
331|------|----------|--------------|
332| `Burstable` | Dev/test, light workloads | Standard_B1ms, Standard_B2s |
333| `GeneralPurpose` | Production workloads | Standard_D2ds_v4, Standard_D4ds_v4 |
334| `MemoryOptimized` | High memory requirements | Standard_E2ds_v4, Standard_E4ds_v4 |
335 
336## PostgreSQL Versions
337 
338| Version | Enum Value |
339|---------|------------|
340| PostgreSQL 11 | `Ver11` |
341| PostgreSQL 12 | `Ver12` |
342| PostgreSQL 13 | `Ver13` |
343| PostgreSQL 14 | `Ver14` |
344| PostgreSQL 15 | `Ver15` |
345| PostgreSQL 16 | `Ver16` |
346 
347## High Availability Modes
348 
349| Mode | Description |
350|------|-------------|
351| `Disabled` | No HA (single server) |
352| `SameZone` | HA within same availability zone |
353| `ZoneRedundant` | HA across availability zones |
354 
355## Best Practices
356 
3571. **Use Flexible Server** — Single Server is deprecated
3582. **Enable zone-redundant HA** — For production workloads
3593. **Use DefaultAzureCredential** — Prefer over connection strings
3604. **Configure Entra ID authentication** — More secure than SQL auth alone
3615. **Enable both auth methods** — Entra ID + password for flexibility
3626. **Set appropriate backup retention** — 7-35 days based on compliance
3637. **Use private endpoints** — For secure network access
3648. **Tune server parameters** — Based on workload characteristics
3659. **Use read replicas** — For read-heavy workloads
36610. **Stop dev/test servers** — Save costs when not in use
367 
368## Error Handling
369 
370```csharp
371using Azure;
372 
373try
374{
375    ArmOperation<PostgreSqlFlexibleServerResource> operation = await servers
376        .CreateOrUpdateAsync(WaitUntil.Completed, "my-postgresql", data);
377}
378catch (RequestFailedException ex) when (ex.Status == 409)
379{
380    Console.WriteLine("Server already exists");
381}
382catch (RequestFailedException ex) when (ex.Status == 400)
383{
384    Console.WriteLine($"Invalid configuration: {ex.Message}");
385}
386catch (RequestFailedException ex)
387{
388    Console.WriteLine($"Azure error: {ex.Status} - {ex.Message}");
389}
390```
391 
392## Connection String
393 
394After creating the server, connect using:
395 
396```csharp
397// Npgsql connection string
398string connectionString = $"Host={server.Data.FullyQualifiedDomainName};" +
399    "Database=myappdb;" +
400    "Username=pgadmin;" +
401    "Password=YourSecurePassword123!;" +
402    "SSL Mode=Require;Trust Server Certificate=true;";
403 
404// With Entra ID token (recommended)
405var credential = new DefaultAzureCredential();
406var token = await credential.GetTokenAsync(
407    new TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net/.default" }));
408 
409string connectionString = $"Host={server.Data.FullyQualifiedDomainName};" +
410    "Database=myappdb;" +
411    $"Username=aad-admin@contoso.com;" +
412    $"Password={token.Token};" +
413    "SSL Mode=Require;";
414```
415 
416## Related SDKs
417 
418| SDK | Purpose | Install |
419|-----|---------|---------|
420| `Azure.ResourceManager.PostgreSql` | PostgreSQL management (this SDK) | `dotnet add package Azure.ResourceManager.PostgreSql` |
421| `Azure.ResourceManager.MySql` | MySQL management | `dotnet add package Azure.ResourceManager.MySql` |
422| `Npgsql` | PostgreSQL data access | `dotnet add package Npgsql` |
423| `Npgsql.EntityFrameworkCore.PostgreSQL` | EF Core provider | `dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL` |
424 
425## Reference Links
426 
427| Resource | URL |
428|----------|-----|
429| NuGet Package | https://www.nuget.org/packages/Azure.ResourceManager.PostgreSql |
430| API Reference | https://learn.microsoft.com/dotnet/api/azure.resourcemanager.postgresql |
431| Product Documentation | https://learn.microsoft.com/azure/postgresql/flexible-server/ |
432| GitHub Source | https://github.com/Azure/azure-sdk-for-net/tree/main/sdk/postgresql/Azure.ResourceManager.PostgreSql |
433