TaskBuster Django Tutorial

Part VII.a – Install and Configure PostgreSQL

Now it’s time to configure the database of our project. In this part of the tutorial we will cover PostgreSQL, and in part VII.b we will cover MySQL. Feel free to choose whichever you want! 🙂

However, keep in mind that in a later part of this tutorial we will deploy our app in Heroku, and we will use PostgreSQL. So if you don’t know which database to configure, I would recommend PostgreSQL 😉

The outline of this part is the following:

Let’s start! 🙂

Install PostgreSQL

In my case I have Mac OS X, (Mountain Lion and/or Yosemite), and I used the Graphical Installer, recommended on the download page of PostgreSQL. It includes PostgreSQL, pgAdmin and the StackBuilder utility, for installation of additional packages.

During the installation process, you will need to set a password for the database superuser account (on Postgres).

Next, try to run

$ pg_config

If you get a not found error, you will need to first find the path of this command with:

$ sudo find / -name pg_config

This should be something like /Library/PostgreSQL/9.3/bin. You need to add it to the $PATH variable, so open the .bash_profile file and add the line:

export PATH=/Library/PostgreSQL/9.3/bin:$PATH

Restart your terminal and try again to type pg_config. Hopefully it will work 😉

Create a PostgreSQL Database

On mac, the command

$ which psql

should point to the Postgres app. And this other command should start the Postgres command line utility:

$ psql -h localhost

If this command rises an error like psql: FATAL: password authentication falied for user “username”, and you’re sure you have entered your password correctly, you have to enter psql using the postgres user:

$sudo -u postgres psql

On the other hand, if you get an error like psql: FATAL: database <user> does not exist, it might be because your package manager failed to create the proper database, see this post for more info. To solve this problem, try:

$ createdb
$ psql -h localhost
(or $ sudo -u postgres psql)

Now, you should be in the command line of Postgres. You can exit this environment by typing

\q

Similarly, you can type \? for more help, \list to list your databases and \du to list your users.

Next, we will create a new database for our project and a new user to whom you will give access to the database:

$ createdb taskbuster_db
(or $ sudo -u postgres createdb taskbuster_db)
$ psql
(or $ sudo -u postgres psql)
CREATE ROLE myusername WITH LOGIN PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE taskbuster_db TO myusername;
ALTER USER myusername CREATEDB;

Where you should change myusername to the user’s name you want, and mypassword to whatever you want. And don’t forget the ; at the end of each statement! 😉

Install the PostgreSQL Django adapter, psycopg2

Next, we need to install a PostgreSQL database adapter for Python: the psycopg2 package:

$ pip install psycopg2

Finally, add it into your requirements/base.txt file, and install it into your working environments (testing and developing).

Configure the Django Database Settings

Next, we need to specify PostgreSQL as our database in the settings file. As this is the local database, we need to redefine the DATABASES variable in the testing and developing settings files. Edit both files, settings/developing.py and settings/testing.py and add:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': get_env_variable('DATABASE_NAME'),
        'USER': get_env_variable('DATABASE_USER'),
        'PASSWORD': get_env_variable('DATABASE_PASSWORD'),
        'HOST': '',
        'PORT': '',
    }
}

Remember that these files import from the settings/base.py file, in which we defined the get_env_variable function as:

from django.core.exceptions import ImproperlyConfigured

def get_env_variable(var_name):
    try:
        return os.environ[var_name]
    except KeyError:
        error_msg = "Set the %s environment variable" % var_name
        raise ImproperlyConfigured(error_msg)

Next, edit the postactivate file of each environment:

$ vi $VIRTUAL_ENV/bin/postactivate

and add the Database settings:

export DATABASE_NAME='taskbuster_db'
export DATABASE_USER='myusername'
export DATABASE_PASSWORD='mypassword'

Now edit the predeactivate file of each enviroment and add:

unset DATABASE_NAME
unset DATABASE_USER
unset DATABASE_PASSWORD

To make these changes effective, you need to deactivate and activate the environments.

Ok, now we are ready to check and sync and migrate our database:

$ python manage.py check
$ python manage.py migrate

You only need to migrate the database in one environment (developing or testing). This is because the migration is applied to the database, which is the same for both of them.

Depending on your Django version, you will be asked to create a super user, so create one now 😉 Otherwise, create one with:

$ python manage.py createsuperuser

If you are working on a Mac and find an error like

django.core.exceptions.ImproperyConfigured: Error loading psycop2 module
Library not loaded: libssl.1.0.0.dylib
Referenced from: .../psycopg2/_psycopg.so
Reason: image not found

You should run:

$ sudo ln -s /Library/PosgreSQL/9.4/lib/libssl.1.0.0.dylib /usr/lib
$ sudo ln -s /Library/PosgreSQL/9.4/lib/libcrypto.1.0.0.dylib /usr/lib

Try to check and migrate again. If this doesn’t work and you still get the same error, try to add the following line into you .bash_profile:

export DYLD_FALLBACK_LIBRARY_PATH=/Library/PostgreSQL/9.4/lib:$DYLD_LIBRARY_PATH

If you are working on a Mac and find an error like:

django.db.utils.OperationalError: could not connect to server: No such file or directory
Is the server running locally and accepting connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

You have to include the following line into the ~/.bash_profile  file:

export PGHOST=localhost

Check and try to migrate again.

Another possible error is the following:

raise ImproperlyConfigured("Error loading psycopg2 module: %s" % e)
django.core.exceptions.ImproperlyConfigured: Error loading psycopg2 module: dlopen(/Users/user/.virtualenvs/tb_dev/lib/python3.4/site-packages/psycopg2/_psycopg.so, 2): Symbol not found: _lo_lseek64
  Referenced from: /Users/user/.virtualenvs/tb_dev/lib/python3.4/site-packages/psycopg2/_psycopg.so
  Expected in: /usr/lib/libpq.5.dylib
 in /Users/user/.virtualenvs/tb_dev/lib/python3.4/site-packages/psycopg2/_psycopg.so

You have to create symlink for the most recent libpq.5.dylib file inside the PosgreSQL directory:

$ sudo ln -fs /Library/PostgreSQL/9.4/lib/libpq.5.6.dylib /usr/lib/libpq.5.dylib

Note that if you’re not using Mac or a different version of PosgreSQL the folder path might be different.

Check and try to migrate again.

 

Finally, let’s run our tests to see that everything works as expected!

$ python manage.py test

Hope is everything ok?!! Did you see how useful are tests? Now we know that after changing the database, everything works as good as before 😉

 

In the next part of this tutorial, we’ll cover User Authentication with social accounts, like for example Google or Twitter.

Keep working! 😉

Don’t forget to share it to your friends, they might find this tutorial helpful! Thanks!

Leave a Reply