Home » Odeon Blogs » Calvin, Weaver »

PostgreSQL cheat sheet for django beginners

22 Aug 7

PostgreSQL cheat sheet for django beginners

Here's a simple article explaining the basics of interacting with PostgreSQL from the command line and from the shell terminal; and telling django to connect to it.

1. What is a postgresql superuser?

Once you have successfully installed postgresql on your OS, it installs a single superuser by default named postgres.

When you want to create your own PostgreSQL database for a particular Django project, you can use this default superuser to help you create a new PostgreSQL user, its corresponding password, and a new PostgreSQL database.

Some notes before you begin...

the $ sign denotes your terminal/command line shell prompt.
the # sign denotes your postgresql shell prompt.

2. Checking

If you have successfully installed PostgreSQL, you should be able to do the following in your terminal:-

$ which psql
/opt/local/lib/postgresql90/bin/psql

Entering the postgresql shell with your "postgres" superuser:-

$ psql -U postgres
psql (9.0.4)
Type "help" for help.

postgres=# \q

$

As you can see, \q is the postgresql command you give to exit the postgresql shell (and you are returned to your terminal prompt).

3. Changing Your Superuser's Password

Should, for any reason you want to change your postgres superuser's password, get into the postgresql shell (by running "psql -U postgres" in your terminal) again and do this:-

postgres=# \password postgres
Enter new password:
Enter it again:

This is not a particularly important step for your local machine (completely optional) but very very important for your server's postgresql database should you decide to allow remote connections into your postgresql database for any reason.

4. Creating a new Database User, Password and a new Database

With one simple command, you can create your new database user and set a password for it from your terminal.

$ createuser -U postgres yournewuser -P
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

Once you have created your new database user and set a password, you can now create your database and tell this new database that it is used by your new user.

$ createdb -U yournewuser -E utf8 -O yournewuser yournewdb -T template0

5. Update your django settings.py file!

With these done, you can now update your DATABASES setting in your django settings.py file.

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

Now, if we write our django data models (also called 'classes') in models.py and then run python manage.py syncdb command in the terminal, the corresponding database table(s) and field(s) will be created in our newly created postgresql database.

And that's it. Have fun!

(Although there are two pretty decent GUI tools for managing your PostgreSQL database, i.e. pgAdmin3 and phppgadmin, I have decided to introduce and encourage the use of these basic commands in your terminal and in the postgresql shell because they are really not too complicated as you can see in the above examples.)

Categories: Django postgresql

Tagged as: django postgresql

Discussion

Sayanee on Aug 23, 2011 - 8:06 said:

thanks Calvin! it helped a lot :D
Atamert Ölçgen on Aug 24, 2011 - 0:42 said:

Excellent guide. I would like to add two more basic commands for importing and exporting.

Taking a database dump:

pg_dump -Fc mydbname > mydbname.dump

(Here `-Fc` produces a custom format dump, it is advised to use this format unless you want to import the data into another engine)

Restoring a database:

pg_restore -1 mydbname.dump -d mydbname

(`-1` flag makes the whole operation run in a single transaction)
Calvin Cheng on Aug 29, 2011 - 3:47 said:

Thanks for the addition. Updating my post to reflect these two commands...
Anthony on Apr 04, 2012 - 20:15 said:

I LOVE YOU! I've been looking for this information for such a long time (in internet time, haha).
qnub on Apr 08, 2012 - 1:26 said:

Is there any hints about testing with postgres?

I create user for testing with createdb privileges, but when i run tests i take "psycopg2.OperationalError: FATAL: database "db_test" does not exist". Or if i create that db manually - i take "Got an error recreating the test database: cannot drop the currently open database"
kosii on Apr 12, 2012 - 9:36 said:

@qnub
Hey, I had just the same error when I accidentally upgraded django-nose from 0.1.3 to 1.0.0. Downgrading solved the issue.
Ted on May 07, 2012 - 22:12 said:

In Step 4 above:

$ createuser -U postgres yournewuser -P #this was not successful for me

This was (by putting -P in front of yournewuser:
$ createuser -U postgres -P yournewuser

Wouldn't have gotten this far without your help though!

PostgreSQL cheat sheet for django beginners