Migrating django app from MySQL to Postgres

In this tutorial, we will take a django app backed by MySQL and will convert MySQL database to postgres database. This is useful if we are deploying our app to Heroku because Heroku uses standard Postgres

Prerequisites:

  • We assume that you have a running django app with MySQL as a database.
  • Also, your app is running on virtualenv.

Converting MySQL to Postgres:

Let’s assume that you have a django app running with MySQL and you want to convert this to Postgres.

1) Install dependenicies:

$ pip install psycopg2
$ pip install py-mysql2pgsql

2) Create Postgres database:

[email protected]:~$ psql
psql (9.1.11)
Type "help" for help.

postgres=# create database my_database;
CREATE DATABASE
postgres=#

3) Run:

$ py-mysql2pgsql

At initial run this command creates a file named mysql2pgsql.yml having the below info:

mysql:
hostname: localhost
port: 3306
socket: /tmp/mysql.sock
username: foo
password: bar
database: your_database_name
compress: false
destination:

postgres:
hostname: localhost
port: 5432
username: foo
password: bar
database: your_database_name

Update the above configuration file with appropriate database credentials for both ‘MySQL’ and Postgres.

4) Run:

$ py-mysql2pgsql -v -f mysql2pgsql.yml

The above command will transfer the data from MySQL database to Postgres.

Note:

  • The above command may raise some Integrity errors, but no worries it can be fixed. :)
  • You can also include or exclude some tables check this Here

5) Be sure to update your database settings.py file:

DATABASES = {
"default": {
   "ENGINE": "django.db.backends.postgresql_psycopg2",
   "NAME": "your_database_name",
   "USER": "your_username",
   "PASSWORD": "your_password",
   "HOST": "localhost",
   "PORT": "5432",
}
}

6) Verify the correctness by adding some data to your existing database(Postgres).

Thank you for reading the Agiliq blog. This article was written by Manjunath Hugar on May 27, 2014 in mysqltopostgres .

You can subscribe ⚛ to our blog.

We love building amazing apps for web and mobile for our clients. If you are looking for development help, contact us today ✉.

Would you like to download 10+ free Django and Python books? Get them here