We will not discuss who is better or who is not, we just try to provide you a simple how-to to migrate from MySQL to Postgres or from Postgres to Mysql. On internet are several documents about that, but I will try to add new fresh informations from my experience with that. The differences between this Postgres and MySQL are starting from the concept of this two platforms. For example Postgres follow more closer the ANSI SQL standard than MySQL, the method of administration is totally different between them, Mysql support several database storage engines, Postgres just one built in. I will show you the differences starting from SQL Syntax to Database administration. I hope all this will be very usefull for you.

Administration Console

In Postgres all authentication is handle by the operating system itself and in Mysql all is handle by MySQL Server.
To connect on Postgres server just run

[root@randombugs]# su – postgres
[postgres@randombugs]#~psql -h server -U username -W
Welcome to psql 8.1.15, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#

In Mysql is more simple

[root@randombugs]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16592
Server version: 5.0.67-0ubuntu6 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

SQL Syntax

Difference	Postgres	MySQL
Line Comment	— Double dash is ANSI SQL Standard	# Sharp is Unix standard but not ANSI SQL
Quotes	‘ for strings and ” for field names and is ANSI SQL	‘ and ” for strings and ` for field names; if we set sql_mode=’ANSI_QUOTES’ in MySQL then ANSI SQL notation is used.
Logical Operators	AND and OR	C operatos; || and &&; in Postgres || means concatenation
String Comparasion	case sensitive	is not case sensitive

Data Types

Postgres	MySQL
SMALLINT	TINYINT
INTEGER	MEDIUMINT
SMALLINT	TINYINT UNSIGNED
INTEGER	SMALLINT UNSIGNED
INTEGER	MEDIUMINT UNSIGNED
NUMERIC(20)	BIGINT UNSIGNED
DOUBLE PRECISION	DOUBLE
TEXT	LONGTEXT
BLOB	BYTEA
AUTO_INCREMENT	SERIAL

SQL Commands

Comment	Postgres	MySQL
Showing Databases	\l	show databases;
Using a database	\c database name	use database name
Showing Tables	\d	show tables;
Describe Table	\d table name	desc table name

Access to database server

In MySQL all can be done from one single command:

grant [privileges] on [database].[table] from [username]@[host] identified by ‘[password]‘;

privileges – select, insert, super, etc
database – database name
table – table name
username – user who will conect to db
host – access from this host
password – access password

In Postgres is a little bit more complicated

CREATE USER [username] WITH PASSWORD ‘[password]‘;
GRANT [privileges] ON [table] to [username]

and the host is handled from /etc/postgresql/pg_hba.conf

host all all 0.0.0.0 0.0.0.0 password

I don’t recommend you the last type of configuration. Just read in pg_hba.conf how to create your access.

Converting from Mysql to Postgres

MySQL already implemented, for mysqldump, a compatbile mode for postgresql. Mysql supports several databases or several compatible modes: ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options,no_table_options, no_field_options. Anyway for converting to postgres just use:

mysqldump -u root -p [databasename] –compatible=postgresql > posgres.sql

Note: Until version 5.1 (if I remember well) the quotes was not correctly used in postgres compatbile mode and you should change the this with sed or with any editor.

Now just upload your script to Postgres

psql -h [server] -d [databasename] -U [username] -W < posgres.sql

Converting from Postgres to MySQL

Postgres doesn’t have any “compatible mode” for MySQL, but are some third party programs for that.
http://www.lightbox.ca/pg2mysql.php is one of them.
pg2mysql is a php program what it comes with a web application, but the guys from weberp write a tutorial about Postgres 2 Mysql migration and they already modify pg2mysql to work from command line: http://www.weberp.org/HowToConvert.

Anyway Migrating from Postgres to Mysql is a hard job … Success !