What should you use to store the "initial" state of an existing database with?

General Discussion
1

I don’t know if this is the best way to go about it but I can tell you what we did in a similar situation.


We used liquibase to create a changelog from the initial state of our database schema and stored that in a file called base.xml. The changeSets generated by liquibase were not perfect but they didn’t need much manual attention to get them to re-create our schema.


Then we created a file called update.xml. This file doesn’t contain any changeSets, but instead includes other files that do. The rational was any new modifications to the existing schema would be grouped together into their own file and an include entry would be added to update.xml. Hence our update.xml file looks something like:

  1.   
  2.   
  3.   

Finally we created a file called create.xml that includes both base.xml and update.xml.


So when ever we need to upgrade the schema of an existing database, we just run liquibase against the update.xml:

  1. liquibase --changeLogFile=./update.xml update

But when we need to create a schema from scratch we run it against create.xml:

  1. liquibase --changeLogFile=./create.xml update

Finally, just in case someone accidently runs base.xml against a pre-existing schema, we have some preconditions for each changeSet in base.xml that will check for the presence of something that shouldn’t be there if the schema was blank (e.g. a well known table in our schema etc) and mark the change set as ran.


There may be better ways to achieve what we wanted, but it serves our needs and it is relatively straightforward.

2

I’ve used a couple options:


  1. Create a standard database backup of your existing database. All new database changes are tracked via liquibase, and the original database snapshot is used to bring a fresh database up to your “start using liquibase” stage.


  1. Use liqubase’s generateChangeLog feature to create changesets to get a new database up to the current state. You can include diffType=DATA as a flag in order to have data exported along with the schema, but there are some objects (such as stored procedures, check constraints, and others) that are not generated or are missing certain attributes (such as if an index is clustered or non-clustered). You can take that generated changeset as a starting point and add any missing or wrong parameters. It is usually good to keep the generated changelog in a seperate ed changeset to easily tell what is “legacy” and what is new change tracking. If you run the generated changesets against an existing database you will get errors because liquibase will re-run them. You can prevent this by either using the changeLogSync command or adding preconditions checking for the existance of tables before running the changesets.


Nathan

3

Say a project has been going on for a while and you want to store that existing state as the first changeset of the database?
(including things like stored procs).

Is this possible?


P.S. I realize that storing the data is outside the scope of Liquibase.