Migrating from/to MySQL to/from Postgresql
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.
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
In Mysql is more simple
[root@randombugs]# mysql -u root -p
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.
|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|
|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 !