PostgreSQL is a powerful, open source object-relational database system with many years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. The Aspire PostgreSQL integration provides a way to connect to existing PostgreSQL databases, or create new instances from thedocker.io/library/postgres container image.

The PostgreSQL hosting integration models various PostgreSQL resources as the following types.

• PostgresServerResource
• PostgresDatabaseResource
• PostgresPgAdminContainerResource
• PostgresPgWebContainerResource

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

aspireaddpostgresql

Selectanintegrationtoadd:
> postgresql (Aspire.Hosting.PostgreSQL)
> Other results listed as selectable options...

#:package Aspire.Hosting.PostgreSQL@*

<PackageReferenceInclude="Aspire.Hosting.PostgreSQL"Version="*"/>

In your AppHost project, callAddPostgres on thebuilder instance to add a PostgreSQL server resource then callAddDatabase on thepostgres instance to add a database resource as shown in the following example:

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddPostgres("postgres");
var postgresdb=postgres.AddDatabase("postgresdb");
var exampleProject=builder.AddProject<Projects.ExampleProject>()
.WithReference(postgresdb);
// After adding all resources, run the app...

1. When Aspire adds a container image to the app host, as shown in the preceding example with thedocker.io/library/postgres image, it creates a new PostgreSQL server instance on your local machine. A reference to your PostgreSQL server and database instance (thepostgresdb variable) are used to add a dependency to theExampleProject.

2. When adding a database resource to the app model, the database is created if it doesn’t already exist. The creation of the database relies on the AppHost eventing APIs, specificallyResourceReadyEvent. In other words, when thepostgres resource isready, the event is raised and the database resource is created.

3. The PostgreSQL server resource includes default credentials with ausername of"postgres" and randomly generatedpassword using theCreateDefaultPasswordParameter method.

4. TheWithReference method configures a connection in theExampleProject named"messaging".

By default, when you add aPostgresDatabaseResource, it relies on the following script to create the database:

CREATEDATABASE"<QUOTED_DATABASE_NAME>"

To alter the default script, chain a call to theWithCreationScript method on the database resource builder:

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddPostgres("postgres");
var databaseName="app_db";
var creationScript=$$"""
-- Create the database
CREATE DATABASE{{databaseName}};
""";
var db=postgres.AddDatabase(databaseName)
.WithCreationScript(creationScript);
builder.AddProject<Projects.ExampleProject>()
.WithReference(db)
.WaitFor(db);
// After adding all resources, run the app...

The preceding example creates a database namedapp_db. The script is run when the database resource is created. The script is passed as a string to theWithCreationScript method, which is then run in the context of the PostgreSQL resource.

When adding PostgreSQL resources to thebuilder with theAddPostgres method, you can chain calls toWithPgAdmin to add thedpage/pgadmin4 container. This container is a cross-platform client for PostgreSQL databases, that serves a web-based admin dashboard. Consider the following example:

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddPostgres("postgres")
.WithPgAdmin();
var postgresdb=postgres.AddDatabase("postgresdb");
var exampleProject=builder.AddProject<Projects.ExampleProject>()
.WithReference(postgresdb);
// After adding all resources, run the app...

The preceding code adds a container based on thedocker.io/dpage/pgadmin4 image. The container is used to manage the PostgreSQL server and database resources. TheWithPgAdmin method adds a container that serves a web-based admin dashboard for PostgreSQL databases.

To configure the host port for the pgAdmin container, call theWithHostPort method on the PostgreSQL server resource. The following example shows how to configure the host port for the pgAdmin container:

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddPostgres("postgres")
.WithPgAdmin(pgAdmin=>pgAdmin.WithHostPort(5050));
var postgresdb=postgres.AddDatabase("postgresdb");
var exampleProject=builder.AddProject<Projects.ExampleProject>()
.WithReference(postgresdb);
// After adding all resources, run the app...

The preceding code adds and configures the host port for the pgAdmin container. The host port is otherwise randomly assigned.

