Liquibase create Schema for postgres

Question

I'm using Dropwizard (1.0.0) and Liquibase to create a database if it's not existing.

Problem here is that I'm using a different Postgres schema (not public). It seems like Liquibase is not able to create this schema before is it? I was expecting Liquibase to generate this schema, but it always throws a "Schema with name xx not found" if I try to build the database.

Solution 1

Even though Liquibase does not have CREATE SCHEMA in its bundled changes/refactorings (and therefore doesn't generate one during a dropwizard db dump), you could still include this as a changeset in your migrations changelog using the sql tag, as follows:

<changeSet author="christian" id="1">
    <sql dbms="postgresql" endDelimiter=";">
        CREATE SCHEMA foo
    </sql>
</changeSet>

Note that Liquibase will create it's own tables in the PUBLIC schema, regardless - before applying any changesets:
If you run db migrate --dry-run in dropwizard, you'll see that Liquibase would first execute

CREATE TABLE PUBLIC.DATABASECHANGELOGLOCK ...
CREATE TABLE PUBLIC.DATABASECHANGELOG ...

before running

CREATE SCHEMA foo;

Solution 2

Not directly in answer to the question, but posting it for anyone who ran into the error I did, with creating tables in multiple schemas. I was getting an error executing this from maven with the defaultSchemaName configuration.

[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.6.2:update (default-cli) on project demo: Error setting up or running Liquibase: ERROR: relation "databasechangelog" already exists [Failed SQL: CREATE TABLE databasechangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))] -> [Help 1]

I tried to fix it by adding the following configurations to pom.xml, but that was only a partial solution:

<defaultSchemaName>foo</defaultSchemaName>
<changelogSchemaName>foo</changelogSchemaName>

Finally, I got this fixed by adding foo to the end of my connection string also, like this jdbc:postgresql://localhost:5432/postgres?currentSchema=foo

Solution 3

Apart from the fact that you are using Dropwizard then the Spring Boot Pre-Liquibase module does what you are asking.

There's a chicken-and-egg problem with Liquibase in the sense that it cannot be used to configure its own prerequisite, for example a schema. This is the problem which Pre-Liquibase solves. It executes some SQL prior to Pre-Liquibase itself.

Sometimes you'll want to host multiple instances of an application on the same database host. Then you'll want to separate them by schema. This is but one possible use-case for Pre-Liquibase.

Pre-Liquibase is meant for Spring so it will not work out-of-the-box for your Dropwizard scenario. But feel free to steal ideas.

(full disclosure: I'm the author of Pre-Libuibase)

Comments

[a_horse_with_no_name]

You can tell Liquibase to use a different schema for its own tables through the --defaultSchemaName parameter.

[fspinnenhirn]

The schema has to exist first, though, for that to work. The equivalent dropwizard command db migrate --schema foo fails with "invalid schema name" when trying to create the Liquibase tables

[Muthaiah PL]

If we decide to use the change log file for multiple databases, say SQL Server and Postgres, will this still work??? Having 2 pieces for the same changes differentiating by dbms property???

[user2359997]

@fspinnenhirn could you please let us know if we could pass schema name dynamically say for example <property name="schemaName" value="temp2"/> <changeSet author="abcd" id="1"> <sql dbms="postgresql" endDelimiter=";"> CREATE SCHEMA $(schemaName) </sql> </changeSet>

[Eugene]

this should be upvoted 100 times. liquibase decision to only be able to use an existing shema/keyspace (cassandra), makes it useless for production grade migration with multiple environments and multiple different keyspaces.