Set up psql database in django.

program-ape
, , , , , , , , , , ,

I use sqlite as my django database, but there are some limitations in sqlite. So I want to switch to mysql or psql(PostgreSQL). Both of them need a little bit more setup than sqlite, but still quick easy.

  1. Install database client.
  2. Create user.
  3. Create database.
  4. Modify django settings.

Let’s begin with mysql.

1
2
3
4
5
6
7
8
# mysql is already installed in Ubuntu.
$ mysql -u root -p
# enter root password
# create new user "test" with password "test"
mysql> create user 'test'@'localhost' identified by 'test';
# create database test.db
mysql> create database test.db;
Query OK, 1 row affected (0.00 sec)

Now let’s see how to do it for psql.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# We need to install a couple of packages first.
$sudo apt-get install libpq5
$sudo apt-get install postgresql-common
$sudo apt-get install postgresql-client-common
$sudo apt-get install postgresql-client-9.1
$sudo apt-get install postgresql-9.1
$sudo apt-get install python-psycopg2 (or $pip install psycopg2)

# create user "test" with password "test"
$sudo -u postgres createuser test -P -s -e

# login as postgres
$sudo -u postgres psql postgres
# list all user names
$postgres SELECT rolname FROM pg_roles;
# create test.db
$postgres CREATE DATABASE test;
CREATE DATABASE

# list all databases
$sudo -u postgres psql -l

Ok now we created user name, password and database, we need to modify settings.py accordingly.

1
2
3
4
5
6
7
8
9
10
11
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2', # for psql
        #'ENGINE': 'django.db.backends.mysql', # for mysql
        'NAME': 'test.db',
        'USER': 'test',
        'PASSWORD': 'test',
        'HOST': 'localhost',
        'PORT': '',
    }
}

We are all set!