Installation

Execute the following commands to install PostgreSQL:

$ sudo apt-get install postgresql
$ sudo su - postgres -c "createuser -s $USER"
$ createdb

Database

Then create the database for the application:

$ sudo su - postgres
$ psql
  postgres=# create database digger;
  postgres=# create user digger_usr with encrypted password 'secret';
  postgres=# grant all privileges on database digger to digger_usr;
  postgres=# \c digger postgres
  digger=# grant all on schema public to digger_usr;
  digger=# \q

If you accidentally made a mistake while creating the user, just drop the user and create it again:

$ dropuser digger
$ createuser digger_usr -P

If the database was already created and the username has changed, some tables may remain under the ownership of a previous user. If you get an error like “permission denied for table [table_name]” then it might be the case. Use the command \dt to list all the tables with their ownership. For each one of the impacted tables, use the alter table statement:

$ psql -d digger
  =# alter table table_name owner to digger_usr
  =# \q

To drop the database after use:

$ dropdb digger

PostgreSQL directories are the followings:

/var/log/postgresql/
/var/lib/postgresql/
/etc/postgresql/