Re: Mysql to postgres tools -reviews?

>>>>> "GW" == Graham White <gawhite7(at)home(dot)com> writes:

GW> I am looking for feedback from anyone that has moved their data from
GW> Mysql to Postgres using the tools in contrib/mysql or any other data
GW> porting tools on the net. Did any data get lost in the transfer?
GW> Recommendations? Amount of time for transfers? Any feedback would be
GW> greatly appreciated. Thanks in advance.

I didn't migrate any data, just schemas. The tools were all
inadequate, including the ones I found on the web, let alone the ones
in the contrib section.

Here are my notes for converting the schemas I had; obviously, you
need to apply application specific knowledge to select from the richer
set of PG datatypes that you don't have in MySQL, and vice versa. For
example, in MySQL, I used a hand-packed unsigned integer to hold IP
addresses (my app would pack/unpack the dotted quads) but in PG, I use
the inet data type.

The short of it is that you don't want to use a totally automated tool
for this; you need to use your noodle and make intelligent choices of
your available data types.

--cut here--
change # comments to -- comments

change "int(10)" to "integer"

change "integer unsigned NOT NULL auto_increment" fields to type
"SERIAL PRIMARY KEY"

change "integer unsigned" to "integer check(colname >= 0)"

change "mediumtext" => "text"

remove "Type = XXX" table option at end of create statements.

change "timestamp(14)" => "timestamp default 'NOW()'" .. probably need
trigger to update this kind of timestamp on every update as well.

change "datetime" => "timestamp"

move "KEY" indexes after column create with CREATE INDEX

change "enum" fields to "varchar(X) check (fn in ('...','...'))"

change "set" fields to some type of text field, and make app convert
to/from comma separated string of values.

change "tinyint(N)" to "smallint"

change "UNIQUE KEY name" to "UNIQUE"

delete "binary" attributes to char fields.

delete any "default NULL" as they are redundant.

"text NOT NULL" needs to have "default ''" for same behavior as in mysql.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera(at)kciLink(dot)com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/