Thread: Mapping/DB Migration tool

Hi,

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.

Any good idea what would be the best approach ?

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

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

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

-- 
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




>From: Reece Hart <reece@harts.net>
>To: MC Moisei <mcmoisei@hotmail.com>
>CC: pgsql-general <pgsql-general@postgresql.org>
>Subject: Re: [GENERAL] Mapping/DB Migration tool
>Date: Tue, 25 Jul 2006 14:49:34 -0700
>
>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 ?

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-222006-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:002006-07-25 00:00:00
(2 rows)

Time: 1.457 ms

-- 
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

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

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