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

Answer 1

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"   );
}

Answer 2

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"))