All Tips

Moving Django database from MySQL to PostgreSQL on Mac

databasemacpostgres
👋 Thanks for reading! Things have changed since this was written, take it with a grain of salt ;)

∞ Moving from MySQL to PostgreSQL on Mac

∞ Install PostgreSQL with Brew

First install PostgreSQL

brew install postgresql

Allow ps to auto start

pg_ctl -D /usr/local/var/postgres start && brew services start postgresql

∞ Create User and DB

∞ Open PostgreSQL session

## if you need to exit at any time, press ctl+d, or \q
psql postgres

∞ Create a user

CREATE ROLE username WITH LOGIN PASSWORD 'password' ;

∞ Add permissions to user

ALTER ROLE username CREATEDB;

∞ Create a Database

CREATE DATABASE databasename;

∞ Add Authorized Users to Database

GRANT ALL PRIVILEGES ON DATABASE databasename TO username;

∞ Navigation

# List all database
\list

# Connect to database
\connect databasename

# List all tables
\dt

∞ Django Setup

First install the PostgreSQL helper

pip install psycopg2-binary

Next, add the database to settings.py

	,
    'postgresql': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'bdname',
        'USER': 'username',
        'PASSWORD': 'pass',
        'HOST': '127.0.0.1',
        'PORT': '',
    }

Ensure migrations are up to date

python manage.py makemigrations
python manage.py migrate

Migrate settings to the new database and ensure tables are clear for import.

python manage.py migrate --database=posgresql
python manage.py sqlflush --database=postgresql

Export MySQL database to json.

python manage.py dumpdata --all --natural --indent=4 > dbname.json

Import json to PostgreSQL

python manage.py loaddata dbname.json --database=postgresql

Finally change the default database in settings.py to the PostgreSQL connection.

∞ PostgreSQL Config

If you installed with Homebrew, the config file will be here:

/usr/local/var/postgres
location