Migrating from mysql to postgresql

  • Tutorial
Hello to the respected community!

At some point in time, there was a need to transfer the application database on django from mysql to postgresql. The first two approaches to this problem were unsuccessful, but they made it possible to deal with data integrity and to eradicate problems for manage.py syncdband manage.py migrate.

On the first run, we tried to transfer the database by converting the sql sheet to the postgresql dialect.
On the second approach, we tried to transfer through ./manage.py dumpdata, but errors with keys, invalid data constantly got out (there were a lot of manual corrections in the database).

A lot of time passed between the second and third calls, and the last googling on the problem led me to this article. Morally, I was ready to analyze and parse line-by-line footcloths sql / yml weighing gigabytes, there were blanks for this process ... and yet I decided to try and insist on the simplicity of the process.

Well, let's go (everything was done in virtualenv, an empty database was created in postgresql):
$ pip install py-mysql2pgsql
$ py-mysql2pgsql
No configuration file found.
A new file has been initialized at: mysql2pgsql.yml
Please review the configuration and retry...
$ vim mysql2pgsql.yml
$ py-mysql2pgsql -v -f mysql2pgsql.yml

After 5 minutes of waiting (everything was done in a virtual machine, with a not-so-advanced configuration). We flew out a couple of times, with an error ОШИБКА: нулевое значение в колонке "created" нарушает ограничение NOT NULL, in my case this could be solved by deleting the record in the muscle table.

After we check with the new database configuration: manage.py run_gunicorn- everything starts without errors. Now it’s time for optimization.

I hope this description helps those who encounter a similar problem.

Also popular now: