Home » Databases, Featured, Headline, How-to, Linux, Mysql, Postgresql

Migrating from/to MySQL to/from Postgresql

19 March 2009 2 Comments

Migrating from/to Postgresql to/from Mysql

Migrating from/to Postgresql to/from Mysql


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 !


2 Comments »

  • Robert Treat said:

    There are other ways to do the “complicated” things he mentions above, and some of the “simple” things he thinks mysql can do aren’t as useful or simple as stated. I’m not sure if the author lacks either the knowledge or desire to straighten this out, but it seems worth mentioning that many of the examples here can be simplified if you want, despite the authors assertions.

    quick case in point; in the first example, if you are root, you could configure your system to allow you to connect simply via just “psql -U username -d dbname”, which is just as simple as mysql when you factor in needing to issue a use command to get at your data.

  • Remote Management Solutions said:

    Thanks for sharing your thoughts on database.

    Regards

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.