Search This Blog

How to setup PostgreSQL on Linux for Django

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

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.

Verify that psycopg2 was properly compiled and installed by issuing the following from python shell:

The next step is to configure PostgreSQL and then your Django project.

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

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.

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.