Azure Database for PostgreSQL integration

Dette indhold er ikke tilgængeligt i dit sprog endnu.

Azure Database for PostgreSQL—Flexible Server is a relational database service based on the open-source Postgres database engine. It’s a fully managed database-as-a-service that can handle mission-critical workloads with predictable performance, security, high availability, and dynamic scalability. The Aspire Azure PostgreSQL integration provides a way to connect to existing Azure PostgreSQL databases, or create new instances from .NET with the docker.io/library/postgres container image.

Hosting integration

The Aspire Azure PostgreSQL hosting integration models a PostgreSQL flexible server and database as the AzurePostgresFlexibleServerResource and AzurePostgresFlexibleServerDatabaseResource types. Other types that are inherently available in the hosting integration are represented in the following resources:

PostgresServerResource
PostgresDatabaseResource
PgAdminContainerResource
PgWebContainerResource

To access these types and APIs for expressing them as resources in your AppHost project, install the 📦 Aspire.Hosting.Azure.PostgreSQL NuGet package:

aspire add azure-postgresql

Aspire CLI er interaktiv; vælg det passende søgeresultat når du bliver spurgt:

Select an integration to add:

> azure-postgresql (Aspire.Hosting.Azure.PostgreSQL)
> Other results listed as selectable options...

#:package Aspire.Hosting.Azure.PostgreSQL@*

<PackageReference Include="Aspire.Hosting.Azure.PostgreSQL" Version="*" />

Add an Azure Database for PostgreSQL resource

To add an Azure Database for PostgreSQL resource to your AppHost project, call the AddAzurePostgres method providing a name:

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddAzurePostgresFlexibleServer("postgres");
var postgresdb = postgres.AddDatabase("postgresdb");

var exampleProject = builder.AddProject<Projects.ExampleProject>()
    .WithReference(postgresdb);

// After adding all resources, run the app...

The preceding code adds an Azure PostgreSQL resource named postgres with a database named database to the AppHost project. The WithReference method passes the connection information to the ExampleProject project.

Connect to an existing Azure PostgreSQL flexible server

You might have an existing Azure Database for PostgreSQL service that you want to connect to. You can chain a call to annotate that your AzurePostgresResource is an existing resource:

var builder = DistributedApplication.CreateBuilder(args);

var existingPostgresName = builder.AddParameter("existingPostgresName");
var existingPostgresResourceGroup = builder.AddParameter("existingPostgresResourceGroup");

var postgres = builder.AddAzurePostgresFlexibleServer("postgres")
    .AsExisting(existingPostgresName, existingPostgresResourceGroup);

builder.AddProject<Projects.ExampleProject>()
    .WithReference(postgres);

// After adding all resources, run the app...

For more information on treating Azure PostgreSQL resources as existing resources, see Use existing Azure resources.

Run Azure PostgreSQL resource as a container

The Azure PostgreSQL hosting integration supports running the PostgreSQL server as a local container. This is beneficial for situations where you want to run the PostgreSQL server locally for development and testing purposes, avoiding the need to provision an Azure resource or connect to an existing Azure PostgreSQL server.

To run the PostgreSQL server as a container, call the RunAsContainer method:

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddAzurePostgresFlexibleServer("postgres")
    .RunAsContainer();

var postgresdb = postgres.AddDatabase("postgresdb");

var exampleProject = builder.AddProject<Projects.ExampleProject>()
    .WithReference(postgresdb);

The preceding code configures an Azure PostgreSQL Flexible Server resource to run locally in a container.

Configure the Azure PostgreSQL server to use password authentication

By default, the Azure PostgreSQL server is configured to use Microsoft Entra ID authentication. If you want to use password authentication, you can configure the server to use password authentication by calling the WithPasswordAuthentication method:

var builder = DistributedApplication.CreateBuilder(args);

var username = builder.AddParameter("username", secret: true);
var password = builder.AddParameter("password", secret: true);

var postgres = builder.AddAzurePostgresFlexibleServer("postgres")
    .WithPasswordAuthentication(username, password);

var postgresdb = postgres.AddDatabase("postgresdb");

var exampleProject = builder.AddProject<Projects.ExampleProject>()
    .WithReference(postgresdb);

The preceding code configures the Azure PostgreSQL server to use password authentication. The username and password parameters are added to the AppHost as parameters, and the WithPasswordAuthentication method is called to configure the Azure PostgreSQL server to use password authentication.

