How to create schema in Postgres DB, before liquibase start to work?

Question

I have standalone application. It’s on java, spring-boot, postgres and it has liquibase.

I need to deploy my app and liquibase should create all tables, etc. But it should do it into custom schema not in public. All service tables of liquibase (databasechangelog and databasechangeloglock) should be in custom schema too. How can I create my schema in DB before liquibase start to work? I must do it inside my app when it’s deploying, in config or some like. Without any manual intervention into the DB.

application.properties:

spring.datasource.jndi-name=java:/PostgresDS
spring.jpa.properties.hibernate.default_schema=my_schema
spring.jpa.show-sql = false
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.datasource.continue-on-error=true
spring.datasource.sql-script-encoding=UTF-8

liquibase.change-log = classpath:liquibase/changelog-master.yaml
liquibase.default-schema = my_schema

UPD:

When liquibase start, it's create two tables databasechangelogs and one more table. After that, liquibase start working. But I want liquibase in liquibase.default-schema = my_schema, but it's not exist when liquibase start to work and it an error: exception is liquibase.exception.LockException: liquibase.exception.DatabaseException: ERROR: schema "my_schema" does not exist

I want liquibase work in custom schema, not in public:

liquibase.default-schema = my_schema

but before liquibase can do it, the schema must be created. Liquibase can't do this because it not started yet and for start it needs schema. Vicious circle.

Answer 1

I found a solution with my application.properties.

org.springframework.jdbc.datasource.init.ScriptUtils worked before liquibase.

Logs:

14:11:14,760 INFO  [org.springframework.jdbc.datasource.init.ScriptUtils] 
(ServerService Thread Pool -- 300) Executing SQL script from URL 
[vfs:/content/app.war/WEB-INF/classes/schema.sql]

14:11:14,761 INFO  [org.springframework.jdbc.datasource.init.ScriptUtils] 
(ServerService Thread Pool -- 300) Executed SQL script from URL 
[vfs:/content/app.war/WEB-INF/classes/schema.sql] in 1 ms.

14:11:14,912 ERROR [stderr] (ServerService Thread Pool -- 300) INFO 9/27/18 
2:11 PM: liquibase: Successfully acquired change log lock

14:11:15,292 ERROR [stderr] (ServerService Thread Pool -- 300) INFO 9/27/18 
2:11 PM: liquibase: Reading from my_schema.databasechangelog

14:11:15,320 ERROR [stderr] (ServerService Thread Pool -- 300) INFO 9/27/18 
2:11 PM: liquibase: Successfully released change log lock

I just put schema.sql with CREATE SCHEMA IF NOT EXISTS my_schema; into resources dir and all working properly.

Thanks all for help.

Update: It's work for Spring boot 1.X. If you are use Spring Boot 2, you should enable schema.sql in properties file, with spring.datasource.initialization-mode=always. More info in Spring Boot - Loading Initial Data

Update 2: In Spring Boot 2.5.2 (maybe in earlier versions too) this solution is not working now, as @peterh has wrote in comment. Sad but true. The last version I was try this solution and it's work was Spring Boot 2.0.9 In docs Spring Boot says that it was redesigned from Spring Boot 2.5.x

Update 3: Some information why they kill this feature -> https://github.com/spring-projects/spring-boot/issues/22741

Answer 2

To solve this, we need to run a SQL statement that creates the schema during Spring Boot initialization at the point when DataSource bean had been already initialized so DB connections can be easily obtained but before Liquibase runs.

By default, Spring Boot runs Liquibase by creating an InitializingBean named SpringLiquibase. This happens in LiquibaseAutoConfiguration.

Knowing this, we can use AbstractDependsOnBeanFactoryPostProcessor to configure SpringLiquibase to depend on our custom schema creating bean (SchemaInitBean in the example below) which depends on DataSource. This arranges the correct execution order.

My application.properties:

db.schema=my_schema
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.properties.hibernate.default_schema=${db.schema}
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.liquibase.enabled=true
spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.xml
spring.liquibase.defaultSchema=${db.schema}

Add the @Configuration class below to the project, for example put it in a package processed by component scan.

@Slf4j
@Configuration
@ConditionalOnClass({ SpringLiquibase.class, DatabaseChange.class })
@ConditionalOnProperty(prefix = "spring.liquibase", name = "enabled", matchIfMissing = true)
@AutoConfigureAfter({ DataSourceAutoConfiguration.class, HibernateJpaAutoConfiguration.class })
@Import({SchemaInit.SpringLiquibaseDependsOnPostProcessor.class})
public class SchemaInit {

    @Component
    @ConditionalOnProperty(prefix = "spring.liquibase", name = "enabled", matchIfMissing = true)
    public static class SchemaInitBean implements InitializingBean {

        private final DataSource dataSource;
        private final String schemaName;

        @Autowired
        public SchemaInitBean(DataSource dataSource, @Value("${db.schema}") String schemaName) {
            this.dataSource = dataSource;
            this.schemaName = schemaName;
        }

        @Override
        public void afterPropertiesSet() {
            try (Connection conn = dataSource.getConnection();
                 Statement statement = conn.createStatement()) {
                log.info("Going to create DB schema '{}' if not exists.", schemaName);
                statement.execute("create schema if not exists " + schemaName);
            } catch (SQLException e) {
                throw new RuntimeException("Failed to create schema '" + schemaName + "'", e);
            }
        }
    }


    @ConditionalOnBean(SchemaInitBean.class)
    static class SpringLiquibaseDependsOnPostProcessor extends AbstractDependsOnBeanFactoryPostProcessor {

        SpringLiquibaseDependsOnPostProcessor() {
            // Configure the 3rd party SpringLiquibase bean to depend on our SchemaInitBean
            super(SpringLiquibase.class, SchemaInitBean.class);
        }
    }
}

This solution does not require external libraries like Spring Boot Pre-Liquibase and not affected by limitations on data.sql / schema.sql support. My main motivation for finding this solution was a requirement I had that schema name must be a configurable property. Putting everything in one class and using plain JDBC is for brevity.

Answer 3

You can use Spring Boot Pre-Liquibase module for this. It is exactly what it is meant for. It executes some SQL prior to executing Liquibase itself. Pre-Liquibase sets itself up in the Spring Boot AutoConfigure chain so that it is guaranteed to always execute before Liquibase.

Step 1

Add the following Starter to your project:

<dependency>
    <groupId>net.lbruun.springboot</groupId>
    <artifactId>preliquibase-spring-boot-starter</artifactId>
    <version>  ---latest-version---  </version>
</dependency>

Step 2

Add a SQL file to src/main/resources/preliquibase with a name of postgresql.sql and content like this:

CREATE SCHEMA IF NOT EXISTS ${spring.liquibase.default-schema};

The ${} syntax denotes a placeholder variable. Pre-Liquibase will resolve it from the properties in your Spring Environment.

Step 3

Set application properties like this:

spring.liquibase.default-schema=${my.db.schemaname}
spring.jpa.properties.hibernate.default_schema=${my.db.schemaname}

Now - in this example - the only thing left to decide is where the my.db.schemaname value comes from. That is your choice. The example project advocates that it should come from an OS environment variable, in particular if your are deploying to a cloud.

Final words

WARNING: Pre-Liquibase is possibly way too flexible in that it allows to execute any SQL code. Don't be tempted to put stuff in Pre-Liquibase files which rightfully belong in an Liquibase ChangeSet. Honestly, the only usage I can think of for Pre-Liquibase is to set up a database "home" (meaning a schema or a catalog) where Liquibase db objects can live so that instances of the same application can be separated by schema or catalog while residing on the same database server.

(Disclosure: I'm the author of Pre-Liquibase module)

Answer 4

Simple solution based on Pavel D. answer. It also can be used without liquibase

@Slf4j
@Component
public class SchemaConfig implements BeanPostProcessor {

    @Value("${db.schema}")
    private String schemaName;

    @Override
    public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
        if (!StringUtils.isEmpty(schemaName) && bean instanceof DataSource) {
            DataSource dataSource = (DataSource) bean;
            try (Connection conn = dataSource.getConnection();
                 Statement statement = conn.createStatement()) {
                log.info("Going to create DB schema '{}' if not exists.", schemaName);
                statement.execute("create schema if not exists " + schemaName);
            } catch (SQLException e) {
                throw new RuntimeException("Failed to create schema '" + schemaName + "'", e);
            }
        }
        return bean;
    }
}