azure-resource-manager-postgresql-dotnet

Azure.ResourceManager.PostgreSql（.NET）

用于管理 PostgreSQL Flexible Server 部署的 Azure Resource Manager SDK。

安装

dotnet add package Azure.ResourceManager.PostgreSql
dotnet add package Azure.Identity

示例基线：SDK v1.2.0 / API 2023-12-01-preview。这并不表示当前版本；请为目标订阅选择并固定受支持的 SDK/API 版本，并使用该版本编译这些代码片段。

注意：本技能聚焦于 PostgreSQL Flexible Server。Single Server 已退役；请使用 Flexible Server，并检查当前区域和版本支持情况。

环境变量

AZURE_SUBSCRIPTION_ID=<your-subscription-id>
AZURE_RESOURCE_GROUP=<your-resource-group>
AZURE_POSTGRESQL_SERVER_NAME=<your-postgresql-server>

身份验证

using Azure.Identity;
using Azure.ResourceManager;
using Azure.ResourceManager.PostgreSql;
using Azure.ResourceManager.PostgreSql.FlexibleServers;

ArmClient client = new ArmClient(new DefaultAzureCredential());

资源层级

Subscription
└── ResourceGroup
    └── PostgreSqlFlexibleServer              # PostgreSQL Flexible Server instance
        ├── PostgreSqlFlexibleServerDatabase  # Database within the server
        ├── PostgreSqlFlexibleServerFirewallRule # IP firewall rules
        ├── PostgreSqlFlexibleServerConfiguration # Server parameters
        ├── PostgreSqlFlexibleServerBackup    # Backup information
        ├── PostgreSqlFlexibleServerActiveDirectoryAdministrator # Entra ID admin
        └── PostgreSqlFlexibleServerVirtualEndpoint # Read replica endpoints

核心工作流

以下是彼此独立的示例代码片段，并非一个可编译的程序。请根据已安装的 SDK 按需添加导入项（Azure、Azure.Core、资源组类型）。在预配或调整大小之前，请确认订阅/资源 ID、成本、可用性和回滚方案；删除之前，请确认存在可恢复的备份，并核实目标无误。ARM 凭据用于管理基础设施；数据库连接则需要单独配置已获授权的数据库角色。对于新打开的连接池连接，请使用 Npgsql 的密码提供程序 API 刷新 Entra 令牌，不要无限期保留同一个令牌。

1. 创建 PostgreSQL Flexible Server

using System;
using Azure.ResourceManager.PostgreSql.FlexibleServers;
using Azure.ResourceManager.PostgreSql.FlexibleServers.Models;

var subscription = await client.GetDefaultSubscriptionAsync();
ResourceGroupResource resourceGroup = (await subscription.GetResourceGroupAsync("my-resource-group")).Value;

PostgreSqlFlexibleServerCollection servers = resourceGroup.GetPostgreSqlFlexibleServers();

PostgreSqlFlexibleServerData data = new PostgreSqlFlexibleServerData(AzureLocation.EastUS)
{
    Sku = new PostgreSqlFlexibleServerSku("Standard_D2ds_v4", PostgreSqlFlexibleServerSkuTier.GeneralPurpose),
    AdministratorLogin = "pgadmin",
    AdministratorLoginPassword = Environment.GetEnvironmentVariable("POSTGRES_ADMIN_PASSWORD") ?? throw new InvalidOperationException("POSTGRES_ADMIN_PASSWORD is required"),
    Version = PostgreSqlFlexibleServerVersion.Ver16,
    Storage = new PostgreSqlFlexibleServerStorage
    {
        StorageSizeInGB = 128,
        AutoGrow = StorageAutoGrow.Enabled,
        Tier = PostgreSqlStorageTierName.P30
    },
    Backup = new PostgreSqlFlexibleServerBackupProperties
    {
        BackupRetentionDays = 7,
        GeoRedundantBackup = PostgreSqlFlexibleServerGeoRedundantBackupEnum.Disabled
    },
    HighAvailability = new PostgreSqlFlexibleServerHighAvailability
    {
        Mode = PostgreSqlFlexibleServerHighAvailabilityMode.ZoneRedundant,
        StandbyAvailabilityZone = "2"
    },
    AvailabilityZone = "1",
    AuthConfig = new PostgreSqlFlexibleServerAuthConfig
    {
        ActiveDirectoryAuth = PostgreSqlFlexibleServerActiveDirectoryAuthEnum.Enabled,
        PasswordAuth = PostgreSqlFlexibleServerPasswordAuthEnum.Enabled
    }
};

