Database Setup

Notes

I have developed this guide with Ubuntu in mind. If you're using a different Linux distro/Operating System, pretty much everything but the prerequisites section should still apply.

PostgreSQL Prerequisites

Install PostgreSQL

# On Ubuntu:
$ sudo apt-get update
$ sudo apt-get install postgresql-9.1 postgresql-server-dev-9.1
$ sudo build-dep pyscopg2

Once you have all the prerequisite packages installed, you can install psycopg2 in the virtual environment.

$ cd ~/some/directory/sse-library
$ source env/bin/activate
$ pip install psycopg2
$ deactivate

PostgreSQL configuration

PostgreSQL doesn't work directly with user accounts. Instead, you must create a database role which can conceptually be treated as a database user. A role can represent a single user or an entire group of users, but for the purposes of this guide we will only use single user roles. If you see the term "database user" from this point forward, assume that it is referring to a database role.

By default, PostgreSQL will be set up with just 1 superuser; this user is named "postgres" and is capable of setting up other users for us.

When you call a PostgreSQL command, it will assume that you would like to run that command using your Linux/Mac OS X/Windows username (TODO: Double check this this is the case on Windows and Mac OS). However, by default PostgreSQL will not setup a role for your useraccount. What this means is that in order to actually do anything with PostgreSQL, we would have to run a command like the following

$ psql # This will fail and complain about the user database not existing.
$ sudo -u postgres psql # This will work but is a pain to type.

That kind of sucks, so we're going to go ahead and create a dedicated PostgreSQL superuser for your login account.

$ sudo -u postgres createuser --superuser $USER

This will create a user for your account; in other words, if your username is "test" then this will create a database role named "test". This doesn't have to be your username; If you want to specify a different username to use, just replace $USER with your username of choice.

By default, the user we created won't have a password, so lets give it one. You don't have to give the user a password for a developer machine, but its a very good idea to have a database password for this user in a live setup.

$ sudo -u postgres psql
psql (9.1.4)
type "help" for help.

postgres=# \password *insert database role name here*

You will be asked to enter your password twice. Now that we have a role set up with a password, we should create a database for this user.

$ createdb $USER

Once again, you can replace $USER with whatever you want. Your database role should be ready to go now. You can test that your base PostgreSQL setup is working by running the following command:

$ psql

If you decided to create a role with a different name than your username, use the following instead.

$ psql -U *role name*

If you don't get any error messages or warnings and the psql screen pops up, then you are golden. Exist the psql screen we just launched by entering in "\q". Now that we have general PostgreSQL stuff set up, let's move on to your project specific settings.

Project specific setup

First, we'll want to create a project-specific user. This can be named whatever but we'll stick with librarian here.

$ createuser -P librarian
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

Set up a database for the project.

$ psql template1
psql (9.1.4)
Type "help" for help.

template1=# CREATE DATABASE sse_library OWNER librarian ENCODING 'UTF8';
CREATE DATABASE
template1=# \q

You can call this database whatever you want. Note that the librarian user we created owns the database.

If local_settings.py is not present in the same directory as settings.py, create it and copy/paste the following python array into it.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'sse_library',                      # Or path to database file if using sqlite3.
        'USER': 'librarian',                      # Not used with sqlite3.
        'PASSWORD': 'librarian_password',                  # Not used with sqlite3.
        'HOST': 'localhost',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
    }
}

Now, run python manage.py syncdb. Assuming everything was setup properly, you should have a working database PostgreSQL Database.