2 min read

MySQL to Postgresql

I dislike MySQL. I never did like it, since Oracle bought Sun it starting to become more then just disliking it. I prefer to work with Postgresql. I want to migrate some of my MySQL DB to Postgresql. The first try was using the mysqldump with the option of --compatible=postgresql what is looking quite promossing. But it isn't, some extra work! I found on the https://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL a reffert to a gem program. But like ruby gems always, it didn't worked (What do people like about ruby? It seems never to work for me....) But lucky the itnernet is a wide space, so i found this blog post http://onestoryeveryday.com/mysql-to-postgresql-conversionmigration.html the referd perl script was not woring. But it helped me to get the ruby gem to work... Where was the mysql2psql.yml ? Ah... it have to run once the program, then it creates a mysql2psql.yml into the working directory. But here kicks a problem in, you need a mysql connection. So when you have just a dump it's bad. It would mean to install a new mysql db, import the dump and then export it. Else you could save this time and do it directly from the live db.But i had to do it anyway. It's a fine program, that took a bit of time. Thanks for this, but some better shell handling would be welcomed. So far, this are my steps;
sudo apt-get install mysql-server
sudo  apt-get install ruby-dev rake libmysql++-dev libpq-dev
sudo gem install mysql2psql
cd /tmp # Here is where my dump file is stored.
sudo apt-get install mysql-server
mysql -u root -p << EOF
CREATE DATABASE mail;
EOF
mysql -u root -p mail < mail.dump
mysql2psql # Need to edit the config file
mysql2psql # This can take a while
createdb -U postgres mail
psql -U postgres -d mail < mysql_db_to_pg.dump
Here an example for the mysql2psql.yml file:
mysql:
hostname: localhost
port: 3306
socket: /var/run/mysqld/mysqld.sock
username: root
password: apassword
database: mail

destination:
# if file is given, output goes to file, else postgres
file: mysql_db_to_pg.dump
postgres:
hostname: localhost
port: 5432
username: mysql2psql
password:
database: mysql2psql_test

# if tables is given, only the listed tables will be converted. leave empty to convert all tables.
#tables:
#- table1
#- table2
# if exclude_tables is given, exclude the listed tables from the conversion.
#exclude_tables:
#- table3
#- table4
# if supress_data is true, only the schema definition will be exported/migrated, and not the data
supress_data: false

# if supress_ddl is true, only the data will be exported/imported, and not the schema
supress_ddl: false

# if force_truncate is true, forces a table truncate before table loading
force_truncate: false
  so far Akendo