Open Hareshraman opened 3 weeks ago
This needs support from both pgpool and postgresql community together.
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?
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 ?
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.
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.
@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:
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.
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.16I 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:
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).
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="7.0.4" />
.global.postgresql.database
as 'db1'Now to reproduce the issue.
global.postgresql.database
empty,postgresql.database
should be empty to ensure a database doesn't existdb1
database is createddb1
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?
What is the expected behavior?
What do you see instead?