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

What happens if you do <select pg_reload_conf();> from init script after setting configuration value? — vilkg
– vilkg, Commented Mar 12, 2019 at 13:06
I have attempted that, but it does not seem to have any effect. I get the same issue as described above. — Melissa
– Melissa, Commented Mar 12, 2019 at 17:57
Have you tried dynamic properties ? stackoverflow.com/a/79057238/18179479 — Mircea Sirghi
– Mircea Sirghi, Commented Oct 5, 2024 at 14:35

vilkg · Accepted Answer · 2019-03-19 15:42:33Z

9

+25

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, 2019 at 15:42

answered Mar 11, 2019 at 13:49

vilkg

6756 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ameenhere Over a year ago

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

Andy Guibert Over a year ago

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 Over a year ago

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

Melissa Over a year ago

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.

mkebri · Accepted Answer · 2019-03-18 13:09:47Z

1

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

edited Mar 18, 2019 at 13:09

answered Mar 18, 2019 at 13:01

mkebri

2,1751 gold badge17 silver badges14 bronze badges

1 Comment

Melissa Over a year ago

I wanted to solve this using '''launched via JDBC URL scheme'' not any code.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related