All Tips

Migrating from MySQL to Postgres on Ubuntu Server

databasepostgresubuntu
👋 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;

∞ 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-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
location