How To Install PostgreSQL on Ubuntu

I note here the quick guide to install PostgreSQL 9.6 on Ubuntu 14.04 / 16.04 / 17.04

Add PostgreSQL Apt Repository

Ubuntu is included Postgres by default but if it is not the one you want to install, so let include Postgresql apt repository by your self. This repository will integrate with your normal system and package management which provides automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.

To install Postgres from this repository, follow these steps:

  • Choose you Ubuntu version:
    • zesty (17.04)
    • xenial (16.04)
    • trusty (15.04)
  • Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository

  • Import the repository signing key, and update the package lists

Install PostgreSQL 9.6

To install PostgreSQL 9.6 on Ubuntu, use apt-get or other apt-driving command to get Postgres’s package from the repository above:

You can change 9.6 to other version in the command by what you want to use.

After install, the system should start automatically Postgresql server

First Usage

Create user:

First, Postgres call the user who want to use database connection is role.

Second, the Postgres Client (psql) and most other Postgres command accept the system user (Operation System User, not who want to use Postgres) as the default role login.

So, at first look, you may confuse OS user with Postgres user (role).

Basic shell command:

If you meet the error:

Let switch to superuser that named postgres:

Type the sudo’s password then type the new password for john

Create database:

Basic shell command:

again, switch to superuser to do the command. The -O parameter specifies the owner for myfirstdb database.

Access the database

Use psql to test accessible role john

To access follow the superuser role, let run:

postgres is the default super roles of PostgreSQL.

Fix the authentication failed error

Sometime you can’t log user in and receive an error:

It’s cause of “pg_hba.conf’s effect”. By default, Postges only accepts peer connections,  not the TCP with password. So, to enable connections which allows you use username/password to connect you need to change authentication method for TCP. But at first, we need to find where “pg_hba.conf” locates:

Now you know the path /etc/postgresql/10/main/pg_hba.conf, edit it:

You’ll see localhost connection with ident method, change them to md5 then save it.

After that, restart the Postgres service:


Hoàng Tựa

Yêu thích lập trình web và tạo ra những thứ đẹp đẽ.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.