Mapping/DB Migration tool

Hello, MC.

Only human can do this. Moreover, if "data integrity is very
important".

Regards

You wrote:

MM> Hi,

MM> I'm looking to migrate psql db1 to a psql db2 that has a different structure
MM> even though 70% would be the same. I'd need a tool that allows me to map
MM> field to field and then migrate the data from one db to another. Needless to
MM> say that preserving data integrity is very important.

MM> Any good idea what would be the best approach ?

MM> Thanks,
MM> Constantin
MM> http://www.goodstockimages.com

MM> ---------------------------(end of
MM> broadcast)---------------------------
MM> TIP 4: Have you searched our list archives?

MM> http://archives.postgresql.org

--
With best wishes,
Pavel mailto:pavel@microolap.com

On Jul 25, 2006, at 2:59 PM, 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. I'd need a tool that
allows me to map field to field and then migrate the data from one
db to another. Needless to say that preserving data integrity is
very important.

I'm not aware of any migration tool to help on this (other than a
good SQL editor ;). It seems the best approach is to load an exact
copy of db1 and then write a script to transform it into db2,
creating, dropping, and altering the structure as necessary.

I think there are some tools (EMS?) that can compare schemas and
generate the SQL to transform db1 to db2. But there is a good chance
an automated approach like this will not transform data the way you
want.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

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

The main thing is I changed a bunch of date types to timestamp type. Is
there a simple way to change the type on such fields ?

Some fields got renamed and the type changed.

It sounds like doing a copy|template of db1 as db2 and then applying the new
changes as a script that will probably work. The contraints are easy to
migrate once I have the whole structure in place.

Thanks a lot to all for replying to my post that quickly. Keep them coming
if you have more ideas

Regards,
Constantin
http://www.goodstockimages.com

On Tue, 2006-07-25 at 17:21 -0500, MC Moisei wrote:

The main thing is I changed a bunch of date types to timestamp type.
Is
there a simple way to change the type on such fields ?

Yes, and more generally to change the type of a column. See below:

rkh@csb-dev=> create table timely (quand date);
CREATE TABLE
Time: 14.385 ms
rkh@csb-dev=> insert into timely values ('1968-11-22');
INSERT 0 1
Time: 2.398 ms
rkh@csb-dev=> insert into timely values (now());
INSERT 0 1
Time: 4.683 ms
rkh@csb-dev=> select * from timely ;
quand
------------
1968-11-22
2006-07-25
(2 rows)

Time: 2.263 ms
rkh@csb-dev=> alter table timely alter column quand type timestamp;
ALTER TABLE
Time: 39.002 ms
rkh@csb-dev=> select * from timely ;
quand
---------------------
1968-11-22 00:00:00
2006-07-25 00:00:00
(2 rows)

Time: 1.457 ms

Similarly, try 'alter table timely rename column quand to cuando' .

Also: \h alter table

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

On Wed, 2006-07-26 at 15:18 +0200, Karsten Hilbert wrote:

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.

Would you mind giving a small example ? That sounds really
useful.

I don't have any examples of the destructive kind available, but here's
a non-destructive one.

I once discovered that deleting a primary key was taking forever. I
finally tracked this down to the lack of an index on one of the many
tables which contained FK references to that PK. The pg_* views contain
all of the necessary data to identify these cases. I wrote such views
to select all FK-PK pairs with index status, and another to show those
without indexes on the FK. For example:

rkh@csb-dev=> select * from pgtools.foreign_keys;
fk_namespace | fk_relation | fk_column | fk_indexed | pk_namespace | pk_relation | pk_column | pk_indexed | ud | c_namespace | c_name
--------------+---------------+---------------+------------+--------------+-------------+---------------+------------+----+-------------+-------------------------
unison | p2gblatalnhsp | p2gblathsp_id | t | unison | p2gblathsp | p2gblathsp_id | t | cc | unison | p2gblathsp_id_exists
unison | p2gblatalnhsp | p2gblataln_id | t | unison | p2gblataln | p2gblataln_id | t | cc | unison | p2gblataln_id_exists
unison | p2gblathsp | pseq_id | t | unison | pseq | pseq_id | t | cc | unison | pseq_id_exists

rkh@csb-dev=> select * from pgtools.foreign_keys_missing_indexes limit 5;
fk_namespace | fk_relation | fk_column | fk_indexed | pk_namespace | pk_relation | pk_column | pk_indexed | ud | c_namespace | c_name
--------------+-------------+-------------+------------+--------------+-------------+-------------+------------+----+-------------+-----------------
gong | node | alias_id | f | gong | alias | alias_id | t | cn | gong | alias_id_exists
taxonomy | node | division_id | f | taxonomy | division | division_id | t | cc | taxonomy | $1
mukhyala | pao | tax_id | f | mukhyala | mytax | tax_id | t | cr | mukhyala | pao_tax_id_fkey

Then. something like this:
rkh@csb-dev=> select 'create index '||fk_relation||'_'||fk_column||'_idx on '||fk_relation||'('||fk_column||');' from pgtools.foreign_keys_missing_indexes ;
?column?
-----------------------------------------------------------------------------
create index node_alias_id_idx on node(alias_id);
create index node_division_id_idx on node(division_id);
create index pao_tax_id_idx on pao(tax_id);

Finally, I used psql to generate the script and execute it:
$ psql -Atc 'select <as above>' | psql -Xa
(I'm skipping the quoting hassle, which you could circumvent by creating
a view to build the script.)

In case your interested in these "pgtools" views, I've uploaded them to
http://harts.net/reece/pgtools/ .

(Note: I created these views a long time ago with the intent to release
them, but I never did so. I think there's now a pgtools or pg_tools
package on sourceforge, but that's unrelated.)

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

On Wed, Jul 26, 2006 at 08:48:14AM -0700, Reece Hart wrote:

In case your interested in these "pgtools" views, I've uploaded them to
http://harts.net/reece/pgtools/ .

I am looking into it.

Any chance you could do a text dump with --no-owner --no-acl ?

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Thu, 2006-07-27 at 13:02 +0200, Karsten Hilbert wrote:

Any chance you could do a text dump with --no-owner --no-acl ?

The pgdump is already --no-owner, but I forgot --no-acl. I just
uploaded a new tarball using both flags.

BTW, you can generate this yourself with what you have using
pg_restore.

eg$ pg_restore --no-acl --no-owner pgtools.pgdump >pgtools.sql

-Reece

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