ArmOperation<PostgreSqlFlexibleServerResource> operation = await servers
    .CreateOrUpdateAsync(WaitUntil.Completed, "my-postgresql-server", data);

PostgreSqlFlexibleServerResource server = operation.Value;
Console.WriteLine($"Server created: {server.Data.FullyQualifiedDomainName}");

2. 创建数据库

PostgreSqlFlexibleServerResource server = await resourceGroup
    .GetPostgreSqlFlexibleServerAsync("my-postgresql-server");

PostgreSqlFlexibleServerDatabaseCollection databases = server.GetPostgreSqlFlexibleServerDatabases();

PostgreSqlFlexibleServerDatabaseData dbData = new PostgreSqlFlexibleServerDatabaseData
{
    Charset = "UTF8",
    Collation = "en_US.utf8"
};

ArmOperation<PostgreSqlFlexibleServerDatabaseResource> operation = await databases
    .CreateOrUpdateAsync(WaitUntil.Completed, "myappdb", dbData);

PostgreSqlFlexibleServerDatabaseResource database = operation.Value;
Console.WriteLine($"Database created: {database.Data.Name}");

3. 配置防火墙规则

PostgreSqlFlexibleServerFirewallRuleCollection firewallRules = server.GetPostgreSqlFlexibleServerFirewallRules();

// Public-access firewall rules require the client’s public egress IP, not its private VNet address.
// Example documentation range only; replace with the approved public egress range.
PostgreSqlFlexibleServerFirewallRuleData ruleData = new PostgreSqlFlexibleServerFirewallRuleData
{
    StartIPAddress = System.Net.IPAddress.Parse("203.0.113.10"),
    EndIPAddress = System.Net.IPAddress.Parse("203.0.113.10")
};

ArmOperation<PostgreSqlFlexibleServerFirewallRuleResource> operation = await firewallRules
    .CreateOrUpdateAsync(WaitUntil.Completed, "allow-internal", ruleData);

// OPTIONAL broad exception: 0.0.0.0 allows Azure services from OTHER customers’ subscriptions too.
// Do not apply without explicit approval; prefer specific egress IPs or private networking.
PostgreSqlFlexibleServerFirewallRuleData azureServicesRule = new PostgreSqlFlexibleServerFirewallRuleData
{
    StartIPAddress = System.Net.IPAddress.Parse("0.0.0.0"),
    EndIPAddress = System.Net.IPAddress.Parse("0.0.0.0")
};

await firewallRules.CreateOrUpdateAsync(WaitUntil.Completed, "AllowAllAzureServicesAndResourcesWithinAzureIps", azureServicesRule);

4. 更新服务端配置

PostgreSqlFlexibleServerConfigurationCollection configurations = server.GetPostgreSqlFlexibleServerConfigurations();

// Get current configuration
PostgreSqlFlexibleServerConfigurationResource config = await configurations
    .GetAsync("max_connections");

// Update configuration
PostgreSqlFlexibleServerConfigurationData configData = new PostgreSqlFlexibleServerConfigurationData
{
    Value = "500",
    Source = "user-override"
};

ArmOperation<PostgreSqlFlexibleServerConfigurationResource> operation = await configurations
    .CreateOrUpdateAsync(WaitUntil.Completed, "max_connections", configData);

// Common PostgreSQL configurations to tune
string[] commonParams = { 
    "max_connections", 
    "shared_buffers", 
    "work_mem", 
    "maintenance_work_mem",
    "effective_cache_size",
    "log_min_duration_statement"
};

5. 配置 Entra ID 管理员

PostgreSqlFlexibleServerActiveDirectoryAdministratorCollection admins = 
    server.GetPostgreSqlFlexibleServerActiveDirectoryAdministrators();

PostgreSqlFlexibleServerActiveDirectoryAdministratorData adminData = 
    new PostgreSqlFlexibleServerActiveDirectoryAdministratorData
{
    PrincipalType = PostgreSqlFlexibleServerPrincipalType.User,
    PrincipalName = "[email protected]",
    TenantId = Guid.Parse("<tenant-id>")
};

ArmOperation<PostgreSqlFlexibleServerActiveDirectoryAdministratorResource> operation = await admins
    .CreateOrUpdateAsync(WaitUntil.Completed, "<entra-object-id>", adminData);

6. 列出和管理服务端

