Sections

Membership

Features

Converting a Database Schema from MySQL to PostgreSQL

Posted by martin on 1 Jun 2002, last updated on 25 Aug 2002.

Port your database dump from MySQL to PostgreSQL easily with this PHP script, the article also explains how it works.

The need for portability

You probably have heard of PEAR::DB, you may have even used it, if not let me EXPLAIN - it is a library for database abstraction. PEAR::DB is made to help people port their code to another database backend. That's very good but RDBMS's tend to use different syntaxes and people need to convert the SQL code too.

How it all began

I have used PEAR::DB for a couple of months (7 Jul 2002), and it works perfectly with the MySQL server, so I decided I need to test it against other databases too. As PostgreSQL is a nice Open Source database, a lot more advanced than MySQL, it seemed like a good choice for me.

I installed PostgreSQL FROM sources and it worked, I was able to connect through the bundled client, but this was the moment my problems started. I had a tough time testing different DSN's until I found that I only had to add unix before the database host and separate it with a + FROM it. It seems that the PostgreSQL driver defaults to TCP/IP sockets and the MySQL to Unix as I've always used unix sockets for MySQL.

Converting the schema

The first problem I encountered was that PostgreSQL doesn't like MySQL dumps, so after studying the PostgreSQL docs I came to this translation TABLE that I need to use.

MySQL	PostgreSQL
# MySQL comment	--ANSI SQL comment
UNIQUE KEY name (cols)	UNIQUE (cols)
PRIMARY KEY (cols)	the same
KEY name (cols)	CREATE INDEX name ON TABLE (cols)
id int NOT NULL auto_increment	id SERIAL
binary	doesn't exist
enum	doesn't exist, convert to varchar
tinyint(n) or smallint(n)	smallint
mediumint(n) or int(n)	int

MySQL default dumps FROM version 3.23.49 on use the ANSI SQL comment style with a required space after --.

I also found that PostgreSQL doesn't accept dates like 0000-00-00, so I had to strip default values FROM date/datetime columns.

Note: There is no need to convert the defaultINSERT statements as they are compatible with PostgreSQL, only the DATA types are different.

The convertor code

The code is written to be used FROM the commandline not a webserver. I recompiled PHP several times with different configure lines but it always said that $argv is undefined so the filenames are hard-coded.

You'll have to redefine $enum if it doesn't match your needs for a substitution of enum are different.

#!/usr/local/bin/php -q
<?php

$source = "/home/shaggy/shaggy.sql";
$output = "/home/shaggy/pgtest.sql";

$enum = 'varchar(10)'; // convert enum to this

if ( !file_exists($source) ) {
 die("File not found: $source\n");
}

$fd = fopen($source, "r");
$result = fread($fd, filesize($source));
fclose($fd);

$result = mysql2postgre($result);

$fd = fopen($output, "w");
if (fwrite($fd, $result)) {
 echo "OK\n";
} else {
 echo "Failed\n";
}
fclose($fd);




function mysql2postgre($source) {
 global $enum;

 $result = $source;
 $result = preg_replace('/Type=MyISAM/i', '', $result);

 // convert line comments
 $result = preg_replace("/#(.*)/", '--$1', $result);
 // and compress newlines
 $result = preg_replace("/\n{2,}/", "\n\n", $result);

 // get rid of proprietary code
 $result = preg_replace("/DROP TABLE IF EXISTS\W+.+/i", '', $result);

 // indices
 $result = preg_replace("/(.*)UNIQUE KEY.+\((.+)\)/i",
 "$1UNIQUE ($2)", $result);

 // a little hack to save primary keys
 $result = preg_replace("/(.*)PRIMARY KEY.+\((.+)\)/i",
 "$1PRIMARY ($2)", $result);
 $result = preg_replace("/,\n.*KEY\W.+\((.+)\)/i",
 "\n-- was KEY ($1)", $result);
 $result = preg_replace("/(.*)PRIMARY.+\((.+)\)/i",
 "$1PRIMARY KEY (\\2)", $result);

 $result = preg_replace("/(.*?)(\w+).+auto_increment/i",
 '$1$2 SERIAL', $result);

 // Postgre doesn't support the binary modifier
 $result = preg_replace('/binary/i', '', $result);

 // type transformations
 $result = preg_replace('/enum\(.+\)/i', $enum, $result);

 $result = preg_replace('/tinyint\(.+\)/i', 'smallint', $result);
 $result = preg_replace('/smallint\(.+\)/i', 'smallint', $result);
 $result = preg_replace('/meduimint\(.+\)/i', 'int', $result);
 $result = preg_replace('/int\(.+\)/i', 'int', $result);

 // Most of my default dates are '0000-00-00'
 $result = preg_replace("/datetime(.*) default '.*'/i",
 'datetime$1', $result);
 $result = preg_replace("/date(.*) default '.*'/i",
 'date$1', $result);


 return $result;
}

?>

I am using the PCRE regular expressions instead of POSIX because they are generally faster.

The first regular expression removes the Type=MyISAM identifier that is used in MySQL to set the TABLE type, change it if you use InnoBD or BDB tables.

