[bitnami/postgresql-ha] XX000: unable to get session context , Unable to create a database when connecting via Pgpool.

[Hareshraman]

Name and Version

bitnami/postgresql-ha:14.2.16

What architecture are you using?

None

What steps will reproduce the bug?

Pre-requisite

Npgsql nuget : "Npgsql.EntityFrameworkCore.PostgreSQL" Version="7.0.4" . Postgresql-ha:14.2.16

I am encountering an issue while attempting to connect to a PostgreSQL Pgpool server using Entity Framework Core. The setup involves the utilization of the Postgresql HA Helm Chart by Bitnami, which sets up a high-availability PostgreSQL cluster. The intended behavior is for EF Core to create the database if it doesn't already exist and subsequently migrate data. However, upon initiating the connection and migration process, I am immediately confronted with the following error:

Npgsql.PostgresException (0x80004005): XX000: unable to get session context.

My architecture comprises a PostgreSQL Pgpool server orchestrated via the Bitnami Postgresql HA Helm Chart. The primary goal is to establish a connection using Entity Framework Core and perform migrations to set up the initial data structure. Regrettably, the encountered error is impeding progress.

Ideally, when EF Core connects to the PostgreSQL Pgpool server, it should be able to acquire the necessary session context to proceed with database operations, including database creation and data migration.

Steps Taken:

Configured the PostgreSQL Pgpool server using the Bitnami Postgresql HA Helm Chart. Attempted to connect to the database and initiate data migration using Entity Framework Core. Encountered the Npgsql.PostgresException with the error message indicating a failure to obtain the session context (XX000: unable to get session context).

1. In our environment we're using postgresql-ha chart , 2 replicas of pgpool , 3 replicas of postgresql server. We use EF core , <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="7.0.4" /> .
2. We used to set global.postgresql.database as 'db1'
3. We create migrations with the help of a helm hook job, which runs as a pre-install job which ensures migrations are created.
4. The connection string in _npgsqlConnectionStringBuilder

_npgsqlConnectionStringBuilder.Host = pgpool.infra.svc.cluster.local
_npgsqlConnectionStringBuilder.Port = 5432
_npgsqlConnectionStringBuilder.Database = db1
_npgsqlConnectionStringBuilder.Username = postgres
_npgsqlConnectionStringBuilder.Password = "randompassword"

Now to reproduce the issue.

1. Set global.postgresql.database empty , postgresql.database should be empty to ensure a database doesn't exist
2. Run a migration to ensure db1 database to ensure is created
3. Database is not created if it doesn't exist db1

Does this mean we cannot create databases if my connection string is pgpool ? or only if connect to postgresql service. ?

Are you using any custom parameters or values?

  postgresql-ha:
    persistence: 
      enabled: false
    global:
      postgresql:
        database: ""
    pgpool:
      ## @param pgpool.logConnections Log all client connections (PGPOOL_ENABLE_LOG_CONNECTIONS)
      logConnections: true
      ## @param pgpool.logHostname Log the client hostname instead of IP address (PGPOOL_ENABLE_LOG_HOSTNAME)
      logHostname: true
       ## @param pgpool.logPerNodeStatement Log every SQL statement for each DB node separately (PGPOOL_ENABLE_LOG_PER_NODE_STATEMENT)
      logPerNodeStatement: true
      ## pgpool.childMaxConnections The maximum number of client connections in each child process (PGPOOL_CHILD_MAX_CONNECTIONS)
      childMaxConnections: "150"
      ## pgpool.childLifeTime The time in seconds to terminate a Pgpool-II child process if it remains idle (PGPOOL_CHILD_LIFE_TIME)
      childLifeTime: "150"
      ## clientIdleLimit The time in seconds to disconnect a client if it remains idle since the last query (PGPOOL_CLIENT_IDLE_LIMIT)
      clientIdleLimit: "150"
      ## connectionLifeTime The time in seconds to terminate the cached connections to the PostgreSQL backend (PGPOOL_CONNECTION_LIFE_TIME)
      connectionLifeTime: "180"
      resources:
        limits:
          cpu: 750m
          memory: 1536Mi
        requests:
          cpu: 500m
          memory: 1024Mi
    postgresql:
      ## Maximum total connections postgres can handle
      maxConnections: "200"
      resources:
        requests:
          cpu: 500m
          memory: 1024Mi
        limits:
          cpu: 750m
          memory: 1536Mi

