Move from MySQL to PostgreSQL database in Django on Shared Hosting

Using the PostgreSQL database instead of a MySQL database with Django has many advantages. I wrote a post here on using PostgreSQL in Django on shared hosting. But, if you already have a Django app that using a MySQL database, here is how you can migrate it from MySQL to PostgreSQL database on shared hosting.

Table of Contents

The hosting plan that you are using must provide PostgreSQL databases in addition to the most common MySQL databases. I am using the Stellar Plus plan of NameCheap shared hosting that has unlimited PostgreSQL databases.

Make sure to back up your project and database in case anything goes wrong.

Create a PostgreSQL Database

Go to PostgreSQL databases in your CPanel and create a new database.

Create a new user and add that user to the newly created database.

Add the new database to settings.py

Now edit your settings.py and add a new PostgreSQL database without changing the default one. Add the following code:

'pgsql': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'database_name',
        'USER': 'database_username',
        'PASSWORD': 'database_password',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }

Make sure to insert your database name, username and password in the above code. The settings for databases will look like this:

Install psycopg2

Open terminal in your CPanel and enter the virtual environment. Then run the following command:

pip install psycopg2-binary

Go to Setup Python app in CPanel and restart the python app you are using to run Django.

Create Tables in the new Database

Now you have to create tables in the new PostgreSQL database. To do this, run the following command:

python manage.py migrate --noinput --database=pgsql

Although, the new database is empty, but run the following command to delete all the data in the new database to make sure it is empty before we load data from the old database into it.

python manage.py flush --database=pgsql

Type yes and press enter if it asks for confirmation.

Export data from the old Database

In this step, you will export all data from the old database into a json file. Run the following command to do this:

python manage.py dumpdata>alldata.json

This will export all the data into a file alldata.json.

Import data into the new Database

Run the following command to import the data exported in the previous step into the new database:

python manage.py loaddata alldata.json --database=pgsql

Change the default Database

Now edit your settings.py and change the name of the new database form pgsql to default and remove the old database. The code will look like this:

Go to setup python app in CPanel and restart the python app.

You have successfully migrated your app from MySQL to PostgreSQL database. If you have any questions, ask them in the comments and I will add the answer in the FAQ section here.

Useful Links

Post Views: 2,109