The code should process any input but it is recommended to use a dump from mysqldump or a similar application.

Comments

User Login
by apdex (apdex@arcor.de) on 10 Nov 2002 3:20pm GMT

Hi,

can you explain me, what i have to do that the user login will be work.

I wanna make a new webpage with an user login, so what have i to do that it works. I hope you write back, cya

Apdex

Convert the schema
by martin on 10 Nov 2002 4:51pm GMT

Apdex, you need to save the code and then convert the database schema using it, after that you just execute the code against your database as usual.

What is database schema?
by Bart (russianet@yahoo.com) on 12 Nov 2002 4:07pm GMT

Could you please give us a difinition of what the database scheme is?

Thanks,

b a r t

A database schema is...
by martin on 20 Nov 2002 8:19am GMT

A database schema means the same as a database in MySQL. It is a group of table definitions possibly containing information.

enum
by Pawel (mpx@interia.pl) on 4 Feb 2003 2:40pm GMT

I have found an interesting approach to converting ENUM:

(http://archives.postgresql.org/pgsql-sql/2002-03/msg00273.php)

"enum('s','n')

I changed it for:

col1 CHAR CHECK (col1 IN ('s','n'))

Which is SQL. :-)

If what is enumerated are numbres, just use INT instead of CHAR.

Saludos... :-)"

User/Schema
by sudhu (sgupta@beyondaccess.net) on 6 Feb 2003 8:12pm GMT

What is the difference between "User" and "Schema" in MySQL database?

How can I create some tables under a particular user in MySQL database?

Thanks.

-sudhu

PHP code was a cut & paste for me
by Rob Bailey () on 3 Mar 2003 10:26pm GMT

Thanks, Martin, migrating from MySQL to PostgreSQL was a breeze thanks to your little swab of code. I just had to change some fields named "user" to "usr".

cheers!

Datetime...
by Jonas (jonas@frizone.ch) on 20 May 2003 11:17pm GMT

Thanks, this is a great script Martin...

How did you guys handle the "datetime" datatype in postgre?

Jonez

datetime datatype
by lalo c () on 23 Oct 2003 6:22am GMT

For others who might come across this page, postgresql's datetime equivalent would be:TIMESTAMP WITHOUT TIME ZONE

TIMESTAMP precision
by Ariel (aroza@strip-this.iname.com) on 12 Jan 2004 10:53pm GMT

And to note PostgreSQL's TIMESTAMP precision ranges from 0 to 13, which is out of bounds to MySQL's TIMESTAMP default precision of 14... thats another thing to modify inside the dumps

How do we convert postgresqldatabase to mysql
by renju (renjuranni@yahoo.com) on 26 Mar 2004 10:21am GMT

its very informative

Do u know the conversion from access to postgresql or postgresql to mysql

PostreSQL to MySQL or Access to PostreSQL
by martin on 27 Mar 2004 5:23pm GMT

Renju it's pretty difficult to convert a PostgreSQL dump into MySQL without losing lots of the definitions. It would also require a lot more processing and may not be exactly a linear process.

As for Access I haven't used it much.

Access to PostgreSQL or MySQL
by flugh () on 4 Apr 2004 1:58am GMT

Go to

http://www.intranet2internet.com/public/default.asp?PAGE=download&ID=DUMP

and get AccessDUMP. It's a freebie that will dump your .mdb to MySQL format. Then just take your resulting .sql file, put on the mysql box, run 'mysql < accessdump.sql', and you are set :)

By the way, I bought the 'Pro' version of AccessDUMP, and give it about 1 star. Don't bother, just stick with the freebie, as there are bugs in the Pro version and the author won't answer any emails (even from a paying customer). The extra features you get for paying aren't worth it IMHO, but hey, the guy's software gets used daily, so I paid up :)

Very useful. Thank you!
by Tyler (tblack@h2st.com) on 13 Apr 2004 5:53pm GMT

