Setup PostgreSQL in Ubuntu

Jun 19, 2016 2-minute read

Tech • Development

postgres • postgresql • ubuntu

PostgreSQL is a powerful, open source object-relational database system.

Install PostgreSQL

1. From PPA (Recommended)

Create a file /etc/apt/sources.list.d/pgdg.list, and add following line for the repository

# for ubuntu 16.04 users
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main

# for ubuntu 15.10 users
deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main

# for ubuntu 14.04 users
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

# for ubuntu 12.04 users
deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main

Import the repository signing key, and update the package lists

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

Install postgreSQL and, check version

sudo apt-get install postgresql-9.5
psql --version

2. From native store

Enter following commands:

sudo apt-get update
sudo apt-get install postgresql-9.4
psql --version

First time setup

Connect to the default database with user postgres:
```
sudo -u postgres psql template1
```

Set the password for user postgres, then exit psql (Ctrl+D):

ALTER USER postgres with encrypted password 'xxxxxxxx';

Edit the pg_hba.conf file. And, change ‘peer’ to ‘md5’ on the line concerning postgres:
```
sudo vim /etc/postgresql/9.5/main/pg_hba.conf
```
Before changes:
```
local   all     postgres    peer
```
After changes:
```
local   all     postgres    md5
```
Restart the database:
```
sudo service postgresql restart
```
You can check the password with psql -U postgres

Create a user (with superuser access):

createuser -U postgres -d -e -E -l -P -r -s <new_username>

Again edit pg_hba.conf file, and change ‘peer’ to ‘md5’ on the line concerning ‘all’ other users:

Before changes:
```
local   all     all     peer
```
After changes:
```
local   all     all     md5
```
Restart, and check that you can login with new user
```
psql -U <new_username> template1
```

To access with you as a user

Create user with your username(can be found by whoami):

createuser -U postgres -d -e -E -l -P -r -s <my_name>

Restart postgresql server and check that you can login without -U postgres:
```
psql template1
```
You can create database easily now:
```
createdb <db_name>
```

Install PostgreSQL

1. From PPA (Recommended)

2. From native store

First time setup

To access with you as a user

Resources