[question] setting server options
kcris opened this issue · 8 comments
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
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());
}
}
Thanks so much for the prompt reply
Indeed, your example works.
Note that I am also calling setDataDirectory
and setPort
on 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
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());
}
}
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
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.
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.
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
Ok, thanks for letting me know 👍