How to migrate from MySQL to PostgreSQL

Added by Eduardo Turiño over 7 years ago

Hi. I have redmine 0.8.7 with mysql 5.0, and I need to migrate to a postgresql database. I've tried with some mysql-to-postgre migration scripts but it always fails when i try to access any project.

How can i change from mysql to postgres?

Replies (9)

RE: How to migrate from MySQL to PostgreSQL - Added by Eduardo Turiño over 7 years ago

nevermind. I'll post here what i did in order to get it right:
- first, install redmine in a fresh postgre database
- then, copy (i did it table by table) the rows from every table from the mysql database to the postgresql, truncating the postgresql tables first.
- select max(id) from MyTable; => max(id) + 1 (MaxIdPlusOne) => use that number in an ALTER SEQUENCE MyTable_id_seq RESTART with MaxIdPlusOne; That for every table with a sequence.
And you should be good to go.

RE: How to migrate from MySQL to PostgreSQL - Added by Thomas Chemineau almost 7 years ago

Hi,

I am facing to the same problem. Except I am trying to migrate from PostgreSQL to MySQL.
Could someone have some hits to do that ?

I am currently using:
- the pgsql_dump command with special parameters;
- the sqlt command to convert the dump to MySQL.

And I am having still some errors.

Any help would be appreciated :)

RE: How to migrate from MySQL to PostgreSQL - Added by Felix Schäfer almost 7 years ago

You generally can't just "sqldump" activerecord/rails data from one DB type to another, as the adapters have database-specific optimizations.

There are different means to transfer the data from one DB type to the other though, your could try taps, see also this article about its usage: http://adam.heroku.com/past/2009/2/11/taps_for_easy_database_transfers/

RE: How to migrate from MySQL to PostgreSQL - Added by Thomas Chemineau almost 7 years ago

Thank you for your help.

I read about your links and taps. And I was finaly able to migrate my redmine database from postgresl to mysql, without any errors. It is really a powerfull tool :)

RE: How to migrate from MySQL to PostgreSQL - Added by Viktor Berke about 4 years ago

+1 for the taps method, works fine with SQLite 3 -> PostgreSQL. OTOH it failed when I tried to convert to MySQL.

RE: How to migrate from MySQL to PostgreSQL - Added by Erdem Kaya almost 4 years ago

Is there anyone successful with this method while trying to migrate from MySQL to PostgreSQL?

RE: How to migrate from MySQL to PostgreSQL - Added by Vincent Lizzi over 3 years ago

I've just migrated a Redmine installation from MySQL to PostgreSQL using mysql2psql using the process below. Starting with a working Redmine installation running on Passenger and MySQL; and PostgreSQL is available on the same host.

Create a PostgreSQL database.
SSH into your host, then:

gem install mysql2psql
mysql2psql

This creates a blank configuration file. Edit it to set the connection details for your mysql database and new postgresql database. I added an encoding setting to source and destination based on my existing database config even though an encoding setting was not in the sample config: encoding: utf8

vim mysql2psql.yml

Run the transfer:
mysql2psql

Edit Redmine database config.

cd ~/rails_apps/redmine-2.2.0/config
cp database.yml database.yml.backup
vim database.yml
cd ..

Create (or edit) Gemfile.local to install the postgresql adapter:
vim Gemfile.local

# Gemfile.local
gem 'activerecord-postgresql-adapter'

bundle

Restart Passenger:
touch tmp/restart.txt

Refresh the web browser and log in to Redmine.

RE: How to migrate from MySQL to PostgreSQL - Added by yogi viegelinsch over 2 years ago

Vincent Lizzi wrote:

I've just migrated a Redmine installation from MySQL to PostgreSQL using mysql2psql using the process below. Starting with a working Redmine installation running on Passenger and MySQL; and PostgreSQL is available on the same host.

Create a PostgreSQL database.
SSH into your host, then:

gem install mysql2psql
...

I can confirm mysql2psql as working. Thank you for this tip.

Just some hints for doing this on a current Ubuntu:

Before installing the gem you need to apt-get install ruby-dev libmysqlclient-dev postgresql-server-dev-all
Then gem install mysql2psql works smooth.
After installing the gem, I recommend the tripple:
  1. apt-get purge ruby-dev libmysqlclient-dev postgresql-server-dev-all
  2. apt-get autoremove
  3. apt-get autoclean

This should help to keep your system clean.

HTH

RE: How to migrate from MySQL to PostgreSQL - Added by Shak Shak 4 months ago

I successfully used yaml_db to do this migration, making sure to migrate the destination db only up to the db version of the data I was going to seed it with (after which I proceeded to migrate it the rest).

(1-9/9)