Great script. I just had to make a few changes (other than the obvious file names and changing the shebang line to where my bin/php command is.

- The script missed some "mediumint" datatypes. I just changed these to "integer"

- The script did not remove "unsigned" declarations. Wherever these were used was not a problem for me, so I just deleted them and used regular integer types.

- The script inserted a comment part way through a create table command. The comma for the line containing the column definition was placed at the end of the inserted comment (and as a result was ignored), creating a syntax error and the table wasn't created. I just put a comma back where it should have been and everything was fixed.

Thanks a bunch.

Thanks - saved me a load of pain
by Thomas Nichols (nx10mail@yahoo.co.uk) on 21 Jun 2004 2:06pm GMT

Needed some extra hacks - e.g. MySQL uses backticks - ` - which can apparently all be deleted, had some problems with BLOBs - but this was still a real timesaver. Thank you!

convert postgresql to mysql
by nee (n_shukla2000@yahoo.co.in) on 30 Aug 2004 9:19am GMT

can we convert postgresql to mysql using trigger and view future

convert postgresql to mysql
by nee (n_shukla2000@yahoo.co.in) on 30 Aug 2004 9:19am GMT

can we convert postgresql to mysql using trigger and view features

Convert to MSSQL
by Bra (vh81@yahoo.com) on 16 Sep 2004 8:04am GMT

Plz convert this query to MSSQL:

\connect - fahmie

CREATE SEQUENCE "lagu_id_lagu_seq" start 171 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;

SELECT nextval ('"lagu_id_lagu_seq"');

CREATE SEQUENCE "pelanggan_id_pelanggan_seq" start 56 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;

SELECT nextval ('"pelanggan_id_pelanggan_seq"');

CREATE SEQUENCE "pembekal_id_pembekal_seq" start 22 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;

SELECT nextval ('"pembekal_id_pembekal_seq"');

CREATE SEQUENCE "artis_id_artis_seq" start 49 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;

SELECT nextval ('"artis_id_artis_seq"');

CREATE SEQUENCE "album_media_id_media_seq" start 92 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;

SELECT nextval ('"album_media_id_media_seq"');

CREATE SEQUENCE "album_id_album_seq" start 34 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;

SELECT nextval ('"album_id_album_seq"');

CREATE SEQUENCE "pesanan_id_pesanan_seq" start 34 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;

SELECT nextval ('"pesanan_id_pesanan_seq"');

CREATE TABLE "lagu" (

"id_lagu" int4 DEFAULT nextval('lagu_id_lagu_seq'::text) NOT NULL,

"tajuk_lagu" character varying(30),

"id_album" int4,

"id_artis" int4,

"track" int4,

PRIMARY KEY ("id_lagu")

);

CREATE TABLE "pelanggan" (

"id_pelanggan" int4 DEFAULT nextval('pelanggan_id_pelanggan_seq'::text) NOT NULL,

"nama" character varying(30),

"alamat" character varying(200),

"no_tel" character varying(12),

"jantina" character,

"jenis_mc" character varying(7),

"no_mc" character varying(16),

"email" character varying(30),

"username" character varying(20),

"password" character varying(20),

PRIMARY KEY ("id_pelanggan")

);

CREATE TABLE "pembekal" (

"id_pembekal" int4 DEFAULT nextval('pembekal_id_pembekal_seq'::text) NOT NULL,

"nama_sykt" character varying(30),

"kod_sykt" character varying(15),

"alamat" character varying(200),

"no_tel" character varying(12),

"no_fax" character varying(12),

"email" character varying(30),

"jenis_mc" character varying(7),

"no_mc" character varying(16),

"no_pendaftaran" character varying(16),

"username" character varying(20),

"password" character varying(20),

PRIMARY KEY ("id_pembekal")

);

CREATE TABLE "album_keluaran" (

"id_album" int4,

"tahun" int4

);

CREATE TABLE "lagu_sampel" (

"id_lagu" int4,

"jenis_sampel" character varying(15),

"url_sampel" character varying(20)

);

CREATE TABLE "artis" (

"id_artis" int4 DEFAULT nextval('artis_id_artis_seq'::text) NOT NULL,

"nama_artis" character varying(30),

"id_pembekal" int4

);

CREATE TABLE "album_media" (

"id_album" int4,

"jenis_media" character varying(5),

"harga" character varying(16),

"id_media" int4 DEFAULT nextval('album_media_id_media_seq'::text) NOT NULL

);

CREATE TABLE "album" (

"id_album" int4 DEFAULT nextval('album_id_album_seq'::text) NOT NULL,

"nama_album" character varying(30),

"id_pembekal" int4,

"urlgambar" character varying(30),

"penerangan" text,

"id_artis" int4,

"kategori" character varying(30)

);

CREATE TABLE "pesanan" (

"id_pesanan" int4 DEFAULT nextval('pesanan_id_pesanan_seq'::text) NOT NULL,

"id_pelanggan" int4,

"tarikh" date,

"neworder" bool DEFAULT 'true'

);

CREATE TABLE "pesanan_media" (

"id_pesanan" int4,

"id_album" int4,

"id_pembekal" int4,

"kuantiti" int4,

"jumlah_harga" money,

"id_media" int4

);

COPY "lagu" FROM stdin;

COPY "pelanggan" FROM stdin;

COPY "pembekal" FROM stdin;

COPY "album_keluaran" FROM stdin;

COPY "lagu_sampel" FROM stdin;

COPY "artis" FROM stdin;

COPY "album_media" FROM stdin;

COPY "album" FROM stdin;

COPY "pesanan" FROM stdin;

COPY "pesanan_media" FROM stdin;

CREATE UNIQUE INDEX "pelanggan_username_key" on "pelanggan" using btree ( "username" "varchar_ops" );

CREATE UNIQUE INDEX "pembekal_username_key" on "pembekal" using btree ( "username" "varchar_ops" );

CREATE UNIQUE INDEX "artis_id_artis_key" on "artis" using btree ( "id_artis" "int4_ops" );

CREATE UNIQUE INDEX "album_media_id_media_key" on "album_media" using btree ( "id_media" "int4_ops" );

CREATE UNIQUE INDEX "album_id_album_key" on "album" using btree ( "id_album" "int4_ops" );

CREATE UNIQUE INDEX "pesanan_id_pesanan_key" on "pesanan" using btree ( "id_pesanan" "int4_ops" );