Re: Mysql to postgres tools -reviews? - Mailing list pgsql-general

I checked this list with /contrib/mysql and it seems most of these items
are covered in the conversion script.  If you find any that need adding,
please contact the author.  Thanks.


> >>>>> "GW" == Graham White <gawhite7@home.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@kciLink.com       Rockville, MD       +1-240-453-8497
> AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026