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 — , Apr 05 '16 at 07:05
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, Apr 05 '16 at 07:09
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 — , Apr 05 '16 at 07:11
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, Apr 05 '16 at 07:17
I guess because it has no "native" support to create a schema (as that is **very** different between the different DBMS). — , Apr 05 '16 at 07:26
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, Apr 05 '16 at 07:44

score 14 · Answer 1 · answered Jul 29 '16 at 03:01

14

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;

answered Jul 29 '16 at 03:01

fspinnenhirn

1,784
1
13
27

2

You can tell Liquibase to use a different schema for its own tables through the `--defaultSchemaName` parameter. – Jul 29 '16 at 05:46
3

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 – fspinnenhirn Jul 30 '16 at 00:07
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??? – Muthaiah PL Jan 12 '18 at 20:19
@fspinnenhirn could you please let us know if we could pass schema name dynamically say for example CREATE SCHEMA $(schemaName) – user2359997 Nov 26 '19 at 16:53
@fspinnenhirn will this be an answer to my question as well ? https://stackoverflow.com/questions/73035633/liquibase-create-schema-for-mysql – Aԃιƚყα Gυɾαʋ Jul 19 '22 at 11:09

score 9 · Answer 2 · answered Nov 07 '18 at 03:34

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

score 5 · Answer 3 · answered Sep 16 '21 at 13:47

5

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>

answered Sep 16 '21 at 13:47

Maulik Kakadiya

1,467
1
21
31

lbruun · Answer 4 · 2022-11-26T10:17:08.460

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)

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. — Eugene, Sep 21 '21 at 14:42

Liquibase create Schema for postgres

4 Answers4

Linked