PostgreSQL Administering the Database Create User & Database

PostgreSQL.md

##Installation

Install PostgreSQL using apt-get

sudo apt-get install postgresql

For PostgreSQL to work with Ruby, we need the client (application interface) library.

sudo apt-get install libpq-dev

See http://www.postgresql.org/docs/9.3/static/libpq.html

##Administering the Database

PostgreSQL create a (Linux) system user called postgres. This system user is the super administrator for PostgreSQL. Since we have sudo access, it is possible to login as postgres with su.

sudo su postgres

The command line utility of PostgreSQL is called psql.

psql

Reference

A few links.

http://www.postgresql.org/docs/9.3/static/sql-commands.html http://www.postgresql.org/docs/9.3/static/backup-dump.html http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html

Create User & Database

The following steps creates a user called Project_name with an encrypted password. Further we create a database with the same name and give ownership to the newly created user.

create user project_name with encrypted password 'project_name';
alter user project_name createdb;
create database project_name;
create database project_name_dev;
create database project_name_test;
alter database project_name owner to project_name;
alter database project_name_dev owner to project_name;
alter database project_name_test owner to project_name;