Migrating from MySQL to Postgres in Ubuntu

migrating-from-mysql-to-postgres.rst

Migrating from MySQL to Postgres in Ubuntu

Dump your database:

mysqldump --compatible=postgresql --default-character-set=utf8 -r databasename.mysql -u root databasename

Convert the dump to Postgres syntax using https://github.com/lanyrd/mysql-postgresql-converter:

wget https://raw.github.com/lanyrd/mysql-postgresql-converter/master/db_converter.py
chmod +rx db_converter.py
python dbconverter.py databasename.mysql databasename.psql

Install postgres:

apt-cache search postgres # find the most recent
sudo apt-get install postgresql-x.x

Create the database and insert the data:

sudo -u postgres createdb databasename
psql databasename -U postgres -h 127.0.0.1 < databasename.psql

python dbconverter.py databasename.mysql databasename.psql

It should be db_converter.py

I was migrating redmine from mysql to postgres. Afterwards I also had to:

• Change type "bytea" to "text".
• Execute "alter table journals alter COLUMN private_notes drop not null;".

Idk, maybe some more alters will be needed. So far my redmine on postgresql seems to work just fine. Thanks for your script!