Run changeset multiple times in different schemas

Community Answers
1

Greetings. We have an identical set of tables that we want to generate in two different schemas. For obvious reasons I don’t just want to copy and paste the SQL (we use .sql changesets via includes) with different schema prefixes.

I have been able to successfully run a changelog structured like this:

  • master_changelog.xml
    <include file="schema_1.xml" />
    <include file="schema_2.xml" />

  • schema_1.xml
    <property name="schema" value="one" />
    <include file="generate_schema.sql" />

  • schema_2.xml
    <property name="schema" value="two" />
    <include file="generate_schema.sql" />

  • generate_schema.sql
    create table {schema}.my_table (...)

This runs without error but it only results in the creation of one.my_table.

two.my_table is not generated.

Assuming the issue was that the changeset lookup was based on the file name, I created two different symlinks to generate_schema.sql. However, when I run this, I get a duplicate table error: one.my_table already exists. It doesn’t appear that the second property assignment is executed.

Can anyone help with a structure that will allow me to re-run a changeset to create db objects in different schemas without code duplication and without running the liquibase binary twice?

Thankjs!

2

Okay, I finally got this working by using USE SCHEMA in the schema_1.xml and schema_2.xml files rather than properties. However, it still requires that I create symlinks, which is kind of gross. If anyone has a better idea I’d love to hear it.

Thanks!

3

@ablock
How did you use “USE SCHEMA” in the schema_1.xml or schema_2.xml?
Appreciate if you can provide the snippet here.

Thanks,
Swapnil