// List servers in resource group
await foreach (PostgreSqlFlexibleServerResource server in resourceGroup.GetPostgreSqlFlexibleServers())
{
    Console.WriteLine($"Server: {server.Data.Name}");
    Console.WriteLine($"  FQDN: {server.Data.FullyQualifiedDomainName}");
    Console.WriteLine($"  Version: {server.Data.Version}");
    Console.WriteLine($"  State: {server.Data.State}");
    Console.WriteLine($"  SKU: {server.Data.Sku.Name} ({server.Data.Sku.Tier})");
    Console.WriteLine($"  HA: {server.Data.HighAvailability?.Mode}");
}

// List databases in server
await foreach (PostgreSqlFlexibleServerDatabaseResource db in server.GetPostgreSqlFlexibleServerDatabases())
{
    Console.WriteLine($"Database: {db.Data.Name}");
}

7. 备份和时间点还原

// List available backups
await foreach (PostgreSqlFlexibleServerBackupResource backup in server.GetPostgreSqlFlexibleServerBackups())
{
    Console.WriteLine($"Backup: {backup.Data.Name}");
    Console.WriteLine($"  Type: {backup.Data.BackupType}");
    Console.WriteLine($"  Completed: {backup.Data.CompletedOn}");
}

// Point-in-time restore
PostgreSqlFlexibleServerData restoreData = new PostgreSqlFlexibleServerData(AzureLocation.EastUS)
{
    CreateMode = PostgreSqlFlexibleServerCreateMode.PointInTimeRestore,
    SourceServerResourceId = server.Id,
    PointInTimeUtc = DateTimeOffset.UtcNow.AddHours(-2)
};

ArmOperation<PostgreSqlFlexibleServerResource> operation = await servers
    .CreateOrUpdateAsync(WaitUntil.Completed, "my-postgresql-restored", restoreData);

8. 创建只读副本

PostgreSqlFlexibleServerData replicaData = new PostgreSqlFlexibleServerData(AzureLocation.WestUS)
{
    CreateMode = PostgreSqlFlexibleServerCreateMode.Replica,
    SourceServerResourceId = server.Id,
    Sku = new PostgreSqlFlexibleServerSku("Standard_D2ds_v4", PostgreSqlFlexibleServerSkuTier.GeneralPurpose)
};

ArmOperation<PostgreSqlFlexibleServerResource> operation = await servers
    .CreateOrUpdateAsync(WaitUntil.Completed, "my-postgresql-replica", replicaData);

9. 停止和启动服务端

PostgreSqlFlexibleServerResource server = await resourceGroup
    .GetPostgreSqlFlexibleServerAsync("my-postgresql-server");

// Stop server (compute billing stops; storage/backup costs and automatic restart limits still apply)
await server.StopAsync(WaitUntil.Completed);

// Start server
await server.StartAsync(WaitUntil.Completed);

// Restart server
await server.RestartAsync(WaitUntil.Completed, new PostgreSqlFlexibleServerRestartParameter
{
    RestartWithFailover = true,
    FailoverMode = PostgreSqlFlexibleServerFailoverMode.PlannedFailover
});

10. 更新服务端（扩缩容）

PostgreSqlFlexibleServerResource server = await resourceGroup
    .GetPostgreSqlFlexibleServerAsync("my-postgresql-server");

PostgreSqlFlexibleServerPatch patch = new PostgreSqlFlexibleServerPatch
{
    Sku = new PostgreSqlFlexibleServerSku("Standard_D4ds_v4", PostgreSqlFlexibleServerSkuTier.GeneralPurpose),
    Storage = new PostgreSqlFlexibleServerStorage
    {
        StorageSizeInGB = 256,
        Tier = PostgreSqlStorageTierName.P40
    }
};

ArmOperation<PostgreSqlFlexibleServerResource> operation = await server
    .UpdateAsync(WaitUntil.Completed, patch);

11. 删除服务端

PostgreSqlFlexibleServerResource server = await resourceGroup
    .GetPostgreSqlFlexibleServerAsync("my-postgresql-server");

await server.DeleteAsync(WaitUntil.Completed);

关键类型参考

类型	用途
PostgreSqlFlexibleServerResource	Flexible Server 实例
PostgreSqlFlexibleServerData	服务端配置数据
PostgreSqlFlexibleServerCollection	服务端集合
PostgreSqlFlexibleServerDatabaseResource	服务端内的数据库
PostgreSqlFlexibleServerFirewallRuleResource	IP 防火墙规则
PostgreSqlFlexibleServerConfigurationResource	服务端参数
PostgreSqlFlexibleServerBackupResource	备份元数据
PostgreSqlFlexibleServerActiveDirectoryAdministratorResource	Entra ID 管理员
PostgreSqlFlexibleServerSku	SKU（计算层级 + 规格）
PostgreSqlFlexibleServerStorage	存储配置
PostgreSqlFlexibleServerHighAvailability	高可用性配置
PostgreSqlFlexibleServerBackupProperties	备份设置
PostgreSqlFlexibleServerAuthConfig	身份验证设置