When adding PostgreSQL resources to thebuilder with theAddPostgres method, you can chain calls toWithPgWeb to add thesosedoff/pgweb container. This container is a cross-platform client for PostgreSQL databases, that serves a web-based admin dashboard. Consider the following example:

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddPostgres("postgres")
.WithPgWeb();
var postgresdb=postgres.AddDatabase("postgresdb");
var exampleProject=builder.AddProject<Projects.ExampleProject>()
.WithReference(postgresdb);
// After adding all resources, run the app...

The preceding code adds a container based on thedocker.io/sosedoff/pgweb image. All registeredPostgresDatabaseResource instances are used to create a configuration file per instance, and each config is bound to thepgweb container bookmark directory. For more information, seePgWeb docs: Server connection bookmarks.

To configure the host port for the pgWeb container, call theWithHostPort method on the PostgreSQL server resource. The following example shows how to configure the host port for the pgAdmin container:

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddPostgres("postgres")
.WithPgWeb(pgWeb=>pgWeb.WithHostPort(5050));
var postgresdb=postgres.AddDatabase("postgresdb");
var exampleProject=builder.AddProject<Projects.ExampleProject>()
.WithReference(postgresdb);
// After adding all resources, run the app...

The preceding code adds and configures the host port for the pgWeb container. The host port is otherwise randomly assigned.

To add a data volume to the PostgreSQL server resource, call theWithDataVolume method on the PostgreSQL server resource:

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddPostgres("postgres")
.WithDataVolume(isReadOnly:false);
var postgresdb=postgres.AddDatabase("postgresdb");
var exampleProject=builder.AddProject<Projects.ExampleProject>()
.WithReference(postgresdb);
// After adding all resources, run the app...

The data volume is used to persist the PostgreSQL server data outside the lifecycle of its container. The data volume is mounted at the/var/lib/postgresql/data path in the PostgreSQL server container and when aname parameter isn’t provided, the name is generated at random. For more information on data volumes and details on why they’re preferred overbind mounts, seeDocker docs: Volumes.

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddAzurePostgresFlexibleServer("postgres")
.RunAsContainer(container=>
{
container.WithDataVolume();
});
builder.Build().Run();

To add a data bind mount to the PostgreSQL server resource, call theWithDataBindMount method:

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddPostgres("postgres")
.WithDataBindMount(
source:"/PostgreSQL/Data",
isReadOnly:false);
var postgresdb=postgres.AddDatabase("postgresdb");
var exampleProject=builder.AddProject<Projects.ExampleProject>()
.WithReference(postgresdb);
// After adding all resources, run the app...

Data bind mounts rely on the host machine’s filesystem to persist the PostgreSQL server data across container restarts. The data bind mount is mounted at theC:\PostgreSQL\Data on Windows (or/PostgreSQL/Data on Unix) path on the host machine in the PostgreSQL server container. For more information on data bind mounts, seeDocker docs: Bind mounts.

To add an init bind mount to the PostgreSQL server resource, call theWithInitBindMount method:

var builder=DistributedApplication.CreateBuilder(args);
var postgres=builder.AddPostgres("postgres")
.WithInitBindMount(@"C:\PostgreSQL\Init");
var postgresdb=postgres.AddDatabase("postgresdb");
var exampleProject=builder.AddProject<Projects.ExampleProject>()
.WithReference(postgresdb);
// After adding all resources, run the app...

The init bind mount relies on the host machine’s filesystem to initialize the PostgreSQL server database with the containersinit folder. This folder is used for initialization, running any executable shell scripts or.sql command files after thepostgres-data folder is created. The init bind mount is mounted at theC:\PostgreSQL\Init on Windows (or/PostgreSQL/Init on Unix) path on the host machine in the PostgreSQL server container.

When you want to explicitly provide the username and password used by the container image, you can provide these credentials as parameters. Consider the following alternative example:

var builder=DistributedApplication.CreateBuilder(args);
var username=builder.AddParameter("username", secret:true);
var password=builder.AddParameter("password", secret:true);
var postgres=builder.AddPostgres("postgres",username,password);
var postgresdb=postgres.AddDatabase("postgresdb");
var exampleProject=builder.AddProject<Projects.ExampleProject>()
.WithReference(postgresdb);
// After adding all resources, run the app...

