Create PostgreSQL extention with PostgreSQLContainer

[boukmi]

Hi all,
I'm currently using PostgreSQLContainer to create a dockerized PostgerSQL container. I need to create a few extensions when the container starts.
Here is wht I did:

@Bean
    public DockerizedPostgreSQL dockerizedPostgreSQL(@Value("${mobkech.docker.postgres.version:10}") String postgresVersion) {
        final DockerizedPostgreSQL dockerizedPostgreSQL = new DockerizedPostgreSQL(postgresVersion)
                .withCopyFileToContainer(forClasspathResource("docker/postgres/create-extensions.sh"), "/docker-entrypoint-initdb.d");

        dockerizedPostgreSQL.start();
        return dockerizedPostgreSQL;
    }

and here is the script content:

#!/usr/bin/env bash
set -e

EXTENSIONS=('hstore' 'unaccent' 'uuid-ossp' 'pg_trgm' 'pgcrypto');
for extension in ${EXTENSIONS[@]}
do
    psql -U postgres -d "${DB_NAME}" -c "CREATE EXTENSION \"${extension}\""
done

This does not seem to work because I keep getting this error when I try to create a table that uses UUID:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE public.person (id UUID DEFAULT uuid_generate_v4(), name TEXT NOT NULL)]; nested exception is org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:388)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:418)
	at com.mobkech.commons.db.test.docker.DockerizedPostgreSQLTest.testDockerizedPostgreSQL(DockerizedPostgreSQLTest.java:27)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:230)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at com.intellij.rt.execution.application.AppMainV2.main(AppMainV2.java:128)
Caused by: org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2533)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2268)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:313)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:268)
	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:409)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376)
	... 37 more

That means that uuid-ossp has not been added.
What is the correct way to add extensions to PostgreSQLContainer container?

Thank you so much for your help guys.

[hantsy]

There is a module for PostgresSQL , https://github.com/testcontainers/testcontainers-java/tree/master/modules/postgresql

And it is published to public repos, https://mvnrepository.com/artifact/org.testcontainers/postgresql

[boukmi]

Yes I know and I can't figure out how to load extensions using PostgreSQLContainer
I'll probably create an image using a Dockerfile

[kishaningithub]

@boukmi This worked for me

  private static PostgreSQLContainer setupPostgresContainer() {
    PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer()
            .withDatabaseName("integration-tests-db")
            .withUsername("username")
            .withPassword("password");
    postgreSQLContainer.withInitScript("db/setup.sql");
    return postgreSQLContainer;
  }

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If you believe this is a mistake, please reply to this comment to keep it open. If there isn't one already, a PR to fix or at least reproduce the problem in a test case will always help us get back on track to tackle this.

[stale]

This issue has been automatically closed due to inactivity. We apologise if this is still an active problem for you, and would ask you to re-open the issue if this is the case.

[Muenze]

@boukmi This worked for me

  private static PostgreSQLContainer setupPostgresContainer() {
    PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer()
            .withDatabaseName("integration-tests-db")
            .withUsername("username")
            .withPassword("password");
    postgreSQLContainer.withInitScript("db/setup.sql");
    return postgreSQLContainer;
  }

Saved my day, thank you so much.

[TomasLukac]

@boukmi This worked for me

  private static PostgreSQLContainer setupPostgresContainer() {
    PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer()
            .withDatabaseName("integration-tests-db")
            .withUsername("username")
            .withPassword("password");
    postgreSQLContainer.withInitScript("db/setup.sql");
    return postgreSQLContainer;
  }

What should be the contents of setup.sql ? I found that the definition for pgcrypto is here https://github.com/hqinnus/postgres-9.0.1-fyp/blob/master/share/contrib/pgcrypto.sql but I get an error: org.postgresql.util.PSQLException: ERROR: language "C" does not exist