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