Skip to main content

Django Migrates Database From MySQL To PostgreSQL

1.1 Django migrates database from MySQL to PostgreSQL​

Overview​

note

In this tutorial, we will migrate our database from MySQL version 5.7 to PostgreSQL version 15.7 while using Django version 5.0.6. Although this guide is tailored for Django 5.0.6, some of the steps may vary slightly with different versions of Django.

Preparation​

  1. Configure the New Database

First, let's set up our new PostgreSQL database in Django's settings. Update your settings.py to include both databases until the migration is complete:

/projects/manga/manga/settings.py
DATABASES = {
	'default': {
		"ENGINE": os.environ['DB_ENGINE'],
		"NAME": os.environ['DB_NAME'],
		"USER": os.environ['DB_USER'],
		"PASSWORD": os.environ['DB_PASSWORD'],
		"HOST": os.environ['DB_HOST'],
		"PORT": os.environ['DB_PORT'],
	},
	'postgresql': {
		"ENGINE": "django.db.backends.postgresql_psycopg2",
		"NAME": os.environ['DB_NAME'],
		"USER": os.environ['DB_USER'],
		"PASSWORD": os.environ['DB_PASSWORD'],
		"HOST": os.environ['DB_HOST'],
		"PORT": os.environ['DB_PORT'],
	}
}
  1. Migrate the Database Schema

Apply the existing migrations to your new PostgreSQL database with the following command:

python3 manage.py migrate --database=postgresql

Note: The syncdb command is deprecated in newer versions of Django, and the --no-initial-data flag has also been removed.

  1. Clean Up the New Database

Before transferring the data, ensure the new database is clean. This step involves removing any initial data that might conflict with your existing data:

python3 manage.py sqlflush --database=postgresql
  1. Transfer Data

To move your data, use Django's dumpdata and loaddata commands. Make sure to use the --natural-foreign option to handle foreign keys correctly:

# Dump all data from the old database
python3 manage.py dumpdata --all --natural-foreign > /tmp/dump_YYYYMMDD.json

# Load the data into the new PostgreSQL database
python3 manage.py loaddata /tmp/dump_YYYYMMDD.json --database=postgresql
  1. Update Configuration

After transferring all data, configure PostgreSQL as the default database in your settings.py:

/projects/manga/manga/settings.py
DATABASES = {
	'mysql': {
		"ENGINE": os.environ['DB_ENGINE'],
		"NAME": os.environ['DB_NAME'],
		"USER": os.environ['DB_USER'],
		"PASSWORD": os.environ['DB_PASSWORD'],
		"HOST": os.environ['DB_HOST'],
		"PORT": os.environ['DB_PORT'],
	},
	'default': {
		"ENGINE": "django.db.backends.postgresql_psycopg2",
		"NAME": os.environ['DB_NAME'],
		"USER": os.environ['DB_USER'],
		"PASSWORD": os.environ['DB_PASSWORD'],
		"HOST": os.environ['DB_HOST'],
		"PORT": os.environ['DB_PORT'],
	}
}

Once you've completed the above steps, test your application thoroughly to ensure everything works as expected with the new PostgreSQL database. Adjust your configurations or troubleshoot any issues that arise during testing.

Reference​

For additional details and tips on migrating from MySQL to PostgreSQL using Django, visit this comprehensive guide https://blog.libove.org/posts/django-migrate-mysql-to-postgres/

With this guide, migrating your Django application from MySQL to PostgreSQL should be smooth and efficient. Happy coding!