Thread: Schema migration tools?

Greetings,

We have the traditional three servers:

    dev --> staging --> production

each with a PostgreSQL instance and the same schema, at least over
time.  Right now, we handle schema migration (updating the schema for
new releases) by manually-created scripts that apply the changes to
staging and production.

I'd like a tool that would automatically create these scripts, and I
wondered if anything like this existed.  The theory would be that it
would consider two databases a and b, and produce the appropriate
script to change b's schema to match a.

Does anything like this exist?  If not, I might have a new project...

Thanks!
-- Christophe

Christophe wrote:
> Greetings,
> Does anything like this exist?  If not, I might have a new project...

Typically new projects are found over at www.pgfoundry.org. :P

Joshua D. Drake

On Apr 21, 2008, at 7:44 PM, Christophe wrote:
> Greetings,
>
> We have the traditional three servers:
>
>     dev --> staging --> production
>
> each with a PostgreSQL instance and the same schema, at least over
> time.  Right now, we handle schema migration (updating the schema
> for new releases) by manually-created scripts that apply the changes
> to staging and production.
>
> I'd like a tool that would automatically create these scripts, and I
> wondered if anything like this existed.  The theory would be that it
> would consider two databases a and b, and produce the appropriate
> script to change b's schema to match a.
>
> Does anything like this exist?  If not, I might have a new project...

There's a bunch. I've been using http://dbmstools.sourceforge.net/ for
a while, and some others are http://xml2ddl.berlios.de/ and http://www.liquibase.org/
  . They're mostly focused on maintaining the schema in a non-sql-
script format (with good reason) but can extract it from a running
database too.

Or you can just maintain your schema by manually writing version n to n
+1 upgrade scripts and version n to n-1 downgrade scripts and
generating the schema for version n in the obvious way.

Cheers,
   Steve

Christophe -

We use LiquiBase: http://www.liquibase.org/home

We don't use it quite the same as what you're proposing, but I think you could use it that way.  When we found it, we did 'Generate Changelog' to start, then made new changes to the ChangeLog, and applied it to each database as needed.   It has really helped us, because we were forgetting to apply the changes to the production database that were needed.

Hope this helps!
Kerri




--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@yumaed.org      (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

On Apr 21, 2008, at 10:44 PM, Christophe wrote:

> I'd like a tool that would automatically create these scripts, and I
> wondered if anything like this existed.  The theory would be that it
> would consider two databases a and b, and produce the appropriate
> script to change b's schema to match a.
>
> Does anything like this exist?  If not, I might have a new project...


However it happens, you need to *capture* the schema changes that need
to be applied to each database. I don't think you can just compare
schemas and guarantee that database A will be transformed in exactly
the same way as database B. For example, suppose the last text column
name in database B was different from A. How can you determine if the
column was renamed or if the column was dropped and a new column was
added? The semantics of that difference could be very important.

It would be nice if PostgreSQL had some kind of unique reference for
the column, but I think columns are just numbered sequentially as they
are added. It would also be neat to have a built-in way to log the
schema changes.


John DeSoi, Ph.D.

On Apr 22, 2008, at 3:09 PM, John DeSoi wrote:

> It would be nice if PostgreSQL had some kind of unique reference for
> the column, but I think columns are just numbered sequentially as
> they are added. It would also be neat to have a built-in way to log
> the schema changes.

It does: log_statement set to either 'all' or 'ddl' will do the trick.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Apr 22, 2008, at 4:53 PM, Erik Jones wrote:

>> It would be nice if PostgreSQL had some kind of unique reference
>> for the column, but I think columns are just numbered sequentially
>> as they are added. It would also be neat to have a built-in way to
>> log the schema changes.
>
> It does: log_statement set to either 'all' or 'ddl' will do the trick.

If I do this, is there a way to get a transaction consistent log of
just the necessary commands to transform another copy of the database?
In other words, I assume this approach will log each DDL command even
if the transaction is rolled back. Correct?

Thanks,



John DeSoi, Ph.D.

On Apr 22, 2008, at 4:33 PM, John DeSoi wrote:

>
> On Apr 22, 2008, at 4:53 PM, Erik Jones wrote:
>
>>> It would be nice if PostgreSQL had some kind of unique reference
>>> for the column, but I think columns are just numbered sequentially
>>> as they are added. It would also be neat to have a built-in way to
>>> log the schema changes.
>>
>> It does: log_statement set to either 'all' or 'ddl' will do the
>> trick.
>
> If I do this, is there a way to get a transaction consistent log of
> just the necessary commands to transform another copy of the
> database? In other words, I assume this approach will log each DDL
> command even if the transaction is rolled back. Correct?


Right.  It's not something you'd want to lift directly and run
somewhere else.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Christophe,

I agree with Dr. DeSoi that it may not always be possible to automate
schema transformations. However, if you need to compare the metadata
between two databases, the free, open-source SchemaCrawler for SQL
Server tool will do this for you. You can take human-readable
snapshots of the schema and data, for later comparison. Comparisons
are done using a standard diff tool such as WinMerge. SchemaCrawler
outputs details of your schema (tables, views, procedures, and more)
in a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler
can also output data (including CLOBs and BLOBs) in the same plain-
text formats.

SchemaCrawler is available at SourceForge:
http://schemacrawler.sourceforge.net/

Sualeh Fatehi

It's not open source; it's expensive; but the products from Embarcadero work
pretty well.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice