Changing tables to different schema

General Discussion
1

Hi All,

We have implemented liquibase in our project and we would like to move the tables to different schema including the data in the tables.
Our DBA is planning to do an export and import of the tables to new schema. We would like to know how would the liquibase change logs work in this situation.

Thanks,
Naresh

2

If you have not included schema information in your changeSets, everything will work just fine in a different schema.  Liquibase tracks the id/author/filename of changesets that have been ran in the databasechangelog table in the user’s default schema (normally).  You can change the schema it expects the table to be in with a parameter at runtime if necessary, but you probably do not need to.

If you have included the old schema name in the changeSets, you will want to update them with the correct schema so future database rebuilds will put everything in the correct schema.  That will cause your checksums to change and so you will want to use the tag or update the databasechangelog table setting the md5sum column to null so it will recompute the checksums at the next execution time.

Nathan

3

Hi Nathan,

Thank you so much for your quick reply.
We have not given schema names for tables, so i hope it would work with out any hassles. And i will let you know once i try the approach mentioned by you.

Thanks,
Naresh

4

Hi Nathan,

We have one more thing to know. What ever approach you mentioned we are doing it in DEV/TST/QA/PROD.
We have local XE version of database locally on our desktops and then we CI Hudson server which does automated builds. So we are checking how to do the export and import of the tables locally.
We are thinking to have a sql script CREATE TABLE NEWSCHEMA.XYZ AS SELECT FROM THE OLDSCHEMA.XYZ. In this way we can create tables in the new schema. After that we can apply any changes to table in the new schema.
please do validate our approach and let me know if you have any other thoughts.

Thanks & Regards,
Naresh

5

I would think that approach should work.  If you are doing it with multiple tables you may run into foreign key constraint issues (you  would want to make sure ‘create table as select’ copies index and foreign key constraints) depending on the order you copy tables, but besides that I think it would work fine.

Nathan