PostgreSQL: New Project Setup

By Rayed

January 23, 2013

For each new project that need PostgreSQL you should create its own user and its own DB, the following are the steps need for that.

Installation

$ sudo aptitude install postgresql
$ sudo aptitude install python-psycopg2  # For Django access

Require a Password

$ sudo vi /etc/postgresql/9.1/main/pg_hba.conf
:
#local   all             all                      peer
local    all             all                      md5
:
$ sudo service postgresql restart

Create Postgres User

$ sudo -u postgres createuser -P my_user
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) n
Shall the new role be allowed to create more new roles? (y/n) n

Create DB

$ sudo -u postgres createdb my_db -O my_user

Test It

$ psql -U my_user my_db

After login you issue any SQL statement, you should also try the following commands:

\d list tables.
\l list databases.
\du list users.