What is the expected behavior?

1. Database is created if database doesn't exist
2. Migration created successfully.

What do you see instead?

1. Database is not created , if it doesn't exist .

Additional information

No response

[Hareshraman]

This needs support from both pgpool and postgresql community together.

[javsalgar]

Hi,

Thank you so much for sharing the issue. However, as you mention, it is not clear to me that this has to do with the Bitnami packaging of Pgpool + PostgreSQL repgmgr, but with how these applications work together. Did you also check with their corresponding communities?

[Hareshraman]

This issue is not entirely from the chart , But we deploy postgres HA as a bundle and it's managed entirely as one chart. As bitnami bundles pgpool and postgres together, we would need to test if database creation is possible via pgpool. Although this issue needs to be triaged with both the postgresql and pgpool team. Let's consider a scenario in the real world where the ask is to isolate data and we would like to achieve this by creating different databases. This also results in bigger problems wr.t scaling using postgres. This also makes me wonder about how could we even create databases on the fly ? , And as of today you can create just one database by providing a value in global.postgresql.database

Let's also consider this scenario, where we would able to create databases using postgres-headless.svc , but using postgres-ha the recommendation is to always let pgpool handle connections from the client , but only when creating databases ? we connect to postgres-headless-svc ? I'm unsure about this and not aware of the repercussions of doing so

Then in that case we would be maintaining 2 connection strings in the system ? one for queries etc which talk to-- pgpool.svc and the other for database creation -- that talks to postgres-headless.svc ? are these assumptions correct ?

[carrodher]

The issue may not be directly related to the Bitnami container image/Helm chart, but rather to how the application is being utilized, configured in your specific environment, or tied to a specific scenario that is not easy to reproduce on our side.

If you think that's not the case and are interested in contributing a solution, we welcome you to create a pull request. The Bitnami team is excited to review your submission and offer feedback. You can find the contributing guidelines here.

Your contribution will greatly benefit the community. Feel free to reach out if you have any questions or need assistance.

Suppose you have any questions about the application, customizing its content, or technology and infrastructure usage. In that case, we highly recommend that you refer to the forums and user guides provided by the project responsible for the application or technology.

With that said, we'll keep this ticket open until the stale bot automatically closes it, in case someone from the community contributes valuable insights.

[github-actions[bot]]

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

[HeinrichvH]

@Hareshraman yes, it is possible! I can tell you a more or less "easy" workaround, I am not an expert for pgpool but for me it seems like pgpool needs an existing DB in order to create a session / session context. When you use efCore, in this case I guess you are using npgsql version of it, the nuget is doing a check if the DB exists before creating it, this means it will perform a query to check this. But in order for executing the query, the pgpool needs an existing database.

I propose you the following workaround:

1. Create an empty DB Context
2. Register the empty DB Context with credentials that are capable of accessing the "postgres" db
3. After building your app create a scope, get the empty DB Context, open a connection to the DB itself and then execute the creation command.

here is one basic example for step 3:

       using var scope = app.ApplicationServices.CreateScope();
        
        var creationDbContext = scope.ServiceProvider.GetService<CreationDbContext>();
        await using var connection = creationDbContext!.Database.GetDbConnection();
        await connection.OpenAsync();
        
        var checkCmd = connection.CreateCommand();
        checkCmd.CommandText = "SELECT 1 FROM pg_database WHERE datname = 'central_event_store'";
        var result = await checkCmd.ExecuteScalarAsync();
        
        if (result == null)
        {
            var createCmd = connection.CreateCommand();
            createCmd.CommandText = "CREATE DATABASE central_event_store";
            await createCmd.ExecuteNonQueryAsync();
        }
        
        var dbContext = scope.ServiceProvider.GetService<TDbContext>();
        await (dbContext?.Database.MigrateAsync() ?? Task.CompletedTask);