SKU 层级

层级	用例	SKU 示例
Burstable	开发/测试、轻量工作负载	Standard_B1ms, Standard_B2s
GeneralPurpose	生产工作负载	Standard_D2ds_v4, Standard_D4ds_v4
MemoryOptimized	高内存需求	Standard_E2ds_v4, Standard_E4ds_v4

PostgreSQL 版本

版本	枚举值
PostgreSQL 11	Ver11
PostgreSQL 12	Ver12
PostgreSQL 13	Ver13
PostgreSQL 14	Ver14
PostgreSQL 15	Ver15
PostgreSQL 16	Ver16

高可用性模式

模式	描述
Disabled	无高可用性（单个服务端）
SameZone	在同一可用性区域内实现高可用性
ZoneRedundant	跨可用性区域实现高可用性

最佳实践

1. 使用 Flexible Server — Single Server 已弃用
2. 启用区域冗余 HA — 适用于生产工作负载
3. 使用 DefaultAzureCredential — 优先于连接字符串
4. 配置 Entra ID 身份验证 — 比单独使用 SQL 身份验证更安全
5. 审慎选择身份验证方式 — 在支持的情况下优先使用仅限 Entra 的身份验证；仅在有明确记录的需求时启用密码身份验证
6. 设置适当的备份保留期 — 根据合规要求设置为 7-35 天
7. 使用专用终结点 — 确保网络访问安全
8. 调整服务器参数 — 根据工作负载特征进行配置
9. 使用只读副本 — 适用于读取密集型工作负载
10. 停止开发/测试服务器 — 不使用时节省成本

错误处理

using Azure;

try
{
    ArmOperation<PostgreSqlFlexibleServerResource> operation = await servers
        .CreateOrUpdateAsync(WaitUntil.Completed, "my-postgresql", data);
}
catch (RequestFailedException ex) when (ex.Status == 409)
{
    Console.WriteLine("Server already exists");
}
catch (RequestFailedException ex) when (ex.Status == 400)
{
    Console.WriteLine($"Invalid configuration: {ex.Message}");
}
catch (RequestFailedException ex)
{
    Console.WriteLine($"Azure error: {ex.Status} - {ex.Message}");
}

连接字符串

创建服务器后，使用以下方式连接：

// Supply password from a secret provider; never log the resulting connection string.
// Npgsql connection string
string connectionString = $"Host={server.Data.FullyQualifiedDomainName};" +
    "Database=myappdb;" +
    "Username=pgadmin;" +
     $"Password={Environment.GetEnvironmentVariable("POSTGRES_ADMIN_PASSWORD") ?? throw new InvalidOperationException("Password required")};" +
    "SSL Mode=VerifyFull;";

// With Entra ID token (recommended)
var credential = new DefaultAzureCredential();
var token = await credential.GetTokenAsync(
    new TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net/.default" }));

string connectionString = $"Host={server.Data.FullyQualifiedDomainName};" +
    "Database=myappdb;" +
    $"[email protected];" +
    $"Password={token.Token};" +
    "SSL Mode=VerifyFull;";

相关 SDK

SDK	用途	安装
Azure.ResourceManager.PostgreSql	PostgreSQL 管理（此 SDK）	dotnet add package Azure.ResourceManager.PostgreSql
Azure.ResourceManager.MySql	MySQL 管理	dotnet add package Azure.ResourceManager.MySql
Npgsql	PostgreSQL 数据访问	dotnet add package Npgsql
Npgsql.EntityFrameworkCore.PostgreSQL	EF Core 提供程序	dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

参考链接

资源	URL
NuGet 包	https://www.nuget.org/packages/Azure.ResourceManager.PostgreSql
API 参考	https://learn.microsoft.com/dotnet/api/azure.resourcemanager.postgresql
产品文档	https://learn.microsoft.com/azure/postgresql/flexible-server/
GitHub 源码	https://github.com/Azure/azure-sdk-for-net/tree/main/sdk/postgresql/Azure.ResourceManager.PostgreSql

适用场景

当需要执行概述中描述的工作流或操作时，可使用此技能。

限制

• 仅当任务明确符合上述范围时，才使用此技能。
• 不要将输出视为可替代特定环境验证、测试或专家审查的内容。
• 如果缺少必需的输入、权限、安全边界或成功标准，请暂停并请求澄清。