[question] setting server options

[kcris]

Hi @tomix26

I am running a huge test suite in scala, I ran into this error

org.flywaydb.core.internal.exception.FlywaySqlException: Unable to obtain connection from database (jdbc:postgresql://localhost:5432/postgres) for user 'postgres': FATAL: sorry, too many clients already
-------------------------------------------------------------------------------------------------------------------------------------------------
SQL State  : 53300
Error Code : 0
Message    : FATAL: sorry, too many clients already

which quicly lead to the max_connections issue,

the default value for this option is apparently not good enough for me.

Running this in psql prompt

postgres=# show max_connections;
 max_connections 
-----------------
 300
(1 row)

shows I believe that the default is 300.

So I was trying to set a higher value like this

  private lazy val postgres =
    EmbeddedPostgres
      .builder()
      .setDataDirectory(s"target/postgres/${getClass.getSimpleName}-${System.currentTimeMillis}")
      .setPort(5432)
      .setServerConfig("max_connections", "1000") //avoids???: FATAL: sorry, too many clients already

but if I run the psql prompt again it still shows 300 connections. So I guess I'm doing it wrong.

How can I set a custom max_connections programmaticcally to an instance of EmbeddedPostgres ??

Thanks

ps: this option has to be set before the server starts. Cannot change at runtime. See this

pps: related options might have to be increased too, see this

[tomix26]

Hi @kcris, thanks for the question. I guess you are connecting to a wrong server or something like that. Because I've tested the same approach as you, and for me it works fine. Check the example below:

    @Test
    public void testEmbeddedPg() throws Exception
    {
        try (EmbeddedPostgres pg = EmbeddedPostgres.builder().setServerConfig("max_connections", "1000").start();
             Connection c = pg.getPostgresDatabase().getConnection()) {
            Statement s = c.createStatement();
            ResultSet rs = s.executeQuery("show max_connections");
            assertTrue(rs.next());
            assertEquals(1000, rs.getInt(1));
            assertFalse(rs.next());
        }
    }

[kcris]

Thanks so much for the prompt reply

Indeed, your example works.

Note that I am also calling setDataDirectory and setPorton the embedded instance.

And in that case, all my server options are ignored :)

      EmbeddedPostgres
        .builder()
        .setDataDirectory(s"target/postgres/${getClass.getSimpleName}-${System.currentTimeMillis}"
        .setPort(5432)
        .setServerConfig("max_connections", "1000")
        .setServerConfig("shared_buffers", "512MB")

my actual code (scala), using embedded-postgres-2.0.1 gives me this:

what am I doing wrong?

thanks again! Chris

edit: I have first suspected setDataDirectory but I think now that it's setPort that causes the server settings to be ignored

[tomix26]

Hmm, I really don't know 🤔 For me, it works fine even if I set the data directory property.

@Test
    public void testEmbeddedPg() throws Exception
    {
        try (EmbeddedPostgres pg = EmbeddedPostgres.builder()
                .setDataDirectory("target/postgres/EmbeddedPostgresTest-" + System.currentTimeMillis())
                .setServerConfig("max_connections", "1000")
                .start();
             Connection c = pg.getPostgresDatabase().getConnection()) {
            Statement s = c.createStatement();
            ResultSet rs = s.executeQuery("show max_connections");
            assertTrue(rs.next());
            assertEquals(1000, rs.getInt(1));
            assertFalse(rs.next());
        }
    }

[kcris]

it's not setDataDirectory that causes the issue (as I first suspected), my bad, sorry.

It's setPort. Can you please try that one? Thanks a lot @tomix26

[tomix26]

Even with the port set, it still works as expected. As I said at the beginning, you're probably connecting to a different server running locally on the same port. So if you want my further assistance, check that there is no postgres process running locally and attach a reproducer of the problem to make the investigation easier.

[kcris]

that's not the case.

Above it's actual code, starting the server and immediately connecting to it.

It's your example with just added setPort and setDataDirectory.

Nevermind. Thanks again.

[kcris]

you were correct. Port 5432 was already listening.

No idea how that can happen, since two apps cannot listen to the same port number

and EmbeddedPostgres never complained about it (so I did not checked until now)

But that's what happens, there was 'another' 5432 already listening. Weird.

Sorry for not checking earlier...

Thanks @tomix26

[tomix26]

Ok, thanks for letting me know 👍