3. DataBases¶

3.1. PostgreSQL¶

3.1.1. Postgres Shell¶

In Ubuntu use sudo -u

sudo -u postgres psql
# or \list will list all the databases, their owner and access privileges
postgres=# \l

3.1.2. Create and Delete a PostgreSql DB¶

Use terminal to go to django directory where DB should be located. Create a user if there is not one already created. Create DB.

postgres=# CREATE USER <username>;
postgres=# ALTER ROLE <user> WITH PASSWORD; <password> # change userpassword
postgres=# CREATE DATABASE <dbname> OWNER <username>;
# 'IF EXISTS' prevent an error from removing database
postgres=# DROP DATABASE [IF EXiSTS] <dbname>;
# If active connections
postgres=# SELECT * FROM pg_stat_activity WHERE datname = '<dbname>';
# returns pif
postgres=# SELECT pg_terminate_backend (pid)
postgres=# FROM pg_stat_activity
postgres=# WHERE pg.stat_activity.datname = '<dbname>'
postgres=# DROP DATABASE <dbname>;

Drop postgresdb

3.1.3. Update Settings in Django¶

Find <mysite>/settings.py file. Go down to the DATABASES section and you should see the following.

DATABASES = {
    'default': {
        'ENGINE" 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

Replace with this-

DATABASES = {
    'default': {
        'ENGINE" 'django.db.backends.postgresql',
        'NAME': '<db_name>',
        'USER': '<db_username>'
        'PASSWORD': '<db_password'
        'HOST': 'localhost',
        'PORT': ''
    }
}

You will also need to install psycopg2 into your virtual environment. This is needed for django to access the postgreSQL database. You may run into an error - ‘./psycopg/psycopg.h:36:10: fatal error: libpq-fe.h: No such file or directory’. To fix this error you will need to install ligpq-dev. libpq-dev can’t be installed with pip because it is not part of PyPi. You will need to install it using sudo apt install libpq-dev. After installing libpq-dev you should be able to do a pip install psycopg2.

After replacing the DB settings you will also have to

(venv) #:python manage.py makemigrations <project name>
(venv) #:python manage.py migrate <project name>

3.2. ER Diagrams and Tools¶

My requirements:

free
reverse engineer from a database

What I found 8/21/20

dbdiagram.io web interface
pgModeler simple, not free
DBeaver complex

3.2.1. Install DBeaver¶

$ sudo snap install dbeaver-ce

Installed DBeaver and created ER diagram. Diagram did not default to standard ER graphic notation. Also would be nicer if I could tell 1-many and many-many relationships on Table Columns. Still everything seems to work ok.