location

Migrating from MySQL to Postgres on Ubuntu Server

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

Install Postgres

sudo apt install postgresql postgresql-contrib

Set ps to run on startup

sudo update-rc.d postgresql enable

Next start a postgres session

sudo -u postgres psql

Create Userer and DB

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;
# 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-primary --indent=4 > dbname.json

Import json to Postgres

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

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

Tips

Restarting Postgres

sudo service postgresql restart

Tuning

Edit conf file. You can start a session with PostgreSQL and run SHOW config_file; to see its location.

sudo nano /etc/postgresql/10/main/postgresql.conf

Set shared_buffers to 25% of total ram. Example: with 4gb ram:

shared_buffers = 1024MB