PostgreSQL: New Project Setup

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.

One thought on “PostgreSQL: New Project Setup”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.