How do you include postgresql.conf on docker container when using org.testcontainers

Question

Is it possible to give postgresql testcontainer a custom postgresql.conf file via config?

I have included maven dependency

<dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>postgresql</artifactId>
            <version>1.10.6</version>
</dependency>

And using 'Database containers launched via JDBC URL scheme' for DB url As such have the setting in my Spring Boot app as:

datasource:
    url: jdbc:tc:postgresql:10-alpine:///databasename
    driver-class-name: org.testcontainers.jdbc.ContainerDatabaseDriver

I need to have a custom setting in postgresql.conf. Is there a way of pushing postgresql.conf to the docker container started by testcontainers?

EDIT 1

Thanks @vilkg I did know about the TC_INITSCRIPT script option and SET function however:

I am wanting a custom setting such as my.key
ALTER system does not work for your own settings eg: ALTER SYSTEM SET my.key = 'jehe'; get error Could not execute the SQL command. Message returned: `ERROR: unrecognized configuration parameter "my.key"
I had previously try SET and ALTER DATABASE as below

SET my.key = 'new  value 8';    -- sets for current session 
ALTER DATABASE test SET my.key = 'new  value 8';  -- sets for subsequent sessions
select current_setting('my.key');

PROBLEM IS

when testcontainer starts postgres container and I pass it an init script to run url: jdbc:tc:postgresql:10-alpine:///databasename?TC_INITSCRIPT=init_pg.sql
- and I can include the above SQL its happy..
- I know setting of that secret.key is working correctly in this script because it will fail on the line select current_setting('my.key'); if other two are commented out
- I also know that runing it against db name test is correct eg: 'ALTER DATABASE test' because if I use a different name it fails Testcontainers automatically connects the app to db named test So with all of the above I believe the DB is setup nicely and all should be good

BUT When I use 'current_setting('my.key')' within application code it fails

I have attempted that, but it does not seem to have any effect. I get the same issue as described above. — Melissa, Mar 12 '19 at 17:57

vilkg · Answer 1 · 2019-03-19T15:42:33.573

8

If you want to continue launching Postgres container using JDBC URL scheme, test containers can execute init script for you. The script must be on the classpath, referenced as follows:

jdbc:tc:postgis:9.6://hostname/databasename?TC_INITSCRIPT=somepath/init.sql

ALTER SYSTEM SET command was introduced in postgres 9.4, so you could use it in your init script.

Another option would be to start postgres container using database containers objects and use withCopyFileToContainer() method. Example:

JdbcDatabaseContainer<?> postgisContainer = new PostgisContainerProvider()
        .newInstance()
        .withDatabaseName( POSTGRES_DATABASE_NAME )
        .withUsername( POSTGRES_CREDENTIALS )
        .withPassword( POSTGRES_CREDENTIALS )
        .withCopyFileToContainer(MountableFile.forClasspathResource("postgresql.conf"), "/var/lib/postgresql/data"));

EDIT:

If none of the above works, you can reconfigure Postgres command and pass your custom configuration keys. All you need is extending PostgreSQLContainer and overriding configure() method.

@Override
protected void configure()
{
     setCommand( "postgres -c $your_config_key=$your_config_value"   );
}

edited Mar 19 '19 at 15:42

answered Mar 11 '19 at 13:49

vilkg

625
6
7

Some configuration parameters cannot be dynamically altered by using the SET command. For instance, `max_locks_per_transaction` config requires a server restart. I will try the second option and get back to you if it works – ameenhere Mar 11 '19 at 14:00
why is it necessary to override the `configure()` method? Shouldn't we be able to use the built-in `GenericContainer.withCommand(String)` method? – Andy Guibert Apr 15 '19 at 20:30
after checking the src, I see that for some reason PostgreSQLContainer overrides `configure()` and calls `setCommand("postgres")` in it, which makes it impossible to use `.withCommand()` with this container. Seems like a bug so I'll try a PR – Andy Guibert Apr 15 '19 at 20:33
Thanks @vilkq but the main thing I wanted was to ensure: ''launched via JDBC URL scheme'' and as you will see, in comments on initial question, using the init script and alter system command from postgres does not help - its the same issue. – Melissa Jun 03 '19 at 15:10

mkebri · Answer 2 · 2019-03-18T13:09:47.497

we have to create our database and the connection’s user. This is done by using environment variables from the Docker image. To change postgres.conf we can use DockerFIle where we will replace the existing postgres.conf by the new configuration.

@ClassRule
public static GenericContainer postgresql= new GenericContainer(
  new ImageFromDockerfile("postgresql:10-alpine")
   .withDockerfileFromBuilder(dockerfileBuilder -> {
      dockerfileBuilder.from("myPostgresql:10-alpine")
      // root password is mandatory
      .env("PG_ROOT_PASSWORD", "root_password")
      .env("PG_DATABASE", "postgres")
      .env("PG_USER", "postgres")
      .env("PG_PASSWORD", "postgres")
})

Next, we have to create a database schema and populate the database. From the image documentation, the directory /docker-entrypoint-initdb.d is scanned at startup and all files with .sh, .sql et .sql.gz extension are executed. So, we just have to put our files schema.sql and data.sql in this directory. Somme parameteres can be set by sql requesting running database

.withFileFromClasspath("a_schema.sql", "db/pg/schema.sql")
.withFileFromClasspath("b_data.sql", "db/pg/data.sql"))

I wanted to solve this using '''launched via JDBC URL scheme'' not any code. — Melissa, Jun 03 '19 at 15:10

How do you include postgresql.conf on docker container when using org.testcontainers

2 Answers2