PostgreSQL

PostgreSQL

PostgreSQL is an object-relational database management system (ORDBMS).

Installation

• Install PostgreSQL

$ sudo apt-get install postgresql 

Administration

Setup Root User Password

• Set up the password of the PostgreSQL root user

$ sudo -u postgres psql postgres 

Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'password'; 

or

postgres=# \password postgres 

Use \quit or \q to quit psql.

postgres=# \q 

Create User

Creating a user in PostgreSQL is in fact creating a role. So there are two ways to do it. The first one is using CREATE ROLE SQL command and the other one is using the programme, createuser.

CREATE ROLE

CREATE ROLE name;

createuser

createuser [option...] [username]

References

• PostgreSQL 8.4.4 Documentation - 20.1. Database Roles
• PostgreSQL 8.4.4 Documentation - createuser

Restart Server

To restart the server, /etc/init.d/postgresql-8.x (depending on the version).

sudo /etc/init.d/postgresql-8.4 restart

Install pgAdmin III

$ sudo apt-get install pgadmin3 

References

http://www.postgresql.org/docs/8.4/static