The PostgreSQL hosting integration automatically adds a health check for the PostgreSQL server resource. The health check verifies that the PostgreSQL server is running and that a connection can be established to it.

The hosting integration relies on the📦 AspNetCore.HealthChecks.Npgsql NuGet package.

To get started with the Aspire PostgreSQL client integration, install the📦 Aspire.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 anNpgsqlDataSource instance that you can use to interact with PostgreSQL.

dotnetaddpackageAspire.Npgsql

#:package Aspire.Npgsql@*

<PackageReferenceInclude="Aspire.Npgsql"Version="*"/>

In theProgram.cs file of your client-consuming project, call theAddNpgsqlDataSource extension method on anyIHostApplicationBuilder to register anNpgsqlDataSource for use via the dependency injection container. The method takes a connection name parameter.

builder.AddNpgsqlDataSource(connectionName:"postgresdb");

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

publicclassExampleService(NpgsqlDataSource dataSource)
{
// Use dataSource...
}

There might be situations where you want to register multipleNpgsqlDataSource instances with different connection names. To register keyed Npgsql clients, call theAddKeyedNpgsqlDataSource method:

builder.AddKeyedNpgsqlDataSource(name:"chat");
builder.AddKeyedNpgsqlDataSource(name:"queue");

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

publicclassExampleService(
[FromKeyedServices("chat")]NpgsqlDataSource chatDataSource,
[FromKeyedServices("queue")]NpgsqlDataSource queueDataSource)
{
// Use data sources...
}

The Aspire PostgreSQL integration provides multiple configuration approaches and options to meet the requirements and conventions of your project.

When using a connection string from theConnectionStrings configuration section, you can provide the name of the connection string when calling theAddNpgsqlDataSource method:

builder.AddNpgsqlDataSource("postgresdb");

Then the connection string will be retrieved from theConnectionStrings configuration section:

{
"ConnectionStrings":{
"postgresdb":"Host=myserver;Database=postgresdb"
}
}

For more information, see theConnectionString.

The Aspire PostgreSQL integration supportsMicrosoft.Extensions.Configuration. It loads theNpgsqlSettings fromappsettings.json or other configuration files by using theAspire:Npgsql key. Exampleappsettings.json that configures some of the options:

The following example shows anappsettings.json file that configures some of the available options:

{
"Aspire":{
"Npgsql":{
"ConnectionString":"Host=myserver;Database=postgresdb",
"DisableHealthChecks":false,
"DisableTracing":true,
"DisableMetrics":false
}
}
}

For the complete PostgreSQL client integration JSON schema, seeAspire.Npgsql/ConfigurationSchema.json.

You can also pass theAction<NpgsqlSettings> configureSettings delegate to set up some or all the options inline, for example to disable health checks:

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

By default, Aspireclient integrations have health checks enabled for all services. Similarly, many Aspirehosting integrations also enable health check endpoints. For more information, see:

• Adds theNpgSqlHealthCheck, which verifies that commands can be successfully executed against the underlying Postgres database.
• Integrates with the/health HTTP endpoint, which specifies all registered health checks must pass for app to be considered ready to accept traffic

Aspire integrations automatically set up Logging, Tracing, and Metrics configurations, which are sometimes known asthe pillars of observability. Depending on the backing service, some integrations may only support some of these features. For example, some integrations support logging and tracing, but not metrics. Telemetry features can also be disabled using the techniques presented in theConfiguration section.

The Aspire PostgreSQL integration uses the following log categories:

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

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

• Npgsql

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

• Npgsql:
  • ec_Npgsql_bytes_written_per_second
  • ec_Npgsql_bytes_read_per_second
  • ec_Npgsql_commands_per_second
  • ec_Npgsql_total_commands
  • ec_Npgsql_current_commands
  • ec_Npgsql_failed_commands
  • ec_Npgsql_prepared_commands_ratio
  • ec_Npgsql_connection_pools
  • ec_Npgsql_multiplexing_average_commands_per_batch
  • ec_Npgsql_multiplexing_average_write_time_per_batch