CODEWITHSUNDEEP
databasemigrationliquibasedata-migrationliquibase-hibernate
DEEPAK MURTHY
DEEPAK MURTHY

Reputation: 23

Does liquibase support data migration from one database to another

I have a scenario where we are migrating from Oracle DB to Postgres Sql, I know I can generate a change-log from Oracle schema using liquibase maven plugin and can use the same to create my schema in Postgres. What i want to know is if its possible to copy the data from oracle to postgresql too using liquibase. If so, how to do it.

Any help is much appreciated.

Upvotes: 2

Views: 5849

Answers (2)

Robert
Robert

Reputation: 1631

Yes, this can be done using liquibase. If you have the setup changelog, you can do it as hinted by @htshame. Note that you will need to amend the data a bit. I also recommend to use a dedicated data-output-directory (otherwise you will get a huge XML):

  1. Set liquibase.command.url to the source server (or use the command line option)
  2. liquibase generateChangeLog --diffTypes data --data-output-directory=data --changeLogFile data.xml will generate a data dump
  3. Amend the data dump such that tables that act a Constraints for others are at the top of the data dump (and as such injected first).
  4. Set liquibase.command.url to the target server
  5. liquibase drop-all to (optionally clean the target DB)
  6. liquibase update --changelog-file setup.xml will create all the tables, indices, constraints, etc.
  7. liquibase update --changelog-file data.xml will then inject all the data

In case you are lacking the setup.xml changelog, you can do a full dump from the existing database:

  1. Set liquibase.command.url to the source server (or use the command line option)
  2. liquibase generate-changelog --diff-types catalogs,tables,functions,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data,storedprocedures,triggers,sequences,databasepackage,databasepackagebody --changelog-file all.xml --data-output-directory=data will generate a full dump
  3. Possibly you need to amend data types of some fields in the XML (in case they don't exist in the target DB)
  4. Set liquibase.command.url to the target server
  5. liquibase drop-all to (optionally clean the target DB)
  6. liquibase update --changelog-file all.xml will then rebuild the database on the target server

Note that in the second case, since you are using the data types of the source database, you may not have a 100% percent compliant setup. E.g. Camunda provides a setup.xml changelog and I strongly recommend to use it as it allows to integrate upcoming schema updates without difficulties.

Upvotes: 0

htshame
htshame

Reputation: 7330

I'm not sure you can copy the data from one database to another using Liquibase changeSet directly.

But you can use generateChangeLog with --diffTypes=data attribute. It'll include data in the generated changeSets.

Check out the generateChangeLog docs

–diffTypes - List of diff types to include in changelog expressed as a comma separated list from: tables, views, columns, indexes, foreignkeys, primarykeys, uniqueconstraints, data.

And also check out this example

liquibase   
        --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver  
        --classpath="C:\\Program Files\\Microsoft JDBC Driver 6.0 for SQL Server\\sqljdbc_6.0\\enu\\jre8"  
        --url="jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks2017;integratedSecurity=false;"
        --changeLogFile="D:\Source\generateChangeLog--PersonSchema.xml"
        --username=liquibase
        --password=liquibase@123
        --logLevel=info
        --defaultSchemaName=dbo
        --diffTypes=data
        generateChangeLog

Upvotes: 1

Related Questions