Installing Postgres in Ubuntu 14.04

In Postgres, Server on May 28th, 2015

In this blog, I will how to install Postgres database in Ubuntu 14.04. There are few commands to create database, change password and database owner.

Install:

$ sudo apt-get install postgresql postgresql-contrib

Connect to Database

$sudo -i -u postgres
$psql

To quite from Postgres

\q

To logout from postgres user

exit

Postgres has two types of authentication:

  1. IDENT/PEER authentication: By running the “psql” command as a UNIX user e.g.: sudo -u postgres psql.
  2. TCP authentication: by TCP/IP connection using PostgreSQL’s own managed username/password. For this you have to specify host with -h option e.g. psql -d postgres -U postgres -h localhost

When logged in as other user, if you do:

$ psql -d postgres -U postgres

psql: FATAL: Peer authentication failed for user “postgres”

That’s because you are trying to connect to Postgres as PEER authentication.  Unless you are logged in as postgres user into system, you will get this user.  You have to use -h parameter (for host name) to connect via TCP authentication.

Few useful commands

1. Create a user

$sudo -i -u postgres
$createuser --interactive

You have to give username

2. Set a passwrd to user

$psql
postgres=#\password <username>;

Enter new password
OR to change Password

postgres=#ALTER USER 'username WITH PASSWORD 'new_password'

 

3. Create database

$createdb <dbname>

4. Change database owner

$psql
postgres=#ALTER DATABASE <database> OWNER TO <user>

4. Change user Role

$psql
postgres=#ALTER USER 'username' SUPERUSER

Note on Prompt:

$ = you are logged as Postgres user
postgres=# you are connected to Postgres