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.

Liquibase does not create anything without you telling it to do so. You need to include a create schema in your change log — a_horse_with_no_name
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 — Christian
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 — a_horse_with_no_name
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 — Christian
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. — pozs

fspinnenhirn fspinnenhirn · Accepted Answer · 2016-07-29T03:01:46

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;

Liquibase create Schema for postgres

3 Answers