Redmine: Migration from MySQL to PostgreSQL 9.5

Recently, customers of our company are increasingly asking to make the transition from MySQL to PostgreSQL. Indeed, the active development of PostgreSQL gives developers a reason to think about changing the DBMS. Often, this raises the question of transferring large amounts of data, which the existing database has undergone with MySQL.

We ourselves are active users of Redmine and plugin developers for it, so we chose this CRM to illustrate the migration process.

The following describes one of the ways to migrate from a MySQL database to PostgreSQL 9.5 for Redmine on CentOS 6 OS.
')


So, let's begin:

1. First, install the PostgreSQL 9.5 repository

rpm -ihv https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm 

2. Install PostgreSQL 9.5 itself.

 yum install postgresql95-server postgresql95-contrib postgresql95-devel service postgresql-9.5 initdb ru_RU.utf8 chkconfig postgresql-9.5 on alternatives --install /usr/bin/psql psql /usr/pgsql-9.5/bin/psql 5000 alternatives --install /usr/bin/pg_dump pg_dump /usr/pgsql-9.5/bin/pg_dump 5000 alternatives --install /usr/bin/pg_config pg_config /usr/pgsql-9.5/bin/pg_config 5000 alternatives --install /usr/bin/createdb createdb /usr/pgsql-9.5/bin/createdb 5000 alternatives --install /usr/bin/createuser createuser /usr/pgsql-9.5/bin/createuser 5000 alternatives --install /usr/bin/dropdb dropdb /usr/pgsql-9.5/bin/dropdb 5000 alternatives --install /usr/bin/dropuser dropuser /usr/pgsql-9.5/bin/dropuser 5000 alternatives --install /usr/bin/pg_dumpall pg_dumpall /usr/pgsql-9.5/bin/pg_dumpall 5000 alternatives --install /usr/bin/createuser createuser /usr/pgsql-9.5/bin/createuser 5000 

3. We do PostgreSQL tuning using pgtune.leopard.in.ua , add the adjusted settings to the end of the file.

4. Configure PostgreSQL authorization so that you can only authenticate from localhost over the network or through a socket, and root can walk under the user “postgres” without a password.

pg_hba.conf:

 local all postgres ident map=supervisor host all all 127.0.0.1/32 md5 

pg_ident.conf:

 supervisor postgres postgres supervisor root postgres 

5. Create a user and DB “redmine” in PostgreSQL:

 createuser --username=postgres --no-superuser --no-createdb --no-createrole --encrypted redmine createdb --username=postgres --encoding=utf-8 --template=template0 --owner=redmine redmine psql --username=postgres --dbname=postgres --command="ALTER USER \"redmine\" WITH ENCRYPTED PASSWORD 'yourgreatpassword'" 

6. Writing PostgreSQL authorization data, commenting on MySQL authorization in redmine / config / database.yml:

 #  ,     (!) production: adapter: postgresql database: redmine host: localhost username: redmine password: "yourgreatpassword" 

7. Install the tool for data migration.

My choice was py-mysql2pgsql. Tools like yaml_db did not give effect - Redmine stubbornly gave an error when starting migrations.

We configure py-mysql2pgsql.

Install:

 yum install python-pip python-devel pip install py-mysql2pgsql 

Make the first run, create a configuration file template:

 py-mysql2pgsql 

We edit py-mysql2pgsql.yml, we enter the data of authorization of MySQL and the new PostgreSQL base.

Perform data migration. For a 1 GB database on an average server, this process took about 15 minutes.

 py-mysql2pgsql -v -f mysql2pgsql.yml 

That's all!

Ask any questions, write your recommendations. I will be glad to discuss in the comments.

PS Similarly, we have migrated a 200 GB Zabbix database from MySQL to PostgreSQL. Works!