I hope this helps you. So this problem is only related to the implementation of the npgsql ef core nuget package and not to the bitnami helm chart. Atleast i did not find any setting in the pg_pool for enabling a default db for session context usage, but like I said i am not an pg_pool expert.

[aitting]

public abstract class PostgreSQLPendingEfCoreMigrationsChecker<TDbContext> : PendingEfCoreMigrationsChecker<TDbContext>
    where TDbContext : DbContext, IEfCoreDbContext
{

    public static string DatabaseNotFouncMessage = "unable to get session context";

    public IConfiguration Configuration =>
        LazyServiceProvider.LazyGetRequiredService<IConfiguration>();

    protected PostgreSQLPendingEfCoreMigrationsChecker(
        ILoggerFactory loggerFactory,
        IUnitOfWorkManager unitOfWorkManager,
        IServiceProvider serviceProvider,
        ICurrentTenant currentTenant,
        IDistributedEventBus distributedEventBus,
        IAbpDistributedLock abpDistributedLock,
        string databaseName) : base(loggerFactory, unitOfWorkManager, serviceProvider, currentTenant, distributedEventBus, abpDistributedLock, databaseName)
    {

    }


    protected override async Task LockAndApplyDatabaseMigrationsAsync()
    {
        try
        {
            await base.LockAndApplyDatabaseMigrationsAsync();
        }
        catch (Exception ex)
        {
            if (ex.Message.Contains(DatabaseNotFouncMessage))
            {

                var connectionString = Configuration.GetConnectionString(DatabaseName);

                await DatabaseCreator.CreateByConnectionStringAsync(connectionString!);
            }
            throw;
        }

    }
}

using Npgsql;

namespace Hd.AbpExtensions.EntityFrameworkCore.DbMigrations.EfCore
{
    public class DatabaseCreator
    {

        public const string DefaultDatabase = "postgres";


        public static async Task CreateByConnectionStringAsync(string connectionString)
        {
            try
            {
                if (connectionString.IsNullOrWhiteSpace())
                {

                    throw new ArgumentNullException($"尝试创建数据时无法解析连接字符串{connectionString}");
                }

                var conns = connectionString.Split(";");

                var databaseName = conns.FirstOrDefault(x => x.StartsWith("Database"))?.Split("=")?[1];
                if (databaseName.IsNullOrWhiteSpace())
                {
                    throw new Exception("在尝试创建数据库时失败！没有找到数据库名称！");
                }

                string uid = conns.FirstOrDefault(x => x.StartsWith("User ID") || x.StartsWith("Username"))?.Split("=")?[1] ?? "postgres";
                string pwd = conns.FirstOrDefault(x => x.StartsWith("Password"))?.Split("=")?[1] ?? "dNq6Zd0wOdc2Ha";
                string host = conns.FirstOrDefault(x => x.StartsWith("Host"))?.Split("=")?[1] ?? "localhost";
                int port = Convert.ToInt32(conns.FirstOrDefault(x => x.StartsWith("Port"))?.Split("=")?[1] ?? "5432");

                await CreateAsync(uid, pwd, host, port, databaseName);
            }
            catch (Exception ex)
            {
                throw new Exception("在尝试创建数据库时失败!", ex);
            }
        }

        public static async Task CreateAsync(string uid = "postgres", string pwd = "", string host = "localhost", int port = 5431, string databaseName = "")
        {
            if (databaseName.IsNullOrWhiteSpace())
            {
                throw new Exception("请传入需要创建数据库名称!");
            }

            var connString = $"User ID={uid};Password={pwd};Host={host};Port={port};Database={DefaultDatabase};Pooling=true";

            var dataSourceBuilder = new NpgsqlDataSourceBuilder(connString);
            var dataSource = dataSourceBuilder.Build();

            using (var conn = await dataSource.OpenConnectionAsync())
            {
                await using (var cmd = new NpgsqlCommand($"create database \"{databaseName}\"", conn))
                {

                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }
    }
}

[github-actions[bot]]

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

[github-actions[bot]]

Due to the lack of activity in the last 5 days since it was marked as "stale", we proceed to close this Issue. Do not hesitate to reopen it later if necessary.