Converting MySQLdatabases to postgresql

Is there an app to do this? Most of my datan=bases are in MySQL, but my ISP
uses PostgresQL. I have locar=ted some scripts, but the author points out
that the two databases are not equivalent, and straight conversion is not
really possible.
TIA,

Doug.
--
ICQ Number 178748389. Registered Linux User No. 277548.
Anger is one letter away from danger.
- Eleanor Roosevelt.

Doug Laidlaw wrote:
> Is there an app to do this? Most of my datan=bases are in MySQL, but my ISP
> uses PostgresQL. I have locar=ted some scripts, but the author points out
> that the two databases are not equivalent, and straight conversion is not
> really possible.
> TIA,
>
> Doug.

There are differences, but not in data types or in SQL statements.

I have one set up, just as a backup system, the main database is in
MySQL, most of the SQL commands are dynamic from M$ applications of
various types. serves about 35 clients.

The only difference If I test the postgres version is that I change the
'connection string. A single string located in a shared area of the network.

I have to say that I didn't have any success with both systems accessing
the same tables simultaniously. Which ever gets in first wins that battle.


I suspect that what your talking about is not so much database, but
support/application code which is specific to one of the systems, but I
bet your socks that it is only the 'connection string' used in those
functions that needs looking at. Most DB programmers will write each
function (or set of functions) to work like this

Func Foo()
connect to database
do some work
close connection
end

This is because having a lot of pipes open on a very large database is
costly in resources.

Sometimes though for the sake of speed they need to keep the connection up.

Remember while you look that many connections can be held open at the
same time from each client.

Quoth Doug Laidlaw <(E-Mail Removed)>:
> Is there an app to do this? Most of my datan=bases are in MySQL,
> but my ISP uses PostgresQL. I have located some scripts, but the
> author points out that the two databases are not equivalent, and
> straight conversion is not really possible. TIA,

Let me suggest that you NOT try to do the conversion as one big step;
down that path lies madness, irrespective of where you're coming and
going.

I have fairly regularly been involved in converting data into
PostgreSQL databases, and have found it most useful to go through a
phased approach.

Phase I: Load data in a completely unvalidated form

For this purpose, practically any input method will do. Most fields
should be of type TEXT to allow any kind of crud to make it over.
This part should be as automated as you can get, I think the "most
modern" thing is the converter at GBorg...

<http://gborg.postgresql.org/project/mysql2psql/projdisplay.php>

At this point, what you have is the original data, which is probably
pretty borked as far as your application is concerned. MySQL is
fairly much _terrible_ from a data validation perspective, allowing
invalid dates and the like.

Phase II: Cleanse the data

This may actually involve multiple steps.

The notion is that you take the imported data, and move it over to a
new set of tables, fixing what's broken.

You define a new table that has strongly typed fields (e.g. - date
stamps and such) and fix the data errors.

In my most recent version of this, I had to do a lot of work to
reformat telephone numbers. The "legacy" application was very
easy-going about what crud you could toss in; the destination
application requires that the formats conform with ITU standards. I
had no issues with timestamps being drastically wrong, happily.

It may take many queries to fix things up.

Ideally, this generates a _new_ set of tables, so that you leave the
data that you imported from the old system pristine so you can look
back and compare in any auditing processes that may need to be done...

Phase III: Load into the destination application's tables.

Once all the data is cleansed, it should be easy to load it into the
tables for the new application, in one "fell swoop."

insert into app_table1 (f1,f2,f3,f4) select f1,f2,f3,f4 from phase2.app_table1;
insert into app_table2 (f1,f2,f3,f4) select f1,f2,f3,f4 from phase2.app_table2;
insert into app_table3 (f1,f2,f3,f4) select f1,f2,f3,f4 from phase2.app_table3;

All being said, that's not _quite_ what I do. I'm expecting to do a
_lot_ of data conversions specific to a certain application. As a
result, I have created stored procedures specific to that application.
I have 4 kinds of objects, which interlink pretty tightly. Those
linkages have to get created in the new system, and actually, there
are further additional linkages (that may be treated as "magic" :-))
that may not even have existed in the old system.

I'll have tables:

object_a;
object_b;
object_c;
object_d;

And linkages...

a_b_links;
a_c_links;
d_c_links;

And create the objects via:

select create_a(fields) from phase2.object_a;
select create_b(fields) from phase2.object_b;
select create_c(fields) from phase2.object_c;
select create_d(fields) from phase2.object_d;
select link_a_b(a,b) from phase2.a_b_links;
select link_a_c(a,c) from phase2.a_c_links;
select link_d_c(d,c) from phase2.d_c_links;

That meant writing 7 stored procedures, one for each object/linkage
type...

The result of that is a _really_ clean set of data.

In any case, if you try to do it all via one "magick step," you'll
have a lot of trouble getting it all right.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/spiritual.html
The difference between a child and a hacker is the amount he flames
about his toys. -- Ed Schwalenberg

Quoth rassle <(E-Mail Removed)>:
> There are differences, but not in data types or in SQL statements.

What????

The essential differences _are_ in data types, and handling of data
typing, as well as in the forms of SQL accepted by the respective
systems.

They are WILDLY different, particularly in the degree of respect the
projects have for ANSI standards.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www.ntlug.org/~cbbrowne/postgresql.html
(eq? 'truth 'beauty) ; to avoid unassigned-var error, since compiled code
; will pick up previous value to var set!-ed,
; the unassigned object.
-- from BBN-CL's cl-parser.scm

On Mon, 19 Jul 2004 13:52:49 +0000, Christopher Browne wrote:
> Quoth rassle <(E-Mail Removed)>:
>> There are differences, but not in data types or in SQL statements.
>
> What????
>
> The essential differences _are_ in data types, and handling of data
> typing, as well as in the forms of SQL accepted by the respective
> systems.
>
> They are WILDLY different, particularly in the degree of respect the
> projects have for ANSI standards.

Dunno that much about either viz. standards.

Wondering though, since Postgresql was designed to be an OO DBMS or maybe
more properly as an (almost) RDBMS with OO extensions, there may be
difference "by design". At one point I was reading up on postgresql OO
extensions and considering using it for an image database, with some
embedded transformations using the OO extensions. For various reasons I
did not go ahead with it, but the research was interesting. Is anyone
actually using any of these OO extensions (extendability?)? How would that
map into SQL syntax? Is there any new/proposed SQL standard allowing any
extensions? er, sounds like a bunch of dumb questions, but I'm interested.

--
Juhan Leemet
Logicognosis, Inc.

In article <(E-Mail Removed) >, Juhan Leemet wrote:
>
> Wondering though, since Postgresql was designed to be an OO DBMS or maybe
> more properly as an (almost) RDBMS with OO extensions, there may be
> difference "by design". At one point I was reading up on postgresql OO
> extensions and considering using it for an image database, with some
> embedded transformations using the OO extensions. For various reasons I
> did not go ahead with it, but the research was interesting. Is anyone
> actually using any of these OO extensions (extendability?)? How would that
> map into SQL syntax? Is there any new/proposed SQL standard allowing any
> extensions? er, sounds like a bunch of dumb questions, but I'm interested.

You normally don't want to use the OO stuff as the child table implementation
has problems and isn't currently getting a lot of attention. You can do the
same stuff using views.

The type extension stuff is nice, but really isn't OO. Some new feeatures
relating to composite types is getting put into 7.5 which should have a beta
release on or about August 1. These make it easier to define new types as
you don't have to write the low level routines needed for base types.