igniteflow/migrating-from-mysql-to-postgres.rst

Last active January 12, 2020 11:35

Star (10) You must be signed in to star a gist
Fork (6) You must be signed in to fork a gist

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/igniteflow/5670193.js"></script>
Save igniteflow/5670193 to your computer and use it in GitHub Desktop.

Download ZIP

Migrating from MySQL to Postgres in Ubuntu

Raw

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

r0d0dendr0n commented Dec 5, 2018

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!