How to import mysql data into postgresql in Heroku?(Django project)

Question

Recently I need to import mysql data into postgres database in Heroku. Actcually it includes several steps:

1. convert mysql data to postgresql
2. import postgresql data to Heroku

After referring plenty of materials and testing several tools in github, finally I succeed. Here I want to share some of my experience and references.

Answer 1

Firstly, I list some tools for converting mysql database format into postgresql format.

1. mysql-postgresql-converter : I finally use this tool and succeed. Dump MySQL database in PostgreSQL-compatible format

mysqldump -u username -p --compatible=postgresql databasename > outputfile.sql

then use the converter to transfer data into *.psql file. then load new dump into a fresh PostgreSQL database.

2. mysql2postgres : It is a tool which is introduced in Heroku Dev Center. Just refer here Migrating from MySQL to Postgres on Heroku. it is based on Ruby. However, as for me, I found some issues after I finish installation and cannot solve it.

You have already activated test-unit 2.5.5, but your Gemfile requires test-unit 3.2.3. #95

3. py-mysql2pgsql: Similar process with mysql2postgres above by editing a *.yml file for configuration. There is a nice reference table in README file called Data Type Conversion Legend, which compares different data type between MySQL and PostgreSQL. You can manually modify the data type.

** This website lists some other converting methods.

Some basic operations in PostgreSQL:

$sudo su - postgres
$createtedb testdb
$psql testdb
=# create user username password ' password ';
-- To change a password:
=# alter role username password ' password ';
=# create database databasename with encoding 'utf8';

How to list all database in postgres: PostgreSQL - SELECT Database

postgres-# \l
                         List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

postgres-# 

Now type the below command to connect/select a desired database, here we will connect to the testdb database:

postgres=# \c testdb;
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=# 

After you create your database, import the converted tables into psql. Kindly note that a database should be created before importing data.

$psql -h server -d databasename -U username -f data.sql

(sometimes a sudo -u postgres should be added before psql)

How to generate dump of psql using pg_dump: creating dump file

$sudo -u postgres pg_dump -Fc --no-acl --no-owner -h localhost -U postgres databasename > mydb.dump

The next step, how to import data into Heroku Postgres? After previous steps, you may have import data into your local PostgresSQL or generate a pg_dump file. Here two methods will be introduced to transfer data to remote Heroku Postgres.

1. use pg_dump file.reference Use the raw file URL in the pg:backups restore command:

   $ heroku pg:backups:restore 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump' DATABASE_URL
   

In this case, you should firstly upload the dump file to somewhere with an HTTP-accessible URL. The dev center in Heroku recommend using Amazon S3. The DATABASE_URL represents the HEROKU_POSTGRESQL_COLOR_URL of the database you wish to restore to. For example, my database url is postgresql-globular-XXXXX.

2. use pg:push pg:push will push data from your local psql database into remote Heroku Postgres database. The command looks like this:

   $heroku pg:push mylocaldb DATABASE_URL --app sushi
   

This command will take the local database "mylocaldb" and push it to the database at DATABASE_URL on the app "sushi". Kindly note that the remote database must be empty before performing pg:push in order to prevent accidental data overwrites and loss. Actually I use this pg:push method and succeed finally.

More information about Heroku Postgres can be found in official document of Heroku.

**others:

1. Viewing logs of your web application in Heroku: heroku logs --tail
2. How to deploy Python and Django Apps on Heroku?

3. How to write the Procfile of Django Apps? A common Procfile of Django projects will look like this:

   web: gunicorn yourprojectname.wsgi --log-file -
   

Here web is a single process type. What we need to modify is yourprojectname.wsgi. Just replace your project name in the prefix.

4. How to add Gunicorn to your application?

   $ pip install gunicorn
   $ pip freeze > requirements.txt
   

5. How to run command line in remote Heroku server? You can execute the bash command in Heroku.

   $heroku run bash
   Running bash attached to terminal... up, run.1
   ~ $ ls
   

Then you can do some command such ls, cd just like in your local bash.

Also, you can use commands in this pattern to execute manage.py in remote Heroku:heroku run python manage.py runserver