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.

Impatience

Liquibase does not create anything without you telling it to do so. You need to include a create schema in your change log – Nies

I've actually created a dump before using liquibase (with --schema xx) and was expecting it to recreate the schema/whole db. seems like it doesn't do it – Impatience

Again: Liquibase only runs statements that result from the definition in your change log. It won't do any magic or guess what objects you are missing. It is nothing more then a structured way of writing a SQL statement. It knows nothing about your dump – Nies

Yeah I got that. But I was wondering why liquibase dump doesn't dump schema and database but only the tables.. Doesn't make sense in my eyes – Impatience

I guess because it has no "native" support to create a schema (as that is very different between the different DBMS). – Nies

Also, with using multiple schema in PostgreSQL, it is a preferred scenario, when each of your your app has a separate user, which own its (possibly only) schema(s), but cannot access any of the others. In this setup, these users usually doesn't have the right to even create schemas (and also, because they're are not superusers, they cannot create extensions either). This scenario f.ex. cannot be created with that user, and because in PostgreSQL you cannot switch users within a connection, it is preferable to "set things up" first, i.e. create the schema, extensions, etc. before liquibase runs. – Noblenobleman

Answer 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;

Esta

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

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 – Esta

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??? – Isogamete

@Esta 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> – Consonance

@Esta will this be an answer to my question as well ? #73036133 – Snazzy

Answer 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

Materiel

with liquibase 4.27.0 it seems to be enough to set --liquibase-schema-name on postgres. docs.liquibase.com/parameters/liquibase-schema-name.html – Rockel

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

Sutherland

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. – Townsend

Answer 4

Liquibase does not have CREATE SCHEMA, you need to manage create schema function by running SQL query.

<changeSet author="liquibase_user" id="1">
    <sql>
        CREATE SCHEMA IF NOT EXISTS liquibase_demo;
    </sql>
</changeSet>

Anatomy

Answer 5

Works great with Pre-Liquibase. To create schema, using SpringBoot 2.7 (at the moment):

1 - Add to pom:

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

2 - Create file "src/main/resources/preliquibase/default.sql" with content:

CREATE SCHEMA IF NOT EXISTS foobar;

Done. Executes before Liquibase (and you can have "?currentSchema=foobar" in your "jdbcUrl:").

Selfseeking

Answer 6

simple approach is an initial call to liquibase using default schema public with a "create schema if not exists foo" and then a second call with the actual migration changelog.

Carpenter