Redmine: migrating from MySQL to PostgreSQL 9.5

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

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

    The following describes one way to migrate from a MySQL database to PostgreSQL 9.5 for Redmine on CentOS 6.



    So, let's start:

    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 corrected settings to the end of the file.

    4. We configure PostgreSQL authorization in such a way that only localhost can log in via the network or through the socket, and root can go 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 a redmine database 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. We write down the PostgreSQL authorization data, comment on the 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 had no effect - Redmine stubbornly threw an error when starting migrations.

    Configure py-mysql2pgsql.

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


    We do the first run, create a configuration file template:
    py-mysql2pgsql


    We edit py-mysql2pgsql.yml, enter the authorization data for MySQL and the new PostgreSQL database.

    We carry out 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 migrated a 200 GB Zabbix database from MySQL to PostgreSQL. Works!
    Tags:

    Also popular now: