Re: Mapping/DB Migration tool

On Tue, 2006-07-25 at 13:59 -0500, MC Moisei wrote:

> I'm looking to migrate psql db1 to a psql db2 that has a different
> structure
> even though 70% would be the same.

Depending on how much the structure changes (as opposed to more trivial
things like column names), you might consider whether you could actually
use the database itself to do this.

For some kinds of changes, and especially those that make destructive
in-place changes that might require debugging, I've written views which
generate the SQL statements to execute. I then do something like:

$ psql -Atc 'select sql from sql_changes' | psql -Xa

This works particularly well when the changes can be computed in some
way from the database, such as creating indexes for unindexed PKs
(postgresql doesn't require indexes on PKs).

You'd probably want to do this by making copies of the original database
as a template ('create database db2 template db1') or createdb -T .

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0