Provisioning-generated Bicep

If you’re new to Bicep, it’s a domain-specific language for defining Azure resources. With Aspire, you don’t need to write Bicep by hand, because the provisioning APIs generate Bicep for you. When you publish your app, the generated Bicep is output alongside the manifest file. When you add an Azure PostgreSQL resource, the following Bicep is generated:

@description('The location for the resource(s) to be deployed.')
param location string = resourceGroup().location

resource postgres_flexible 'Microsoft.DBforPostgreSQL/flexibleServers@2024-08-01' = {
  name: take('postgresflexible-${uniqueString(resourceGroup().id)}', 63)
  location: location
  properties: {
    authConfig: {
      activeDirectoryAuth: 'Enabled'
      passwordAuth: 'Disabled'
    }
    availabilityZone: '1'
    backup: {
      backupRetentionDays: 7
      geoRedundantBackup: 'Disabled'
    }
    highAvailability: {
      mode: 'Disabled'
    }
    storage: {
      storageSizeGB: 32
    }
    version: '16'
  }
  sku: {
    name: 'Standard_B1ms'
    tier: 'Burstable'
  }
  tags: {
    'aspire-resource-name': 'postgres-flexible'
  }
}

resource postgreSqlFirewallRule_AllowAllAzureIps 'Microsoft.DBforPostgreSQL/flexibleServers/firewallRules@2024-08-01' = {
  name: 'AllowAllAzureIps'
  properties: {
    endIpAddress: '0.0.0.0'
    startIpAddress: '0.0.0.0'
  }
  parent: postgres_flexible
}

output connectionString string = 'Host=${postgres_flexible.properties.fullyQualifiedDomainName}'

output name string = postgres_flexible.name

The preceding Bicep is a module that provisions an Azure PostgreSQL flexible server resource. Additionally, role assignments are created for the Azure resource in a separate module:

@description('The location for the resource(s) to be deployed.')
param location string = resourceGroup().location

param postgres_flexible_outputs_name string

param principalType string

param principalId string

param principalName string

resource postgres_flexible 'Microsoft.DBforPostgreSQL/flexibleServers@2024-08-01' existing = {
  name: postgres_flexible_outputs_name
}

resource postgres_flexible_admin 'Microsoft.DBforPostgreSQL/flexibleServers/administrators@2024-08-01' = {
  name: principalId
  properties: {
    principalName: principalName
    principalType: principalType
  }
  parent: postgres_flexible
}

In addition to the PostgreSQL flexible server, it also provisions an Azure Firewall rule to allow all Azure IP addresses. Finally, an administrator is created for the PostgreSQL server, and the connection string is outputted as an output variable. The generated Bicep is a starting point and is influenced by changes to the provisioning infrastructure in C#. Customizations to the Bicep file directly will be overwritten, so make changes through the C# provisioning APIs to ensure they are reflected in the generated files.

Customize provisioning infrastructure

All Aspire Azure resources are subclasses of the AzureProvisioningResource type. This type enables the customization of the generated Bicep by providing a fluent API to configure the Azure resources using the ConfigureInfrastructure API:

var builder = DistributedApplication.CreateBuilder(args);

builder.AddAzurePostgresFlexibleServer("postgres")
  .ConfigureInfrastructure(infra =>
  {
      var flexibleServer = infra.GetProvisionableResources()
                                .OfType<PostgreSqlFlexibleServer>()
                                .Single();

      flexibleServer.Sku = new PostgreSqlFlexibleServerSku
      {
          Tier = PostgreSqlFlexibleServerSkuTier.Burstable,
      };
      flexibleServer.HighAvailability = new PostgreSqlFlexibleServerHighAvailability
      {
          Mode = PostgreSqlFlexibleServerHighAvailabilityMode.ZoneRedundant,
          StandbyAvailabilityZone = "2",
      };
      flexibleServer.Tags.Add("ExampleKey", "Example value");
  });

The preceding code:

Chains a call to the ConfigureInfrastructure API:
- The infra parameter is an instance of the AzureResourceInfrastructure type.
- The provisionable resources are retrieved by calling the GetProvisionableResources method.
- The single PostgreSqlFlexibleServer is retrieved.
- The sku is set with PostgreSqlFlexibleServerSkuTier.Burstable.
- The high availability properties are set with PostgreSqlFlexibleServerHighAvailabilityMode.ZoneRedundant in standby availability zone "2".
- A tag is added to the flexible server with a key of ExampleKey and a value of Example value.

There are many more configuration options available to customize the PostgreSQL resource. For more information, see Azure.Provisioning customization.

Client integration

To get started with the Aspire Azure PostgreSQL client integration, install the 📦 Aspire.Azure.Npgsql NuGet package in the client-consuming project, that is, the project for the application that uses the PostgreSQL client. The PostgreSQL client integration registers an NpgsqlDataSource instance that you can use to interact with PostgreSQL.

dotnet add package Aspire.Azure.Npgsql

#:package Aspire.Azure.Npgsql@*

<PackageReference Include="Aspire.Azure.Npgsql" Version="*" />

Add PostgreSQL client

In the Program.cs file of your client-consuming project, call the AddNpgsqlDataSource extension method to register a NpgsqlDataSource for use via the dependency injection container. The method takes a connection name parameter:

builder.AddAzureNpgsqlDataSource(connectionName: "postgresdb");

The preceding code snippet demonstrates how to use the AddAzureNpgsqlDataSource method to register an NpgsqlDataSource instance that uses Azure authentication (Microsoft Entra ID). This "postgresdb" connection name corresponds to a connection string configuration value.

After adding NpgsqlDataSource to the builder, you can get the NpgsqlDataSource instance using dependency injection. For example, to retrieve your data source object from an example service define it as a constructor parameter and ensure the ExampleService class is registered with the dependency injection container:

public class ExampleService(NpgsqlDataSource dataSource)
{
    // Use dataSource...
}

For more information on dependency injection, see .NET dependency injection.

Add keyed Azure Npgsql client

There might be situations where you want to register multiple NpgsqlDataSource instances with different connection names. To register keyed Npgsql clients, call the AddKeyedAzureNpgsqlDataSource method:

builder.AddKeyedAzureNpgsqlDataSource(name: "sales_db");
builder.AddKeyedAzureNpgsqlDataSource(name: "inventory_db");

Then you can retrieve the NpgsqlDataSource instances using dependency injection. For example, to retrieve the connection from an example service:

public class ExampleService(
    [FromKeyedServices("sales_db")] NpgsqlDataSource salesDataSource,
    [FromKeyedServices("inventory_db")] NpgsqlDataSource inventoryDataSource)
{
    // Use data sources...
}

For more information, see Keyed services in .NET.

Configuration

The Aspire Azure Database for PostgreSQL library provides multiple options to configure the PostgreSQL connection based on the requirements and conventions of your project. A ConnectionString is required.

Use a connection string

When using a connection string from the ConnectionStrings configuration section, you can provide the name of the connection string when calling AddNpgsqlDataSource:

builder.AddNpgsqlDataSource(connectionName: "database");

The connection information is retrieved from the ConnectionStrings configuration section:

{
  "ConnectionStrings": {
    "database": "Host=myserver;Database=mydb;Username=myuser;Password=mypassword"
  }
}

Use configuration providers

The library supports Microsoft.Extensions.Configuration. It loads settings from configuration using the Aspire:Npgsql key:

{
  "Aspire": {
    "Npgsql": {
      "DisableHealthChecks": true,
      "DisableTracing": false
    }
  }
}

Use inline delegates

You can configure settings inline:

builder.AddNpgsqlDataSource(
    "database",
    settings => settings.DisableHealthChecks = true);

Observability and telemetry

Aspire integrations automatically set up Logging, Tracing, and Metrics configurations.

Logging

The Aspire Azure Database for PostgreSQL integration uses the following log categories:

Npgsql.Connection
Npgsql.Command
Npgsql.Transaction
Npgsql.Copy
Npgsql.Replication
Npgsql.Exception

Tracing

The Aspire Azure Database for PostgreSQL integration will emit the following tracing activities using OpenTelemetry:

Npgsql

Metrics

The Aspire Azure Database for PostgreSQL integration will emit the following metrics using OpenTelemetry:

Npgsql
- db.client.connections.create_time
- db.client.connections.use_time
- db.client.connections.wait_time
- db.client.connections.idle.max
- db.client.connections.idle.min
- db.client.connections.max
- db.client.connections.pending_requests
- db.client.connections.timeouts
- db.client.connections.usage

Spørg & svar Samarbejd Fællesskab Diskutér Se