Mar 1, 2016

How to setup PostgreSQL on Linux for Django

  1. First step is to install PostgreSQL on Ubuntu. In its default installation, Ubuntu does not included PostgreSQL. So uou have to install it separately.
    sudo apt-get install postgresql
  2. Now install the python module for accessing PostgreSQL -- psycopg2. You have two choices here -- either install this from the OS package manager or manually using pip. Which approach to choose depends on your python development environment.

    i. If you're using the system python as development environment, you may install psycopg2 from the OS package manager. You can do this by
    $ sudo apt-get install python-psycopg2

    ii. However, if your python development environment is based on a virtual environment, the above approach will not work. This is because the above installs psycopg2 on the system python dist-packages, which does not copied into the virtual environment when it's created. This is the behavior even if the package is installed before the virtual environment is created. So in this case you need to revert to installing psycopg using the pip from the virtual environment.

    This is where it gets a bit interesting. Pip base installation of psycopg, essentially installs the source code and then uses the OS compiler and libraries to build the python extension modules. One of the essential libraries for this is the PostgreSQL client development libraries. So you need to install this first.
    $ sudo apt-get install libpq-dev

    Buidling a python extension module also requires python development environment, which is not installed by default. You have to install this by:

    $ sudo apt-get install python-dev

    Now install psycopg2 using pip.

    (virtenv) $ pip install psycopg2

    This will download the C source for the python extension module and will trigger these modules to be built. You should a series of messages where gcc is invoked to compile the C source. If all goes well, psycopg2 should be installed to your virtual environment.
  3. Verify that psycopg2 was properly compiled and installed by issuing the following from python shell:
    $ python
    Python 2.7.6 (default, Jun 22 2015, 17:58:13)
    [GCC 4.8.2] on linux2
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import psycopg2

    The next step is to configure PostgreSQL and then your Django project.
  4. When PostgreSQL is installed, it will create the user account 'postgres' in the OS that will be the administrator account for managing PostgreSQL. To administer PostgreSQL, you need to login as this user and then start the PostgreSQL console using 'psql'. But before we go into PgSQL administration tasks, we need to set a password for the user 'postgres'. Do this by switching to root and then using the passwd command

    $ sudo su
    # passwd postgres
    Enter new UNIX password: <password>
    Retype new UNIX password: <password>
  5. Create the database that you want to use with your Django project from PostgreSQL shell.

    $ su - postgres
    Password: <password>
    postgres@hostname:~$ createdb <dbname>

    Note how we changed the user context to postgres, thePostgreSQL administrator before we created the database.

    Another point here is that createdb is actually an external Perl script that is installed when PostgreSQL is installed. This, along with a few others such as createuser, are convenience wrappers to ease PostgreSQL administration directly from the bash rather than having to launch the PostgreSQL shell and then issue relevant commands. 
  6. Next step is to create a role that will be used to access this database. A role is a PostgreSQL internal user that is independent of the operating system user. You can assign access privileges to roles to different database objects. Every connection to the database server is made using the name of some particular role, and this role determines the initial access privileges for commands issued in that connection. For example, creating new databases requires a different privilege from the login privilege.

    Only roles that have the LOGIN attribute can be used as the initial role name for a database connection. Without going too deep into the internals, you can use the helper script, createuser, to create a role with login privilege.

    postgres@hostname:~$ createuser -P 
    Enter password for new role: <password>
    Enter it again: <password>
  7. Grant the necessar privileges on the new database to the newly created user. You do this from psql prompt from PostgreSQL administrator.

    postgres@lubuntu1:~$ psql
    psql (9.3.11)
    Type "help" for help.
    postgres=# GRANT ALL ON DATABASE  to 

    Note that you don't have to grant all privileges. You may be selective.
  8. Now configure Django to use the newly created PostgreSQL by defining this in the project

        'default': {
             'ENGINE': 'django.db.backends.postgresql_psycopg2',
             'NAME': '<dbname>',
             'USER': '<dbuser>',
             'PASSWORD': '<dbuser password>',
             'HOST': 'localhost',
             'PORT': '',
  9. From your Django project, issue

    $ python ./ migrate

    If your installation was successfully completed, various tables used by the Django project should be created in your newly minted database.